Railsと考えるデータベースのインデックス戦略

Rails Developers Meetup 2018 Day 3 Extreme
進行スポンサーPRとして登壇させていただきました。

Transcript

1. In Rails Developers Meetup 2018 Day 3 Extreme @onunu sponsored by Railsと考える データベースの インデックス戦略
2. Hello! おぬま りく@onunu IESHILという不動産の情報格差 をなんとかしていくサービスを 作っています データエンジニアです Twitter: @onunu_ Github: onunu
3. 0. 今日の話 -MySQL の index- 特にカーディナリティについて
4. こんなことを聞かれました 日次で特定の未処理フラグを検索して更新するRails上の Rakeタスクがあるんですが、検索が遅いです。 でもフラグ系のカラムへのインデックスってあまり意味ない ですよね? ログの一覧をばっと表示するページがあるんですが、イン デックスはちゃんと張ってあるしWHERE句でもORDER句でも そのカラム以外を指定していないのにフルスキャンになっ てしまいます
5. 数日前のぼく 「よーしこれをいい感じに答えて Rails DMのネタにしちゃうぞー！」
6. あとはMySQLの話です 本当に、ごめんなさい
7. こんなことを聞かれました 日次で特定の未処理フラグを検索して更新するRails上の Rakeタスクがあるんですが、検索が遅いです。 でもフラグ系のカラムへのインデックスってあまり意味ない ですよね? ログの一覧をばっと表示するページがあるんですが、イン デックスはちゃんと張ってあるしWHERE句でもORDER句でも そのカラム以外を指定していないのにフルスキャンになっ てしまいます どちらも、カーディナリティが重要
8. カーディナリティとは? ◎ SHOW INDEX で確認できる ◎ テーブルの行数に対して ◦ 種類が少ない -> カーディナリティが低い ◦ 種類が多い -> カーディナリティが高い カラムの値の種類の数
9. 注意 今回のセッションでは、議論を簡潔にするために インデックスがあるテーブルに対する INSERT/UPDATE/DALETE 時の更新オーバーヘッドについて 議論のスコープから除外しています ご了承ください
10. カーディナリティとインデックスの関係性 ◎ カーディナリティが低いカラムに対するインデックスはあまり意味 がない ◎ クエリでfetchされる行が、全体の30%を超える場合はオプティマイ ザがフルスキャンを選択 ◎ インデックスを付与するときの基準は、だいたい95%くらい ◎ innoDBに置ける SHOW INDEXは統計情報(ランダムにサンプリングし た結果)なので実際に計算した値と異なる 一般的には、以下のように理解されてると思います
11. 1. カーディナリティ 　とインデックスの 　　関係性(真)
12. カーディナリティとインデックスの関係性 これは本当か？ ◎ カーディナリティが低いカラムに対するインデックスはあまり意味 がない ◎ クエリでfetchされる行が、全体の30%を超える場合はオプティマイ ザがフルスキャンを選択 ◎ インデックスを付与するときの基準は、だいたい95%くらい ◎ innoDBに置ける SHOW INDEXは統計情報(ランダムにサンプリングし た結果)なので実際に計算した値と異なる 見ていきましょう
13. 検証用のデータを作る
14. 検証用のデータを作る 1000万件超のレコードをインサート
15. 検証用のデータを作る それぞれのカラムにINDEXを張る
16. 検証用のデータを作る 検証用のインデックスを張らないテーブルもつくっておく
17. カーディナリティが低いカラムに対する インデックスは本当に意味がないか? カーディナリティが低いlowカラムで 条件を指定するクエリを作成した
18. カーディナリティが低いカラムに対する インデックスは本当に意味がないか? EXPLAINを使って実行計画を確かめる 当たり前だが、インデックスを張っていない テーブルの参照はフルスキャンになる。
19. カーディナリティが低いカラムに対する インデックスは本当に意味がないか? NO, Fetchされる行数が少なければ、 インデックスは効果を発揮する インデックス有り インデックス無し 実行結果 490458 rows in set (1.59 sec) 490458 rows in set (6.57 sec) 冒頭のように、カーディナリティは低いが 検索結果の行数が少ないクエリがよく利用される場合、 インデックスの利用を考えてもよい
20. 逆にカーディナリティが高いカラムはどうだろうか? highカラムの取り得る値は0-1の乱数(浮動小数)なので、 検索結果は全データの10%くらいになるはず。 しかし、インデックスは利用されず、 フルスキャンになってしまっている... カーディナリティが高いカラムはどうか?
21. 実際に実行してみる カーディナリティが高いカラムはどうか? やはり遅い
22. FORCE INDEXを指定して、 利用するインデックスを明示してみる カーディナリティが高いカラムはどうか?
23. 先ほどのままのクエリ FORCE INDEXで明示的に指定 実行結果 1666655 rows in set (6.80 sec) 1666655 rows in set (12.91 sec) カーディナリティが高いカラムはどうか? 実際にクエリを実行し、速度を比較してみる Fetchされるであろうデータが全体の およそ10%なのにも関わらず、 インデックスを利用した場合の方が遅くなってしまう
24. カーディナリティが高いカラムはどうか? これはちゃんとリファレンスに明記されている 小さなテーブルまたは、レポートクエリーが行の大半またはすべてを処理する 大きなテーブルに対するクエリーでは、インデックスはあまり重要ではありませ ん。 クエリーで行の大半にアクセスする必要がある場合は、順次読み取る方が、 インデックスを処理するより高速です。 クエリーですべての行が必要でない場合でも、順次読み取りは、ディスクシー クを最小にします。 https://dev.mysql.com/doc/refman/5.6/ja/mysql-indexes.html
25. では実際に、SELECTで指定するカラムを highだけにしてみる カーディナリティが高いカラムはどうか? - SELECTの内容による FORCE INDEXを指定してなくても、 インデックスが利用される
26. 実行してみる カーディナリティが高いカラムはどうか? - SELECTの内容による はやくなった！
27. 十分に検索結果が少なくなるようなクエリに 修正してみる 先ほどのクエリのおよそ1/10 カーディナリティが高いカラムはどうか? - fetchされる行数も関係している 今度はインデックスが利用されるようになった
28. カーディナリティが高いカラムはどうか? - fetchされる行数も関係している これはMySQL5.1のリファレンスの内容で、 少なくとも5.6以降のリファレンスでは 割合について明記されていない 実は最初にあげた、 クエリでfetchされる行が、全体の30%を超える場合は オプティマイザがフルスキャンを選択 http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/mysql-indexes.html
29. 1万件の同様のレコードを持つテーブルを作成し、 検索条件の同じクエリを試してみる (つまり条件にマッチするレコードの割合はほぼ同じ) カーディナリティが高いカラムはどうか? - fetchされる行数も関係している インデックスが利用される
30. つまりオプティマイザは カーディナリティが高いカラムはどうか? ◎ SELECTで指定されるカラムの内、インデックスのないカ ラムの有無とその量 ◎ fetchされる行数の割合(≒カーディナリティ) ◎ fetchされる行数 を総合的に判断してインデックス利用の是非を判断する
31. まとめ ◎ カーディナリティの低いカラムに対するインデックス ◦ 意味がある場合もある ◎ カーディナリティが高いカラムに対するインデックス ◦ SELECT句の内容によってインデックスを利用しない場 合もある ◦ fetchされる行数によってはインデックスを利用しない場 合もある ◦ インデックス利用の閾値は30%で固定ではなく ◦ fetchされる行数と割合によって変動する ◦ フルスキャンは必ずしも悪ではない
32. 2. 採用の話
33. リブセンスでは一緒に働く仲間を募集中です！ いろんなサービスを作ってるので、 興味ある方は話しかけてください :) We are hiring! ぼくのいるチームが開発しているIESHILは PostgreSQLです。悪しからず。
34. MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.3.1 MySQL のインデックスの使用の仕組み (https://dev.mysql.com/doc/refman/5.6/ja/mysql-indexes.html) MySQL :: MySQL 5.1 リファレンスマニュアル :: 6.4.5 MySQLにおけるインデックスの使用 (http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/mysql-indexes.html) MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.20 フルテーブルスキャンを回避する方法 (https://dev.mysql.com/doc/refman/5.6/ja/how-to-avoid-table-scan.html) mysqlのindexとorder by の速度を改善する。 - お前の血は何色だ!! 4 (http://d.hatena.ne.jp/rti7743/20080629) 漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!! (http://nippondanji.blogspot.com/2009/03/mysqlexplain.html) 参考文献
35. ご静聴ありがとうございました！