5.单表访问方法

单表访问方法

我们在第一章的时候就说过，一条sql的执行需要经过连接管理，解析与优化，存储引擎。那么在第二步中，将解析后的文本进行查询优化后，会生成一个执行计划。这个执⾏计划表明了应该使⽤哪些索引进⾏查询，表之间的连接顺序是 啥样的，最后会按照执⾏计划中的步骤调⽤存储引擎提供的⽅法来真正的执⾏查询，并将查询结果返回给⽤户。下面就来剖析查询优化

1.6大访问方法(access method)

对于存储引擎而言，执行一条SQL查询语句时，无非按照两种方式(如下)

• 全表扫描：查询的时候将表中的记录一条条扫描过去
• 使用索引
  • 使用聚簇索引或唯一(UNIQUE)二级索引
  • 使用普通二级索引
  • 使用聚合索引
  • 遍历二级、聚合索引

这种执行查询语句的方式就叫做访问方法。同⼀个查询语句可能可以使⽤多种不同的访问⽅法来执⾏，虽然最后的查询结果都是 ⼀样的，但是执⾏的时间可能差⽼⿐⼦远了，就像是从钟楼到⼤雁塔，你可以坐⽕箭去，也可以坐⻜机去，当然也可以坐乌⻳去。下边细细道来各种访问⽅法的具 体内容。

(1)const - 使用索引方式

通过主键或者唯⼀⼆级索引列与常数（不含NULL）的等值⽐较来定位⼀条记录是像坐⽕箭⼀样快的，所以他们把这种通过主键或者唯⼀⼆级索引列来定位 ⼀条记录的访问⽅法定义为：const，意思是常数级别的，代价是可以忽略不计的。不过这种const访问⽅法只能在主键列或者唯⼀⼆级索引列和⼀个常数进⾏等 值⽐较时才有效，如果主键或者唯⼀⼆级索引是由多个列构成的话，索引中的每⼀个列都需要与常数进⾏等值⽐较，这个const访问⽅法才有效（这是因为只有该 索引中全部列都采⽤等值⽐较才可以定位唯⼀的⼀条记录）。

(2)ref - 使用索引方式

对于某个普通二级索引与常数（含NULL）进行等值比较,并且使用索引而不是全表扫描的方式，叫做ref

对于聚合索引，只要是最左边的连续索引列是与常数（含NULL）的等值⽐较,并且使用索引而不是全表扫描，叫做ref。如下

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5Bab3BGY-1686638491366)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220829100059828.png)]

(3)ref_or_null - 使用索引方式

有时候我们不仅想找出某个⼆级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来，就像下边这个查询：

SELECT * FROM single_demo WHERE key1 = ‘abc’ OR key1 IS NULL;

当使⽤⼆级索引⽽不是全表扫描的⽅式执⾏该查询时，这种类型的查询使⽤的访问⽅法就称为ref_or_null

(4)range - 使用索引方式

索引（聚簇，二级，聚合）进行范围匹配

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zAQkFoGB-1686638491368)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220829135540345.png)]

(5)index - 使用索引方式

遍历⼆级索引、聚合索引的节点的执⾏⽅式称之为：index。但是有个前提是查询覆盖索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RgFwHu1t-1686638491368)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220829135759840.png)]

(6)all - 全表扫描方式

不使用任何索引，直接去表里面一条条记录遍历，即全表扫描方式

2.4点注意事项

(1)多二级索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fdfMfbFO-1686638491369)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220829160903276.png)]

(2)二级索引 + 非索引列

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AN4EYDvQ-1686638491369)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220829161114711.png)]

(3)复杂搜索条件下找出范围匹配的区间(没有用到索引的条件给它转换为TRUE)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xQcO1gMH-1686638491370)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220829165324801.png)]

(4)索引合并(由优化器决定是否合并，不是一定发生合并)

①Intersection合并（联合索引替代）

不同索引条件之间用and连接的情况。

• 二级索引等值匹配，聚合索引最左连续列等值匹配
• 主键范围匹配

②Union合并

不同索引条件之间用or连接的情况

• 二级索引等值匹配，聚合索引最左连续列等值匹配
• 主键范围匹配

③Sort-Union合并

不同索引条件之间用or连接的情况

• 先按照二级/聚合索引查出的主键值排序，然后进行Union排序

• 主键范围匹配

③Sort-Union合并

不同索引条件之间用or连接的情况

• 先按照二级/聚合索引查出的主键值排序，然后进行Union排序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D9pOjYbi-1686638491370)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220830092333166.png)]