最近工作中遇到的SQL查询优化的问题,由于查询语句执行时间过长导致请求超时,这里记录一下方便以后查阅,废话不多说直接开始。先说一下我遇到的问题,有两张表具体表内容不方便多说。大概就是一张表存储的基础信息(下面成为A表),需要用另一张表(下面成为B表)中的内容进行统计。一开始使用的是先左连接,然后进行统计,统计的时候在select列表中使用了子查询,导致查询速度慢。不过数据量还不是很大而且着急投产就那么上线了。后来测试环境进行压测导致数据量暴增,结果就不用多说了,直接请求超时了。
超时以后我就对SQL进行了分析(可以使用explane进行分析),发现是因为子查询导致查询速度变慢。所以就把原SQL进行了修改,先在B表中将数据统计完成,然后再与A表进行左连接。速度提升了不止十倍。
所以得出结论,不到万不得已不能在Select列表中写子查询啊,虽然语法是允许的。
就这件事情,对SQL优化的问题,做下总结方便自己查看,也希望对大家有所帮助。其实我理解的所谓的查询SQL的优化,就是避免查询的时候进行全表的扫描。下面给大家说一下如何避免全表扫描,
1、在经常查询的字段上建立索引,也就是在Where和Order by涉及到的列上创建索引,以前只知道要创建索引,很多人不知道为什么创建索引,其实就是为了避免扫描全表。但是创建索引的时候要注意,如果这一列的数据有大量重复数据的话,就不要在该列创建索引了,设置了也不会生效;
2、避免对字段进行null值的判断;
比如说,select id,name from student where countNum is null; 或者说select id,name from student where countNum is not null;
如果需要用到这个需要怎么办呢,我们创建表结构或者插入数据的时候,可以给这些字段一个默认值0。判断的时候直接select id,name from student where countNum=0就可以了。
3、尽量避免使用in和not in,合理使用in和exists(in和exists的区别在单独的文章中记录);
4、尽量避免使用!=、<>操作符及or这些都会导致引擎放弃使用索引。
5、我们常用的模糊查询like '%%'同样会导致全表扫描。
6、where 子句中避免对字段进行表达式的计算;
比如,select id,name from student where countNum/10>6,如果遇到这种情况可以用where countNum>6*10。
7、索引的个数要适量,因为创建索引会对insert和update的效率有影响,按照以往的经验不要超过6个。
8、另外,创建符合索引时候,必须使用该索引中的第一个字段作为条件,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
以上是避免全表扫描的情况,除此以外设计表结构的时候,能使用数值类型的,就避免使用字符型。因为在处理查询的时候,字符类型是逐个字符比较,而数值型只比较一次。还有一个最最重要的,今天写这篇文章的目的。select列表不要使用子查询,哈哈。
今天就到此为止吧,希望对大家有所帮助。