LoginSignup
118
103

More than 5 years have passed since last update.

@lastcat_

MySQLのexplainとかについてしらべたときのメモ

Posted at

課題で調べたときのメモをせっかくなので残しとくことにする。

課題の経過をここに残します。

要件

対象のデータテーブルは以下のようなもの。

usersテーブル

id name carrier created_at
1 hoge 'a' 2015-05-12
2 fuga 'c 2015-02-12
... .... ....... ..........

carrierカラムについては携帯電話の通信キャリアの頭文字カラムをイメージ。今回は['a','b','c']の３値を取りうるとする。

このテーブルに対して「指定された期間内に作成されたレコードを各日、各キャリアごとに集計する」というクエリを考える。

理想の出力:(仮に2015-03-01~2015-03-31間の集計だったとして)

carrier created_at count(carrier)
a 2015-03-01 32
b 2015-03-01 13
c 2015-03-01 44
a 2015-03-02 12
b 2015-03-02 13
c 2015-03-02 4
......... ...... .......
a 2015-03-31 11
b 2015-03-31 14
c 2015-03-31 40

テストデータ挿入に使用したrake task

namespace :insert do
  task do: :environment do
    carriers = ['a', 'b', 'c']
    (1..1000000).each do |i|
      u = User.create(name: "name#{i}", carrier: carriers[rand(3)])
      u.created_at = DateTime.now - rand(365*24*3600).second
      u.save
    end
  end
end
  • レコード数: 100万件
  • carrier(簡単のためにとりあえず"a","b","c"の3種類を取るということにした)
  • created_atはとりあえず現在から一年以内とした(DateTime.now - rand(365*24*3600).second)

結果

クエリ

select carrier, date(created_at),count(carrier) from users where created_at between '2015-03-01' and '2015-03-31' group by date(created_at), carrier;

このクエリの出力

+---------+------------------+----------------+
| carrier | date(created_at) | count(carrier) |
+---------+------------------+----------------+
| a       | 2015-03-01       |            922 |
| b       | 2015-03-01       |            957 |
| c       | 2015-03-01       |            916 |
| a       | 2015-03-02       |            944 |
| b       | 2015-03-02       |            916 |
| c       | 2015-03-02       |            894 |
| a       | 2015-03-03       |            933 |
| b       | 2015-03-03       |            921 |
| c       | 2015-03-03       |            935 |
..............................................
| a       | 2015-03-30       |            901 |
| b       | 2015-03-30       |            875 |
| c       | 2015-03-30       |            896 |
+---------+------------------+----------------+

は求めていた結果と等しい。よってこれは要件を満たすクエリだと考えられる。

とりあえず求める結果が得られたということで、続いてパフォーマンスについての考察を行う。

パフォーマンスについて

初期段階(インデックス無し)

(実際には貼っているが(possible_keys)、use index ()句で使用をキャンセルしている(keyカラムがnull)。)

+----+-------------+-------+------+---------------------------------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys                         | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+-------+------+---------------------------------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | users | ALL  | index_users_on_created_at_and_carrier | NULL | NULL    | NULL | 994336 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------------------------------+------+---------+------+--------+----------------------------------------------+
  • select_type: 今回はサブクエリやUNIONを指定していないのでここはSIMPLEになる
  • type: ALLはテーブルフルスキャンを表す。一番効率が悪い
  • extra:
    • Using where: indexだけではwhereを解決できないことを示している
    • Using temporary: テンポラリーテーブルを作成することを示している。あまり良くない
    • Using filesort: レコードがクイックソートされていることを示す かなり良くない
  • rows hitする行数の見積もり。低い方が良い。

結果: これで0.40sec程度

created_atにインデックスを貼る

add_index "users", ["created_at"], name: "index_users_on_created_at", using: :btree

explain結果

