MySQL 执行计划

每次提交一个SQL给MySQL,查询优化器都会针对这个SQL语句的语义去生成一个执行计划

1.执行计划内容

  • const
    直接通过聚簇索引或唯一索引+聚簇回源定位到数据,速度极快,性能超高,常量级的
  • ref
    普通二级索引,查询效率也很快,如果包含多个列的普通索引,必须是从索引最左侧开始连续多个列都是等值比较才属于ref
  • ref_or_null
    如果使用了IS NULL这种语法,聚簇索引或唯一索引属于ref,普通二级索引ref_or_null
  • range
    SQL语句里有范围查询会走这个方式
  • index
    需要查询的字段在联合索引里,针对这种只要遍历二级索引就可以拿到想要的数据,而不需要回溯到聚簇索引的访问方式,就是index
  • all
    全表扫描

2.各种SQL语句用什么执行计划

  • select * from table where x1 = xx or x2 >= xxx 索引(x1,x3),(x2,x4) 一般情况下,会选择在索引里扫描行数比较少的条件
    比如x1=xx扫描数据行数比较少,挑选(x1,x3)索引,这里就是一个ref方式,找到符合条件的数据回表查出完整的数据,加载到内存根据x2>=xxx条件筛选
  • select * from table where x1 = xx and c1 = xxx and c2 >= xxxx and c3 IS NOT NULL 索引(x1) 通过索引(x1)查询数据加载到内存再根据c1,c2,c3条件筛选
  • select * from table where x1 = x and x2 = xx 索引(x1),(x2) 分别对x1,x2索引树进行查找,对两拨数据做交集,然后再回表查找完整数据
  • select * from t1, t2 where t1.x1 = xxx and t1.x2 = t2.x2 and t2.x3 = xxx 首先根据t1.x1 = xxx 这个条件去t1表查出一批数据,接着对这批数据x2字段的值,以及t2.x3 = xxx这个条件去t2表里找x2字段的值和x3字段的值都匹配的数据,再把两个表的数据关联起来
    先从一个表里查一波数据,这个表叫驱动表,再根据这波数据去另一个表里查一波数据进行关联,另一个表叫被驱动表

嵌套循环关联
多表查询的时候,他会先在一个驱动表里根据where条件筛选一波数据,然后这波数据走循环,每个数据都到另一个被驱动表里去根据连接条件和where筛选数据然后关联,重复上述过程知道关联完所有表

3.根据成本选择执行计划

  • IO成本
    从磁盘读数据到内存就是IO成本,MySQL都是一页一页读的,读一页的成本约定为1.0
  • CPU成本
    拿到数据对数据进行一些计算,筛选,分组,排序,这些都是耗费CPU资源的,属于CPU成本,一般约定读取和检测一条数据是否符合条件的成本是0.2
  • 全表扫描成本
show table status like '表名' // 拿到表的统计信息

rows就是表里的记录数,data_length就是聚簇索引的字节数大小,data_length/1024/16大约就是页数
IO成本:数据页数量1.0+微调值
CPU成本:行记录数0.2+微调值
IO成本+CPU成本=总成本

  • 索引成本
    一般都是从二级索引查数据再去聚簇索引回表
    二级索引查数据的IO成本一般看查询条件涉及几个范围,一般一个范围就粗暴的认为等同于一个数据页,估算一下根据查询条件可能会在二级索引查出多少数据,接着到聚簇索引回表,默认一条数据就是一个数据页,然后到内存中再去判断符合跳进的

4.根据规则优化执行计划

  • 常量替换
  • 删除没意义的SQL
  • SQL改写
select * from t1 join t2 on t1.x1 = t2.x1 and t1.id = 1

MySQL先根据t1的主键id查询出id=1的值,然后替换掉去t2中查询

select * from t1 where x1 = (select x1 from t2 where id = xxx)

先进行子查询,然后拿着子查询的结果去t1单表查询

select * from t1 where x1 in (select x2 from t2 where x3 = xxx)

MySQL会先执行子查询,把查询结果都写入一个临时表,也可以说是物化表,会多这个表建好索引,然后再进行in查询

select * from t1 where x1 in (select x2 from t2 where x3 = xxx)

MySQL会转化成半连接,semi join,半连接与in+子查询的语义完全相同,这是MySQL内核级的操作

5.explain

每条sql语句前面加上explain就会得到这条sql的执行计划

  • id
    每条执行计划都会对应一个唯一的id
  • select_type
    执行计划对应的查询是个什么查询类型
  • table
    要查询的表名
  • partitions
    表分区
  • type
    访问表的方法,(const,ref,range,index,all)
  • possible_keys
    可能用到的索引
  • key
    实际选择的索引
  • key_len
    索引长度
  • ref
    使用某个字段的索引进行等值匹配搜索的时候,跟索引列进行等值匹配的那个目标的一些信息
  • rows
    预估扫描行数
  • filtered
    经过搜索条件过滤之后剩余数据的百分比
  • extra
    额外的信息