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

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223


Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Текстовая версия: http://zametkinapolyah.ru/zame....tki-o-mysql/chast-11
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/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 или в столбце много повторяющихся одинаковых значений (вы просто исключаете эти повторяющиеся значения из индекса,поскольку при огромном количестве одинаковых значений будет все равно происходить "тупой" перебор).

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