MySQLの基本概念と最適化

内部結合と外部結合

完全結合：2つのテーブルを結合する際、両方のテーブルで完全に一致する結果セットのみを保持します
左結合：2つのテーブルを結合する際、左側のテーブルのすべての行を返します。右側のテーブルに一致するレコードがなくても同様です。
右結合：2つのテーブルを結合する際、右側のテーブルのすべての行を返します。左側のテーブルに一致するレコードがなくても同様です

正規化

あるクエリ文にWHERE、GROUP BY、HAVING、ORDER BY、JOINが同時に現れる場合の実行順序は以下の通りです

テーブルの取得 - フィールドの選択 - フィールドのグループ化 - フィルタリング - 絞り込み - ソート

FROM JOIN が最初に実行され、関連するテーブルが取得されます
WHERE句：まずWHERE句が実行され、条件を満たすレコードが抽出されます。
GROUP BY句：次にGROUP BY句で指定された列に従ってグループ化され、通常はSELECT句でグループをフィルタリングするためにCOUNT、MAX、SUMなどのグループ化集計関数が使用されます。
HAVING句：グループ化後、HAVING句が使用され、条件を満たすグループが保持されます。
SELECT句：次にSELECT句が実行され、表示する列が選択されます。
ORDER BY句：最後にORDER BY句で指定された列に従って結果がソートされます。 > HAVINGはGROUP BYの後にのみ使用できます

WHEREは必ずGROUP BYの前に実行されます
列名とキーワードが同じ場合、""で囲んで別名を示す必要があります

データベースアーキテクチャ

主にServer層とストレージエンジン層に分かれます

Server層：MySQLのほとんどのコアサービス機能はこの層に含まれます。クエリの解析、分析、最適化、キャッシュ、およびすべての組み込み関数（日付、時間、数学、暗号化関数など）が含まれます。ストレージエンジンを跨ぐ機能（ストアドプロシージャ、トリガー、ビューなど）もこの層で実装されます。
ストレージエンジン層：データの保存と取得を担当します。そのアーキテクチャはプラグイン方式で、InnoDB、MyISAM、Memoryなどの複数のストレージエンジンをサポートします。現在最も一般的に使用されているストレージエンジンはInnoDBで、MySQL 5.5.5バージョンからデフォルトのストレージエンジンとなっています。 > Server層はAPIを介してストレージエンジンと通信し、異なるストレージエンジン間の差異を隠蔽します。

SQLクエリ文がMySQLでどのように実行されるか

クエリキャッシュ：クライアントとの接続確立後、データベースキャッシュをクエリします。同じ文が以前に実行されていれば、結果を直接メモリから読み取ります（キャッシュの無効化は非常に頻繁に発生するため、MySQL 8.0以降では削除されました）
構文解析：キャッシュにヒットしない場合、SQL文を実行のために解析し、まずアナライザで構文解析と文法解析を行い、文が正しいかどうかを判断します。
最適化：正しいSQL文を最適化し、インデックスを使用するかどうか、テーブルのクエリ順序などを決定します
実行：最後に実行エンジンが文を実行し、結果を返します

MySQLのストレージエンジン

MySQLには主に2つのストレージエンジンがあります。MyISAMとInnoDBです。両者の違いは、MyISAMはテーブルロックのみをサポートし、インデックスは非クラスタ型のB木構造であるのに対し、InnoDBエンジンは行ロックをサポートし、インデックスはクラスタ型と非クラスタ型の両方を持ち、構造はB+木です

インデックス

インデックスの実現方法

MySQLのデフォルトストレージエンジンはInnoDBで、デフォルトのインデックス構造はB+木です。B+木では、リーフノードのみがデータを保存し、非リーフノードはキー値を保存し、インデックスの方向を指します。リーフノードの構造は双方向の順序付きデータリンクリストであるため、範囲検索が可能です。同時に、B+木の1つのノードは複数の子を保存できるため、B+木はより低く太くなり、ディスククエリ回数を減らすことができます。通常の二分木とは異なり、一度の走査ですべてのリーフデータを取得できるため、検索効率が向上します。

