MySQL 5.6 Developer試験対策 5 基本的な最適化 -- 最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する -- SELECTステートメントの最適化 (途中まで)
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
公式: 最適化の概要
問い合わせの最適化に適した索引を認識する。問い合わせの最適化に適した索引を作成する
最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する
SELECT ステートメントの最適化
SELECT ステートメントの速度
- InnoDB の読み取り専用トランザクションの最適化
- オプティマイザを困らせない
MySQL の WHERE 句の最適化の方法
- 定数テーブル
- 空白/1行のテーブルや、PK/Unique KEYで1行特定されるスカラサブクエリは定数に展開される
- 「定数伝播フェーズ」
- 空白/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)