1. インデックス
MySQLで利用可能なインデックスは一部を除き、B-Treeインデックスと呼ばれる構造でできてます。Bツリーインデックスは、ブランチノード(ブランチ), リーフノード(リーフ)で構成されており、ルートノードから数ステップで目的のデータに到達することができます。
データの格納とアクセス方法
クラスタインデックス
MySQLのInnoDBストレージエンジンでは、すべてのテーブルは主キー（PRIMARY KEY）によるクラスタインデックスを持ちます。クラスタインデックスとは、データ行が主キーの値によって物理的に順序付けられた状態のことを指します。このため、主キーによる検索は非常に高速です。
セカンダリインデックス
セカンダリインデックスは、クラスタインデックス以外のすべてのインデックスを指します。セカンダリインデックスのリーフページ（最下層のインデックスページ）には、インデックスキーと、それが指す主キーの値が格納されます。このため、セカンダリインデックスを使ったクエリでは、まずセカンダリインデックスを使って主キーの値を見つけ、その後クラスタインデックスを使って実際のデータ行を取得します。
SELECT date FROM SAMPLE_TABLE WHERE name = '袋';
SELECT * FROM SAMPLE_TABLE WHERE id = 8;
SELECT id, name FROM SAMPLE_TABLE WHERE date = '2018/5/29';
カバリングインデックス
カバリングインデックスは、クエリに必要なすべてのデータがインデックス自体に含まれている場合のインデックスを指します。カバリングインデックスが使用されると、データ行を参照するためのインデックスダイブを行う必要がなくなり、パフォーマンスが向上します。インデックスのサイズは通常、テーブルデータよりも小さいため、カバリングインデックスのスキャンは全テーブルスキャンよりも高速です。
SELECT id, name, qty FROM SAMPLE_TABLE WHERE name = '袋';
SELECT name FROM SAMPLE_TABLE WHERE qty = 6;
SELECT id, qty FROM SAMPLE_TABLE WHERE id = 8;
引用元
上記画像はこちらのサイトから引用させていただきました。めちゃくちゃ分かりやすいのでオススメです。
インデックスの種類
MySQLにはいくつかの種類のインデックスを利用することができます。状況に応じて選択することでパフォーマンスを最適化することができます。以下に主要なものをまとめます。詳しくは公式ドキュメントを参照ください。
プライマリー・インデックス
各テーブルには1つの主キーインデックスしか持つことができません。これは一意で、NULLを含むことができません。InnoDBストレージエンジンでは、主キーはクラスタ化インデックスを形成し、行データがこのインデックス順に物理的に格納されます。
CREATE TABLE table_name (
column_name1 data_type PRIMARY KEY,
...
);
ユニーク・インデックス
一意インデックスは、すべての値が一意であることを保証します。主キーインデックスとは異なり、一意インデックスはNULL値を許容します（ただし、一意性制約はNULL値には適用されません）。
ALTER TABLE table_name
ADD UNIQUE (column_name);
マルチカラム・インデックス
マルチカラムインデックス（Composite）は、複数の列に対して一つのインデックスを作成します。特定のクエリで一緒に検索・フィルタリングされる列に対して最適化されており、複雑なクエリのパフォーマンス改善を期待できます。
CREATE INDEX index_name
ON table_name (column_name1, column_name2);
インデックスが正しく機能しないケース
インデックスは正しく設定することでパフォーマンスを改善することができますが、一方で闇雲に設定しても効果がありません。ここでは、インデックスが正しく機能しないケースをいくつか紹介してみます。
前方一致でないLIKE検索
MySQLのインデックスは、ワイルドカードが文字列の先頭にあるとき（'%abc'のように）のLIKE検索には効果がありません。
SELECT * FROM employees
WHERE name LIKE '%Smith';
マルチカラムインデックスの各列をOR条件で利用する場合
マルチカラムインデックスは、そのインデックスが定義されている列の順序に基づいて効果を発揮します。一般的には、最初の列がOR条件で使用されていない場合、インデックスは効果がない可能性があります。
SELECT * FROM employees
WHERE last_name = 'Smith' OR first_name = 'John';
検索行数がテーブルの全行に近いほど多くなるケース
全行スキャンが起こると、インデックスの利点は減少します。MySQLは一部のクエリでインデックスを使用しないと判断することがあり、その場合は全テーブルスキャンを行います。
SELECT * FROM employees
-- salary列に格納されている値は大多数が1以上であるケース
WHERE salary > 1;
EXPLAIN句を使ったチューニング
MySQLのEXPLAIN句はSQLクエリの実行計画を表示する強力なツールで、パフォーマンスチューニングに不可欠です。これを用いると、クエリがどのように実行され、どのインデックスが使用されているかを把握できます。また、フルテーブルスキャンのような非効率的な操作が行われていないかも確認できます。
EXPLAIN SELECT * FROM table_name WHERE column = value
手前味噌で恐縮ですが、以下の記事でインデックスを最適化するための方法をハンズオンで解説しているので確認してみてください。
2. トランザクション
MySQLにおけるトランザクションは、データベース操作のまとまりであり、複数のクエリやステートメントを一つの処理単位としてまとめる機能です。
トランザクションは、データベースの一貫性と信頼性を確保するための重要な概念であり、複数の操作をまとめて制御することでデータの整合性を保つことができます。
一方で、トランザクション時間の拡大は、ロックの競合やデッドロックなどの問題が発生しやすくなり、パフォーマンスが低下する可能性があります。
発生する問題
トランザクションの分離レベルは、同時実行性とデータ一貫性の間のバランスを決定します。それぞれのレベルでの問題は、この2つの要素間の複雑なトレードオフから生じます。高い一貫性は同時実行性を制限し、高い同時実行性は一貫性を妨げる可能性があります。
ダーティーリード
あるトランザクションが更新されている最中に、他のトランザクションからデータを読み出すことができてしまう状況です。これは、READ UNCOMMITTEDの分離レベルでのみ発生します。以下の例では、トランザクションBの内容をCOMMITされる前に途中で読みこんでしまっています。結果的に、ロールバックされたにもかかわらずトランザクションA、誤ったデータを取得しています。
反復不能読み取り（ファジーリード）
これは、同一トランザクション内で同じデータを２回以上読み取ると、その間に他のトランザクションがデータを更新しコミットした結果、異なる値が返される現象を指します。これは、READ COMMITTEDの分離レベルで可能性があります。以下の例では、トランザクションAの中で２回に分けて商品Aの在庫数を取得していますが、結果が異なっています。
ファントムリード
同一トランザクション内で同じデータ範囲を二回以上クエリすると、その間に他のトランザクションが新たなデータを追加・コミットした結果、クエリ結果が異なる現象を指します。これは、一般的にREPEATABLE READの分離レベルで発生する可能性があります。ただし、MySQLのInnoDBでは、この分離レベルでも後述するネクストキーロックという機能を使うことで防止することができます。
ロストアップデート
ロストアップデートとは、複数のトランザクションが同時に同じデータを更新しようとする際に、最初の更新が上書きされてしまう現象です。以下の例では、トランザクションAが先行して在庫を消費したにも関わらず、トランザクションBはその変更を検知できずに、結果的に在庫数を正しく反映できていません。防止策としては、タイムスタンプやバージョンを格納するカラムを別個用意する楽観的ロックと、 共有ロック(LOCK IN SHARE MODE)や排他ロック(FOR UPDATE:)文などを用いて他のトランザクションからアクセスさせない悲観的ロックという方法があります。さらに、データベースの外部でロックを管理する方式、つまり、データベースのロック機能を使用せず、アプリケーション側でロックの状態を管理するアドバイザリーロックというものもあります。
分離レベル
トランザクション分離レベルは上記の問題をどれくらい許容することができるのかを踏まえて考慮する必要があります。また、一貫性とパフォーマンスはトレードオフの関係にある点も留意してください。
READUNCOMMITTED
全てのトランザクションレベルの中で一番パフォーマンスに優れます。ただし、このレベルでは、他のトランザクションがまだコミットしていないデータを読み取ることができます。これは「ダーティーリード」問題を引き起こします。この問題は、一つのトランザクションが別のトランザクションの未確定の変更を読み取ることを意味します。
READCOMMITTED
このレベルでは、他のトランザクションがコミットしたデータのみが読み取り可能です。ただし、同一トランザクション内での読み取り時に別のトランザクションによってデータが変更されると、反復不能読み取りが発生します。
REPEATABLEREAD
MySQLのInnoDBストレージエンジンのデフォルトのトランザクション分離レベルは、このレベルになります。トランザクションが開始された時点でのスナップショットが用いられ、そのトランザクション内での読み取りは一貫した結果を返します。一般的にこの分離レベルではファントムリードを発生させますが、MySQLのInnoDBでは、ネクストキーロックという機能を用いて防止することができています。
SERIALIZABLE
この最も高いレベルでは、全てのトランザクションが順序付けられ、実質的に一度に一つのトランザクションだけが実行されます。これにより、上記の全ての問題（ダーティーリード、反復不能読み取り、ファントムリード）が防止されますが、同時実行性は最も低くなりパフォーマンス上の問題を引き起こします。
| 分離レベル | ダーティーリード | 反復不能読み取り | ファントムリード |
|---|---|---|---|
| READUNCOMMITTED | ◯ | ◯ | ◯ |
| READCOMMITTED | - | ◯ | ◯ |
| REPEATABLEREAD | - | - | -※ |
| SERIALIZABLE | - | - | - |
◯: 発生する, -: 発生しない
※MySQLのInnoDBでは、REPEATABLEREADの場合でも、ネクストキーロックという機能を用いることでファントムリードを防止しています。
MVCC
MySQLのInnoDBストレージエンジンでは、多版同時実行制御（MVCC）が実装されています。MVCCは、同時に多数のトランザクションを効率的に処理するための手法で、各トランザクションが一貫したスナップショットを見ることを保証します。これにより、長時間ロックを持つことなくデータの読み書きが可能になります。また、MVCCはトランザクションの隔離性を提供することで反復不能読み取りを防止します。具体的には、DB_ROW_ID, DB_TRX_IDといったカラムを用いたUndoログレコードでトランザクションのやりとりを保存します。当該トランザクションによる更新前の情報を取得する場合は、このログレコードを参照することで一貫性を担保します。
3. ロック
MySQLにおけるロックは、同時実行される複数のトランザクション間のデータ整合性を保つための仕組みです。ロックは同じデータに対する競合を防ぎ、トランザクションの一貫性を確保します。
しかし、不適切なロックの使用はデッドロックと呼ばれる状態を引き起こす可能性があります。デッドロックは2つ以上のトランザクションが互いに必要とするロックを持ち合い、進行が停止する現象です。適切なロックの使用とデッドロックの回避策の考慮が重要です。
ロックの範囲
行ロック
行ロックは特定の行のみをロックする方式です。他のトランザクションは同一の行にアクセスすることができませんが、他の行へのアクセスは許可されます。
テーブルロック
テーブルロックは全ての行を含むテーブル全体をロックする方式です。他のトランザクションは全ての操作がブロックされます。インデックスを適切に設定されていない場合はテーブルロックが発生する可能性もあります。
InoDBにおけるロックの種類
レコードロック
レコードロックは、特定の一行に対するロックです。これは、その行が他のトランザクションによって変更や削除されるのを防ぐために行われます。以下のように２種類あります。
共有ロック
共有ロックとは、データベースの行に対して設定されるロックの一種です。共有ロックを持つトランザクションは、その行を読み取ることができますが、更新や削除はできません。また、その行に対して他のトランザクションも共有ロックを取得することができますが、排他ロックは取得できません。共有ロックは、SELECT文にFOR SHAREやLOCK IN SHARE MODEオプションを付けることで取得できます。
SELECT * FROM piyos WHERE id = 5 FOR SHARE;
-- この時点で、idが5の行はロックされ、他のトランザクションからは読取りのみ許可される。
排他ロック
排他ロックでは、ロックを保持するトランザクションによる行の読み取りは許可されますが、他のトランザクションによる行の読み取りは許可されません。つまり、排他ロックされた行は、そのロックをかけたトランザクション以外からは見えなくなります。データの整合性を保つために必要な場合がありますが、同時実行性を低下させる可能性があるため、必要最小限に使用することが推奨されます。共有ロックは、SELECT文にUPDATEオプションを付けることで取得できます。
SELECT * FROM piyos WHERE id = 1 FOR UPDATE;
-- この時点で、idが1の行はロックされ、他のトランザクションからの変更は待機状態になる。
ギャップロック
ギャップロックは、インデックスレコード間の「ギャップ」をロックするメカニズムであり、これにより他のトランザクションがギャップに新しいレコードを挿入するのを防ぎます。
SELECT * FROM table_name WHERE id > 1 AND id < 3 FOR UPDATE;
-- この時点で、idが1より大きく3より小さい範囲はロックされる。新しいレコードの挿入は待機状態になる。
ネクストキーロック
ネクストキーロックはレコードロックとギャップロックを組み合わせたものです。MySQLのInnoDBでは、「前方」および「後方」の双方のギャップにもロックを取得します。また、この機能を使うことでREPEATABLEREADでもファントムリードの問題を防止しています。
INSERT INTO table_name (id, value) VALUES (1, 100);
INSERT INTO table_name (id, value) VALUES (3, 200);
INSERT INTO table_name (id, value) VALUES (5, 500);
START TRANSACTION;
SELECT * FROM table_name WHERE id = 3 FOR UPDATE;
-- ここではidが3の行にネクストキーロックがかかります。
-- この結果、他のトランザクションがidが3の行を変更するのを防ぐとともに、
-- idが1と3, 3と5の間に新たな行が挿入されることを防ぎます。
COMMIT;
ロックにおけるインデックスの重要性
MySQLのInnoDBエンジンでは、ロックはインデックスを使用して取得されます。そのため、インデックスが設定されていない場合や、適切なインデックスが設定されていない場合には、意図した範囲よりも広範なロックが取得される可能性があります。
例えば、ある列に対して値を検索し、その結果に対してロックを取得しようとした場合、その列にインデックスが設定されていれば一致する行だけがロックされます。しかし、インデックスが設定されていなければ、テーブル全体がスキャンされ、テーブル全体にロックがかかる可能性があります。
また、MySQLのInnoDBエンジンでは、ギャップロックやネクストキーロックといったロックもインデックスを使用します。これらのロックは、行の間の「ギャップ」または「次のキー」に対するロックを提供し、トランザクションの際の一貫性を保つために重要な役割を果たします。
デッドロックが発生するタイミング
INSERT/UPDATEで発生
外部キーで発生するケース
デッドロック検出・調査方法
MySQLでは、デッドロックはInnoDBストレージエンジンによって自動的に検出されます。デッドロックが発生すると、InnoDBはデッドロックを解消するために一つのトランザクションを選び、そのトランザクションをロールバックします。その際には、以下のようなエラーが表示されます。
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
また、以下のようなコマンドを実行することで発生したデッドロックの状況を確認することができます。
mysql> SHOW ENGINE INNODB STATUS;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-11-25 15:58:22 139815661168384
*** (1) TRANSACTION:
TRANSACTION 43260, ACTIVE 186 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 19, OS thread handle 139815619204864, query id 143 localhost u2 updating
UPDATE Animals SET value=30 WHERE name='Aardvark'
...
デッドロックを防ぐ方法
ひとたびデッドロックが発生してしまうと、パフォーマンスの悪化やシステム障害に繋がります。影響度の大きいデッドロックを防止するためには、予めデッドロックを防止する設計が必要になります。
以下に主な防止策を上げてみます。
ロックの範囲を小さくする
ロックするデータの範囲が広いと、他のトランザクションがそのデータにアクセスできなくなり、競合や待機が発生しやすくなります。そのため、ロックするデータは必要最小限に絞り、インデックスや主キーなどを利用して効率的に検索できるようにすることが重要です。
ロックの取得時間を短くする
ロックを取得したまま長時間処理を行うと、他のトランザクションがそのロックを待たなければならず、パフォーマンスが低下します。また、ロックを取得したトランザクションが別のロックを要求すると、デッドロックの可能性が高まります。そのため、ロックは必要な時だけ取得し、早めに解放することが望ましいです。
同じ順序でロックを取得する
異なるトランザクションが異なる順序で複数のロックを取得しようとすると、デッドロックが発生する可能性があります。複数のロックを取得する場合は、常に同じ順序で取得することが必要です。
タイムアウトを設定する
トランザクションが一定時間内に必要なロックを取得できない場合、タイムアウトを設定して自動的にロールバックさせることで、デッドロックの発生を回避できます。