Mysql入门MySQL分页优化
|
《Mysql入门MySQL分页优化》要点： MYSQL教程最近,帮同事重写了一个MySQL SQL语句,该SQL语句涉及两张表,其中一张表是字典表(需返回一个字段),另一张表是业务表(本身就有150个字段,需全部返回),当然,字段的个数是否合理在这里不予评价.平时,返回的数据大概5w左右,系统尚能收到数据.但12月31日那天,数据量大概20w,导致SQL执行时间过长,未能在规定的时间内反馈结果,于是系统直接报错. MYSQL教程一般的思路是用MySQL的分页功能,即直接在原SQL语句后面增加LIMIT子句.但请注意,虽然你看到的反馈结果只是LIMIT后面指定的数量,于是想当然的以为MySQL只是检索了指定数量的数据,然后给予返回.其实,MySQL内部实现的原理是,检索所有符合where条件的记录,然后返回指定数量的记录.从这个角度来看,直接在原SQL语句后面添加LIMIT子句只能说是一种可以实现功能的方案,但未必最优. MYSQL教程具体在本例中,首先我们来看一下150个字段的表的统计信息： MYSQL教程 MYSQL教程一行大概就占2k,而Innodb默认页的大小为16k,这意味着,一个页中最多可存储8行的数据.随机读的可能性大大增加.而这无疑会对数据库系统的IO造成极大的压力.? MYSQL教程优化前 MYSQL教程如果采用上述方案,即直接在原SQL语句后面增加LIMIT子句,下面,我们来看看它的执行情况. MYSQL教程首先,直接添加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;其执行时间如下： MYSQL教程 MYSQL教程大概执行了32s,绝大部分都花费到Sending data上了.Sending data指的是服务器检索数据,读取数据,并将数据返回给客户端的时间. MYSQL教程关于上述执行结果,有以下几点需要说明： MYSQL教程1. 这是SQL语句多次执行后的结果,这样就可以排除结果缓存的影响,事实上,每次查询的时长都是32s左右. MYSQL教程2. 为什么选用的是limit 50000,10000,而不是0,这个主要是考虑到对于LIMIT子句来说,越到后面,分页的成本越高.基于此,选择了中间值来作为分页的结果. MYSQL教程该语句的执行计划如下： MYSQL教程 MYSQL教程优化后： MYSQL教程优化的思路： MYSQL教程只对该表的主键进行分页,然后用返回的主键作为子查询的结果,来检索该表其它字段的值. MYSQL教程改写后的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);其执行时间如下： MYSQL教程 MYSQL教程大概3s多,比第一种方案快了差不多10倍,效果显著. MYSQL教程下面来看看其执行计划(explain extended) MYSQL教程 MYSQL教程?总结： MYSQL教程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' 需再增加一个嵌套子查询, MYSQL教程比如这样的语句是不能正确执行的. MYSQL教程但是,只要你再加一层就行.如： MYSQL教程这样就可以绕开limit子查询的问题. MYSQL教程2. 如果想查看MySQL查询优化器等价改写后的SQL语句,可首先通过explain extended得到具体的执行计划,然后通过show warnings查看. MYSQL教程具体在本例中,等价改写后的SQL语句如下： MYSQL教程 MYSQL教程与设想中的执行顺序一致~ MYSQL教程3. 如何查看MySQL语句各步骤的执行时间. MYSQL教程以上就是本文的全部内容,希望对大家MySQL分页优化有所赞助. 《Mysql入门MySQL分页优化》是否对您有启发，欢迎查看更多与《Mysql入门MySQL分页优化》相关教程，学精学透。编程之家PHP学院为您提供精彩教程。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |