alivelime.github.io

EXPLAINで見る

パフォーマンス改善入門

自己紹介

PF課金基板(購入チーム)
マネー統合で購入APIの開発やDBまわりを担当しました
100GBのデータを40時間掛けてコンバートしてました
C++ / D言語好き CodeIQやpaizaハッカソンやってます

今回紹介する話

MySQL5.5/5.6 Innodbが対象
マネー統合案件を通してよくあるパターンをまとめました
テーブル構造などは大幅にデフォルメしてあります。
設計関係の話はありません。
EXPLAINを通しパフォーマンス向上についてお話します
基礎的なパターンから実際のやや複雑なクエリまで

EXPLAIN? なにそれ美味しいの?

実は2年くらい前まで使ったことありませんでした…。

…というわけでマニュアル読みましょう!

EXPLAINとは…

MySQL 5.6 リファレンス・マニュアル
http://dev.mysql.com/doc/refman/5.6/ja/explain.html

EXPLAIN は、クエリー実行計画 (つまりMySQLがクエリーをどのように実行するかの説明) を取得するために使用されます。

とありますが、構文とかいろいろ小難しいことが

書いてあってよくわかりません。

…どうやって使えばよいのか?

使い方はググれば沢山!

「mysql explain」でググると沢山解説ページが出てきます。

このへん見れば大体分かります。

…EXPLAINの要点をまとめると

EXPLAINの要点

SELECTクエリの頭にEXPLAINとつけるだけ!
どれくらい時間が掛かりそうか計算できる!
インデックスが正しく使われているかチェックする!
パフォーマンスを上げるには必須!

…じゃあ実際にやってみよう!

とりあえずEXPLAIN

あるテーブルを全部selectしてみる。。

mysql> explain select * from purchase;

+----+-------------+----------+------+---------------+------+---------+------+-----------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows      | Extra |
+----+-------------+----------+------+---------------+------+---------+------+-----------+-------+
|  1 | SIMPLE      | purchase | ALL  | NULL          | NULL | NULL    | NULL | 180298799 | NULL  |
+----+-------------+----------+------+---------------+------+---------+------+-----------+-------+

とりあえずここを見る

まずはrows、対象レコードは何件か?
- 1億8千万レコード近くある
次にtype、どのように探すか?
- ALL…テーブルのフルスキャン
possible_keys、どのインデックスが使えそうか
- NULL…使えるインデックスがない
key、どのインデックスを使うか
- NULL…whereを特に指定していない。
テーブル全部読み込んでいるのがわかる

‥typeとはなんぞや?

typeの見方

種類説明早さ const PRIMARY、UNIQUEのルックアップ最速 eq_ref JOINでPRIMARY、UNIQUEが使われる最速 ref ユニークでないインデックス普通 range インデックスを用いた範囲検索普通 index フルインデックススキャン遅い ALL フルテーブルスキャンとても遅い

…インデックスで検索した場合のEXPLAIN

インデックスがある場合

プライマリキー

mysql> EXPLAIN SELECT * FROM purchase WHERE purchase_id = 310000000;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | purchase | const | PRIMARY       | PRIMARY | 8       | const |    1 | NULL  |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+

type … const、つまり早い
rows … 1行だけ
key … プライマリキー

セカンダリINDEX

mysql> EXPLAIN SELECT * FROM purchase WHERE basket_service_type = 'admin';
+----+-------------+----------+------+---------------+--------------+---------+-------+---------+-----------------------+
| id | select_type | table    | type | possible_keys | key          | key_len | ref   | rows    | Extra                 |
+----+-------------+----------+------+---------------+--------------+---------+-------+---------+-----------------------+
|  1 | SIMPLE      | purchase | ref  | basket_serv.  | basket_serv. | 258     | const | 3068718 | Using index condition |
+----+-------------+----------+------+---------------+--------------+---------+-------+---------+-----------------------+

type … ref、ユニークでないインデックス
key … 検索したいインデックスが使われている
rows … 300万レコード

NULLも行けます(mysqlのみ)

