Часто сталкиваюсь с тем, что люди с опытом работы более 3-х лет backend разработчиками про индексы могут рассказать только то, что они ускоряют поиск.
- А за счет чего происходит ускорение?
- Чем отличается кластерный от некластерного?
- В каких случаях они работают, а в каких нет?
- Если индексы классно ускоряют поиск, почему их не создают на все поля во всех таблицах?
Для того, чтобы разобраться в этих вопросах предлагаю использовать аналогии из реального мира. Лично мне это прием очень сильно помогает разбираться в новых для меня областях.
Для примера возьмем бумажный телефонный справочник.
Предположим, что внесенные в него ФИО с телефонами идут в порядке добавления записей (сначала добавлен Петров, затем Васичкин, потом Иванов и еще млн других записей). Для того, чтобы найти телефон кого-то конкретного, придется просмотреть весь справочник от начала и до конца, что не очень то удобно. Этот пример представляет собой таблицу с данными без каких-либо индексов (такие таблицы называют кучей).
Теперь переделаем справочник так, чтобы в нем все было упорядочено сначала по фамилии, затем по имени, отчеству и номеру телефона. Для поиска телефона нужного нам человека мы открываем справочник посередине. Если нам не повезло сразу попасть на страницу с нужной фамилией, то в зависимости от фамилий на странице, мы понимаем, что дальше нужно искать, например, слева от открытой страницы. Теперь с левой частью справочника проделываем то же действие, открываем приблизительно посередине и проводим такую же оценку как и выше. Так до тех пор, пока не найдем в справочнике нужного нам человека. Мы только что добавили в наш справочник уникальный составной кластерный индекс. Кластерный он потому, что сами данные теперь хранятся упорядоченно.
Добавим еще одно улучшение в наш справочник - оглавление по первой букве фамилий. Теперь для поиска нужного человека открываем оглавление, в нем получаем номер страницы для перехода и дальнейшего поиска по записям. Это неуникальный некластерный индекс, который содержит указатель на место дальнейшего поиска данных.
Теперь представим ситуацию, что нам нужно найти телефон, но знаем мы только имя и отчество его владельца. К сожалению, ни один из заложенных в наш справочник способов поиска (упорядочивание данных по ФИО и оглавление по первой букве фамилии) никак не поможет в этом вопросе, поэтому останется только просмотреть весь справочник от начала до конца.
Добавленный некластерный индекс будет маленьким, но все равно добавит несколько страниц в справочник. Если же мы добавим в справочник другие виды оглавлений (например, по фамилии целиком и отдельно по имени), то количество страниц с оглавлениями может превысить количество страниц с основными данными (ФИО и телефонами). В личной практике сталкивался со случаем, когда индексы занимали в 5 раз больше места на диске, чем данные в таблице.
И наконец рассмотрим ситуацию, когда нужно наш справочник расширить новыми данными, например, нужно добавить еще пару Ивановых. Если между существующими записями недостаточно места, чтобы вписать новые, то потребуется вырвать из справочника такой лист, на его место вклеить пару новых, перераспределить записи с вырванного листа между новыми листами не забыв добавить в правильное место записи из-за которых все началось. Также потребуется перенумеровать все последующие страницы и обновить оглавление. Затратная процедура не правда ли? Получается, что индексы могут невероятно ускорить поиск, но при этом существенно замедляют запись.
В качестве сухого остатка: индексы - это отдельные структуры данных, которые служат цели быстрого поиска, при этом они занимают дополнительное место на жестком диске и при добавлении/изменении данных в таблице индексы требуют ресурсы для актуализации. Именно поэтому при проектировании баз данных приходится тщательно продумывать не только структуру таблиц, но и набор индексов к таблицам, чтобы найти баланс между чтением и записью.