MySQL 5.6 Developer試験対策 5 基本的な最適化 -- 最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する -- SELECTステートメントの最適化 (途中まで2)
MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」
https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882education.oracle.com
MySQL 5.6 リファレンスマニュアル
公式: 最適化の概要
問い合わせの最適化に適した索引を認識する。問い合わせの最適化に適した索引を作成する
最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する
SELECT ステートメントの最適化
エンジンコンディションプッシュダウンの最適化
- MySQL ClusterのNDBストレージエンジンでのみ
- 略
インデックスコンディションプッシュダウンの最適化
- 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)
use_index_extensions=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
- 実行計画の変化
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
- innodb_large_prefixが有効ならば3072
IS NULL の最適化
MySQLではインデックスと範囲を使用してNULL含めて検索できるという話
Nested Loop 結合アルゴリズム
- 外側のループで読み取られた行をバッファに積んで、内側のループと比較する
- バッファサイズ:
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)
- 収まらない間は増やすことで速度が向上する
ネストした結合の最適化
- クエリ平坦化の話
- TODO: 復習
外部結合の単純化
RIGHT OUTER JOIN
はパース時にLEFT OUTER JOIN
に書き換わる- TODO: 復習