MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
[https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882:embed:cite]
MySQL 5.6 リファレンスマニュアル
[https://dev.mysql.com/doc/refman/5.6/ja/:embed:cite]
公式: 最適化の概要
[https://dev.mysql.com/doc/refman/5.6/ja/optimize-overview.html:embed:cite]
問い合わせの最適化に適した索引を認識する。問い合わせの最適化に適した索引を作成する
最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する
SELECT ステートメントの最適化
SELECT ステートメントの速度
-
- トランザクションID(
TRX_ID
) - 書き込みまたはロック読み取りにのみ必要
- MySQL 5.6.4 ~は読み取り専用トランザクションでこのオーバーヘッドを回避できる
START TRANSACTION READ ONLY
- トランザクションID(
- オプティマイザを困らせない
MySQL の WHERE 句の最適化の方法
-
定数テーブル
- 空白/1行のテーブルや、PK/Unique KEYで1行特定されるスカラサブクエリは定数に展開される
- 「定数伝播フェーズ」
- 不要なかっこの削除とかはしなくていい
-
【補】インデックスが絡むと式変形が必要なことも
- 複合インデックス使用時、カラムの順番は重要
col + 1
はインデックス効かなかったりする
ORDER BY
句と別のGROUP BY
句があると、一時テーブルが作成される:
CREATE TABLE tbl(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
PRIMARY KEY(id)
);
EXPLAIN SELECT * FROM tbl GROUP BY name ORDER BY name\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using temporary; Using filesort
1 row in set (0.00 sec)
ERROR:
No query specified
これ
Extra: Using temporary; Using filesort
ORDER BY
またはGROUP BY
に結合キュー内の最初のテーブルと異なるテーブルのカラムが含まれている場合、 一時テーブルが作成される- 「結合キュー内の最初のテーブル」 = 駆動表
- 例: 駆動表の主キー(B+木インデックス付きのカラム)でソート
EXPLAIN SELECT * FROM tbl t1
JOIN tbl t2
ON t1.id = t2.id
ORDER BY t1.id;
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+
| 1 | SIMPLE | t1 | index | PRIMARY | PRIMARY | 4 | NULL | 1 | NULL |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | sample.t1.id | 1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+-------+
2 rows in set (0.00 sec)
-
例: 駆動表のインデックスのないカラムでソート
- ファイルソート
EXPLAIN SELECT * FROM tbl t1
JOIN tbl t2
ON t1.id = t2.id
ORDER BY t1.name;
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+----------------+
| 1 | SIMPLE | t1 | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using filesort |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | sample.t1.id | 1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+----------------+
2 rows in set (0.00 sec)
-
例: 駆動表でない表(内部表)の主キー
- 一時テーブルできる
EXPLAIN SELECT * FROM tbl t1
JOIN tbl t2
ON t1.id = t2.id
ORDER BY t2.id;
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+---------------------------------+
| 1 | SIMPLE | t1 | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | sample.t1.id | 1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+---------------------------------+
2 rows in set (0.00 sec)
-
例: 駆動表でない表(内部表)のインデックスのないカラム
- 一時テーブルできる
EXPLAIN SELECT * FROM tbl t1
JOIN tbl t2
ON t1.id = t2.id
ORDER BY t2.name;
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+---------------------------------+
| 1 | SIMPLE | t1 | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | sample.t1.id | 1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+--------------+------+---------------------------------+
2 rows in set (0.00 sec)
- カヴァリングインデックス
EXPLAIN SELECT id FROM tbl t1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 1
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
CREATE TABLE tbl2 (
id INT NOT NULL AUTO_INCREMENT,
num INT,
name VARCHAR(191),
PRIMARY KEY(id),
UNIQUE KEY(num),
UNIQUE KEY(name)
);
MySQL は、インデックス設定されたカラムが数値であるとして、 インデックスツリーのみを使用して、次のクエリーを解決します。
- VARCHAR(191)とかでもUsing indexになる…謎
EXPLAIN SELECT name FROM tbl2 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl2
type: index
possible_keys: NULL
key: name
key_len: 194
ref: NULL
rows: 1
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
range の最適化
- B+木インデックスが効くよという話
EXPLAIN SELECT * FROM tbl WHERE id < 10;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | tbl | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
-
type: range
- B+木インデックスの範囲検索が効いている、の意
-
導出すると効かなくなる
- MySQL8.0〜の式インデックスを使えばこの限りではない
EXPLAIN SELECT * FROM tbl WHERE id + 1 < 11;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tbl | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
- ORの場合、先頭の条件にインデックスが効かないと、後ろも効かない
EXPLAIN SELECT * FROM tbl WHERE id + 1 < 11 OR id < 10;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tbl | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
-
【疑問点】NOTで効くのなんで?
id < 5
とid > 5
に展開されて片方だけ効いているのだろうか?
EXPLAIN SELECT * FROM tbl WHERE id <> 5;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | tbl | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
- UNIONだと一時テーブルが生じる
EXPLAIN (SELECT * FROM tbl WHERE id < 5) UNION ALL (SELECT * FROM tbl WHERE id > 5);
+----+--------------+------------+-------+---------------+---------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+------+------+-----------------+
| 1 | PRIMARY | tbl | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where |
| 2 | UNION | tbl | range | PRIMARY | PRIMARY | 4 | NULL | 1 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+-------+---------------+---------+---------+------+------+-----------------+
3 rows in set (0.00 sec)
-
各範囲の行数の見積もり戦略
- インデックスダイブ
- 昔から利用できる方法
- 等価範囲ごとに2つの「ダイブ」を作る
-
等価範囲とは
col_name IN (10, 20, 30)
なら3つの等価範囲
- インデックス統計
- >=5.6.5から利用できる方法
eq_range_index_dive_limit
変数で切り替える等価範囲の数を設定可能N + 1
にすると最大N個の等価範囲の比較にインデックスダイブ使用0
にするとインデックス統計無効化
インデックスマージの最適化
CREATE TABLE tbl3 (
num1 INT,
num2 INT,
INDEX (num1),
INDEX (num2)
);
INSERT INTO tbl3 VALUES (1,1),(2,1),(1,2),(2,2);
INSERT INTO tbl3 VALUES (1,1),(2,1),(1,2),(2,2);
INSERT INTO tbl3 VALUES (1,1),(2,1),(1,2),(2,2);
INSERT INTO tbl3 VALUES (1,1),(2,1),(1,2),(2,2);
- こういうクエリ
Using union(num1,num2)
EXPLAIN SELECT * FROM tbl3 WHERE num1 = 10 OR num2 = 20;
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
| 1 | SIMPLE | tbl3 | index_merge | num1,num2 | num1,num2 | 5,5 | NULL | 2 | Using union(num1,num2); Using where |
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+-------------------------------------+
1 row in set (0.00 sec)
Using intersect(num1,num2)
EXPLAIN SELECT * FROM tbl3 WHERE num1 = 1 AND num2 = 2;
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+------------------------------------------------------+
| 1 | SIMPLE | tbl3 | index_merge | num1,num2 | num1,num2 | 5,5 | NULL | 8 | Using intersect(num1,num2); Using where; Using index |
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+------------------------------------------------------+
1 row in set (0.01 sec)
-
Using sort_union(num1,num2)
- 行を返す前にまずすべての行IDをフェッチし、それらをソートする必要がある
EXPLAIN SELECT * FROM tbl3 WHERE (num1 > 10 OR num2 = 20) AND num1 + 1 < 20;
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+------------------------------------------+
| 1 | SIMPLE | tbl3 | index_merge | num1,num2 | num1,num2 | 5,5 | NULL | 2 | Using sort_union(num1,num2); Using where |
+----+-------------+-------+-------------+---------------+-----------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)