原资料
极客时间-MySQL实战45讲-林晓斌
MySQL查询和更新语句的执行过程
执行过程
- 连接器:连接器负责跟客户端建立连接、获取权限、维持和管理连接。
- TCP,3306端口
- 连接池,与客户端断开连接后,不会销毁线程,而是新来连接后复用,因此需要限制数量
- 查询缓存:同样的sql语句直接返回结果
- 评价:没啥用,mysql 8.0已舍弃
- 默认不开启
- 没啥用是因为使用场景限制
- sql一样,大小写都要相同,这一点还好
- 对表的更新会失效掉这张的表的所有缓存
- 分析器 Parser
- 词法分析:语法树
- 语法分析:检查是否满足 MySQL 语法
- 优化器:根据“解析树”生成最优的执行计划
- 执行器
- 调用存储引擎的接口,完成查询
innodb单表查询过程
- 扫描方式
- 单点扫描
- 区间扫描
- 主体可以分为主键索引,二级索引,无索引
- 主键
- 二级索引需要回表
- 一般是在二级索引上找到一条符合条件的主键,立即回表
- 可以采用MRR方式,在二级索引上一次找到一批符合条件的主键,排序后回表
- 不立即回表的情况如:索引合并
- 无索引全表扫描
- 一般情况下只能走一个索引
- 可以采用索引合并的方式加快搜索,这时就使用了多个索引
- intersect:两个二级索引的单点查询
- 取交集,即and
- 分别从两个二级索引取主键(m+n),主键有序
- 取得一个交集的主键就立即回表
- 减少回表的次数(m+n)降到min(m,n)
- 非单点查询,主键列无序,不能使用这种优化
- Union 和Sort-Union
- Union 是取并集,即是or
- 有序直接回表
- 无序排序后回表
- intersect:两个二级索引的单点查询
- 单表查询类型
- const:常数,
- 通过主键或者唯一二级索引进行=值查询,查询出唯一一条记录
- 组合索引需要所有列的等值,保证唯一一条
- 不能是 is null,null可以是多条,保证唯一一条
- ref,
- 通过二级索引获取到多条记录
- null最快也只能是ref
- ref or null
- ref + null
- null会放在索引的最左面
- range
- 多个单点扫描或范围扫描
- 单点为ref或const
- 负无穷到正无穷是全表扫描
- index
- 直接使用二级索引的“全表扫描”
- 条件
- where条件无法匹配,如联合索引不能使用
- 查询和where条件的列正好又被索引覆盖
- 这样可以对二级索引全量扫描,一般二级索引比主键索引小的多,因此快一些
- all:全表扫描
- const:常数,
innodb多表查询过程
- join https://manzb.top/2020/02/29/mysql-join/
- join类型
- 内连接
- 左外连接
- 右外连接
- 全连接:不支持
- 驱动表与被驱动表
- 内连接,都可以做驱动表,选取一个查询代价小的表做驱动表(小表做驱动表)
- 外连接,
- 一般根据连接关系确定驱动关系
- 除非sql语句等价于内连接,才可以换选驱动表
- 算法
- 被驱动表
- 有索引可以走Index Nested-Loop Join或Batched Key Access join
- 无索引可以走Simple Nested-Loop Join或Block Nested-Loop Join
- Index Nested-Loop Join:索引嵌套循环
- 如果被驱动表有索引,则可以用NLJ
- 每次只能取一个t2的数据获取到结果集后,进行循环
- 效率低
- 被驱动表
- Batched Key Access join:批量Key访问
- 优化点
- 驱动表查询到的行数据先缓存在join_buffer中,之后批量访问被驱动表索引树
- 被驱动表索引行数据缓存在read_rnd_buffer中并按照主键进行排序,之后MRR顺序回表查询数据
- 每次循环只能获取一个结果集优化为两次的批量获取,从mn降低到kn
- 优化点
- Simple Nested-Loop Join
- 暴力匹配,不使用
- 从驱动表取出一条记录,扫描被驱动表,被驱动表没有索引
- 驱动表需要查询n,被驱动表m,则是n*m
- Block Nested-Loop Join
- 将驱动表的需要数据放到join_buffer中,直到放满
- 一行行扫描被驱动表的所有数据,完成匹配,join buffer中的数据未排序,是对t1表做的批量查询
- 循环
- 匹配的次数不变,但是放到了内存中,匹配速度加快了
- 将驱动表的需要数据放到join_buffer中,直到放满