Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Railsと考えるデータベースのインデックス戦略
Search
onunu
July 14, 2018
4
1.6k
Railsと考えるデータベースのインデックス戦略
Rails Developers Meetup 2018 Day 3 Extreme
進行スポンサーPRとして登壇させていただきました。
onunu
July 14, 2018
Tweet
Share
More Decks by onunu
See All by onunu
othlotech
onunu
1
3.1k
ビッグデータと機械学習の狭間で -データエンジニアに求められる役割-
onunu
5
1.3k
地球とSQL
onunu
2
460
Railtieでpluginを作ろう!
onunu
0
410
Featured
See All Featured
The Illustrated Children's Guide to Kubernetes
chrisshort
48
48k
The World Runs on Bad Software
bkeepers
PRO
65
11k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
44
6.8k
GraphQLの誤解/rethinking-graphql
sonatard
67
10k
Gamification - CAS2011
davidbonilla
80
5k
Building an army of robots
kneath
302
43k
Code Review Best Practice
trishagee
64
17k
jQuery: Nuts, Bolts and Bling
dougneiner
61
7.5k
Principles of Awesome APIs and How to Build Them.
keavy
126
17k
Build your cross-platform service in a week with App Engine
jlugia
229
18k
[RailsConf 2023 Opening Keynote] The Magic of Rails
eileencodes
28
9.1k
Ruby is Unlike a Banana
tanoku
97
11k
Transcript
In Rails Developers Meetup 2018 Day 3 Extreme @onunu sponsored
by Railsと考える データベースの インデックス戦略
Hello! おぬま りく@onunu IESHILという不動産の情報格差 をなんとかしていくサービスを 作っています データエンジニアです Twitter: @onunu_ Github:
onunu
0. 今日の話 -MySQL の index- 特にカーディナリティについて
こんなことを聞かれました 日次で特定の未処理フラグを検索して更新するRails上の Rakeタスクがあるんですが、検索が遅いです。 でもフラグ系のカラムへのインデックスってあまり意味ない ですよね? ログの一覧をばっと表示するページがあるんですが、イン デックスはちゃんと張ってあるしWHERE句でもORDER句でも そのカラム以外を指定していないのにフルスキャンになっ てしまいます
数日前のぼく 「よーしこれをいい感じに答えて Rails DMのネタにしちゃうぞー!」
あとはMySQLの話です 本当に、ごめんなさい
こんなことを聞かれました 日次で特定の未処理フラグを検索して更新するRails上の Rakeタスクがあるんですが、検索が遅いです。 でもフラグ系のカラムへのインデックスってあまり意味ない ですよね? ログの一覧をばっと表示するページがあるんですが、イン デックスはちゃんと張ってあるしWHERE句でもORDER句でも そのカラム以外を指定していないのにフルスキャンになっ てしまいます どちらも、カーディナリティが重要
カーディナリティとは? ◎ SHOW INDEX で確認できる ◎ テーブルの行数に対して ◦ 種類が少ない ->
カーディナリティが低い ◦ 種類が多い -> カーディナリティが高い カラムの値の種類の数
注意 今回のセッションでは、議論を簡潔にするために インデックスがあるテーブルに対する INSERT/UPDATE/DALETE 時の更新オーバーヘッドについて 議論のスコープから除外しています ご了承ください
カーディナリティとインデックスの関係性 ◎ カーディナリティが低いカラムに対するインデックスはあまり意味 がない ◎ クエリでfetchされる行が、全体の30%を超える場合はオプティマイ ザがフルスキャンを選択 ◎ インデックスを付与するときの基準は、だいたい95%くらい ◎
innoDBに置ける SHOW INDEXは統計情報(ランダムにサンプリングし た結果)なので実際に計算した値と異なる 一般的には、以下のように理解されてると思います
1. カーディナリティ とインデックスの 関係性(真)
カーディナリティとインデックスの関係性 これは本当か? ◎ カーディナリティが低いカラムに対するインデックスはあまり意味 がない ◎ クエリでfetchされる行が、全体の30%を超える場合はオプティマイ ザがフルスキャンを選択 ◎ インデックスを付与するときの基準は、だいたい95%くらい
◎ innoDBに置ける SHOW INDEXは統計情報(ランダムにサンプリングし た結果)なので実際に計算した値と異なる 見ていきましょう
検証用のデータを作る
検証用のデータを作る 1000万件超のレコードをインサート
検証用のデータを作る それぞれのカラムにINDEXを張る
検証用のデータを作る 検証用のインデックスを張らないテーブルもつくっておく
カーディナリティが低いカラムに対する インデックスは本当に意味がないか? カーディナリティが低いlowカラムで 条件を指定するクエリを作成した
カーディナリティが低いカラムに対する インデックスは本当に意味がないか? EXPLAINを使って実行計画を確かめる 当たり前だが、インデックスを張っていない テーブルの参照はフルスキャンになる。
カーディナリティが低いカラムに対する インデックスは本当に意味がないか? NO, Fetchされる行数が少なければ、 インデックスは効果を発揮する インデックス有り インデックス無し 実行結果 490458 rows
in set (1.59 sec) 490458 rows in set (6.57 sec) 冒頭のように、カーディナリティは低いが 検索結果の行数が少ないクエリがよく利用される場合、 インデックスの利用を考えてもよい
逆にカーディナリティが高いカラムはどうだろうか? highカラムの取り得る値は0-1の乱数(浮動小数)なので、 検索結果は全データの10%くらいになるはず。 しかし、インデックスは利用されず、 フルスキャンになってしまっている... カーディナリティが高いカラムはどうか?
実際に実行してみる カーディナリティが高いカラムはどうか? やはり遅い
FORCE INDEXを指定して、 利用するインデックスを明示してみる カーディナリティが高いカラムはどうか?
先ほどのままのクエリ FORCE INDEXで明示的に指定 実行結果 1666655 rows in set (6.80 sec)
1666655 rows in set (12.91 sec) カーディナリティが高いカラムはどうか? 実際にクエリを実行し、速度を比較してみる Fetchされるであろうデータが全体の およそ10%なのにも関わらず、 インデックスを利用した場合の方が遅くなってしまう
カーディナリティが高いカラムはどうか? これはちゃんとリファレンスに明記されている 小さなテーブルまたは、レポートクエリーが行の大半またはすべてを処理する 大きなテーブルに対するクエリーでは、インデックスはあまり重要ではありませ ん。 クエリーで行の大半にアクセスする必要がある場合は、順次読み取る方が、 インデックスを処理するより高速です。 クエリーですべての行が必要でない場合でも、順次読み取りは、ディスクシー クを最小にします。 https://dev.mysql.com/doc/refman/5.6/ja/mysql-indexes.html
では実際に、SELECTで指定するカラムを highだけにしてみる カーディナリティが高いカラムはどうか? - SELECTの内容による FORCE INDEXを指定してなくても、 インデックスが利用される
実行してみる カーディナリティが高いカラムはどうか? - SELECTの内容による はやくなった!
十分に検索結果が少なくなるようなクエリに 修正してみる 先ほどのクエリのおよそ1/10 カーディナリティが高いカラムはどうか? - fetchされる行数も関係している 今度はインデックスが利用されるようになった
カーディナリティが高いカラムはどうか? - fetchされる行数も関係している これはMySQL5.1のリファレンスの内容で、 少なくとも5.6以降のリファレンスでは 割合について明記されていない 実は最初にあげた、 クエリでfetchされる行が、全体の30%を超える場合は オプティマイザがフルスキャンを選択 http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/mysql-indexes.html
1万件の同様のレコードを持つテーブルを作成し、 検索条件の同じクエリを試してみる (つまり条件にマッチするレコードの割合はほぼ同じ) カーディナリティが高いカラムはどうか? - fetchされる行数も関係している インデックスが利用される
つまりオプティマイザは カーディナリティが高いカラムはどうか? ◎ SELECTで指定されるカラムの内、インデックスのないカ ラムの有無とその量 ◎ fetchされる行数の割合(≒カーディナリティ) ◎ fetchされる行数 を総合的に判断してインデックス利用の是非を判断する
まとめ ◎ カーディナリティの低いカラムに対するインデックス ◦ 意味がある場合もある ◎ カーディナリティが高いカラムに対するインデックス ◦ SELECT句の内容によってインデックスを利用しない場 合もある
◦ fetchされる行数によってはインデックスを利用しない場 合もある ◦ インデックス利用の閾値は30%で固定ではなく ◦ fetchされる行数と割合によって変動する ◦ フルスキャンは必ずしも悪ではない
2. 採用の話
リブセンスでは一緒に働く仲間を募集中です! いろんなサービスを作ってるので、 興味ある方は話しかけてください :) We are hiring! ぼくのいるチームが開発しているIESHILは PostgreSQLです。悪しからず。
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) 参考文献
ご静聴ありがとうございました!