なぜB+木を使用するのか

B+木：リーフノードのみがデータを保存し、非リーフノードはキー値のみを保存し、インデックスの方向を指します。リーフノード間は双方向ポインタで接続されており、最下層のリーフノードは双方向の順序付きリンクリストを形成し、範囲検索が可能です。同時に、挿入と削除時にインデックスを維持するために、ページ分割とページマージなどの操作が必要です。

なぜ通常の二分木を使用しないのか？

通常の二分木は退化する可能性があり、リストになると全テーブルスキャンと同等になります。平衡二分木は二分探索木と比較して検索効率がより安定しており、全体の検索速度も速いです。

なぜ平衡二分木を使用しないのか？

平衡二分木の各ノードは1つのキー値とデータのみを保存しますが、B+木は複数のキー値を保存でき、木の高さも低いため、対応するディスク検索回数も少なくなります。

なぜB木ではなくB+木を使用するのか

B+Treeのディスク読み書き能力はB Treeと比較してより強力であり、IO回数が少なく、B+Treeは常にリーフノードでデータを取得するため、木全体を走査する必要がなく、すべてのデータを取得でき、IO回数が安定しています。リーフノードは順序通りにソートされているため、B+Treeのソート能力がより強力です。

InnoDBインデックス

InnoDBはB+木インデックスモデルを使用しているため、データはすべてB+木に保存されています。各インデックスはInnoDB内で対応するB+木に対応します。
インデックスタイプは主キーインデックス（クラスタインデックス）と非主キーインデックス（セカンダリインデックス）に分けられます。主キーインデックスのリーフノードには行全体のデータが保存されます。非主キーインデックスのリーフノードの内容は主キーの値です。

インデックスの分類

リーフの内容に基づいて、インデックスは主キーインデックスと非主キーインデックス（リーフノードに主キー情報のみを保存）に分けられます

主キーインデックス: InnoDBの主キーはデフォルトのインデックスで、データ列は重複を許可せず、NULLを許可しません。1つのテーブルに1つの主キーしか作成できません。主キーインデックスのリーフノードには行全体のデータが保存され、InnoDBでは主キーインデックスはクラスタインデックスと呼ばれます
一意インデックス: データ列は重複を許可せず、NULL値を許可します。1つのテーブルに複数の列に一意インデックスを作成できます。
普通インデックス: 基本的なインデックスタイプで、一意性の制限はなく、NULL値を許可します。
複合インデックス：複数の列値で1つのインデックスを構成し、組み合わせ検索に使用され、インデックスマージより効率的です > 非主キーインデックスに基づくクエリは、追加のインデックステーブルのスキャンが必要です

主キーインデックスと非主キーインデックスの違い

SELECT * FROM T WHERE k BETWEEN 3 AND 5を実行した場合、何回の木の検索操作が必要で、何行がスキャンされますか？
ここでkは非クラスタインデックスです

クエリプロセス
非クラスタインデックスで3を検索し、対応する主キーを取得し、クラスタインデックスで主キーに対応する行データを検索します（最初のテーブルルックアップ）
非クラスタインデックスで5を検索し、対応する主キーを取得し、クラスタインデックスで主キーに対応する行データを検索します（2回目のテーブルルックアップ）
kインデックステリーの次の値k=6を取得し、条件を満たさないため、ループが終了します（3回目のクエリ）。 > このクエリプロセスでは、kインデックステリーの3つのレコードを読み取り、2回テーブルルックアップしています。この例では、クエリ結果が必要なデータが主キーインデックスにしかないため、テーブルルックアップを避けることができません。

インデックスの最適化

SQL文をSELECT ID FROM T WHERE k BETWEEN 3 AND 5に変更すると、IDの値のみをクエリする必要があり、IDの値はすでにkインデックステリー上にあるため、直接クエリ結果を提供でき、テーブルルックアップは不要です。つまり、このクエリでは、インデックスkがクエリ要件を「カバー」しており、これをカバリングインデックスと呼び、テーブルルックアップを回避します。
カバリングインデックスは木の検索回数を減らすことができるため、クエリパフォーマンスを大幅に向上させます。したがって、カバリングインデックスを使用することは一般的なパフォーマンス最適化手段です。 > インデックスプッシュダウン最適化はテーブルルックアップ回数を減らし、クエリ効率を向上させます。

