MySQL 最適化チュートリアル: 大規模なページングクエリ

背景

基本的にバックエンド開発をしていれば、ページングの需要や機能に触れることになります。基本的に誰もが MySQL の LIMIT を使用してこれを処理しており、現在私が担当しているプロジェクトもこの方法で記述されています。しかし、データ量が増えると、LIMIT の効率は極端に低下します。この記事では、LIMIT 句の最適化について説明します。

制限の最適化

多くのビジネスシナリオではページング機能が必要であり、これは基本的に LIMIT を使用して実装されます。

テーブルを作成し、200 万件のレコードを挿入します。

# 新しいt5テーブルを作成する CREATE TABLE `t5` (
 `id` int NOT NULL AUTO_INCREMENT、
 `name` varchar(50) NOT NULL,
 `text` varchar(100) NOT NULL,
 主キー (`id`)、
 キー `ix_name` (`name`),
 キー `ix_test` (`text`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

# 200万件のデータを挿入するストアドプロシージャを作成する CREATE PROCEDURE t5_insert_200w()
始める
 i INT を宣言します。
 i=1000000 を設定します。
 i<=3000000の場合
 t5(`name`,text) に挿入します。VALUES('god-jiang666',concat('text', i));
 i=i+1 を設定します。
 終了しながら;
終わり;

# ストアド プロシージャを呼び出して 200 万個のデータを挿入します call t5_insert_200w();

ページめくりが比較的少ない場合、LIMIT によってパフォーマンス上の問題は発生しません。

しかし、ユーザーが最後のページを見つける必要がある場合はどうすればよいでしょうか?

通常、次のページをクエリするために order by xxx desc などの逆 SQL を使用するのではなく、ページングクエリを実行するために順方向の順序を使用するため、すべてのページが正常にジャンプできることを確認する必要があります。

t5 から * を選択し、テキスト制限 100000、10 で並べ替えます。

このような SQL クエリページングを使用すると、200 万件のデータからこれらの 10 行のデータを抽出するコストが非常に高くなります。まず最初の 1,000,010 件のレコードをソートして見つけ、次に最初の 1,000,010 件のレコードを破棄する必要があります。私のMacBook Proの実行には5.578秒かかりました。

次に、上記の SQL ステートメントの実行プランを見てみましょう。

説明: select * from t5 order by text limit 1000000, 10;

実行プランから、大規模なページングの場合、テキストフィールドにインデックスを追加しても、MySQL はインデックススキャンを実行しないことがわかります。

これはなぜでしょうか?

MySQL インデックス (II) インデックスの設計方法に戻ると、MySQL データベースのクエリオプティマイザはコストベースのアプローチを採用しており、クエリコストの見積りはCPU コストとIO コストに基づいていることが述べられています。

MySQL は、クエリコストの見積もりにおいて、フルテーブルスキャンの方がインデックススキャンよりも効率的であると判断した場合、インデックスを放棄してフルテーブルスキャンを直接実行します。

このため、大きなページを含む SQL クエリでは、フィールドがインデックス化されていても、MySQL は完全なテーブルスキャンを実行します。

次に、上記のクエリ SQL を引き続き使用して、推測を検証します。

説明: select * from t5 order by text limit 7774, 10;

説明: select * from t5 order by text limit 7775, 10;

上記の実験はすべて私の MBP で実行されました。7774 の重要なポイントでは、MySQL はそれぞれインデックススキャンとフルテーブルスキャンのクエリ最適化方法を使用しました。

したがって、MySQL は独自のコストクエリオプティマイザに基づいてインデックスを使用するかどうかを決定すると想定できます。

MySQL のクエリオプティマイザのコアアルゴリズムに手動で介入することはできないため、最適化戦略では、ページングを最適なページングクリティカルポイントで維持する方法に重点を置く必要があります。

最適化方法

1. カバーインデックスを使用する

SQL ステートメントがクエリのためにテーブルに戻らずにインデックスを通じてクエリ結果を直接取得できる場合、このインデックスはカバーリングインデックスと呼ばれます。

実行プランを表示するには、MySQL データベースで explain キーワードを使用します。追加の列に「Using index」と表示される場合、この SQL ステートメントはカバーリングインデックスを使用していることを意味します。

カバーリングインデックスを使用することでパフォーマンスがどの程度向上するか比較してみましょう。

# カバーインデックスは使用されません select * from t5 order by text limit 1000000, 10;

このクエリには 3.690 秒かかりました。カバーリングインデックスの最適化を使用することでパフォーマンスがどの程度向上するかを見てみましょう。

# カバーリングインデックスを使用して、 id、`text` を t5 から order by text limit 1000000、10 で選択します。

上記の比較から、超大規模なページングクエリでは、カバーリングインデックスを使用した後は 0.201 秒かかりましたが、カバーリングインデックスを使用しない場合は 3.690 秒かかり、18 倍以上高速化されています。実際の開発では、これは大きなパフォーマンスの最適化です。 (このデータはMBPで実行して取得しました)

2. サブクエリの最適化

実際の開発では、1 つまたは 2 つの列を照会する SELECT 操作は非常にまれであるため、上記のカバーインデックスの適用範囲は比較的限られています。

したがって、ページング SQL ステートメントをサブクエリに書き換えることでパフォーマンスを向上させることができます。

t5 から * を選択します。id>=(t5 から id を選択、テキストで順序を指定、制限 1000000、1)、制限 10。

実際、この方法を使用することによって達成される効率改善は、上記のカバーリングインデックスを使用することによって達成される効率改善と基本的に同じです。

ただし、この最適化方法にも制限があります。

この書き込み方法では、主キーIDが連続している必要があります。
Where句では他の条件を追加することはできません

3. 遅延連合

上記のサブクエリのアプローチと同様に、JOIN を使用して最初にインデックス列のページング操作を完了し、次にテーブルに戻って必要な列を取得できます。

t5 から a.* を選択し、内部結合します (t5 から id を選択し、テキスト制限 1000000、10 で順序付けします)。b は a.id=b.id になります。

実験から、JOIN を使用して書き換えた後は、上記 2 つの制限が解除され、SQL 実行効率が失われていないことがわかります。

4. 最後のクエリが終了した場所を記録する

上記で使用した方法とは異なり、最後の終了位置を記録する最適化の考え方は、変数を使用して最後のデータの位置を記録し、次のページング中にこの変数の位置から直接スキャンを開始することです。これにより、MySQL が大量のデータをスキャンしてから破棄することを回避できます。

id>=1000000 の t5 から * を選択し、制限を 10 にします。

上記の実験に基づくと、ページング操作に主キーインデックスを使用しているため、SQL のパフォーマンスが最も高速であると結論付けるのは難しくありません。

要約する

大規模なページングクエリのパフォーマンスが低下する理由を紹介し、いくつかの最適化のアイデアを共有しました。
超大規模ページングの最適化の考え方は、完全なテーブルスキャンをトリガーせずに、ページング SQL を可能な限り最高のパフォーマンス範囲で実行することです。
上記の共有が、MySQL の道で迂回を避けるのに役立つことを願っています~~~

参考文献

「MySQL パフォーマンス最適化」の第 6 章 - クエリ最適化パフォーマンス
データベースクエリオプティマイザーの技術

これで、大規模ページングクエリに関する MySQL 最適化チュートリアルに関するこの記事は終了です。MySQL 大規模ページングクエリに関する関連コンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: