勉強日記

チラ裏

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

dev.mysql.com


公式: 最適化の概要

dev.mysql.com

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

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

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

SELECT ステートメントの速度

MySQL の WHERE 句の最適化の方法

  • 定数テーブル
    • 空白/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 < 5id > 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)