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

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

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

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

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

```.rb
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`にインデックスを貼る

```.rb
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の[インデックスコンディションプッシュダウンの最適化](https://dev.mysql.com/doc/refman/5.6/ja/index-condition-pushdown-optimization.html)が行なわれていることを示す。~~インデックスのフィールドだけを利用する、という最適化。~~where条件にて必要なカラムをindexが「一部」カバーしてる場合に、レコードfetchをフィルタリングする最適化。無駄なレコードfetchを減らすだけでアクセスはしているのでcovering indexよりは遅い。

結果: これで0.15secほど。

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

```.rb
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を使用していると書いてある記事](http://www.geocities.jp/mickindex/database/db_optimize.html#LocalLink-group_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レファレンスマニュアル](http://ftp.stu.edu.tw/Unix/Database/Mysql/doc/refman/5.1-olh/ja/order-by-optimization.html))

これにより、クエリに`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`のカラムがある(「上の日付レベルではソートされている」は関係があるようにも思えるが、わからない)
  - [バグとして報告されている](https://bugs.mysql.com/bug.php?id=74462)ようだけど、わからない……
 - created_atの方が実行時間が長いにもかかわらずコストが低い(まだこのコストの値の解釈について調べる必要はある)([参考エントリ](http://yakst.com/ja/posts/2568)))

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