Многопоточное чтение SQLite: оптимизация SELECT для 1 млн записей

При работе с SQLite в высоконагруженных сценариях, когда требуется выполнять тысячи параллельных SELECT-запросов к read-only базе данных, стандартные подходы могут давать низкую производительность. Особенно это заметно при попытке заменить LINQ-операции (WHERE, ORDER BY, SKIP, TAKE) для таблицы с 1 млн записей и 20 полями. Рассмотрим эффективные методы ускорения.

Проблема Mode=Memory и Cache=Shared

Использование Data Source=filename;Mode=Memory;Cache=Shared создаёт общую базу в памяти, но совместный доступ через Cache=Shared снижает пропускную способность. Включение PRAGMA read_uncommitted = true не решает проблему, так как SQLite всё равно блокирует записи при конкурентном чтении. Для read-only сценариев лучше отказаться от Memory-режима.

Оптимальная конфигурация для read-only

Для максимальной скорости многопоточных SELECT-запросов используйте:

• Файловую базу данных с параметром Mode=ReadOnly - это снимает блокировки на запись.
• WAL-режим (PRAGMA journal_mode=WAL) - позволяет параллельно читать и писать без взаимных блокировок.
• Увеличение кэша (PRAGMA cache_size = -20000 - 20 МБ) для хранения страниц в памяти.
• Отключение синхронизации (PRAGMA synchronous = OFF или NORMAL) для read-only.

Использование VFS для многопоточности

Стандартный VFS (Virtual File System) SQLite не оптимизирован для тысяч параллельных чтений. Кросс-платформенные альтернативы:

• unix-dotfile - стандартный VFS для Unix, но он использует файловые блокировки, что снижает производительность.
• NFS VFS - не подходит для локальных баз.
• Собственная реализация VFS - в Microsoft.Data.Sqlite можно создать кастомный VFS, который использует pread/pwrite без блокировок, но это требует глубоких знаний.

На практике для 500-5000 параллельных SELECT-запросов лучше не использовать VFS, а применить пул соединений с WAL-режимом.

Пул соединений и многопоточность

Создайте фиксированный пул из 10-20 соединений (по числу ядер процессора). Каждое соединение открывается с Mode=ReadOnly и Journal Mode=WAL. Запросы распределяются через очередь (например, BlockingCollection). Это даёт:

• Отсутствие блокировок между потоками.
• Использование кэша страниц на уровне соединения.
• Возможность выполнять 500-5000 запросов с задержкой менее 1 мс на SELECT (при условии индексации).

Индексация и оптимизация запросов

Для таблицы с 1 млн записей и 20 полями (10 строковых) обязательно создайте индексы на поля, используемые в WHERE, ORDER BY и JOIN. Используйте составные индексы для покрытия запросов. Например:

CREATE INDEX idx_filter ON table (field1, field2) WHERE condition;

Также избегайте SELECT * - выбирайте только нужные столбцы. Для пагинации (SKIP, TAKE) используйте ROWID или LIMIT/OFFSET с индексами.

Альтернативы SQLite для высоких нагрузок

Если производительность остаётся низкой, рассмотрите:

• DuckDB - аналитическая СУБД с отличной поддержкой параллельных чтений.
• SQLite с расширением mpt (multi-process) - экспериментальная VFS для многоядерных систем.
• In-memory базы данных (например, Redis) для кэширования часто запрашиваемых данных.

Выбор зависит от требований к кросс-платформенности и объёму данных.