Суперсила индексов для оптимизации SQL-запросов

Для чего эти оптимизации применяются?

Большинство из них применяется для:

• исправления ошибок в запросах;
• обеспечения повторного использования представлений без фактического выполнения логики представления базой данных.

В первом случае, можно было бы заявить: «Ну и что, просто возьми, и исправь этот дурацкий SQL-запрос». Но пусть первым бросит в меня камень тот, кому не доводилось ошибаться.

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

Используемые базы данных

В этой статье мы будет сравнивать 10 SQL-оптимизаций в пяти наиболее широко используемых СУБД (согласно рейтингу баз данных):

• MySQL 8.0.2;
• DB2 LUW 10.5.

Другой рейтинг почти вторит ему.

Как обычно, в этой статье я буду выполнять запросы к базе данных Sakila.

Вот список этих десяти разновидностей оптимизаций:

• транзитивное замыкание;
• невозможные предикаты и ненужные обращения к таблицам;
• устранение JOIN;
• устранение «бессмысленных» предикатов;
• проекции в подзапросах EXISTS;
• cлияние предикатов;
• доказуемо пустые множества;
• oграничения CHECK;
• ненужные рефлексивные соединения;
• Pushdown предикатов

Сегодня мы обсудим пп. 1-3, во второй части — 4 и 5, а в части 3 – 6-10.

Транзитивное замыкание

Начнем с чего-нибудь попроще: транзитивного замыкания. Это тривиальное понятие, применимое ко множеству математических операций, например, оператору равенства. Его можно сформулировать в этом случае следующим образом: если A = B и B = C, то A = C.

Несложно, правда? Но это влечет некоторые интересные последствия для оптимизаторов SQL.
Рассмотрим пример. Извлечем все фильмы с ACTOR_ID = 1:

Взглянем теперь на план выполнения этого запроса в случае СУБД Oracle:

Особенно тут интересен раздел предикатов. Предикат ACTOR_ID = 1, вследствие транзитивного замыкания применяется как к таблице ACTOR, так и таблице FILM_ACTOR. Если:

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

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE first_name = ‘PENELOPE’
AND last_name = ‘GUINESS’

Как вы можете видеть, оценка числа строк таблицы FILM_ACTOR завышена, а оценка для вложенных циклов (NESTED LOOP) занижена. Вот пару интересных значений:

SELECT count(*) FROM film_actor WHERE actor_id = 1;
SELECT avg(c) FROM (
SELECT count(*) c FROM film_actor GROUP BY actor_id
);

Отсюда и получаются оценки. Если база данных знает, что речь идет о ACTOR_ID = 1, то может собрать статистику по количеству фильмов для этого конкретного актёра. Если же не знает (поскольку стандартный механизм сбора статистики не соотносит FIRST_NAME/LAST_NAME с ACTOR_ID), то мы получим среднее число фильмов для всех актеров. Простая, несущественная ошибка в данном конкретном случае, но в сложном запросе она может распространяться дальше, накапливаться и приводить дальше в запросе (выше в плане) к неправильному выбору JOIN.

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

DB2

Кстати, если вам нравятся крутые планы выполнения вроде этого, воспользуйтесь сценарием Маркуса Винанда (Markus Winand).

MySQL

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

ID SELECT TYPE TABLE TYPE REF ROWS
——————————————
1 SIMPLE a const const 1
1 SIMPLE fa ref const 19

Но тот факт, что в столбце REF два раза указано const показывает, что в обеих таблицах идет поиск по константному значению. В то же время, план запроса с FIRST_NAME / LAST_NAME выглядит следующим образом:

ID SELECT TYPE TABLE TYPE REF ROWS
————————————————
1 SIMPLE a ref const 3
1 SIMPLE fa ref a.actor_id 27

И, как вы можете видеть, в REF теперь указана ссылка на столбец из предиката JOIN. Оценка кардинальности практически такая же, как в Oracle.

Так что да, MySQL тоже поддерживает транзитивное замыкание.

PostgreSQL

Все наши базы данных поддерживают транзитивное замыкание.

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

Невозможные предикаты и ненужные обращения к таблицам

Эта совсем дурацкая оптимизация, но почему бы и нет? Если пользователи пишут невозможные предикаты, то зачем их вообще выполнять? Вот несколько примеров:

— «Очевидный»
SELECT * FROM actor WHERE 1 = 0
— «Хитрый»
SELECT * FROM actor WHERE NULL = NULL