mysql> explain select * from purchase where grant_id is null;
+----+-------------+----------+------+---------------+--------------+---------+-------+----------+-----------------------+
| id | select_type | table    | type | possible_keys | key          | key_len | ref   | rows     | Extra                 |
+----+-------------+----------+------+---------------+--------------+---------+-------+----------+-----------------------+
|  1 | SIMPLE      | purchase | ref  | grant_id      | grant_id     | 9       | const | 88052842 | Using index condition |
+----+-------------+----------+------+---------------+--------------+---------+-------+----------+-----------------------+

type … ref、ユニークでないインデックス
key … 検索したいインデックス
row … 880万レコード

…ではインデックスが使われないパターンは?

1.インデックスが貼ってない

そもそもインデックスが貼っていない場合

mysql> explain select * from purchase where tax = 0;
+----+-------------+----------+------+---------------+------+---------+------+-----------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows      | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+-----------+-------------+
|  1 | SIMPLE      | purchase | ALL  | NULL          | NULL | NULL    | NULL | 180298726 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+-----------+-------------+

インデックスが貼ってないとどうしようもない
MIN() / MAX()を使うとさらに激重に
必要ならインデックス貼りましょう

2.型が間違っている

transaction_idにインデックスは貼ってあるけれど‥

mysql> explain select * from settlement where transaction_id = 123456789;
+----+-------------+------------+------+----------------+------+---------+------+-----------+-------------+
| id | select_type | table      | type | possible_keys  | key  | key_len | ref  | rows      | Extra       |
+----+-------------+------------+------+----------------+------+---------+------+-----------+-------------+
|  1 | SIMPLE      | settlement | ALL  | transaction_id | NULL | NULL    | NULL | 181576359 | Using where |
+----+-------------+------------+------+----------------+------+---------+------+-----------+-------------+

possible_keysに候補が出ている!
しかしkeyはNULLになっている
実はtransaction_idは文字列(varchar)

…正しい書き方は?

文字列と比較する!

mysql> explain select * from settlement where transaction_id =　'123456789';
+----+-------------+------------+------+----------------+----------------+---------+-------+------+-----------------------+
| id | select_type | table      | type | possible_keys  | key            | key_len | ref   | rows | Extra                 |
+----+-------------+------------+------+----------------+----------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | settlement | ref  | transaction_id | transaction_id | 82      | const |    1 | Using index condition |
+----+-------------+------------+------+----------------+----------------+---------+-------+------+-----------------------+

possible_keysとkeyが正しく選択されている!
一般的には数値でもとりあえず'囲んでおく'

3.左辺で計算しちゃダメ

昨日までのデータをちょっと見たい‥

EXPLAIN SELECT * FROM settlement
WHERE
    TIMEDIFF(now(),  settlement_date) < 3600 * 24;
+----+-------------+------------+------+---------------+------+---------+------+-----------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows      | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+-----------+-------------+
|  1 | SIMPLE      | settlement | ALL  | NULL          | NULL | NULL    | NULL | 181738042 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+-----------+-------------+

1レコードずつ計算してから now()と比較するので遅い
正しい書き方は…

右辺で計算する!

EXPLAIN SELECT * FROM settlement
WHERE settlement_date > now() - interval 1 day;
+----+-------------+------------+-------+----------------+----------------+---------+------+--------+-----------------------+
| id | select_type | table      | type  | possible_keys  | key            | key_len | ref  | rows   | Extra                 |
+----+-------------+------------+-------+----------------+----------------+---------+------+--------+-----------------------+
|  1 | SIMPLE      | settlement | range | settlement_date | settlement_date | 5       | NULL | 338668 | Using index condition |
+----+-------------+------------+-------+----------------+----------------+---------+------+--------+-----------------------+

インデックスが一つの場合は以上!

indexが複数ある場合

検索条件が一つの場合はインデックスが使われるかどうかだけ気をつければ良い。
しかし、AND検索ではインデックスは一つしか使われない。(ここ大事)
possible_keysとkeyの見方に注意!
目安はcardinality!
カーディナリティとは‥?

