Эффективный способ обеспечения уникальных строк в SQLite3

Я использую SQLite3 в одном из моих проектов, и мне нужно убедиться, что строки, вставленные в таблицу, уникальны в отношении комбинации некоторых из их колонны. В большинстве случаев вставленные строки будут отличаться в этом отношении, но в случае соответствия новая строка должна обновить/заменить существующий.

Очевидным решением было использование составного первичного ключа с предложением конфликта для обработки конфликтов. Для этого:

CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT);

стало следующим:

CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT, PRIMARY KEY (Fld0, Fld2, Fld3) ON CONFLICT REPLACE);

Это действительно приводит к ограничению уникальности, в котором я нуждаюсь. К сожалению, это изменение также приводит к снижению производительности, которое выходит за рамки того, что я ожидал. я сделал несколько тестов с использованием утилиты командной строки sqlite3, чтобы убедиться, что в остальной части моего кода нет ошибки. Тест включает ввод 100 000 строк, либо в одном транзакции или в 100 транзакций по 1000 строк каждая. Я получил следующие результаты:

                                | 1 * 100,000   | 10 * 10,000   | 100 * 1,000   |
                                |---------------|---------------|---------------|
                                | Time  | CPU   | Time  | CPU   | Time  | CPU   |
                                | (sec) | (%)   | (sec) | (%)   | (sec) | (%)   |
--------------------------------|-------|-------|-------|-------|-------|-------|
No primary key                  | 2.33  | 80    | 3.73  | 50    | 15.1  | 15    |
--------------------------------|-------|-------|-------|-------|-------|-------|
Primary key: Fld3               | 5.19  | 84    | 23.6  | 21    | 226.2 | 3     |
--------------------------------|-------|-------|-------|-------|-------|-------|
Primary key: Fld2, Fld3         | 5.11  | 88    | 24.6  | 22    | 258.8 | 3     |
--------------------------------|-------|-------|-------|-------|-------|-------|
Primary key: Fld0, Fld2, Fld3   | 5.38  | 87    | 23.8  | 23    | 232.3 | 3     |

В настоящее время мое приложение выполняет транзакции не более 1000 строк, и я был удивлен 15-кратным снижением производительности. Я ожидал максимум 3-кратного снижения пропускной способности и увеличения использования ЦП, как видно в случае транзакции в 100 тыс. Транзакций. Я предполагаю, что индексирование, связанное с поддержанием ограничений первичного ключа, требует значительно большего числа синхронных операций БД, что делает мои жесткие диски узким местом в этом случае.

Использование режима WAL имеет некоторый эффект - увеличение производительности примерно на 15%. К сожалению, этого недостаточно. PRAGMA synchronous = NORMAL, похоже, не имеет никакого эффекта.

Я мог бы восстановить некоторую производительность, увеличив размер транзакции, но я бы предпочел не делать этого из-за увеличения использования памяти и проблем с отзывчивостью и надежность.

Текстовые поля в каждой строке имеют переменную длину около 250 байтов в среднем. Производительность запросов не имеет большого значения, но производительность вставки очень важна. Мой код приложения находится на C и является (предположительно) переносимым, по крайней мере, для Linux и Windows.

Есть ли способ улучшить производительность вставки без увеличения размера транзакции? Либо какая-то настройка в SQLite (что-то, но постоянно заставляющее БД в асинхронную операцию, то есть) или программно в моем коде приложения? Например, существует ли способ обеспечить уникальность строк без использования индекса?

BOUNTY:

Используя метод хеширования/индексации, описанный в моем собственном ответе, мне удалось несколько снизить падение производительности до такой степени, что это, вероятно, приемлемо для моего приложения. Кажется, однако, что по мере увеличения количества строк в таблице наличие индекса делает вставки медленнее и медленнее.

Меня интересует какой-либо метод или настройка тонкой настройки, которые повысят производительность в данном конкретном случае использования, если это не связано с взломом кода SQLite3 или иным образом заставило проект стать незаменимым.

Ответ 1

Я использовал sqlite для вставки миллионов строк во время выполнения, и это то, что я использовал для повышения производительности:

Используйте как можно меньше транзакций.
Используйте параметризованные команды для вставка данных (подготовка команду один раз и просто изменить paramater в цикле)
Set PRAGMA synchronous OFF (не уверен как это работает с WAL)
Увеличить размер страницы базы данных.
Увеличьте размер кеша. Это важный параметр, поскольку он заставит sqlite фактически записывать данные на диск меньше времени и будет выполнять больше операций в памяти, что ускорит весь процесс.
Если вам нужен индекс, добавьте его после вставки строк, запустив необходимую команду sqlite. В этом случае вам нужно будет обеспечить уникальность самостоятельно, как вы сейчас делаете это сейчас.

Если вы попробуете их, отправьте результаты своих тестов. Я считаю, что это будет интересно всем.

Ответ 2

Предложение ON CONFLICT REPLACE заставит SQLite удалить существующие строки, а затем вставить новые строки. Это означает, что SQLite, вероятно, собирается потратить некоторое время

удаление существующих строк
обновление индексов
вставка новых строк
обновление индексов

