あなたはMySQLの挙動を本当に理解していますか？

#はじめに
この記事は、CyberAgent 20新卒 エンジニア Advent Calendar 2019の3日目の記事です。

今回の記事は今年の夏に内定者バイトを行った際に、
僕「sqlでORDER BYをつけない場合の並び順ってprimary key順じゃないんですか？」
トレーナーさん「それは実行計画に依存するかな」
僕「？」
となったので、自分なりに調べたことをまとめたものになります。

内定者バイトの記事はまた後日書きます(遅くなっており本当に申し訳ございません。)。
ちなみに内定者バイトではこういうことをしていました。
https://twitter.com/ue_knnk/status/1192731466070806530

#実行計画とは
SQL文はざっくりと以下の図のように処理されます。

つまり、実行計画とは統計情報を元にオプティマイザが、どのようにデータアクセスを行いSQLを実行するかをまとめたものになります。

#実験
それでは本題のORDER BYをつけない場合の並び順はprimary key順なのかについて実験していきます。

MySQLのバージョンは8.0.18です。
また、ストレージエンジンはバイト先に合わせ、InnoDBを使います。

まず、日付をprimary keyとして降順に初期データを投入します。
イメージとしては動画配信サイトのコメントのテーブルです。

InnoDBのデータ構造はクラスタードインデックスなのでprimary keyを持つテーブルの場合データはprimary keyのキー値でソート済みになっています。
なので、ORDER BYをつけなくてもprimary key順になっています。

実行計画も見てみるとインデックスを使っていないことが分かります。

次にインデックスを使うようにsqlを実行すると、インデックスで指定したキー順が優先されて並んでいることが分かります。

実行計画を見ると、インデックスを使っていることが分かります。

このように実行計画でインデックスを使用している場合、インデックスのキーが優先されてソートされるため、primary key順で並べたい場合は以下のようにORDER BYをつけてsqlを実行する必要があります。

#まとめ
今回は内定者バイト中に思った、考えてみたらそうだなあってなったことを調べて、まとめてみました。
挙動を理解していないとトラブルになりかねないので、知っておいて損はないのではと思います。
今回の例だと、動画配信サイトのコメントは基本的に投稿順で表示するため、primary key順でソートされていないと困りますよね。
読んでいただいてありがとうございます。

#参考
https://blog.kamipo.net/entry/2016/12/24/234944
https://qiita.com/at_1016/items/33186523cfc20fb58675