基本用語の解説付きインデックス・チューニング入門

B-Tree・カーディナリティ・実行計画・オプティマイザ・プロファイラ

データベースのインデックス・チューニングで出てくる用語は、O/Rマッパーに慣れていると聞きなれない言葉かもしれません。

本記事では用語を解説しながら、RailsとMongoidでのインデックス・チューニングの一歩として基本的な手順をご紹介します。

はじめに

こんにちは。KitchHikeエンジニアの小川です。

Railsのようなフルスタックフレームワークを使っていると、データベースを気にする機会はだいぶ減るのではないでしょうか。ActiveRecordのようなO/Rマッパーが上手く抽象化してくれるおかげで、生のSQLを書く必要はほとんどなくなりました。

一方でその抽象化は、データベースを見えにくくしているともいえます。データベースを直接見る機会が減ってしまうことで、知らず知らずのうちにトラブルの原因となっていることも少なくありません。

今回はデータベースに親しむ第一歩として、Rails / MongoDB の組み合わせでのインデックス・チューニングについてご紹介したいと思います。

インデックス・チューニングってなんで必要なの？

さて、本題に入る前に、そもそもインデックス・チューニングのインデックスとはなんなのでしょうか。

DBのインデックス(索引)とは、一言でいえば 目的のデータに対して素早くアクセスするためのデータ構造 です。たとえば本の索引では、索引をたどることで、本文をくまなく探さずとも、目的のキーワードが何ページにあるかを見つけることができます。

DBのインデックスも、本の索引と同様です。

インデックスがつけられたカラムであれば、テーブル内の目的のデータに対して高速にアクセスできます。インデックス・チューニングとは、データに高速にアクセスするために、適切にインデックスをつけてあげることを指します。

このインデックスがないと、コレクションをすべて見なくては目的のデータは見つけられません。データが少ないうちは問題になりませんが、ある程度までデータが増えてくるとパフォーマンスの問題が出てくるようになります。だからこそ、インデックス・チューニングが必要になるのです。

補足: MongoDBとRDB

ここまでご覧になって「MongoDBってNoSQLじゃないの？RDBとはインデックスの仕組みが違うんじゃないの？」と思われるかもしれません。

結論からいえば、MongoDBとRDBのインデックス・チューニングは、やり方はほとんど変わりません。

というのも、MongoDBもRDBもインデックスのデータ構造にはほぼ同じものが使われているからです。一般的なDBのインデックスにはB-Tree / B+Treeが使われています。そして、MongoDBもそれと同様にB-Treeが使用されているのです。

以降ではMongoDBでのパフォーマンス・チューニングの手順を説明いたしますが、RDBとその手順に大きな隔たりはありません。MySQLやPostgresSQLでも同じような手順、同じようなツールを使って調べることとなるはずです。

用語解説: B-Tree / B+Tree

ツリー状の構造で、1つのノードから3つ以上の子ノードを持っているのが特徴のデータ構造です。検索性能が優れており、RDBのインデックスによく使われています。

インデックス・チューニングをやってみよう

では早速、インデックス・チューニングをやってみましょう。 以下の手順でチューニングを進めていきます。

1. スロークエリを収集する
2. スロークエリの実行計画を見てみる
3. インデックスをつける

1. スロークエリを収集する

用語解説: スロークエリ

応答に時間がかかっている遅いクエリのこと

用語解説: プロファイラ

DBで実行されたコマンドの情報を収集し、分析するためのツール

スロークエリの設定をしよう

インデックス・チューニングにあたってまず行うべきは、スロークエリを特定することです。 一般的にはログに出力したり、プロファイラを有効にして調査したりすることで特定をします。

今回は調査がしやすいプロファイラを使ってスロークエリを検出してみましょう。 MongoShellを起動して、以下のコマンドを打ってみます。

> db.setProfilingLevel(1, 5)
{ "was" : 0, "slowms" : 100, "ok" : 1 }

第一引数でログレベル、第二引数で何ms以上のクエリを記録するかを指定します。スロークエリを見たいときは、ログレベルを1に指定してください。

