数百万のデータボリュームに対する MySQL ページングクエリ方法とその最適化の提案

データベース SQL の最適化はよくある問題です。何百万ものデータ ボリュームに対してページング クエリを実行する場合、どのような最適化の提案が有効でしょうか。以下に、参考および学習用に、よく使用されるいくつかの方法を記載します。

方法1: データベースが提供するSQL文を直接使用する

• ステートメントスタイル: MySQLでは、次のメソッドを使用できます: SELECT * FROM テーブル名 LIMIT M,N
• 適用可能なシナリオ: 少量のデータ（数百または数千のタプル）の状況に適しています
• 理由/欠点: 完全なテーブルスキャンは非常に遅く、一部のデータベース結果セットは不安定です (たとえば、1、2、3 が一度に返され、2、1、3 が別の時間に返されます)。制限は、結果セットの M 位置から N 個の出力を取得し、残りを破棄することです。
  

方法 2: 主キーまたは一意のインデックスを作成し、そのインデックスを使用する (1 ページあたり 10 エントリと想定)

• ステートメントスタイル: MySQLでは、次のメソッドを使用できます: SELECT * FROM テーブル名 WHERE id_pk > (pageNum*10) LIMIT M
• 適用可能なシナリオ: 大量のデータ（数万タプル）を扱う状況に適しています
• 理由: インデックススキャンは非常に高速になります。一部の友人は、データが pk_id でソートされていないため、データが欠落するケースがあると指摘しました。方法 3 のみを使用できます。
  

方法3: インデックスに基づいて並べ替える

• ステートメントスタイル: MySQLでは、次のメソッドを使用できます: SELECT * FROM テーブル名 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
• 適用可能なシナリオ: 大量のデータ (数万のタプル) がある場合に適用できます。ORDER BY の後の列オブジェクトが主キーまたは一意である場合、インデックスを使用して ORDER BY 操作を省略できますが、結果セットは安定しています (安定性の意味については、方法 1 を参照してください)。
• 理由: インデックス スキャンは非常に高速です。ただし、MySQL のソートには ASC のみがあり、DESC はありません (DESC は偽物であり、将来的には本物の DESC になる予定です。楽しみです...)。
  

方法4: インデックスに基づいて準備する

最初の疑問符は pageNum を表し、2 番目は?ページあたりのタプル数を示します

• ステートメントのスタイル: MySQL では、次のメソッドを使用できます: PREPARE stmt_name FROM SELECT * FROM table name WHERE id_pk > (? * ?) ORDER BY id_pk ASC LIMIT M
• 適用シナリオ: 大量のデータ
• 理由: インデックス スキャンは非常に高速です。準備ステートメントは、一般的なクエリ ステートメントよりも高速です。
  

方法 5: MySQL を使用して ORDER 操作をサポートすると、インデックスを使用して一部のタプルをすばやく見つけ、テーブル全体のスキャンを回避できます。

たとえば、行 1000 から 1019 までのタプルを読み取ります (pk は主キー/一意キーです)。

SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

方法 6: サブクエリ/結合 + インデックスを使用してタプルをすばやく見つけ、タプルを読み取ります。

たとえば（idは主キー/一意キー、青いフォントは変数）

サブクエリを使用した例:

SELECT * FROM your_table WHERE id <=
(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc
$ページサイズを制限する

接続の使用例:

SELECT * FROM your_table AS t1
JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

MySQL は大量のデータに対して制限ページングを使用します。ページ数が増えると、クエリの効率が低下します。

テスト実験

1. limit start、count paging ステートメントを直接使用します。これは私のプログラムでも使用されている方法です。

製品制限開始、カウントから*を選択

開始ページが小さい場合、クエリのパフォーマンス上の問題はありません。ページングが 10、100、1000、10000 (1 ページあたり 20 レコード) から開始された場合の実行時間を見てみましょう。

次のように：

積限界から * を選択 10, 20 0.016 秒 積限界から * を選択 100, 20 0.016 秒 積限界から * を選択 1000, 20 0.047 秒 積限界から * を選択 10000, 20 0.094 秒

開始レコードの数が増えると、時間も長くなることがわかりました。これは、ページング ステートメントの制限が開始ページ番号と密接に関係していることを示しています。そこで、開始レコードを 40w (レコードの約半分) に変更してみましょう。

製品制限400000、20から*を選択 3.229秒

最後の記録ページを撮った時間を見てみましょう。

製品制限 866613 から * を選択、20 37.44 秒

このようにページ番号が最も大きいページの場合、この時間は明らかに耐えられないほど長いです。

このことから、次の 2 つのことが分かります。

1. 制限ステートメントのクエリ時間は開始レコードの位置に比例します。
2. MySQL の limit ステートメントは非常に便利ですが、レコード数が多いテーブルに直接使用するには適していません。
   

2. 制限ページング問題に対するパフォーマンス最適化手法

テーブルのカバーインデックスを使用してページングクエリを高速化する

インデックス クエリを使用するステートメントにそのインデックス列 (カバー インデックス) のみが含まれている場合、クエリが非常に高速になることは誰もが知っています。

インデックス検索には最適化されたアルゴリズムがあり、データはクエリ インデックス上にあるため、関連するデータ アドレスを探す必要がなくなり、時間を大幅に節約できます。また、MySQL には関連するインデックス キャッシュもあり、同時実行性が高い場合にキャッシュを使用すると効果が高まります。

この例では、id フィールドが主キーであることがわかっているため、デフォルトの主キー インデックスが含まれます。次に、カバーリング インデックスを活用したクエリがどのように実行されるかを見てみましょう。

今回は、次のように、最後のページのデータをクエリします (id 列のみを含むカバー インデックスを使用)。

製品制限 866613、20 から ID を選択 0.2 秒

すべての列をクエリするのにかかる 37.44 秒と比較すると、速度は 100 倍以上向上します。

すべての列をクエリする場合、2 つの方法があります。1 つは id>= 形式、もう 1 つは結合を使用する方法です。実際の状況を確認してください。

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

クエリ時間は0.2秒です！

別の書き方

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

クエリ時間も非常に短いです!

3. 複合インデックス最適化法

MySql のパフォーマンスはどの程度まで向上できるでしょうか? MySql データベースは、DBA レベルの専門家に最適です。一般的に、10,000 件のニュース記事の小さなシステムを任意の方法で作成し、xx フレームワークを使用して迅速な開発を実現できます。しかし、データ量が 10 万、100 万、1,000 万に達したとき、そのパフォーマンスは依然としてそれほど高いのでしょうか?ちょっとしたミスでシステム全体が書き換えられたり、システム全体が正常に動作しなくなったりする可能性があります。はい、もうナンセンスはやめましょう。

事実について話しましょう。例を見てみましょう。

データ テーブル collect (id、title、info、vtype) には、次の 4 つのフィールドのみが含まれます。title は固定長、info はテキスト、id は段階的、vtype は tinyint、vtype はインデックスです。これは基本的なニュース システムの単純なモデルです。今度はデータを入力して、100,000 件のニュース記事を入力します。最終的なコレクションには 100,000 件のレコードが含まれ、データベース テーブルは 1.6G のハード ディスク領域を占有します。

さて、次の SQL ステートメントを見てみましょう。

collect limit 1000,10 から id、title を選択します。

非常に高速です。基本的に0.01秒で大丈夫です。以下を参照してください。

collect limit 90000,10 から id、title を選択します。

90,000 件のレコードからページングを開始します。結果はどうでしょうか?

8〜9秒で完了しました。一体何が悪かったのでしょうか?実際、このデータを最適化したい場合は、オンラインで答えを見つけることができます。次の文を見てください。

ID 制限 90000,10 で collect order から ID を選択します。

非常に高速です。0.04秒でも大丈夫です。なぜ？ id主キーをインデックスとして使用するため、当然高速です。オンラインでの変更方法は次のとおりです。

collect から id、title を選択します。id>=(collect から id を選択します。order by id limit 90000,1) limit 10;

これは id をインデックスとして使用した結果です。しかし、問題がもう少し複雑になれば、それで終わりです。次の文を見てください

collect から id を選択し、vtype=1 で order by id limit 90000,10 を指定します。

とても遅くて、8〜9秒かかりました。

この時点で、多くの人が私と同じように感じ、圧倒されていると感じていると思います。 vtype はインデックス化されていますか?どうして遅いのでしょうか? vtype がインデックス化されているのは良いことです。

collect から id を選択します。vtype=1 制限 1000,10;

基本的には 0.05 秒と非常に高速ですが、90,000 から始めて 90 倍に増やすと、速度は 0.05 * 90 = 4.5 秒になります。そしてテスト結果は桁違いに8～9秒でした。

ここから、テーブルを分割するというアイデアを提案する人もいましたが、これは dis #cuz フォーラムと同じアイデアです。考え方は次のとおりです。

インデックス テーブル t (id、title、vtype) を作成し、それを固定長に設定して、ページングを実行し、結果をページ分割して、collect で情報を検索します。それは実現可能でしょうか?実験すれば分かります。

t(id, title, vtype)には100,000件のレコードが格納されており、データテーブルのサイズは約20Mです。使用

collect から id を選択します。vtype=1 制限 1000,10;

もうすぐです。基本的には0.1～0.2秒で完了します。なぜこのようなことが起こるのでしょうか?収集データが多すぎるため、ページングに時間がかかるのだと思います。制限はデータ テーブルのサイズに完全に関連しています。実際、これは完全なテーブルスキャンですが、データ量が 100,000 と少ないため高速です。さて、クレイジーな実験をしてみましょう。100 万件のレコードを追加してパフォーマンスをテストします。データを10倍追加すると、tテーブルはすぐに200Mを超え、固定長になりました。クエリ ステートメントは同じままで、完了するまでに 0.1 ～ 0.2 秒かかります。サブテーブルのパフォーマンスは大丈夫ですか？

間違っている！制限はまだ 90,000 なので、高速です。大きなものを贈ろう、900,000から

vtype=1 の t から id を選択し、id 制限 900000,10 で並べ替えます。

結果を見てください、時間は1〜2秒です！なぜ ？

テーブルを分割しても時間がかかり、非常に憂鬱です！固定長にすると limit のパフォーマンスが向上するという人もいます。私も最初は、レコードの長さが固定されているので、MySQL で 900,000 の位置を計算できるはずだと思っていました。しかし、私たちは MySQL の知能を過大評価していました。これはビジネス データベースではありません。事実は、固定長と非固定長が制限にほとんど影響を与えないことを証明しています。 discuz は 100 万件のレコードに達すると非常に遅くなると言う人がいるのも不思議ではありません。これは本当だと思います。これはデータベースの設計に関係しています。

MySQL が 100 万の制限を破ることができない可能性はありますか? ? ?本当に100万ページが限界なのでしょうか？

答えは「いいえ」です。100 万を超えることができない理由は、MySQL の設計方法を知らないからです。ここではテーブルを使わない方法で、クレイジーなテストをしてみましょう。 100 万件のレコードと 10G のデータベースを持つテーブルをすばやくページ分割する方法。

テストは collect テーブルに戻り、テストの結果は次のようになります。

30万件のデータであれば、テーブルパーティショニング方式でも可能ですが、30万件を超えると速度が遅くなり、耐えられなくなります。もちろん、分割テーブル＋私の方法を使えば完璧です。しかし、私の方法を使用した後、テーブルを分割しなくても問題を完全に解決できるようになりました。

答えは、複合インデックスです。以前、MySQL インデックスを設計していたとき、インデックス名を任意に選択でき、複数のフィールドを選択できることを偶然発見しました。これは何の役に立つのでしょうか?

始める

ID 制限 90000,10 で collect order から ID を選択します。

インデックスが使用されるため非常に高速ですが、where 句が追加されると、インデックスは使用されなくなります。試しに search(vtype,id) のようなインデックスを追加しました。

次にテスト

collect から id を選択します。vtype=1 制限 90000,10;

とても早いです！ 0.04秒で完了しました！

再テスト:

collect から id 、title を選択します。vtype=1 limit 90000,10;

8〜9秒かかり、検索インデックスが使用されなかったのは残念です。

再度テストします: search(id,vtype)、引き続き id を選択しますが、残念ながら 0.5 秒かかります。

要約すると、where 条件があり、インデックスを使用して limit を使用する場合は、インデックスを設計し、where を最初に配置し、limit に使用する主キーを 2 番目に配置し、主キーのみを選択する必要があります。

ページングの問題に対する完璧な解決策。 ID をすばやく返すことができれば、制限を最適化できる可能性があります。このロジックによれば、数百万の制限は 0.0x 秒で分割されるはずです。 MySQL ステートメントの最適化とインデックス作成は非常に重要であるようです。

上記は、数百万のデータボリュームに対する MySQL ページング クエリ メソッドの詳細と最適化の提案です。MySQL ページング クエリとその最適化の詳細については、123WORDPRESS.COM の他の関連記事をご覧ください。