Базы данных для начинающих. Создание частичных и композитных (составных) индексов в базах данных

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223
Рубрика SQLite на сайте: zametkinapolyah...
Текстовая версия: zametkinapolyah...
Паблик блога в ВК: zametki...
Тематическая группа в ВК: zametki_bd
-- Композитные и частичные индексы в базах данных SQLite
/*
Мы упоминали о том, что SQLite позволяет создавать несколько индексов для одной таблицы в базе данных,
давайте разберемся с синтаксисом создания нескольких индексов для одной таблицы, такие индексы еще называют
композитными:
*/
CREATE INDEX ix1_table1 ON table1 (a);
CREATE INDEX ix1_table1 ON table1 (a, b);
/*
Создание нескольких индексов для одной таблицы ничем принципиально не отличается от создания одного
индекса: мы просто перечисляем столбцы в круглых скобках, которые будут проиндексированы.
*/
-- Для составных индексов имеет значение то, как и в каком порядке указаны имена столбцов при создании индекса. Если индекс состоит из столбцов col1 и col2, то он может быть применен только в запросе где в критериях используются оба этих поля.
SELECT col1, col2 FROM table1;
SELECT col1, col3, col4 FROM table1;
-- В большинстве случаев индекс по одному столбцу будет работать достаточно хорошо и сэкономит время и место. Индексы по более чём трём столбцам вряд ли будут полезными, если только таблица не используется крайне однообразно
/*
Но SQLite дает возможность создавать индексы не для всех значений столбца (не для всех строк в таблице),
а только частичные индексы. Частичные индексы создаются путем наложения определенных
условий на значения, хранящиеся в столбце при помощи клаузулы WHERE:
*/
col1
1
2
3
4
5
6
null
8
12
null
45
null
null
CREATE INDEX partInd_table1 ON table1 (col1)
WHERE col1 IS NOT NULL;
/*
Мы знаем, что в SQLite значение с типом данных NULL всегда уникально, так как значение с классом данных NULL
не равно даже другому NULL, в данном случае будет создан частичный индекс для всех строк столбца, кроме тех строк,
у которых в столбце column_name хранится значение NULL.
*/
-- Частичные индексы экономят место на вашем сервере в том случае, если индексируемый столбец имеет много значений NULL или в столбце много повторяющихся одинаковых значений (вы просто исключаете эти повторяющиеся значения из индекса,поскольку при огромном количестве одинаковых значений будет все равно происходить "тупой" перебор).
-- Используя частичные индексы в вышеописанных случаях вы ускоряете процесс выборки данных и при этом операции, связанные с изменением данных не так сильно "тормозят".