MySQL8.0.13のリリースノートに遡って見て見ると、以下の様な記述があったのでどのような処理なのか？どれだけパフォーマンスに影響があるのかを確認してみました。
Optimizer Notes: The optimizer now supports a Skip Scan access method that enables range access to be used in previously inapplicable situations to improve query performance. For more information, see Skip Scan Range Access Method. Thanks to Facebook for the patch on which this access method is based. (Bug #26976512, Bug #88103)
Changes in MySQL 8.0.13 (2018-10-22, General Availability)
Skip Scan Range Access Method (スキャン範囲アクセス方法のスキップ)
このクエリーを実行するために、MySQL では、インデックススキャンを選択してすべての行をフェッチし (インデックスには選択するすべてのカラムが含まれます)、WHERE 句から f2 > 40 条件を適用して最終結果セットを生成できます。レンジスキャンは全インデックススキャンよりも効率的ですが、最初のインデックスカラムであるf1に条件がないため、この場合は使用できません。 ただし、MySQL 8.0.13 の時点では、オプティマイザは、ループインデックススキャンと同様のスキップスキャンという方法を使用して、f1の値ごとに複数のレンジスキャンを実行できます (セクション8.2.1.17「GROUP BY の最適化」 を参照):
8.2.1.2 range の最適化
上記に記載があるように、以下のSQLではPKに全てのデータを含んでいますが、f1,f2の並び順になっているので、”WHERE f2 > 40”の条件ではCONSTにはなりません。その代わりに、全インデックスをスキャンする代わりに、”Using index for skip scan”という方法でデータをフィルターする事が出来ています。
mysql> CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
Query OK, 0 rows affected (0.19 sec)
mysql> INSERT INTO t1 VALUES
(1,1), -> (1,1), (1,2), (1,3), (1,4), (1,5),
2,1), -> (2,1), (2,2), (2,3), (2,4), (2,5);
Query OK, 10 rows affected (0.04 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+----+
| f1 | f2 |
+----+----+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
+----+----+
10 rows in set (0.00 sec)
mysql> INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
NSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;Query OK, 10 rows affected (0.01 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.01 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> select * from t1 limit 30;
+----+----+
| f1 | f2 |
+----+----+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 1 | 6 |
| 1 | 7 |
| 1 | 8 |
| 1 | 9 |
| 1 | 10 |
| 1 | 11 |
| 1 | 12 |
| 1 | 13 |
| 1 | 14 |
| 1 | 15 |
| 1 | 16 |
| 1 | 17 |
| 1 | 18 |
| 1 | 19 |
| 1 | 20 |
| 1 | 21 |
| 1 | 22 |
| 1 | 23 |
| 1 | 24 |
| 1 | 25 |
| 1 | 26 |
| 1 | 27 |
| 1 | 28 |
| 1 | 29 |
| 1 | 30 |
+----+----+
30 rows in set (0.00 sec)
mysql> ANALYZE TABLE t1;
+------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+---------+----------+----------+
| CONFIRM.t1 | analyze | status | OK |
+------------+---------+----------+----------+
1 row in set (0.03 sec)
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 160 |
+----------+
1 row in set (0.03 sec)
mysql> select count(*) FROM t1 WHERE f2 > 40;
+----------+
| count(*) |
+----------+
| 80 |
+----------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 53
filtered: 100.00
Extra: Using where; Using index for skip scan
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN FORMAT=JSON SELECT f1, f2 FROM t1 WHERE f2 > 40\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "15.92"
},
"table": {
"table_name": "t1",
"access_type": "range",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"f1",
"f2"
],
"key_length": "8",
"rows_examined_per_scan": 53,
"rows_produced_per_join": 53,
"filtered": "100.00",
"using_index_for_skip_scan": true,
"cost_info": {
"read_cost": "10.63",
"eval_cost": "5.30",
"prefix_cost": "15.93",
"data_read_per_join": "848"
},
"used_columns": [
"f1",
"f2"
],
"attached_condition": "(`CONFIRM`.`t1`.`f2` > 40)"
}
}
}
1 row in set, 1 warning (0.00 sec)
mysql>上記の”Using index for skip scan”が利用出来る条件はかなり限られているので、JOINで処理している複数テーブルを1つのテーブルにまとめる事が出来、且つ抽出するデータが全てインデックスに含まれている処理にする事が出来るようなケースであれば、高速化出来る印象を受けました。実際に運用環境で実装て試した訳では無いので、どれだけ改善出来るか不明瞭ではありますが、データをフィルター出来るのであればパフォーマンス改善につながるのでとても有難い改善ではあります。
適用条件
この方法を使用すると、構成された範囲ごとに適格でない行が MySQL によってスキップされるため、アクセスされる行の数が減ります。このスキップスキャンアクセス方法は、次の条件下で適用できます。
| 1 | 表 T には、フォーム ([A_1, …, A_k,] B_1, …, B_m, C [, D_1, …, D_n]) のキー部分を持つ複合インデックスが少なくとも 1 つあります。 キー部分 A および D は空でもかまいませんが、B および C は空でない必要があります。 |
| 2 | クエリーは 1 つのテーブルだけを参照します。 |
| 3 | クエリーでは、GROUP BY または DISTINCT は使用していない。 |
| 4 | クエリーは、インデックス内のカラムのみを参照します。 |
| 5 | A_1、…、A_ k の述語は等価述語であり、定数である必要があります。 これには、IN() 演算子が含まれます。 |
| 6 | クエリーは結合クエリー (OR 条件の AND) である必要があります: (cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR …) AND … |
| 7 | C には範囲条件が必要です。 |
| 8 | D カラムに対する条件は許可されています。 D の条件は、C の範囲条件と組み合せる必要があります。 |
備考：実行プラン、インデックス順序、条件によるオプティマイザーの処理の差異
EXPLAIN ステートメントは、EXPLAIN 出力の一部ではないが、EXPLAIN の後に SHOW WARNINGS ステートメントを発行することで表示できる追加 (「extended」) 情報を生成します。 MySQL 8.0.12 では、拡張情報は SELECT, DELETE, INSERT, REPLACE および UPDATE ステートメントで使用できます。 8.0.12 より前のリリースでは、拡張情報は SELECT ステートメントでのみ使用できます。
8.8.3 拡張 EXPLAIN 出力形式
MySQL5.7の場合
MySQL5.7.18にて挙動を確認。基本的な処理はマニュアル通りで上記と同じ方法で確認しています。
MySQL8.0.30では53行のインデックスデータでの処理が実施されていますが、MySQL5.7.18では160行全てのインデックスデータにアクセスしている事が確認出来ます。
~/win/tmp/rdbms-docker/mysql [10:59:25]> docker-compose -f docker-compose.yml up -d
Pulling db (mysql:5.7.18)...
5.7.18: Pulling from library/mysql
9f0706ba7422: Pull complete
2290e155d2d0: Pull complete
547981b8269f: Pull complete
2c9d42ed2f48: Pull complete
55e3122f1297: Pull complete
abc10bd84060: Pull complete
c0a5ce64f2b0: Pull complete
c4595eab8e90: Pull complete
098988cead35: Pull complete
300ca5fa5eea: Pull complete
43fdc4e3e690: Pull complete
Digest: sha256:d178dffba8d81afedc251498e227607934636e06228ac63d58b72f9e9ec271a6
Status: Downloaded newer image for mysql:5.7.18
Recreating mysql_db_1 ...
Recreating mysql_db_1 ... done
~/win/tmp/rdbms-docker/mysql [11:02:45]>
~/win/tmp/rdbms-docker/mysql [11:51:38]> mysql -h 127.0.0.1 -P 23306 -u root -p POC
Enter password:
ERROR 2026 (HY000): SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol
~/win/tmp/rdbms-docker/mysql [11:51:43]> mysql -h 127.0.0.1 -P 23306 -u root -p --ssl-mode=DISABLED
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE `POC` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
Query OK, 1 row affected (0.02 sec)
mysql> use POC
Database changed
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.18 |
+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
Query OK, 0 rows affected (0.06 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.02 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
NSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 2Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
NSERT INTO t1 SELECT f1, f2 + 40 FROM t1;Query OK, 20 rows affected (0.00 sec)
Records: 20 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
Query OK, 40 rows affected (0.01 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
Query OK, 80 rows affected (0.00 sec)
Records: 80 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 160
filtered: 33.33
Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)
mysql> EXPLAIN EXTENDED SELECT f1, f2 FROM t1 WHERE f2 > 40\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 160
filtered: 33.33
Extra: Using where; Using index
1 row in set, 2 warnings (0.00 sec)
mysql>