cardinalityとは何か?

タウンページで例えると‥

ページ番号　→　ほとんど絞り込める
- カーディナリティが高い!
都道府県名　→　探すのが大変!
- カーディナリティが低い
カーディナリティが高いものが優先
DBではどうなるか‥

実際にSQLで確認

やりたいこと

全ての会員の購入履歴がpurchaseテーブルに入っている
ある会員の購入成功したデータだけ取り出したい
会員IDとステータスにはインデックスが貼ってある
インデックスはどう使われる‥?

インデックスを予測する

ステータスが成功のもので絞る?
- ステータスは以下の4種類
  - success failure processing pending
- データ件数が多すぎる (カーディナリティが低い)
- status = 'success' のものは 150000000レコードもある!
会員IDで絞る?
- 一人の会員は最大でも2万件 (カーディナリティが高い)
会員IDのインデックスが使われるはず‥

EXPLAINで確認する

mysql> explain select * from purchase where status = 'success' and member_id = 'お得意様のID';
+----+-------------+----------+------+---------------------------+--------------+---------+-------+-------+------------------------------------+
| id | select_type | table    | type | possible_keys             | key          | key_len | ref   | rows  | Extra                              |
+----+-------------+----------+------+---------------------------+--------------+---------+-------+-------+------------------------------------+
|  1 | SIMPLE      | purchase | ref  | member_id, status  | member_id | 48      | const | 46552 | Using index condition; Using where |
+----+-------------+----------+------+---------------------------+--------------+---------+-------+-------+------------------------------------+

possible_keysにはstatusもある
インデックスは member_idしか使われていない。
ではステータスにindex貼るのは無駄なのか‥?

カーディナリティの低いカラムに

インデックスを貼る意味

特殊な条件を絞り込むのに使う
今回の例では"処理中"のデータがほしい場合
ステータスが"処理中"は2億レコード中数件程度
会員IDでは25000件
処理中データを探すときはステータス < 会員ID
‥今度はステータスのインデックスが使われるはず

カーディナリティが低くても

意味のあるインデックス

mysql> explain select * from purchase where status = 'processing' and member_id = 'お得意様';
+----+-------------+----------+------+---------------------------+--------------+---------+-------+------+------------------------------------+
| id | select_type | table    | type | possible_keys             | key          | key_len | ref   | rows | Extra                              |
+----+-------------+----------+------+---------------------------+--------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | purchase | ref  | member_id,status | status | 1       | const |   16 | Using index condition; Using where |
+----+-------------+----------+------+---------------------------+--------------+---------+-------+------+------------------------------------+

今度はちゃんとステータスが使われている!
カーディナリティが低くても該当件数が少なければ有用
‥ちなみにOR検索のときは?

ORのときはちゃんと

複数のインデックスが使われる

mysql> explain select * from purchase where service_id = 0 or basket_service_type = 'admin';
+----+-------------+----------+-------------+---------------------------+---------------------------+---------+------+---------+-----------------------------------------------------+
| id | select_type | table    | type        | possible_keys             | key                       | key_len | ref  | rows    | Extra                                               |
+----+-------------+----------+-------------+---------------------------+---------------------------+---------+------+---------+-----------------------------------------------------+
| . | . | purchase | index_merge | basket_service_type,service_id | service_id,basket_service_type | 4,258   | NULL | 3114932 | Using
+----+-------------+----------+-------------+---------------------------+---------------------------+---------+------+---------+-----------------------------------------------------+

key … 両方のインデックスが使われている
typeはindex_margeになっている
mysql 4.x の時代ではこれができなかったらしい
‥前半のまとめ

前半のまとめ

AND検索ではインデックスはテーブルひとつにつき一つ
OR検索ではうまく複数インデックスが使われる
カーディナリティの高いものが使われやすい
見積件数の少ないインデックスが使われる
cardinaryが低いインデックスは結局使われないことも
‥実践編へ

ちょっと実践編

インデックスが効くものとそうでないもの

