Я разберу здесь оператор CREATE INDEX — от формальной грамматики до поведения NULL-значений и совместимости форматов схемы. Это справочный материал, к которому удобно возвращаться при проектировании индексов в SQLite
Команда CREATE INDEX включает ключевые слова CREATE INDEX, имя нового индекса, ключевое слово ON, имя соответствующей таблицы и список столбцов или выражений в скобках. Опциональное предложение WHERE делает индекс частичным
Если указано IF NOT EXISTS и индекс с таким именем уже существует, команда не выполняется и не вызывает ошибок
Произвольных ограничений на количество индексов, которые можно прикрепить к одной таблице, не существует. Количество столбцов в индексе ограничено значением, задаваемым функцией sqlite3_limit(SQLITE_LIMIT_COLUMN,...)
Индексы удаляются командой DROP INDEX
Формальная грамматика оператора выглядит следующим образом:
CREATE UNIQUE INDEX IF NOT EXISTS schema-name . index-name ON table-name ( indexed-column , ... ) WHERE exprindexed-column может обозначать имя столбца или выражение с опциональными модификаторами, такими как порядок сортировки ASC или DESC
Уникальные индексы
Если между CREATE и INDEX указано UNIQUE, не допускается наличие дублирующихся записей, за что будет выдана ошибка при попытке вставки
Все значения NULL для уникальных индексов принимаются как уникальные, что соответствует правилам PostgreSQL, MySQL, Firebird и Oracle, однако отличается от Informix и Microsoft SQL Server
При переносе схем между различными СУБД важно учитывать это поведение: использование SQL Server может привести к неожиданным результатам в SQLite при наличии NULL-значений в уникальных столбцах
По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы
По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы
Индексы по выражениям
Выражения в индексе ограничены лишь столбцами индексируемой таблицы и не могут ссылаться на другие таблицы или использовать подзапросы
Индексы по выражениям доступны с версии SQLite 3.9.0. Выражения могут содержать только столбцы индексируемой таблицы и не включать изменяемые функции
Убывающие индексы
Каждое имя столбца или выражение может содержать ASC или DESC для указания порядка сортировки, который может игнорироваться в зависимости от формата файла базы данных
Устаревший формат схемы (1) не учитывает порядок сортировки, тогда как формат с убывающим индексом (4) это делает
С версии 3.7.10 применяется новый формат схемы, в то время как прежние версии используют устаревший. Прагма legacy_file_format позволяет изменить поведение для разных версий SQLite
На моей практике неожиданная потеря порядка сортировки в индексе — одна из самых трудно диагностируемых проблем совместимости. Рекомендую проверять формат схемы при работе с базами данных, открываемыми старыми версиями SQLite
NULLS FIRST и NULLS LAST
Предикаты NULLS FIRST и NULLS LAST не поддерживаются для индексов. В целях сортировки SQLite считает значения NULL меньше всех остальных значений. Поэтому значения NULL всегда появляются в начале индекса ASC и в конце индекса DESC
Правила сортировки
Предложение COLLATE, необязательно следующее за каждым именем столбца или выражением, определяет последовательность сортировки (collation sequence), используемую для текстовых записей в этом столбце. Последовательностью сортировки по умолчанию является последовательность, определённая для данного столбца в операторе CREATE TABLE
Если же последовательность сортировки иным образом не определена, используется встроенная последовательность сортировки BINARY
Типичные ошибки при создании индексов
Работая с индексами в SQLite, я регулярно сталкиваюсь с несколькими повторяющимися проблемами, которые стоит обозначить отдельно
Использование изменяемых функций в выражениях индекса. Функции вроде random() или datetime('now') не могут быть частью индекса по выражению — SQLite вернёт ошибку при попытке создать такой индекс
Игнорирование формата схемы при убывающих индексах. Если база данных должна открываться версиями SQLite до 3.7.10, убывающий порядок сортировки будет молча проигнорирован. Это не ошибка компиляции — поведение просто изменится без предупреждения
Ожидание поведения NULLS LAST в ASC-индексе. В отличие от PostgreSQL, SQLite не поддерживает NULLS LAST для индексов. Если логика приложения зависит от позиции NULL-значений в результатах, это нужно учитывать на уровне запросов, а не индексов
Создание индекса по столбцам другой таблицы. Выражение в индексе может ссылаться только на столбцы той таблицы, для которой создаётся индекс. Попытка сослаться на другую таблицу приведёт к ошибке
Ответы на эти вопросы могут быть для вас полезными
Можно ли создать индекс, если он уже существует, без получения ошибки? Да. Используйте предложение IF NOT EXISTS — в этом случае команда просто ничего не сделает, если индекс с таким именем уже есть
Как SQLite обрабатывает значения NULL в уникальном индексе? Каждое значение NULL считается уникальным и отличным от других NULL. Это поведение совпадает с PostgreSQL, MySQL и Oracle, но отличается от Informix и Microsoft SQL Server
Поддерживаются ли индексы по вычисляемым выражениям? Да, начиная с версии SQLite 3.9.0. Выражение должно ссылаться только на столбцы индексируемой таблицы и не может содержать изменяемые функции или подзапросы
Учитывается ли порядок сортировки ASC/DESC в индексе? Зависит от формата схемы. Формат 4 (убывающий индекс) учитывает порядок сортировки и используется по умолчанию начиная с SQLite 3.7.10. Более старые версии используют формат 1, который порядок сортировки игнорирует
Как удалить индекс в SQLite? С помощью команды DROP INDEX index-name. Индекс можно удалить независимо от таблицы, к которой он привязан
