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 ステートメントの最適化
Multi-Range Read の最適化
-
Disk-Sweep Multi-Range Read (MRR)
- セカンダリインデックスでの範囲スキャン時、テーブルがキャッシュに乗っていないと、ランダムアクセスが頻発する
- 【補】セカンダリゆえインデックスのクラスタ性が低い、ということか
- 最初にインデックスだけをスキャンし、該当する行のキーを収集することで、ランダムディスクアクセスの回数を軽減し、ベーステーブルデータの順次スキャンを増やす
- インデックスは順番になっているので順次スキャンで取得できる
- これをデータ行ID順にソートする
- これを用いてデータ業を順次スキャンで取得する
- デフォルト有効、コストベース
mrr=on,mrr_cost_based=on,
- 試してみる
CREATE TABLE tbl(
col1 INT,
col2 INT,
content TEXT,
INDEX (col1),
INDEX (col2)
);
INSERT INTO tbl VALUES(1,1,'hoge'),(2,2,'piyo');
EXPLAIN SELECT * FROM tbl WHERE col1 < 1 AND col2 < 1;
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------+
| 1 | SIMPLE | tbl | range | col1,col2 | col1 | 5 | NULL | 1 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------+
1 row in set (0.00 sec)
- MRR訊いてない
- コストベースをやめ、常にMRRを試みてみる
SET optimizer_switch='mrr_cost_based=off';
- 効いた
EXPLAIN SELECT * FROM tbl WHERE col1 < 1 AND col2 < 1;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+
| 1 | SIMPLE | tbl | range | col1,col2 | col1 | 5 | NULL | 1 | Using index condition; Using where; Using MRR |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------------------+
1 row in set (0.00 sec)
ORDER BY の最適化
- B+木インデックスを利用してソート要らず
DESC tbl;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| col1 | int(11) | YES | MUL | NULL | |
| col2 | int(11) | YES | MUL | NULL | |
| content | text | YES | | NULL | |
+---------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
EXPLAIN SELECT col1 FROM tbl ORDER BY col1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
type: index
possible_keys: NULL
key: col1
key_len: 5
ref: NULL
rows: 2
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
Extra: Using index
- こういうのは駄目:
EXPLAIN SELECT ABS(col1) AS col1 FROM tbl ORDER BY col1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl
type: index
possible_keys: NULL
key: col1
key_len: 5
ref: NULL
rows: 2
Extra: Using index; Using filesort
1 row in set (0.00 sec)
ERROR:
No query specified
Using index; Using filesort
- select listはカヴァリングインデックス、ソートはfilesortの意
GROUP BY の最適化
- インデックスを利用して一時テーブル作成を回避する
- すべての
GROUP BY
句が同じインデックスから属性を参照し、順番に使用していること
CREATE TABLE multbl (
col1 INT,
col2 INT,
content TEXT,
PRIMARY KEY(col1, col2)
);
- 複合インデックス
(col1,col2)
- 全使用
EXPLAIN SELECT col1,col2 FROM multbl GROUP BY col1,col2 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: multbl
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 1
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
col1
のみ使用
EXPLAIN SELECT col1,col2 FROM multbl GROUP BY col1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: multbl
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 1
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
col2
だけやcol2,col1
のみ使用だとソートが生じてしまう
EXPLAIN SELECT col1,col2 FROM multbl GROUP BY col2 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: multbl
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 1
Extra: Using index; Using temporary; Using filesort
1 row in set (0.00 sec)
ERROR:
No query specified
EXPLAIN SELECT col1,col2 FROM multbl GROUP BY col2,col1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: multbl
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 1
Extra: Using index; Using filesort
1 row in set (0.00 sec)
ERROR:
No query specified
ルースインデックススキャン
- 集約関数がMINまたはMAXの場合、その算出もインデックスを使えて速い、という話
- Extraが
Using index for group-by
になるらしい - 再現できなかった…
タイトインデックススキャン
複合インデックス(col1,col2,col3)
において、GRUOP BY col1,col3
はインデックスによる一時テーブル作成回避が効かない
EXPLAIN SELECT col1,MIN(col2),col3 FROM multbl GROUP BY col1,col3;
+----+-------------+--------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | multbl | index | PRIMARY | PRIMARY | 12 | NULL | 1 | Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+---------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)
col1,col3
とギャップがあっても、WHEREでギャップが埋まっていれば一時テーブルを回避できる
EXPLAIN SELECT col1,col2,col3 FROM multbl WHERE col2=1 GROUP BY col1,col3;
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | multbl | index | PRIMARY | PRIMARY | 12 | NULL | 1 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)