最近工作的时候遇到个问题就是要去优化一条Mysql的查询,想了许多方法都不尽人意,没能把查询速度提高到理想的速度,后来无意间把查询的条件加上单引号,性能就大大提高了,为什么加了单引号性能就会变得这么快呢?
优化前的sql
select table_name,yet_totalSize,day1_totalSize,day1_rate,day7_rate,day7_totalSize from (select r.tbl_name as table_name,sum(if(r.d_s in (20190602) ,r.totalsize,0 ) ) as yet_totalSize,
sum( if(r.d_s in (20190601) ,r.totalsize,0) ) as day1_totalSize,
(sum( if(r.d_s in (20190602) ,r.totalsize,0) ) - sum(if(r.d_s in (20190601) ,r.totalsize,0 )))/sum(if(r.d_s in (20190601) ,r.totalsize,0 ) ) as day1_rate,
(sum( if(r.d_s in (20190602) ,r.totalsize,0) ) - sum(if(r.d_s in (20190526) ,r.totalsize,0 )))/sum(if(r.d_s in (20190526) ,r.totalsize,0 ) ) as day7_rate,
sum( if(r.d_s in (20190526) ,r.totalsize,0) ) as day7_totalSize
from uts3.monitor_data_rawlog r where r.tbl_name in ( "...") and r.d_s in (20190602,20190601,20190526) group by r.tbl_name) t order by day1_totalSize desc limit 0,10
优化后的sql
select table_name,yet_totalSize,day1_totalSize,day1_rate,day7_rate,day7_totalSize from (select r.tbl_name as table_name,sum(if(r.d_s in ('20190602') ,r.totalsize,0 ) ) as yet_totalSize,
sum( if(r.d_s in ('20190601') ,r.totalsize,0) ) as day1_totalSize,
(sum( if(r.d_s in ('20190602') ,r.totalsize,0) ) - sum(if(r.d_s in ('20190601') ,r.totalsize,0 )))/sum(if(r.d_s in ('20190601') ,r.totalsize,0 ) ) as day1_rate,
(sum( if(r.d_s in ('20190602') ,r.totalsize,0) ) - sum(if(r.d_s in ('20190526') ,r.totalsize,0 )))/sum(if(r.d_s in ('20190526') ,r.totalsize,0 ) ) as day7_rate,
sum( if(r.d_s in ('20190526') ,r.totalsize,0) ) as day7_totalSize
from uts3.monitor_data_rawlog r where r.tbl_name in ( "...") and r.d_s in ('20190602','20190601','20190526') group by r.tbl_name) t order by day1_totalSize desc limit 0,10
注意只是d_s这个字段补上单引号而已。
先用EXPLAIN分析优化前的sql语句:
再分析优化后的sql语句:
从2.67s降到170ms,可以看出优化前type=index,key=idx_monitor_data_rawlog_tn(table_name的索引),表示查询是读全表的,没有使用到d_s的索引,扫描了780675行数据。
再看优化后的sql,type=range,key=inx_ds_tn(d_s和table_name的聚合索引),表示查询是按索引范围扫描的,d_s的索引有被使用到,总共才扫描20335行数据。
所以可以看出,没加引号之前,d_s的索引是没有被用到的。
为什么呢?
因为d_s是varchar类型的,而没加单引号字段会被当作数字类型来使用,索引就没法正常使用了(索引)。这就是索引失效的一个条件之一:varchar类型在使用索引的时候要用单引号。
总结:如果列类型是字符串,那一定要在条件中将数据使用单引号包含起来,否则索引会失效。