MySQL の数千万の高速ページングを最適化する方法

MySQL データベースの最適化処理により、数千万件の高速ページング分析が可能になります。見てみましょう。
データテーブルの収集 (id、title、info、vtype) にはこれら 4 つのフィールドがあり、タイトルは固定長、情報はテキスト、id は段階的、vtype は tinyint、vtype はインデックスです。これは、基本的なニュース システムの単純なモデルです。次に、データを入力し、100,000 件のニュース項目を入力します。
最終的な収集は 100,000 レコードで、データベース テーブルは 1.6G のハードディスクを占有します。 OK、次の SQL ステートメントを見てください:
select id,title fromcollectlim 1000,10; 非常に高速です。基本的には 0.01 秒で完了します。次の文を見てください。
select id,title fromcollectlimit 90000,10;ページネーションが始まります。結果は?
8 ～ 9 秒で完了します。一体何が問題なのでしょうか? ? ? ?実際、このデータを最適化したい場合は、オンラインで答えを見つけることができます。次のステートメントを見てください:
select id from Collect order by id limit 90000,10; これは非常に高速で、0.04 秒で OK です。 なぜ？インデックス作成に id 主キーを使用した方が当然高速だからです。オンライン変更は次のとおりです:
select id,title fromcollect where id>=(select id fromcollect order by id limit 90000,1) limit 10;
これは、インデックス作成に id を使用した結果です。しかし、問題が少しだけ複雑であれば、それで終わりです。次のステートメントを見てください
select id fromcollect where vtype=1 order by id limit 90000,10; 非常に遅く、8 ～ 9 秒かかりました。
ここに来て、多くの人が私が故障していると感じていると思います！ vtype はインデックス化されていますか?なぜこんなに遅いのでしょうか? vtype のインデックスを付けると、collect where vtype=1 limit 1000,10; から直接 ID を選択でき、基本的に 0.05 秒ですが、90,000 から 90 倍、つまり 0.05 まで増やすことができます。 90 = 4.5 秒の速度。そして、テスト結果は8〜9秒という桁違いです。ここから、discuz フォーラムと同じアイデアであるサブテーブルのアイデアを提案し始めた人がいます。アイデアは次のとおりです。
インデックス テーブルを構築します: t (id、title、vtype) を固定長に設定し、ページングを実行して結果をページアウトし、収集して情報を見つけます。 出来ますか？実験すればわかります。
100,000 レコードが t(id,title,vtype) に保存され、データテーブルのサイズは約 20M です。
select id from t where vtype=1 order by id limit 90000,10; を使用します。基本的には0.1～0.2秒で実行可能です。なぜそうなるのでしょうか?収集データが多すぎるため、ページングに時間がかかるためだと思います。制限はデータテーブルのサイズに完全に関係します。実際、これはデータ量が少ないというだけで完全テーブル スキャンであり、100,000 個の場合にのみ高速になります。 OK、クレイジーな実験をしてみましょう。これを 100 万に追加して、パフォーマンスをテストしてみましょう。
10倍のデータを追加すると、tテーブルはすぐに200Mを超え、固定長になりました。たった今のクエリ ステートメントが完了するまでに 0.1 ～ 0.2 秒かかります。サブテーブルの性能に問題はありませんか？間違っている！制限はまだ 90,000 なので、高速です。大きくして、900,000 から始めます
select id from t where vtype=1 order by id limit 900000,10; 結果を見てください。時間は 1 ～ 2 秒です。
なぜですか?? タイムスケジュールを分割しても、時間が長くてとてもイライラします。固定長にするとリミットのパフォーマンスが向上するという人もいますが、最初はレコードの長さが固定されているので、MySQL は 900,000 の位置を計算できるはずだと思いました。 しかし、私たちは MySQL のインテリジェンスを過大評価していました。MySQL はビジネス データベースではありません。固定長と非固定長が制限にほとんど影響を与えないことが証明されています。 discuz が 100 万レコードに達すると非常に遅くなるという人がいるのも不思議ではありません。これはデータベースの設計に関係していると思います。
MySQL は 100 万の制限を突破できないでしょうか? ? ?本当に100万ページになると限界なのでしょうか？ ? ?
答えは「いいえ」です!!!! 100 万を超えることができない理由は、mysql の設計方法を知らないからです。テーブル以外のメソッドを導入して、クレイジーなテストをしてみましょう! 1 つのテーブルで 100 万件のレコードと 10G のデータベースを処理できます。
さて、テストは収集テーブルに戻りました。結論は、データが 300,000 件であれば、分割テーブル方式を使用することが可能です。300,000 件を超えると、速度が耐えられなくなります。もちろんサブテーブル+私の方法を使えば完璧です。しかし、私の方法を使用した後は、テーブルを分割することなく完全に解決できました。
答えは「複合インデックス」です！ 以前、mysql インデックスを設計しているときに、インデックス名を任意に選択でき、入力するフィールドを複数選択できることを偶然発見しました。これは何に使うのでしょうか? ID 制限による収集順序からの最初の選択 ID はインデックスが使用されるため非常に高速ですが、where を追加するとインデックスは使用されません。試してみようと思い、search(vtype,id)のようなインデックスを追加しました。次に、
select id fromcollect where vtype=1 limit 90000,10; を非常に高速にテストします。 0.04秒で完了！
再度テスト: select id ,title fromcollect where vtype=1 limit 90000,10; 大変申し訳ありませんが、8 ～ 9 秒かかります。検索インデックスはありません。
再度テスト: search(id,vtype) または select id ステートメント。これも非常に残念ですが、0.5 秒です。
要約: where 条件があり、インデックス作成に limit を使用したい場合は、where を最初に置き、limit で使用される主キーを 2 番目に置くインデックスを設計する必要があります。選択できるのは主キーのみです。
ページングの問題を完全に解決しました。 ID をすぐに返すことができれば、このロジックに従って、100 万レベルの制限を 0.0x 秒で分割する必要があります。 mysql ステートメントの最適化とインデックス作成が非常に重要なようです。
さて、最初の質問に戻りますが、上記の研究を開発にうまくかつ迅速に適用するにはどうすればよいでしょうか?複合クエリを使用すると、軽量フレームワークは役に立たなくなります。ページング文字列を自分で書かなければならないのは面倒なことです。ここで別の例を示します。
select * fromcollect where id in (9000,12,50,7000); 0秒で確認できます。
mygod、mysqlのインデックスはin文にも有効です！インターネットではインデックスを作成できないという意見は間違っているようです。
この結論により、軽量フレームワークに簡単に適用できます:
コードは次のとおりです:
$db=dblink();
$db->pagesize=20;
$sql="select id fromcollect where vtype =$vtype";
$db->execute($sql);
$strpage=$db->strpage(); //簡単に出力できるようにページング文字列を一時変数に保存します
while($rs =$ db->fetch_array()){
$strid.=$rs['id'].',';
}
$strid=substr($strid,0,strlen($strid)-1); // ID 文字列を構築します
$db->pagesize=0; //データベース接続を一度使用するだけで済み、再度開く必要がないように、クラスをログアウトせずにページングをクリアすることが非常に重要です。
$db ->execute("($strid) の id を収集してから id,title,url,sTime,gTime,vtype,tag を選択");
fetch_array() ): ?>

 
 < ?php echo $rs['url'];?>
 
