Работа с индексами в 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 показывает две записи – по одной для каждой таблицы из нашего запроса:
В 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 раз меньше записей для выборки
В ситуациях, когда невозможно выбрать подходящий индекс, следует подумать о денормализации. В этом случае стоит пользоваться правилом:
Лучше много легких записей, чем много тяжелых чтений
Следует создать таблицу, оптимизированную под запрос и синхронно её обновлять. Однако убедитесь, что ваш сервер хорошо настроен. В качестве срочных мер рассмотрите возможность кешировать результаты тяжелых запросов.
<h2>TL;DR
Сегодня мы поговорим о том, как выбрать лучшие курсы Power BI в Украине, особенно для…
В 2023 году во всех крупнейших регионах конкуренция за вакансию выросла на 5–12%. Не исключением…
Unicorn Hunter/Talent Manager Лина Калиш создала бесплатный трекер поиска работы в Notion, систематизирующий все этапы…
Edtech-стартап Mate academy принял решение отправить своих работников в десятидневный отпуск – с 25 декабря…
Служба безопасности Украины задержала в Киеве 46-летнего программиста, который за деньги устанавливал шпионские программы и…
IT-специалист Джордан Катлер создал и выложил на Github подборку разнообразных ресурсов, которые помогут достичь уровня…