開発を行う上でデータベース（DB）とSQLはとても身近なものです。
一方その仕組みは複雑で、様々な種類のDBがありますが似て非なる動作をするDBの仕様を混同している人は少なくなく、
SNSでSQLの間違った情報を広めて炎上するエンジニアなども定期的に出ます。
そこで今回はMySQL InnoDBについて、開発を行っている人が知っておくとよいことをまとめることにしました。
MySQL InnoDBを使った開発を行っている人が対象です。
できる限り公式のソースを併記するようにしています。
他のDBでも共通していることも多いですが、MySQL以外でどうなのかはあまり触れていないのでご留意ください。
デッドロック
デッドロックは複数のトランザクションが互いに他が必要なロックを保持しておりどのトランザクションも進行できなくなる状態です。
内容的には他の項目より難しい部類ですが、重要性が高いのでこちらを先に記載しています。
MySQL自体がデッドロックを検出できた場合は、一方のトランザクションをロールバックして他のトランザクションを通すようになっています。
また、SHOW ENGINE INNODB STATUSを実行することでその時点で最後に発生したデッドロックの詳細を表示できます。
実はMySQLのドキュメントとしてデッドロックを最小化および処理する方法が公開されています。
上記にはデッドロックが発生した際の対応についても記載がありますが、ここではデッドロックの発生を予防するために把握しておいた方がよいことをピックアップしました。
- トランザクションが競合する可能性を低くするために、トランザクションのサイズを小さく、期間を短く保ってください。
- トランザクションが競合する可能性を低くするために、関連する一連の変更を行なった直後にトランザクションをコミットしてください。 特に、コミットされていないトランザクションを含むインタラクティブな mysql セッションは、長時間開いたままにしないでください。
- locking reads (
SELECT ... FOR UPDATEまたはSELECT ... FOR SHARE) を使用する場合は、READ COMMITTEDなどのより低い分離レベルを使用してみてください。 - トランザクション内の複数のテーブルを変更する場合や、同じテーブル内のさまざまな行のセットを変更する場合は、毎回、これらの操作を一貫性のある順序で実行してください。 その結果、トランザクションで明示的に定義されたキューが生成され、デッドロックは発生しません。 たとえば、さまざまな場所で同様の
INSERT、UPDATE、およびDELETEステートメントのシーケンスを複数回コーディングするのではなく、データベース操作をアプリケーション内の関数に編成したり、ストアドルーチンを呼び出したりします。 - テーブルに適切なインデックスを追加してください。 これにより、クエリーでスキャンする必要のあるインデックスレコード数が減少するため、ロックの設定も減少します。 MySQL サーバーがクエリーに最適であるとみなすインデックスを特定するために、
EXPLAIN SELECTを使用してください。 - ロックの使用を減らしてください。
SELECTが古いスナップショットからデータを返すことを許可できる場合は、FOR UPDATEまたはFOR SHARE句を追加しないでください。 同じトランザクション内の各一貫性読み取りでは、独自の新しいスナップショットから読み取られるため、READ COMMITTED分離レベルを使用することが適切な方法です。
よくあるのが、大きい集計をまるまる1トランザクションでやってしまうパターンです。
1秒かからずに終わるならまだよいですが、大きいトランザクションは本当に分割できないか検討しましょう。
負荷とはトレードオフですが、無理やりバルクインサートしてデッドロックが発生して無限に待ったりエラーで再試行するくらいなら、1日ごと、1時間ごとなど集計単位を分割してトランザクションを小さくしてデッドロックを回避する方がトータルとしては安全で効率がよいことが多いです。
インデックス
INDEXはきちんとあたっていてきちんと絞り込めることが重要です。
無駄なインデックスを作らない
各クエリーで使用できるインデックスは 1 つだけであるため、カラムごとに個別のセカンダリインデックスを作成しないでください。
https://dev.mysql.com/doc/refman/8.0/ja/optimizing-innodb-queries.html
との記載がある通り、インデックスがある方がよいから、といって「とりあえず全てのカラムにインデックスを作成する」というのは悪手です。
インデックスとはその名の通り索引です。データが挿入や更新されるたびに索引を作るコストが発生しています。
きちんと計画を立てて必要なインデックスを作成しましょう。
複合INDEX（マルチカラムインデックス）は順番が重要
よく使う順に記載する
複合INDEXは前から順番に一部だけでも利用できます。そのため、多くのSQLをカバーできるようにINDEXの順番を決めましょう。col1,col2,col3という複合INDEXはcol1というINDEXとしても機能し、col1,col2という複合INDEXとしても機能します。（参考）
このとき、もちろんcol1のINDEXとしても機能するので別でcol1単体のINDEXは不要になります。もし別であるなら削除しましょう。
例えば、first_name, last_nameという複合INDEXがあれば、それはfirst_nameのINDEXとしても使えます。ただし、last_nameのINDEXとしては使えません。
フルネーム(first_nameとlast_nameの両方)かlast_name単体でしか検索せず、first_nameで検索することがないのであれば複合INDEXはlast_name, first_nameとすべきです。
なお、MySQL 8.0.13からはスキップスキャンという機能が実装されており、こちらを使うとcol2,col3というcol1を飛ばすようなパターンなどでも複合INDEXを利用できます。
範囲検索するカラムは後ろに記載する
複合INDEXにて、範囲指定されたカラムの後にあるカラムはINDEXで利用されません。
オプティマイザは、比較演算子が
https://dev.mysql.com/doc/refman/8.0/ja/range-optimization.html=、<=>、またはIS NULLである場合にかぎり、追加のキーパートを使用して、間隔を判断しようとします。 演算子が>、<、>=、<=、!=、<>、BETWEEN、またはLIKEの場合、オプティマイザはそれを使用しますが、追加のキーパートは考慮しません。
表現がわかりにくいですが、
column1, column2, column3という順の複合INDEXがあり、WHERE column1 = 'abc' AND column2 >= 100 AND column3 = 'def'という条件の場合、
column1は=で定数比較なので複合INDEXの次カラム(=column2)も参照されますが、column2が範囲指定なので複合INDEXの次カラム(=column3)は無視され、column1, column2までの複合INDEXとして利用されます。
column3はINDEXの時点では絞り込みされず、レコードを取得後に条件判定されます。
複合INDEXの簡易イメージとして（あくまでイメージで実構造は異なる）、指定した項目のみが記載されている小さい別テーブルができ、1つ目の順で並べられていて同順なら2つ目の順で並べられて...という状態で並んでいるのを想像してください。
ここから、一定の範囲のみを取ってくる形になります。
| key1 | key2 | key3 |
| NULL | 1 | 'abc' |
| NULL | 1 | 'xyz' |
| NULL | 2 | 'foo' |
| 1 | 1 | 'abc' |
| 1 | 1 | 'xyz' |
| 1 | 2 | 'abc' |
| 2 | 1 | 'aaa' |
カバリングインデックスにする
よくインデックスをはる、というのは主にセカンダリインデックスです。
セカンダリインデックスで絞り込まれた結果を持って、プライマリーキーを使って実際のレコードを引っ張ってくるという動作を行っています。
しかしながら、前述の簡易イメージのようにセカンダリインデックス自体が小さい表のようなものです。
取りたい値も全てインデックスの中に入っていれば、レコードへのアクセスなくセカンダリインデックスのみで完結するので高速です。
これをカバリングインデックスと呼びます。
抜けがちな点
utf8mb4を使おう
MySQLでのutf8はutf8mb3（3バイトの UTF-8 Unicode エンコーディング）のエイリアスです。
公式でも記載がある通り、utf8mb4を使いましょう。そうしなければ絵文字などが使えません。
よくある間違い
間違った情報を見かける例です。
かなり古いバージョンや、Oracleなど他のDBでは正しい場合もありますが、少なくとも最近のMySQLの仕様としては間違っているものを羅列します。
×: NULLはINDEXが使えない
10年以上前の記事でもMySQLはNULLでもINDEXを使います、という記事が出てくるくらいなのですが
今でも定期的に「MySQLはNULLだとINDEXを使えない」とか「NULLを入れずにありえない値を入れましょう」と発言して炎上している人達がでます。
MySQL は、インデックスと範囲を使用して、
https://dev.mysql.com/doc/refman/8.0/ja/is-null-optimization.htmlIS NULLを含むNULLを検索できます。
と記載の通り、NULLでもINDEXを使います。
そもそもNULLが入らないよう設計するならよいですが、設計上NULLなことがある値なのにDBにNULLを入れないために無理にありえない値や0といった嘘の値を入れるのはバグの元です。
NULLが適切な場合にはちゃんとNULLを入れるようにしましょう。
×: 複合インデックスを使うにはwhere句に書くカラムの順番を合わせないとダメ
公式でこれに関する具体的な記述を見つけられなかったので他の方のブログ記事ですが
複合インデックスを使うにはwhere句に書くカラムの順番を合わせないとダメとか無いですよねという話 の通りです。
そもそもwhere句の順番どおりにしか使えないならオプティマイザがINDEXを使い分けるということが発生しませんよね。
ただし、基本的には順番がめちゃくちゃになっているより合わせている方が見やすいので合わせている方がよいと思います。
△：複合INDEXはカーディナリティの高い順にする
バリエーションの多さを意味するカーディナリティは高い方がより絞り込めます。
そのためDBを扱う際の一般論として、複合INDEXはカーディナリティが高い順にする方がよいと言われます。
ただ、きちんと絞り込めるINDEXが作成できることの方が重要であり、複合INDEXにおいてカーディナリティの重要性はその次です。
特に前述の通り、範囲検索するカラムは後ろに記載したいので、単純にカーディナリティの高い日時や価格などを最初に持ってくると範囲検索する場合にはむしろ複合INDEXで適切に絞り込みきれずかなり遅くなるといったケースがあります。
カーディナリティの前に、きちんと絞り込めるINDEXになっているかをよく検討しましょう。