【MySQL】`SELECT id FROM news ORDER BY published_at DESC` と `SELECT * FROM news ORDER BY published_at DESC` では結果が異なることについて

皆さん、こんにちは。 暖かくなってきたので Tシャツとサンダルで通勤しちゃったりする方、 MUGENUP の osada です。 服装に気を使わなくてよい(わけではないのですが)というのは、 エンジニアの利点の一つですよね。

さて、先日、こんな現象が持ち込まれました。

「下の2つのコードで、結果が異なるんですが……?」

News.order("published_at DESC").map(&:id)
News.order("published_at DESC").select(:id).map(&:id)

今日はMySQL の order で * は特別な動きをする(ようだ)というお話です。

対象は、上記の2つの結果が異なる理由が分からない人です。

Rails ではなく、MySQL レベルの挙動の違いだった

まず疑ったのが、Rails で挙動を書き換えているんじゃないか? ということです。 select をハックして、SQLレベルでは、order が書き換わっているのでは?

しかし、結果はNOでした。

[2] pry(main)> News.order("published_at DESC").map(&:id)
  News Load (4.4ms)  SELECT `news`.* FROM `news` ORDER BY published_at DESC
=> [10, 11, 12, 13, 14, 15, 16, 9, 8, 7, 6, 5, 4, 3, 2, 1]
[3] pry(main)> News.order("published_at DESC").select(:id).map(&:id)
  News Load (0.5ms)  SELECT id FROM `news` ORDER BY published_at DESC
=> [15, 14, 13, 12, 11, 10, 16, 9, 8, 7, 6, 5, 4, 3, 2, 1]

試しに、発行されたSQLを、db console から叩きましたが、結果は同じでした。

10 から 15 までの順番が、全く逆順になっているのです。

そこに注目してみると、 id が 10 から 15 のレコードの、published_at は 値が同じ でした (これはバグだったので、プルリクで止めました)。

そういえば、ORDER BY で対象カラムの値が同じとき、MySQL はどのような挙動を示すのでしょうか?

MySQLオプティマイザトレース

幸いにも、MySQLには、実行計画を見る仕組みが備わっていました。

[D14] MySQL 5.6時代のパフォーマンスチューニング *db tech showcase 2013 Tokyo

こちらの 17ページ の通りにやってみます。

mysql> set session optimizer_trace='enabled=on,one_line=off';
Query OK, 0 rows affected (0.17 sec)

mysql> set session optimizer_trace_max_mem_size=102400;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT id FROM `news` ORDER BY published_at DESC;
......

mysql> select * from information_schema.optimizer_trace\g;
......
| SELECT `news`.id FROM `news` ORDER BY published_at DESC | {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `news`.`id` AS `id` from `news` order by `news`.`published_at` desc"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "table_dependencies": [
              {
                "table": "`news`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`news`",
                "table_scan": {
                  "rows": 16,
                  "cost": 1
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`news`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "scan",
                      "rows": 16,
                      "cost": 4.2,
                      "chosen": true,
                      "use_tmp_table": true
                    }
                  ]
                },
                "cost_for_plan": 4.2,
                "rows_for_plan": 16,
                "sort_cost": 16,
                "new_cost_for_plan": 20.2,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": null,
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`news`",
                  "attached": null
                }
              ]
            }
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`news`.`published_at` desc",
              "items": [
                {
                  "item": "`news`.`published_at`"
                }
              ],
              "resulting_clause_is_simple": true,
              "resulting_clause": "`news`.`published_at` desc"
            }
          },
          {
            "refine_plan": [
              {
                "table": "`news`",
                "access_type": "table_scan"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "desc",
                "table": "`news`",
                "field": "published_at"
              }
            ],
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            },
            "filesort_execution": [
            ],
            "filesort_summary": {
              "rows": 16,
              "examined_rows": 16,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 14544,
              "sort_mode": "<sort_key, additional_fields>"
            }
          }
        ]
      }
    }
  ]
} |                                 0 |                       0 |
.....

凄い色々でてきました。 一つ一つ見ていくと、大変興味深そうですが、 今回は、違いだけに注目したいので、 さっそくもう一つのSQLの実行計画も確認します。

mysql> SELECT * FROM `news` ORDER BY published_at DESC;

mysql> select * from information_schema.optimizer_trace\g;

すると、ある一点だけが異なりました。

            "filesort_summary": {
              "rows": 16,
              "examined_rows": 16,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 10908,
              "sort_mode": "<sort_key, rowid>"
            }

filesort_summarysort_mode です

SQL sort_mode
SELECT id FROMnewsORDER BY published_at DESC; "<sort_key, additional_fields>"
SELECT * FROMnewsORDER BY published_at DESC; "<sort_key, rowid>"

SELECT * のときは、sort_key の他に rowid が使用されるようです。 つまり、published_at が同じときは、rowid でソートされるのですね。

MySQL の rowid ?

この row_id については、ググると下記の情報が見つかりました。

8.2.1.15 ORDER BY Optimization