union
group by
order by
limit
‥クエリの組み立てを通して確認!

2つのテーブルをLEFT JOINするクエリ

ある会員の商品とDMMポイントの返金リストを作ります

テーブル概要

この3つのテーブルから履歴を作ります
purchase ‥ 180,000,000レコード
charge ‥ 30,000,000レコード
cancel ‥ 600,000レコード

left joinでjoin先のテーブルを

キーにするとindex効かない

SELECT
     COALESCE(p.purchase_id, ch.charge_id) as 注文ID
    ,COALESCE(p.amount, ch.sales_price) AS 金額
FROM
    cancel as c
    LEFT JOIN purchase AS p ON (c.order_id = p.purchase_id)
    LEFT JOIN charge AS ch ON (c.order_id = ch.charge_id)
WHERE
    (p.member_id = 'ある会員' OR ch.member_id = 'ある会員')
AND c.status = 'success'

EXPLAINの結果

+----+-------------+-------+--------+---------------+------------+---------+---------------------+--------+-----------------------+
| id | select_type | table | type   | possible_keys | key        | key_len | ref                 | rows   | Extra                 |
+----+-------------+-------+--------+---------------+------------+---------+---------------------+--------+-----------------------+
|  1 | SIMPLE      | c     | ref    | status    | status | 1       | const               | 302929 | Using index condition |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY    | 8       | purchase.c.order_id |      1 | NULL                  |
|  1 | SIMPLE      | ch    | eq_ref | PRIMARY       | PRIMARY    | 8       | purchase.c.order_id |      1 | Using where           |
+----+-------------+-------+--------+---------------+------------+---------+---------------------+--------+-----------------------+

会員IDのインデックスが使われていない!
…クエリを修正

少し改良したクエリ

SELECT
     COALESCE(p.purchase_id, ch.charge_id) as 注文ID
    ,COALESCE(p.amount, ch.sales_price) AS 金額
FROM
    cancel as c
    LEFT JOIN (SELECT * FROM purchase WHERE member_id = '会員ID') AS p ON (c.order_id = p.purchase_id)
    LEFT JOIN (SELECT * FROM charge WHERE member_id = '会員ID') AS ch ON (c.order_id = ch.charge_id)
AND c.status = 'success'

LEFT JOINの中で先にデータを作る
でも2つともLEFT JOINなので両方ともNULLのデータが出来る…

EXPLAIN

+----+-------------+------------+------+---------------+--------------+---------+---------------------+--------+-----------------------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref                 | rows   | Extra                 |
+----+-------------+------------+------+---------------+--------------+---------+---------------------+--------+-----------------------+
|  1 | PRIMARY     | c          | ALL  | NULL          | NULL         | NULL    | NULL                | 586057 | NULL                  |
|  1 | PRIMARY     | <derived2> | ref  | <auto_key0>   | <auto_key0>  | 8       | purchase.c.order_id |     10 | NULL                  |
|  1 | PRIMARY     | <derived3> | ref  | <auto_key0>   | <auto_key0>  | 8       | purchase.c.order_id |     10 | Using where           |
|  3 | DERIVED     | charge     | ref  | member_id    | member_id   | 48      | const               |    923 | Using index condition |
|  2 | DERIVED     | purchase   | ref  | member_id  | member_id | 48      | const               |   1596 | Using index condition |
+----+-------------+------------+------+---------------+--------------+---------+---------------------+--------+-----------------------+

会員IDのインデックスは使われている
1行目を見ると、cancelテーブル全部スキャンしている
一時テーブルが作られるためeq_refにならない
‥UNIONに書き換える

unionに書き換える

ダメな例

SELECT
     order_id as 注文ID
    ,price AS 金額
FROM
    (
              SELECT /* 必要なカラム */ FROM purchase as p inner join cancel as c ON p.purchase_id = c.cancel_id
    UNION ALL SELECT /* 必要なカラム */ FROM charge as ch inner join cancel as c ON ch.charge_id = c.cancel_id
    ) AS tmp
