Как оптимизировать медленные SQL запросы?

Kate · 14 Янв 2024

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

Как выявить и отладить такие проблемы? В этой статье будет показано решение наиболее распространённых проблем с производительностью БД, вызванных неправильной индексацией. Примеры будут приведены для Postgres, MySQL и SQLite.

Как БД выполняет запрос?

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

Как только SQL-код запроса разобран, оптимизатор запросов("query optimizer") просматривает этот запрос и предлагает последовательность операций, которые необходимо выполнить для получение запрашиваемого результата. Для простых запросов это может быть всего одна операция. Для сложных, включающих в себя JOIN, GROUP BY и другие подобные конструкции, требуются несколько операций, каждой из которых необходим результат выполнения предыдущей.

Существуют множество разных примитивных операций, но если рассматривать те, которые извлекают данные, то их можно разделить на 2 группы: операции, которые ищут нужные данные путем последовательного чтения, иногда их называют "последовательные сканы"("sequential scans") или "табличные сканы"("table scans"); и операции, которые для поиска данных используют индекс, часто это называют "поиск по индексу"("index searches") или "индексные сканы"("index scans").

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

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

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

В поисках медленных запросов

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

Как обнаружить медленные запросы? Существуют несколько стратегий. Самая простая - прислушаться к вашим пользователям. На днях коллега сказал мне, что некоторые HTTP запросы, которые он делал к Flask API, которое я поддерживаю, выполнялись около 20 секунд. Получив точные данные об этих HTTP запросах, я смог найти SQL запрос, который нужно было оптимизировать.

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

Как найти и исправить последовательные сканы

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

Хорошие новости заключаются в том, что вы можете "спросить" вашу БД, какие операции нужны для выполнения запроса. Это реализовано с помощью SQL слова EXPLAIN, которое является частью спецификации SQL.

Рассмотрим три БД, с которыми я обычно работаю, - MySQL, Postgres, SQLite, и все три способны "объяснить" запросы для отладки проблем с производительностью.

Пример схемы БД

Далее я покажу вам небольшую схему, которая будет использована для создания таблиц в Postgres, MySQL и SQLite. Также я покажу вам как анализировать запросы в каждой СУБД.

Поскольку я обычно работаю с Python, я покажу вам схему, которую я буду использовать в соответствии с требованиями фреймворка SQLAlchemy. Даже если вы не работаете с Python, эти классы должны быть очень просты для понимания.

Пусть будет БД, которая хранит информацию о статьях блога вместе с авторами. Вот её определение:

class Article(db.Model):
id = db.Column(db.Integer, primary_key=True)
slug = db.Column(db.String(256), index=True, unique=True, nullable=False)
title = db.Column(db.String(256))
author_id = db.Column(db.Integer, db.ForeignKey('author.id'))

class Author(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(256), index=True, unique=True, nullable=False)
Обратите внимание, что в этой схеме столбецArticle.slug индексируется, а Article.title - нет. Это важно, потому что это позволит увидеть как поиск по индексируемым и неиндексируемым полям обрабатывается в разных СУБД.

Postgres

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

Используя таблицу article, получим первые 5 статей в алфавитном порядке с помощью следующего запроса:

SELECT * FROM article ORDER BY article.slug LIMIT 5;
Давайте используем EXPLAIN на этом запросе, просто добавив его в качестве префикса:

test=# EXPLAIN SELECT * FROM article ORDER BY article.slug LIMIT 5;

QUERY PLAN
------------------------------------------------------------------------------------------
Limit (cost=0.27..1.26 rows=5 width=139)
-> Index Scan using ix_article_slug on article (cost=0.27..43.38 rows=217 width=139)
(2 rows)
Этот запрос требует выполнения двух операций. Операция LIMITне очень интересна, но можно увидеть, что она зависит от поиска по индексу столбца slug, который используется для извлечения первых пяти записей в алфавитном порядке. Это действительно хорошо; индексное сканирование довольно эффективно по сравнение с последовательным.

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

test=# EXPLAIN SELECT slug FROM article ORDER BY article.slug LIMIT 5;

QUERY PLAN
----------------------------------------------------------------------------------------------
Limit (cost=0.27..1.26 rows=5 width=49)
-> Index Only Scan using ix_article_slug on article (cost=0.27..43.38 rows=217 width=49)
(2 rows)
Что произойдёт, если мы выполним похожий запрос, но будем использовать столбец title, который не является индексируемым. Вот результат:

test=# EXPLAIN SELECT * FROM article ORDER BY article.title LIMIT 5;

