经验总结

  1. 首页 > 生活 > >

Mysql单表访问方法，索引合并，多表连接原理，基于规则的优化，子查询优化

生活百科

参考书籍《mysql是怎样运行的》非常推荐这本书 ， 通俗易懂 ， 但是没有讲mysql主从等内容书中还讲解了本文没有提到的子查询优化内容 ， 本文只总结了常见的子查询是如何优化的系列文章目录和关于我
一丶单表访问方法mysql执行查询语句的方法叫做访问方法 ， 同一语句使用不同的访问方法执行 ， 查询结果都是一样的 ， 但是不同的查询方法效率差距很大 ， mysql优化器会选择成本最低的访问方法 ， 理解访问方法对我们理解索引有益处
1.const查询可以通过主键或者唯一索引与常数进行等值比较来定位一条记录 ， 这种访问方法被定位为const ， 如果唯一索引存在多列 ， 那么需要多列都进行等值比较 。（唯一索引不限制null元素的个数 ， 所以is null并不会使用const访问方法）
2.ref搜索条件为二级索引与常数进行等值比较的 ， 形成的扫描区间为单点扫描区间（key='a' key是二级索引 ， 需要扫描的区间是[a,a],这称为单点扫描区间）只需要定位到满足条件的第一条记录 ， 然后沿着B+树叶子节点的指针向右查找直到不满足条件即可（也许需要回表 ， 并不是将所有满足的数据从二级索引上拿到主键然后一起回表 ， 而是每获取一条便立即回表） 。这种访问方法称为ref.
同样二级索引允许存储null值 ， 且不限制个数（唯一二级索引也不限制）但是为null的值放在B+树的最左侧 ， 查找的流程任然一致 ， 即使是key is null 也必须要进行单点扫描、
如果二级索引存在多列 ， 并不需要多列都进行等值比较 ， 但是要求最左连续的列进行等值比较（比如联合索引a,b,c,a=1 and c=2 可以使用ref ， 找到a=1向右并且索引下推过滤掉不满足c=2的记录 ， 减少回表 ， 但是如果是b=1 and c=2 这时候是无法使用ref的 ， 因为联合索引是先按照a排序 ， 再依次b ， c 。）
3.ref_or_null查询条件是二级索引等值查询 or 二级索引 is null,可以使用ref_or_null,这其实涉及到两个扫描区间[null,null],[等值,等值]执行流程和ref一样 。
4.range使用索引执行查询时 ， 对应的扫描区间是若干单点区间 ， 或者范围扫描区间 ， 那么可以使用range(全表扫描不能算作range ， 单个单点扫描区间是ref而不是range）
5.index我们知道二级索引需要存储索引列和主键 ， 聚簇索引需要存储所有列和主键（以及隐藏列）所以二级索引大小远小于聚簇索引 ， 且如果一个查询不需要进行回表 ， 那么将直接利用二级索引进行全表扫描（索引小 ， 意味着IO次数小）这种访问方法叫index
比如select 主键 from table where 无法走索引的条件 ， 那么这时候不如扫描二级索引 ， 其B+树叶子节点保存的是主键和索引列 ， 每一页可以存放更多数据 ， 减少IO次数 ， 其中的主键也可以覆盖需要查询的主键
6.all直接扫描所有的聚簇索引记录
二丶多范围读取MRR上面我们说到回表 ， 是每从二级索引中获取一条符合的数据都会到聚簇索引根据主键进行回表 ， 但是二级索引中的主键是无需的 ， 这导致每次执行回表操作都是随机IO ， 导致性能开销巨大 ， mysql为了优化这种随机IO ， 使用了MRR多范围读取 ， 即先读取一部分二级索引 ， 然后将主键值排序后再统一执行回标 ， 将随机IO优化为顺序IO 。

经验总结扩展阅读


上一篇：2023年10月11日立墓碑好不好 2023年10月11日立墓碑吉日一览表

下一篇：2023年2月11日认干儿子好吗 2023年2月11日认干儿子吉日一览表