设为首页收藏本站
查看: 62|回复: 0

[PHP] MySQL分页优化_MySQL

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34270
主题
17032
UID
1347
M币
67
贡献
17171

  • 发表于 2017-5-12 16:12:00 | 显示全部楼层 |阅读模式
    最近,帮同事重写了一个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倍,效果显著。
    下面来看看其执行计划(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.php1.cn)!
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表