Топ контрибуторов
loading
loading
Знаете ли Вы, что

Если у вас есть уникальная статья и вы хотите, чтобы она стала достоянием общественности, вы можете разместить ее на Quizful.

Лента обновлений
ссылка Oct 22 23:24
Комментарий от alexcei88:
В вопросе переменная s даже не выводиться, а выводитьс...
ссылка Oct 22 17:46
Комментарий от AlexFurm:
Это UB, так можно вызывать только статические функции ч...
ссылка Oct 22 17:43
Комментарий от AlexFurm:
Любые битовые операции с signed это UB
ссылка Oct 21 20:30
Комментарий от yoori:
Любой вариант скомпилируется если компилировать не в конеч...
ссылка Oct 21 16:53
Добавлен вопрос в тест QA (Quality Assurance)
Статистика

Тестов: 153, вопросов: 8596. Пройдено: 443368 / 2177510.

SQL Server: использование индексов при настройке производительности

head tail Статья
категория
Базы данных
дата16.07.2009
авторIogli
голосов21

[Disclaimer: Данная статья была переведена в рамках "Конкурса на лучший перевод статьи" на сервисе Quizful. Ссылка на оригинал находится внизу страницы.]

Эффективное построение индексов  - один из лучших способов повышения производительности приложения, работающего с базой данных. Без  использования индексов,  SQL сервер подобен читателю, пытающемуся найти слово в книге, просматривая каждую страницу. Если в книге есть предметный указатель (индекс), читатель может выполнить  поиск необходимой информации гораздо быстрее.

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

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

Полезные индексы

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

Например, запросы к базе данных  Northwind, приведенные ниже, будут выполняться более эффективно  при построении индекса по столбцу UnitPrice.

Delete from Products Where UnitPrice=1
Select * from products Where UnitPrice between 14 AND 16

Поскольку элементы индекса хранятся отсортированными, индексирование также оказывается полезным при построении запроса с использованием инструкции Order by. Без индекса записи загружаются и сортируются во время выполнения запроса. Индекс по UnitPrice  позволит  при обработке следующего запроса просто просканировать индекс и извлечь строки по ссылке. Если требуется упорядочить строки по убыванию, достаточно будет просто просканировать индекс в обратном порядке.

Select * From Products order by UnitPrice ASC

Группировка записи с использованием инструкции Group by  также зачастую требует сортировки, таким образом, построение индекса по колонке UnitPrice будет полезным и при следующем запросе, подсчитывающим количество  единиц продукта по каждой определенной цене

Select count(*), UnitPrice From Products Group by UnitPrice

Индексы оказываются полезными для поддержания уникального значения столбца, так как СУБД может легко по индексу просмотреть содержится ли уже такое значение. По этой причине первичные ключи всегда проиндексированы.

Недостатки индексирования

Индексы ухудшают производительность системы во время изменений записи. В любое время при выполнении запроса на изменение данных в таблице индекс должен также изменяться.  Для выбора оптимального количества индексов необходимо тестирование базы данных и наблюдение за ее производительностью. Статичные системы, где базы данных используются в основном для извлечения данных, например для построения отчетов, позволяют содержать большее количество индексов для поддержки запросов только на чтение. Базы данных с большим количеством транзакций для изменения данных будут нуждаться в небольшом количестве индексов для обеспечения более высокой пропускной способности.

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

Построение оптимального индекса

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

Простой индекс

Простой индекс – это индекс, использующий значения одного поля таблицы. Использовать  простой индекс выгодно по двум причинам. Во-первых, работа базы данных сильно нагружает жесткий диск. Большие индексные ключи будут заставлять базу данных выполнять большее количество операций ввода-вывода, что ограничивает производительность.

Во-вторых, поскольку элементы индекса часто вовлечены в сравнения, меньшие индексы легче сравнивать. По этим двум причинам единственная колонка целочисленного типа является лучшим индексом, так как он мал и легок для сравнения. Строки символов, с другой стороны, требуют посимвольного сравнения  и внимания к обработке параметров.

Селективный индекс

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

Индекс с высоким процентом уникальных значений, также называют селективным индексом. Очевидно, уникальный индекс  обладает наибольшей селективностью, так как не содержит дублирующихся значений. Многие СУБД могут отслеживать статистику о каждом индексе и могут распознавать, как много неповторяющихся значений содержит каждый индекс. Данная статистика используется при генерации плана выполнения запроса.

Покрывающие индексы

Индексы состоят  из столбца данных, по которому собственно построен индекс и указателя на соответствующую строку. Это похоже  на предметный указатель (индекс) книги: он содержит только ключевые слова и ссылку на страницу, на которую вы можете обратиться за дополнительной информацией. Обычно СУБД будет следовать указателям к строке из индекса, чтобы собрать всю информацию необходимую для запроса. Тем не менее, если индекс содержит все столбцы необходимые в запросе,  информация может быть  извлечена без обращения к самой таблице.

Рассмотрим индекс по столбцу UnitPrice, который уже упоминался выше. СУБД  может использовать только элементы индекса для выполнения следующего запроса.

Select Count(*), UnitPrice From Products Group by UnitPrice

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

Кластерный индекс

Многие базы данных имеют один специальный индекс к таблице, где все данные из строки содержаться в индексе. В SQL сервере такой индекс называется кластерным (кластеризованным). Кластерный индекс можно сравнить  с телефонным справочником, потому как каждый элемент индекса содержит всю информацию, которая вам нужна и не содержит ссылок для получения дополнительных данных.

Есть общее правило - каждая нетривиальная таблица должна иметь кластерный индекс. Если возможно  создать только один индекс к таблице, сделайте его кластерным. В SQL сервере при создании первичного ключа будет автоматически создан кластерный индекс (если он еще не содержится), используя столбец с первичным ключом, как ключ для индексирования. Кластерный индекс наиболее эффективный индекс (если он используется, то покрывает весь запрос) и во многих СУБД такой индекс способствует эффективному управлению пространством, запрашиваемым для хранения таблиц, так как в противном случае (без построения кластерного индекса) строки таблиц хранятся в неупорядоченной структуре, которую называют кучей.

При выборе столбцов для кластерного индекса будьте осторожны. Если вы изменить запись и поменяете значение столбца в кластерном индексе, база данных будет вынуждена перестроить элементы индекса (чтобы держать их в отсортированном порядке). Помните, элементы индекса для кластерного индекса содержать все значения столбцов, таким образом, изменение значение столбца сопоставимо с выполнением инструкции Delete и последующей за ней инструкцией Insert, что очевидно вызовет проблемы с производительностью, если делать это часто. По этой причине, кластерные индексы часто состоят из столбцов первичного ключа и внешнего ключа. Значения ключей если меняются, то очень редко.

Заключение

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

----------
Оригинальный текст статьи: SQL Performance Tuning using Indexes

Если Вам понравилась статья, проголосуйте за нее

Голосов: 21  loading...
googperson   googman   googler   admin   WhiteSpirit   yohan   globus   sgauStudent   SunDrop   AlexeyTarasyuk   homak   serj   SeMMeN   latinacariba   vanezy   marinka_85   sintetix   lonely   squareroot   WhiteClick   BUBLIC