执行计划和性能优化
const:意味着性能超高的常量级,直接可以通过聚簇索引或者二级索引+聚簇索引回源,轻松查到数据。这种情况的二级索引,必须是唯一索引。
ref:select * from table where name = xxx,如果name是普通索引,则是ref。如果包含多个列的普通查询,那么必须是索引最左侧开始连续多个列都是等值比较才可以是属于ref方式。
针对name is NULL,即使name是主键或者唯一索引,还是只能走ref,但是如果针对一个二级索引同时比较了一个值还有限定了IS NULL,类似于select * from table where name = x or name is NULL,那么在执行计划里就叫做ref_or_null。
range:select * from table where age >= x and age <= x,假设age就是一个普通索引,一旦利用了索引进行范围查询,那么就是range。
index:在(x1, x2, x3)这种索引下,select x1,x2,x3 from table where x2 = xxx。针对这个SQL,会直接遍历KEY(x1, x2, x3)索引树(这里说的是二级索引)的叶子节点的那些页,一个接一个遍历,然后找到x2=xxx的数据,把里面的x1,x2,x3三个字段的值提取出来就可以了。针对这种只要遍历二级索引就可以拿到想要的数据,而不需要回源到聚簇索引的访问方式,叫做index访问方式。
all:全表扫描,扫描聚簇索引的所有叶子节点,也就是一个表里一行一行数据区扫描,数据多性能会很差。
eq_ref:针对被驱动表如果基于主键进行等值匹配,那么他的查询方式就是eq_ref了。
index_merge:单表查询基于多个索引提取数据后进行合并。
如果面对两个字段都能使用索引时如何选择?
MySQL的查询优化器一般会选择在索引里扫描行数比较少的哪个条件
MySQL能否查询到多个索引
如果同时查两个索引树取一个交集后,数据量很小,然后再回表到聚簇索引去查,此时会提升性能。一个SQL要用多个索引,有很多硬性条件,比如说如果有联合索引,必须把联合索引每个字段都放在SQL里,而且必须是等值匹配;或者是通过主键查询+其他二级索引值匹配,也有可能做一个多索引查询和交集。
多表关联的SQL语句时如何执行的
先从一个表查出数据,此表称为驱动表,再根据这波数据区另外一个表查一波数据进行关联,另外一个表叫做"被驱动表"。
嵌套循环关联
nested-loop join就是指假设驱动表找到10条数据,接着就需要用遍历这10条数据的关联字段值去被驱动表用等值查询筛选数据,这只是两个表连接,要是三个表连接就是三重循环。所以,针对多表查询,尽量给两个表都加上索引,索引要确保从驱动表里查询也是通过索引区查找,接着对被驱动表查询也通过索引去找,如果做到这一点,多表关联查询语句性能才能高。
MySQL如何根据成本优化选择执行计划
全表扫描的成本: 需要先磁盘O把聚簇索引里的叶子节点上的数据页一页一页都读到内存页里,这有多少数据页就耗费多少IO成本,接着内存里每一条数据都判断是否符合搜索条件,有多少条数据就要耗费多少CPU成本。
show tables status like 表名
可以看到rows和data_length,rows就是记录数,data_length就是表的聚簇索引的字节数大小,此时用data_length除以1024就是kb为单位的大小,然后再除以16kb(默认页大小),就知道了页数量,此时知道了数据页数量和rows数量,就可以计算全表扫描的成本。
IO成本: 数据页数量 * 1.0 + 微调值
CPU成本: 行记录数 * 0.2 + 微调值
将他们相加,就是一个总的查询成本。
使用索引的成本: 二级索引里根据条件查一波数据的IO成本,查询条件涉及到几个范围,比如说name值再25100,250350两个区间,那么就是两个范围,否则name=xx就仅仅是一个范围区间。一个范围区间粗暴地认为等同于一个数据页,这个IO可以预估得很小,基本是是个位数。拿到索引数据之后,还要根据搜索条件在索引里搜索,此时就要估算从二级索引里读取符合条件的数据成本了。比如估算查到100条数据,那么就是100 * 0.2 + 微调值。
多表关联的成本:按照之前多表关联的逻辑和上面计算成本的方式,计算。