勉強日記

チラ裏

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 リファレンスマニュアル

dev.mysql.com


公式: 最適化の概要

dev.mysql.com

問い合わせの最適化に適した索引を認識する。問い合わせの最適化に適した索引を作成する

最適でないSQLコマンドを認識する。最適でないSQLコマンドを修正する

SELECT ステートメントの最適化

エンジンコンディションプッシュダウンの最適化

  • MySQL ClusterのNDBストレージエンジンでのみ

インデックスコンディションプッシュダウンの最適化

dev.mysql.com

  • 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
  • 実行計画の変化
    • セカンダリインデックス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)
  • 拡張インデックスの長さの上限は通常のインデックスと同じ

IS NULL の最適化

dev.mysql.com

MySQLではインデックスと範囲を使用してNULL含めて検索できるという話

Nested Loop 結合アルゴリズム

dev.mysql.com

  • 外側のループで読み取られた行をバッファに積んで、内側のループと比較する
    • バッファサイズ: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)

  • 収まらない間は増やすことで速度が向上する

ネストした結合の最適化

dev.mysql.com

  • クエリ平坦化の話
  • TODO: 復習

外部結合の単純化

dev.mysql.com

  • RIGHT OUTER JOINはパース時にLEFT OUTER JOINに書き換わる
  • TODO: 復習

Multi-Range Read の最適化

  • Disk-Sweep Multi-Range Read (MRR)
    • セカンダリインデックスでの範囲スキャン時、テーブルがキャッシュに乗っていないと、ランダムアクセスが頻発する
    • 最初にインデックスだけをスキャンし、該当する行のキーを収集することで、ランダムディスクアクセスの回数を軽減し、ベーステーブルデータの順次スキャンを増やす
      • インデックスは順番になっているので順次スキャンで取得できる
      • これをデータ行ID順にソートする
      • これを用いてデータ業を順次スキャンで取得する