Стоит прочитать: обзор книги Маркуса Винанда «SQL Performance explained»

Андрей Мягков

TeamLead в Simtech Development

Очень многие веб-ресурсы (сайты, интернет-магазины, соц. сети и так далее) применяют для хранения данных реляционные базы данных (Oracle, MySQL, PostgreSQL и другие) с использованием языка SQL. Считаю, что книга обязательна к прочтению всем разработчикам (и не только), кто работает с языком SQL и с базами данных (БД). Книга станет путеводителем в мир SQL и анализа реляционных баз данных.

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

Например, самый простой SQL-запрос читается как предложение на английском языке:

			SELECT date_of_bith FROM employees WHERE last_name = 'WINAND'
		

Написание простых запросов к базе данных, в основном, не требует от разработчика глубоких знаний о её внутренней работе. И когда дело доходит до производительности, разработчик всегда «тыкает пальцем» в базу данных, указывая на медленное выполнение запроса и снимая с себя ответственность. Однако, опыт говорит об обратном, разработчик должен иметь представление о внутренней работе SQL и индексировании данных. Только в этом случае он сможет написать правильный запрос и получить быстрый ответ от базы данных. Фактически решение о необходимости индексирования должно ложиться на плечи разработчика. Этот этап является одним из важнейших этапов при работе с SQL, так как только при индексировании база данных будет правильно понимать «как получить требуемую информацию».

На страницах книги «SQL Performance explained» автор рассказывает о том, что нужно знать разработчику об индексах, а точнее о B-tree индексах, как с ними работать и применять на практике.

Итак, подробнее о книге

Структура книги хорошо адаптирована для разработчиков, большинство глав соответствуют определённой части оператора SQL:

• Anatomy of an Index.
• The Where Clause.
• Performance and Scalability.
• The Join Operation.
• Clustering Data.
• Sorting and Grouping.
• Partial Results.
• Insert, Delete and Update.
• Execution Plans.

Это поможет быстро ориентироваться по содержанию книги, когда вы будете применять знания на практике.

Глава Anatomy of an Index не содержит в себе никаких примеров SQL-запросов, но описывает структуру индекса. Это понимание обязательно для последующих глав. Здесь вы найдёте что такое Slow Index.

Глава The Where Clause является самой объёмной и содержит максимум описания оператора WHERE. Прочтение этой главы помогло мне отойти от простых выборок по одной колонке таблицы. Применяю сложные запросы с выборкой по нескольким колонкам из нескольких таблиц, используя диапазоны (для выборки дат) и другие условия. После изучения данной главы я смог оптимизировать текущий код нагруженного интернет-магазина и повысить производительность поиска товаров в три раза. Первоначально сложная фильтрация товаров выполнялась в несколько запросов и влекла за собой проблему с паджинацией на странице. После добавления индексов и фильтрации товаров по одному SQL-запросу, скорость выдачи увеличилась в три раза.

В главе Performance and Scalability описывается как правильно выполнить анализ производительности запросов, выявить узкие места в запросе и рассказывается о масштабировании базы без изменения железа сервера. Автор на примере выборки количества строк показывает какая разница между выполнением запроса в таблицах с разными объёмами данных и как на это влияет применение индексов.

Главы The Join Operation и Sorting and Grouping объясняют применение индексов для быстрого соединения таблиц, сортировки и группировки данных.

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

И тут на помощь приходят индексы, которые хранят данные предварительно отсортированным способом.

Если требуется получить больше данных, которые хранятся в нескольких связанных таблицах, операция JOIN позволит избежать множественных запросов к базе данных. Моя практика показывает, что менее ресурсоёмким является один большой запрос в несколько таблиц, в отличие от нескольких мелких (беря во внимание большие объёмы данных).

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

Глава Partial Results. В этой главе показано, как использовать конвейерный порядок для эффективного получения частичных результатов. Данный подход поможет повысить производительность запросов. Рассмотрим, например, кейс с социальной сетью и выдачей последних 10 сообщений пользователю. Применение обычной сортировки всех сообщений и ограничение по лимиту выдачи вызовет серьёзные проблемы с производительностью, так как объём таблицы с сообщениями может быть огромным.

В главе Insert, Delete and Update подробно объясняется, как влияет применение индексов на производительность запросов записи. Использование индексов должно быть продуманным, поскольку всё имеет свою цену.

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

Запрос INSERT — единственный запрос, который не получает никакой выгоды от использования индексов в базе, потому что не использует условие WHERE. Данное поведение показало мне «цену» большого количества индексов. Теперь я стараюсь не злоупотреблять индексами в таблицах.

Запрос DELETE очень похож на запрос выборки данных, но с дополнительным шагом для удаления строк. Шаг удаления аналогичен вставке новой строки. И тут нужно понимать, что есть шаг, который не «любит» индексы, но без них будет выполняться полное чтение таблицы для нахождения нужных строк. Таким образом, для запроса DELETE  индексы просто необходимы.

Запрос UPDATE содержит в себе как удаление старой записи, так и вставку новой. Но есть главное отличие: данный запрос затрагивает не все столбцы, соответственно, и не все индексы таблицы, что повышает производительность запроса по отношению к INSERT и DELETE

Глава Execution Plans поможет разработчику изучить функциональность запроса EXPLAIN и провести полный анализ запросов и данных. Пожалуй, это одна из важнейших глав в книге, которая направлена на приобретение навыка анализа и оптимизации запросов. Изучив данную главу, я смог выполнить анализ своего проекта (интернет-магазин), выявить узкие места при выборке данных, добавить необходимые индексы и получить значительный прирост в производительности.

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

Выводы

Самые важные главы книги «SQL Performance explained» для меня:

• Anatomy of an Index.
• Performance and Scalability.
• Execution Plans.

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

Узнал много интересной информации о Slow indexes (да, бывают и такие!), Explain запросах и как с ним работать. И самое главное: как строить, развивать и улучшать БД для хранения и получения большого объёма данных.

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