インデックスプッシュダウンのプッシュダウンとは、上位層（Server層）が担当する一部の処理を下位層（エンジン層）に任せることを意味します。インデックス化された列が条件として使用される場合、先に条件を満たすデータをフィルタリングし、テーブルルックアップ回数を減らします。InnoDBのインデックスデータとインデックスが一緒に存在するため、テーブルルックアップは存在せず、つまりセカンダリインデックスのみがインデックスプッシュダウンを使用できます

実行計画の確認方法

https://tobebetterjavaer.com/sidebar/sanfene/mysql.html#_26-実行計画の確認-explain-各フィールドの意味の理解方法
実行計画のkey列は使用されるインデックス名を表示します
Extraのusing indexはカバリングインデックスが使用されたことを示します

隔離レベル

ダーティリード：別のトランザクションの未コミットデータを読み取る
非反復読み取り：1つのトランザクション内で同じデータを複数回読み取る場合、前後で読み取ったデータが異なる場合
ファントムリード：トランザクションが2回クエリして条件に一致するレコード数が、前後で異なる場合

ロック

テーブルロック：オーバーヘッドが小さく、ロックが速い；デッドロックが発生しない；ロックの粒度が大きく、ロック競合の確率が最も高く、同時実行度が最も低い。
行ロック：オーバーヘッドが大きく、ロックが遅い；デッドロックが発生する；ロックの粒度が最も小さく、ロック競合の確率が最も低く、同時実行度も最も高い。
ページロック：オーバーヘッドとロック時間はテーブルロックと行ロックの間；デッドロックが発生する；ロックの粒度はテーブルロックと行ロックの間にあり、同時実行度は普通

InnoDBはデフォルトで行ロックをサポートし、MyISAMのテーブルロックと比較してより細かく、同時トランザクションの読み書きに対してパフォーマンスが高くなります。行ロックはギャップロック、ネキストキーロック、レコードロックに分けられます。

InnoDB行ロックの実装

行ロックは最も細かい粒度のロックであり、テーブルロックとページロックと比較して競合の可能性が最も低いです。クエリ文の限定に基づいて、行ロックはレコードロック、ギャップロック、ネキストキーロックによって実現されます。
例えば、一意インデックスを使用した等値検索では、SELECT * FROM user WHERE id=9の場合、InnoDBはレコードロックを使用してid=9のデータにロックをかけます。等値または範囲クエリでは、SELECT * FROM t WHERE id > 1 AND id < 6 FOR UPDATEは、idが1-6の範囲内のデータにギャップロックを使用します。SELECT * FROM t WHERE id > 1 AND id <= 6 FOR UPDATEは、クエリデータの範囲が1-6で境界を含む場合、つまり一部のレコード（13456）に一致すると、レコードロックを使用してロックをかけると同時に、1-5間のデータにギャップロックを使用し、他のトランザクションがギャップに新しい行を挿入するのを防ぎます。このレコードロックとギャップロックを組み合わせた方法はネキストキーロックと呼ばれます。
一意インデックスを使用してレコードに一致した場合、ネキストキーロックはレコードロックに退化します。

デッドロック

ファントムリードの解決方法

繰り返し読み取りの隔離レベルでは、ファントムリードの問題が存在します：
InnoDBエンジンは「繰り返し読み取り」の隔離レベルでのファントムリードの問題を解決するために、next-keyロックを導入しました。これはレコードロックとギャップロックの組み合わせです。
Record Lockはレコード自体にロックをかけます。
Gap Lockは2つの値間の空間にロックをかけ、他のトランザクションがこの空間に新しいデータを挿入するのを防ぎ、ファントムリード現象を回避します。

MVCCの実装原理

マルチバージョン同時制御は、データの歴史的バージョンを維持することで、データへの同時アクセスの一貫性と分離性の問題を解決します。
トランザクションがデータを変更する場合、MVCCはそのトランザクションのデータスナップショットを作成し、元のデータを直接変更しません

