技術力をスコア化して市場価値をチェックしてみませんか?PR

ブログやSNSのアウトプットをAIが分析。技術力の順位を算出!自分の市場価値を測ってみませんか?

2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

MySQL 8.0.13 で Skip Scan Range Access Method を試してみる

Last updated at Posted at 2018-11-27

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 BYDISTINCTを使っていない
  • 複合インデックスに含まれる列だけを選択(射影)する

などの条件を満たす場合、この Skip Scan Range Access Method が使える(場合がある)、ということになります。

※詳しくは以下を参照。

やってみる

まずは、公式リファレンスマニュアルの例に沿ってやってみます。

例(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=1f1=2のそれぞれに対してf2180まで存在しています。

このテーブルに対して、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=1Handler_read_key=5Handler_read_next=80でした。

データを少し触って、f1=0f1=1f1=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(インデックススキャン)になっています。


2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up

Comments

No comments

Let's comment your feelings that are more than good

Qiita Advent Calendar is held!

Qiita Advent Calendar is an article posting event where you post articles by filling a calendar 🎅

Some calendars come with gifts and some gifts are drawn from all calendars 👀

Please tie the article to your calendar and let's enjoy Christmas together!

2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Login to continue?

Login or Sign up with social account

Login or Sign up with your email address