< td>  ;
 < /td>
    ;< /td>



echo $strpage;
単純な変換により、アイデアは非常に単純になります。 1) 最適化インデックスを通じて ID を見つけ、それを「123,90000,12000」のような文字列に綴ります。 2) 2 番目のクエリで結果が見つかります。
小さなインデックスと少しの変更により、mysql は数百万、さらには数千万の効率的なページングをサポートできるようになります。
ここでの例を通して、私は何かを考えました。大規模なシステムの場合、PHP はフレームワーク、特に SQL ステートメントさえ認識できないフレームワークを使用してはなりません。というのは、私の軽量フレームワークは最初は崩壊しそうになったからです。 ERP、OA、大規模な Web サイトの場合、ロジック層を含むデータ層はフレームワークを使用できません。プログラマが SQL ステートメントを制御できなくなると、プロジェクトのリスクが飛躍的に増大します。特に mysql を使用する場合、mysql が最高のパフォーマンスを達成するには専門の DBA が必要です。インデックスによって生じるパフォーマンスの違いは、何千倍にもなる場合があります。
追記：実際にテストした結果、100万データ、160万データ、15Gテーブル、1億9000万インデックスとなると、インデックスを使用したとしても0.49秒が限界です。したがって、ページング中にデータが 100,000 個になった後は、他の人にデータを見せないようにするのが最善です。そうしないと、非常に遅くなります。インデックスを使っても。このような最適化の後、MySQL は数百万ページの制限に達しました。しかし、このような結果はすでに非常に良好です。sqlserver を使用している場合、間違いなくスタックします。 (str) の id を使用した 160 万件のデータは非常に高速で、基本的にはまだ 0 秒です。そうであれば、mysql は数千万のデータを簡単に処理できるはずです。