百万级SQL优化总结

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,这点我仍然很困惑