Краткий экскурс по важнейшим понятиям в мире индексов БД:
Поиск по базам данных осуществляет каждый. Но полный поиск по всей базе (миллионы записей) может быть очень долгим и будет очень неэффективным. К примеру:
SELECT*FROM users WHERE id =5
Такой запрос пройдётся по всем 1.000.000 записям в поисках всех записей с id 5.
Поэтому люди придумали индексы -- отдельно хранимые структуры данных, которые позволяет убыстрить поиск по базе данных с O(n) до O(log n) по времени.
Чаще всего индекс это b-tree -- описание этой структуры данных выходит за пределы текущего вопроса -- в котором отсортированно хранятся данные колонки.
CREATEINDEX users_id ON users (id)
Теперь наш запрос не приведёт к поднятию всей базы, а лишь к поднятию небольшого индекса. А данные быстро найдутся.
Ещё быстрее запрос можно сделать, если мы знаем, что значения в колонке должны быть уникальны, тогда используя специальный синтаксис:
CREATEUNIQUEINDEX users_id ON users (id)
мы создадим индекс при поиске, по которому СУБД не будет пытаться найти ещё значение, если хотя бы одно уже было найдено.
Очень часто можно увидеть записи такого рода:
CREATEINDEX users_id ON users (sex, birthyear)
Несмотря на то, что может показаться, что это создание двух индексов -- это не так. На самом деле создаётся один индекс, но по двум колoнкам. Это нужно, чтобы такие запросы:
SELECT*FROM users WHERE sex = male AND birthyear =1984
выполнялись без поиска в бд, а поиском в индексе. Стоит обратить внимание, что порядок указания колoнок важен, такой запрос:
SELECT*FROM users WHERE birthyear =1984AND sex = male
оптимизирован с помощью индексов не будет.
Стоит указать, что индексы, в например MYSQL, хранятся в БД в виде или B-Tree, или HashMap. Стоит указать, что использование второго варианта обеспечит постоянное время поиска (O(1)), но с помощью такого индекса будет невозможно искать отсортированные значения (например, age < 20). Для каждого индекса можно указать свой тип индекса.
Проверить используются ли индексы для ваших запросов (и вообще проследить производительность вашего SQL кода) можно посмотрев в EXECUTION PLANS, обсуждение которых выходит за рамки текущего вопроса.
