SQLite Индекс
Индекс — это специальная таблица поиска, которую поисковый движок базы данных использует для ускорения извлечения данных. Проще говоря, индекс — это указатель на данные в таблице. Индекс в базе данных очень похож на индекс книги.
Возьмём для аналогии страницы указателя китайского словаря. Вы можете быстро найти нужный иероглиф, используя таблицы, отсортированные по пиньиню, количеству черт, радикалам и т. д.
Индексы помогают ускорить SELECT-запросы и предложения WHERE, но замедляют ввод данных при использовании инструкций UPDATE и INSERT. Индексы можно создавать или удалять, не влияя на данные.
Используйте инструкцию CREATE INDEX для создания индекса. Она позволяет задать имя индекса, указать таблицу и столбец или столбцы для индексирования, а также указать, в каком порядке — по возрастанию или по убыванию — строится индекс.
Индексы также могут быть уникальными, подобно ограничению UNIQUE, что предотвращает дублирование записей в столбце или комбинации столбцов.
Команда CREATE INDEX
CREATE INDEXБазовый синтаксис выглядит следующим образом:
CREATE INDEX index_name ON table_name;
Одностолбцовый индекс
Одностолбцовый индекс — это индекс, созданный только по одному столбцу таблицы. Базовый синтаксис выглядит следующим образом:
CREATE INDEX index_name ON table_name (column_name);
Уникальный индекс
Использование уникального индекса служит не только для производительности, но и для целостности данных. Уникальный индекс не позволяет вставлять в таблицу какие-либо дублирующиеся значения. Базовый синтаксис выглядит следующим образом:
CREATE UNIQUE INDEX index_name on table_name (column_name);
Составной индекс
Составной индекс — это индекс, созданный по двум или более столбцам таблицы. Базовый синтаксис выглядит следующим образом:
CREATE INDEX index_name on table_name (column1, column2);
Создавать одностолбцовый индекс или составной — зависит от столбцов, которые вы очень часто используете в предложении WHERE в качестве условий фильтрации запросов.
Если используется только один столбец, выбирайте одностолбцовый индекс. Если в предложении WHERE в качестве фильтров часто используются два или более столбцов, выбирайте составной индекс.
Неявный индекс
Неявные индексы автоматически создаются сервером базы данных при создании объектов. Индексы автоматически создаются для ограничений первичного ключа и ограничений уникальности.
Пример
Ниже приведён пример. Мы создадим индекс по столбцу salary таблицы COMPANY:
sqlite> CREATE INDEX salary_index ON COMPANY (salary);
Теперь давайте воспользуемся командой.indicesили.indexes, чтобы вывести список всех доступных индексов таблицы COMPANY, как показано ниже:
sqlite> .indices COMPANY
В результате будет получен следующий результат, гдеsqlite_autoindex_COMPANY_1— это неявный индекс, созданный при создании таблицы.
salary_index sqlite_autoindex_COMPANY_1
Вывести список всех индексов в базе данных можно следующим образом:
sqlite> SELECT * FROM sqlite_master WHERE type = 'index';
Команда DROP INDEX
Индекс можно удалить с помощью командыDROPв SQLite. При удалении индекса следует соблюдать особую осторожность, потому что производительность может либо снизиться, либо улучшиться.
Базовый синтаксис выглядит следующим образом:
DROP INDEX index_name;
Вы можете использовать следующую инструкцию, чтобы удалить ранее созданный индекс:
sqlite> DROP INDEX salary_index;
Когда следует избегать индексов?
Хотя индексы предназначены для повышения производительности базы данных, существует несколько ситуаций, когда индексов следует избегать. При использовании индексов учитывайте следующие рекомендации:
Индексы не следует использовать в небольших таблицах.
Индексы не следует использовать для таблиц, в которых часто выполняются крупномасштабные операции обновления или вставки.
Индексы не следует использовать для столбцов, содержащих большое количество значений NULL.
Индексы не следует использовать для столбцов, которые часто изменяются.