QUERY PLAN
-----------------------------------------------------------------------
Limit (cost=10.77..10.79 rows=5 width=139)
-> Sort (cost=10.77..11.32 rows=217 width=139)
Sort Key: title
-> Seq Scan on article (cost=0.00..7.17 rows=217 width=139)
(4 rows)
Как можно увидеть, теперь в запросе появилась третья операция - сортировка. В предыдущем случае сортировка по алфавиту могла быть получена непосредственно из индекса, но title не индексируется, поэтому сортировка в этом запросе должна происходить в памяти. Если читать этот запрос снизу вверх, то сначала в результате последовательного сканирования все записи попадают в память, затем строки сортируются, и, наконец, возвращаются первые пять строк.

Этот запрос будет выполняться всё медленнее и медленнее по мере роста числа записей в таблице article. В примере выше можно увидеть, что в таблице 217 строк. Представьте, насколько больше усилий потребовалось бы, если бы этот запрос выполнялся с 10 000 записей, все они должны были бы считываться в память для сортировки, чтобы вернуть первые пять и отбросить остальные.

Оптимизация JOIN

Давайте посмотрим, что произойдёт с запросом, использующие JOIN. Следующий запрос возвращает первые пять статей и их авторов, отсортированным в алфавитном порядке по имени автора:

test=# EXPLAIN SELECT article.*, author.name
test=# FROM article, author
test=# WHERE article.author_id = author.id ORDER BY author.name LIMIT 5;

QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=0.14..8.36 rows=5 width=153)
-> Nested Loop (cost=0.14..356.66 rows=217 width=153)
Join Filter: (article.author_id = author.id)
-> Index Scan using ix_author_name on author (cost=0.14..13.69 rows=103 width=18)
-> Materialize (cost=0.00..8.25 rows=217 width=139)
-> Seq Scan on article (cost=0.00..7.17 rows=217 width=139)
(6 rows)
Если вы предпочитаете альтернативный способ определения этого запроса с помощью JOIN, будьте уверены, что он даст такой же результат:

test=# EXPLAIN SELECT article.*, author.name
test=# FROM article JOIN author ON article.author_id = author.id
test=# ORDER BY author.name LIMIT 5;

QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=0.14..8.36 rows=5 width=153)
-> Nested Loop (cost=0.14..356.66 rows=217 width=153)
Join Filter: (article.author_id = author.id)
-> Index Scan using ix_author_name on author (cost=0.14..13.69 rows=103 width=18)
-> Materialize (cost=0.00..8.25 rows=217 width=139)
-> Seq Scan on article (cost=0.00..7.17 rows=217 width=139)
(6 rows)
Плохая новость - запросы начинаются с последовательного скана таблицы article. Результаты сканирования "материализуются" в виде таблицы в памяти. Затем операция JOIN объединяет эти данные вместе с индексным сканом столбца name таблицы author. В данном случае JOIN оценивается с помощью алгоритма "вложенного цикла"("nested loop"), который является самой базовой(и менее производительной) операцией JOIN в Postgres.

Вы можете задаться вопросом, почему Postgres выполняет последовательное сканирование всех статей, если можно было использовать внешний ключ author_id. Ответ может вас удивить: множество СУБД(Postgres в том числе) не создают автоматически индекс на внешние ключи("foreign key")! Первичный ключи("primary key") автоматически индексируются, но внешние ключи должны быть явно проиндексированы, если вы хотите, чтобы они были эффективными.

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

class Article(db.Model):
id = db.Column(db.Integer, primary_key=True)
slug = db.Column(db.String(256), index=True, unique=True, nullable=False)
title = db.Column(db.String(256))
author_id = db.Column(db.Integer, db.ForeignKey('author.id'), index=True) # <-- add an index here
Или непосредственно в SQL:

CREATE INDEX ix_article_author_id ON article (author_id);
Теперь можно повторить предыдущий запрос и получить гораздо лучшее "разложение":

test=# EXPLAIN SELECT article.*, author.name
test=# FROM article, author
test=# WHERE article.author_id = author.id ORDER BY author.name LIMIT 5;

