MySQL 百万レベルのデータページングクエリ最適化ソリューション

データベースからクエリする必要があるテーブルに数万件のレコードがある場合、すべての結果を一度にクエリすると、特にデータ量が増えるにつれて、非常に遅くなります。このとき、ページングクエリが必要です。データベース ページング クエリにも多くの方法と最適化ポイントがあります。私が知っている方法をいくつか挙げます。

準備

以下にリストされている最適化のいくつかをテストするために、既存のテーブルを以下に説明します。

テーブル名: order_history
説明: 特定のビジネスの注文履歴テーブル。主なフィールド: unsigned int id、tinyint(4) int type
フィールドの状況: テーブルには、テキストなどの大きな配列を除いて、合計 37 個のフィールドがあります。最大値は varchar(500) です。id フィールドはインデックスであり、増分されます。
データ量: 5709294
MySQL バージョン: 5.7.16
オフラインで何百万ものデータを含むテスト テーブルを見つけるのは簡単ではありません。自分でテストする必要がある場合は、テスト用のデータを挿入するシェル スクリプトを作成できます。
以下のすべての SQL ステートメントの実行環境は変更されていません。基本的なテスト結果は次のとおりです。

select count(*) from orders_history;

返される結果: 5709294

3 つのクエリ時間は次のとおりです。

8903ミリ秒
8323 ミリ秒
8401ミリ秒

一般的なページングクエリ

一般的なページング クエリは、単純な limit 句を使用して実装できます。制限句は次のように宣言されます。

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 句を使用すると、SELECT ステートメントによって返されるレコードの数を指定できます。以下の点に注意してください。

最初のパラメータは、返される最初のレコード行のオフセットを指定します。 2 番目のパラメータは、返されるレコード行の最大数を指定します。

パラメータが 1 つだけ指定されている場合: 返される行の最大数を示します。2 番目のパラメータは -1 で、特定のオフセットからレコード セットの最後までのすべての行を取得することを意味します。最初の行オフセットは 0 (1 ではありません) です。

アプリケーションの例を次に示します。

select * from orders_history where type=8 limit 1000,10;

このステートメントは、orders_history テーブルから 1000 番目のレコードの後の 10 レコード、つまり 1001 番目から 10010 番目のレコードを照会します。

デフォルトでは、データ テーブル内のレコードは主キー (通常は ID) でソートされます。上記の結果は次のようになります。

select * from orders_history where type=8 order by id limit 10000,10;

3 つのクエリ時間は次のとおりです。

3040ミリ秒
3063 ミリ秒
3018ミリ秒

このクエリ方法では、次のクエリ レコード ボリュームが時間に与える影響をテストします。

order_history から * を選択します。type=8 制限 10000,1;
order_history から * を選択します。type=8 制限 10000,10;
order_history から * を選択します。type=8 制限 10000,100;
order_history から * を選択します。type=8 制限 10000,1000;
order_history から * を選択します。type=8 制限 10000,10000;

3 つのクエリ時間は次のとおりです。

クエリ 1 レコード: 3072 ミリ秒 3092 ミリ秒 3002 ミリ秒
10 レコードをクエリ: 3081 ミリ秒 3077 ミリ秒 3032 ミリ秒
100 レコードのクエリ: 3118 ミリ秒 3200 ミリ秒 3128 ミリ秒
1000 レコードのクエリ: 3412 ミリ秒 3468 ミリ秒 3394 ミリ秒
10,000 レコードのクエリ: 3749 ミリ秒 3802 ミリ秒 3696 ミリ秒

さらに、10 回以上のクエリも実行しました。クエリ時間から判断すると、クエリレコード数が 100 未満の場合は、基本的にクエリ時間に違いがないことが確認できます。クエリレコードの数が増えると、費やされる時間も長くなります。

クエリオフセットのテスト:

order_history から * を選択します。type=8 制限 100,100;
order_history から * を選択します。type=8 制限 1000,100;
order_history から * を選択します。type=8 制限 10000,100;
order_history から * を選択します。type=8 制限 100000,100;
order_history から * を選択します。type=8 制限 1000000,100;

3 つのクエリ時間は次のとおりです。

クエリ 100 オフセット: 25ms 24ms 24ms
クエリ 1000 オフセット: 78ms 76ms 77ms
クエリ 10000 オフセット: 3092ms 3212ms 3128ms
クエリ 100000 オフセット: 3878ms 3812ms 3798ms
クエリ 1000000 オフセット: 14608ms 14062ms 14700ms

クエリ オフセットが増加すると、特にクエリ オフセットが 100,000 を超えると、クエリ時間が大幅に増加します。

このページング クエリ メソッドは、データベースの最初のレコードからスキャンを開始するため、時間をさかのぼるにつれてクエリ速度が遅くなります。さらに、クエリするデータが増えるほど、全体的なクエリ速度は遅くなります。

サブクエリの最適化の使用

このメソッドは、まずオフセット位置の ID を見つけてから、逆方向にクエリを実行します。このメソッドは、ID が段階的に増加する状況に適しています。

order_history から * を選択します。type=8 制限 100000,1;

orders_history から id を選択します。type=8 制限 100000,1;

order_historyから*を選択し、type=8で、 
id>=(orders_history から id を選択、type=8、limit 100000,1) 
制限 100;

order_history から * を選択します。type=8 制限 100000,100;

4 つのステートメントのクエリ時間は次のとおりです。

ステートメント 1: 3674ms
ステートメント2: 1315ms
ステートメント3: 1327ms
ステートメント4: 3710ms

上記のクエリに関する注意:

最初のステートメントと 2 番目のステートメントを比較してください。select * の代わりに select id を使用すると、速度が 3 倍向上します。2 番目のステートメントと 3 番目のステートメントを比較してください。速度の差は数十ミリ秒です。3 番目のステートメントと 4 番目のステートメントを比較してください。select id の速度向上により、3 番目のステートメントのクエリ速度が 3 倍向上します。この方法は、元の一般的なクエリ方法よりも数倍高速になります。

最適化を制限するためにIDを使用する

このメソッドは、データ テーブルの ID が継続的に増加していることを前提としています。次に、ページ数とクエリされたレコード数に基づいて、クエリされる ID の範囲を計算できます。クエリには、 と の間の ID を使用できます。

select * from orders_history where type=2 and id between 1000000 and 1000100 limit 100;

クエリ時間: 15ms 12ms 9ms

このクエリ方法はクエリ速度を大幅に最適化し、基本的に数十ミリ秒以内に完了できます。制限は、ID が明確にわかっている場合にのみ使用できることです。ただし、テーブルを作成すると、通常は基本的な ID フィールドが追加され、ページング クエリのトラバーサルが大量に発生します。

別の書き方もあります:

select * from orders_history where id >= 1000001 limit 100;


もちろん、複数のテーブルが関連付けられている場合に他のテーブル クエリの ID セットを使用してクエリを実行するためによく使用される in メソッドを使用してクエリを実行することもできます。

select * from orders_history where id in (select order_id from trade_2 where goods = 'pen') limit 100;

一部の MySQL バージョンでは、IN 句での limit の使用がサポートされていないことに注意してください。

一時テーブルを使用して最適化する

このメソッドはクエリの最適化には属さなくなりましたが、ここで簡単に説明します。

ID を使用して最適化を制限する問題については、ID を継続的に増加させる必要があります。ただし、履歴テーブルを使用する場合や、データ欠落の問題が発生する場合など、一部のシナリオでは、一時ストレージ テーブルを使用してページング ID を記録し、ページング ID を使用してクエリを実行することを検討できます。これにより、特にデータの量が数千万の場合、従来のページング クエリの速度が大幅に向上します。

データテーブルIDの説明

通常、データベースにテーブルを作成するときは、クエリを容易にするために、各テーブルに ID が増加するフィールドを追加することが必須です。

注文データベースなどのデータベース内のデータ量が非常に大きい場合は、通常、個別のデータベースとテーブルに分割されます。現時点では、データベース ID を一意の識別子として使用することは推奨されません。代わりに、分散型の高同時実行一意の ID ジェネレーターを使用して一意の ID を生成し、データ テーブル内の別のフィールドを使用してこの一意の識別子を保存する必要があります。

最初に範囲クエリを使用して ID (またはインデックス) を検索し、次にインデックスを使用してデータを検索すると、クエリ速度が数倍向上します。つまり、最初に id を選択し、次に * を選択します。