🎈🎈

主题

单表访问 ​

提示

MySQL执行查询语句的方式称为访问方法。对于单表来说，MySQL的单表查询方式被分为全表扫描查询和索引查询。

单表访问方法 ​

单表访问的注意事项 ​

单个二级索引 ​

一般情况下只能利用单个二级索引执行查询。

sql
SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

MySQL查询优化器会判断使用哪个二级索引查询扫描的行数更少，选择较少的那个二级索引查询主键，回表查询后将得到的结果再根据其他的条件进行过滤。

索引合并 ​

一般情况下执行一个查询时最多只会用到单个二级索引，但使用到多个索引来完成一次查询的执行方法称之为：index merge（索引合并）。

  • Intersection合并

某个查询可以使用多个二级索引，将二级索引查询的结果取交集，再回表查询。必须符合如下情况才可能会使用到Intersection合并：

  1. 二级索引列都是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。
sql
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
  1. 主键列可以是范围匹配。因为二级索引的列相同时会按照主键的顺序进行排序，有序的主键有助于提升取交集速度。
sql
# 可能会用到聚簇索引和二级索引合并，因为key1作为二级索引叶子节点中是包含主键的，可以直接二级索引查询后再
# 进行主键匹配，然后回表。这里主键的搜索条件只是从别的二级索引得到的结果集中过滤记录。是不是等值不重要
SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

按照有序的主键回表取记录有个专有名词叫：Rowid Ordered Retrieval，简称ROR。

上述的条件一二是发生Intersection合并的必要条件，但不是充分条件，也就是说即使情况一、情况二成立，也不一定发生Intersection索引合并，这得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，而通过Intersection索引合并后需要回表的记录数大大减少时才会使用Intersection索引合并。

如果多个列不需要单独使用的话还是推荐使用联合索引替代索引合并，少读一颗B+树的同时也不同合并结果。

  • Union合并

某个查询可以使用多个二级索引，将二级索引查询的结果取并集，再回表查询。必须符合如下情况才可能会使用到Union合并：

  1. 二级索引列都是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。
  2. 主键列可以是范围匹配
  3. 使用Intersection索引合并的搜索条件
sql
SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');
  1. 先按照key1 = 'a' AND key3 = 'b'使用Intersection索引合并的方式得到一个主键集合。
  2. 再通过ref的访问方法获取key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'的主键集合。
  3. 采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作返回数据。
  • Sort-Union合并

按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并。比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

Intersection合并适合的是从二级索引中获取的记录数太多，导致回表开销太大而出现的，如果存在Sort-Intersection合并，那么对大量数据进行排序是非常耗时的，所以不存在Sort-Intersection合并。