【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_summary
の sort_mode
です
SQL | sort_mode |
---|---|
SELECT id FROM newsORDER BY published_at DESC; |
"<sort_key, additional_fields>" |
SELECT * FROM newsORDER 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
周りは奥が深いので、別の機会に
、とさせていただこうと思います。
「待てないよ!」という方は、漢(オトコ)のコンピュータ道 が絶賛、オススメです!
まとめ
MySQL
のORDER 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 に吸収されてからですか?