【SQL】インデックス必要?explainで確認できない

0pt   2018-09-14 21:23
IT技術情報局

はじめに

rubyを学びはじめて3ヶ月目!
キータを使ってみたかった!マークダウンで書いてみたかった!

ということで、railsで簡単なタスク管理アプリを作る際に起きたことを共有します。

SQLでインデックスを追加したのに、テストデータによって使われてないことがあります。

SQLでインデックスを使って検索されるために、いくつかの条件でデータを作って調べてみました。

【SQL】インデックスとは

特定のカラムにインデックスを追加すると、データベースのテーブルから高速で取り出せるそう。
https://tech.nikkeibp.co.jp/it/members/ITPro/ITBASIC/20000919/1/ 

ということで、titleカラムにインデックスをつけました。

schema.rb create_table "tasks", force: :cascade do |t| t.string "title", limit: 500, default: "", null: false #省略 t.index ["title"], name: "index_tasks_on_title" t.index ["user_id"], name: "index_tasks_on_user_id" end

ただ本当に、インデックスを使ってデータを取り出しているか気になったので調べてみました。

【SQL】Explainというステートメント

ExplainはSQLでの実行計画が効率的に処理できているかを調べるステートメントです。

実行計画の処理については、
Seq Scan・・・テーブルを最初から最後といったように調べます。
Index Scan・・・Index情報から検索条件に合うインデックスを調べてくれます。
などがあります。

詳しく説明をすると一つの記事になりそうなので割愛します。

以下の記事がとてもわかりやすいスライドでまとめられています。
https://www.slideshare.net/MikiShimogai/postgre-sql-explain

【titleにインデックスつけたのにインデックス使われてない!からはじまった検証】 検証0:ダミーデータ(facker:Pokemon.name)50件から“ゴースト“を探す

68747470733a2f2f71696974612d696d6167652d
3行目のTask.where(title:”ああああああか”).explainをコンソールに入力の結果
  Seq Scan on tasks →シーケンシャルスキャン(上から順に調べる)が使われる:失敗

○データ数が足りないのかと条件変更 検証1:100万件のデータ(title1~title1000000で“title1000”)を探す

100万件のデータが浮かばれないのでコード非表示
 →シーケンシャルスキャン(上から順に調べる)が使われる:失敗

○検証1でtitleが繰り返されて生成されている(密度が薄い)のがだめだということで条件変更

下記記事より引用
インデックス使用状況の検証

非常に小さなテストデータを使用することも、結果に特に致命的な影響を与えます。

100,000行から1,000行を選択する場合は、インデックスが使用される可能性がありますが、100行から1行を選択する場合はインデックスはまず使用されません。

なぜなら、100行はおそらく1つのディスクページに収まるため、1ページを逐次読み取るよりも高速な計画は存在しないからです。
https://www.postgresql.jp/document/10/html/indexes-examine.html

ということで、君のデータは密度が薄かったのだよといわれていたので、fackerを駆使しschemaファイルに濃いデータを生成する。

検証2:20万件のデータ(200件以上の異なるデータ)から“hogehoge”を探す

10種類以上のtitleのデータ型に対応するデータを20種類を1000セット作る(20×1000=200000件)

seed.rb 1000.times do |n| 10.times do |n| title = Faker::Pokemon.name content = Faker::Pokemon.name limit_time = Faker::Date.between(2.days.ago, Date.today) status = Faker::Number.between(1, 3) priority_color = Faker::Color.color_name Task.create!(title: title, content: content, limit_time: limit_time, status: status, priority_color: priority_color, ) end 10.times do |n| title = Faker::Color.name 省略 end

Task.where(title:”hogehoge”).explainを入力…
68747470733a2f2f71696974612d696d6167652d

Bitmap Index Scan→インデックス使用される!:成功

まとめ

1.SQLでは最も効率の良いデータの実行計画(検索方法)を探します。
 Explainを使うと、どの実行計画を使おうとしているか表示してくれます。

2.単純な(100件から1件を探してくるような)データを探す時は、シーケンシャルスキャン(上から順に探す)を使っている(たとえ、インデックスをつけたとしても!)

3.SQLでインデックスが使われるのは、データ数が多くてなおかつ、そのデータの種類が多い場合です。

Source: rails tag

   ITアンテナトップページへ
情報処理/ITの話題が沢山。