課題で調べたときのメモをせっかくなので残しとくことにする。
課題の経過をここに残します。
要件
対象のデータテーブルは以下のようなもの。
usersテーブル
id | name | carrier | created_at |
---|---|---|---|
1 | hoge | 'a' | 2015-05-12 |
2 | fuga | 'c | 2015-02-12 |
... | .... | ....... | .......... |
carrierカラムについては携帯電話の通信キャリアの頭文字カラムをイメージ。今回は['a','b','c']の3値を取りうるとする。
このテーブルに対して**「指定された期間内に作成されたレコードを各日、各キャリアごとに集計する」**というクエリを考える。
理想の出力:(仮に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の方が実行時間が長いにもかかわらずコストが低い(まだこのコストの値の解釈について調べる必要はある)(参考エントリ))
という疑問点が新たに出た。