После перехода на SQL Server с Oracle многие вещи удивляют. К автоматическим транзакциям сложно привыкнуть — после обновления не нужно набирать commit (что приятно), но в случае ошибки не получится набрать откат (что просто ужасно).
Трудно привыкнуть к архитектуре, в которой журнал используется как для отката, так и для отката транзакций.
Трудно привыкнуть к ситуации «писатель блокирует читателей, читатель блокирует писателей», а как только вы к этому привыкнете, от этой привычки еще труднее избавиться.
И не последнее место в рейтинге сложностей занимает доминирование кластерных индексов.
По умолчанию первичным ключом таблицы является кластерный индекс, поэтому он есть почти у всех таблиц.
На самом деле этот зверь совсем не страшен и даже очень полезен.
Попробуем разобраться, зачем он нужен и как им пользоваться.
Файлы, страницы, RID
Данные любой таблицы физически хранятся в файле базы данных.Файл базы данных разделен на страницы (pages) — логические единицы хранения данных сервера.
Страница в MS SQL Server должна иметь размер 8 килобайт (8192 байт), из них под данные отводится 8060 байт. Для каждой строки можно указать ее физический адрес, так называемый Row ID (RID): в каком файле она находится, в каком порядке страницы этого файла, в каком месте на странице.
Таблице присваивается вся страница — одна страница может содержать данные только из одной таблицы.
Более того, если необходимо прочитать/записать строку, сервер читает/записывает всю страницу, так как это намного быстрее.
Как работает индекс B-дерева?
B-дерево означает сбалансированное дерево.Индекс содержит ровно одну корневую страницу, которая является точкой входа для поиска.
Корневая страница содержит ключевые значения и ссылки на страницы следующего уровня для заданных значений индекса.
При поиске по индексу находится последнее значение, не превышающее искомое, и происходит переход на соответствующую страницу.
На последнем, листовом уровне дерева перечислены все ключи, и для каждого из них имеется ссылка (закладка) на данные таблицы.
Естественным кандидатом на ссылку является RID, и он действительно используется как таковой в случае кучи.
На следующем рисунке буквы B обозначают ссылки на строки таблицы.
Когда вы добавляете запись в таблицу, вы также должны добавить ее в индекс.
Новая запись индекса, ссылающаяся на запись таблицы, вставляется на страницу конечного уровня.
В этом случае может оказаться, что на этой странице нет свободного места.
Затем:
- В индекс выделяется новая страница, также на конечном уровне.
- Половина записей с предыдущей страницы переносится на новую (чтобы при последовательном добавлении не столкнуться с ситуацией, когда придется выделять страницу заново для следующей строки).
Новая страница встроена в горизонтальные ссылки: вместо ссылок «Предыдущий Далее» настроены ссылки «Предыдущий Новый Следующий».
- Родительская страница содержит ссылку на новую страницу, снабженную соответствующим ключом.
В этом случае может переполниться и родительская страница — тогда процесс разделения данных повторится на более высоком уровне.
Если переполнение достигнет самого верха, корневая страница разделится на две части, появится новый корень, а высота дерева увеличится на 1.
При этом наличие индекса резко ускоряет поиск данных: вместо непрерывного сканирования можно проводить бинарный поиск, спускаясь по дереву.
Также, благодаря наличию горизонтальных ссылок на страницы одного уровня, вы можете очень быстро перемещаться по диапазону индексных ключей.
И мы плавно подходим к основным задачам выборки: поиску единичного значения и сканированию диапазонов.
Куча маленькая
Давайте рассмотрим некоторую модельную таблицу, организованную в виде кучи: в записях нет определенного порядка.RID, который запись получает в самом начале, остается с ней практически всегда.
В редких случаях записи в куче могут переместиться на другую страницу — это происходит, когда после обновления строка больше не помещается на занимаемое ею пространство.
Но в этом случае ссылка на новую локацию остаётся на том же месте — то есть, зная RID, полученный линией при добавлении, строку всегда можно найти.
Поэтому для индексов кучи лучшим выбором для ссылки на данные является RID. .
Допустим, в таблице 200 тысяч записей, и на каждой странице помещается от 48 до 52 записей.
Предположим, что таблица занимает 4000 страниц.
Допустим, мы хотим найти все записи, в которых поле [Город] имеет значение «Пермь».
Допустим также, что их всего 3, но мы об этом пока не знаем.
Серверу придется просканировать все 4000 страниц.
Даже если сервер найдет все 3 записи, ему все равно придется идти до конца — ведь нет гарантии, что нужных записей больше нет. Таким образом, для выполнения запроса потребуется 4000 операций чтения логических страниц.
Что, если у нас есть индекс, по которому можно осуществлять поиск с помощью двоичного поиска, — скажем, дерево высотой 3? Затем серверу потребуется прочитать 3 индексные страницы, чтобы найти адрес первой записи.
Адреса второй и третьей записи будут расположены рядом — либо на одной странице, либо на соседней: страницы индекса соединены ссылками по горизонтали.
То есть после максимум 4 чтений сервер наверняка знает RID всех трёх записей.
Если вам совсем не повезло, все 3 записи находятся на разных страницах.
Таким образом, если индекс есть, то после 7 логических чтений страницы обязательно будут найдены все 3 записи.
7 против 4000 впечатляет. Но будет так хорошо, когда пластинок будет мало.
А что, если это не «Пермь», а «Москва», и требуемых записей не 3, а 20 тысяч? Это не очень много, всего 10 процентов от общего количества записей.
Но картина быстро становится менее радужной.
За 3 чтения сервер найдет первую запись.
И тогда ему нужно будет прочитать 20 тысяч RID и прочитать страницу 20 тысяч раз, чтобы получить строку: мы помним, что сервер считывает данные только целыми страницами.
Вполне может оказаться, что нужные записи разбросаны по всей таблице, и для обеспечения 20 тысяч логических чтений вам потребуется прочитать большую часть страниц с диска.
Все равно хорошо, если не все.
Вместо 4 тысяч логических чтений получаем 20 тысяч.
Индекс очень хорошо работает при поиске небольшого количества значений, но плохо работает при пересечении больших диапазонов.
Оптимизатор запросов хорошо это понимает. Поэтому, если он ожидает, что поиск по индексу даст достаточно большой диапазон, он выберет полное сканирование таблицы вместо поиска по индексу.
Это, кстати, одно из редких мест, где Оптимизатор может ошибиться даже при правильной статистике.
Если на самом деле необходимые данные расположены очень компактно (например, 20 тысяч логических чтений — это 60 раз прочитать блок с диска и 19940 раз прочитать блок в кэше), то принудительное использование индекса даст выигрыш по памяти и скорости.
А как насчет диапазонов?
Проблема именно в том, что по окончании поиска по индексу сервер получает не данные, а только адрес, где они расположены.Серверу всё равно нужно зайти на этот адрес и забрать оттуда данные.
Было бы здорово, если бы данные были прямо в конце пути! Некоторые действительно лгут. Например, ключевые значения расположены в индексе — за ними нет необходимости идти.
Только для неключевых полей.
Что произойдет, если в индекс будут включены и неключевые поля? Ну, скажем, не все, а только те, которые нужны запросу на сканирование? Что произойдет в этом случае? индекс с дополнительными (включенными) столбцами .
Он уступает обычному индексу по размеру: его листовые страницы содержат не только ключи и адреса строк, но и часть данных.
Такой индекс работает так же хорошо при поиске одного значения, но гораздо лучше при сканировании диапазонов.
Если индекс покрывает запрос (то есть содержит все столбцы, перечисленные в запросе), то таблица вообще не нужна для выполнения запроса.
Возможность брать все необходимые данные из индекса, не обращаясь к закладкам, — огромный выигрыш.
Вернемся к нашему модельному примеру.
Предположим, что столбцы, необходимые для запроса, включены в индекс.
Для простоты предположим, что конечная страница индекса содержит ровно 50 записей (ключи, добавленные столбцы, ссылки на записи).
Тогда для сканирования 20 тысяч записей потребуется прочитать всего 400 страниц индекса — вместо 20 тысяч логических чтений для непокрывающего индекса.
400 против 20 тысяч – разница в 50 раз.
Не зря оптимизатор запросов любит предлагать включать в индекс определенные столбцы.
Возможно, стоит добавить в индекс Все столбцы? Тогда любой запрос будет покрыт индексом.
Более того, тогда в листьях даже не нужны RID, потому что такой индекс ни по каким данным не пойдет в таблицу; у него все под рукой.
В этом случае сам стол становится ненужным! И мы пришли к концепции кластерный индекс .
Оно структурировано как обычное B-дерево, но его листовые страницы содержат сами данные, а не ссылки на записи таблицы, и отдельной таблицы от него больше нет. Стол не может иметь кластеризованный индекс, он может быть кластерный индекс.
Любое сканирование ключей кластеризованного индекса будет лучше, чем полное сканирование таблицы.
Даже если вам нужно просканировать 97% всех записей.
Где подвох?
Первый понятно где.Кластеризованный индекс — это таблица, и таблица может быть только одна.
На сервере должна быть мастер-копия данных, и только из одного индекса он готов выкинуть все закладки и оставить только сами данные.
Если существует другой индекс, включающий все поля, он все равно будет содержать адреса строк.
Есть второй подвох.
Если у вас кластеризованный индекс, вы больше не можете использовать RID в качестве адреса строки.
Записи в кластерном индексе сортируются (физически — внутри страницы, логически — по горизонтальным связям между страницами).
Когда вы добавляете запись или изменяете ключевые поля, запись перемещается в правильное место — часто внутри страницы, но ее также можно переместить на другую страницу.
Другими словами, RID в кластерном индексе больше не идентифицирует запись однозначно.
.
Поэтому ключ кластерного индекса используется в качестве адреса строки, который однозначно идентифицирует ее.
То есть при поиске в некластеризованном индексе после обхода его дерева мы получаем не адрес данных, а ключ кластеризованного индекса.
Чтобы получить сами данные, нужно пройти и по дереву кластеризованного индекса.
Представим себе сканирование диапазона из 20 тысяч записей по некластеризованному индексу, построенному на кластеризованном.
Теперь вам нужно будет выполнить не 20 тысяч логических чтений страницы с использованием известного RID, а 20 тысяч поисков в кластерном индексе — и каждый поиск потребует 3, а то и больше логических чтений.
Что делать, если ключ кластерного индекса не уникален? Но этого не происходит. Он должен быть уникальным для сервера.
Если пользователь запрашивает неуникальный кластерный индекс, сервер присваивает каждому ключу 4-байтовое целое число, чтобы гарантировать уникальность ключа.
Это сделано прозрачно для пользователей: сервер не только не сообщает им точное значение номера, но и не раскрывает сам факт его наличия.
Уникальность ключа необходима именно для того, чтобы можно было однозначно идентифицировать записи — чтобы ключ кластеризованного индекса мог служить адресом строки.
Так делать или не делать?
Вооружившись теорией, мы можем описать рациональную процедуру построения кластерного индекса.Вам следует записать все индексы, которые нужны таблице, и выбрать из них кандидата на кластеризацию.
Вам не нужно создавать кластерный индекс только для того, чтобы он был у вас.
Если ключ индекса не предполагается сканировать, он не очень хороший кандидат для кластеризации (если другие индексы предполагается сканировать - тогда даже очень плохой кандидат).
Выбор неправильного кандидата для кластеризации приведет к снижению производительности, поскольку все остальные индексы будут работать хуже, чем в куче.
Предлагается следующий алгоритм выбора:
- Определите все индексы, используемые для поиска одного значения.
Если такой индекс только один, его необходимо кластеризовать.
Если их несколько, перейдите к следующему шагу.
- Добавьте к индексам из предыдущего шага все индексы, для которых ожидается сканирование диапазона.
Если их нет, кластерный индекс не нужен; несколько индексов в куче будут работать лучше.
Если есть, то каждый из них следует сделать покрывающим, добавив все нужные столбцы при сканировании запросов по этому индексу.
Если такой индекс только один, его следует кластеризовать.
Если их несколько, перейдите к следующему шагу.
- Лучшего кандидата для кластеризации среди всех покрывающих индексов определенно не существует. Вам следует кластеризовать один из этих индексов, принимая во внимание следующее:
- Длина ключа.
Ключ кластеризованного индекса представляет собой ссылку на строку и хранится на конечном уровне некластеризованного индекса.
Меньшая длина ключа означает меньше места для хранения и лучшую производительность.
- Степень покрытия.
Кластеризованный индекс содержит все поля «бесплатно», а покрывающий индекс с наибольшим набором полей является хорошим кандидатом на кластеризацию.
- Частота использования.
Поиск одного значения в покрывающем индексе — это самый быстрый поиск, а кластеризованный индекс охватывает любой запрос.
- Длина ключа.
P.S. Почему он единственный?
Когда я начал писать эту статью, я прекрасно понимал, почему таблица не может иметь более одного кластерного индекса.На середине написания я перестал это понимать и теперь уже не понимаю (хотя, что забавно, я еще могу это объяснить).
Сейчас у меня есть только гипотезы.
Кластеризованный индекс, во-первых, содержит все данные в листовых вершинах, а во-вторых, не содержит никаких ссылок на данные таблицы (потому что нет внешней по отношению к нему таблицы).
Ну а что мешает создать несколько индексов, оформленных вот так — содержащих все поля и не содержащих ссылок? Я не знаю.
Я могу только предложить.
Прежде всего, мы можем создать столько индексов, сколько захотим, которые будут включать все поля.
Это означает, что вся польза, которую нам сулит наличие нескольких кластеризованных индексов, относительно невелика: на листовом уровне дополнительных индексов не будет ссылок на данные, то есть мы сэкономим некоторое пространство.
Какие проблемы влечет за собой создание нескольких кластеризованных индексов?
- Ключи кластерного индекса — это ссылки на данные, которые хранятся в листьях некластеризованных индексов.
Если бы кластерных индексов могло быть несколько, среди них всё равно нужно было бы выбрать «основной», тот, ключами которого являются идентификаторы данных.
- Если вам нужно добавить в кластеризованный индекс неключевой столбец, то индекс придется полностью перестраивать, а некластеризованные индексы на нем менять вообще не нужно.
Если бы кластеризованных индексов было несколько, пришлось бы все перестраивать, и невозможно было бы заранее определить, сколько времени это займет.
- Возникнет множество ситуаций, чреватых ошибками.
Например, при наличии нескольких кластерных индексов, если пользователь удаляет главный индекс (тот, ключи которого служат ссылками на данные в некластеризованных индексах), серверу придется автоматически выбрать новый главный индекс.
Другими словами, создать несколько кластерных индексов технически возможно, но это дорого, неудобно и бесполезно.
Возможно, я не вижу каких-либо соображений, из-за которых невозможно создать несколько кластеризованных индексов.
Я был бы очень признателен, если бы кто-нибудь указал мне на эти соображения.
Удачи всем в кластеризации индексов! Теги: #t-sql #разработка #кластерные индексы #Microsoft SQL Server
-
Быстрое Подключение С Хостингом Sage 50
19 Oct, 24 -
Горизонт Инфинибокс F2230
19 Oct, 24 -
Ubuntu И Debian Linux Для Продвинутых
19 Oct, 24
