MySQL inndbジョイントインデックスを正しく使用する方法を徹底的に理解するためのケーススタディ

最近確認された5件のデータを照会するビジネスがあります。

`id`、`title` を選択
`th_content` から
ここで、`audit_time` < 1541984478
 かつ `ステータス` = 'オンライン'
`audit_time` DESC、`id` DESC で並べ替え
制限5;

当時の監視状況を確認すると、CPU 使用率が 100% を超えていました。Show show processlist見ると、多くの類似クエリがcreate sort index状態にあることがわかりました。

テーブルの構造を表示する

テーブル `th_content` を作成します (
 `id` bigint(20) 符号なし NOT NULL AUTO_INCREMENT,
 `title` varchar(500) 文字セット utf8 NOT NULL デフォルト '' COMMENT 'コンテンツのタイトル',
 `content` mediumtext CHARACTER SET utf8 NOT NULL COMMENT 'メインコンテンツ',
 `audit_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '監査時間',
 `last_edit_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最終編集時間',
 `status` enum('CREATED','CHECKING','IGNORED','ONLINE','OFFLINE') 文字セット utf8 NOT NULL デフォルト 'CREATED' コメント '情報ステータス',
 主キー (`id`)、
 キー `idx_at_let` (`audit_time`,`last_edit_time`)
)ENGINE=InnoDB デフォルト文字セット=utf8mb4;

インデックスには、左側にaudit_timeとの結合インデックスがあり、 statusにはインデックスがありません。

上記の SQL 実行のロジックを分析します。

• 共同インデックスからレビュー時間未満のすべての主キー ID を検索します (このタイムスタンプより前に 100 万件のデータがレビューされている場合、対応する 100 万件のデータの主キー ID が共同インデックスから取得されます)
• 将来的に最適化が行われると良いでしょう。現在のところ、100 個の主キー ID をソートし、テーブルを返す操作の次のステップで、互いに近い主キーを 1 回のディスク I/O で取得できるようになっています。
• テーブルを1つずつ戻って、100万行を見つけ、ステータスが「ONLINE」の行をフィルター処理します。
• 最後に、クエリ結果を並べ替えます（500,000 行がすべてオンラインの場合は、これらの 500,000 行の並べ替えを続けます）。

最後に、データ量が大きいため、5 行のみが取得されますが、先ほど示した極端な例によれば、実際には 100 万行のデータが照会され、最終的にデータベースの 50 万行のメモリ ソートがメモリ内で実行されます。

したがって、非常に非効率的です。

最初のステップのクエリ プロセスを説明するために概略図が描かれています。ピンクの部分は、最終的にテーブルでクエリする必要があるデータ行を表します。

画像では、インデックス保存ルールに従って一部のデータを偽造して入力しました。何か間違っている点がありましたら、メッセージを残して指摘してください。この図を通して、共同インデックスストレージとインデックスクエリの方法を理解していただければ幸いです。

改善案1

範囲検索は、適切なインデックスでは使いにくいです。audit_time とstatusの結合インデックスを追加すると、どのような改善audit_time見られますか?

テーブル `th_content` を変更し、インデックス `idx_audit_status` (`audit_time`, `status`) を追加します。

mysql> explain select `id`, `title` from `th_content` where `audit_time` < 1541984478 and `status` = 'ONLINE' order by `audit_time` desc, `id` desc limit 5;
+----+--------------+------------+--------+------------------------------------------+------------------+--------+--------+------------+-------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+--------------+------------+--------+------------------------------------------+------------------+--------+--------+------------+-------------+
| 1 | SIMPLE | th_content | range | idx_at_ft_pt_let、idx_audit_status | idx_audit_status | 4 | NULL | 209754 | where の使用 |
+----+--------------+------------+--------+------------------------------------------+------------------+--------+--------+------------+-------------+

詳細: audit_timeは範囲検索なので、2 番目の列のインデックスは使用されません。audit_time のみ使用できるため、 audit_time key_len 4 になります。次のアイデア 2 では、これら 2 つのフィールドkey_len依然として 5 です。

インデックスを追加した後の実行プロセスを分析してみましょう。

• ジョイントインデックスから監査時間より短い最大のaudit_timeを持つ行のジョイントインデックスを見つけます。
• 次に、 < audit_timeは範囲検索であり、2 番目の列のインデックスの値が分散しているため、1 つずつ下方向に検索します。したがって、5 行目が取得されるまで、条件 ( status = 'ONLINE') を満たすインデックス行を 1 つずつ前方検索して一致させる必要があります。
• 必要な特定のデータを照会するにはテーブルに戻ります

上の図では、ピンク色は最初の列のインデックス要件を満たす行を示しています。1 つずつ前方にクエリを実行すると、このリーフ ノードに 3 つのレコードが見つかります。次に、前のリーフ ノードで左方向にクエリを続行する必要があります。レコードに一致する 5 行が見つかるまで、最終的にテーブルに戻ります。

改善点

status値はインデックス内にあるため、 status = 'ONLINE' を満たす行をフィルタリングするときに、クエリのためにテーブルに戻る必要はありません。テーブルに戻ると、 5 行のデータのみがクエリされるため、 iopsが大幅に削減されます。

この指標の欠点

idx_audit_statusでスキャンされた 5 行すべてstatusがONLINEの場合、スキャンする必要があるのは 5 行のみです。

idx_audit_statusでスキャンされた最初の 100 万行のうち、 statusがONLINEである行が 4 行のみの場合、必要な 5 行を取得するには 100 万 1 行をスキャンする必要があります。インデックスは不確定な数の行をスキャンする必要があります。

改善案2

テーブル `th_content` を変更し、インデックス `idx_audit_status` を削除します。
テーブル `th_content` を変更し、インデックス `idx_status_audit` (`status`, `audit_time`) を追加します。 

こうすることで、仕分けするときもテーブルに戻すときもプレッシャーがかかりません。

要約する

以上がこの記事の全内容です。この記事の内容が皆様の勉強や仕事に何らかの参考学習価値をもたらすことを願います。123WORDPRESS.COM をご愛顧いただき、誠にありがとうございます。これについてもっと知りたい場合は、次のリンクをご覧ください。