![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
MySQL8.0から、Descending Indexがサポートされる。
これによりMySQL5.7と比較して、大きなテーブルでの降順(DESC)のデータ参照処理を、高速に実行する事が出来るようになる。小さいテーブルに関しては、既存のMySQL5.7でもインデックスが利用出来るので、それ程差は出ないかと思います。
MySQL8.0.1で、Sakila Sampleデータベースのrentalテーブルを参照した場合 (INDEXはASCで作成されている)
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.1-dmr |
+-----------+
1 row in set (0.00 sec)
mysql> show create table rental\G
*************************** 1. row ***************************
Table: rental
Create Table: CREATE TABLE `rental` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `idx_fk_staff_id` (`staff_id`),
CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from rental order by rental_date asc limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date | inventory_id | customer_id | return_date | staff_id | last_update |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| 1 | 2005-05-24 22:53:30 | 367 | 130 | 2005-05-26 22:04:30 | 1 | 2006-02-15 21:30:53 |
| 2 | 2005-05-24 22:54:33 | 1525 | 459 | 2005-05-28 19:40:33 | 1 | 2006-02-15 21:30:53 |
| 3 | 2005-05-24 23:03:39 | 1711 | 408 | 2005-06-01 22:12:39 | 1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)
mysql> explain select * from rental order by rental_date asc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| 1 | SIMPLE | rental | NULL | index | NULL | rental_date | 10 | NULL | 3 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date | inventory_id | customer_id | return_date | staff_id | last_update |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| 11739 | 2006-02-14 15:16:03 | 4568 | 373 | NULL | 2 | 2006-02-15 21:30:53 |
| 14616 | 2006-02-14 15:16:03 | 4537 | 532 | NULL | 1 | 2006-02-15 21:30:53 |
| 11676 | 2006-02-14 15:16:03 | 4496 | 216 | NULL | 2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)
mysql> explain select * from rental order by rental_date desc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
| 1 | SIMPLE | rental | NULL | index | NULL | rental_date | 10 | NULL | 3 | 100.00 | Backward index scan |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
MySQL8.0.1で、Sakila Sampleデータベースのrentalテーブルを参照した場合 (INDEXをDESCで作成し直した場合)
インデックスを降順に作成したので、先程とはEXTRAが反対になっている事が確認出来る。
mysql> alter table rental add unique key rental_date_desc (`rental_date` desc,`inventory_id` desc,`customer_id` desc); Query OK, 0 rows affected (0.30 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from rental order by rental_date asc limit 3; +-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+ | rental_id | rental_date | inventory_id | customer_id | return_date | staff_id | last_update | +-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+ | 1 | 2005-05-24 22:53:30 | 367 | 130 | 2005-05-26 22:04:30 | 1 | 2006-02-15 21:30:53 | | 2 | 2005-05-24 22:54:33 | 1525 | 459 | 2005-05-28 19:40:33 | 1 | 2006-02-15 21:30:53 | | 3 | 2005-05-24 23:03:39 | 1711 | 408 | 2005-06-01 22:12:39 | 1 | 2006-02-15 21:30:53 | +-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+ 3 rows in set (0.00 sec) mysql> explain select * from rental order by rental_date asc limit 3; +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+ | 1 | SIMPLE | rental | NULL | index | NULL | rental_date_desc | 10 | NULL | 3 | 100.00 | Backward index scan | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+ 1 row in set, 1 warning (0.01 sec) mysql> select * from rental order by rental_date desc limit 3; +-----------+---------------------+--------------+-------------+-------------+----------+---------------------+ | rental_id | rental_date | inventory_id | customer_id | return_date | staff_id | last_update | +-----------+---------------------+--------------+-------------+-------------+----------+---------------------+ | 11739 | 2006-02-14 15:16:03 | 4568 | 373 | NULL | 2 | 2006-02-15 21:30:53 | | 14616 | 2006-02-14 15:16:03 | 4537 | 532 | NULL | 1 | 2006-02-15 21:30:53 | | 11676 | 2006-02-14 15:16:03 | 4496 | 216 | NULL | 2 | 2006-02-15 21:30:53 | +-----------+---------------------+--------------+-------------+-------------+----------+---------------------+ 3 rows in set (0.00 sec) mysql> explain select * from rental order by rental_date desc limit 3; +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+ | 1 | SIMPLE | rental | NULL | index | NULL | rental_date_desc | 10 | NULL | 3 | 100.00 | NULL | +----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) mysql>
詳細は、以下のMySQL Server Teamのブログを確認下さい。
MySQL 8.0 Labs – Descending Indexes in MySQL
http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/
MySQL5.5 ～ MySQL5.7までは昇順(ASC)で格納されたインデックスデータを利用してASC, DESC共に参照処理を行う
MySQL 5.7までは、インデックスのデータはASCで格納されています。こちらは、DESCで格納したインデックスと比べて、降順のデータの参照パフォーマンスは遅いが、
クエリーにDESCオプションを付けて、直近のデータを参照するとASCで作成したINDEXを利用してデータを参照する為、インデックスを利用した後方参照処理になる為、
インデックスが無い場合と比べても高速なレスポンスで後方参照処理を行う事が出来る。以下のマニュアルは、若干分かり難いがASC、DESCを付けてインデックスを作成しても、
MySQL5.5以降のMySQLであれば昇順(ASC)で作成されたINDEXでASCもDESCも処理出来る為、高速に参照する事が可能です。MySQL8.0で処理がより高速になる。
5.7 英語マニュアル
https://dev.mysql.com/doc/refman/5.7/en/create-index.html
An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.
5.6 日本語マニュアル
https://dev.mysql.com/doc/refman/5.6/ja/create-index.html
index_col_name の指定を ASC または DESC で終了させることができます。これらのキーワードは、インデックス値の昇順または降順での格納を指定する将来の拡張のために許可されています。現在、これらは解析されますが、無視されます。インデックス値は、常に昇順で格納されます。
MySQL5.7.18の実行プランは以下のような感じです。インデックスを利用して降順(DESC)参照が行われている事が確認出来る。
MySQL8.0RCがリリースされた頃に、大きなテーブルでレスポンスの差を確認して見たいと思います。
mysql> select @@version;
+-------------------------------------------+
| @@version |
+-------------------------------------------+
| 5.7.18-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table rental\G
*************************** 1. row ***************************
Table: rental
Create Table: CREATE TABLE `rental` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `idx_fk_staff_id` (`staff_id`),
CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
mysql> select * from rental limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date | inventory_id | customer_id | return_date | staff_id | last_update |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| 1 | 2005-05-24 22:53:30 | 367 | 130 | 2005-05-26 22:04:30 | 1 | 2006-02-15 21:30:53 |
| 2 | 2005-05-24 22:54:33 | 1525 | 459 | 2005-05-28 19:40:33 | 1 | 2006-02-15 21:30:53 |
| 3 | 2005-05-24 23:03:39 | 1711 | 408 | 2005-06-01 22:12:39 | 1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)
mysql> explain select * from rental limit 3;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
| 1 | SIMPLE | rental | NULL | ALL | NULL | NULL | NULL | NULL | 16005 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.02 sec)
Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` limit 3
mysql> select * from rental order by rental_date asc limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date | inventory_id | customer_id | return_date | staff_id | last_update |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| 1 | 2005-05-24 22:53:30 | 367 | 130 | 2005-05-26 22:04:30 | 1 | 2006-02-15 21:30:53 |
| 2 | 2005-05-24 22:54:33 | 1525 | 459 | 2005-05-28 19:40:33 | 1 | 2006-02-15 21:30:53 |
| 3 | 2005-05-24 23:03:39 | 1711 | 408 | 2005-06-01 22:12:39 | 1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)
mysql> explain select * from rental order by rental_date asc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| 1 | SIMPLE | rental | NULL | index | NULL | rental_date | 10 | NULL | 3 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)
Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` order by `sakila`.`rental`.`rental_date` limit 3
mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date | inventory_id | customer_id | return_date | staff_id | last_update |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| 11739 | 2006-02-14 15:16:03 | 4568 | 373 | NULL | 2 | 2006-02-15 21:30:53 |
| 14616 | 2006-02-14 15:16:03 | 4537 | 532 | NULL | 1 | 2006-02-15 21:30:53 |
| 11676 | 2006-02-14 15:16:03 | 4496 | 216 | NULL | 2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)
mysql> explain select * from rental order by rental_date desc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| 1 | SIMPLE | rental | NULL | index | NULL | rental_date | 10 | NULL | 3 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` order by `sakila`.`rental`.`rental_date` desc limit 3
mysql>