MySQL 5.6 Developer試験対策 5 基本的な最適化 -- 最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する -- SELECTステートメントの最適化 (途中まで3)
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
公式: 最適化の概要
最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する
SELECT ステートメントの最適化
Multi-Range Read の最適化
- Disk-Sweep Multi-Range Read (MRR)
- デフォルト有効、コストベース
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)
