为什么SQL需要优化?
- 查询性能低
- 执行时间过长
- 等待时间过长
- SQL写的太差(尤其体现在多表查询上)
- 索引失效
- 服务器参数(缓存,线程数)设置不合理
- 项目需求不合理
- ...等等
我们程序员写代码时能做的只有对写SQL尽可能的做出优化,执行效率更高,有效的使用索引,重点放在写SQL上
SQL的执行过程
MySQL在接收到客户端传入的SQL语句后并不能马上对该SQL进行执行,是需要经过一系列复杂的流程,最终转变成二进制的机器码,才能被执行的,我们需要对执行的SQL进行优化,那么就有必须先来了解下,一个SQL语句的执行有哪些主要环节,以查询的SQL来举例
查询语句的执行过程:
SQL的执行顺序
写SQL时的顺序
select ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
解析SQL时的顺序
from ... on ... join ... where ... group by ... having ... select ... order by ... limit ...
SQL优化的目标
在SQL语句执行之前,会专门有个叫查询优化器的组件对我们写的SQL进行优化和调整,然后生成执行计划,也就是说,最终执行的SQL不一定是我们当初自己写的SQL,如果出现这个情况大家不要惊讶
对于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操作的次数,从而提升查询的性能
拓展:在BTree数据结构中,数据全都存放在叶子节点上,无论查找什么数据都只跟树的层数有关,一个3层结构的BTree能容纳上百万的数据,在上百万的数据中查询某个数据就只需要找3次,效率极高
索引的利
- 减少IO操作次数,提供查询效率
- 降低CUP使用率(在排序操作中尤为明显)
索引的弊
- 占据大量的硬盘存储空间
- 不适用索引的情况数据量小的表频繁变动的字段不经常查询的字段降低DML操作的效率
索引分类
一个表是可以有多个不同的索引,
- 主键索引只在建立主键约束时自动添加,特点:非空且唯一
- 单列索引单独对表中的某个列数据建立索引
- 唯一索引在该索引中没有重复的数据,都是唯一的
- 复合索引多个列的值组成的索引,当第一个列的值重复时,按照后面的组合必须查找数据复合索引相当于字典的二级目录,当前一个值一致时,再使用后一个值做筛选
项目中使用最多的是复合索引,在实际的需求中我们往往都是按照多个条件做查询,而MySQL在做查询时只能选择1个索引来使用,因此复合索引比较有优势,但是复合索引的使用限制比较多,后面在实际操作的时候再说
