なぜ、SQLは重たくなるのか?──『SQLパフォーマンス詳解』の翻訳者が教える原因と対策

『SQLパフォーマンス詳解』の翻訳者の松浦隼人さんに、8つの「SQLが重たくなる原因とその対策」を聞きました。システムのボトルネックになるような「問題のあるSQL」を回避するノウハウを学びましょう。

なぜ、SQLは重たくなるのか?──『SQLパフォーマンス詳解』の翻訳者が教える原因と対策

データの操作や定義をする言語「SQL」は、どのような領域を担うエンジニアにとっても必修科目です。しかし、その仕様をきちんと理解し、パフォーマンスに優れたSQLを書ける方はそれほど多くありません。問題のあるSQLを書いてしまい、知らぬ間にそれがシステムのボトルネックになってしまう事態はよく発生します。

では、どうすればそうした事態を回避できるのでしょうか? そのノウハウを学ぶため、今回は『SQLパフォーマンス詳解』の翻訳者であり、自身もエンジニアでもある松浦隼人(まつうら・はやと/@dblmktさんに8つの「SQLが重たくなる原因とその対策」について聞きました。実地で技術を磨き上げたスペシャリストが語る珠玉のメソッド、全エンジニアにとって必見の内容です。

【原因(1)】ORMが生成するSQLを確認していない

──本日はよろしくお願いします! 1つ目は、「ORMが生成するSQLを確認していない」。これは特に、若手エンジニアの場合はやってしまいがちかもしれないですね。

松浦 そうですね。ORMは、どんなときでも適切なSQLを生成する魔法のツールではありません。使い方を工夫しなければ、効率の悪いSQLが生成されてしまうケースだってあるんです。だからこそ、その内容をきちんと確認することが重要になってきます。

1

松浦隼人さん

──「効率の悪いSQL」の例は、どのようなものがありますか?

松浦 最も有名なのは、「N+1問題」です。たとえば、データベース内にあるブログ記事の情報を取得するケースを考えてください。

それぞれのブログ記事には、「料理」とか「日記」などのタグが紐づいており、タグ情報は別テーブルにあるとします。通常であれば、全データを取得する際にはJOIN句を用いるなどして、1つのSELECT文だけでこと足りるでしょう。

しかしORMの場合、仮に10記事取得するケースだとSELECT文が「1回(記事一覧を取得)+10回(各記事に紐づくタグを取得)=合計11回」発行されてしまうことがあります。これがN+1問題です。この問題が発生すると、クエリ数が指数関数的に増え、パフォーマンス劣化につながってしまいます。

──それを避けるには、何をすればいいのでしょうか?

松浦 有名なORMのほとんどには、JOIN句を生成するなどN+1問題を回避するための機能が備わっています。その機能をきちんと理解し、適切に使用するといいでしょう。

もしORMの機能を使っても問題が避けられない場合には、素のSQL文を実行することも検討すべきです。もちろんその際には、検索条件に設定される値によってSQLインジェクションが発生しないか、チェックを忘れないようにしてください。

【原因(2)】複合インデックスを適切に使えていない

──次は、「複合インデックスを適切に使えていない」。どのようなときに、この問題は発生するのでしょうか?

松浦 たとえば、社員の情報が格納されたテーブルがあり、複合インデックスとして(社員ID, 部署ID)を設定しているとします。このテーブルに対し、「部署ID」のみをWHERE句条件に指定した場合には、インデックスが無効になってしまうんです。

なぜかというと、データベースは複合インデックスのエントリをソートする際、インデックスの定義に書かれている順序に従って列を識別するためです。つまりこの場合は、インデックスが作成される際に「社員ID」の列がまずソートされ、「社員ID」に同じ値が複数あるときに限り「部署ID」の列もソートされます。

そして、検索も「社員ID」⇒「部署ID」の順で実施されるため、「部署ID」だけではインデックスが効かないというわけです。

四角形の各ブロックはインデックスツリー内にあるノードを示す。 (社員ID, 部署ID)という複合インデックスが張られている場合、社員IDの同値は隣接しているものの、部署IDの同値は隣接しているわけではない。このようなツリーでは、 2番目の列だけで検索するクエリは役に立たなくなる

──この問題を避けるには、どのような対策を取るべきですか?

松浦 「どのようなWHERE句が使用されるケースが多いのか」を事前にリサーチし、それに沿った形でインデックスを張ることが重要になります。たとえば、WHERE句の条件に「部署ID」が使用されるケースが多いならば、(部署ID, 社員ID)という順の複合インデックスにした方が効果的でしょう。

むやみに複合インデックスを張っても、パフォーマンスは改善されません。データベースが利用される状況に応じて、適切な複合インデックスを張ることが大切なんです。

【原因(3)】複合インデックスの走査範囲を考慮していないため、大なり小なり記号やBETWEENが遅い

3

松浦 「大なり小なり記号やBETWEENが遅い」というのも、先ほど解説した複合インデックスに関連したものです。たとえば、社員の情報が格納されたテーブルがあり、複合インデックスが(誕生日, 部署ID)で設定されているとします。

この場合にWHERE句として「誕生日(BETWEEN句)」「部署ID」を使用した場合、まずはBETWEEN句により誕生日の大小比較の走査が走り、その後に部署IDが走査されるため、検索される範囲が非常に広くなってしまうんです。

これを避けるには、(部署ID, 誕生日)という順の複合インデックスに変えることがおすすめです。そうすることで、部署IDが合致したデータのみ誕生日によるBETWEEN検索がかかるため、走査する範囲が小さくなります。

4

四角形の各ブロックはインデックスツリー内にあるノードを示し、青枠は走査される範囲を示す。「誕生日 1971-01-01~1971-01-09」「部署ID 27」という条件で検索した場合、走査される範囲にはこれだけの差が生じる

──なるほど! 確かにその通りですね。

松浦 要するに、インデックスのどこからどこまでが走査されるのかをイメージしてWHERE句条件を指定しなければ、ムダなアクセスが発生する可能性があるということなんです。

【原因(4)】LIKE句において、ワイルドカードが文字列の前方についている

松浦 これは簡単に言うと、LIKE句の%(ワイルドカード)を使用する際に、データベース内でどのような走査がされているのかを理解しなければ、非効率的な検索となってしまうということです。実は、インデックスの走査においてLIKEが有効なのは、ワイルドカードの前までなんです。

5

青枠は走査される範囲を示す

たとえば「LIKE 'WIN%D'」のように真ん中に%を入れた場合、%の前である「WIN」まではインデックスが効きます。一方で、「LIKE '%WIND'」のように%が前方につく「後方一致検索」の場合ですと、インデックスは無効となり非常に重たいクエリになってしまいます。

──その前提があったとして、「それでも後方一致検索をしたい」場合には、どうすれば良いのでしょうか?

松浦 そこまでいくとリレーショナルデータベースが苦手とする領域になるので、代わりにElasticsearchやApache Solrなどの全文検索システムを使うことが必要になります。つまり、リレーショナルデータベースが得意な検索条件と苦手な検索条件を理解しているからこそ、これらのツールを適切に使いわけることが可能になるんです。

【原因(5)】テーブル結合が不得意なデータベースで、テーブル結合を多用している

エンジニアHubに会員登録すると
続きをお読みいただけます(無料)。
登録のメリット
  • すべての過去記事を読める
  • 過去のウェビナー動画を
    視聴できる
  • 企業やエージェントから
    スカウトが届く