+----+-------------+-------+-------+-----------------------------------------------------------------+---------------------------+---------+------+--------+--------------------------------------------------------+
| id | select_type | table | type  | possible_keys                                                   | key                       | key_len | ref  | rows   | Extra                                                  |
+----+-------------+-------+-------+-----------------------------------------------------------------+---------------------------+---------+------+--------+--------------------------------------------------------+
|  1 | SIMPLE      | users | range | index_users_on_created_at_and_carrier,index_users_on_created_at | index_users_on_created_at | 6       | NULL | 170382 | Using index condition; Using temporary; Using filesort |
+----+-------------+-------+-------+-----------------------------------------------------------------+---------------------------+---------+------+--------+--------------------------------------------------------+

変更点

  • type: range => インデックスを使用した範囲選択を表す。
  • key: index_users_on_created_at => 貼ったindexが使われていることがわかる。
  • rows: かなり減少した。
  • Extra: Using index condition => MySQLのインデックスコンディションプッシュダウンの最適化が行なわれていることを示す。インデックスのフィールドだけを利用する、という最適化。where条件にて必要なカラムをindexが「一部」カバーしてる場合に、レコードfetchをフィルタリングする最適化。無駄なレコードfetchを減らすだけでアクセスはしているのでcovering indexよりは遅い。

結果: これで0.15secほど。

created_atとcarrierとの複合インデックスにする

add_index "users", ["created_at", "carrier"], name: "index_users_on_created_at_and_carrier", using: :btree

explainの結果は

+----+-------------+-------+-------+-----------------------------------------------------------------+---------------------------------------+---------+------+--------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys                                                   | key                                   | key_len | ref  | rows   | Extra                                                     |
+----+-------------+-------+-------+-----------------------------------------------------------------+---------------------------------------+---------+------+--------+-----------------------------------------------------------+
|  1 | SIMPLE      | users | range | index_users_on_created_at_and_carrier,index_users_on_created_at | index_users_on_created_at_and_carrier | 6       | NULL | 174988 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+-------+-----------------------------------------------------------------+---------------------------------------+---------+------+--------+-----------------------------------------------------------+

となる。

結果: extra的にはこちらの方が遅そう(Using filesort; Using temporary)だが、実行速度はこちらの方が早い(0.09sec)。

date用のカラムを作成してそれとcarrierの複合indexを使用してみる

クエリは他の表現がどうしても考えつかなかったので、他に工夫できないか考えてみた。現状created_atをdate()関数で日付単位に変更しているが、GROUP BYにもindexを使用していると書いてある記事を見つけたため、日付用のカラムを作成し、indexを貼ってみたところ、

クエリ
select carrier, created_date, count(created_date) from users where created_date between '2015-03-01' and '2015-03-31' group by created_date, carrier;

explain結果

+----+-------------+-------+-------+-----------------------------------------+-----------------------------------------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys                           | key                                     | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+-----------------------------------------+-----------------------------------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | users | range | index_users_on_created_date_and_carrier | index_users_on_created_date_and_carrier | 4       | NULL | 179674 | Using where; Using index |
+----+-------------+-------+-------+-----------------------------------------+-----------------------------------------+---------+------+--------+--------------------------+

となり、Using temporeryが消え、実行時間も大幅に短縮された(0.04~0.05sec)。
しかしながら疑問点(後述)にあげているように、このようにした場合になぜパフォーマンスが向上するのか確信を持って説明できない。

疑問点

そもそもorder byを使ってないのにUsing filesortが出ている点について

MySQL はデフォルトで、GROUP BY col1、col2, ...の全クエリーを ORDER BY col1、col2, ...で指定したかのように、クエリーをソートします。同じカラムリストを含む ORDER BY 節を明示的に取り入れた場合、ソートが実行されるとはいえ、MySQL は速度ペナルティなしに最適化します。クエリーに GROUP BY が含まれていながら、結果のソートに費やすオーバーヘッドを避けたい場合、ORDER BY NULL を指定することでソートを実行しないようにすることができます。(MySQLレファレンスマニュアル)

これにより、クエリにorder by nullを追加したとところ、

