GA になってからサボり気味で 4 ヶ月ぶりの MySQL 8.0 ネタです。MySQL 8.0.13 が登場してからすでに 1 ヶ月…。
MySQL 8.0.13 では Skip Scan Range Access Method がサポートされたとのことなので（Facebook 提供のパッチによる）、試してみます。
Skip Scan Range Access Method（Skip Scan access method）とは
雑にいうと「複合インデックスの 1 番目の列が検索条件に入っていない場合でも当該インデックスを利用して検索する」仕組みです。
通常、複合インデックスを使うには 1 番目のキーが検索条件に入っている必要がありますが、
- 単一のテーブルに対する検索である
-
GROUP BYやDISTINCTを使っていない - 複合インデックスに含まれる列だけを選択（射影）する
などの条件を満たす場合、この Skip Scan Range Access Method が使える（場合がある）、ということになります。
※詳しくは以下を参照。
- 8.2.1.2 Range Optimization / Skip Scan Range Access Method（MySQL 8.0 Reference Manual）
やってみる
まずは、公式リファレンスマニュアルの例に沿ってやってみます。
例(1)
mysql> CREATE DATABASE skip_scan_test;
Query OK, 1 row affected (0.03 sec)
mysql> USE skip_scan_test;
Database changed
mysql> CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
Query OK, 0 rows affected (0.08 sec)
mysql> INSERT INTO t1 VALUES
-> (1,1), (1,2), (1,3), (1,4), (1,5),
-> (2,1), (2,2), (2,3), (2,4), (2,5);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
Query OK, 20 rows affected (0.01 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
Query OK, 40 rows affected (0.00 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
Query OK, 80 rows affected (0.01 sec)
Records: 80 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE t1;
+-------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| skip_scan_test.t1 | analyze | status | OK |
+-------------------+---------+----------+----------+
1 row in set (0.09 sec)
mysql> SELECT * FROM t1;
+----+----+
| f1 | f2 |
+----+----+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
（中略）
| 1 | 76 |
| 1 | 77 |
| 1 | 78 |
| 1 | 79 |
| 1 | 80 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
（中略）
| 2 | 76 |
| 2 | 77 |
| 2 | 78 |
| 2 | 79 |
| 2 | 80 |
+----+----+
160 rows in set (0.00 sec)
複合インデックス（この例では主キー）に含まれる列のうち、f1=1とf1=2のそれぞれに対してf2が1～80まで存在しています。
このテーブルに対して、EXPLAINを実行してみると、
EXPLAIN(1)
mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 53 | 100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)
Using index for skip scanが表示されました。
実際にSELECTしてみます。
SELECT(1)
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT f1, f2 FROM t1 WHERE f2 > 40;
+----+----+
| f1 | f2 |
+----+----+
| 1 | 41 |
| 1 | 42 |
| 1 | 43 |
| 1 | 44 |
| 1 | 45 |
（中略）
| 1 | 76 |
| 1 | 77 |
| 1 | 78 |
| 1 | 79 |
| 1 | 80 |
| 2 | 41 |
| 2 | 42 |
| 2 | 43 |
| 2 | 44 |
| 2 | 45 |
（中略）
| 2 | 76 |
| 2 | 77 |
| 2 | 78 |
| 2 | 79 |
| 2 | 80 |
+----+----+
80 rows in set (0.00 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 5 |
| Handler_read_last | 0 |
| Handler_read_next | 80 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
Handler_read_first=1・Handler_read_key=5・Handler_read_next=80でした。
データを少し触って、f1=0・f1=1・f1=2に対するf2が均等に存在しない状態を作ってみます。
例(2)
mysql> UPDATE t1 SET f1 = 0 WHERE f1 = 1 AND f2 < 21;
Query OK, 20 rows affected (0.02 sec)
Rows matched: 20 Changed: 20 Warnings: 0
mysql> UPDATE t1 SET f2 = f2 + 80 WHERE f1 = 0;
Query OK, 20 rows affected (0.10 sec)
Rows matched: 20 Changed: 20 Warnings: 0
mysql> ANALYZE TABLE t1;
+-------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| skip_scan_test.t1 | analyze | status | OK |
+-------------------+---------+----------+----------+
1 row in set (0.03 sec)
EXPLAINを実行すると、
EXPLAIN(2)
mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| 1 | SIMPLE | t1 | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 53 | 100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)
やはりUsing index for skip scanが表示されました。
実際にSELECTしてみると、
SELECT(2)
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT f1, f2 FROM t1 WHERE f2 > 40;
+----+-----+
| f1 | f2 |
+----+-----+
| 0 | 81 |
| 0 | 82 |
| 0 | 83 |
| 0 | 84 |
| 0 | 85 |
（中略）
| 0 | 96 |
| 0 | 97 |
| 0 | 98 |
| 0 | 99 |
| 0 | 100 |
| 1 | 41 |
| 1 | 42 |
| 1 | 43 |
| 1 | 44 |
| 1 | 45 |
（中略）
| 1 | 76 |
| 1 | 77 |
| 1 | 78 |
| 1 | 79 |
| 1 | 80 |
| 2 | 41 |
| 2 | 42 |
| 2 | 43 |
| 2 | 44 |
| 2 | 45 |
（中略）
| 2 | 76 |
| 2 | 77 |
| 2 | 78 |
| 2 | 79 |
| 2 | 80 |
+----+-----+
100 rows in set (0.00 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 7 |
| Handler_read_last | 0 |
| Handler_read_next | 100 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
こんな結果になりました。
なお、複合インデックスは 3 列以上であっても Skip Scan Range Access Method は有効です。
例(3)
mysql> CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL, f3 INT NOT NULL, s1 VARCHAR(100) NOT NULL, PRIMARY KEY(f1, f2, f3));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO t2 VALUES
-> (1,1,1,'a'), (1,2,2,'b'), (1,3,3,'c'), (1,4,4,'d'), (1,5,5,'e'),
-> (2,1,6,'f'), (2,2,7,'g'), (2,3,8,'h'), (2,4,9,'i'), (2,5,10,'j');
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t2 SELECT f1, f2 + 5, f3, s1 FROM t2;
Query OK, 10 rows affected (0.09 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t2 SELECT f1, f2 + 10, f3, s1 FROM t2;
Query OK, 20 rows affected (0.05 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t2 SELECT f1, f2 + 20, f3, s1 FROM t2;
Query OK, 40 rows affected (0.06 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t2 SELECT f1, f2 + 40, f3, s1 FROM t2;
Query OK, 80 rows affected (0.07 sec)
Records: 80 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t2;
+----+----+----+----+
| f1 | f2 | f3 | s1 |
+----+----+----+----+
| 1 | 1 | 1 | a |
| 1 | 2 | 2 | b |
| 1 | 3 | 3 | c |
| 1 | 4 | 4 | d |
| 1 | 5 | 5 | e |
（中略）
| 1 | 76 | 1 | a |
| 1 | 77 | 2 | b |
| 1 | 78 | 3 | c |
| 1 | 79 | 4 | d |
| 1 | 80 | 5 | e |
| 2 | 1 | 6 | f |
| 2 | 2 | 7 | g |
| 2 | 3 | 8 | h |
| 2 | 4 | 9 | i |
| 2 | 5 | 10 | j |
（中略）
| 2 | 76 | 6 | f |
| 2 | 77 | 7 | g |
| 2 | 78 | 8 | h |
| 2 | 79 | 9 | i |
| 2 | 80 | 10 | j |
+----+----+----+----+
160 rows in set (0.00 sec)
mysql> ANALYZE TABLE t2;
+-------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| skip_scan_test.t2 | analyze | status | OK |
+-------------------+---------+----------+----------+
1 row in set (0.06 sec)
mysql> EXPLAIN SELECT f1, f2, f3 FROM t2 WHERE f2 > 40;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| 1 | SIMPLE | t2 | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 53 | 100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT f1, f2, f3 FROM t2 WHERE f2 > 40;
+----+----+----+
| f1 | f2 | f3 |
+----+----+----+
| 1 | 41 | 1 |
| 1 | 42 | 2 |
| 1 | 43 | 3 |
| 1 | 44 | 4 |
| 1 | 45 | 5 |
（中略）
| 2 | 76 | 6 |
| 2 | 77 | 7 |
| 2 | 78 | 8 |
| 2 | 79 | 9 |
| 2 | 80 | 10 |
+----+----+----+
80 rows in set (0.00 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 5 |
| Handler_read_last | 0 |
| Handler_read_next | 80 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
但し、複合インデックスに存在しない列を含んだSELECTでは無効です（複合主キーならリーフノードを直接参照できるので有効にしてくれてもいいのに…と思わなくもないですが）。
無効例(1)
mysql> EXPLAIN SELECT * FROM t2 WHERE f2 > 40;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 160 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
また、複合インデックスなら何でも OK、とはさすがにならないようで、
無効例(2)
mysql> CREATE TABLE test1 (pk1 INT NOT NULL, pk2 INT NOT NULL, val1 INT, str1 VARCHAR(100), PRIMARY KEY(pk1, pk2));
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO test1 VALUES (1, 1, 1, '1');
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO test1 VALUES (1, 2, 2, '22');
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO test1 VALUES (1, 3, 3, '333');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test1 VALUES (1, 4, 4, '4444');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO test1 VALUES (1, 5, 5, '55555');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO test1 VALUES (2, 1, 6, '666666');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO test1 VALUES (2, 2, 7, '7777777');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO test1 VALUES (2, 3, 8, '88888888');
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO test1 VALUES (2, 4, 9, '999999999');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO test1 VALUES (2, 5, 10, '0000000000');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test1 SELECT pk1 + 2, pk2, val1 + 10, NULL FROM test1;
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO test1 SELECT pk1 + 4, pk2 + 10, NULL, str1 FROM test1;
Query OK, 20 rows affected (0.06 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> INSERT INTO test1 SELECT pk1 + 8, pk2 + 20, val1, NULL FROM test1;
Query OK, 40 rows affected (0.08 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test1;
+-----+-----+------+------------+
| pk1 | pk2 | val1 | str1 |
+-----+-----+------+------------+
| 1 | 1 | 1 | 1 |
| 1 | 2 | 2 | 22 |
| 1 | 3 | 3 | 333 |
| 1 | 4 | 4 | 4444 |
| 1 | 5 | 5 | 55555 |
| 2 | 1 | 6 | 666666 |
| 2 | 2 | 7 | 7777777 |
| 2 | 3 | 8 | 88888888 |
| 2 | 4 | 9 | 999999999 |
| 2 | 5 | 10 | 0000000000 |
| 3 | 1 | 11 | NULL |
| 3 | 2 | 12 | NULL |
| 3 | 3 | 13 | NULL |
| 3 | 4 | 14 | NULL |
| 3 | 5 | 15 | NULL |
| 4 | 1 | 16 | NULL |
| 4 | 2 | 17 | NULL |
| 4 | 3 | 18 | NULL |
| 4 | 4 | 19 | NULL |
| 4 | 5 | 20 | NULL |
| 5 | 11 | NULL | 1 |
| 5 | 12 | NULL | 22 |
| 5 | 13 | NULL | 333 |
| 5 | 14 | NULL | 4444 |
| 5 | 15 | NULL | 55555 |
| 6 | 11 | NULL | 666666 |
| 6 | 12 | NULL | 7777777 |
| 6 | 13 | NULL | 88888888 |
| 6 | 14 | NULL | 999999999 |
| 6 | 15 | NULL | 0000000000 |
| 7 | 11 | NULL | NULL |
| 7 | 12 | NULL | NULL |
| 7 | 13 | NULL | NULL |
| 7 | 14 | NULL | NULL |
| 7 | 15 | NULL | NULL |
| 8 | 11 | NULL | NULL |
| 8 | 12 | NULL | NULL |
| 8 | 13 | NULL | NULL |
| 8 | 14 | NULL | NULL |
| 8 | 15 | NULL | NULL |
| 9 | 21 | 1 | NULL |
| 9 | 22 | 2 | NULL |
| 9 | 23 | 3 | NULL |
| 9 | 24 | 4 | NULL |
| 9 | 25 | 5 | NULL |
| 10 | 21 | 6 | NULL |
| 10 | 22 | 7 | NULL |
| 10 | 23 | 8 | NULL |
| 10 | 24 | 9 | NULL |
| 10 | 25 | 10 | NULL |
| 11 | 21 | 11 | NULL |
| 11 | 22 | 12 | NULL |
| 11 | 23 | 13 | NULL |
| 11 | 24 | 14 | NULL |
| 11 | 25 | 15 | NULL |
| 12 | 21 | 16 | NULL |
| 12 | 22 | 17 | NULL |
| 12 | 23 | 18 | NULL |
| 12 | 24 | 19 | NULL |
| 12 | 25 | 20 | NULL |
| 13 | 31 | NULL | NULL |
| 13 | 32 | NULL | NULL |
| 13 | 33 | NULL | NULL |
| 13 | 34 | NULL | NULL |
| 13 | 35 | NULL | NULL |
| 14 | 31 | NULL | NULL |
| 14 | 32 | NULL | NULL |
| 14 | 33 | NULL | NULL |
| 14 | 34 | NULL | NULL |
| 14 | 35 | NULL | NULL |
| 15 | 31 | NULL | NULL |
| 15 | 32 | NULL | NULL |
| 15 | 33 | NULL | NULL |
| 15 | 34 | NULL | NULL |
| 15 | 35 | NULL | NULL |
| 16 | 31 | NULL | NULL |
| 16 | 32 | NULL | NULL |
| 16 | 33 | NULL | NULL |
| 16 | 34 | NULL | NULL |
| 16 | 35 | NULL | NULL |
+-----+-----+------+------------+
80 rows in set (0.00 sec)
mysql> EXPLAIN SELECT pk1, pk2 FROM test1 WHERE pk2 > 30;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test1 | NULL | index | PRIMARY | PRIMARY | 8 | NULL | 80 | 33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
この例ではfor skip scanが付かないUsing index（インデックススキャン）になっています。