MySQL大量データ量ページングクエリ最適化方案

方法1:データベースが提供するSQL文を直接使用する
文のスタイル:MySQLでは、次の方法で使用できます.

SELECT * FROM     LIMIT M,N

シーンへの適応:データ量が少ない場合(タプル100/キロ)に適しています.
原因/欠点:全表スキャンでは、速度が遅く、あるデータベース結果セットの戻りが不安定(ある回は1,2,3、別の回は2,1,3)である.Limitは,結果セットのM位置からN本の出力を取り出し,残りは捨てることを制限する.
 
方法2:プライマリ・キーまたは一意のインデックスを作成し、インデックスを使用します(ページごとに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の後の列オブジェクトは、プライマリ・キーまたは一意であることが望ましいので、ORDERBY操作はインデックスで消去できるが、結果セットは安定している(安定した意味、方法1参照)
理由:インデックススキャンは、速度が速い.しかしMySQLのソート操作は、ASCだけがDESCを持っていない(DESCは偽物で、未来は本物のDESCを作ることができて、期待...).
 
方法4:インデックスベースprepareの使用
1番目の疑問符はpageNum、2番目?ページあたりのメタグループ数を示します
文のスタイル:MySQLでは、次の方法で使用できます.

PREPARE stmt_name FROM SELECT * FROM     WHERE id_pk > (？* ？) ORDER BY id_pk ASC LIMIT M

シーンに合わせる:ビッグデータ量
理由:インデックススキャンは、速度が速い.prepare文はまた一般的なクエリー文より少し速いです.
 
方法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
LIMIT $pagesize 

接続の使用例:

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ビッグデータ量はlimitでページングされ、ページ番号が大きくなるにつれてクエリー効率が低下します.
 
しけんしけん
1、limit start、countページング文を直接使うのも、私のプログラムで使う方法です.

select * from product limit start, count 

開始ページが小さい場合、クエリにパフォーマンスの問題はありません.1010010000010000からページングが開始される実行時間(各ページに20件)をそれぞれ見てみましょう.
次のようになります.

select * from product limit 10, 20   --0.016  
select * from product limit 100, 20  -- 0.016 
select * from product limit 1000, 20  -- 0.047 
select * from product limit 10000, 20   --0.094 

開始レコードが増加するにつれて時間も増大することが分かったが,これはページング文limitが開始ページ番号と大きく関係していることを示しているので,開始レコードを40 wに変更して見る(つまり記録の一般的な程度)

select * from product limit 400000, 20   --3.229  

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

select * from product limit 866613, 20   --37.44  

このようなページング最大のページ番号ページは明らかにこの時間に耐えられない.
そこから私たちも2つのことをまとめることができます.

limit文のクエリ時間は、開始レコードの位置に比例する

.

mysqlのlimit文は便利ですが、多くのテーブルを記録するのに直接使用するのは適切ではありません.

 
2、limitページング問題に対する性能最適化方法
表の上書きインデックスを使用してページング・クエリーを高速化
インデックス・クエリを使用した文にそのインデックス・カラム(インデックスの上書き)のみが含まれている場合、クエリが速くなることはよく知られています.
インデックス検索には最適化アルゴリズムがあり、データはクエリーインデックスの上にあるため、関連するデータアドレスを探す必要がなくなり、多くの時間を節約できます.またMysqlにも関連するインデックスキャッシュがあり、同時高のときにキャッシュを利用するとより効果的です.
私たちの例では、idフィールドがプライマリ・キーであることを知っており、デフォルトのプライマリ・キー・インデックスが自然に含まれています.インデックスを上書きするクエリの効果を見てみましょう.
今回、最後のページのデータをクエリーします(id列のみを含む上書きインデックスを使用します).

select id from product limit 866613, 20 0.2  

すべてのカラムをクエリーした37.44秒に比べて、約100倍の速度が向上しました.
では、すべてのカラムをクエリーするには、id>=の形式とjoinを利用して、実際の状況を見てみましょう.

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級の達人に適しています.一般的には1万編のニュースを作る小型システムはどのように書いてもいいですが、xxフレームワークで迅速な開発を実現することができます.
しかし、データ量は10万、百万から千万に達していますが、彼の性能はそんなに高いのでしょうか.ちょっとしたミスで、システム全体が書き換えられ、さらに本システムが正常に動作しない可能性があります.よし、そんなにくだらないことを言わないで.
 
事実で話して、例を見てください.
データテーブルcollect(id,title,info,vtype)は、titleが一定長、infoがtext、idが徐々に、vtypeがtinyint、vtypeがインデックスの4つのフィールドです.
これは基本的なニュースシステムの簡単なモデルです.今、中にデータを記入して、10万編のニュースを記入します.最後にcollectは10万件の記録で、データベーステーブルは1.6 Gを占めています.
OK、次のsql文を見てください.

select id,title from collect limit 1000,10;

すぐにほぼ0.01秒でOKなので、次の

select id,title from collect limit 90000,10;

9万件からページ分けして、結果?
8-9秒で完成、my godはどこで問題が発生しましたか?実はこのデータを最適化して、ネット上で答えを見つけます.次の文を見てください.

select id from collect order by id limit 90000,10;

すぐに、0.04秒でOKです.どうして?idプライマリキーでインデックスを作るのはもちろん速いです.
ネット上の改法は:

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

これがidをインデックスにした結果です.しかし、問題は少し複雑で、終わりました.次の文を見てください.

select id from collect where vtype=1 order by id limit 90000,10; 

遅くて、8-9秒かかりました!
ここまで来ると、多くの人が私と同じように、崩壊感を持っていると信じています.vtypeはインデックスを作ったでしょう?どうして遅いの?vtypeがインデックスを作ったのはいいですね.直接

select id from collect where vtype=1 limit 1000,10;

速いですが、基本的に0.05秒ですが、90倍アップして、9万から、それは0.05*90=4.5秒の速度です.とテスト結果は8~9秒で1桁になりました.
 
ここから分表の考え方が提案され、dis#cuzフォーラムと同じ考え方です.考え方は以下の通りである.
インデックステーブルを作成します:t(id,title,vtype)を長く設定して、それからページを分けて、ページを分けて結果を出してcollectの中でinfoを探しに行きます.可能ですか?実験でわかった.
10万本をt(id,title,vtype)に記録し,データテーブルサイズは20 M程度である.使用する

select id from t where vtype=1 order by id limit 90000,10;

もうすぐです.基本的に0.1-0.2秒で完走できます.どうしてこんなことになったの?
collectのデータが多すぎるので、ページを分けて長い道のりを走ると思います.limitはデータテーブルのサイズに完全に関係しています.実はこのようにするのはやはり全表スキャンで、ただデータ量が小さいため、10万しか速くありません.OK、クレイジーな実験をして、100万本を加えて、性能をテストします.10倍のデータを加えると、すぐにt表は200 M以上になり、しかも定長です.それともさっきのクエリー文で、時間は0.1-0.2秒で完成しました!メーターの性能は大丈夫ですか?
間違いだ!私たちのlimitはまだ9万なので、速いです.大きいのをあげて、90万から始めます

select id from t where vtype=1 order by id limit 900000,10;

結果を見ると、時間は1-2秒!why ?
時間がこんなに長くて、とても憂鬱です!定長でlimitの性能が上がると言われていましたが、最初は1本の記録の長さが固定されているのでmysqlは90万の位置を算出できるのではないかと思っていました.しかし、私たちはmysqlの知能を過小評価しました.彼はビジネスデータベースではありません.事実は定長と非定長がlimitにあまり影響しないことを証明していますか.道理でdiscuzが100万件に達すると記録が遅くなると言われていますが、これは本当だと信じています.これはデータベース設計と関係があります.
まさかMySQLは100万の制限を突破できませんか??100万のページングで本当に限界?
答えは:NOがなぜ100万を突破できないのかはmysqlを設計しないからだ.次は非分表法を紹介して、クレイジーなテストをします!1枚の表は100万記録を完成して、しかも10 Gデータベース、どのように迅速にページを分けます!
では、私たちのテストはcollect表に戻り、テストの結論を始めました.
30万のデータは、分表法で実行できます.30万を超えると、彼のスピードが遅くなります.あなたは我慢できません.もちろん、分表+私のような方法では、それは絶対に完璧です.しかし、私のこの方法を使ったら、時計を分けなくても完璧に解決できます!
答えは:複合インデックス!ある時mysqlインデックスを設計したとき、何気なくインデックスの名前を見つけることができて、いくつかのフィールドを選択して入ることができて、これは何の役に立ちますか?
スタート

select id from collect order by id limit 90000,10; 

こんなに早くインデックスを外したからだが、whereを付けたらインデックスを外さない.試してみるとsearch(vtype,id)というインデックスが付いています.
そしてテスト

select id from collect where vtype=1 limit 90000,10; 

とても速いです!0.04秒で完了!
再テスト:

select id ,title from collect where vtype=1 limit 90000,10; 

残念ながら、8-9秒、searchインデックスを歩かなかった!
再テスト:search(id,vtype)、やはりselect idという文も、残念ながら0.5秒です.
以上:where条件があり、limitを参照したい場合は、whereを1位に、limitが使用するプライマリ・キーを2位に、selectプライマリ・キーしか使用できないインデックスを設計する必要があります.
ページ分けの問題を完璧に解決しました.idをすばやく返すことができればlimitを最適化することが望ましい.このような論理では、百万級のlimitは0.0 x秒で終わるはずだ.mysql文の最適化とインデックス化は非常に重要なようです.