MySQL大数据量分页查询方法及其优化

这次我们之间查询最后一页的数据（操作包围索引，只包括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 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 语句的优化和索引时很是重要的！

原文链接：https://www.cnblogs.com/geningchao/p/6649907.html

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!