Оптимизиция по индексам. Советы. Часть 1.
Этой записью начинаю цикл статей по оптимизации БД с помощью индексов. Если вы хотите найти точный ответ на вопрос «какие индексы я должен создать чтобы производительность была оптимальной?», то не ищите его(ответ) ни тут, ни где-либо ещё. Нет чёткого правила где и какие индексы нужно создавать. Есть лишь советы рекомендательного характера, а вариантов построения индексов для одной базы данных очень много. Где и какие индексы строить зависит от назначения таблиц, частоты запросов на выборку и частоты изменения этой таблицы, от частоты участия в выборке оперделённой группы полей и т.д. и т.п.
Советы:
1. Не стройте большого количества индексов на таблице, которая очень часто меняется(частые запросы delete, insert). При запросах delete и insert, в индексах происходят соответствующие изменения, а при большом количестве индексов , умноженном на количесвто вставок и удалений приводит к резкому возрастанию нагрузки на сервер и замедлению запросов вставки и удаления. К тому же, индекс сильно фрагментируется, что наоборот, снизит скорость выборки, а не ускорит её. В свою очередь, это приводит к совету под номером 2.
2. В качестве профилактики, для таблиц, учавствующих в частом изменении и содержащих индексы следует создать job (event в MySQL), который будет индексы перестраивать или дефрагментировать. Время для перестроения индексов выбирайте такое, чтобы к таблице было наименьшее количество обращений. Частоту перестроения выбирайте в соответствии с частотой изменения таблиц и fill factor индексов.
3. Как выбрать fill factor для индекса? SQL Server организовывает индексы как B-Tree, с одной корневой страницей, являющейся началом для построения индекса. Корневая страница может иметь указатели на несколько страниц следующего уровня индекса, и, каждая из этих страниц, может иметь указатели на множество страниц следующего уровня. Последний уровень индекса — это уровень листьев, который должен содержать все индексные ключевые значения в сортируемой последовательности.
Fill factor — показатель заполнености страниц индекса.
Чем он ниже, тем меньше страницы заполнены. Fill factor определяет заполненость страниц при создании индекса. Если вам придётся вставить новую строку в таблицу, в индекс должна добавиться соответствуюющая ссылка(если индекс не кластерный, или само значение, если индекс кластерный) на соответствующей странице индекса. Таким образом, если страница уже заполнена, то происходит разрыв страницы индекса, в соответствующее место вставляется новая страница, в которую переносится около половины записей первоначальной страницы и эти страницы связываются в цепочку. Так получаем фрагментацию индекса, кроме этого, вставка новой страницы и перенос в неё записей- задача ресурсоёмкая. Поэтому важно в таблицах, в которые часто происходит вставка делать fill factor ниже обычного, резервируя больше места для новых записей. Для вас это будет стоить лишь места на диске, так как страница индекса резервирует больше места под новые записи.
Продолжение следует…
