最近，帮同事重写了一个mysql sql语句，该sql语句涉及两张表，其中一张表是字典表（需返回一个字段），另一张表是业务表（本身就有150个字段，需全部返回），当然，字段的个数是否合理在这里不予评价。平时，返回的数据大概5w左右，系统尚能收到数据。但12月31日那天，数据量大概20w，导致sql执行时间过长，未能在规定的时间内反馈结果，于是系统直接报错。
一般的思路是用MySQL的分页功能，即直接在原SQL语句后面增加LIMIT子句。但请注意，虽然你看到的反馈结果只是LIMIT后面指定的数量，于是想当然的以为MySQL只是检索了指定数量的数据，然后给予返回。其实，MySQL内部实现的原理是，检索所有符合where条件的记录，然后返回指定数量的记录。从这个角度来看，直接在原SQL语句后面添加LIMIT子句只能说是一种可以实现功能的方案，但未必最优。
具体在本例中，首先我们来看一下150个字段的表的统计信息：
一行大概就占2k，而Innodb默认页的大小为16k，这意味着，一个页中最多可存储8行的数据。随机读的可能性大大增加。而这无疑会对数据库系统的IO造成极大的压力。
优化前
如果采用上述方案，即直接在原SQL语句后面增加LIMIT子句，下面，我们来看看它的执行情况。
首先，直接添加LIMIT子句后的SQL语句如下（已省略a1表的150个字段和a2中的一个字段）：
代码如下:
FROM upay_csys_scquery_txn_log_his a1 LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) WHERE STATUS<>'00' AND settle_date=20151230 limit 50000,10000;
其执行时间如下：
大概执行了32s，绝大部分都花费到Sending data上了。Sending data指的是服务器检索数据，读取数据，并将数据返回给客户端的时间。
代码如下:
FROM upay_csys_scquery_txn_log_his a1 LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) where seq_id in (select seq_id from (select seq_id FROM upay_csys_scquery_txn_log_his a1 WHERE STATUS<>'00' AND settle_date=20151230 order by 1 limit 50000,10000) as t);
其执行时间如下：
大概3s多，比第一种方案快了差不多10倍，效果显著。
CRMEB单商户商城打通版基于Thinkphp6.0+vue+mysql+redis开发，前后台全部采用前后端分离式开发。前端框架为uni-app,多端合一，首页页面后台可视化编辑操作，后台采用iview框架。
下面来看看其执行计划（explain extended）
总结：
1. 改写后的语句原本如下：
代码如下:
FROM upay_csys_scquery_txn_log_his a1 LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) where seq_id in (select seq_id FROM upay_csys_scquery_txn_log_his a1 WHERE STATUS<>'00' AND settle_date=20151230 order by 1 limit 50000,10000);
但MySQL报以下错误：
代码如下:
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
需再增加一个嵌套子查询，
比如这样的语句是不能正确执行的。
代码如下:
select * from table where id in (select id from table limit 12);
但是，只要你再加一层就行。如：
代码如下:
select * from table where id in (select t.id from (select * from table limit 12)as t)
这样就可以绕开limit子查询的问题。
问题解决。
2. 如果想查看MySQL查询优化器等价改写后的SQL语句，可首先通过explain extended得到具体的执行计划，然后通过show warnings查看。
具体在本例中，等价改写后的SQL语句如下：
与设想中的执行顺序一致~
3. 如何查看MySQL语句各步骤的执行时间。
以上就是MySQL分页优化_MySQL的内容，更多相关内容请关注PHP中文网（www.php.cn）！
