mysql 查询语句的执行过程

原资料

极客时间-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
    • 有序直接回表
    • 无序排序后回表
• 单表查询类型
  • const：常数，
    • 通过主键或者唯一二级索引进行=值查询，查询出唯一一条记录
    • 组合索引需要所有列的等值，保证唯一一条
    • 不能是 is null，null可以是多条，保证唯一一条
  • ref，
    • 通过二级索引获取到多条记录
    • null最快也只能是ref
  • ref or null
    • ref + null
    • null会放在索引的最左面
  • range
    • 多个单点扫描或范围扫描
    • 单点为ref或const
    • 负无穷到正无穷是全表扫描
  • index
    • 直接使用二级索引的“全表扫描”
    • 条件
      • where条件无法匹配，如联合索引不能使用
      • 查询和where条件的列正好又被索引覆盖
    • 这样可以对二级索引全量扫描，一般二级索引比主键索引小的多，因此快一些
  • all：全表扫描

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表做的批量查询
  • 循环
  • 匹配的次数不变，但是放到了内存中，匹配速度加快了