ITPub博客

首页 > 数据库 > MySQL > 一个慢SQL优化建议

一个慢SQL优化建议

原创 MySQL 作者:czxin788 时间:2019-05-14 20:34:24 0 删除 编辑

现象

目前数据库的磁盘io太高,究其原因是某条慢sql导致的。

观察最近一星期数据库的磁盘io,其iowait一直都维持在50%:

数据库中有大量的慢SQL,几乎都是和waybill表有关:

慢sql

原始慢SQL:


explain
SELECT
 MAIN_NO AS mainNo,
 LOGISTICSNO AS logisticsno,
 PUSH_ZY_STAT AS pushZyStat,
 PUSH_ZY_TIME AS pushZyTime
FROM
 waybill
WHERE
  1 = 1
  and DATE_FORMAT(CREATE_TIME, '%Y-%m-%d %H:%i:%S' ) <= '2019-05-13 13:55:00'
  and DATE_FORMAT(CREATE_TIME, '%Y-%m-%d %H:%i:%S' ) >= '2019-05-10 13:55:00'
 

分析:

通过观察执行计划,发现走的全表扫描,预估扫描行数6564043,数量太大,这样很耗费磁盘IO。

其实waybill.create_time字段上有索引,但上述sql不走索引,其原因就是在create_time字段上用了函数DATE_FORMA,所以就不会走索引。

请开发人员记住 一个原则:在等号左边的字段上用函数,永远不会走索引,请避开这个雷区!!! 你可以把把函数放在等号右边,这样还能走索引。

解决办法1:

将上述sql的函数去掉,改写为:



SELECT



 MAIN_NO AS mainNo,
 LOGISTICSNO AS logisticsno,
 PUSH_ZY_STAT AS pushZyStat,
 PUSH_ZY_TIME AS pushZyTime,
CREATE_TIME
FROM
 waybill
WHERE
  1 = 1
  and CREATE_TIME <= DATE_FORMAT( '2019-05-13 13:55:00' , '%Y-%m-%d %H:%i:%S' )
  and CREATE_TIME >=  DATE_FORMAT( '2019-05-10 13:55:00' , '%Y-%m-%d %H:%i:%S' )

再看其执行计划,发现走索引了。

优化前后对比

优化前 优化后
执行时间 1000s 0.4s

解决方法2:

上述改写方法在查找时间范围小的时候,是走索引的,但是如果时间范围很大,就不走索引了。

比如查找2019-03-10 13:55:00到2019-05-13 13:55:00的数据,就全表扫描了:



SELECT



 MAIN_NO AS mainNo,
 LOGISTICSNO AS logisticsno,
 PUSH_ZY_STAT AS pushZyStat,
 PUSH_ZY_TIME AS pushZyTime,
CREATE_TIME
FROM
 waybill
WHERE
  1 = 1
  and CREATE_TIME <= DATE_FORMAT( '2019-05-13 13:55:00' , '%Y-%m-%d %H:%i:%S' )
  and CREATE_TIME >=  DATE_FORMAT( '2019-03-10 13:55:00' , '%Y-%m-%d %H:%i:%S' )

对于这个问题,我认为最彻底解决问题的办法就是根据create_time字段,对waybill表做分区表。


#删除主键索引


alter table test .waybill drop index primary key;
#重新创建复合主键索引(分区表的强制要求)
alter table test .waybill add  PRIMARY KEY (`ID`,`CREATE_TIME`);
#删除如下唯一索引,改成普通索引:
UNIQUE KEY `ID` (`ID`),
UNIQUE KEY `index_unique_waybill` (`LOGISTICSNO`,`OID_TRADERNO`)


#把waybill改成分区表:


alter table test .waybill
partition by range columns (create_time)
(partition p201701   values less than ( '2017-02-01' ),
 partition p201702   values less than ( '2017-03-01' ),
 partition p201703   values less than ( '2017-04-01' ),
 partition p201704   values less than ( '2017-05-01' ),
 partition p201705   values less than ( '2017-06-01' ),
 partition p201706   values less than ( '2017-07-01' ),
 partition p201707   values less than ( '2017-08-01' ),
 partition p201708   values less than ( '2017-09-01' ),
 partition p201709   values less than ( '2017-10-01' ),
 partition p201710   values less than ( '2017-11-01' ),
 partition p201711   values less than ( '2017-12-01' ),
 partition p201712   values less than ( '2018-01-01' ),
 partition p201801   values less than ( '2018-02-01' ),
 partition p201802   values less than ( '2018-03-01' ),
 partition p201803   values less than ( '2018-04-01' ),
 partition p201804   values less than ( '2018-05-01' ),
 partition p201805   values less than ( '2018-06-01' ),
 partition p201806   values less than ( '2018-07-01' ),
 partition p201807   values less than ( '2018-08-01' ),
 partition p201808   values less than ( '2018-09-01' ),
 partition p201809   values less than ( '2018-10-01' ),
 partition p201810   values less than ( '2018-11-01' ),
 partition p201811   values less than ( '2018-12-01' ),
 partition p201812   values less than ( '2019-01-01' ),
 partition p201901   values less than ( '2019-02-01' ),
 partition p201902   values less than ( '2019-03-01' ),
 partition p201903   values less than ( '2019-04-01' ),
 partition p201904   values less than ( '2019-05-01' ),
 partition p201905   values less than ( '2019-06-01' ),
 partition p201906   values less than ( '2019-07-01' ),
 partition p201907   values less than ( '2019-08-01' ),
 partition p201908   values less than ( '2019-09-01' ),
 partition p201909   values less than ( '2019-10-01' ),
 partition p201910   values less than ( '2019-11-01' ),
 partition p201911   values less than ( '2019-12-01' ),
 partition p201912   values less than ( '2020-01-01' ),
 partition p202001   values less than ( '2020-02-01' ),
 partition p202002   values less than ( '2020-03-01' ),
 partition p202003   values less than ( '2020-04-01' ),
 partition p202004   values less than ( '2020-05-01' ),
 partition p202005   values less than ( '2020-06-01' ),
 partition p202006   values less than ( '2020-07-01' ),
 partition p202007   values less than ( '2020-08-01' ),
 partition p202008   values less than ( '2020-09-01' ),
 partition p202009   values less than ( '2020-10-01' ),
 partition p202010   values less than ( '2020-11-01' ),
 partition p202011   values less than ( '2020-12-01' ),
 partition p202012   values less than ( '2021-01-01' ),
    ...
 partition p210012     values less than (maxvalue)
);

可以看到,上述sql语句只需要扫描p201903、p201904、p201905三个分区即可得到结果,不需要全表扫描。

注意事项:由于分区表的限制,必须把分区键create_time做成主键id的一部分,即创建复合主键索引PRIMARY KEY ( ID , CREATE_TIME );同时分区键create_time也必须是唯一键索引的一部分,即复合唯一键索引,或者删除唯一索引,改成普通索引(我是这么做的)。

优化前后对比

优化前 优化后
执行时间 > 1000s 4s

请大家评估上述方案的可行性,谢谢。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28916011/viewspace-2644389/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-06-03

  • 博文量
    185
  • 访问量
    585840