Rails Developers Meetup 2018 で「MySQL/InnoDB の裏側」を発表しました

Rails Developers Meetup 2018: Day 1 で「MySQL/InnoDB の裏側」と題して SELECT クエリの実行フローや InnoDB のインデックス周りの発表しました。MySQL with InnoDB のインデックスの基礎知識とありがちな間違い + α の内容です。

Nested Loop Join のスライドは無理やり差し込んだ感が溢れてますがご了承ください＞＜

追記: 動画も公開されたので貼り付けておきます。1

key_len について発表で全然触れなかったんですが、重要な内容なので次のエントリーにまとめました。

MySQL で複合インデックスを作成する際には必ず key_len を確認すべきという話

補足

サンプルデータ

MySQL のサンプルデータとしては world や employee が有名だと思うんですが、前々から world は物足りないし employee は仰々しいと感じていたので自前で用意しました。

https://github.com/abicky/sample-data-railsdm-2018

必要に応じて README に書いてある環境変数を設定するなり、database.yml を修正するなりした上で rake タスクを実行すればデータが入ります。

% git clone git@github.com:abicky/sample-data-railsdm-2018.git
% cd sample-data-railsdm-2018
% ./bin/setup
% rake db:insert

質疑応答

nested loop joinがnested loop join以外の手法と比べてどういうメリット/デメリットがあるのか知りたいです

「MySQL 以外の DB をまともに使ったことがないので詳しくないのと、徹夜で資料作って頭が働いてないので明日にでもブログに書きます」と言った気がするんですが、後日調べてまとめようと思います！（時と場所の指定まではしていない）

primary id が定義されてないテーブルでは secondary index はなにを参照するのか気になります

うろ覚えな知識で

  1. unique key
  2. unique key がなければ内部的に作成する

という回答をしましたが、kamipo さんがマニュアルページ教えてくれました！ https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

  1. unique key の中でも全てのカラムに NOT NULL 制約のあるもの
  2. 該当するものがなければ GEN_CLUST_INDEX という隠れた index を作成する

という感じみたいですね。

範囲検索で二つ目のインデックスが効果ある場合(ICP狙いの場合)って具体的にどういう場合なのでしょうか？

「テーブルの特性によります」と回答しました。例えば今回の例であれば

  • started_at に未来の値が指定されることがけっこうある
    • ended_at >= NOW() で残るレコードのうち半分以上が started_at > NOW() とか
  • ended_at >= NOW() AND started_at <= NOW() のようなクエリのせいで MySQL の I/O 負荷や CPU 負荷に困っている
    • 負荷が多くなくても MySQL からのレスポンスタイムをわずかでも削りたい
  • insert のパフォーマンスは気にしない
  • メモリが潤沢にあってインデックスの肥大化は気にならない
    • ディスク容量も然り

等の条件を満たすようなら ICP は有効なのかなと思います。

started_at にインデックスを張ることについて

質問が多かったので補足します。
started_at にインデックスを張るべきかどうかはテーブルの特性次第です。今回の前提はスライドに書いたとおりです。その前提に立つと

  • started_at <= NOW() による絞り込みの効果はほぼない
    • 掲載終了した商品も含めてほとんどの商品は started_at <= NOW() の条件を満たす
  • 絞り込みの効果がほぼなく、カバリングインデックスも関係しないのであれば効果のほとんどないインデックスはリソースの無駄
    • buffer pool・ディスクがもったいない
  • insert に時間がかかる

と言えます。「ないよりあった方が良いのでは？」という質問に対する回答は「リソースの無駄」が一番の理由でしょうか。
仮に将来掲載される商品を取得したいという要求がある場合

SELECT * FROM started_at > NOW();

とするのではなく

SELECT * FROM started_at > NOW() AND ended_at > NOW();

とすることで十分な可能性もあるでしょう。これは掲載期間の制限があり、長期間掲載される商品があまり存在しない前提です。

「started_at でソートしたいのでは？」という意見も見かけましたが、ended_at >= NOW() のような条件が存在する限り、started_at のインデックスはソートに使われることはないです。

参考資料

「詳解MySQL 5.7」はとても良い本でした。MySQL 5.7 の新機能を紹介する上で MySQL が今までどうだったか、どう変わったか等、内部的な仕組みについてわかりやすく説明されているので今回の発表のような内容に興味がある方は必読本だと思います。

Amazonで詳細を見る

発表直前に読み返してみて気付いたんですが、今回の発表内容は「実践ハイパフォーマンスMySQL」の 5 章、6 章の内容に相当します。クエリの最適化の例も色々載っているので、発表内容が物足りなかった方は見てみると良いと思います。

Amazonで詳細を見る

あとは次のスライドが個人的にとても好きです。

発表では全然触れませんでしたが、テーブルの設計に関しては「SQLアンチパターン」が参考になると思います。

Amazonで詳細を見る

あとは次の内容が参考になると思います。

We’re hiring

初参加ということもあって場の雰囲気がよくわからなかったので発表時には全然話さなかったんですが、Repro ではエンジニアを募集しています！
特に基盤系の面倒を見れるエンジニアが明らかに不足しているので（僕みたいな未経験者が面倒見てるぐらいですし）、AWS とかデータ分析基盤（Fluentd, Hadoop, Presto etc.）に詳しい方や興味のある方は是非遊びに来てみてほしいです！

興味があれば下記採用ページから応募してもらったり、@a_bicky にご連絡いただけたりすると嬉しいです！

https://repro.io/jp/careers/

  1. 外部キーを定義する場合は単一キーがないといけないみたいなことを言ってますが、勘違いでした… ↩