MySQL百万級データのページ別に最適化方案を照会する。

データベースからの検索が必要なテーブルが何万本も記録されている場合、一度にすべての結果を調べるのは遅くなります。特にデータ量の増加に伴って明らかになります。この場合は、改ページで照会する必要があります。データベースのページ別のクエリーにも、多くの方法と最適化のポイントがあります。私が知っている方法を簡単に説明します。
準備工作
下記に挙げた最適化をテストするために、既存の表について説明します。
表名：order_history
説明：ある業務の注文履歴表
メインフィールド：unsigned int id，tinyint(4)int type
フィールドの状況：この表は全部で37フィールドで、textなどの大型配列を含まず、最大はvarrhar（500）、idフィールドはインデックスで、しかもインクリメントです。
データ量：5709029
MySQLバージョン：5.7.16
ラインの下で百万級のテストシートを探すのは大変です。自分でテストする必要があれば、shellスクリプトなどの挿入データを書いてテストしてもいいです。
以下のsqlのすべての文の実行環境は変更されていません。以下は基本的なテスト結果です。select count(*) from orders_history;結果を返します。
三回の照会時間はそれぞれです。

8903 ms
8323 ms
8401 ms

普通は改ページで検索します
普通の改ページ検索は簡単なlimit子文を使って実現できます。limitサブステートメントは以下の通りです。SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offsetLIMIT子文は、SELECT文の戻りの記録数を指定するために使用されてもよい。以下の点に注意してください。
最初のパラメータは、最初のレコード行に戻るオフセット量を指定します。
2番目のパラメータ指定は、記録行に戻る最大数を指定します。
パラメータが一つしか与えられていない場合、最大のレコード行の数を返します。
二つ目のパラメータは-1です。あるオフセットからレコードセットの終了までのすべてのレコード行を検索します。
初期記録行のオフセットは0です。1ではなく、0です。
以下はアプリケーションの例です。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;三回の照会時間はそれぞれです。

3040 ms
3063 ms
3018 ms

このような照会方式に対して、以下で照会記録量が時間に与える影響をテストします。

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

三回の照会時間は以下の通りです。

  1   ：3072ms 3092ms 3002ms
  10   ：3081ms 3077ms 3032ms
  100   ：3118ms 3200ms 3128ms
  1000   ：3412ms 3468ms 3394ms
  10000   ：3749ms 3802ms 3696ms

また、十回以上の調査をしました。調査の時間から見て、ほぼ確定できます。調査記録量が100以下の時、調査時間にはほとんど差がありません。
クエリのオフセット量のテスト:

select * from orders_history where type=8 limit 100,100;
select * from orders_history where type=8 limit 1000,100;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 100000,100;
select * from orders_history where type=8 limit 1000000,100;

三回の照会時間は以下の通りです。

  100  ：25ms 24ms 24ms
  1000  ：78ms 76ms 77ms
  10000  ：3092ms 3212ms 3128ms
  100000  ：3878ms 3812ms 3798ms
  1000000  ：14608ms 14062ms 14700ms

照会オフセットが大きくなるにつれて、特に照会オフセットが10万以上になると、照会時間は急激に増加した。
このような改ページ照会方式はデータベースの第一条記録からスキャンを開始します。だから、後ほど、検索速度が遅くなります。また、調べたデータが多ければ多いほど、総クエリ速度が遅くなります。
サブクエリの最適化を使う
このように、オフセット位置のIDを先に位置付けし、後で照会すると、このような方式は、idが増加する場合に適用される。

select * from orders_history where type=8 limit 100000,1;

select id from orders_history where type=8 limit 100000,1;

select * from orders_history where type=8 and 
id>=(select id from orders_history where type=8 limit 100000,1) 
limit 100;

select * from orders_history where type=8 limit 100000,100;

4つの文の照会時間は以下の通りです。

 1   ：3674ms
 2   ：1315ms
 3   ：1327ms
 4   ：3710ms

上の照会に対して注意が必要です。
第1条の語句と第2条の語句を比較します。select*の代わりにselect idを使うと速度が3倍になります。
2番目の文と3番目の文を比較します。速度は数十ミリ秒違います。
第3条の語句と第4条の語句を比較する：select idの速度が増加したおかげで、第3条の語句の照会速度は3倍になりました。
この方式は元の一般的な照会方法に比べて、数倍のスピードで増加します。
ID限定最適化を使う
このように、データテーブルのIDが連続的に増加していると仮定すると、クエリのページ数とクエリの記録数からクエリーのIDの範囲を算出することができ、id between andを使用して照会することができます。

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

照会時間：15 ms 12 ms 9 ms
このような照会方式は、照会速度を大幅に最適化し、基本的に数十ミリ秒以内に完了することができる。制限は、IDを明確に知る場合にのみ使用できますが、一般的にテーブルを作る時は基本的なidフィールドが追加されます。
もう一つの書き方があります。select * from orders_history where id >= 1000001 limit 100;
もちろん、inの方式で照会することもできます。このような方法は、多くのテーブルが関連しているときにクエリーを行い、他のテーブルが照会しているidのセットを使ってクエリーを行います。

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

いくつかのmysqlバージョンは、n子文の中でlimitを使用することをサポートしていません。
臨時表を使って最適化する
この方式はもう照会最適化に属していません。ここに添付してください。
IDの限定最適化においては、IDは連続的に増加する必要があるが、履歴書を使用する場合や、データの欠落が発生した場合には、一時的に保存されたテーブルを用いて、改ページのidを記録し、改ページのidを用いてinクエリーを行う場合がある。これは大幅に伝統的なページ別の検索速度を向上させることができます。
データテーブルのidについて説明します。
一般的には、データベースにテーブルを作成する際に、各テーブルにIDフィールドを強制的に追加すると、検索が便利になります。
発注書などのデータ量が非常に大きい場合は、仕訳表を作成します。この場合は、唯一の識別としてデータベースのIDを使用することを勧めず、分布式の高併合固有のIDジェネレータを用いて生成し、データテーブルには別のフィールドを使用してこの一意の識別を保存する。
先に範囲を使って、位置付けID（またはインデックス）を調べてから、インデックスを使って位置指定データを作成すると、何倍もの速度で検索できます。つまり、先にselect idを作って、それからselect*;