WHERE
        tmp.member_id = 'ある会員ID'
    and tmp.status = 'success';

外側のwhereはダメ
インデックスが効かない

EXPLAIN

+----+--------------+------------+--------+-------------------+-------------+---------+----------------------+--------+-----------------+
| id | select_type  | table      | type   | possible_keys     | key         | key_len | ref                  | rows   | Extra           |
+----+--------------+------------+--------+-------------------+-------------+---------+----------------------+--------+-----------------+
|  1 | PRIMARY      | <derived2> | ref    | <auto_key0>       | <auto_key0> | 90      | const,const          |     10 | Using where     |
|  2 | DERIVED      | c          | index  | PRIMARY,cancel_id | status  | 1       | NULL                 | 586057 | Using index     |
|  2 | DERIVED      | p          | eq_ref | PRIMARY           | PRIMARY     | 8       | purchase.c.cancel_id |      1 | Using where     |
|  3 | UNION        | c          | index  | PRIMARY,cancel_id | status  | 1       | NULL                 | 586057 | Using index     |
|  3 | UNION        | ch         | eq_ref | PRIMARY           | PRIMARY     | 8       | purchase.c.cancel_id |      1 | Using where     |
| NULL | UNION RESULT | <union2,3> | ALL    | NULL              | NULL        | NULL    | NULL                 |   NULL | Using temporary |
+----+--------------+------------+--------+-------------------+-------------+---------+----------------------+--------+-----------------+
6 rows in set (0.00 sec)

unionの外ではインデックスが効かない
fromの中でサブクエリ使う場合も同じ
これはCOALESCEや集約関数でも同じ

ムダのないクエリ

SELECT
     order_id as 注文ID
    ,price AS 金額
FROM
    (
              SELECT
                  purchase_id as order_id , p.amount as price
                  , member_id , c.status
              FROM purchase as p INNER JOIN cancel as c ON p.purchase_id = c.cancel_id
              WHERE p.member_id = 'ある会員' and c.status = 'success'
    UNION ALL
            SELECT charge_id as order_id, member_id, sales_price as price, c.status
            FROM charge as ch inner join cancel as c ON ch.charge_id = c.cancel_id
             WHERE ch.member_id = 'ある会員' and c.status = 'success'
    ) AS tmp

EXPLAIN

+----+--------------+------------+--------+------------------------------+--------------+---------+------------------------+------+-----------------------+
| id | select_type  | table      | type   | possible_keys                | key          | .. | rows | Extra                 |
+----+--------------+------------+--------+------------------------------+--------------+---------+------------------------+------+-----------------------+
|  1 | PRIMARY      | <derived2> | ALL    | NULL                         | NULL         | .. | 2519 | NULL                  |
|  2 | DERIVED      | p          | ref    | PRIMARY,member_id            | member_id    | .. | 1596 | Using index condition |
|  2 | DERIVED      | c          | eq_ref | PRIMARY,cancel_id,status     | PRIMARY      | .. |    1 | Using where           |
|  3 | UNION        | ch         | ref    | PRIMARY,member_id            | member_id    | .. |  923 | Using index condition |
|  3 | UNION        | c          | eq_ref | PRIMARY,cancel_id,status     | PRIMARY      | .. |    1 | Using where           |
| NULL | UNION RESULT | <union2,3> | ALL    | NULL                       | NULL         | .. | NULL | Using temporary       |
+----+--------------+------------+--------+------------------------------+--------------+---------+------------------------+------+-----------------------+

数千件に絞り込めている
unionや一時テーブルでは先に出来る限りデータを絞る
次はGROUP BY…

今日のお買い上げトップ5が欲しい

架空の機能を実装してみる

+--------------+------------+--------------+--------+
| member_id    | クレカ合計 | DMMポイント合計 | 合計  |
+--------------+------------+--------------+--------+
| 会員X|     100575 |            0 | 100575 |
| 会員C|      79904 |         7264 |  87168 |
| 会員D|      82684 |            0 |  82684 |
| 会員P|      80460 |            0 |  80460 |
| 会員S|      80127 |            0 |  80127 |
+--------------+------------+--------------+--------+
5 rows in set (0.24 sec)