Что я беру на себя, основываясь на документации SQLite и читающей о других системах управления базами данных. Я не смотрел исходный код.

SQLite имеет два способа выражения ограничений уникальности: PRIMARY KEY и UNIQUE. Однако оба они создают индекс.

Теперь действительно важный материал.,.

Хорошо, что вы делали тесты. Большинство разработчиков этого не делают. Но я думаю, что ваши результаты теста плохо вводят в заблуждение.

В вашем случае не имеет значения, как быстро вы можете вставлять строки в таблицу, у которой нет первичного ключа. Таблица, которая не имеет первичного ключа, не удовлетворяет вашим основным требованиям к целостности данных. Это означает, что вы не можете полагаться на свою базу данных, чтобы дать вам правильные ответы.

Если он не должен давать правильные ответы, я могу сделать это действительно, очень быстро.

Чтобы получить значимое время для вставки в таблицу без ключа, вам нужно либо

запустить код перед вставкой новых данных чтобы убедиться, что вы не нарушаете необъявленное ограничение первичного ключа, и убедитесь, что вы обновили существующие строки с правильными значениями (вместо этого вставки), или
запустить код после вставки в этот таблицы для очистки дубликатов (Fld0, Fld2, Fld3) и согласовать конфликты

И, конечно же, время, которое должны принять эти процессы, должно быть также учтено.

FWIW, я выполнил тест, выполнив инструкции вставки 100K SQL в вашу схему в транзакциях из 1000 операторов, и это заняло всего 30 секунд. Единственная транзакция из 1000 вставных операторов, которая, кажется, является тем, что вы ожидаете в процессе производства, заняла 149 мс.

Возможно, вы можете ускорить процесс, вставив в временную таблицу без ключа, а затем обновив таблицу с ключом.

Ответ 3

(Я обычно не отвечаю на собственные вопросы, но хотел бы задокументировать несколько идей/частичных решений для этого.)

Основная проблема с составным первичным ключом заключается в том, как обрабатываются индексы. Композитные клавиши означают индекс на составном значении, что в моем случае означает индексирование строк. Хотя сравнение строковых значений происходит не так медленно, индексирование значения с длиной, скажем, 500 байт означает, что узлы B-дерева в индексе могут помещать гораздо меньше указателей в строке / node, чем B-дерево, которое индексирует 64-битное целочисленное значение. Это означает, что для каждого поиска индекса загружается гораздо больше страниц БД по мере увеличения высоты B-дерева.

Чтобы решить эту проблему, я изменил свой код так, чтобы:

Он использует режим WAL. Увеличение производительности, безусловно, стоило такого небольшого изменения, так как у меня нет проблем с отсутствием автономного файла DB.
Я использовал хеш-функцию MurmurHash3 - после повторной записи на C и его адаптации - для создания одного 32-битного хэш-значение из значений полей, которые будут формировать ключ. Я сохранил этот хеш в новом индексированном столбце. Поскольку это целочисленное значение, индекс довольно быстрый. Это единственный индекс для этой таблицы. Поскольку в таблице будет не более 10 000 000 строк, столкновение хэшей не будет проблемой производительности, хотя я не могу считать значение хеша UNIQUE, индекс будет возвращать только одну строку в общем случае.

В этот момент есть две альтернативы, которые я закодировал и в настоящее время проходит тестирование:

DELETE FROM Event WHERE Hash=? AND Fld0=? AND Fld2=? AND Fld3=?, а затем INSERT.
UPDATE Event SET Fld1=?,... WHERE Hash=? AND Fld0=? AND Fld2=? AND Fld3=?, за которым следует INSERT, если не обновлены строки.

Я ожидаю, что вторая альтернатива будет быстрее, но мне придется сначала завершить тестирование. В любом случае, похоже, что с этими изменениями падение производительности (по сравнению с исходной таблицей без индекса) уменьшилось до 5 или около того, что намного более управляемо.

EDIT:

В этот момент я решил использовать вторую вариацию, которая действительно немного быстрее. Кажется, однако, что любой тип индекса сильно замедляет SQLite3, поскольку индексная таблица становится больше. Увеличение размера страницы БД до 8192 байтов, по-видимому, несколько помогает, но не так резко, как хотелось бы.

Ответ 4

Case When Exists((Select ID From Table Where Fld0 = value0 and Fld2 = value1 and Fld3 = value 2)) Then
    --Insert Statement
End

Я не на 100%, что вставка работает так же, как в SQLite, но я думаю, что это нужно. Это с правильной индексацией полей Where должно быть довольно быстрым. Однако это две транзакции, которые нужно учитывать.

Ответ 5

В дополнение ко всем остальным отличным ответам, одна вещь, которую вы можете сделать, - это разделение данных на несколько таблиц.

SQLite INSERT становятся медленнее и медленнее по мере увеличения количества строк, но если вы можете разбить таблицу на несколько, эффект уменьшится (например: "names" → "names_a", "names_b",... для имена, начинающиеся с буквы x). Позже вы можете сделать CREATE VIEW "names" AS SELECT * FROM "names_a" UNION SELECT * FROM "names_b" UNION ....