MySQL分页优化
最近,帮同事重写了一个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子句,狼蚁网站SEO优化,我们来看看它的执行情况。
,直接添加LIMIT子句后的SQL语句如下(已省略a1表的150个字段和a2中的一个字段)
其执行时间如下
大概执行了32s,绝大部分都花费到Sending data上了。Sending data指的是服务器检索数据,读取数据,并将数据返回给客户端的时间。
关于上述执行结果,有以下几点需要说明
1. 这是SQL语句多次执行后的结果,这样就可以排除结果缓存的影响,事实上,每次查询的时长都是32s左右。
2. 为什么选用的是limit 50000,10000,而不是0,10000,这个主要是考虑到对于LIMIT子句来说,越到后面,分页的成本越高。基于此,选择了中间值来作为分页的结果。
该语句的执行计划如下
优化后
优化的思路
只对该表的主键进行分页,然后用返回的主键作为子查询的结果,来检索该表其它字段的值。
改写后的SQL语句如下
其执行时间如下
大概3s多,比第一种方案快了差不多10倍,效果显著。
狼蚁网站SEO优化来看看其执行计划(explain extended)
1. 改写后的语句原本如下
但MySQL报以下错误
需再增加一个嵌套子查询,
比如这样的语句是不能正确执行的。
,只要你再加一层就行。如
这样就可以绕开limit子查询的问题。
问题解决。
2. 如果想查看MySQL查询优化器等价改写后的SQL语句,可通过explain extended得到具体的执行计划,然后通过show warnings查看。
具体在本例中,等价改写后的SQL语句如下
与设想中的执行顺序一致~
3. 如何查看MySQL语句各步骤的执行时间。
以上就是本文的全部内容,希望对大家MySQL分页优化有所帮助。
编程语言
- 如何快速学会编程 如何快速学会ug编程
- 免费学编程的app 推荐12个免费学编程的好网站
- 电脑怎么编程:电脑怎么编程网咯游戏菜单图标
- 如何写代码新手教学 如何写代码新手教学手机
- 基础编程入门教程视频 基础编程入门教程视频华
- 编程演示:编程演示浦丰投针过程
- 乐高编程加盟 乐高积木编程加盟
- 跟我学plc编程 plc编程自学入门视频教程
- ug编程成航林总 ug编程实战视频
- 孩子学编程的好处和坏处
- 初学者学编程该从哪里开始 新手学编程从哪里入
- 慢走丝编程 慢走丝编程难学吗
- 国内十强少儿编程机构 中国少儿编程机构十强有
- 成人计算机速成培训班 成人计算机速成培训班办
- 孩子学编程网上课程哪家好 儿童学编程比较好的
- 代码编程教学入门软件 代码编程教程