tom__bo’s Blog

MySQL!! MySQL!! @tom__bo

MySQL 8.0 Descending index

MySQL8.0の機能を調べてまとめている。
Descending indexについて読んだまとめ。

dev.mysql.com

MySQL 8.0では、Descending index(DESC, 降順のindex)がサポートされるようになった。 これまではASC(昇順)のindexを逆順にスキャンすることはできたが、パフォーマンス上のペナルティがあった。 Descending indexを使うことで、降順のアクセスでも正順でスキャンすることができる。 optimizerはDESCを含むマルチカラムindexであってもコストを考慮してこれを利用してくれる。

サンプル

c1, c2カラムにasc, descの組み合わせでカラムを作った。
index名はasc, descの頭1文字をとってaかd

mysql> create table t4 (
    -> id int not null auto_increment,
    -> c1 int,
    -> c2 int,
    -> index aa (c1 asc, c2 asc),
    -> index ad (c1 asc, c2 desc),
    -> index da (c1 desc, c2 asc),
    -> index dd (c1 desc, c2 desc),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `aa` (`c1`,`c2`),
  KEY `ad` (`c1`,`c2` DESC),
  KEY `da` (`c1` DESC,`c2`),
  KEY `dd` (`c1` DESC,`c2` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

適当にデータを入れてEXPLAIN

mysql> select count(*) from t4;
+----------+
| count(*) |
+----------+
|      768 |
+----------+
1 row in set (0.01 sec)

mysql> explain select * from t4 order by c1 asc, c2 asc limit 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t4    | NULL       | index | NULL          | aa   | 10      | NULL |   10 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t4 order by c1 asc, c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t4    | NULL       | index | NULL          | ad   | 10      | NULL |   10 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t4 order by c1 desc, c2 asc limit 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t4    | NULL       | index | NULL          | da   | 10      | NULL |   10 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from t4 order by c1 desc, c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t4    | NULL       | index | NULL          | dd   | 10      | NULL |   10 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

order byの順序に合わせて正順で引けるindexを使うようになっている。

インデックスがない場合の挙動

上記のサンプルでorder by c1 asc, c2 descをするときに使っているindexを1つずつ落としていって、どのindexがつかわれるのかを実験してみる。

  • クエリ explain select * from t4 order by c1 asc, c2 desc limit 10;

  • asc, descのindexをとる

mysql> alter table t4 alter index ad invisible;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t4 order by c1 asc, c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | t4    | NULL       | index | NULL          | da   | 10      | NULL |   10 |   100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

c1, c2の両方でBackward index scanを使うdesc, ascのindexが使われた

  1. 次にasc, desc, desc, ascのindexをとる
mysql> alter table t4 alter index da invisible;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t4 order by c1 asc, c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t4    | NULL       | index | NULL          | aa   | 10      | NULL |  768 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)

aa使って、filesortになった。 rowsで全件が読まれていることから結局index full scanになっていしまっている。。。

  1. 次にasc, desc, desc, asc, asc, ascのindexをとる
mysql> alter table t4 alter index aa invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t4 order by c1 asc, c2 desc limit 10;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t4    | NULL       | index | NULL          | dd   | 10      | NULL |  768 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

dd使ってfilesort.
これもrowsで全件が読まれていることから結局index full scanになってしまっている。

DESC indexの条件

Descending indexはInnoDBストレージエンジンで以下の条件下で有効。

  • secondary indexでDESC indexを含んでいる場合やDESC indexを含むPKではChange bufferingはサポートされない
  • InnoDB SQL parserはdescending indexをサポートしない. InnoDB full-text searchにおいて、これはFTS_DOC_IDカラムのindexはDESC indexとして定義できないことを意味する
  • DESC indexはこれまでのASC indexが使える条件で有効
  • 通常のカラムやgenerated column(virtualでもstoredでも)でも有効
  • DISTINCT句もDESC indexでも有効
  • DESC indexを利用し、集約関数を呼び出すが、group byを使わない場合、MIN()/MAX()最適化は行われない
  • BTREEでは有効だが、HASH index, FULLTEXT index, SPTIAL indexでは無効

このリストの2つめはよくわからなかった。
Full text searchのクエリはInnoDB独自のパーサーを使っているのかな。Fulltext searchはそもそもの設計としてMySQLではなくてElasticsearchを使うと思うので、調べる優先度低め。

DESC indexを含む場合にChange bufferingがサポートされないのは、大量のwriteに対してどういうパフォーマンス劣化があるのか調べると良さそう。

references