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 ステートメントの最適化
エンジンコンディションプッシュダウンの最適化
- MySQL ClusterのNDBストレージエンジンでのみ
- 略
インデックスコンディションプッシュダウンの最適化
[https://dev.mysql.com/doc/refman/5.6/ja/index-condition-pushdown-optimization.html:embed:cite]
- ICP: Index Condition Pushdown
- 使用可能なストレージエンジン: InnoDB, MyISAM
-
カヴァリングインデックスのWHERE版みたいなやつ
- インデックスからのフィールドだけを使用してWHERE条件の部分を評価できる場合、MySQLサーバーはWHERE条件の部分をストレージエンジンにプッシュダウンする
-
cf. ICPを利用しない場合、テーブルから行を取得して、そのデータでWHEREの条件評価する
- プッシュされたインデックス条件を評価し、満たされている行のみテーブルから読み取る
optimizer_switch
変数のindex_condition_pushdown
フラグで有効化
インデックス拡張の使用
CREATE TABLE t1 (
i1 INT NOT NULL DEFAULT 0,
i2 INT NOT NULL DEFAULT 0,
d DATE DEFAULT NULL,
PRIMARY KEY (i1, i2),
INDEX k_d (d)
) ENGINE = InnoDB;
INSERT INTO t1 VALUES
(1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
(1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
(1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
(2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
(2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
(3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
(3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
(3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
(4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
(4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
(5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
(5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
(5, 5, '2002-01-01');
EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: PRIMARY,k_d
key: k_d
key_len: 8
ref: const,const
rows: 1
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
- 複合主キーインデックス
(i1,i2)
のうち片方i1
(4バイト)と、セカンダリインデックスk_d(d)
(3バイト、アライメントがあるから4)とで8バイトの館長インデックスが考慮されている
possible_keys: PRIMARY,k_d
key: k_d
key_len: 8
ref: const,const
SHOW STATUS
でも違いを確認できる
FLUSH TABLE t1;
FLUSH STATUS;
SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 1 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.00 sec)
- インデックス拡張はデフォルトでON
SELECT @@GLOBAL.optimizer_switch;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.optimizer_switch |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
useindexextensions=on
- 無効にしてみる
SET optimizer_switch = 'use_index_extensions=off';
EXPLAIN
,SHOW STATUS
再確認
EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: PRIMARY,k_d
key: k_d
key_len: 4
ref: const
rows: 5
Extra: Using where; Using index
1 row in set (0.00 sec)
ERROR:
No query specified
-
実行計画の変化
- セカンダリインデックス
k_d(d)
しか効かなくなっている - rows(InnoDBが結果を生成するために調査する必要のある行数)が1から5に増えた
Using where
… テーブル行をフェッチしてWHERE条件を評価している、の意味- ICPが効いていればこうならない
- セカンダリインデックス
SHOW STATUS
も
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 5 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.00 sec)
-
拡張インデックスの長さの上限は通常のインデックスと同じ
- 767
- innodblargeprefixが有効ならば3072
IS NULL の最適化
[https://dev.mysql.com/doc/refman/5.6/ja/is-null-optimization.html:embed:cite]
MySQLではインデックスと範囲を使用してNULL含めて検索できるという話
Nested Loop 結合アルゴリズム
[https://dev.mysql.com/doc/refman/5.6/ja/nested-loop-joins.html:embed:cite]
-
外側のループで読み取られた行をバッファに積んで、内側のループと比較する
- バッファサイズ:
join_buffer_size
(バイト) - 結合に関連するカラムだけが格納される
- バッファサイズ:
SELECT @@GLOBAL.join_buffer_size;
+---------------------------+
| @@GLOBAL.join_buffer_size |
+---------------------------+
| 262144 |
+---------------------------+
1 row in set (0.00 sec)
- デフォルト256KB
mysql> SET join_buffer_size=0;
SET join_buffer_size=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS ;
SHOW WARNINGS ;
+---------+------+-------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1292 | Truncated incorrect join_buffer_size value: '0' |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)
- 最小128B
mysql> SELECT @@LOCAL.join_buffer_size;
SELECT @@LOCAL.join_buffer_size;
+--------------------------+
| @@LOCAL.join_buffer_size |
+--------------------------+
| 128 |
+--------------------------+
1 row in set (0.00 sec)
- 連番投入用のビュー作成
CREATE TABLE digits (num INT PRIMARY KEY);
INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE VIEW seq AS
SELECT d1.num * 100 + d2.num * 10 + d3.num AS num
FROM digits d1, digits d2, digits d3
ORDER BY num;
- 連番のJOIN
CREATE TABLE a(col int);
CREATE TABLE b(col int);
INSERT INTO a SELECT num FROM seq;
INSERT INTO b SELECT num FROM seq;
EXPLAIN SELECT * FROM a JOIN b b1 USING(col) ;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 1000 | NULL |
| 1 | SIMPLE | b1 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)
Using join buffer (Block Nested Loop)
- 収まらない間は増やすことで速度が向上する
ネストした結合の最適化
[https://dev.mysql.com/doc/refman/5.6/ja/nested-join-optimization.html:embed:cite]
- クエリ平坦化の話
- TODO: 復習
外部結合の単純化
[https://dev.mysql.com/doc/refman/5.6/ja/outer-join-simplification.html:embed:cite]
RIGHT OUTER JOIN
はパース時にLEFT OUTER JOIN
に書き換わる- TODO: 復習
Multi-Range Read の最適化
-
Disk-Sweep Multi-Range Read (MRR)
- セカンダリインデックスでの範囲スキャン時、テーブルがキャッシュに乗っていないと、ランダムアクセスが頻発する
- 【補】セカンダリゆえインデックスのクラスタ性が低い、ということか
- 最初にインデックスだけをスキャンし、該当する行のキーを収集することで、ランダムディスクアクセスの回数を軽減し、ベーステーブルデータの順次スキャンを増やす
- インデックスは順番になっているので順次スキャンで取得できる
- これをデータ行ID順にソートする
- これを用いてデータ業を順次スキャンで取得する