Индексы в SQL
Когда вы добавляете в таблицу новую строку, СУБД размещает эти данные не оптимально. Например, если вы добавляете строку в таблицу Users, СУБД не размещает строки в числовом порядке значений столбца id или в алфавитном порядке значений столбца last_name. Вместо этого он просто помещает данные в следующее доступное место в файле (СУБД поддерживает список свободных мест для каждой таблицы).
Это приводит к тому, что для выполнения запроса типа:
SELECT email FROM Users WHERE email LIKE 'l%';
Серверу базы данных приходится проверять каждую строку таблицы, чтобы найти соответствия. Это подходит для маленьких таблиц, но становится чрезмерно времязатратным по мере роста объёма данных.
Для сравнения как запрос на поиск по email отработает в зависимости от наличия индекса на поле.
Индексы функционируют как предметные указатели в книге 📖, позволяя быстро находить информацию без прочтения всего текста. Они представляют собой специальные таблицы, строки которых, в отличие от обычных таблиц данных, расположены в строго определённом порядке. Но вместо того, чтобы содержать все данные о некоторой записи, индекс содержит только столбец (или столбцы), используемый, чтобы найти строки в таблице данных, вместе с информацией, описывающей, где физически расположена эта строка. Таким образом, роль индексов состоит в том, чтобы облегчить поиск подмножества строк и столбцов таблицы без необходимости сканировать каждую строку в таблице.
Создание индекса ✨
Возвращаясь к таблице Users, вы можете добавить индекс к столбцу email, чтобы ускорить любые запросы, которые работают со значением этого столбца.
Вот как можно добавить такой индекс в СУБД MySQL:
CREATE INDEX idx_email ON Users (email);
Эта инструкция создаёт индекс c именем idx_email для столбца Users.email. При наличии индекса оптимизатор запросов может выбрать использование индекса, если сочтёт это полезным. Если в таблице имеется более одного индекса, оптимизатор должен решить, применение какого именно индекса наиболее выгодно для конкретной инструкции SQL.
Все системы управления базами данных предоставляют возможность просмотра существующих индексов. Для пользователей MySQL существует команда SHOW, которая позволяет отобразить все индексы для конкретной таблицы, как показано в примере ниже:
SHOW INDEX FROM Users;
Вывод демонстрирует, что в таблице Users есть 2 индекса: один — для столбца id с именем PRIMARY и ещё один для столбца email, который мы только что определили.
Когда таблица была создана, MySQL автоматически сгенерировала индекс для столбца первичного ключа, которым в данном случае является id, и присвоил индексу имя PRIMARY. Это особый тип индекса, используемый с ограничением первичного ключа, которое гарантирует, что каждое значение в столбце или группе столбцов, назначенных в качестве первичного ключа таблицы, уникально и не может быть NULL.
Удаление индекса
Если после создания индекса вы решите, что он больше не нужен, можете удалить его следующим образом:
DROP INDEX idx_email ON Users;
Уникальные (UNIQUE) индексы
При проектировании баз данных важно определить, для каких столбцов допускаются повторения значений, а для каких — нет.
Например, в таблице Users может быть несколько пользователей с одинаковыми именами, но идентификаторы и адреса электронных почт они должны иметь разные, чтобы была возможность их различать.
Добиться гарантируемой уникальности значений можно, создав уникальный индекс на столбец Users.email. Уникальный индекс выполняет две функции:
- он обеспечивает все преимущества стандартного индекса
- он предотвращает дублирование значений в индексируемом столбце
Система управления базой данных будет проверять уникальный индекс при попытке добавления или изменения данных в индексированном столбце, чтобы убедиться, что введённое значение не дублирует уже существующее в таблице.
Создание уникального индекса для столбца Users.email выполняется следующим образом:
CREATE UNIQUE INDEX idx_email ON Users (email);
При наличии индекса вы получите сообщение об ошибке, если попытаетесь добавить нового клиента с уже существующим адресом электронной почты:
Error(1062) 23000: "Duplicate entry '[email protected]' for key 'users.idx_email'"
Создание уникальных индексов для столбца или столбцов, определённых как первичный ключ, излишне, так как система управления базой данных автоматически обеспечивает уникальность значений первичного ключа. Впрочем, размещение нескольких уникальных индексов в одной таблице допустимо и может быть целесообразно, если вы видите в этом необходимость.
Многостолбцовые индексы
Помимо одностолбцовых индексов, существует возможность создавать индексы, включающие в себя несколько столбцов. К примеру, для поиска студентов по имени и фамилии можно создать совместный индекс по этим двум полям:
CREATE INDEX idx_full_name ON Student (last_name, first_name);
Такой индекс окажется полезным для запросов, где необходимы и имя, и фамилия, или только фамилия. Однако, для запросов, задающих только имя, он не принесёт пользы. Это аналогично поиску номера телефона по телефонному справочнику: если известны и имя, и фамилия, поиск упрощается благодаря упорядоченности справочника по фамилии, а затем по имени. Если же известно только имя, придётся перебирать все записи в поисках нужного человека.
При создании индексов, включающих несколько столбцов, важно продумать порядок столбцов в индексе, чтобы он был максимально эффективным. Однако для достижения нужной производительности запросов всегда можно создать несколько индексов с теми же столбцами, но разным порядком их следования.
Как используются индексы
Индексы часто применяются СУБД для эффективного поиска нужных строк в таблице, а затем для получения дополнительных данных из связанных таблиц по запросу пользователя. Возьмём для примера запрос:
SELECT id, first_name, last_name FROM Student WHERE first_name LIKE 'A%' AND last_name LIKE 'L%'
В ответ на такой запрос СУБД может выбрать один из нескольких подходов:
- Произвести полное сканирование всех строк таблицы.
- Воспользоваться индексом по столбцу last_name для поиска студентов с фамилией на «L», а затем проверить каждую из этих строк на соответствие имени, начинающегося на «A».
- Использовать составной индекс по last_name и first_name для непосредственного нахождения студентов, удовлетворяющих обоим критериям.
Последний метод представляется наиболее эффективным, так как позволяет найти все необходимые строки за один проход, избегая повторного обращения к таблице. Но как определить, какой из методов выберет оптимизатор запросов MySQL? Для этого можно использовать команду EXPLAIN, которая показывает, как СУБД планирует выполнить запрос, не запуская его фактически:
EXPLAIN SELECT id, first_name, last_name FROM Student WHERE first_name LIKE 'A%' AND last_name LIKE 'L%';
Анализируя результаты, можно увидеть, что в столбце possible_keys указаны потенциально применимые индексы idx_last_name или idx_full_name, а в столбце key указано, что выбран индекс idx_full_name.
Обратная сторона индексов
Если индексы столь эффективны, возникает вопрос: почему бы просто не индексировать всё подряд? 🧐
Ответ кроется в том, что каждый индекс представляет собой таблицу (пусть и особый тип таблицы, но все же это таблица). Следовательно, каждый раз, когда строка добавляется в таблицу или удаляется из неё, должны быть изменены все индексы в этой таблице. При обновлении строки любые индексы для столбца (или столбцов), которые были затронуты, также должны быть изменены. Следовательно, чем больше у вас индексов, тем больше должна работать СУБД, чтобы поддерживать все объекты схемы в актуальном состоянии — что приводит к замедлению работы.
Более того, индексы занимают дополнительное место на диске и требуют внимательного управления со стороны администраторов баз данных. Поэтому оптимальным решением является создание индексов только тогда, когда это действительно необходимо. Если индекс нужен временно, например, для выполнения месячного отчёта, его можно добавить перед началом процедуры и удалить после её завершения.
В итоге, идеальный подход заключается в нахождении баланса: необходимо иметь достаточно индексов для эффективной работы, но не столько, чтобы это сказывалось на производительности. Если вы не уверены в нужном количестве индексов, начните с минимального их числа и добавляйте по мере необходимости.
Давайте проверим как вы усвоили тему:
Какое утверждение наилучшим образом объясняет причину, по которой не следует индексировать каждый столбец в таблице базы данных?