SQL：実行計画

EXPLAIN <クエリ>;

実行するクエリの実行計画を確認する。

• 目的：クエリが高速かどうか確認するため

mysql> EXPLAIN SELECT
    ->   COUNT(*)
    -> FROM
    ->   employees\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 299246
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

EXPLAIN ANALYZE <クエリ>;

• 実行結果を分析するために使う

mysql> EXPLAIN SELECT * FROM salaries WHERE salary = 66596 LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2746427
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN ANALYZE SELECT * FROM salaries WHERE salary = 66596 LIMIT 10\G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 10 row(s)  (cost=279820.75 rows=10) (actual time=1.328..1034.066 rows=10 loops=1)
    -> Filter: (salaries.salary = 66596)  (cost=279820.75 rows=274643) (actual time=1.042..1033.686 rows=10 loops=1)
        -> Table scan on salaries  (cost=279820.75 rows=2746427) (actual time=0.819..837.084 rows=444460 loops=1)

1 row in set (1.04 sec)

# 別例

mysql> EXPLAIN
    -> SELECT *
    -> FROM employees
    -> WHERE birth_date = "1964-06-02"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ALL  ※※※ここ！！！
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 299246   ※※※ここ！！！
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN ANALYZE
    -> SELECT *
    -> FROM employees
    -> WHERE birth_date = "1964-06-02"\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (employees.birth_date = DATE'1964-06-02')  
(cost=30156.85 rows=29925)
(actual time=1.736..613.382 rows=57 loops=1)
    -> Table scan on employees  (cost=30156.85 rows=299246) 
(actual time=1.377..503.530 rows=300024 loops=1)

mysql> SHOW INDEX FROM employees\G
*************************** 2. row ***************************
        Table: employees
   Non_unique: 1
     Key_name: bd_index
 Seq_in_index: 1
  Column_name: birth_date
    Collation: A
  Cardinality: 4758
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.03 sec)

mysql> EXPLAIN
    -> SELECT *
    -> FROM employees
    -> WHERE birth_date = "1964-06-02"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref   ※※※ここ！！！
possible_keys: bd_index
          key: bd_index
      key_len: 3
          ref: const
         rows: 57   ※※※ここ！！！
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.04 sec)

mysql> EXPLAIN ANALYZE
    -> SELECT *
    -> FROM employees
    -> WHERE birth_date = "1964-06-02"\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on employees using bd_index 
(birth_date=DATE'1964-06-02')  
(cost=19.95 rows=57) 
(actual time=5.153..5.759 rows=57 loops=1)

1 row in set (0.03 sec)