Mysql中怎么优化千万级快速分页

62次阅读

共计 3166 个字符，预计需要花费 8 分钟才能阅读完成。

本篇文章为大家展示了 Mysql 中怎么优化千万级快速分页，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

数据表 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 秒到了一个数量级。从这里开始有人提出了分表的思路，这个和 discuz 是一样的思路。思路如下：
建一个索引表：t (id,title,vtype) 并设置成定长，然后做分页，分页出结果再到 collect 里面去找 info。是否可行呢？实验下就知道了。
10 万条记录到 t(id,title,vtype) 里，数据表大小 20M 左右。用
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 的性能，开始我也以为，因为一条记录的长度是固定的，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 语句的优化和索引时非常重要的！
好了，回到原题，如何将上面的研究成功快速应用于开发呢？如果用复合查询，我的轻量级框架就没的用了。分页字符串还得自己写，那多麻烦？这里再看一个例子，思路就出来了：
select * from collect where id in (9000,12,50,7000); 竟然 0 秒就可以查完！
mygod，mysql 的索引竟然对于 in 语句同样有效！看来网上说 in 无法用索引是错误的！
有了这个结论，就可以很简单的应用于轻量级框架了：
代码如下：
$db=dblink();
$db- pagesize=20;
$sql= select id from collect 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(select id,title,url,sTime,gTime,vtype,tag from collect where id in ($strid)
? while($rs=$db- fetch_array()): ?
tr
td nbsp; ?php echo $rs[id ? /td
td nbsp; ?php echo $rs[url ? /td
td nbsp; ?php echo $rs[sTime ? /td
td nbsp; ?php echo $rs[gTime ? /td
td nbsp; ?php echo $rs[vtype ? /td
td nbsp; a href= ?act=show id= ?php echo $rs[id ? target= _blank ?php echo $rs[ title ? /a /td
td nbsp; ?php echo $rs[tag ? /td
/tr
?php endwhile; ?
/table
?php
echo $strpage;