SQL优化
1:基本环境
1:数据库:Mysql
2:表结构
3:数据数量:500+万
2:目的
2.1:原始Sql语句
selectzero_dateline,sum(item_amount) as `sum` from c_log_item where item_id in(11400019,12400199,11400018) and zero_dateline between '1361116800' and'1363535999' and item_amount>0 group by zero_dateline order by zero_datelineasc
花费时间:40s
3:思考
3.1:in
网上说in的速度很慢,用上in就不能用索引,慢是对,但是关于in不能使用索引这个是错误的.
有的说用exists来代替,也有的说用join来代替.我找到网上一种我比较赞同的原话
IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。
总之,这个in是可以进行优化的
3.2关于索引
索引是提高数据查询的必备神器.高性能Mysql曾经提到过很多关于Sql正确的使用索引方法,因为一些Sql语句的不正确也会导致Sql不会运行.其中有一条就是:当多个条件时,不会全部使用索引,对于这条语句而言,即使分别为各个字段建索引,也不会使用到,因此应该使用复合索引.另外,在使用普通查询时可以在select前面加上explain命令,这个命令可以查看用到的索引,以及级别.
用之前那条sql查询时,使用的索引是zero_dateline,并且type的级别为index.
Type级别由高到低为: system >const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL
最坏的情况就是All.这个就是没有使用索引,而导致的全表扫描.
网上提示至少要保证sql在range级别以上.所以关于索引,这部分也可以优化.
4:结论
两者结合就是
1:添加联合索引:
ALTER TABLE c_log_item ADD INDEXzero_item(item_id,item_amount,zero_dateline);
2:修改sql语句为:
select`zero_dateline`,sum(`item_amount`) as `sum` from(select`zero_dateline`,`item_amount` from `c_log_item` where `item_id` in(11400019,12400199,11400018)) as newTable where `zero_dateline` between'1361116800' and '1363535999' and`item_amount`>0 group by `zero_dateline` order by `zero_dateline` asc;
结果显示:
使用时间:0.48s
Type级别:range
5:总结:
有的说in不好,但是我觉得应该因地制宜.如之前红色字所说,外表大,内表小的时候用in.当仅仅使用in的时候表的数据为2w左右,当使用item_amount的时候,数据量为20W+,当仅仅使用zero_dateline的时候,数据量为300万,因此,这样的方式使用in是不错的.
另外,使用了in作为子查询,那么就不能使用了索引,这句话是对的,使用in之后,用explain显示,的确两条语句,in没有使用索引,但是优化后结果为0.48s,这点我仍然很困惑