Как использовать индексы в JOIN запросах Mysql

Работа с индексами в Mysql — это фундаментальная задача для построения систем с высокой производительностью. В этой статье разберемся с тем, как Mysql использует индексы в JOIN запросах.

Вводные

Представим, что у нас есть простая система подсчета статистики просмотров статей. Данные о статьях мы храним в одной таблице:

+-------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type         | Null | Key | Default           | Extra                       |
+-------+--------------+------+-----+-------------------+-----------------------------+
| id    | int(11)      | NO   | PRI | 0                 |                             |
| ts    | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| title | varchar(512) | YES  |     | NULL              |                             |
+-------+--------------+------+-----+-------------------+-----------------------------+

Данные со статистикой хранятся в другой таблице с такой структурой:

+------------+---------+------+-----+------------+----------------+
| Field      | Type    | Null | Key | Default    | Extra          |
+------------+---------+------+-----+------------+----------------+
| url_id     | int(11) | NO   | PRI | NULL       | auto_increment |
| article_id | int(11) | NO   |     | 0          |                |
| date       | date    | NO   |     | 0000-00-00 |                |
| pageviews  | int(11) | YES  |     | NULL       |                |
| uniques    | int(11) | YES  |     | NULL       |                |
+------------+---------+------+-----+------------+----------------+

Обратите внимание, что во второй таблице первичный ключ — это url_id. Это идентификатор ссылки на статью. Т.е. у одной статьи может быть несколько разных ссылок, и для каждой из них мы будем собирать статистику. Колонка article_id соответствует колонке id из первой таблицы. Сама статистика очень простая — количество просмотров и уникальных посетителей в день.

Выборка статистики для одной статьи

Сделаем выбор статистики для одной статьи:

SELECT s.article_id, s.date, SUM(s.pageviews), SUM(s.uniques)
FROM articles a
JOIN articles_stats s ON (s.article_id = a.id)
WHERE a.id = 4
GROUP BY s.date;

# Статистика для статьи с id = 4

На выходе получим просмотры и уникальных посетителей для этой статьи за каждый день:

+------------+------------+------------------+----------------+
| article_id | date       | SUM(s.pageviews) | SUM(s.uniques) |
+------------+------------+------------------+----------------+
|          4 | 2016-01-03 |            28920 |           9640 |
...							    ...
|          4 | 2016-01-07 |             1765 |            441 |
+------------+------------+------------------+----------------+
499 rows in set (0.37 sec)

Запрос отработал за 0.37 секунд, что довольно медленно. Посмотрим на EXPLAIN:

+----+-------------+-------+-------+---------------+---------+---------+-------+--------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows   | Extra                                        |
+----+-------------+-------+-------+---------------+---------+---------+-------+--------+----------------------------------------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const |      1 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | s     | ALL   | NULL          | NULL    | NULL    | NULL  | 676786 | Using where                                  |
+----+-------------+-------+-------+---------------+---------+---------+-------+--------+----------------------------------------------+

EXPLAIN показывает две записи — по одной для каждой таблицы из нашего запроса:

  • Для первой таблицы Mysql выбрал индекс PRIMARY и эффективно его использовал.
  • Для второй таблицы Mysql не смог найти подходящих индексов и ему пришлось проверить почти 700 тыс. записей, чтобы сгенерировать результат.
В JOIN запросах Mysql будет использовать индекс, который позволит отфильтровать больше всего записей из одной из таблиц

Поэтому нам необходимо убедиться, что Mysql будет быстро выполнять запрос такого вида:

SELECT article_id, date, SUM(pageviews), SUM(uniques) FROM articles_stats WHERE article_id = 4 GROUP BY date

Согласно логике выбора индексов построим индекс по колонке article_id:

CREATE INDEX article_id on articles_stats(article_id);

Проверим использование индексов в нашем первом запросе:

EXPLAIN SELECT s.article_id, s.date, SUM(s.pageviews), SUM(s.uniques) from articles a join articles_stats s on (s.article_id = a.id) where a.id = 4 group by s.date;

И увидим, что Mysql теперь использует индексы для двух таблиц:

+----+-------------+-------+-------+---------------+------------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref   | rows | Extra                                        |
+----+-------------+-------+-------+---------------+------------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY    | 4       | const |    1 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | s     | ref   | article_id    | article_id | 4       | const |  677 | Using where                                  |
+----+-------------+-------+-------+---------------+------------+---------+-------+------+----------------------------------------------+

Это значительно ускорит запрос (ведь Mysql во втором случае обрабатывает в 1000 раз меньше данных).

Агрегационные запросы

Предыдущий пример носит более лабораторный характер. Более приближенный к практике запрос — это выборка статистики сразу по нескольким статьям:

SELECT s.article_id, s.date, SUM(s.pageviews), SUM(s.uniques), a.title, a.ts
FROM articles a
JOIN articles_stats s ON (s.article_id = a.id)
WHERE a.id IN (4,5,6,7)
GROUP BY s.date;

Однако в этом случае Mysql будет вести себя точно также. Он оценит какие индексы можно использовать из каждой таблицы. EXPLAIN покажет:

+----+-------------+-------+--------+---------------+------------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key        | key_len | ref               | rows | Extra                                        |
+----+-------------+-------+--------+---------------+------------+---------+-------------------+------+----------------------------------------------+
|  1 | SIMPLE      | s     | range  | article_id    | article_id | 4       | NULL              | 2030 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY    | 4       | test.s.article_id |    1 | Using index                                  |
+----+-------------+-------+--------+---------------+------------+---------+-------------------+------+----------------------------------------------+

Таблицы будут обработаны в другом порядке. Сначала будет сделана выборка всех подходящих значений из таблицы статистики. А затем из таблицы с названиями.

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

Дополнительные фильтры

На практике приходится иметь дело с дополнительными фильтрами в запросах. Например, выборка статистики только за определенную дату:

SELECT s.article_id, s.date, SUM(s.pageviews), SUM(s.uniques), a.title, a.ts
FROM articles a
JOIN articles_stats s ON (s.article_id = a.id)
WHERE s.date = '2017-05-14'
GROUP BY article_id

В этом случае, Mysql снова не сможет подобрать индекс для таблицы статистики:

+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows   | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+
|  1 | SIMPLE      | s     | ALL    | article_id    | NULL    | NULL    | NULL              | 676786 | Using where |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.s.article_id |      1 |             |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+--------+-------------+

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

CREATE INDEX date ON articles_stats(date);

Теперь запрос будет использовать индексы на обе таблицы:

+----+-------------+-------+--------+-----------------+---------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref               | rows | Extra                                        |
+----+-------------+-------+--------+-----------------+---------+---------+-------------------+------+----------------------------------------------+
|  1 | SIMPLE      | s     | ref    | article_id,date | date    | 4       | const             | 2996 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY         | PRIMARY | 4       | test.s.article_id |    1 |                                              |
+----+-------------+-------+--------+-----------------+---------+---------+-------------------+------+----------------------------------------------+

Сложные фильтры и сортировки

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

SELECT s.article_id, s.date, SUM(s.pageviews), SUM(s.uniques), a.title, a.ts
FROM articles a
JOIN articles_stats s ON (s.article_id = a.id)
WHERE a.ts > '2017-04-15' AND s.date = '2017-05-14' AND s.uniques > 15000
GROUP BY article_id
ORDER BY s.pageviews

# Запрос отработает за 0.15 секунд, что довольно медленно

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

+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows  | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | s     | range  | date          | date    | 4       | NULL              | 26384 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.s.article_id |     1 | Using where                                  |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+----------------------------------------------+

Индекс date позволит отфильтровать таблицу статистики до 26 тыс. записей. Каждую из которых придется проверить на соответствие другим условиям (количество уникальных посетителей более 15 тыс.).

Сортировку по просмотрам Mysql будет в любом случае делать самостоятельно. Индексы тут не помогут, т.к. сортируем динамические значения (результат операции GROUP BY).

Поэтому наша задача — выбрать индекс, который позволит максимально сократить выборку по таблице articles_stats используя фильтр s.date = '2017-05-14' AND s.uniques > 15000.

Создадим индекс на обе колонки из первого пункта:

CREATE INDEX date_uniques ON articles_stats(date,uniques);

Тогда Mysql сможет использовать этот индекс для фильтрации таблицы статистики:

+----+-------------+-------+--------+---------------+--------------+---------+-------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key          | key_len | ref               | rows | Extra                                        |
+----+-------------+-------+--------+---------------+--------------+---------+-------------------+------+----------------------------------------------+
|  1 | SIMPLE      | s     | range  | date_uniques  | date_uniques | 9       | NULL              | 1681 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY,ts_id | PRIMARY      | 4       | test.s.article_id |    1 | Using where                                  |
+----+-------------+-------+--------+---------------+--------------+---------+-------------------+------+----------------------------------------------+

# При таком индексе Mysql обработает в 10 раз меньше записей для выборки

Другие стратегии ускорения JOIN

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

Лучше много легких записей, чем много тяжелых чтений

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

TL;DR

  • Используйте те же правила создания индексов, что и для обычных запросов — индекс для колонок в WHERE.
  • В сложных выборках выбирайте индексы (перебором), которые позволят уменьшить число "rows" в EXPLAIN запроса.
  • В особо сложных случаях — денормализация и кеширование.

Подпишитесь на Хайлоад с помощью Google аккаунта
или закройте эту хрень