为什么SQL需要优化



1. 查询性能低

2. 执行时间过长

3. 等待时间过长

4. SQL写的太差(尤其体现在多表查询上)

5. 索引失效

6. 服务器参数(缓存,线程数)设置不合理

7. 项目需求不合理

SQL的执行过程

MySQL在接收到客户端传入的SQL语句后并不能马上对该SQL进行执行,是需要经过一系列复杂的流程,最终转变成二进制的机器码,才能被执行的,我们需要对执行的SQL进行优化,那么就有必须先来了解下,一个SQL语句的执行有哪些主要环节,以查询的SQL来举例。



1、通过网络的通讯协议接收客户端传入的SQL。

2、查看该SQL对应的结果在查询缓存中是否存在，存在则直接返回结果，不存在则继续往下走。

3、解析器来解析当前SQL,最终形成初步的解析树。

4、预处理器对解析树进行调整,完成占位符赋值等操作。

5、查询优化器对最终的解析树进行优化,包括调整SQL顺序等。

6、根据优化后的结果得出查询语句的执行计划,就是查询数据的具体实施方案,交给查询的执行引擎。

7、查询执行引擎调用存储引擎提供的API,最后由存储引擎来完成数据的查询,然后返回结果。

写SQL时的顺序



select ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

from ... on ... join ... where ... group by ... having ... select ... order by ... limit ...



对于SQL优化来讲重中之重就是优化索引的使用

什么是索引?

索引是一种帮助数据库获得高效查询效率的数据库对象,该数据库对象使用了特殊的数据结构,以B+树和Hash树最为常见,MySQL中索引默认使用的是B+树。

我们在查询某个字时,可以先从目录中查找,看看我们需要的找字在字典中具体页码是多少,然后再直接翻到对应的页码,从而快速的找我们需要的内容,如果没有这个目录,我们就只能从字典的第一页开始,一页一页的往下翻,直到找到我们需要的内容,从这里我们不难看出目录对查询数据的所提高的性能之大,作用非常关键,同样索引也是一样的。

索引提高查询效率的原理

我们对age列建立B树(一般指B+树)索引,遵循树数据结构的特点,对比当前节点,小的放左边,大的放右边:

查询age小于等于18的数据:

select * from user where age <= 18

没有索引的时候

使用的全表检索的方式,直接访问文件中的数据,对该列的每一个值进行访问,此时访问文件中数据使用了大量的IO操作,而IO操作是要耗费大量性能。

有索引的时候

索引文件已经使用数据结构对数据进行了排序和合并,当我们需要查询age小于等于18的数据时,只要通过二分查找的方式,从索引中找18和18节点上挂载的左边节点全都获取出来,再根据ID获取到对应的数据,这种方式能有效的减少IO操作使用了索引后我们能感受到最直接的好处就是,通过减少IO操作的次数,从而提升查询的性能。

拓展

在B+Tree数据结构中,数据全都存放在叶子节点上,无论查找什么数据都只跟树的层数有关,一个3层结构的BTree能容纳上百万的数据,在上百万的数据中查询某个数据就只需要找3次,效率极高。

索引的利

• 减少IO操作次数,提供查询效率

• 降低CUP使用率(在排序操作中尤为明显)

索引的弊

• 占据大量的硬盘存储空间

不适用索引

• 数据量小的表

• 频繁变动的字段

• 不经常查询的字段

• 降低DML操作的效率

索引分类

• 一个表是可以有多个不同的索引,

主键索引

• 只在建立主键约束时自动添加,特点:非空且唯一

单列索引

• 单独对表中的某个列数据建立索引

唯一索引

• 在该索引中没有重复的数据,都是唯一的

复合索引

• 多个列的值组成的索引,当第一个列的值重复时,按照后面的组合必须查找数据。

• 复合索引相当于字典的二级目录,当前一个值一致时,再使用后一个值做筛选。

• 项目中使用最多的是复合索引,在实际的需求中我们往往都是按照多个条件做查询,而MySQL在做查询时只能选择1个索引来使用,因此复合索引比较有优势,但是复合索引的使用限制比较多,后面在实际操作的时候再说。

强调

在SQL语句执行之前,会专门有个叫查询优化器的组件对我们写的SQL进行优化和调整,然后生成执行计划,也就是说,最终执行的SQL不一定是我们当初自己写的SQL,如果出现这个情况大家不要惊讶

语法

explain 查询语句

其中type是我们需要优化的:其值常见的有以下几个,按照执行性能排列为

system > const > eq_ref > ref > range > index > all

const: 根据主键索引或者唯一索引查询到的结果

explain select id,name from employee where id = 1;

ref: 使用非唯一性索引做查询,返回匹配的记录行,常见于多表查询中

explain select * from department d join employee e on d.id = e.dept_id;

range: 索引做范围查询,常见于<、<=、>、>=、between等操作符

explain select age from employee where age >= 20 and age <= 30

index: 索引全查询,MySQL遍历整个索引来查找匹配的行。

explain select age from employee where age >= 20

注意:SQL优化的目标之一要把type优化在ref到index之间,该值没有优化的情况下一般都是all

索引原则

1. 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件 作为索引的列,如果不能有效的区分。数据,那么这个列就不适合作为索引列;比如(性别,状态不多的状态列)。

2. 更新非常频繁的字段不适合创建索引,原因:索引有维护成本。

3. 不会出现在where 子句中的字段不该创建索引。

4. 索引不是越多越好;(只为必要的列创建索引)。

join原则

注意:不要使用隐式连接,原因在于SQL的执行顺序



1. 数据量小的表写在join的左边,数据量大的表写在join的右边

MySQL中join都是通过Nested Loop Join来实现,简单理解为循环嵌套,应该数据量少的表作为外层循环,数据量大的表作为内层循环,然后合并结果

2. 优先优化Nested Loop的内层循环

3. 保证join语句中被作为连接条件的字段已经建立了索引

4. 扩大缓冲区的大小,容纳更大的查询数据(在配置文件中配置,DBA来做)

1. where语句中索引列参与算术计算,该索引失效

2. where语句中索引列参与函数运算,该索引失效

3. where语句中使用in运算符有时会让索引失效

4. where语句中做不等于( != , <> )运行,该索引失效

5. where语句中发生类型转发,该索引失效

6. where语句中模糊查询时以%开头,该索引失效

7. 在复合索引的使用时跟声明时顺序不一致或者中间有列的缺失,该索引失效



如:声明了(a,b,c)的复合索引,但是在用是时候中间有列的缺失where a = xx and c = xx中间缺失了b,所以无法使用该复合索引,只要不是缺失中间列,其他情况索引均有效