Оптимизация запросов на основе кластеризованных и некластеризованных индексов в SQL?

Для SQL Server

Q1 Дополнительное пространство необходимо только для кластерного индекса, если он не является уникальным. SQL Server добавит 4-байтовый уникальный указатель к неуникальному кластеризованному индексу. Это связано с тем, что он использует ключ кластера как идентификатор строки в некластеризованных индексах.

Q2 Некластеризованный индекс можно читать по порядку. Это может помочь запросам, в которых вы указываете заказ. Это также может сделать объединения слиянием привлекательными. Это также поможет с запросами диапазона (x ‹col и y> col).

Q3 SQL Server выполняет дополнительный «поиск по закладкам» при использовании некластеризованного индекса. Но это только в том случае, если ему нужен столбец, которого нет в индексе. Также обратите внимание, что вы можете include дополнительных столбцов на конечном уровне индексов. Если индекс можно использовать без дополнительного поиска, он называется индексом покрытия.

Если требуется поиск по закладке, не требуется большого процента строк, пока не будет проще сканировать весь кластеризованный индекс. Уровень зависит от размера строки, размера ключа и т. Д. Но 5% строк - это типичная обрезка.

Q4. Если в вашем приложении важнее всего было выполнение обоих этих запросов как можно быстрее, вы могли бы создать покрывающий индекс для них обоих:

create index IX_1 on employee (age) include (name, salary);
create index IX_2 on employee (salary) include (name, age);

Обратите внимание, что вам не нужно специально включать ключ кластера, поскольку некластеризованный индекс имеет его как указатель строки.

Q5. Это более важно для кластерных ключей, чем для некластерных ключей из-за уникальности. Однако реальная проблема заключается в том, является ли индекс выборочным или нет для ваших запросов. Представьте себе индекс по значению bit. Если распределение данных не будет очень перекосом, такой индекс вряд ли будет использоваться для чего-либо.

Подробнее об Уникификаторе. Представьте себе и неуникальный кластерный индекс по возрасту, и некластеризованный индекс по заработной плате. Допустим, у вас есть следующие строки:

age | salary | uniqifier
20  | 1000   | 1
20  | 2000   | 2

Тогда индекс заработной платы будет располагать такие строки

1000 -> 20, 1
2000 -> 20, 2

Допустим, вы выполнили запрос select * from employee where salary = 1000, а оптимизатор решил использовать индекс заработной платы. Затем он найдет пару (20, 1) из поиска по индексу, а затем найдет это значение в основных данных.

Я не знаю о внутреннем устройстве Microsoft SQL Server, но могу ответить для MySQL, который вы отметили для своего вопроса. Детали могут отличаться для других реализаций.

Q1. Правильно, дополнительное пространство для кластеризованного индекса не требуется.

Что произойдет, если вы отбросите кластерный индекс? Механизм MySQL InnoDB всегда использует первичный ключ (или первый непустой уникальный ключ) в качестве кластеризованного индекса. Если вы определяете таблицу без первичного ключа или отбрасываете первичный ключ существующей таблицы, InnoDB генерирует внутренний искусственный ключ для кластерного индекса. У этого внутреннего ключа нет логического столбца, на который можно было бы ссылаться.

Q2. Порядок строк, возвращаемых запросом, использующим некластеризованный индекс, не гарантируется. На практике это порядок доступа к строкам. Если вам нужно, чтобы строки возвращались в определенном порядке, вы должны использовать ORDER BY в своем запросе. Если оптимизатор может сделать вывод, что ваш желаемый порядок совпадает с порядком, в котором он будет обращаться к строкам (порядок индекса, будь то кластерный или некластеризованный индекс), то он может пропустить этап сортировки.

Q3. Некластеризованный индекс InnoDB не имеет указателя на соответствующую строку в конце индекса, он имеет значение первичного ключа. Таким образом, поиск в некластеризованном индексе на самом деле представляет собой два поиска в B-дереве, первый для поиска листа некластеризованного индекса, а затем второй поиск в кластеризованном индексе.

Это вдвое превышает стоимость поиска по одному B-дереву (более или менее), поэтому InnoDB имеет дополнительную функцию, называемую Адаптивный индекс хеширования. Часто используемые значения кэшируются в AHI, и в следующий раз, когда запрос выполняет поиск кэшированного значения, он может выполнить поиск O (1). В кэше AHI он находит указатель непосредственно на лист кластеризованного индекса, поэтому часть времени исключает оба поиска в B-дереве.

Насколько это повысит общую производительность, зависит от того, как часто вы ищете те же значения, которые искали ранее. По моему опыту, обычно соотношение хэш-поисков и не-хеш-поисков составляет примерно 1: 2.

Q4. Создайте индексы для обслуживания запросов, которые необходимо оптимизировать. Обычно кластеризованный индекс является первичным или уникальным ключом, и, по крайней мере, в случае InnoDB это необходимо. Ни age, ни salary вряд ли будут уникальными.

Вам может понравиться моя презентация Как правильно создавать индексы.

Q5. InnoDB автоматически создает индекс, когда вы объявляете уникальное ограничение. У вас не может быть ограничения без существующего для него индекса. Если бы у вас не было индекса, как бы движок обеспечил уникальность при вставке значения? Потребуется поискать повторяющееся значение в этом столбце по всей таблице. Индекс помогает сделать уникальные проверки намного более эффективными.