![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/881b17159120af564f95594ee26ad93d.png)
1.当客户端连接到MySQL服务器时,服务器对其进行认证。可以通过用户名与密码认证,也可以通过SSL证书进行认证。登录认证后,服务器还会验证客户端是否有执行某个查询的操作权限。
2.在正式查询之前,服务器会检查查询缓存,如果能找到对应的查询,则不必进行查询解析,优化,执行等过程,直接返回缓存中的结果集。
3.MySQL的解析器会根据查询语句,构造出一个解析树,主要用于根据语法规则来验证语句是否正确,比如SQL的关键字是否正确,关键字的顺序是否正确。
而预处理器主要是进一步校验,比如表名,字段名是否正确等
4.查询优化器将解析树转化为查询计划,一般情况下,一条查询可以有很多种执行方式,最终返回相同的结果,优化器就是根据成本找到这其中最优的执行计划
5.执行计划调用查询执行引擎,而查询引擎通过一系列API接口查询到数据
6.得到数据之后,在返回给客户端的同时,会将数据存在查询缓存中
在开篇的图里面,我们知道了SQL语句从客户端经由网络协议到查询缓存,如果没有命中缓存,再经过解析工作,得到准确的SQL,现在就来到了我们这模块说的优化器。
首先,我们知道每一条SQL都有不同的执行方法,要不通过索引,要不通过全表扫描的方式。
那么问题就来了,MySQL是如何选择时间最短,占用内存最小的执行方法呢?
什么是成本?
1.I/O成本。数据存储在硬盘上,我们想要进行某个操作需要将其加载到内存中,这个过程的时间被称为I/O成本。默认是1。
2.CPU成本。在内存对结果集进行排序的时间被称为CPU成本。默认是0.2。
单表查询的成本
先来建一个用户表dev_user,里面包括主键id,用户名username,密码password,外键user_info_id,状态status,外键main_station_id,是否外网访问visit,这七个字段。索引有两个,一个是主键的聚簇索引,另一个是显式添加的以username为字段的唯一索引uname_unique。
如果搜索条件是select * from dev_user where username=‘XXX’,那么MySQL是如何选择相关索引呢?
1.使用所有可能用到的索引
我们可以看到搜索条件username,所以可能走uname_unique索引。也可以做聚簇索引,也就是全表扫描。
2.计算全表扫描代价
我们通过show table status like ‘dev_user’命令知道rows和data_length字段,如下图。
ows:表示表中的记录条数,但是这个数据不准确,是个估计值。
data_length:表示表占用的存储空间字节数。
data_length=聚簇索引的页面数量X每个页面的大小
反推出页面数量=1589248÷16÷1024=97
I/O成本:97X1=97
CPU成本:6141X0.2=1228
总成本:97+1228=1325
3.计算使用不同索引执行查询的代价
因为要查询出满足条件的所有字段信息,所以要考虑回表成本。
I/O成本=1+1X1=2(范围区间的数量+预计二级记录索引条数)
CPU成本=1X0.2+1X0.2=0.4(读取二级索引的成本+回表聚簇索引的成本)
总成本=I/O成本+CPU成本=2.4
4.对比各种执行方案的代价,找出成本最低的那个
多表查询的成本
对于两表连接查询来说,他的查询成本由下面两个部分构成:
- 单次查询驱动表的成本
- 多次查询被驱动表的成本(具体查询多次取决于对驱动表查询的结果集有多少个记录)
index dive
如果前面的搜索条件不是等值,而是区间,如select * from dev_user where username>‘admin’ and username<'test’这个时候我们是无法看出需要回表的数量。
步骤1:先根据username>'admin’这个条件找到第一条记录,称为区间最左记录。
步骤2:再根据username<'test’这个条件找到最后一条记录,称为区间最右记录。
步骤3:如果区间最左记录和区间最右记录相差不是很远,可以准确统计出需要回表的数量。如果相差很远,就先计算10页有多少条记录,再乘以页面数量,最终模糊统计出来。