Что такое разреженность индексов и почему это плохо

Что проверяют

Понимаете ли вы физическое устройство индекса. За вопросом стоит мысль, что индекс это страницы на диске, которые бывают заполнены наполовину, и что стоимость доступа считается в прочитанных страницах, а не в строках таблицы.

Как отвечать

Сначала разведите два прочтения термина, потому что их смешивают.

Обычно имеют в виду низкую плотность страниц. Из-за удалений, обновлений ключа и вставок в случайные места страницы расщепляются и остаются полупустыми: вместо сотен ключей страница хранит десятки, дерево растёт, и на тот же запрос уходит кратно больше чтений. Индекс распухает, занимает место в памяти сервера, вытесняет оттуда полезные страницы, утяжеляет резервную копию.

Второе прочтение это индекс, построенный не по всем строкам, частичный или с условием. Это не болезнь, а приём: если значимых строк мало, такой индекс как раз экономит место и обслуживание.

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

Что делать: последовательные ключи вместо случайных, перестроение или реорганизация по фактическому показателю фрагментации, разумный коэффициент заполнения для таблиц с частыми вставками, удаление неиспользуемых и дублирующих индексов.

Где спотыкаются

Объясняют проблему пустыми значениями и низкой уникальностью колонки. Это другая тема: пустые значения индексу не мешают, а высокая уникальность как раз то, ради чего индекс и строят. Причина в том, как заполнены страницы.

Перестраивают индексы по расписанию как ритуал. Пока причина остаётся, картина возвращается к концу недели, а перестроение большой таблицы стоит блокировок и журнала. Сначала причина, потом обслуживание.

Не связывают проблему с кодом. Слой доступа к данным нередко сам создаёт индекс на каждый внешний ключ и раздаёт случайные идентификаторы в качестве ключа: вставка попадает в середину дерева, страницы расщепляются, и виноватой объявляют базу.