QUERY PLAN
--------------------------------------------------------------------------------------------------
Limit (cost=0.29..1.72 rows=5 width=153)
-> Nested Loop (cost=0.29..62.29 rows=217 width=153)
-> Index Scan using ix_author_name on author (cost=0.14..13.69 rows=103 width=18)
-> Index Scan using ix_article_author_id on article (cost=0.14..0.45 rows=2 width=139)
Index Cond: (author_id = author.id)
(5 rows)
Это действительно лучше, но я надеялся, что Postgres использует более эффективный алгоритм для JOIN, чем вложенный цикл. Я думаю, в данном случае Postgres решил, что вложенный цикл будет лучшим решением, потому что в итоге мы ищем всего пять записей. Видно, что при сканировании нового индекса на внешний ключ author_id понадобилось посмотреть только 2 строки из 217 в таблице, поэтому вложенный цикл в итоге оказался не очень дорогим. Если бы в таблицах было гораздо больше строк, или понадобилось бы больше, чем пять записей, "решение" этого запроса могло отличаться. По этой причине очень важно использовать выражение EXPLAIN на конкретных запросов, которые выполняются медленно, и проводить анализ на реальной базе данных(или на её резервной копии("mirror")).

MySQL

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

Начнём с того же простого запроса, который использовался для Postgres, чтобы выбрать первые пять статей, отсортированных в алфавитном порядке:

SELECT * FROM article ORDER BY article.slug LIMIT 5;
Посмотрим, что думает MySQL об этом запросе, добавив ключевое слово EXPLAIN:

mysql> EXPLAIN SELECT * FROM article ORDER BY article.slug LIMIT 5;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 217 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
Название операции "filesort" в MySQL может запутать, это просто означает, что записи нужно отсортировать, в отличии от индексируемых данных, которые не нуждаются в сортировке, поскольку индекс сам хранит данные отсортированными.

Это разочаровывает, потому что MySQL читает все записи в таблице article и сортирует их по столбцу slug, даже несмотря на то, что столбец article.slug индексирован. Я не совсем понимаю, почему так происходит, но я подозреваю, что MySQL решил, что при таком масштабе(217 записей) более эффективным будет отсортировать всю таблицу, чем вычитывать индекс для первых пяти записей, и затем вычитывать их из таблицы.

Посмотрим, сможем ли мы убедить MySQL использовать индекс article.slug. В следующем запросе, вместо того, чтобы запрашивать все столбцы таблицы, я запрашиваю только сам slug. Этот запрос может быть разрешён только на данных, хранящихся в индексе, поэтому я ожидаю, что MySQL предпочтёт индекс в этот раз:

mysql> EXPLAIN SELECT slug FROM article ORDER BY article.slug LIMIT 5;
+----+-------------+---------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | article | NULL | index | NULL | ix_article_slug | 1026 | NULL | 5 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
И да, на этот раз он предпочёл индекс.

Теперь вы, наверное, догадываетесь, что произойдёт, если мы отсортируем по столбцу title, которые не индексируется.

mysql> EXPLAIN SELECT * FROM article ORDER BY article.title LIMIT 5;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 217 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
Здесь MySQL снова возвращается к алгоритму filesort.

Оптимизация JOIN

SQLite

В качестве последнего примера, давайте посмотрим, как эти же запросы выполняются в SQLite. Вот схема, сгенерированная SQLAlchemy:

