慢SQL优化与分区表应用-CSDN博客

针对数据库中因大量慢SQL导致磁盘IO过高的问题，本文详细分析了一个与waybill表相关的慢查询案例，展示了如何通过修改SQL语句及采用分区表技术，显著提升查询效率，从1000秒优化至0.4秒。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

现象

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

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

JP++OMPSklJoQMHDji2iqIoiqIoSl7B1CdG1KCoVa9e3bH1YrfuCKmsKYqiKIqiKCcH3WdNURRFURTlFECVNUVRFEVRlDBGlTVFURRFUZQwRpU1RVEURVGUMEZfMFAURVEURQljdGRNURRFURQlbCH6Px5ZFRmto8B4AAAAAElFTkSuQmCC

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

41fJCvYJXhQAAAAASUVORK5CYII=

慢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'

tcgUAAAAAMEd8AspjawAAAABAS89mZwAAAAAAsAcmoAAAAACAIZiAAgAAAACGYAIKAAAAABiCCSgAAAAAYAgmoAAAAACAIZiAAgAAAACG+BsqC33G1vepSAAAAABJRU5ErkJggg==

分析：

通过观察执行计划，发现走的全表扫描，预估扫描行数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'

)

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

gv7cAAAAASUVORK5CYII=

优化前后对比

	优化前	优化后
执行时间	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'

)

P5kiuPlJulenI+JI+ax4+19dY+udktNQDQC7EIx1HB745Y5d6BWdNLj1OYsFcuN2H3WLMi5xWIsR+BB1mAxsBcC7YgQYeEY+mA7AaJFIAxuIlprzICQAAYA9w3wDeeLMQf3q+r5IDAAAAAAAAAAC4BP8Bi0VWjLD+WH0AAAAASUVORK5CYII=

对于这个问题，我认为最彻底解决问题的办法就是根据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)



 );

Abo0uDqPz0gFAAAAAElFTkSuQmCC

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

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

优化前后对比