こんにちは。株式会社SunAsteriskでエンジニアをやっているtakeです。
この記事はSun* Advent Calendar 2022の2日目の記事です。

概要

バッチが終わらない、検索が重すぎる、そんな時はチューニングを行いましょう。
今記事では私が業務で基幹システムのDB設計を行なった際に実際に行ったチューニング手法をいくつか紹介したいと思います。

執筆者について

本当に信用していいの？という方のために書いた人のプロフィールを少々記載してみます。。

項目	概要
ハンドルネーム	take
性別	男
好きな食べ物	カロリーが高くて太りそうな食べ物全般
好きなeditor	jetBrains(ideaVim併用)
得意領域	BE,Infraの設計、構築

書いた人は現在、株式会社SunAsteriskという会社でエンジニアとして働いてます。得意領域はバックエンドの設計開発ではありつつ、フロントエンド、インフラ領域も臨機応変に対応する何でも屋さんエンジニアとして、
最近はReact/ts/Azure環境でフロント/インフラ担当として開発に携わったりしてます。
プライベートではは、WebRTCやWebSocketなどの技術に興味があり、個人プロダクトを作りながら遊んでますが、Splatoon３などと言うけしからんゲームが発売されたことにより個人プロダクト開発が滞ってます。ダメダメエンジニアです。
ダメダメな私ですが、運が良く１００万レコードを超えるようなテーブルを複数設計、運用するような機会があり今記事を執筆させて頂くに至ります。

対象読者

テーブルの設計の基礎を知りたい方
おおよそ100万レコード未満のテーブルのチューニングで悩む方
ORM(Eloquent,ActiveRecord,TypeORM,...etc)を利用しているがSQLそのものはよくわかっていない方
クエリチューニングをしたいが具体的に何をするべきかわからない方

TL;DR

