为什么SQL需要优化?
- 查询性能低
- 执行时间过长
- 等待时间过长
- SQL写的太差(尤其体现在多表查询上)
- 索引失效
- 服务器参数(缓存,线程数)设置不合理
- 项目需求不合理
- …等等
我们程序员写代码时能做的只有对写SQL尽可能的做出优化,执行效率更高,有效的使用索引,重点放在写SQL上
SQL的执行过程
MySQL在接收到客户端传入的SQL语句后并不能马上对该SQL进行执行,是需要经过一系列复杂的流程,最终转变成二进制的机器码,才能被执行的,我们需要对执行的SQL进行优化,那么就有必须先来了解下,一个SQL语句的执行有哪些主要环节,以查询的SQL来举例
查询语句的执行过程:
-
通过网络的通讯协议接收客户端传入的SQL
-
查看该SQL对应的结果在查询缓存中是否存在
-
存在则直接返回结果
-
不存在则继续往下走
-
-
由解析器来解析当前SQL,最终形成初步的解析树
-
再由预处理器对解析树进行调整,完成占位符赋值等操作
-
查询优化器对最终的解析树进行优化,包括调整SQL顺序等
-
根据优化后的结果得出查询语句的执行计划,就是查询数据的具体实施方案,交给查询的执行引擎
-
查询执行引擎调用存储引擎提供的API,最后由存储引擎来完成数据的查询,然后返回结果
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 …
详情参看文档: https://www.cnblogs.com/annsshadow/p/5037667.html
SQL优化的目标
对于SQL优化来讲重中之重就是优化索引的使用
什么是索引?
索引是一种帮助数据库获得高效查询效率的数据库对象,该数据库对象使用了特殊的数据结构,以B树和Hash树最为常见,MySQL中索引默认使用的是B树
通俗来讲可以这样理解,索引好比是字典的目录,我们在查询某个字时,可以先从目录中查找,看看我们需要的找字在字典中具体页码是多少,然后再直接翻到对应的页码,从而快速的找我们需要的内容,如果没有这个目录,我们就只能从字典的第一页开始,一页一页的往下翻,直到找到我们需要的内容,从这里我们不难看出目录对查询数据的所提高的性能之大,作用非常关键,同样索引也是一样的
用户表(user)数据:
| id | name | age |
|---|---|---|
| 10 | 赵铁柱 | 18 |
| 20 | 唐马儒 | 25 |
| 30 | 张全蛋 | 20 |
| 40 | 王尼玛 | 16 |
| 50 | 王大锤 | 30 |
| 60 | 易小星 | 30 |
我们对age列建立B树(一般指B+树)索引,遵循树数据结构的特点,对比当前节点,小的放左边,大的放右边:
查询age小于等于18的数据:
-
没有索引的时候
使用的全表检索的方式,直接访问文件中的数据,对该列的每一个值进行访问,此时访问文件中数据使用了大量的IO操作,而IO操作是要耗费大量性能
-
有索引的时候
索引文件已经使用数据结构对数据进行了排序和合并,当我们需要查询age小于等于18的数据时,只要通过二分查找的方式,从索引中找18和18节点上挂载的左边节点全都获取出来,再根据ID获取到对应的数据,这种方式能有效的减少IO操作
使用了索引后我们能感受到最直接的好处就是,通过减少IO操作的次数,从而提升查询的性能
拓展:在BTree数据结构中,数据全都存放在叶子节点上,无论查找什么数据都只跟树的层数有关,一个3层结构的BTree能容纳上百万的数据,在上百万的数据中查询某个数据就只需要找3次,效率极高
索引的利
- 减少IO操作次数,提供查询效率
- 降低CUP使用率(在排序操作中尤为明显)
索引的弊
- 占据大量的硬盘存储空间
- 不适用索引的情况
- 数据量小的表
- 频繁变动的字段
- 不经常查询的字段
- 降低DML操作的效率
索引分类
一个表是可以有多个不同的索引,
-
主键索引
只在建立主键约束时自动添加,特点:非空且唯一
-
单列索引
单独对表中的某个列数据建立索引
-
唯一索引
在该索引中没有重复的数据,都是唯一的
-
复合索引
多个列的值组成的索引,当第一个列的值重复时,按照后面的组合必须查找数据
复合索引相当于字典的二级目录,当前一个值一致时,再使用后一个值做筛选
项目中使用最多的是复合索引,在实际的需求中我们往往都是按照多个条件做查询,而MySQL在做查询时只能选择1个索引来使用,因此复合索引比较有优势,但是复合索引的使用限制比较多,后面在实际操作的时候再说
