思路(概念->使用->12个字段分析->语句执行流程->流程涉及的bufferpool)
概念:
explain是模拟优化器分析sql和在服务端如何执行sql的过程的工具。
使用:
通过慢查询日志,找到对应项目中的sql。通过在执行查询的sql最前面加上explain关键字,依据ID自大到小,相同自上到下分析,有没有走索引,索引是否可以优化。
字段分析:总共12个字段:
Id: 查询表顺序依据id由大到小,id相同自上而下
Select_type:表示查询的类型,六种,simple(直接查表)\primary(嵌套查询的外层查询)\subquery(嵌套查询内部的子查询)\derived\union\union result(UNION操作的结果,id值通常为NULL)
Table: 表示 explain 的一行正在访问哪个表
Partitions: 如果查询是基于分区表的 话,会显示查询将访问的分区。
Type: 针对单表的访问方法,有7种。
System:const在myisam中存在的一种形式,是最好的结果
Const:主键或唯一索引只能查到一条记录
eq_ref:一般出现在join的关联查询中。primary key 或 unique key 索引的所有部分被连接使 用 ,最多只会返回一条符合 条件的记录。
ref: 非唯一性的索引
range: 索引范围查询
index: 访问索引中的所有数据,可以通过加where优化
all:全表扫描
Possible_keys: 可能用到的索引
Key: 实际用到的索引
Key_len: 使用的索引长度, utf8mb4如果是varchar,则为4n+2,如果可以为null,则再加1
Ref: 筛选数据的参考
Rows: 预计扫描行数
Filtered: 表示存储引擎返回的数据经过server过滤后显示的百分比,越高越好
Extra: 额外的信息,会有6况:
using index覆盖索引;
using where表示存储引擎没法利用索引过滤数据,在server层进行过滤;
using index condition:索引下推,
using filesort:不能直接用索引排序;
using temporary:使用了临时表,如distinct非索引列,或group by
using join buffer:使用cache来对MySQL join连接优化
语句执行流程:
1、事务开始,从内存(buffer pool)或磁盘(data file)取到包含这条数据的数据页,返回给server的执行器
2、server的执行器修改数据页的这一行数据的值
3、记录修改前的值到undo log(用于异常回滚)
4、记录修改后的值到redo log(持久化,用于崩溃恢复)
5、调用存储引擎接口,更新数据页到buffer pool(修改记录的值)
6、事务提交
buffer pool(缓冲池):
作用:缓存热点表数据与索引数据,把磁盘上的数据加载到缓冲池,避免每次访问都进行磁盘IO,起到加速访问的作用。
缓冲流程:
读数据如果所查看的page在buffer pool中则直接返回,写数据则把page先写到buffer pool里,会被先刷到redo log中,再写到DB file中,如果未同步到db中,则page为脏页,后台有线程在不断的将buffer中的page写入到db中,叫刷脏。如果数据库宕机可以从redo log将数据继续写入到db中完成刷脏.