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

データベース SQL の最適化は一般的な問題です。数百万のデータ量を含むページング クエリに直面した場合、最適化に関する適切な提案は何でしょうか?参照および学習のために、一般的に使用されるいくつかの方法を以下に示します。

方法 1: データベースによって提供される SQL ステートメントを直接使用する

• ステートメント スタイル: MySQL では、次のようになります。非常に遅く、一部のデータベース結果セットは不安定な結果を返します (たとえば、あるときは 1、2、3 を返し、別のときは 2、1、3 を返します)。制限は、M 位置からの N 出力を制限します。結果セットと残りは破棄します。
• 
  
方法 2: 主キーまたは一意のインデックスを作成し、そのインデックスを使用します (ページあたり 10 エントリを想定)

ステートメント スタイル: MySQL では、次のメソッドが使用できます: SELECT * FROM テーブル名 WHERE id_pk > (pageNum*10) LIMIT M

シナリオに適応: 大量のデータ (タプル数) 10,000)

• がある状況に適しています。理由: インデックスのスキャンが非常に高速になります。友人の提案: データ クエリが pk_id に従ってソートされていないため、データが存在します。欠落しているため、唯一の方法は方法 3
• 
  
方法 3: インデックスに基づいて並べ替える

#ステートメントスタイル: MySQL では、次のメソッドが利用可能です: SELECT * FROM table Name WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M

シナリオに適しています: 大量のデータがある状況に適しています ( ORDER BY の後の列オブジェクトが主キーであることが最善です。または、ORDERBY 操作で削除するインデックスを使用できるにもかかわらず、結果セットが安定している唯一の理由です (安定性の意味については、「安定性」を参照してください)。方法 1)

• 原因: インデックスのスキャンは非常に高速になりますが、MySQL の ASC のみのソート操作には DESC がありません (DESC は偽物です。本物の DESC は将来作成される予定です。楽しみにしています...)。
• 
  
方法 4: インデックスに基づいて prepare

を使用する

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

ステートメント スタイル: MySQL では、次のメソッドを使用できます: PREPARE stmt_name FROM SELECT * FROM table name 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 は大量のデータに対して制限ページングを使用するため、ページ数が増加するとクエリ効率が低下します。

テスト実験

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

select * from product limit start, count

開始ページが小さい場合、クエリのパフォーマンスに問題はありません。10、100、1000、10000 から始まるページングの実行時間を見てみましょう (ページあたり 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秒

開始レコードが増加するにつれて、時間も増加することがわかりました。これは、ページング ステートメントの制限が開始ページ番号と密接に関係していることを示しています。開始レコードを 40w に変更して (平均レコード) を見てみましょう

select * from product limit 400000, 20 3.229秒

レコードの最後のページを取得した時刻を見てみましょう

select * from product limit 866613, 20 37.44秒

この種のページング ページ どうやらこのような時間は耐えられないようです。

このことから、次の 2 つのことが結論付けられます。

limit ステートメントのクエリ時間は、開始レコードの位置に比例します。

Mysql の limit ステートメントは次のとおりです。非常に便利ですが、多数のレコードを含むテーブルに直接使用するのには適していません。

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

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

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

インデックス検索には最適化アルゴリズムがあり、データはクエリ インデックス上にあるため、関連するデータ アドレスを見つける必要がなく、時間を大幅に節約できます。また、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) 就这4个字段，其中 title 用定长，info 用text, id 是逐渐，vtype是tinyint，vtype是索引。这是一个基本的新闻系统的简单模型。现在往里面填充数据，填充10万篇新闻。最后collect 为 10万条记录，数据库表占用硬1.6G。

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秒到了一个数量级。

从这里开始有人提出了分表的思路，这个和dis #cuz 论坛是一样的思路。思路如下：

建一个索引表：t (id,title,vtype) 并设置成定长，然后做分页，分页出结果再到 collect 里面去找info 。是否可行呢？实验下就知道了。

10万条记录到 t(id,title,vtype) 里，数据表大小20M左右。用

select id from collect where vtype=1 limit 1000,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的性能，开始我也以为，因为一条记录的长度是固定的，mysql 应该可以算出90万的位置才对啊？可是我们高估了mysql 的智能，他不是商务数据库，事实证明定长和非定长对limit影响不大？怪不得有人说discuz到了100万条记录就会很慢，我相信这是真的，这个和数据库设计有关！

难道MySQL 无法突破100万的限制吗？？？到了100万的分页就真的到了极限？

答案是：NO 为什么突破不了100万是因为不会设计mysql造成的。下面介绍非分表法，来个疯狂的测试！一张表搞定100万记录，并且10G 数据库，如何快速分页！

好了，我们的测试又回到 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 放第一位，limit用到的主键放第2位，而且只能select 主键！

完美解决了分页问题了。可以快速返回id就有希望优化limit ， 按这样的逻辑，百万级的limit 应该在0.0x秒就可以分完。看来mysql 语句的优化和索引时非常重要的！

推奨事項: 「mysqlチュートリアル」

以上が数百万のデータに対する MySQL ページング クエリ手法とその最適化の提案の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。