Help us understand the problem. What is going on with this article?

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

はじめに

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

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

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

実行計画とは

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

image.png

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

実験

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

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

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

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

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

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

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

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

まとめ

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

参考

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした