SQL 执行效率如何提高 10,000,000 倍

2019-09-11 10:45:00
admin
原创 1447
摘要:总结:mysql嵌套子查询效率确实比较低可以将其优化成连接查询连接表时,可以先用where条件对表进行过滤,然后做表连接(虽然mysql会对连表语句做优化)建立合适的索引,必要时建立多列联合索引学会分析sql执行计划,mysql会对sql进行优化,所以分析执行计划很重要

总结

  1. mysql嵌套子查询效率确实比较低

  2. 可以将其优化成连接查询

  3. 连接表时,可以先用where条件对表进行过滤,然后做表连接(虽然mysql会对连表语句做优化)

  4. 建立合适的索引,必要时建立多列联合索引

  5. 学会分析sql执行计划,mysql会对sql进行优化,所以分析执行计划很重要


索引优化

上面讲到子查询的优化,以及如何建立索引,而且在多个字段索引时,分别对字段建立了单个索引。推荐阅读: MySQL数据库开发的 36 条军规!


后面发现其实建立联合索引效率会更高,尤其是在数据量较大,单个列区分度不高的情况下。


单列索引


查询语句如下:


select * from user_test_copy where sex = 2 and type = 2 and age = 10


索引


CREATE
           index
           user_test_index_sex
           on
           user_test_copy(sex);
CREATE
           index
           user_test_index_type
           on
           user_test_copy(type);
CREATE
           index
           user_test_index_age
           on
           user_test_copy(age);


分别对sex,type,age字段做了索引,数据量为300w,查询时间:0.415s


执行计划:explain +SQL语句



发现type=index_merge

这是mysql对多个单列索引的优化,对结果集采用intersect并集操作


多列索引


我们可以在这3个列上建立多列索引,将表copy一份以便做测试


create index user_test_index_sex_type_age on user_test(sex,type,age);


查询语句:

select * from user_test where sex = 2 and type = 2 and age = 10


执行时间:0.032s,快了10多倍,且多列索引的区分度越高,提高的速度也越多


执行计划: explain +SQL语句



最左前缀

多列索引还有最左前缀的特性,执行一下语句:


select * from user_test where sex = 2select * from user_test where sex = 2 and type = 2select * from user_test where sex = 2 and age = 10


都会使用到索引,即索引的第一个字段sex要出现在where条件中


索引覆盖


就是查询的列都建立了索引,这样在获取结果集的时候不用再去磁盘获取其它列的数据,直接返回索引数据即可,如:


select sex,type,age from user_test where sex = 2 and type = 2 and age = 10


执行时间:0.003s , 要比取所有字段快的多


排序


select * from user_test where sex = 2 and type = 2 ORDER BY user_name


时间: 0.139s


在排序字段上建立索引会提高排序的效率


create index user_name_index on user_test(user_name)


最后附上一些sql调优的总结,以后有时间再深入研究:


  1. 列类型尽量定义成数值类型,且长度尽可能短,如主键和外键,类型字段等等

  2. 建立单列索引

  3. 根据需要建立多列联合索引


    • 当单个列过滤之后还有很多数据,那么索引的效率将会比较低,即列的区分度较低

    • 如果在多个列上建立索引,那么多个列的区分度就大多了,将会有显著的效率提高。


  4. 根据业务场景建立覆盖索引只查询业务需要的字段,如果这些字段被索引覆盖,将极大的提高查询效率

  5. 多表连接的字段上需要建立索引,这样可以极大提高表连接的效率

  6. where条件字段上需要建立索引

  7. 排序字段上需要建立索引

  8. 分组字段上需要建立索引

  9. Where条件上不要使用运算函数,以免索引失效