クロス集計の例

SELECT
     tmp.*
    ,credit_sum + dmmpoint_sum AS sum # 合計
FROM (
    SELECT
        member_id,
        SUM( CASE WHEN pay_type_main = 'credit' THEN amount ELSE 0 END
        ) AS credit_sum, # クレカ
        SUM( CASE WHEN pay_type_main = 'dmmpoint' THEN amount ELSE 0 END
        ) AS dmmpoint_sum # DMMポイント
    FROM purchase
    WHERE purchase_id between 昨日のID and 今日のID
    GROUP BY member_id
) AS tmp
ORDER BY sum DESC
LIMIT 5

EXPLAIN

+----+-------------+------------+-------+----------------------+---------+---------+------+--------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys        | key     | .. | rows   | Extra                                        |
+----+-------------+------------+-------+----------------------+---------+---------+------+--------+----------------------------------------------+
|  1 | PRIMARY | <derived2> | ALL   | NULL                 | NULL    | .. | 118310 | Using filesort                               |
|  2 | DERIVED | purchase   | range | PRIMARY,member_id    | PRIMARY | .. | 118310 | Using where; Using temporary; Using filesort |
+----+-------------+------------+-------+----------------------+---------+---------+------+--------+----------------------------------------------+

extraに注目
Using temporary … 一時テーブルが使われる
Using filesort ‥ ソートにインデックスが使われない
メモリに乗らないとMyISAMテーブルができる

GROUP BY どれほど遅くなるか

30GBのデータに対して…
ただのSELECT * FROM テーブルだと30分
group by をすると1時間半(オンメモリで)
ファイルをガリガリ書きだすと9時間くらい
漢のコンピュータ道 Using filesort http://nippondanji.blogspot.jp/2009/03/using-filesort.html

limitの話

indexが使えると早い
indexが貼ってなくても上から順番に調べていけば良いので基本的には軽い

プライマリインデックスでソート

mysql> explain select * from purchase order by purchase_id desc limit 10;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | purchase | index | NULL          | PRIMARY | 8       | NULL |   10 | NULL  |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------+

セカンダリインデックスでも早い

mysql> explain select * from purchase order by purchase_date desc limit 10;
+----+-------------+----------+-------+---------------+--------------+---------+------+------+-------+
| id | select_type | table    | type  | possible_keys | key          | key_len | ref  | rows | Extra |
+----+-------------+----------+-------+---------------+--------------+---------+------+------+-------+
|  1 | SIMPLE      | purchase | index | NULL          | purchase_ix4 | 6       | NULL |   10 | NULL  |
+----+-------------+----------+-------+---------------+--------------+---------+------+------+-------+
1 row in set (0.00 sec)

インデックス貼ってないので激重

mysql> explain select * from purchase order by amount desc limit 10;
+----+-------------+----------+------+---------------+------+---------+------+-----------+----------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows      | Extra          |
+----+-------------+----------+------+---------------+------+---------+------+-----------+----------------+
|  1 | SIMPLE      | purchase | ALL  | NULL          | NULL | NULL    | NULL | 175771186 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+-----------+----------------+

Using filesortがでてる!
sort_buffer_size / max_heap_table_sizeで割り当てられたメモリに乗らない場合はMyISAMテーブルに一時的に書きだされる

やっぱりインデックスは一つしか使われない

mysql> explain select * from purchase where member_id = 'ある会員ID' order by purchase_id desc limit 10;
+----+-------------+----------+------+---------------+--------------+---------+-------+-------+-------------+
| id | select_type | table    | type | possible_keys | key          | key_len | ref   | rows  | Extra       |
+----+-------------+----------+------+---------------+--------------+---------+-------+-------+-------------+
|  1 | SIMPLE      | purchase | ref  | member_id  | member_id | 48      | const | 44448 | Using where |
+----+-------------+----------+------+---------------+--------------+---------+-------+-------+-------------+
selectの結果
10 rows in set (0.00 sec)

ある会員データをまず取ってくる
ID順に並び替える
プライマリキーでソートするので早い
プライマリキーはメモリにある

ほんの少し遅い

セカンダリインデックスでソート

explain select * from purchase force index(member_id)
where member_id = 'ある会員' order by purchase_date desc limit 10;
+----+-------------+----------+-------+---------------+--------------+---------+------+-------+-------------+
| id | select_type | table    | type  | possible_keys | key          | key_len | ref  | rows  | Extra       |
+----+-------------+----------+-------+---------------+--------------+---------+------+-------+-------------+
|  1 | SIMPLE      | purchase | index | member_id  | member_id | 6       | NULL | 44448 | Using where |
+----+-------------+----------+-------+---------------+--------------+---------+------+-------+-------------+
selectの結果
10 rows in set (0.17 sec)

ある会員IDのデータを全部撮ってきてからソートする
並び替えるためのデータをHDDに読みに行くのでプライマリキーで並び替えるより少し遅い

ちょっと遅い

セカンダリインデックスでソート

mysql> explain select * from purchase where member_id = 'ある会員' order by purchase_date desc limit 10;
+----+-------------+----------+-------+---------------+--------------+---------+------+-------+-------------+
| id | select_type | table    | type  | possible_keys | key          | key_len | ref  | rows  | Extra       |
+----+-------------+----------+-------+---------------+--------------+---------+------+-------+-------------+
|  1 | SIMPLE      | purchase | index | member_id  | purchase_date | 6       | NULL | 39545 | Using where |
+----+-------------+----------+-------+---------------+--------------+---------+------+-------+-------------+
selectの結果
10 rows in set (1.41 sec)

possible_keyと実際に使うkeyが違う
会員IDで絞りこまずに日付の新しいレコードをとにかく会員IDが見つかるまで読み込んでいる。

めっちゃ遅い

一見早そうに見えるけど…

mysql> explain  select * from purchase where order_date < '2004-01-01' order by purchase_date desc limit 1;
+----+-------------+----------+-------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | purchase | index | order_date  | purchase_date | 6       | NULL |    2 | Using where |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-------------+
1 row in set (0.00 sec)

2004年以前のデータを取りたいのにインデックスは最新の2015年のデータから昔へ遡って走査する
実質フルスキャン
ソート順を逆にすると2003年からスキャンするので早い
…FORCE INDEXを使えばOK

mysqlがいうことを利いてくれない!

force indexを使う
FROMのテーブルにFORCE INDEX(キー名)

その他小ネタ

エンコードが違うテーブル同士のjoin

SELECT
    number
FROM
    credit_log as cl
    INNER JOIN purchase as p on cl.member_id = p.member_id
WHERE
    p.purchase_id = 310000000
LIMIT 1

credit_logテーブルはujis
purchaseテーブルはutf8mb4
joinしたいmember_idはvarchar (半角文字)

EXPLAIN

+----+-------------+-------+-------+----------------------+---------+---------+-------+----------+-------------+
| id | select_type | table | type  | possible_keys        | key     | key_len | ref   | rows     | Extra       |
+----+-------------+-------+-------+----------------------+---------+---------+-------+----------+-------------+
|  1 | SIMPLE      | p     | const | PRIMARY,purchase_ix1 | PRIMARY | 8       | const |        1 | NULL        |
|  1 | SIMPLE      | cl    | ALL   | NULL                 | NULL    | NULL    | NULL  | 13566662 | Using where |
+----+-------------+-------+-------+----------------------+---------+---------+-------+----------+-------------+

JOINにインデックスが何も使われない

文字コードを合わせてあげるとうまくいく

SELECT
    number
FROM
    credit_log as cl
    INNER JOIN purchase as p on cl.member_id = CONVERT(p.member_id USING ujis)
WHERE
    p.purchase_id = 310000000
LIMIT 1

