最近,帮同事重写了一个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指的是服务器检索数据,读取数据,并将数据返回给客户端的时间。
关于上述执行结果,有以下几点需要说明:
1. 这是SQL语句多次执行后的结果,这样就可以排除结果缓存的影响,事实上,每次查询的时长都是32s左右。
2. 为什么选用的是limit 50000,10000,而不是0,10000,这个主要是考虑到对于LIMIT子句来说,越到后面,分页的成本越高。基于此,选择了中间值来作为分页的结果。
该语句的执行计划如下:
优化后:
优化的思路:
只对该表的主键进行分页,然后用返回的主键作为子查询的结果,来检索该表其它字段的值。
改写后的SQL语句如下:
复制代码 代码如下: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分页优化有所帮助。
《魔兽世界》大逃杀!60人新游玩模式《强袭风暴》3月21日上线
暴雪近日发布了《魔兽世界》10.2.6 更新内容,新游玩模式《强袭风暴》即将于3月21 日在亚服上线,届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。
艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕,并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时,他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中,玩家将会进入一个全新的海盗主题大逃杀式限时活动,其中包含极高的风险和史诗级的奖励。
《强袭风暴》不是普通的战场,作为一个独立于主游戏之外的活动,玩家可以用大逃杀的风格来体验《魔兽世界》,不分职业、不分装备(除了你在赛局中捡到的),光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式,玩家在加入海盗主题的预赛大厅区域前,可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹,《巨龙崛起》和《魔兽世界:巫妖王之怒 经典版》的玩家都可以获得奖励。
更新动态
- 群星.1999-上华世纪国语总冠军4CD【上华】【WAV+CUE】
- 叶丽仪.1992-仙鹤神针电视剧原声音乐专辑【星传真】【WAV+CUE】
- 陈柏宇.2011-QUINQUENNIUM【SONY】【WAV+CUE】
- 《51首热门当红歌曲 十大顶级发烧女声 3CD》[WAV+CUE][1.6GB]
- 《赵传 深受摇滚迷青睐的台湾音乐人 约定》[WAV+CUE][320MB]
- 《庄心妍 实力唱将 发烧音乐精选集》[WAV+CUE][380MB]
- [音展纪念碟]2006年《TAA台湾第十六届国际HI-ENDHI-FI音响展》2cd[WAV]
- 中国音乐地图之听见西藏山歌牧歌酒歌2020[WAV分轨]
- 王宏伟2017-永远的红飘带[太平洋影音][WAV+CUE]
- 费翔.1997-NOW现在【第一中国】【WAV+CUE】
- 新格合唱团.1981-我唱你和2辑(滚石新格民歌系列)【新格】【WAV+CUE】
- 陈柏宇.2011-QUINQUENNIUM【SONY】【WAV+CUE】
- 雨果唱片-《金唛系列-蔷薇依韵琼瑶》WAV
- 陈百强《凝望(2012华纳LPCD45限量版)[DMI]》[WAV整轨]
- 模拟之声慢刻CD《徐小凤45周年》[德国母带][低速原抓WAV+CUE]