これで system.profile コレクションに5ms以上かかったクエリが記録されるようになります。いったんMongoShellを終了して、スロークエリが記録されるのを待ちます。

スロークエリを見てみよう

しばらく時間をあけた後、記録したスロークエリの内訳をコレクション別に見てみましょう。再びMongoShellを起動して、以下のクエリを実行します。

> db.system.profile.aggregate([
...   { $group: { _id: "$ns", count: { $sum: 1 } } }
... ])
{ "_id" : "kitchhike_staging.users", "count" : 23 }
{ "_id" : "kitchhike_staging.message_boxes", "count" : 465 }
{ "_id" : "kitchhike_staging.popup_photos", "count" : 18 }
(...)

このクエリはコレクション別にスロークエリ(今回でいうと5ms以上かかっているクエリ)が何件あるかを集計したものです。

これでどのコレクションでスロークエリが発生しているのか、ざっくりと目安がつきました。 message_boxes コレクションが多いですね・・・。今回はこのコレクションをチューニングの対象にしてみましょう。

続いて、具体的にどのようなクエリが遅いのか調べてみましょう。以下のクエリは message_boxes コレクションでレスポンスが遅いクエリの上位10件を出力しています。

> db.system.profile.find({ns: "kitchhike_staging.message_boxes" }).sort({millis: -1}).limit(10);
{ "op" : "query", "ns" : "kitchhike_staging.message_boxes", "query" : { "event_id" : ObjectId("5a8bba3821f29027d8afc36c") }, (...), "millis" : 6, (...), "inputStage" : { "stage" : "COLLSCAN", "filter" : { "event_id" : { "$eq" : ObjectId("5a8bba3821f29027d8afc36c") } }, (...) }
{ "op" : "query", "ns" : "kitchhike_staging.message_boxes", "query" : { "event_id" : ObjectId("5a8bbac721f29027d8afc379") }, (...), "millis" : 6, (...), "inputStage" : { "stage" : "COLLSCAN", "filter" : { "event_id" : { "$eq" : ObjectId("5a8bbac721f29027d8afc379") } }, (...) }

項目	意味
ns	コレクション名
query	対象のクエリ
millis	かかった時間(ms)
inputStage.stage	データのアクセス方法

クエリを見ると、みな条件は一緒ですね。どうやら event_id が検索キーになっているクエリが遅いようです。 これを見て、どのような条件のクエリが遅くなっているかが分かりました！今度はこのクエリがどのように実行されているかを解析しましょう。

2. スロークエリの実行計画を見てみる

用語解説: 実行計画

DBのクエリをどう処理するかの内部的な実行手順の情報です。実行計画はDB内部でいくつかの計画が作成され、そのうち最適と判断されたものの一つが実際に実行されます。

用語解説: オプティマイザ

上記の実行計画を作り、その中から最適なものの選択をおこなう機能です。

実行計画の確認

クエリがどのように実行されているかは、DBの実行計画を見ることでわかります。この実行計画は、MongoDBの explain メソッドで見ることができます。

この explain はO/Rマッパーから実行することもできます。今回は rails console からMongoidを使って explain してみましょう。

> pp MessageBox.where(event_id: "5a8bba3821f29027d8afc36c").explain[:queryPlanner]
{"plannerVersion"=>1,
 "namespace"=>"kitchhike_staging.message_boxes",
 "indexFilterSet"=>false,
 "parsedQuery"=>
  {"event_id"=>{"$eq"=>BSON::ObjectId('5a8bba3821f29027d8afc36c')}},
 "winningPlan"=>
  {"stage"=>"COLLSCAN",
   "filter"=>{"event_id"=>{"$eq"=>BSON::ObjectId('5a8bba3821f29027d8afc36c')}},
   "direction"=>"forward"},
 "rejectedPlans"=>[]}

queryPlanner では実際にオプティマイザが選択した実行計画の情報を見ることができます。 https://docs.mongodb.com/manual/reference/explain-results/#queryplanner

項目	意味
namespace	コレクション名
winningPlan	オプティマイザが選択した実行計画
rejectedPlans	オプティマイザが選択しなかった実行計画

winningPlan が実際に選択された実行計画となります。この winningPlan のなかで見ていただきたいのが stage の項目です。これはコレクションのデータがどのように探索されたかを示す項目となります。

stage の種類はいくつかあるのですが、最低限以下2つだけ覚えておけばよいかと思います。

stage名	意味
COLLSCAN	フルスキャン。コレクションのすべてのデータにアクセスします。
IXSCAN	インデックススキャン。インデックスを使ってデータにアクセスします。

遅いクエリの stage がもし COLLSCAN になっているようでしたら、インデックスを適切につけてあげることで高速化が期待できます。

今回でいうと、

 "winningPlan"=>
  {"stage"=>"COLLSCAN",
   "filter"=>{"event_id"=>{"$eq"=>BSON::ObjectId('5a8bba3821f29027d8afc36c')}},
   "direction"=>"forward"},

stage は COLLSCAN になっているので、インデックスをつけることで改善できそうですね。

実行計画をもっと見てみる

実行計画をもっと詳細に見たい場合は、 executionStats を使います。

> pp MessageBox.where(event_id: "5a8bba3821f29027d8afc36c").explain[:executionStats]
{"executionSuccess"=>true,
 "nReturned"=>1,
 "executionTimeMillis"=>6,
 "totalKeysExamined"=>0,
 "totalDocsExamined"=>11034,
 "executionStages"=>
  {"stage"=>"COLLSCAN",
   "filter"=>{"event_id"=>{"$eq"=>BSON::ObjectId('5a8bba3821f29027d8afc36c')}},
   "nReturned"=>1,
   "executionTimeMillisEstimate"=>10,
   "works"=>11036,
   "advanced"=>1,
   "needTime"=>11034,
   "needFetch"=>0,
   "saveState"=>86,
   "restoreState"=>86,
   "isEOF"=>1,
   "invalidates"=>0,
   "direction"=>"forward",
   "docsExamined"=>11034},
 "allPlansExecution"=>[]}

https://docs.mongodb.com/manual/reference/explain-results/#executionstats

項目	意味
executionTimeMillis	かかった時間(ms)
totalKeysExamined	探索されたインデックス数
totalDocsExamined	探索されたドキュメント数

executionStats を見ると実際に探索されたデータの数や、実行にかかった時間も見ることができます。

今回、 totalKeysExamined は0でした。つまり全くインデックスは使われていないことが分かります。

3. インデックスをつける

スロークエリの実行計画を見て、インデックスが使われておらず、フルスキャンになっていることが分かりました。

このクエリが遅いのは検索キーである event_id フィールドにインデックスがつけられていないのが原因のようです。

インデックスを追加する

ということで、対象のモデルにインデックスを定義します。データ量が多いコレクションの場合は、 background オプションを true にしておくとよいでしょう。

class MessageBox
  include Mongoid::Document
  include Mongoid::Timestamps

  index({ event_id: 1 }, { background: true })
  # ...
end

つづいて、先ほどのモデル定義をもとに、DBにインデックスを追加します。 Mongoidにはインデックス追加用のrakeタスクが準備されています。以下のようにコマンドを実行してみましょう。

# すべてのModelに対し、インデックスを追加
bundle exec rake db:mongoid:create_indexes

# 以下のようにして個別に対象のModelを指定することもできる
bundle exec rake db:mongoid:create_indexes[MessageBox]

ちなみに、以下のように rails console からModelを指定してインデックスをつけることもできます。

MessageBox.create_indexes

再び実行計画を確認する

インデックスをつけたので、再度実行計画を見てみましょう。 rails console を立ち上げます。

> pp MessageBox.where(event_id: "5a8bba3821f29027d8afc36c").explain[:queryPlanner]
{"plannerVersion"=>1,
 "namespace"=>"kitchhike_staging.message_boxes",
 "indexFilterSet"=>false,
 "parsedQuery"=>
  {"event_id"=>{"$eq"=>BSON::ObjectId('5a8bba3821f29027d8afc36c')}},
 "winningPlan"=>
  {"stage"=>"FETCH",
   "inputStage"=>
    {"stage"=>"IXSCAN",
     "keyPattern"=>{"event_id"=>1},
     "indexName"=>"event_id_1",
     "isMultiKey"=>false,
     "direction"=>"forward",
     "indexBounds"=>
      {"event_id"=>
        ["[ObjectId('5a8bba3821f29027d8afc36c'), ObjectId('5a8bba3821f29027d8afc36c')]"]}}},
 "rejectedPlans"=>[]}

stageに IXSCAN と出ています！インデックスが使われるようになりました。

つづいて、 executionStats も見てみましょう。

> pp MessageBox.where(event_id: "5a8bba3821f29027d8afc36c").explain[:executionStats]
{"executionSuccess"=>true,
 "nReturned"=>1,
 "executionTimeMillis"=>0,
 "totalKeysExamined"=>1,
 "totalDocsExamined"=>1,
 "executionStages"=>
  {"stage"=>"FETCH",
   "nReturned"=>1,
   "executionTimeMillisEstimate"=>0,
   "works"=>2,
   "advanced"=>1,
   "needTime"=>0,
   "needFetch"=>0,
   "saveState"=>0,
   "restoreState"=>0,
   "isEOF"=>1,
   "invalidates"=>0,
   "docsExamined"=>1,
   "alreadyHasObj"=>0,
   "inputStage"=>
    {"stage"=>"IXSCAN",
     "nReturned"=>1,
     "executionTimeMillisEstimate"=>0,
     "works"=>2,
     "advanced"=>1,
     "needTime"=>0,
     "needFetch"=>0,
     "saveState"=>0,
     "restoreState"=>0,
     "isEOF"=>1,
     "invalidates"=>0,
     "keyPattern"=>{"event_id"=>1},
     "indexName"=>"event_id_1",
     "isMultiKey"=>false,
     "direction"=>"forward",
     "indexBounds"=>
      {"event_id"=>
        ["[ObjectId('5a8bba3821f29027d8afc36c'), ObjectId('5a8bba3821f29027d8afc36c')]"]},
     "keysExamined"=>1,
     "dupsTested"=>0,
     "dupsDropped"=>0,
     "seenInvalidated"=>0,
     "matchTested"=>0}},
 "allPlansExecution"=>[]}

totalDocsExamined 、totalDocsExamined の値も大幅に変わりました。これは今まで全件スキャンしていたものが、 event_id のインデックスが使われるようになって、1回のインデックス探索でデータにたどり着けるようになったためです。

executionTimeMillis も1ms以下となり、これでスロークエリをひとつなくすことができました。

インデックスはどうつける？

用語解説: カーディナリティ

DBのフィールド(カラム)に含まれるデータの種類の高低の度合いを意味します。データの種類が少ないもの、たとえば性別はカーディナリティが低くなります。一方、フィールド内のデータを一意に近く絞り込めるものであるほど、カーディナリティは高くなります。

用語解説: 選択率

コレクション全体に対し、条件となる値によってドキュメントがどの程度まで絞り込めるかを示す数値です。条件となる値がたとえばキー値のように、一意に絞り込めるものであれば非常に低くなります。

インデックス・チューニングで大事なこと2つ

前節までで、インデックス・チューニングの基本的なステップをご紹介しました。

今回は単純なインデックスの追加の例でしたが、実際のところインデックスをどうつけるかはもっと考えどころが多い、複雑なものです。

それこそ Use The Index, Luke のようにインデックスだけを扱った書籍があるくらいです。ですので、このエントリーだけではとても扱いきれるものではありません。

ただ、さしあたって以下二点が大切なのはではないかと思います。

1. インデックスは闇雲につけない
2. インデックスを定期的に見直す

1. インデックスは闇雲につけない

インデックスは万能ではなく、トレードオフがあります。インデックスが使われれば基本的に読み込みは早くなりますが、その分ドキュメントの作成や更新といった書き込み処理は遅くなります。ドキュメントの作成 / 更新時に併せてインデックスの更新をする必要が出てくるからです。

ですからフルスキャンのクエリを見つけたから、「よし、インデックスをつけよう！」でいいわけではありません。そうするとすぐにインデックスが膨れ上がってしまいます。インデックスをつけよう / つけないでおこう、の見きわめが必要となるのです。

では、インデックスを付ける / 付けないの判断はどのような目安があるのでしょうか。その一つの指標がカーディナリティと選択率です。

インデックスはカーディナリティが高くて、選択率が低いものほど有効です。すなわち、値がよくばらついているコレクションのフィールドに対し、それを一意に近く絞り込めるキーにインデックスがつけられれば非常に効率的であるといえるでしょう。

SQL実践入門によればそのコレクション内のドキュメント数の5%〜10%程度まで絞り込めるかがどうかがインデックスを付けるか付けないかの目安であるとされます。今回の例でいえば、インデックスをつけた event_id フィールドは外部キーで、カーディナリティがかなり高いフィールドでした。

2. インデックスを定期的に見直す

もうひとつ重要なのはインデックスは折々に見直す必要があるということです。

「これでインデックスはばっちり！」と思っていても、それが1ヶ月後、2ヶ月後に変わらず機能していることはないと思っておくべきです。

なぜならば、アプリケーションは日々変化していくからです。データが増えていくことで、新たにスロークエリが発生したり、検索クエリが変わることでインデックスが使われなくなったりといったことは日常的に発生します。

そのためにもNewRelicのようなサービスを利用するなどして、日常的にモニタリングしておくことがとっても大事です。

インデックス・チューニングは楽しい

最後にもうひとつ大切な点を。インデックスをどうつけるかは奥深い、複雑なものです、といいましたが、一方で敷居の高いものとはとらえずにチューニングに取り組んでいただければと思います。

インデックス・チューニングが上手くいって高速化できたときはやっぱり嬉しくなります。

データ量によっては、今まで数百、数千msかかっていたクエリが一気に数msまで高速化されることは珍しくありません。その過程でインデックスのつけ方を考えて、色々試行錯誤するのも楽しいものです。

この記事をご覧いただいて、データベースに親しむ第一歩になれば幸いです。

まとめ

インデックス・チューニングの基本的な手順と用語のご紹介をしました。

• インデックス・チューニングは以下3つのステップでおこなう
  1. スロークエリの収集
  2. スロークエリの実行計画を調べる
  3. スロークエリにインデックスをつける
• インデックスは闇雲につけず、カーディナリティと選択率を考えてつけるようにしよう
• インデックス・チューニングは継続的なモニタリングが大事

この記事で解説した用語まとめ

用語	解説
B-Tree / B+Tree	ツリー状の構造で、1つのノードから3つ以上の子ノードを 持っているのが特徴のデータ構造です。検索性能が優れており、 RDBのインデックスによく使われています。
スロークエリ	応答に時間がかかっている遅いクエリのこと
プロファイラ	DBで実行されたコマンドの情報を収集し、分析するためのツール
実行計画	DBのクエリをどう処理するかの内部的な実行手順の情報です。 実行計画はDB内部でいくつかの計画が作成され、 そのうち最適と判断されたものの一つが実際に実行されます。
オプティマイザ	上記の実行計画を作り、その中から最適なものの選択を行う機能です。
カーディナリティ	DBのフィールド(カラム)に含まれるデータの種類の 高低の度合いを意味します。データの種類が少ないもの、 たとえば性別はカーディナリティが低くなります。一方、フィールド内の データを一意に近く絞り込めるものであるほど、カーディナリティは高くなります。
選択率	コレクション全体に対し、条件となる値によってドキュメントが どの程度まで絞り込めるかを示す数値です。条件となる値がたとえば キー値のように、一意に絞り込めるものであれば非常に低くなります。

We're Hiring!

キッチハイクでは、React Nativeエンジニア・Railsエンジニア・インターンエンジニアを募集中です！

www.wantedly.com

www.wantedly.com

www.wantedly.com

参考資料

最後に本記事を書くにあたって参考にさせていただいた書籍・記事の一覧です。こちらも是非ご覧いただければと思います。

• SQL実践入門
• MONGODB MANUAL - Explain Results
• MongoDB 3.0 での explain
• MongoDBが遅いときの切り分け方法
• Use The Index, Luke