MySQL8.0の機能を調べてまとめている。
Descending indexについて読んだまとめ。
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が使われた
- 次に
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になっていしまっている。。。
- 次に
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に対してどういうパフォーマンス劣化があるのか調べると良さそう。