The sort_mode value provides information about the algorithm used and the contents of tuples in the sort buffer:

  • <sort_key, rowid>: Sort buffer tuples contain the sort key value and row ID of the original table row. Tuples are sorted by sort key value and the row ID is used to read the row from the table.
  • <sort_key, additional_fields>: Sort buffer tuples contain the sort key value and columns referenced by the query. Tuples are sorted by sort key value and column values are read directly from the tuple.

  • <sort_key, rowid>: ソートバッファタプル には、ソートキー と テーブルの行id が含まれる. タプルは ソートキーと、テーブルから読まれた行IDによって、ソートされる。

  • <sort_key, additional_fields>: ソートバッファタプルには、ソートキーと、クエリで参照されるカラムが含まれる。タプルは、タプルから直接読み取られたソートキーとカラムによってソートされる。

……なかなか難しいですね!

これ以上の資料が見つからなかったため、挙動の類推でしかないのですが、 rowid のときは、元のテーブルの順序をそのまま維持する という動作なのではないかと思います。

実際、自分で比較アルゴリズムを書くならば、 同値のときに、わざわざ swap するプログラムにはしないと思うのです。

# 昇順 (後ろほど、値が大きい)

for(i = 0, length = tuples.length; i < length - 1; i++){
  if(tuples[i + 1] < tuples[i]){
    tmp = tuples[i];
    tuples[i] = tuples[i + 1];
    tuples[i + 1] = tuples[i];
  } else if(tuples[i + 1] == tuples[i]){
    # わざわざここに swap を書いたりしない。
  }
}

一方、クエリ上に カラムが存在した場合、当然ながらそれはソートします。

ということで、* と、それ以外で、結果が異なる、ということになったようです。

インデックスを貼るとどうなるか?

さてfilesortをみたのなら、当然のようにインデックスを貼りたくなるはずです。

mysql> alter table news add index published_at_idx (published_at);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> SELECT id FROM `news` ORDER BY published_at DESC;
......
mysql> select * from information_schema.optimizer_trace\g;
......
          {
            "refine_plan": [
              {
                "table": "`news`",
                "access_type": "index_scan"
              }
            ]
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`news`",
                "index_provides_order": true,
                "order_direction": "desc",
                "index": "published_at_idx",
                "plan_changed": true,
                "access_type": "index_scan"
              }
            }
          }
......
mysql> SELECT * FROM `news` ORDER BY published_at DESC;
......
mysql> select * from information_schema.optimizer_trace\g;
......
          {
            "refine_plan": [
              {
                "table": "`news`",
                "access_type": "table_scan"
              }
            ]
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`news`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "unknown",
                "plan_changed": false
              }
            }
          }
......
            "filesort_summary": {
              "rows": 16,
              "examined_rows": 16,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 10908,
              "sort_mode": "<sort_key, rowid>"
            }

* ではインデックスが使われず、id ではインデックスが使われました。 一体どうゆうことなんでしょうか?

MySQL :: MySQL 5.1 リファレンスマニュアル :: 6.2.12 ORDER BY最適化

によれば、index が貼られた カラムの order ならば、index が使われるはずですが……?

次のクエリではインデックスを使用して ORDER BY部分を解決します。

SELECT * FROM t1
  ORDER BY key_part1,key_part2,... ;

レコードを全件取得しているので、テーブルスキャンが最適だ、ということなのでしょうか? 試しに LIMIT 10 をつけてみると、plan_changed が発動していました。 なるほど、絞込をするときには、インデックスが使われるのですね。

mysql> SELECT * FROM `news` ORDER BY published_at DESC LIMIT 10;
......
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`news`",
                "index_provides_order": true,
                "order_direction": "desc",
                "index": "published_at_idx",
                "plan_changed": true,
                "access_type": "index_scan"
              }
            }           

単に ORDER BY でインデックスを貼っても効果がないこともあるようなので、 必ず確認して使っていくようにしたいものです。

このindex 周りは奥が深いので、別の機会に、とさせていただこうと思います。 「待てないよ!」という方は、漢(オトコ)のコンピュータ道 が絶賛、オススメです!

まとめ

  • MySQLORDER BY は、ソート指定カラムの値が同値のときに、クエリによって、結果が異なることがあります。
  • SELECT * FROM のときは、rowid が使われるので、同値のときは、テーブルへ INSERT された順がそのままです。
  • SELECT some_column FROM のときは、ORDER BY の指定カラム以外に、some_column がソート対象に含まるため、それらがソートに使われます。
  • ORM を使っている人は、意識しづらいので、気をつけましょう。
  • 同値が疑われるときは、必ず id でも order しましょう. News.order("published_at DESC").order("id DESC").map(&:id)

ということで、思わぬところで、order 順が違ってしまった、という記事でございました。

後書き

なお、使用しているMySQLは、5.6.17 です

Server version: 5.6.17 Homebrew

紙幅の関係上、order の内容を文字列 にしましたが、arel_tableを使ったほうがせくしーです。

News.order(News.arel_table[:published_at].desc)

ところで、「Oracle には row_id があり、MySQL にはないので、テーブルの最後のレコードを見つけるのが大変だ」 という話を、昔同僚としたことがあるのですが、MySQL はいつから row_id が使えるようになったのでしょうか? Oracle に吸収されてからですか?