Релиз MySQL 8.0 Labs - убывающие индексы в MySQL
Дата: 13.01.2017
Данная статья является переводом статьи Chaithra Gopalareddy.
Начиная с релиза 8.0 optimizer labs в Mysql сервер добавлена поддержка убывающих индексов. В статье подробно будет показано, как эта новая функциональность может быть использована для того, чтобы избежать необходимости сортировки результата и, как следствие, улучшить производительность ряда запросов.
Введение
До этого релиза все индексы создавались в порядке возрастания. Синтаксис допускал указание порядка сортировки, но оно игнорировалось. Например, в MySQL 5.7:
mysql 5.7> CREATE TABLE t1 (a INT, b INT, INDEX a_desc_b_asc (a DESC, b ASC));
Query OK, 0 rows affected (0.47 sec)
mysql 5.7> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `a_desc_b_asc` (`a`,`b`) <-- указание порядка сортировки не сохранилось
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Следует отметить, что в MySQL 5.7 оптимизатор имеет возможность сканировать возрастающий индекс в обратном направлении, однако это требует дополнительных затрат. Как будет показано далее, прямое сканирование индекса приблизительно на 15% лучше, чем сканирование в обратном направлении.
Кроме того, из-за отсутствия поддержки убывающих индексов оптимизатор вынужден использовать файловую сортировку в случае смешанного порядка, например ORDER BY a DESC, b ASC.
Улучшения в MySQL 8.0
Теперь InnoDB может хранить записи в порядке убывания, и оптимизатор воспользуется этим обстоятельством, когда в запросе указана сортировка по убыванию. Повторив приведенный выше пример, мы увидим, что указание порядка сортировки индекса корректно сохраняется при создании таблицы:
mysql 8.0> CREATE TABLE t1 (a INT, b INT, INDEX a_desc_b_asc (a DESC, b ASC));
Query OK, 0 rows affected (0.47 sec)
mysql 8.0> show create table t1;
+-------+--------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
KEY `a_desc_b_asc` (`a` DESC,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
Также, была улучшена показываемая командной EXPLAIN информация, чтобы различать случаи прямого и обратного сканирования индекса. В MySQL 5.7 будут использованы обратное сканирование индекса или файловая сортировка для всех запросов кроме №2 и №6 из показанных ниже:
Запрос 1: SELECT * FROM t1 ORDER BY a DESC;
mysql 8.0> explain SELECT * FROM t1 ORDER BY a DESC;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | a_desc_b_asc | 10 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Запрос 2: SELECT * FROM t1 ORDER BY a ASC;
mysql 8.0> explain SELECT * FROM t1 ORDER BY a ASC;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | a_desc_b_asc | 10 | NULL | 10 | 100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
Запрос 3: SELECT * FROM t1 ORDER BY a DESC, b ASC;
mysql 8.0> EXPLAIN SELECT * FROM t1 ORDER BY a DESC, b ASC;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | a_desc_b_asc | 10 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Запрос 4: SELECT * FROM t1 ORDER BY a ASC, b DESC;
mysql 8.0> EXPLAIN SELECT * FROM t1 ORDER BY a ASC, b DESC;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | a_desc_b_asc | 10 | NULL | 10 | 100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
Запрос 5: SELECT * FROM t1 ORDER BY a DESC, b DESC;
mysql 8.0> EXPLAIN SELECT * FROM t1 ORDER BY a DESC, b DESC;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | a_desc_b_asc | 10 | NULL | 10 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)
Запрос 6: SELECT * FROM t1 ORDER BY a ASC, b ASC;
mysql 8.0> EXPLAIN SELECT * FROM t1 ORDER BY a ASC, b ASC;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | t1 | NULL | index | NULL | a_desc_b_asc | 10 | NULL | 10 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
На диаграмме представлены показатели производительности для всех 6 запросов, когда таблица имеет 1 индекс a_desc_b_asc (a DESC, b ASC). В MySQL 5.7 это будет индекс вида a_asc_b_asc(a ASC, b ASC), так как нет поддержки убывающих индексов. Размер тестовых данных 10 миллионов строк.
Пояснения к показателям производительности:
- Мы видим улучшение для запроса №1, где требуется сортировка по убыванию столбца “a”.
- Запрос №2, где требуется сортировка по возрастанию столбца “a”, в MySQL 8.0 занимает больше времени по сравнению с запросом №1, так как происходит сканирование в обратном направлении. (Обратите внимание, что MySQL 8.0 в целом показывает лучшую производительность: в MySQL 5.7 прямое сканирование индекса выполняется столько же времени, сколько и сканирование в обратном направлении в MySQL 8.0).
- В запросе №3, подобно запросу №1, требуемая сортировка совпадает с указанной в определении индекса. Огромная разница в производительности возникает из-за того, что в MySQL 5.7 для запросов со смешанным порядком сортировки всегда используется файловая сортировка.
- Запрос №4 выполняет обратное сканирование индекса, поэтому его производительность в MySQL 8.0 хуже, чем у запроса №3.
- В отличии от MySQL 5.7 в MySQL 8.0 запросы №5 и №6 не могут быть выполнены путем прямого или обратного сканирования индекса. Для них потребуется файловая сортировка.
- Если мы хотим избежать файловой сортировка для запросов №5 и №6, то можно добавить в таблицу индекс вида (a ASC, b ASC). Если, кроме того, мы хотим избавиться и от сканирования в обратном направлении, то нужно добавить ещё 2 индекса: ( a ASC, b DESC) и (a DESC, b DESC).
Окончательное сравнение производительности между MySQL-5.7.14 и MySQL-8.0-labs после добавления дополнительных индексов, описанных в последнем пункте, выглядит так:
Обратите внимание, что в MySQL 5.7 мы не можем добавить дополнительные индексы для повышения производительности вышеупомянутых запросов. Кроме того, новая функциональность позволяет в некоторых случаях избежать материализации, например, когда указан смешанный порядок сортировки для первой таблицы в соединении (join). В статье был приведен ряд ситуаций, в которых убывающие индексы улучшают производительность. Кроме того, убывающие индексы используются при сканировании по диапазону. Хотя не все методы сканирования по диапазону используют убывающие индексы, мы будем пытаться снять эти ограничения в будущем.
Изменения в поведении
Вместе с внедрением убывающих индексов мы также удалили неявную сортировку при использовании группировки. Это также привело к улучшению производительности в ряде случаев.
Заключение
Мы рады, что удалось решить одно из давних пожеланий от сообщества MySQL. Пожалуйста, проверьте работу убывающих индексов и дайте нам знать ваши соображения!
Дата публикации: 13.01.2017
© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.
|
