Эффекты кластерного индекса на производительности DB
Я недавно занялся новым проектом программного обеспечения, который использует SQL Server 2000 для его хранения данных.
В рассмотрении проекта я обнаружил, что одна из основных таблиц использует кластерный индекс на своем первичном ключе, который состоит из четырех столбцов:
Sequence numeric(18, 0)
Date datetime
Client varchar(9)
Hash tinyint
Эта таблица испытывает много вставок в ходе нормального функционирования.
Теперь, я - разработчик C++, не, Администратор DB, но мое первое впечатление от этого дизайна таблицы был то, что это имеющее эти поля как кластерный индекс будет очень вредно для вставки производительности, так как данные должны были бы быть физически переупорядочены на каждом, вставляют.
Кроме того, я не могу действительно видеть преимущество для этого, так как нужно было бы запрашивать все эти поля часто для выравнивания по ширине кластерного индекса, правильно?
Так в основном мне нужны некоторые боеприпасы для того, когда я перехожу к власть имущим, чтобы убедить их, что дизайн таблицы должен быть изменен.
5 ответов
Кластеризованный индекс должен содержать столбцы, которые запрашивают больше всего, чтобы обеспечить наибольшую вероятность поиска или создания некластеризованного индекса, охватывающего все столбцы в запросе.
Первичный ключ и кластерный индекс не обязательно должны совпадать. Оба они являются ключами-кандидатами, и таблицы часто имеют более одного такого ключа.
Вы сказали
Кроме того, я не вижу в этом никакой пользы, так как нужно часто запрашивать все эти поля, чтобы оправдать кластерный индекс, верно?
Это неправда. Поиск можно выполнить, просто используя первый или два столбца кластеризованного индекса. Это может быть поиск диапазона, но это все же поиск. Вам не нужно указывать все столбцы, чтобы получить это преимущество. Но порядок столбцов имеет большое значение. Если вы преимущественно запрашиваете на клиенте, то столбец «Последовательность» - плохой выбор в качестве первого в кластеризованном индексе. Во втором столбце должен быть выбран элемент, который чаще всего запрашивается вместе с первым (а не сам по себе). Если вы обнаружите, что второй столбец запрашивается сам по себе почти так же часто, как и первый столбец, тогда поможет некластеризованный индекс.
Как уже говорили другие, очень важно максимально сократить количество столбцов / байтов в кластеризованном индексе.
Очень плохо, что Последовательность представляет собой случайное значение, а не увеличивается, но, возможно, с этим ничего не поделать.Ответ не для добавления столбца идентификаторов, если ваше приложение не может начать использовать его в качестве основного условия запроса для этой таблицы (маловероятно). Теперь, когда вы застряли с этим столбцом случайной последовательности (предполагая, что он является наиболее часто запрашиваемым), давайте посмотрим на другое из ваших утверждений:
использование этих полей в качестве кластеризованного индекса будет очень вредным для производительности вставки, поскольку данные должны быть физически переупорядочены для каждой вставки.
Это не совсем так.
Физическое расположение на диске - это не совсем то, о чем мы здесь говорим, но оно действительно играет роль с точки зрения фрагментации, которая влияет на производительность.
Строки на каждой странице размером 8k не упорядочены. Просто все строки на каждой странице меньше следующей и больше предыдущей. Проблема возникает, когда вы вставляете строку, а страница заполнена: вы получаете разделение страницы. Движок должен скопировать все строки после вставленной строки на новую страницу, а это может быть дорогостоящим. Со случайным ключом вы получите много разделений страниц. Вы можете решить эту проблему, используя более низкий коэффициент заполнения при перестроении индекса. Вам придется поиграть с ним, чтобы получить правильное число, но 70% или 60% могут служить вам лучше, чем 90%.
Я считаю, что использование datetime в качестве второго столбца CI может быть полезным, поскольку вы все равно будете иметь дело со страницами, которые необходимо разделить между двумя разными значениями Sequence, но это не так плохо, как если бы второй столбец в CI также был случайным, так как вы гарантированно разделяли страницы при каждой вставке, где с возрастающим значением вам может повезти, если строка может быть добавлена на страницу, потому что следующий порядковый номер начинается на следующей странице.
Сокращение типов данных и количества всех столбцов в таблице, а также ее некластеризованных индексов также может повысить производительность, поскольку больше строк на страницу = меньшее количество операций чтения на запрос. Особенно, если движок вынужден сканировать таблицу. Перемещение группы редко запрашиваемых столбцов в отдельную таблицу 1-1 может творить чудеса с некоторыми вашими запросами.
Наконец, есть некоторые улучшения дизайна, которые также могут помочь (на мой взгляд):
- Измените столбец Sequence на bigint, чтобы сохранить байт для каждой строки (8 байтов вместо 9 для числовых значений).
- Используйте таблицу поиска для клиента с 4-байтовым столбцом идентификатора int вместо varchar (9). Это экономит 5 байтов на строку. Если возможно, используйте smallint (от -32768 до 32767), который составляет 2 байта, что дает еще большую экономию - 7 байтов на строку.
Резюме: CI должен начинаться с наиболее запрашиваемого столбца. Удалите все столбцы из CI, если сможете. Сократите столбцы (байты) настолько, насколько сможете. Используйте более низкий коэффициент заполнения, чтобы уменьшить разбиение страницы, вызванное столбцом случайной последовательности (если он должен оставаться первым из-за того, что его запрашивают чаще всего).
Да, и приступайте к онлайн-дефрагментации.Если таблицу нельзя изменить, по крайней мере, ее можно часто реорганизовывать, чтобы поддерживать ее в наилучшем виде. Не пренебрегайте статистикой, чтобы движок мог выбрать подходящие планы выполнения.
ОБНОВЛЕНИЕ
Еще одна стратегия, которую следует учитывать, заключается в том, можно ли преобразовать составной ключ, используемый в таблице, в int, и создать таблицу поиска значений. Скажем, некоторая комбинация менее чем всех 4 столбцов повторяется в более чем 100 строках, например, Последовательность + Клиент + Хэш, но только с различными значениями Даты.Тогда вставка в отдельную таблицу SequenceClientHash со столбцом идентификаторов может иметь смысл, потому что тогда вы можете найти искусственный ключ один раз и использовать его снова и снова. Это также заставит ваш CI добавлять новые строки только на последней странице (yay) и существенно уменьшить размер CI, который повторяется во всех некластеризованных индексах (yippee). Но это имело бы смысл только в определенных узких шаблонах использования.
Теперь marc_s предложил просто добавить дополнительный столбец идентификаторов int в качестве кластеризованного индекса. Возможно, это может помочь, заставив все некластеризованные индексы получать больше строк на страницу, но все зависит от того, где именно вы хотите, чтобы производительность была, потому что это гарантирует, что каждый отдельный запрос в таблице должен будет использовать закладку. поиск, и вы никогда не сможете получить поиск по таблице.
По поводу «тонны разбиения страниц и плохой фрагментации индекса»: как я уже сказал, это можно улучшить с помощью более низкого коэффициента заполнения. Кроме того, частая реорганизация индекса в режиме онлайн (не то же самое, что перестроение) может помочь уменьшить эффект от этого.
В конечном итоге все сводится к конкретной системе и ее уникальной схеме доступа к данным в сочетании с решениями о том, какие части вы хотите оптимизировать. Для некоторых систем наличие более медленной вставки неплохо, если выборка всегда выполняется быстро. Для других более важно иметь последовательное, но немного более медленное время выбора, чем немного более быстрое, но непоследовательное время выбора. Для других данные на самом деле не считываются, пока они не будут отправлены в хранилище данных, поэтому вставки должны быть как можно быстрее.К этому добавляется тот факт, что производительность связана не только с временем ожидания пользователя или даже временем ответа на запрос, но и с ресурсами сервера, особенно в случае массового параллелизма, так что общая пропускная способность (скажем, в ответах клиента на единицу времени) имеет значение больше, чем любой другой фактор.
Есть несколько вещей, которые вы неправильно понимаете в том, как SQL создает и использует индексы.
Кластеризованные индексы не обязательно физически упорядочены на диске по кластеризованному индексу , по крайней мере, не в реальном времени. Это просто логическое упорядочивание.
Я бы не ожидал значительного падения производительности на основе этой структуры, а удаление кластеризованного индекса до того, как вы действительно выявили проблему производительности, связанную с этим индексом, явно преждевременная оптимизация.
Кроме того, индекс может быть полезен (особенно с несколькими полями в нем) даже для поиска, который не сортирует и не запрашивает все столбцы, включенные в него.
Очевидно, что для создания многокомпонентного кластеризованного индекса, как и для любого другого индекса, должно быть обоснование, поэтому имеет смысл попросить об этом, если вы считаете, что он был добавлен необдуманно.
Итог: Не оптимизируйте индексы для производительности вставки до тех пор, пока вы действительно не обнаружите проблему производительности при вставке. Обычно это того не стоит.
Если у вас есть только один кластеризованный индекс на таблице, это может быть не так уж плохо. Однако кластерный индекс также используется для поиска реальной страницы данных при любом попадании в некластерный индекс - поэтому кластерный индекс (все его столбцы) также является частью каждого некластерного индекса, который может быть у вас в таблице.
Поэтому если у вас в таблице несколько некластеризованных индексов, то вы определенно а) тратите много места (и не только на диске - в оперативной памяти вашего сервера!), и б) ваша производительность будет плохой.
Хороший кластеризованный индекс должен быть:
- маленьким (лучший вариант: 4-байтовый INT) - ваш довольно плох: до 28 байт на запись
- уникальным
- стабильным (никогда не меняется)
- постоянно увеличивающимся
Я готов поспорить, что ваша текущая установка нарушает как минимум два, если не больше, из этих требований. Несоблюдение этих рекомендаций приведет к пустой трате места и, как вы справедливо говорите, к фрагментации страниц и индексов, а также к расщеплению страниц (необходимость "переставлять" данные, когда вставка происходит где-то в середине кластеризованного индекса).
Честно говоря: просто добавьте суррогатный ID INT IDENTITY(1,1) к вашей таблице и сделайте его первичным кластеризованным ключом - вы должны увидеть довольно хороший прирост производительности, просто от этого, если у вас много операций INSERT (и UPDATE)!
Дополнительная справочная информация о том, что делает хороший ключ кластеризации и что в них важно, здесь:
Кластерные индексы (CI) лучше всего работают с постоянно увеличивающимися, узкими, редко меняющимися значениями. Вы хотите, чтобы ваш CI охватывал столбцы, которые наиболее часто используются в запросах с операторами> =, <= или BETWEEN.
Я не знаю, как обычно попадают в ваши данные. Чаще всего вы увидите CI в столбце IDENTITY или другом узком столбце (потому что этот столбец также будет возвращен «прикрепленным» ко всем некластеризованным индексам, и мы не хотим, чтобы тонна данных добавлялась к каждой выборке если это не нужно). Возможно, данные запрашиваются чаще всего в установленный срок, и это может быть хорошим выбором, но все четыре столбца скорее всего неверны (я подчеркиваю, вероятно, потому что я не знаю настройки ; это может не иметь ничего плохого). Здесь есть несколько указателей: http://msdn.microsoft.com/en-us/library/aa933131%28SQL.80%29.aspx
Я полностью согласен с последним абзацем Эрика:
«В конечном итоге все сводится к точной системе и ее уникальной схеме доступа к данным в сочетании с решениями о том, какие части вы хотите оптимизировать .. . »
Это основная вещь, которую я заставляю людей усвоить: универсального решения не существует.
Вы должны знать свои данные и действия, предпринятые против них. Вы должны знать, насколько часто бывают различные типы действий, их влияние и ожидаемое время выполнения (вам не нужно жестко настраивать некоторые редко выполняемые запросы и влиять на все остальное, если конечный пользователь соглашается, что время выполнения запроса не так важно - допустим, подождать несколько минут для отчета один раз в неделю - это нормально). Конечно, как сказал Эрик
, «производительность - это не только время ожидания пользователя или время ответа на запрос, но и ресурсы сервера»
. Если такой запрос влияет на общую производительность сервера, его следует рассматривать как серьезного кандидата. для оптимизации, даже если время выполнения хорошее. Я видел несколько очень быстрых запросов, которые использовали огромное количество ЦП на многопроцессорных серверах, в то время как несколько более медленное решение было несравненно «легче» с точки зрения использования ресурсов. В этом случае я почти всегда выбираю более медленный.
Как только вы узнаете, какова ваша цель, вы можете решить, сколько индексов вам нужно и какой из них следует кластеризовать. Уникальные ограничения, отфильтрованные индексы, индексы с включенными столбцами - довольно мощные инструменты для настройки. Выбор правильных столбцов важен, но зачастую выбор правильного порядка столбцов даже важнее. И, наконец, не убивайте производительность вставки / обновления тоннами индексов, если таблица часто изменяется.