MySQLMySQL的分页优化

明天中午,帮同事还写了一个MySQL
SQL语句,该SQL语句涉及个别布置表,其中同样摆设表是字典表(需再次回到一个字段),另一样张表是业务表(本身就是暴发150单字段,需整个回来),当然,字段的个数是否创建在此地不予评论。平时,重返的数码约5w左右,系统尚能收到数额。但12月31日这天,数据量大概20w,导致SQL执行时了长,未能在规定的时间外上报结果,于是系统从来报错。

一般的笔触是为此MySQL的分页效用,即直接当原SQL语句后扩展LIMIT子句子。但请留心,即使你看到的报告结果单是LIMIT前面指定的数码,于是想当的以为MySQL只是寻觅了指定数量之多寡,然后给重临。其实,MySQL内部贯彻之原理是,检索所有符合where条件的记录,然后回来指定数量之笔录。从夫角度来拘禁,直接以原SQL语句后添加LIMIT子句只好算得一种可以实现力量的方案,但不一定尽漂亮。

现实以本例中,首先我们来拘禁一下150独字段的发明的总括信息:

MySQL 1

一行大概就是占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;

该推行时如下:

MySQL 2

约莫执行了32s,绝大部分还花费到Sending data上了。Sending
data指的凡服务器检索数据,读取数据,并以数据重返给客户端的时。

 

至于上述执行结果,有以下几点需要注脚:

1.
立时是SQL语句多次执行后的结果,这样便可去掉结果缓存的震慑,事实上,每便查询的时长都是32s横。

  1. 何以采取的凡limit
    50000,10000,而非是0,10000,那一个重点是考虑到对LIMIT子句来说,越到末端,分页的血本更强。基于此,选用了中等价值来作为分页的结果。

该语句之履计划如下:

MySQL 3

 

优化后:

优化的思绪:

单独对该表的主键举行分页,然后用重返的主键作为子查询的结果,来寻觅该表此外字段的值。

改写后底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 4

约莫3s多,比第一栽方案快了大多10倍增,效果显然。

脚来探视该履行计划(explain extended)

MySQL 5

 

总结:

  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子查询的问题。 
问题解决。
  1. 要想翻MySQL查询优化器等价改写后底SQL语句,可首先通过explain
    extended拿到实际的执行计划,然后经过show warnings查看。

实际在本例中,等价改写后的SQL语句如下:

MySQL 6

暨设想中的尽顺序一致~

3.
怎么着查看MySQL语句各步骤的推行时,可参看:http://www.cnblogs.com/ivictor/p/5085965.html

 

网站地图xml地图