mysql> explain select carrier, date(created_at),count(carrier) from users where created_at between '2015-03-01' and '2015-03-31' group by  date(created_at), carrier order by null; 

+----+-------------+-------+-------+-------------------------------------------------------------------------------------------------------+---------------------------------------+---------+------+------+-------------------------------------------+
| id | select_type | table | type  | possible_keys                                                                                         | key                                   | key_len | ref  | rows | Extra                                     |
+----+-------------+-------+-------+-------------------------------------------------------------------------------------------------------+---------------------------------------+---------+------+------+-------------------------------------------+
|  1 | SIMPLE      | users | range | index_users_on_created_at,index_users_on_created_at_and_carrier,index_users_on_carrier_and_created_at | index_users_on_created_at_and_carrier | 6       | NULL | 4450 | Using where; Using index; Using temporary |
+----+-------------+-------+-------+-------------------------------------------------------------------------------------------------------+---------------------------------------+---------+------+------+-------------------------------------------+

となり、extraからusing file sortが消え、クエリの実行時間も(おそらく)短縮された(0.08~0.09 -> 0.07~0.08)。

この結果の出力として、

+---------+------------------+----------------+
| carrier | date(created_at) | count(carrier) |
+---------+------------------+----------------+
| b       | 2015-03-01       |            957 |
| c       | 2015-03-01       |            916 |
| a       | 2015-03-01       |            922 |
| c       | 2015-03-02       |            894 |
| a       | 2015-03-02       |            944 |
| b       | 2015-03-02       |            916 |

という風に日付のレベルではバラバラにならず、同日内のcarrierレベルで順序がばらつく。
=> インデックスの値がそのままgroup byに使用されているわけではないものの、インデックス内では日付順(厳密には日時順)にソートされているので結局テーブルのレコードにアクセスする順番も日付順になるのでこのように日付のオーダーは守られると考えられる(carrierもインデックスには入っているが、日時レベルでみるとバラバラなのでその順番はこうなる。裏付けとして、created_date-carrierでグルーピングを行った場合にはorder by nullをつけても順番は変化しない)。

date(created_at)とcreated_dateのコストの差

MySQLのjsonフォーマットexplainはクエリの具体的なコストを計算して出力してくれるのだが、

  • date(created_at)の方
explain format=json select carrier, date(created_at),count(carrier) from users where created_at between '2015-03-01' and '2015-04-01' group by date(created_at), carrier order by null; 

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "69543.87"
    },
    "ordering_operation": {
      "using_filesort": false,
      "grouping_operation": {
        "using_temporary_table": true,
        "using_filesort": false,
        "table": {
          ~~
          "key": "index_users_on_created_at_and_carrier",
          "used_key_parts": [
            "created_at"
          ],
          "key_length": "6",
          "rows_examined_per_scan": 173014,
          "rows_produced_per_join": 173014,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "34941.07",
            "eval_cost": "34602.80",
            "prefix_cost": "69543.87",
            "data_read_per_join": "510M"
          },
          ~~
        }
      }
    }
  }
}
  • created_dateの方
explain format=json select carrier, created_date, count(created_date) from users where created_date between '2015-03-01' and '2015-03-31' group by created_date, carrier;
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "72177.22"
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
       ~~
        "used_key_parts": [
          "created_date"
        ],
        "key_length": "4",
        "rows_examined_per_scan": 179674,
        "rows_produced_per_join": 179674,
        "filtered": "100.00",
        "using_index": true,
        "cost_info": {
          "read_cost": "36242.42",
          "eval_cost": "35934.80",
          "prefix_cost": "72177.22",
          "data_read_per_join": "530M"
        ~
    }
  }
}
  • created_atの方で、クエリにorder by nullを使用しているのにordering operationのカラムがある(「上の日付レベルではソートされている」は関係があるようにも思えるが、わからない)
  • created_atの方が実行時間が長いにもかかわらずコストが低い(まだこのコストの値の解釈について調べる必要はある)(参考エントリ))

という疑問点が新たに出た。

118
103
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
118
103