Первый запрос, очевидно, никогда не вернет никаких результатов, но то же самое утверждение справедливо и относительно второго. Ведь хотя NULL IS NULL всегда TRUE, результат вычисления NULL = NULL равен NULL, что, согласно трёхзначной логике, эквивалентно FALSE.

Это не требует особых пояснений, так что перейдем сразу к выяснению, какие из баз данных выполняют такую оптимизацию.

Как вы можете видеть, обращение к таблице ACTOR полностью исключено из плана. В нём присутствует только операция GENROW, генерирующая ноль строк. Идеально.

ID SELECT TYPE TABLE EXTRAS
——————————————
1 SIMPLE Impossible WHERE

На этот раз, MySQL был столь любезен, что сообщил нам о невозможном предложении WHERE. Спасибо! Это сильно облегчает анализ, особенно по сравнению с другими базами данных.

Oracle

QUERY PLAN
——————————————-
Result (cost=0.00..0.00 rows=0 width=228)
One-Time Filter: false

Уже лучше. Никакого надоедливого обращения к таблице ACTOR и маленький аккуратный предикат FALSE.

SQL Server?

SQL Server называет это «константным просмотром», то есть просмотром, при котором ничего не происходит – аналогично DB2.

Все наши базы данных умеют исключать невозможные предикаты:

Устранение JOIN

SELECT first_name, last_name
FROM customer c
JOIN address a ON c.address_id = a.address_id

База данных может выполнить следующее:

SELECT title
FROM film f
JOIN language l ON f.original_language_id = l.language_id

то JOIN все равно можно устранить, но придется добавить предикат NOT NULL, вот так:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

SELECT first_name, last_name
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id

База данных, опять же, может выполнить следующее:

SELECT DISTINCT first_name, last_name
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id

Все эти примеры были подробно изучены в предыдущей статье, так что я не буду повторяться, а лишь подытожу всё, что могут устранять различные базы данных:

К сожалению, не все базы данных могут устранять все виды соединений. DB2 и SQL Server тут – безусловные лидеры!

Продолжение следует

Введение

В данном руководстве мы изложили некоторые рекомендации по оптимизации запросов SQL.

Оптимизация структуры таблиц SQL Server

Разбивайте сложные таблицы на несколько, помните, чем больше в вашей таблице столбцов и тяжелых типов (nvarchar(max)), тем тяжелее по ней проход. Если некоторые данные не всегда используются в select с ней, выносите большие столбцы в отдельные таблицы и связывайте через FK

Выберите правильные типы данных. Всегда выбирайте самый маленький тип для данных, которые Вы должны хранить в столбце.

Если текстовые данные в столбце имеют разную длину, используйте тип  данных NVARCHAR вместо NCHAR.

Не используйте NVARCHAR или NCHAR типы данных, если Вы не должны сохранить 16-разрядные символьные данные (UNICODE). Они требуют в два раза больше места, чем CHAR и VARCHAR, что повышает расходы времени на ввод-вывод (но если у вас кириллица, то без NVARCHAR не обойтись).

Если Вы должны хранить большие строки данных и их длина меньше чем 8,000 символов, используют тип данных NVARCHAR вместо TEXT. Текстовые поля требуют больше ресурсов для обработки и снижают производительность.

Любое поле, в котором должны быть только отличные от нуля значения, нужно объявлять как NOT NULL

Для любого поля, которое должно содержать уникальные значения, стоит указать модификатор UNIQUE

Хранение изображений в БД нежелательно. Храните в таблице путь к файлу (локальный путь или URL), а сам файл помещайте в файловую систему сервера.

Оптимизация запросов SELECT

Если ваше приложение позволяет пользователям выполнять запросы, но вы не можете отсечь лишние тысячи возвращаемых строк, используйте оператор TOP внутри инструкции SELECT.

Не возвращайте клиенту большее количество столбцов или строк, чем действительно необходимо (Не используй * в Select).

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

Корректно используйте JOIN

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

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

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

Если Вы должны регулярно объединять четыре или более таблиц, для получения recordset’а, попробуйте денормализовать таблицы так, чтобы число таблиц, участвующих в объединении уменьшилось. Часто, при добавлении одного или двух столбцов из одной таблицы в другую, объединения могут быть уменьшены.

Если вам нужно постоянно получать некоторые данные на лету (например, расчет бонусов клиента), попробуйте это поле хранить в отдельной колонке и обновлять по необходимости. В этом случае не нужно будет делать лишние join и подзапросы.

Тип JOIN используйте только тот, который вернет вам НЕОБХОДИМЫЕ данные без каких-либо дублей или лишней информации (или совсем отказаться от join). Т.е. не нужно получать всех пользователей таким образом:

В этом случае вы получите много повторов пользователей

Сортировка в SELECT

Самой ресурсоемкой сортировкой является сортировка строк.

При объявлении полей всегда следует использовать размер, который нужен, и не выделять лишние байты про запас.

Если сортируете по дате создания, то попробуйте сортировать просто по id (первичный ключ с identity(1,1)).

Группирование в SELECT

Используйте как можно меньше колонок для группировки.

По возможности лучше использовать Where вместо Having, т.к. это уменьшает количество строк для группировки на ранней стадии.

Если требуется группирование, но без использования агрегатных функций (COUNT(), MIN(), MAX и т.д.), разумно использовать DISTINCT.

Не используйте множественные вложенные группировки через подзапросы.

Ограничить использование DISTINCT

Эта команда исключает повторяющиеся строки в результате. Команда требует повышенного времени обработки. Лучше всего комбинировать с LIMIT.

Ограничить использование SELECT для постоянно изменяющихся таблиц.

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

Оптимизация WHERE в запросе SELECT

Если where состоит из условий, объединенных AND,  они должны располагаться в порядке возрастания вероятности истинности данного условия. Чем быстрее мы получим false  в одном из условий — тем меньше условий будет обработано и тем быстрее выполняется запрос.

Если where состоит из условий, объединенных OR,  они должны располагаться в порядке уменьшения вероятности истинности данного условия. Чем быстрее мы получим true  в одном из условий — тем меньше условий будет обработано и тем быстрее выполняется запрос.

LIKE. Эту операцию следует использовать только при крайней необходимости, потому что лучше и быстрее использовать поиск, основанный на full-text индексах.

Советы по оптимизации хранимых процедур и SQL пакетов

Для обработки данных используйте хранимые SQL процедуры.

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

Всегда включайте в ваши хранимые процедуры инструкцию «SET NOCOUNT ON». Если Вы не включите эту инструкцию, тогда каждый раз при выполнении запроса SQL сервер отправит ответ клиенту, указывающему число строк, на которые воздействует запрос.

Избегайте использования курсоров

По возможности выбирайте быстрый forward-only курсор

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

Когда Вы закончили использовать курсор, как можно раньше не только ЗАКРОЙТЕ (CLOSE) его, но и ОСВОБОДИТЕ (DEALLOCATE).

Используйте триггеры c осторожностью

Триггеры — это усложнение логики работы приложения, неявное неожиданное выполнение дополнительных действий.

Триггеры усложняют интерфейс хранимых процедур. Поместите все необходимые проверки и действия в рамки хранимых процедур.

Временные таблицы для больших таблиц, табличные переменные — для малых (меньше 1000)

При определении временной таблицы имеет смысл проверить ее на существование:

IF OBJECT_ID(‘tempdb..#eventIDs’) IS NOT NULL begin
DROP TABLE #eventIDs
end
CREATE TABLE #eventIDs ( id int primary key,instanceID int )

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

Как уменьшить вероятность дедлоков на базе

Дедлок — это взаимная блокировка 2 выполняющихся пакетов sql. Это самым негативным образом сказывается на быстродействии запросов.

Чтобы избежать deadlocks, пытайтесь разрабатывать ваше приложение с учетом следующих рекомендаций:

• Всегда получайте доступ к объектам в одном и том же порядке.
• Старайтесь делать транзакции короткими и заключайте их в один пакет (batch)
• Старайтесь использовать максимально низкий уровень изоляции для пользовательского соединения, которое работает с транзакцией.

Работа с индексами SQL Server

• Первичный ключ не всегда должен быть кластерным индексом. Если Вы создаете первичный ключ, тогда SQL сервер автоматически делает первичный ключ кластерным индексом.
• Кластерные индексы хороши для запросов, которые ищут запись с уникальным значением (типа номера служащего) и когда Вы должны вернуть большую часть данных из записи или всю запись. Так происходит потому, что запрос покрывается индексом.
• Кластерные индексы хороши для запросов, которые обращаются к столбцам с ограниченным числом значений, например столбцы, содержащие данные о странах или штатах. Но если данные столбца мало отличаются, например, значения типа «да/нет», «мужчина/женщина», то такие столбцы вообще не должны индексироваться.
• Кластерные индексы хороши для запросов, которые используют операторы GROUP BY или JOIN.
• Кластерные индексы хороши для запросов, которые возвращают много записей, потому что данные находятся в индексе, и нет необходимости искать их где-то еще.
• Избегайте помещать кластерный индекс в столбцы, в которых содержатся постоянно возрастающие величины, например, даты, подверженные частым вставкам в таблицу (INSERT). Так как данные в кластерном индексе должны быть отсортированы, кластерный индекс на инкрементирующемся столбце вынуждает новые данные быть вставленным в ту же самую страницу в таблице, что создает «горячую зону в таблице» и приводит к большому объему дискового ввода-вывода. Постарайтесь найти другой столбец, который мог бы стать кластерным индексом.

Советы по выбору некластерных индексов

• Некластерные индексы лучше подходят для запросов, которые возвращают немного записей (включая только одну запись) и где индекс имеет хорошую селективность (более чем 95 %).
• Если столбец в таблице не содержит по крайней мере 95% уникальных значений, тогда очень вероятно, что Оптимизатор Запроса SQL сервера не будет использовать некластерный индекс, основанный на этом столбце. Поэтому добавляйте некластерные индексы к столбцам, которые имеют хотя бы 95% уникальных записей. Например, столбец с «Да» или «Нет» не имеет 95% уникальных записей.
• Постарайтесь сделать ваши индексы как можно меньшего размера (особенно для многостолбцовых индексов). Это уменьшает размер индекса и уменьшает число чтений, необходимых, чтобы прочитать индекс, что увеличивает производительность.
• Если возможно, создавайте индексы на столбцах, которые имеют целочисленные значения вместо символов. Целочисленные значения имеют меньше потерь производительности, чем символьные значения.
• Если ваше приложение будет выполнять один и тот же запрос много раз на той же самой таблице, рассмотрите создание покрывающего индекса на таблице. Покрывающий индекс включает все столбцы, упомянутые в запросе. Из-за этого индекс содержит все данные, которые Вы ищете, и SQL сервер не должен искать фактические данные в таблице, что сокращает логический и/или физический ввод — вывод. С другой стороны, если индекс становится слишком большим (слишком много столбцов), это может увеличить объем ввода — вывода и ухудшить производительность.
• Индекс полезен для запроса только в том случае, если оператор WHERE запроса соответствует столбцу (столбцам), которые являются крайними левыми в индексе. Так, если Вы создаете составной индекс, типа «City, State», тогда запрос » WHERE City = ‘Хьюстон’ » будет использовать индекс, но запрос » WHERE State = ‘TX’ » не будет использовать индекс.
• Любая операция над полем в предикате поиска, которое лежит под индексом, сводит на нет его использование. where isnull(field,’’) = ‘’ здесь индекс не используется, where field = ‘’ and field is not null — здесь используется

Бывает ли слишком много индексов?

Проблема с лишними индексами состоит в том, что SQL сервер должен изменять их при любых изменениях таблицы (INSERT, UPDATE, DELETE).

Лучшим решением ставить сомнительный индекс или нет, будет подождать и собрать статистику по работе индексов.

Лучшие кандидаты на установку индекса

• Это поля, по которым идет Join
• Поля связи, участвующие в подзапросах
• Поля, по которым идет фильтрация в where
• Поля, по которым выполняется сортировка.

Советы по использованию временных таблиц и табличных переменных

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

• Временная таблица храниться физически в tempdb, табличная переменная хранится в памяти SQL
• SQL может сам решить сохранить табличную переменную физически, если там будет много данных, это потеря ресурсов
• Временная таблица подходит для большого объема данных (полноценная выборка), табличная переменная —  для малого объема данных (справочники или набор ID для чего-то)
• Временная таблица доступна из любой процедуры SQL, табличная переменная только в рамках запроса. Не забывайте очищать временные таблицы после их использования

Если вы SQL-разработчик или администратор MS SQL Server, и вы хотели бы разрабатывать веб-решения на SQL, то веб-платформа Falcon Space — это то, что вам нужно.

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

Вводная статья по Falcon Space для SQL специалиста

Еще несколько советов по оптимизации SQL запросов (Февраль 2023)

• Обновлять только то, что необходимо в БД обновить.
• При больших загрузках отключайте ограничения и индексы
• В колонках с низкой избирательностью избегайте индексов (напр на булевы поля).
• Когда в следующий раз будете загружать данные в таблицу, подумайте о том, сколько информации будет запрашиваться, и отсортируйте так, чтобы индексы могли быстро сканировать диапазоны.
• Операции insert или обновления за 1 присест, а не много одиночных update
• Избегайте коррелирующих подзапросов (когда подзапрос зависит от родительского). Для больших таблиц.
• Используйте EXISTS() вместо Count() для проверки условия существованяи хотя бы одной записи.

Здесь приведем некоторые советы по оптимизации запросов SQL Server при анализе плана выполнения запроса.

1. Обычные виновники предупреждений в планах запросов — это операторы, сбрасывающие данные в tempdb, неявные преобразования, которые SQL Server должен производить для выполнения сравнения (что потенциально препятствует использованию индекса), и SQL Server сообщает вам, что он превысил/недооценил количество памяти, которое необходимо использовать для выполнения вашего запроса.

2. Общая мудрость гласит, что поиск (seek) — это хорошо для производительности, поскольку он представляет собой прямой доступ SQL Server к требуемым строкам данных, в то время как сканирование (scan) — это плохо, поскольку он предполагает последовательное чтение индекса для извлечения большого числа строк, приводя к более медленной обработке.

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

5. Index spools. Спулы бывают разных типов, но большинство из них можно сформулировать как операторы, которые сохраняют промежуточную таблицу результатов в tempdb. SQL Server часто использует спул для обработки сложных запросов, преобразуя данные в рабочую таблицу в базе tempdb для использования её данных в последующих операциях. Побочным эффектом здесь является необходимость записи данных на диск в tempdb.

6. Соединения в плане запросов. Merge join оптимально. Nested loops нормально для небольших данных. Hash match — присмотреться (возможно надо упорядочить сначала набор или индекс иметь на поля упорядочивания, по которым идет связь).

7. Если я вижу по плану запроса, что данные напрямую поступают из некластеризованных индексов, то доволен, что данные извлекаются эффективно, поскольку возвращается только ограниченное число столбцов, и надеюсь, что они вернутся в предпочтительном порядке (оттого, что я создаю свои индексы довольно узкими).Если все, что я вижу, это Index scan (сканирование индекса), тогда все прекрасно, но следует убедиться, что:a. Я не вижу table scan (сканирование таблицы) — на худой конец, это может быть сканирование кластерного индекса.b. Я не использую без необходимости SELECT * в своем запросе — зачем считывать все эти лишние данные в память или мешать использованию более узкого индекса, если в этом нет необходимости.c. SQL Server не сканирует весь индекс, чтобы вернуть только ограниченное подмножество строк.Затем я ищу в плане любые другие операторы, обычно вызывающие проблемы: сортировки, спулы, хэш-соединения и т.д.Наконец, я быстро просматриваю план в поисках желтых восклицательных знаков на любых операторах в плане. Эти символы отмечают действия, о которых SQL Server считает, что должен нас предупредить.

8. Стараемся не использовать поля allow null. Избегаем по возможности left join

9. Памятка по плану запроса — https://yadi.sk/i/spoZqlZSJ75eKQ

Avoid using functions in WHERE clause of SQL statement

Having the wild card at the beginning means it has to do a full-table scan looking at each and every row even if the column name is indexed

If a column is indexed, having the wildcard at the end of the string, like ‘abc% would be much more faster as it would be able to go directly to the records which start with ‘abc’ and ignore everything else. So, we must avoid using wildcard characters at the start of a LIKE pattern to enhance the performance of a SQL query

Use Temp table instead of Subqueries

Apart from improving readability over subqueries, temp table also allows you to

• Re-use query results multiple times which in turn reduces table scans
• Using temp table allows us the flexibility of using index or DISTKEY and SORTKEYS and should especially be used to optimize JOINs

Avoid using DISTINCT and ORDER BY unless you really need to

Using DISTINCT and ORDER BY is quite a costly operation because the entire query output needs to be sorted and checked for duplicates. These commands are always processed towards the end of the query before LIMIT and the more rows are returned by the query, the more resources are being used in these operations. UseGROUP BY instead of DISTINCT if you have to get the distinct values.

Create Indexes properly to speed up the query

INDEX is a performance optimization technique that speeds up your query. Indexes are used more particularly when your queries include WHERE clause. Without indexes, one has to go through all the records doing a full-table scan in order to retrieve the desired results which is an extremely slow process. When we use indexes, the query will quickly find out the data without reading each one of them just by eliminating half of the data in each traversal like a binary search.

Syntax for creating Index

SQL Server provides two types of indexes: Clustered index and non-clustered index.

Clustered index: A clustered index defines the order in which data is physically stored in a table. There could be only one clustered index per table and the primary key constraint automatically creates a clustered index on that particular column.

Non-clustered index: A non-clustered index doesn’t sort the data physically inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. It’s like a textbook where the index page is created separately at the beginning of the book. It is created to improve the performance of frequently used queries not covered by clustered index. We could have more than one non-clustered index per table. Non-clustered indexes are slower than clustered indexes, mostly becuase when a query is issued against a column on which the non-clustered index is created, the database will first go to the index and look for the address of the corresponding row in the table. It will then go to that row address and fetch other column values.

MySQL documentation on Index: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

You can go through this article to know more about SQL indexing: https://use-the-index-luke.com/

Distkey & Sortkey

If you are working with Amazon Redshift which doesn’t use indexes that a transactional database such as MySQL or PostgreSQL would use, you need to use DISTKEY and SORTKEY for optimizing query performance.

You can optimize Redshift by choosing a Distribution Key for each table that determines how data is distributed amongst nodes, and select the Sort Key, which determines how data is stored on each node. To put it simply, data should be distributed(DISTKEY) by how you JOIN tables and should be sorted(SORTKEY) by what you use in WHERE statements.

Amazon Redshift supports three kinds of table distribution styles:

• Even distribution: This is the default distribution style of a table. In Even Distribution, data of a table is distributed evenly across all slices. We generally use this distribution if the table is highly de-normalized and no JOIN is required
• Key distribution: The data is distributed across slices based on the column that you define as the key. So all the entries with the same value of that column would end up in the same slice. It’s recommended to use this distribution if you are using JOINs in your query(define distkey as the column that you are using to JOIN it with other tables)
• All distribution: Under this distribution, a copy of the table is maintained across all the computing nodes which results in more space utilization. We generally use this distribution when we have small but frequently joined tables such as lookup tables. For example, a table containing telephone ISD codes against the country name.

Avoid using SELECT *, use only the columns that you need

This is the most common way to optimize a query while using Redshift which is a columnar database. In Columnar databses, data is stored and retrieved in columns as compared to Row oriented databases where data is stored and retrieved one row at a time . As a result, selecting data from more columns that is actually needed slows down your query

Restrict your data in WHERE clauses whenever possible

It is imperative to always restrict your data as much as possible inWHERE clause, as this information will be processed prior to the table scan. It is really important especially when the table is huge. Normally this entails at least filtering on a date range and region limit at a minimum

Go through the Execution Plan

Performance of your SQL queries depends on multiple factors such as the kind of information you want to retrieve, indexes available and the database model. The best way to keep a track of what’s happening with your query(without running the query) is to look at the execution plan. You can add EXPLAIN at the begining of your query to go through the execution plan, which will help you to experiment and find the best solution for your statements.

You can check rows to see the number of rows output by the plan if executed to completion. You can also see the cost — higher numbers mean longer run time. You should use this more as a reference than as an absolute measure. To clarify, you can modify your query and then run EXPLAIN again to see if the cost is reduced.

You can go through this article to know more about Execution plan: https://vertabelo.com/blog/understanding-execution-plans-in-postgresql/

Thanks for reading and feel free to comment & like the article if you found it useful!

Вы любите SQL и хотите улучшить свои навыки выполнения SQL-запросов? Вы знаете, что индексация — отличный инструмент для оптимизации запросов, но при этом не уверены, что она из себя представляет, с какой целью и как используется?

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

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

Рис.1. Таблица ‘product’с 12 миллионами строк

Рис. 2. 4 строки таблицы ‘product’

Начнем с простого запроса:

SELECT COUNT(*)FROM productWHERE category = ‘electronics’;

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

Можно ли быстрее? Конечно. А Как? С помощью индексации.

Понятие индексации

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

Вы просто откроете страницу индексов, найдете “линейную регрессию” и сразу перейдете на нужную страницу.

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

Создание индексов

Давайте создадим индекс для таблицы product и включим в него ‘category’:

Query:CREATE INDEX product_category_indexON product (category);

В отличии от обычного запроса выполнение вышеуказанного займет гораздо больше времени. БД просканирует 12 миллионов строк и с нуля создаст индекс category. Допустим, на это уйдет 4 минуты.

Теперь же задействуем индекс и протестируем выполнение самого первого нашего запроса:

Как видно, в этот раз он будет выполняться намного быстрее и, вероятно, займет 400 миллисекунд.

Даже расширенные запросы, содержащие в качестве условия не только category, станут более эффективными благодаря созданному индексу. Рассмотрим пример:

SELECT COUNT(*)FROM productWHERE category = ‘electronics’AND product_subcategory = ‘headphone’;

Выполнение этого запроса займет меньше времени, чем обычно — около 600 миллисекунд. С помощью индекса БД быстро найдет все товары ‘electronics’ и из небольшого списка записей выберет ‘headphones’.

А сейчас изменим порядок условий в пункте WHERE.

SELECT COUNT(*)FROM productWHERE product_subcategory = ‘headphone’AND category = ‘electronics’;

Несмотря на упоминание product_subcategory до category, БД тем не менее сначала выберет столбец с индексом, а именно category, после чего просканирует строки в поиске указанной product_subcategory из числа имеющихся записей.

Какова же внутренняя суть процесса?

Рис. 3. Возможные планы выполнения запросов для оптимизатора

БД анализирует все возможные пути выполнения запроса, выбирая самый оптимальный из них.

Теперь пора познакомиться с некоторыми терминами БД. Каждый возможный путь называется планом выполнения запроса. По сути, это последовательность операций для получения результата SQL-запроса в реляционной системе управления базами данных (СУРБД).

А компонент СУРБД, определяющий наиболее эффективный способ выполнения запроса с учетом анализа всех возможных планов, называется оптимизатором запросов.

Индексация по нескольким столбцам

Теперь рассмотрим индексацию по нескольким столбцам.

Индекс можно создать более чем для одного столбца.

CREATE INDEX product_category_product_subcategory_indexON product (category, product_subcategory);

Итак, теперь у нас есть индекс для обоих столбцов: category и product_subcategory. Обратите внимание, что здесь важна очередность — сначала сортируются данные в category, после чего в product_subcategory.

Данный тип индекса еще больше ускорит выполнение запроса, предположительно до 60 миллисекунд.

Более того, БД может включать более одного индекса.

В каких случаях следует применять индексацию?

Индексы ускоряют работу БД, а по мере ее разрастания их эффективность становится очевиднее.

При этом важно помнить о том, что:

• Индексам необходимо место для хранения.
• При добавлении данных в БД сначала обновляется исходная таблица, а затем все ее индексы.

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

Типы индексов

Здесь мы кратко рассмотрим 2 типа индексов БД для лучшего понимания темы:

1. Кластеризованные индексы

2. Декластеризованные индексы

Кластеризованные индексы

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

Продемонстрируем вышесказанное на простом примере:

Рис. 4. Кластеризованный индекс

Для таблицы product будет автоматически создан кластеризованный индекс product_pkey, сформированный вокруг первичного ключа product_id.

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

Интересно, как же именно это происходит?

Индексы используют оптимальный метод поиска, известный как двоичный поиск.

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

Следующая таблица отражает соотношение записей данных и максимальное число поисков:

Рис.5. Сложность двоичного поиска

Аналогичным образом для нашего датасета с 12 миллионами строк понадобится не 12 миллионов, а всего лишь 24 поиска — и всё благодаря двоичному поиску. Думаю, теперь вы осознаете супер силу индексов.

Некластеризованный индекс

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

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

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

Как именно это происходит?

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

• записи столбца, для которых был создан индекс;
• адреса соответствующей строки (в основной таблице), в которой находится запись столбца.

Это наглядно отображено в таблице слева на рис.6:

Рис.6. Некластеризованный индекс

Давайте рассмотрим этот запрос более подробно:

CREATE INDEX product_category_indexON product (category);SELECT product_name, category, priceFROM productWHERE category = ‘electronics’;

БД совершает 3 шага:

• Во-первых, она переходит по некластеризованному индексу (product_category_index) и методом двоичного поиска находит искомую запись столбца (category = ‘electronics’).
• Во-вторых, в основной таблице она ищет адреса соответствующей строки, в которой находится запись столбца.
• В-третьих, она переходит к этой строке в основной таблице и выбирает другие значения столбца в соответствии с требованиями запроса (product_name, price).

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

Заключение

Итак, мы выяснили, что такое индексы и какую роль они играют в оптимизации выполнения SQL-запросов, особенно при работе с огромными датасетами.

В завершении приведу вам высказывание Тайгера Вудса, лучшего гольфиста всех времен:

“Независимо от того, насколько хорошо вы играете, вы всегда можете стать лучше, и это вдохновляет”.

Читайте нас в Telegram, VK и Яндекс.Дзен

Description

Fixing and preventing performance problems is critical to the success of any application. We will use a variety of tools and best practices to provide a set of techniques that can be used to analyze and speed up any performance problem!

This is one of my personal favorite areas of research and discussion as it is inherently satisfying. Taking a performance nightmare and tuning it into something fast and sleek feels great and will undoubtedly make others happy.

Defining Optimization

What is “optimal”? The answer to this will also determine when we are done with a problem and can move onto the next one. Often, a query can be sped up through many different means, each of which has an associated time and resource cost.

We usually cannot spend the resources needed to make a script run as fast as possible, nor should we want to. For the sake of simplicity, we will define “optimal” as the point at which a query performs acceptably and will continue to do so for a reasonable amount of time in the future. This is as much as a business definition as it is a technical definition. With infinite money, time, and computing resources, anything is possible, but we do not have the luxury of unlimited resources, and therefore must define what “done” is whenever we chase any performance problem.

This provides us with several useful checkpoints that will force us to re-evaluate our progress as we optimize:

• The query now performs adequately.
• The resources needed to optimize further are very expensive.
• We have reached a point of diminishing returns for any further optimization.
• A completely different solution is discovered that renders this unneeded.

Over-optimization sounds good, but in the context of resource management is generally wasteful. A giant (but unnecessary) covering index will cost us computing resources whenever we write to a table for the rest of eternity (a long time). A project to rewrite code that was already acceptable might cost days or weeks of development and QA time. Trying to further tweak an already good query may net a gain of 3%, but take a week of sweating to get there.

Our goal is to solve a problem and not over-solve it.

What Does the Query Do?

Question #1 that we must always answer is: What is the purpose of a query?

• What is its purpose?
• What should the result set look like?
• What sort of code, report, or UI is generating the query?

It is first-nature for us to want to dive in with a sword in hand and slay the dragon as quickly as humanly possible. We have a trace running, execution plans in hand, and a pile of IO and timing statistics collected before realizing that we have no idea what we are doing 🙂

Step #1 is to step back and understand the query. Some helpful questions that can aid in optimization:

• How large is the result set? Should we brace ourselves for a million rows returned, or just a few?
• Are there any parameters that have limited values? Will a given parameter always have the same value, or are there other limitations on values that can simplify our work by eliminating avenues of research.
• How often is the query executed? Something that occurs once a day will be treated very differently than one that is run every second.
• Are there any invalid or unusual input values that are indicative of an application problem? Is one input set to NULL, but never should be NULL? Are any other inputs set to values that make no sense, are contradictory, or otherwise go against the use-case of the query?
• Are there any obvious logical, syntactical, or optimization problems staring us in the face? Do we see any immediate performance bombs that will always perform poorly, regardless of parameter values or other variables? More on these later when we discuss optimization techniques.
• What is acceptable query performance? How fast must the query be for its consumers to be happy? If server performance is poor, how much do we need to decrease resource consumption for it to be acceptable? Lastly, what is the current performance of the query? This will provide us with a baseline so we know how much improvement is needed.

By stopping and asking these questions prior to optimizing a query, we avoid the uncomfortable situation in which we spend hours collecting data about a query only to not fully understand how to use it. In many ways, query optimization and database design force us to ask many of the same questions.

Tools

To keep things simple, we’ll use only a handful of tools in this article:

Execution Plans

An execution plan provides a graphical representation of how the query optimizer chose to execute a query:

The execution plan shows us which tables were accessed, how they were accessed, how they were joined together, and any other operations that occurred along the way. Included are query costs, which are estimates of the overall expense of any query component. A treasure trove of data is also included, such as row size, CPU cost, I/O cost, and details on which indexes were utilized.

In general, what we are looking for are scenarios in which large numbers of rows are being processed by any given operation within the execution plan. Once we have found a high cost component, we can zoom in on what the cause is and how to resolve it.

STATISTICS IO

SET STATISTICS IO ON;

Once on, we will see additional data included in the Messages pane:

Logical reads tell us how many reads were made from the buffer cache. This is the number that we will refer to whenever we talk about how many reads a query is responsible for, or how much IO it is causing.

Physical reads tell us how much data was read from a storage device as it was not yet present in memory. This can be a useful indication of buffer cache/memory capacity problems if data is very frequently being read from storage devices, rather than memory.

In general, IO will be the primary cause of latency and bottlenecks when analyzing slow queries. The unit of measurement of STATISTICS IO = 1 read = a single 8kb page = 8192 bytes.

Query Duration

Typically, the #1 reason we will research a slow query is because someone has complained and told us that it is too slow. The time it takes a query to execute is going to often be the smoking gun that leads us to a performance problem in need of a solution.

For our work here, we will measure duration manually using the timer found in the lower-right hand corner of SSMS:

Our Eyes

Many performance problems are the result of common query patterns that we will become familiar with below. This pattern recognition allows us to short-circuit a great deal of research when we see something that is clearly poorly written.

As we optimize more and more queries, quickly identifying these indicators becomes more second-nature and we’ll get the pleasure of being able to fix a problem quickly, without the need for very time-consuming research.

In addition to common query mistakes, we will also look out for any business logic hints that may tell us if there is an application problem, parameter issue, or some other flaw in how the query was generated that may require involvement from others aside from us.