sqlite> .schema
CREATE TABLE author (
id INTEGER NOT NULL,
name VARCHAR(256) NOT NULL,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ix_author_name ON author (name);
CREATE TABLE article (
id INTEGER NOT NULL,
slug VARCHAR(256) NOT NULL,
title VARCHAR(256),
author_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(author_id) REFERENCES author (id),
);
CREATE UNIQUE INDEX ix_article_slug ON article (slug);

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

Как и случаях с Postgres и MySQL, мы начнём с простого запроса, который возвращает первые пять статей, отсортированных в алфавитном порядке по их столбцу slug:

SELECT * FROM article ORDER BY article.slug LIMIT 5;
SQLite имеет два разных способа "объяснить"(имеется ввиду EXPLAIN) запрос. Директива EXPLAIN выводит очень подробный список низкоуровневых операций виртуальной машины, которые будут выполнены для исполнения запроса, а директива EXPLAIN QUERY PLAN - высокоуровневый список операций, которые мы видели для Postgres и MySQL. При работе с SQLite я всегда использую последнюю.

sqlite> EXPLAIN QUERY PLAN SELECT * FROM article ORDER BY article.slug LIMIT 5;
QUERY PLAN
`--SCAN TABLE article USING INDEX ix_article_slug
В этом запросе видно, что SQLite сканирует таблицу article, используя индекс по столбцу slug. Это значит, что индекс будет использован для поиска тех первых пяти строк, а затем только эти пять записей будут извлечены из реальной таблицы. На самом деле это очень хорошо.

Мы видели, что Postgres оптимизирует запрос ещё больше, когда информация может быть получена непосредственно из индекса, без необходимости читать таблицу. Имеет ли SQLite такую же оптимизацию? Есть только один способ узнать это:

sqlite> EXPLAIN QUERY PLAN SELECT slug FROM article ORDER BY article.slug LIMIT 5;
QUERY PLAN
`--SCAN TABLE article USING COVERING INDEX ix_article_slug
В данной случае мы возвращаем только slugs, а так как они хранятся в индексе, SQLite экономит время и не обращается к таблице. SQLite называет это покрывающим индексом("covering index").

Что произойдёт, если мы попытаемся отсортировать по столбцу title, который не индексируется?

sqlite> EXPLAIN QUERY PLAN SELECT * FROM article ORDER BY article.title LIMIT 5;
QUERY PLAN
|--SCAN TABLE article
`--USE TEMP B-TREE FOR ORDER BY
Видно, что при сканировании таблицы не используется индекс, и SQLite приходится выполнять сортировку в памяти или на диске(в зависимости от размера), которую от реализует путем создания временного b-tree. Чтобы оптимизировать этот запрос, достаточно просто добавить индекс для столбца title.

Оптимизация JOIN

Далее мы рассмотрим, как исполняются JOIN. Как и в случаях с Postgres и MySQL, следующий запрос извлекает первые пять статей и имена авторов в алфавитном порядке:

sqlite> EXPLAIN QUERY PLAN SELECT article.*, author.name
sqlite> FROM article, author WHERE article.author_id = author.id
sqlite> ORDER BY author.name LIMIT 5;
QUERY PLAN
|--SCAN TABLE article
|--SEARCH TABLE author USING INTEGER PRIMARY KEY (rowid=?)
`--USE TEMP B-TREE FOR ORDER BY
Ниже приведён альтернативный синтаксис с использование JOIN, который должен быть эквивалентным:

sqlite> EXPLAIN QUERY PLAN SELECT article.*, author.name
sqlite> FROM article JOIN author ON article.author_id = author.id
sqlite> ORDER BY author.name LIMIT 5;
QUERY PLAN
|--SCAN TABLE article
|--SEARCH TABLE author USING INTEGER PRIMARY KEY (rowid=?)
`--USE TEMP B-TREE FOR ORDER BY
Здесь SQLite начинает со сканирования таблицы article без индекса, а затем выполняет JOIN с таблицей author путем поиска первичного ключа(который всегда индексирован). Затем выполняется сортировка с помощью b-tree, чтобы получить первые пять элементов.

Это не очень хорошо, поэтому мы должны посмотреть, улучшит ли ситуацию добавление индекса к столбцу author_id, который используется в условии JOIN. С точки зрения SQLAlchemy это будет простое добавление index=True для столбца author_id.

class Article(db.Model):
id = db.Column(db.Integer, primary_key=True)
slug = db.Column(db.String(256), index=True, unique=True, nullable=False)
title = db.Column(db.String(256))
author_id = db.Column(db.Integer, db.ForeignKey('author.id'), index=True) # <-- add an index here
Используя SQL, можно добиться того же самого с помощью этого оператора:

CREATE INDEX ix_article_author_id ON article (author_id);
К сожалению, добавление индекса не изменило запрос:

sqlite> EXPLAIN QUERY PLAN SELECT article.*, author.name
sqlite> FROM article JOIN author ON article.author_id = author.id
sqlite> ORDER BY author.name LIMIT 5;
QUERY PLAN
|--SCAN TABLE article
|--SEARCH TABLE author USING INTEGER PRIMARY KEY (rowid=?)
`--USE TEMP B-TREE FOR ORDER BY
Чтобы понять это, я нашёл документацию о том, как SQLite разрешает JOIN. В SQLite JOIN всегда реализуются как вложенный цикл, и только внутренний цикл может быть оптимизирован с помощью индекса, так что для SQLite это как нельзя лучше подходит.

Медленные запросы без последовательных сканов

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

Когда запрос, не содержащий больших сканов, выполняется медленно, это указывает на то, что структура вашей базы данных может затруднять его эффективное выполнение. Здесь я не хочу сказать, что вы неправильно структурировали вашу БД. На самом деле всё как раз наоборот! Реляционная модель способствует созданию множества таблиц, каждая из которых с определённой информацией и с ключами на другие. Это приводит к тому, что запросы, которым нужно комбинировать фрагменты данных в единый результат, становятся более трудозатратными. Иногда точное следование за реляционной моделью на самом деле становится проблемой.

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

Заключение

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

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