Насколько сложно выбрать правильные некластерные индексы?

Автор: Paul Randal, How hard is it to pick the right non-clustered indexes?

На собрании группы разработчиков .NET в Редмонде, и во время того, как Кимберли рассказывала о пропущенных и лишних индексах, возник следующий вопрос:

«Какой некластерный индекс лучше всего использовать для запроса с условием WHERE lastname = 'Randal' AND firstname = 'Paul' AND middleinitial = 'S'?»

Кимберли сказала, что для этого случая порядок ключей не имеет значения. Я подумал секунду, а затем возразил, сказав, что наиболее селективный столбец должен быть первым. Мы согласились обсудить это с группой в конце, но я подумал ещё немного и понял (и признался группе), что она права – мне следовало бы знать, что не стоит подвергать сомнению знания Кимберли об индексировании… :-)

Она права, потому что для чисто равенственного запроса с использованием AND для нескольких предикатов компонент Storage Engine сразу перейдёт к первой точно совпадающей записи в индексе (а затем продолжит сканирование для поиска дополнительных совпадений, если индекс не уникальный). Не имеет значения, в каком порядке определены ключи индекса, потому что Storage Engine ищет точное совпадение.

Когда я начал спорить, я думал о телефонном справочнике, который упорядочен по фамилии, имени, отчеству. Вы можете подумать, что телефонный справочник упорядочен так, потому что фамилия наиболее селективна. Неправильно. Это потому, что фамилия — это то, что большинство людей знают; просто так совпало, что она наиболее селективна из трёх вариантов. Большинство специалистов по SQL смогут найти Кимберли в телефонном справочнике, ища Tripp, Kimberly. Но что, если бы он был упорядочен по отчеству? У меня не было бы проблем найти Кимберли, но сколько из вас помнит, что её отчество начинается на L? Вероятно, несколько, поскольку мы оба используем свои отчества в публичных именах. Что, если бы он был упорядочен по полному отчеству? Опять же, для меня нет проблем, но кто ещё знает, что её отчество — Lynn?

Затем я начал думать о других запросах и о том, как они повлияют на выбор индекса для ответа на вопрос выше. Если бы я также хотел поддерживать запрос с условием WHERE lastname = 'Randal', то иметь крайний левый ключ индекса, отличный от фамилии, было бы не так хорошо. Если бы порядок ключей был firstname, middleinitial, lastname, то все различные значения фамилий были бы разбросаны по индексу, а не сгруппированы вместе. Индекс всё ещё мог бы использоваться для удовлетворения запроса, если бы он был самым дешёвым. Однако наличие фамилии в качестве ведущего ключа, вероятно, не очень хорошо подошло бы для запроса с условием WHERE firstname = 'Paul' – это говорит в пользу того, чтобы крайним левым ключом было имя.

Что мне выбрать? Вероятно, я не могу иметь оба в одном индексе, поэтому, возможно, мне пришлось бы создать ДВА некластерных индекса, чтобы поддерживать оба запроса. Ответ зависит от того, как часто используются различные запросы, и от баланса между выигрышем в производительности от некластерного индекса и падением производительности из-за необходимости его обслуживания во время операций DML.

Я слышу снова и снова, как люди добавляют некластерный индекс на каждый столбец таблицы, думая, что это поможет, и моё мнение таково, что это неправильно, потому что такие индексы могут обслуживать только запросы, где единственный предикат — это индексируемый столбец. Я обсудил это с Кимберли, и она добавила, что эти индексы также могут использоваться, если столбец выбран как наиболее селективный в запросе с несколькими предикатами и никакой другой индекс не имеет меньшей стоимости (обычно маловероятно). Даже то, что я считал простым случаем, имеет свои нюансы!

Так в чём же смысл этой статьи? Я хотел показать, что индексирование для одного очень простого запроса довольно прямолинейно, но как только количество различных запросов растёт, а предикаты запросов усложняются, индексирование становится более сложным. Вы действительно должны знать свою рабочую нагрузку и свои данные, чтобы знать, какие столбцы используются, в каких комбинациях и как часто, и тогда полезно знать, как оцениваются и используются индексы, чтобы вы могли принимать обоснованные решения о том, какие индексы создавать.

Это мысленное упражнение действительно показало мне, что я не знал, как много я не знаю об индексах — я точно знаю, как они работают на уровне Storage Engine, но не слишком много о том, как они используются Query Processor. Я получил новое уважение к опыту Кимберли в области индексирования. К счастью, на следующей неделе она проводит курс в Microsoft под названием Indexing For Performance — думаю, я посещу его :-)