+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref   | rows | Extra                 |
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | p     | const | PRIMARY       | PRIMARY   | 8       | const |    1 | NULL                  |
|  1 | SIMPLE      | cl    | ref   | member_id     | member_id | 38      | const |    1 | Using index condition |
+----+-------------+-------+-------+---------------+-----------+---------+-------+------+-----------------------+

CONVERT(文字列 USING ujis) で文字コード変換

union allで一時テーブル作るとexplainも重くなるよ

ちょっとマスターテーブル的なの欲しいんだけどー
create temporary table 権限ないしー

select
    c.* # 返金情報
  reason as 返金理由
FROM
(
    select 0 AS reason_id,'理由' AS reason
    union all select 1, '障害'
    union all select 2, '検証'
    union all select 3, 'テスト'
    union all select 4, '間違い'
    union all select 5, '品切れ'
    union all select 6, '乱丁・落丁'
    　‥ # 1万行くらい
    union all select 10000, 'その他'
) as tmp
INNER JOIN cancel as c ON c.reason_id = tmp.reason_id

explainが遅くなります。(1秒くらい)
create temporary table にはインデックス貼れます。

MySQLがエンコード変換でコケる

ujisテーブル日本語の "～" "が3バイト文字だとエラーになる
そんなときはujisのまま出力してperlで除去

# CSVにデータエクスポート
set names ujis;
select * from テーブル into outfile a.csv

# CSVのデータを変換
perl -p -e 's/\x8f\xa1\xc1/\xa1\xc1/g' CSVファイル > 出力先

# CSVデータをインポート
set names utf8mb4;
LOAD DATA INFILE ... # csv読み込み

30GBのファイルでも10秒くらいで処理できる

UPDATEのミスの防止に

updateにも使えるEXPLAIN
実行し終わったクエリにストッパを掛けておく
UPDATE で対象件数を把握する。

updateでjoinするクエリを

実行するときに便利

explain
update
        cancel_detail as cd
        inner join cancel as c on c.cancel_id = cd.cancel_id
        inner join purchase as p on c.order_id = p.purchase_id
        inner join purchase_detail as pd on cd.purchase_detail_id = pd.purchase_detail_id
SET
        cd.amount = -((pd.sales_price + pd.unit_tax) * pd.quantity)
where
        p.basket_service_type = 'admin' # 管理画面から取り消されたものを対象

でも本番のマスターDBでやるとテーブルロックかかったりして後続のトランザクションがタイムアウトで死んだりするのでできるだけプライマリキーで1つずつやるようにしましょう!

EXPLAINは万能なのか?

そんなことはない。。
そのためには仕組みを知りましょう!
→詳しくは小原さんに続く

参考資料

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL
http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
left join vs not exists
http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
subquery vs group by
http://explainextended.com/2010/01/30/aggregates-subqueries-vs-group-by/
SQLを速くするぞ!
http://www.geocities.jp/mickindex/database/db_optimize.html
MySQL 5.6における大量データロード時の考慮点
http://dbstudy.info/files/20131007/mysql56_load_r2.pdf
大きめのテーブルにカラムやインデックスを追加する際の注意
http://d.hatena.ne.jp/LukeSilvia/20090315/p1

ALTER TABLEを使いこなそう
http://nippondanji.blogspot.jp/2009/05/alter-table.html
MySQL Server System Variables
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_interactive_timeout
innodbチューニング
http://yakst.com/ja/posts/65
InnoDBの圧縮を使うときの運用
http://d.hatena.ne.jp/ichii386/20111201/1322679208
group by で集約していないカラムを使うと"適当に"選択される
http://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html
InnoDBに直接アクセス
http://labs.cybozu.co.jp/blog/kazuho/archives/2008/06/mysql_direct_access.php
MySQL 5.6における大量データロード時の考慮点
http://d.hatena.ne.jp/sh2/20131007

alivelime.github.io

alivelime

alivelime.github.io

0 0 (function() { var po = document.createElement('script'); po.type = 'text/javascript'; po.async = true; po.src = 'https://apis.google.com/js/platform.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s); })();