外部キーにはindexを貼りましょう(※MySQLの場合、ForeignKeyとして定義すれば暗黙的にindexが設定されます）
検索利用頻度が高いカラムにindexを貼りましょう
複数の検索条件を高頻度で使うなら複合インデックスが便利です
インデックスを貼ると参照(読取)以外の動作に影響があります(遅くなります)
全文検索は適度に検索サービス(ElasticSearchなど)を利用しましょう
チューニングが必要かどうかの判断にはSlowQueryLogが有効です
クエリの分析にはexplainを使いましょう

なぜ重いのか

DB(RDB)を非常にわかりやすいイメージに例えると辞書に例えられます。
今回は辞書=DB、語句(単語)=レコードと考えてみましょう。

辞書から目的の語句を探す際、ほとんどの方は索引(index)を利用すると思います。
逆に索引を一切活用せずに探す場合は1ページ目から順番に目的の語句を探す必要があるため、
途方もない時間がかかってしまいます。

DBも大まかな原理は似ていて、レコード(語句)にインデックスが設定されていない場合、
大きなテーブルであればあるほど検索に非常に時間がかかってしまいます。
今回はおおよそ100万レコード程度までのチューニングについてまとめます。(さらに大きなテーブルについては今回は扱いません)

実践

実践パートに当たり、以下のようなテーブルが定義してある前提で説明します。
Usersテーブル

id (unsignedInteger)	name (string)	email (string)
1	taro	taro@hoge.com
2	jiro	jiro@hoge.com

Postsテーブル

id (unsignedInteger)	contents (text)	user_id (unsignedInteger)
1	taro's first comment.	1
2	jiro is super tsuyotsuyo engineer.	2
3	taro's second comment.	1
4	taro's third comment	1

※各ユーザー(user)はコンテンツ(contents)を多数、投稿(post)することができるシステムのテーブルです。

1. 外部キーにindexを設定

基本のキ、その１外部キーindexの設定を行いましょう。
例えば特定のユーザーの投稿全てを取得したい場合

以下のクエリを発行します。

SELECT 
    users.id,
    users.name,
    users.email,
    posts.contents
FROM
    users
LEFT JOIN posts
    ON users.id = posts.user_id

indexが利用されるのはテーブル結合を行う以下の部分です。

LEFT JOIN posts
    ON users.id = posts.user_id

MySQLは結合用のキーとなるカラムをforeign_keyとして定義することで暗黙的にindexが付与されますが、
foreign_keyとして定義しなかった(ただのunsignedIntegerとして定義した)場合、indexが付与されないため注意しましょう。

indexを設定する(外部キーとして定義する)ことで外部テーブルとの主テーブルとの関連が瞬時に紐付き、
検索やテーブル結合の速度が大幅に向上します。

2. よく検索に使われるカラムにindexを設定

よく検索で利用するカラムはindexを設定しましょう。
ただし、唯一性が低いカラムにindexを設定してもあまり効果は期待できません。
例えば

会員番号
生徒番号
名前(完全一致)

などは唯一性が高くindexが有効に働きます。逆に

学年
性別
居住都道府県

などはindexを利用してもあまり効果が見込めません。
辞書で「アンデス山脈」を検索する場合、「あ行」のデータで絞り込むのと「アンデス」という単語で絞り込むのでは
絞り込んだ後探す範囲が全く異なります。もちろん「アンデス」で絞り込む方が圧倒的に速いですね。
データベースも仕組みとしてはほとんど同じであるため、名前や会員番号などより唯一性が高いカラムにindexを設定すると良いです。

*データの種類が多く、より少ない件数に絞り込めることをRDBの世界では「カーディナリティが高い」と表現します。

3.複合インデックスを使う

2.よく検索に使われるカラムにindexを設定と一部内容は重複しますが、
検索、ソート、結合で複数カラムを利用する場合は複合インデックスを設定すると良いです。

例えば、検索時必ず名前とメールアドレスを入力するシステムであれば
名前とメールアドレスの２つのカラムに対して複合インデックスを設定すると検索が高速化します。(名前は基本的にほとんど一意である(カーディナリティが高い)ため、複合インデックスを利用する必要がないとも言えます。)

※検索時のクエリの実行順と複合インデックスの設定が一致していないと最大限高速化されないので注意しましょう。
よりわかりやすくまとまっている記事があるためこちらも参照することを推奨します↓。
(https://nishinatoshiharu.com/overview-multicolumn-indexes/)

4.不要なインデックスは削除する

これまでの内容と真逆ですが、不要なインデックスや使用頻度の低いインデックスは削除した方が良いです。
インデックスは検索(参照)時にパフォーマンスを大幅に向上させる反面、
作成/更新/削除処理時にオーバーヘッド(インデックスの作成/更新/削除)が生まれるためパフォーマンスが低下するからです。

検索/ソート/結合に用いないカラム
一意制約の要らないカラム
カーディナリティが低いカラム
などはインデックスを削除することで作成/更新/削除のパフォーマンスが向上します。

5.全文検索は適度に検索サービス(ElasticSearchなど)を利用する

user.name LIKE %ta%のような文字列の全文検索を行う場合、通常のindexだと検索効率が悪く、
検索パフォーマンスが悪くなります。特にレコード数の多いテーブルで全文検索を行うと負荷が大きく、パフォーマンスの悪化が顕著に出てくるため、
ElasticSearchなどの検索サービスを併用すると良いです。
RDBのインデックスとは異なるロジックでデータを保存し、全文検索時も高速にデータを取得することができます。

なお、MySQLではFULLTEXTインデックスと言う全文検索向けのindexが用意されており、全文検索関数と合わせて利用することで
簡易的に全文検索に対応することができます。利用コストや機能を全文検索サービスと比較して適した方法でチューニングすると良いです。
MySQLの全文検索について(公式)

6.チューニングが必要かどうかの判断にはSlowQueryLogが有効

実際にシステムを利用したり、テストする中で具体的にいつどのクエリが遅く、チューニングが必要なのか迷うシーンがあります。
そのような場合、MySQLにはSlowQueryLogと呼ばれる機能があり、そちらを利用することでLogとして遅いクエリを洗い出すことができます。
具体的には、あらかじめ設定しておいた時間を超えるクエリの実行があった場合、そのクエリを丸ごとLogとして保存しておくことができます。
Logを見ることで、チューニングの要不要を検討することができます。
全体検索のような比較的時間のかかる検索要件があるシステムでなければ、0.1秒~1秒など(速度要件の指定がなければ)で指定してみて
その閾値を超えるクエリがログに記録された際にそのクエリをチューニングするといった使い方ができます。(検索対象のテストデータが潤沢に用意されていないとあまり意味がありません。)

SlowQueryLogについての詳細

7.explainを使ってクエリをチェック

最後にexplain句の紹介です。
これまで紹介した手法を使ってDBを設計しても、結局正しく設計できているのか、実際効率の良いクエリが発行されているのかといった疑問があると思います。SlowQueryLogを利用して遅いクエリを洗い出すのも一つの手段ですが、explain句を利用することで、実際に発行するクエリの

どのindexを利用するか
何行ほど読み出されるか(見積もり)
検索方法
etc...
といった情報を取得することができます。explainの結果を理解できるようになればほぼ迷うことなくチューニングができるようになります。
explainの詳しい情報は今記事で扱うと長くなってしまうので以下の参考文献を読むことをお勧めします。
https://dev.mysql.com/doc/refman/8.0/ja/explain.html

参考文献

一部転記
ヤフー社内でやってるMySQLチューニングセミナー大公開
 複合インデックスの指定順について
 MySQLの全文検索について
 SlowQueryLogについての詳細
 explainの詳細

筆者より

要所要所で言い切ってしまっている今記事ですが、厳密に言うとそうではない、といった部分も多くあります。(全文検索エンジンはコストがかかるかつ、全文検索によるパフォーマンスの悪化が許容できる場合は検索エンジンを導入しない方が良い。など)
設計領域は奥が深く、

コスト
可用性、拡張性
非機能要件(クエリパフォーマンスなど）
etc...
考慮しなければならない項目が非常に多いです。これらをあまりDBを使い慣れていないかたが一度に理解するのは難しいかつ、混乱を招くため
とりあえずこうすれば上手く動く論をまとめさせて頂きました。

まとめ

長々と文章ベースになってしまいましたが、チューニングの第一歩は

インデックス(複合インデックスなど含む)を適切に利用する
なんでもRDBにやらせようとしない(全文検索はElasticSearchを利用するなど、ニーズに適したサービスやツールを利用する)
SlowQueryLogやexplainで遅い/効率の悪いクエリを洗い出す
ことが大切です。これを徹底するだけで、中小規模のテーブルのパフォーマンスが問題になることはほぼ無いでしょう。
※求められる要件(レスポンス速度など)やテーブル規模、システムの拡張性などによってはさらに高レベルな対応が必要になることもあります。

さて、Sun* Advent Calendar 2022 次は船木大郎さんの記事です！
よろしければぜひご覧くださいね。