Методы оптимизации запросов в Microsoft SQL Server (фрагмент)
Costel Gabriel CORLĂŢAN, Marius Mihai LAZĂR,
Valentina LUCA, Octavian Teodor PETRICICĂ,
Автор перевода: Скиба Виктор Евгеньевич,
Университет экономических исследований, Бухарест, Румыния
gabi.corlatan@yahoo.com, lazar_mariusmihai@yahoo.com,
luca.valentina@ymail.com, octavian.petricica@ymail.com
Microsoft SQL Server является системой управления реляционными базами данных, имеющей MS-SQL и Transact-SQL в качестве основных структурированных языков программирования. Они базируются на реляционной алгебре, которая в основном используется для добавления данных, изменения, удаления и поиска, а также для доступа к данным управления. Вопросом получения ожидаемых результатов занимается система управления, целью которой является поиск лучшего плана выполнения. Данный процесс называется оптимизацией. Наиболее часто используемыми запросами являются те, в которых производится выборка данных с помощью команды SELECT. Мы должны принять во внимание, что не только запросы выборки нужно оптимизировать, но и другие объекты, такие как: индексы, представления или статистики.
Ключевые слова: SQL Server, запрос, индекс, представление, статистика, оптимизация.
1. Введение
Рассмотрим следующие проблемы, понижающие производительность в системе Microsoft SQL Server. После оптимизации аппаратного обеспечения, операционной системы, а затем настроек сервера SQL, основными факторами, влияющими на скорость исполнения запросов, являются:
• Отсутствующие индексы;
• Неточные статистические данные;
• Неоптимальные запросы;
• Тупики;
• Операции T-SQL, которые не располагают единым набор результатов (курсоры);
• Чрезмерное дробление указателей;
• Частое перекомпилирование результатов;
Это лишь некоторые из факторов, которые могут негативно влиять на производительность базы данных. Ниже мы обсудим каждый из вышеуказанных случаев и дадим более подробную информацию о них.
2. Отсутствующие индексы
Данный фактор влияет на производительность SQL Server’а. Когда отсутствует индексация таблицы, система должна выполнить проход по всей таблице, чтобы найти искомую строку. Это приводит к перегрузке оперативной памяти и процессора, тем самым значительно увеличивая время выполнения запроса. Более того, тупики могут быть созданы, когда, например, когда одновременно два сеанса запрашивают доступ к одной и той же таблице в один момент времени.
Рассмотрим таблицу с 10 000 строк и 4 столбцов, среди которых столбец с именем ID является автоинкрементным.
Таблица 1.1. Исполнение простого запроса, чтобы получить строку в таблице
Таблица 1.2. Исполнение запроса с объединением 2 таблиц
Таблица 1.3. Исполнение запроса с объединением между двумя таблицами
В таблице 1.1, запрос создается с помощью одной таблицы, с наличием и без кластерного индекса столбца, указанного в WHERE (Q1). Во второй таблице (Таблица 1.2), запрос имеет две таблицы, объединенные по колонке ID и условие WHERE (Q2). Согласно [1] и [3], SQL Server поддерживает следующие типы индексов:
• Кластерный индекс;
• Некластеризованный индекс;
• Уникальный индекс;
• Колоночный индекс;
• Индекс с включенными столбцами;
• Индекс по вычисляемым столбцам;
• Отфильтрованный индекс;
• Пространственный индекс;
• Индекс XML;
• Полнотекстовый индекс.
Согласно [2], основными методами оптимизации при помощи индексов являются следующие:
— Рекомендуется, чтобы созданные индексы использовались оптимизатором запросов. В основным, сгруппированные индексы лучше использовать для выборки интервалов данных. Сгруппированные индексы подходят для плотных ключей (повторяющихся значений). Поскольку строки физически не сортируются, запросы, которые работают с использованием этих значений, которые не являются уникальными, найдут их с минимальным количеством операций ввода / вывода. Разгруппировка индексов является более подходящей для уникальных выборок и для поиска отдельных строк;
— Разгруппированные индексы рекомендуется создавать с минимальной плотностью, насколько это возможно. Избирательность индекса может быть оценена по формуле селективности: количество уникальных ключей / количество строк. Разгруппировка индексов с селективностью менее 0, 1, не является эффективной и оптимизатор откажется от его использования. Разгруппировку индексов лучше всего использовать при поиске одной строки. Очевидно, что дублирующие ключи потребуют большего использования ресурсов, чтобы найти одну конкретную строку;
— Помимо повышения селективности индексов, вы должны отсортировать ключевые колонки. Необходимо в первую очередь располагать колонки с более высокой селективностью. По мере того как система будет проходить через дерево индексов, в поиске значения для данного ключа, использование более селективных ключевых столбцов приведет к тому, что потребуется меньше операций ввода/вывода, чтобы добраться до нужного «листка» с индексом. Это напрямую повлияет на скорость выполнения запроса;
— Когда индекс создается, транзакции и основные операции в базе данных принимаются во внимание. Индексы построены таким образом, чтобы оптимизатор мог использовать их для наиболее важных операций;
— Следует принять во внимание при создании индексов то, что они служат для наиболее часто объединяемых условий. Например, если вы часто объединяете две таблицы для выбора некоторых столбцов, вам можно построить индекс, который ускорит это объединение;
— Откажитесь от индексов, которые не используются. Если после анализа планов выполнения запросов, которые должны использовать индексы мы видим, что некоторые индексы не могут на самом деле быть использованы, они должны быть удалены;
— Рекомендуется создавать индексы на ссылки на внешние ключи. Внешние ключи предполагают индекс с уникальным ключом для указанной таблицы, но у нас нет никаких ограничений на таблицу, которая делает ссылку. Создание индекса в зависимой таблице может ускорить проверку целостности внешних ключей, которые являются результатом изменений к упомянутой таблице и может улучшить производительность объединения двух таблиц;
— Для того, чтобы обслуживать некоторые запросы и отчеты пользователей, мы рекомендуем создавать временные индексы. Например, отчет, который используется только один раз в год или один раз в семестр не требует постоянного индекса. Создание индекса перед запуском отчетов и отказ от него впоследствии целесообразно, если это делает выполнение быстрее, чем при запуске отчета без каких-либо индексов;
— Для разблокировки страницы для индекса, может быть использована системная процедура: sys.sp_indexoptions. Это принуждает сервер использовать блокирование на уровне строк и уровне таблицы. До тех пор, пока частая блокировка строки не привела к блокировке всей таблицы, данное решение повышает производительность в случае нескольких одновременных пользователей;
— Благодаря использованию нескольких индексов на одну таблицу оптимизатором, несколько индексов с одним ключом может привести к повышению общей производительности, чем индекс с ключом соединения. Это происходит потому, что оптимизатор может запрашивать индексы по отдельности и может объединить их, чтобы вернуть набор результатов. Это более гибкий метод, чем использование индекса с составным ключом, так как индексные ключи на одном столбце могут быть указаны в любой комбинации, которая не может быть оформлена в случае составных ключей. Столбцы, которые имеют составные ключи должны быть использованы в таком порядке, слева направо;
— Мы рекомендуем использовать приложение Index Tuning Wizard, которое предложит оптимизированные индексы для запросов. Это очень сложный инструмент, который может сканировать файлы трекинга, собранные SQL Server Profiler для того, чтобы предложить индексы, которые позволят повысить производительность.
3. Неточные статистические данные
Согласно [3], система управления базами данных SQL Server в основном зависит от затрат на базе оптимизации. Таким образом, точные статистические данные очень важны для эффективного использования индексов. Без них система не может точно оценить число строк, затронутых запросом. Количество данных, которые будут извлечены из одной или нескольких таблиц (в случае объединения) имеет важное значение при определении метода оптимизации выполнения запроса. Оптимизация запросов является менее эффективной, когда актуальные статистические данные не правильно обновляются.
Оптимизатор запросов SQL Server основан на стоимости, а это означает, что он определяет наилучший механизм доступа к данным, по типу запроса, при применении стратегии идентификации селективности. Каждая статистика имеет прилагающийся индекс, но он может быть создан вручную, на столбцах, которые не принадлежат к какому-либо индексу. Используя статистику, оптимизатор может сделать довольно разумные оценки относительно необходимого времени для системы, чтобы вернуть набор результатов.
3.1 Статистика индексированных столбцов
Полезность индекса полностью зависит от статистики индексированных столбцов. Без каких-либо статистических данных, SQL Server базируясь на основе затрат, оптимизатор запросов не может решить, что является наиболее эффективным способом использования индекса. Для того чтобы удовлетворить это требование, он автоматически создает статистику по ключу индекса каждый раз, когда создается индекс. Механизм извлечения данных, чтобы сохранить стоимость низкого уровня может использовать измененные данные. Например, если таблица имеет одну строку, которой соответствует некоторое значение, которое является уникальным, то имеет смысл использование некластеризованного индекса. Но если изменяются данные, когда добавляются строки с одинаковым значением столбца (дубликаты), использование индекса не имеет никакого смысла.
Согласно [5], SQL Server использует некий алгоритм, чтобы решить, когда необходимо выполнение системной процедуры, которая обновляет статистику, основанную на таких факторах, как количество обновлений и таблицу размеров:
— При вставке строки в пустую таблицу;
— При установке более 1000 строк в таблицу, которая уже имеет 1000 строк.
Автоматическое обновление статистики рекомендуется в подавляющем большинстве случаев, за исключением очень больших таблиц, где обновление статистики может привести к замедлению или блокирование системы. Обновление статистики производится с помощью системной процедуры sys.sp_updatestats в отношении индексированной таблицы или представления.
3.2 Статистика неиндексированных столбцов
Иногда есть возможность выполнения запроса на неиндексированном столбце. Даже в этой ситуации оптимизатор запросов может принять наилучшее решение, если он знает о распределении данных этих столбцов. В отличие от статистики индекса, SQL Server может создать статистику относительно неиндексированных столбцов. Информация о распределении данных или вероятности наличия некоторого значения в неиндексированном столбце может помочь оптимизатору установить оптимальную стратегию. SQL сервер выбирает стратегию оптимизации, даже если он не может использовать индекс для поиска значений. Автоматически создаются статистические данные о неиндексированном столбце. Когда система имеет некоторую информацию, это может помочь создать лучший план, как правило, когда столбцы используются в предикате (например: WHERE).
Литература
1. Adam Jorgensen, Jorge Segarra, Patrick Leblanc, Jose Chinchilla, Aaron Nelson, Microsoft SQL Server 2012 Bible, Ed. Johs Wiley & Sons, Inc., 2012, Indianapolis, Indiana - USA, ISBN: 978-1-118-10687-7
2. Ken Henderson, Transact-SQL (Titlul original: The Guru’s Guide to Transact-SQL), Ed. Teora, București, România, 2002, ISBN: 973-20-0612-9
3. Grant Fritchey, SQL Server 2012 Query Performance Tuning, Ed. Apress, USA, 2012, ISBN: 978-1 4302-4203-1
4. Leonard Lobel, Andrew Brust, Programming Microsoft SQL Server 2012, Ed. Microsoft Press, 2012, ISBN-13: 978-0735658226
5. Jason Strate, TedKrueger, Expert Performance Indexing for SQL Server 2012, Ed. Apress, USA, 2012, ISBN:978-1-4302-3741-9