実装：
MVCCは楽観ロックの思想を採用しており、トランザクションが相互に干渉しないと仮定し、コミット時にデータの検査と更新を行います。
読み取り操作を行う場合：
トランザクションは条件に一致する操作行を検索し、そのトランザクションの開始時間に一致するデータバージョンを選択して読み取ります。複数のバージョンがある場合、現在のトランザクションのタイムスタンプとバージョン番号に基づいて、そのバージョンタイムスタンプ以前のトランザクションを選択してデータを読み取ります。これにより、他の同時トランザクションによるデータ行の変更は、現在のトランザクションの読み取り操作に影響しません。

書き込み操作を行う場合：
変更するデータの新しいバージョンを作成し、変更後のデータをそのバージョンに書き込みます。新しいバージョンには現在のデータのバージョン番号があり、他のトランザクションがデータを読み取る際に使用されます。これにより、書き込みトランザクションがコミットされる前に、他の読み取りトランザクションに影響を与えません。

テーブル間の関連付け方法

外部キー

遅いクエリの最適化

遅いクエリの確認：MySQLのスロークエリログを使用して問題の文を特定
最適化：

SQL文自体：
不要な列を使用していないか、クエリの範囲を指定しているか、
インデックスが無効になっていないか、クエリ実行計画を確認し、全テーブルスキャンが行われていないか、インデックスが正しく使用されているか、カバリングインデックスが使用可能か、複合インデックスが正常に使用されているかなどを確認します
テーブルが

インデックスが無効になるケース

クエリ条件にorが含まれている
likeワイルドカードが含まれているが、likeワイルドカードを使用すると必ずインデックスが無効になるわけではなく、likeクエリが%で始まる場合にのみインデックスが無効になります。
インデックス列でMySQLの組み込み関数を使用し、インデックス列に対して演算を行っている
インデックスフィールドで!=やを使用している
複合インデックスを正しく使用していない

MySQLのログ

binlog バイナリログデータベースのすべての変更操作を記録し、主にデータのバックアップと復元に使用されます
redo redo log 再実行ログトランザクションの永続性を保持し、データダウン後のデータ復旧に使用されます
undo undo log ロールバックログトランザクションの原子性を保証し、トランザクションのロールバックとMVCCに使用されます

redo logとundo logはMySQLのInnoDBエンジンに固有のものです
データベースがダウンした後、MySQLはredo logを使用してデータベースを復元し、データの永続性を保持します。データのコミット後の状態を記録します

undo log ロールバックログトランザクションのコミット前のデータ状態を記録します

トランザクションがコミットされる前に、データをundo logに記録します。トランザクションがロールバックされる場合、undo logに基づいてロールバックします。
MVCCはRead view + undo logを使用して実現されます

手動でのSQLデッドロックの作成、フィールドがインデックスされていない場合にデッドロックが発生するかどうかの判断

インデックスが使用されているかどうかの判断方法

実行計画explainを使用してクエリ文の実行計画を確認し、そのtypeフィールドでインデックスが使用されているかどうかを確認します
テーブルにインデックスがあるかどうかを確認するには、SHOW INDEX FROM tableコマンドを使用します

MySQLの質問

集約関数とHAVING、GROUP BYの使用シナリオ

集約関数はSQLでデータの統計とグループ計算に使用され、HAVING句は通常GROUP BY句と一緒に使用されます。HAVING句はグループ化された結果をフィルタリングし、特定の条件を満たすグループのみを返すことを許可します。

あるクエリ文にWHERE、GROUP BY、HAVING、ORDER BY、JOINが同時に現れる場合の実行順序は以下の通りです

テーブルの取得 - フィールドの選択 - フィールドのグループ化 - フィルタリング - 絞り込み - ソート

