mysql分页查询慢优化

最新推荐文章于 2024-09-20 22:28:47 发布

辰歌淺落

最新推荐文章于 2024-09-20 22:28:47 发布

阅读量608

点赞数

分类专栏： mysql 文章标签： mysql sql 数据库

原文链接：https://www.cnblogs.com/scotth/p/7995856.html

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MySql分页sql语句，如果和MSSQL的TOP语法相比，那么MySQL的LIMIT语法要显得优雅了许多。使用它来分页是再自然不过的事情了。

最基本的分页方式：

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

在中小数据量的情况下，这样的SQL足够用了，唯一需要注意的问题就是确保使用了索引：
举例来说，如果实际SQL类似下面语句，那么在category_id, id两列上建立复合索引比较好：

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10

子查询的分页方式：

随着数据量的增加，页数会越来越多，查看后几页的SQL就可能类似：
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10

一言以蔽之，就是越往后分页，他需要过渡的数据越多，LIMIT语句的偏移量就会越大，速度也会明显变慢。

1、高性能MySQL一书中提到的只读索引方法

优化前SQL:

SELECT c1,c2,cn... FROM member ORDER BY last_active LIMIT 50,5

优化后SQL:

SELECT c1, c2, cn .. .
FROM member
INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5)
USING (member_id)

分别在于，优化前的SQL需要更多I/O浪费，因为先读索引，再读数据，然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了，然后通过member_id读取需要的列。

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

从这里开始有人提出了分表的思路，

建一个索引表： 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秒！

分表了时间还是这么长，非常之郁闷！有人说定长会提高limit的性能，开始我也以为，因为一条记录的长度是固定的，mysql 应该可以算出90万的位置才对啊

难道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 语句的优化和索引时非常重要的！

2、第一步用用程序读取出ID，然后再用IN方法读取所需记录

程序读ID：

SELECT id FROM table LIMIT 20000, 10;
SELECT c1, c2, cn .. . FROM table WHERE id IN (id1, id2, idn.. .)

3、尽量给出查询的大致范围

SELECT c1,c2,cn... FROM table WHERE id>=20000 LIMIT 10;

4、子查询法

SELECT c1,c2,cn... FROM table WHERE id>=
(
SELECT id FROM table LIMIT 20000,1
)
LIMIT 10;

SELECT
sysYw.ywtypename AS typename,
tstkkcjzhead.billno AS billno,
tstkkcjzhead.lrdate AS lrdate,
tstkkcjzhead.userid AS userid,
tstkkcjzhead.usercode AS usercode,
tstkkcjzhead.username AS username,
tstkkcjzhead.jzdate AS jzdate,
tstkkcjzhead.jzrid AS jzrid,
tstkkcjzhead.jzrcode AS jzrcode,
tstkkcjzhead.jzrname AS jzrname,
tstkkcjzhead.rptdate AS rptdate,
tstkkcjzhead.prntimes AS prntimes,
tstkkcjzhead.prndate AS prndate,
tstkkcjzhead.timemark AS timemark,
tstkkcjzhead.gentype AS gentype,
tstkkcjzhead.billtype AS billtype,
tstkkcjzhead.ywbillno AS ywbillno,
tstkkcjzhead.ywtype AS ywtype,
tstkkcjzhead.orgcode AS orgcode,
tstkkcjzhead.orgname AS orgname,
tstkkcjzhead.inorgcode AS inorgcode,
tstkkcjzhead.ckcode AS ckcode,
tstkkcjzhead.ckname AS ckname,
tstkkcjzhead.reccount AS reccount,
tstkkcjzhead.hcost AS hcost,
tstkkcjzhead.wcost AS wcost,
tstkkcjzhead.remark AS remark
FROM tstkkcjzhead tstkkcjzhead
LEFT JOIN tsysywtype sysYw ON sysYw.ywtype=tstkkcjzhead.ywtype
INNER join (SELECT a.billno FROM tstkkcjzhead a where a.orgcode in (select org_code from sys_org where node_code like '00010001%') ORDER BY a.lrdate desc LIMIT 50000,100)t using(billno)
ORDER BY tstkkcjzhead.lrdate desc

SELECT
sysYw.ywtypename AS typename,
tstkkcjzhead.billno AS billno,
tstkkcjzhead.lrdate AS lrdate,
tstkkcjzhead.userid AS userid,
tstkkcjzhead.usercode AS usercode,
tstkkcjzhead.username AS username,
tstkkcjzhead.jzdate AS jzdate,
tstkkcjzhead.jzrid AS jzrid,
tstkkcjzhead.jzrcode AS jzrcode,
tstkkcjzhead.jzrname AS jzrname,
tstkkcjzhead.rptdate AS rptdate,
tstkkcjzhead.prntimes AS prntimes,
tstkkcjzhead.prndate AS prndate,
tstkkcjzhead.timemark AS timemark,
tstkkcjzhead.gentype AS gentype,
tstkkcjzhead.billtype AS billtype,
tstkkcjzhead.ywbillno AS ywbillno,
tstkkcjzhead.ywtype AS ywtype,
tstkkcjzhead.orgcode AS orgcode,
tstkkcjzhead.orgname AS orgname,
tstkkcjzhead.inorgcode AS inorgcode,
tstkkcjzhead.ckcode AS ckcode,
tstkkcjzhead.ckname AS ckname,
tstkkcjzhead.reccount AS reccount,
tstkkcjzhead.hcost AS hcost,
tstkkcjzhead.wcost AS wcost,
tstkkcjzhead.remark AS remark
FROM tstkkcjzhead tstkkcjzhead
LEFT JOIN tsysywtype sysYw ON sysYw.ywtype=tstkkcjzhead.ywtype
where tstkkcjzhead.orgcode in (select org_code from sys_org where node_code like '00010001%') ORDER BY tstkkcjzhead.lrdate desc LIMIT 50000,100