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

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

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

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

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

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

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

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

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

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

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

Кстати, я выполнил тест, запустив в вашу схему операторы вставки SQL по 100 тыс. Транзакций по 1000 операторов, и это заняло всего 30 секунд. Одна транзакция из 1000 операторов вставки, которая, по-видимому, соответствует ожидаемой при производстве, заняла 149 мсек.

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

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

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

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

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

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

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

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

• Он использовал режим WAL . Увеличение производительности, безусловно, стоило такого небольшого изменения, поскольку у меня нет проблем с тем, что файл БД не является автономным.

• Я использовал хеш-функцию 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 раз, что гораздо более управляемо.

РЕДАКТИРОВАТЬ:

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