FROM JOIN が最初に実行され、関連するテーブルが取得されます
WHERE句：まずWHERE句が実行され、条件を満たすレコードが抽出されます。
GROUP BY句：次にGROUP BY句で指定された列に従ってグループ化され、通常はSELECT句でグループをフィルタリングするためにCOUNT、MAX、SUMなどのグループ化集計関数が使用されます。
HAVING句：グループ化後、HAVING句が使用され、条件を満たすグループが保持されます。
SELECT句：次にSELECT句が実行され、表示する列が選択されます。
ORDER BY句：最後にORDER BY句で指定された列に従って結果がソートされます。 > HAVINGはGROUP BYの後にのみ使用できます

WHEREは必ずGROUP BYの前に実行されます
列名とキーワードが同じ場合、""で囲んで別名を示す必要があります

SQL文の記述順序

SELECT 列1, 列2
FROM テーブル名
WHERE 条件
GROUP BY 列
HAVING 条件
ORDER BY 列
LIMIT 数値;

時間差の計算

TIMESTAMPDIFF(単位, 開始日, 終了日)  時間差を計算
単位：HOUR（時間）
DAY（日）
WEEK（週）
MONTH（月）

USINGは接続条件を指定するキーワードで、通常JOIN操作で使用され、接続条件は列名の等価一致に基づきます

JOIN テーブル2 USING (列名);

ON構文を使用する場合、接続条件は1つ以上の列間の論理式に基づき、比較演算子（等号、大記号など）を使用できます

JOIN テーブル2 ON テーブル1.列名 = テーブル2.列名;

year(列名)=時間 指定時間の年を抽出する列

group by句の後の列名は、グループ化に使用する列を指定します。同じ値を持つすべての行は同じグループに分けられます

avg(列)指定列の平均値を計算

IF(条件, 真の場合の値, 偽の場合の値)

ROUND(数値, 小数点以下の桁数)四捨五入

文字列の連結

CONCAT(文字列1, 文字列2, ...)ここで、文字列1、文字列2などは連結する文字列パラメータです。必要に応じて複数のパラメータを指定できます。

limit

limitとOFFSET OFFSET句を使用して返す結果の開始位置を指定します

count 重複を無視する場合、カンマで区切る必要はありません

SELECT COUNT(DISTINCT 列名)
FROM テーブル名;

case when式
CASE WHEN式はSELECTクエリ、WHERE条件、ORDER BY句などで柔軟に使用でき、特定の要件を満たすことができます。

SELECT 名前, 年齢,
    CASE
        WHEN 年齢 < 18 THEN '未成年'
        WHEN 年齢 >= 18 AND 年齢 < 60 THEN '成年'
        ELSE '高齢'
    END AS 年齢層
FROM ユーザー;

問題

主キーの設計

主キーはレコードの一意の識別子として、重複は許可せず、NULLも許可しません

データAが特定のトランザクションの排他ロック（Exclusive Lock）を保持している場合、他のトランザクションがデータAに共有ロック（Shared Lock）または排他ロックをかけると失敗します

タグ: MySQL データベースインデックストランザクションクエリ最適化

5月19日 20:15 投稿

異端開発室

MySQLの基本概念と最適化

内部結合と外部結合

正規化

あるクエリ文にWHERE、GROUP BY、HAVING、ORDER BY、JOINが同時に現れる場合の実行順序は以下の通りです

データベースアーキテクチャ

SQLクエリ文がMySQLでどのように実行されるか

MySQLのストレージエンジン

インデックス

インデックスの実現方法

なぜB+木を使用するのか

InnoDBインデックス

インデックスの分類

主キーインデックスと非主キーインデックスの違い

インデックスの最適化

実行計画の確認方法

隔離レベル

ロック

InnoDB行ロックの実装

デッドロック

ファントムリードの解決方法

MVCCの実装原理

テーブル間の関連付け方法

遅いクエリの最適化

インデックスが無効になるケース

MySQLのログ

手動でのSQLデッドロックの作成、フィールドがインデックスされていない場合にデッドロックが発生するかどうかの判断

インデックスが使用されているかどうかの判断方法

MySQLの質問

集約関数とHAVING、GROUP BYの使用シナリオ

あるクエリ文にWHERE、GROUP BY、HAVING、ORDER BY、JOINが同時に現れる場合の実行順序は以下の通りです

SQL文の記述順序

問題

主キーの設計

ホットタグ