[mySQL] 小册笔记 - 单表访问

BiaDisaForTruth
176 阅读10分钟

Prefix

  • 单表查询

  • from只从一个表里查询

  • case :

    CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

访问方法（access method）

MySQL查询的执行方式大致分为以下两种：

  • 全表扫描
  • 索引扫描
    • 针对主键或唯一二级索引的等值查询
    • 针对普通二级索引的等值查询
    • 针对索引列的范围查询
    • 直接扫描整个索引

MySQL执行查询语句的方式称之为访问方法或者访问类型，在explain语句中，在type栏展示。

const

  • 在explain中，表示为：
    • type = const
    • ref = const

根据索引排序值（群簇索引中的主键，二级索引中唯一索引的所有值）进行等值查询，且查询列都在索引叶子节点上时为const。

例如上述表中的：

select * from single_table where id = ?

以及：

select * from single_table where key2 = ?

const意味着：

  • 查询条件在索引树中可以直接查询到唯一一条记录（例如：群簇索引中的主键，唯一索引中的所有值）

    • 这代表索引所在的B+树就是根据查询值去排序的，并且查询值在索引树中是唯一的。

      • 如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个const访问方法才有效（这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录）。

    • 但是唯一索引，不限制null值数量，因此如果查询列是唯一索引上的但希望通过唯一索引值获取与null值相等的记录，那么不是const查询。

ref

  • explain语句中，type为ref

当二级索引不限制值时，使用常值等值比较，通过二级索引执行查询的方式称为ref。

例如上表中，进行如下查询：

SELECT * FROM single_table WHERE key1 = 'abc'

由于是不限制值，因此在到叶子节点的查询上可能变成了范围查询查出了多条记录，因此速度会比const类型查询的确定唯一记录慢，但效率还是可以的。

  • 当匹配的列太多时，由于有回表的操作，此时效率就低了。

  • 在唯一索引中是不限制null值的，因此在唯一索引中使用IS NULL进行查找时，也是通过ref进行查找的：

    select * from single_table where key2 is NULL

  • 在联合索引中，如果前序的条件都是等值查询，那么查询类型也为ref：

    SELECT * FROM single_table WHERE key_part1 = 'god like';

SELECT * FROM single_table WHERE key_part1 = 'god like' and key_part2='1234'

SELECT * FROM single_table WHERE key_part1 = 'god like' and key_part2='1234' and key_part3='1234';

    此处条件依然是全部为等值查询，不能是范围查询。

ref_or_null

当在二级索引（包括唯一、联合）中，查询条件为等值与null值，此时查询类型就会变成ref_or_null。

  • 根据B+树的排列可知null值是放在最前面的，因此null值是较为容易取得索引上的叶子数据记录的，而不是范围查询需要判断查询范围的。

例如在上述的表中查询：

select * from single_table where key2 =10 or key2 is NULL

查询类型即为ref_null。

range

查询条件对索引列规定了了多个允许值（区间或者多个确定值）的时候就会通过range查询类型进行查询。

  • 包括群簇索引和二级索引，只要出现了索引列范围匹配，那么查询类型都会是range。
select * from single_table where id = 30 or id = 100

select * from single_table where id > 30 

select * from single_table where id in (30,50)

这里需要注意的是，如果in中只有一个值，那么mySQL会优化为const/ref 类型的查询。

index

index查询方式的条件是：

  • 查询列在联合索引中
  • 查询条件缺失了前面的几项

此时由于联合索引的叶子节点中，存储的数据较群簇索引中少，因此查询通过联合索引叶子节点中的查询值进行直接比较要快于群簇索引中查找。

例如：

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

all

不满足上述所有查询条件

  • 查询条件无法使用索引
  • 查询列不在索引中

此时，就会通过群簇索引进行全表扫描，该查询方式称为all。

索引合并

使用多个索引来完成一次查询的执行方式称为index merge

具体的合并算法有3种，以下的索引合并策略都是在MySQL优化器中进行决定的。

可能出现的场景：

  • intersection : 单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大,通过Intersection索引合并后需要回表的记录数大大减少。（AND）

  • union : 优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。(OR)

  • sort-union ： 在union场景中，等值匹配变成了范围匹配，将主键取出排序后就变成了union场景。

为啥有Sort-Union索引合并，就没有Sort-Intersection索引合并么？是的，的确没有Sort-Intersection索引合并这么一说， Sort-Union的适用场景是单独根据搜索条件从某个二级索引中获取的记录数比较少，这样即使对这些二级索引记录按照主键值进行排序的成本也不会太高 而Intersection索引合并的适用场景是单独根据搜索条件从某个二级索引中获取的记录数太多，导致回表开销太大，合并后可以明显降低回表开销，但是如果加入Sort-Intersection后，就需要为大量的二级索引记录按照主键值进行排序，这个成本可能比回表查询都高了，所以也就没有引入Sort-Intersection这个玩意儿。

intersection合并 - AND

  • 使用多个二级索引，将从多个二级索引中查询到的结果取交集

例如：

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

查询过程

  • 从idx_key1二级索引对应的B+树中取出key1 = 'a'的相关记录。
  • 从idx_key3二级索引对应的B+树中取出key3 = 'b'的相关记录。
  • 计算出这两个结果集中id值的交集。
  • 回表，到群簇索引中将所有记录查出。

在只有一个索引的情况下，查询过程：

  • 从idx_key1二级索引对应的B+树中取出key1 = 'a'的相关记录。
  • 回表，符合条件的都查出。
  • 在群簇索引取出的数据中按照剩余条件再过滤。

虽然读取多个二级索引比读取一个二级索引消耗性能，但是读取二级索引的操作是顺序I/O，而回表操作是随机I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

Intersection索引合并的必要场景

  • 二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。

    • 这里的每个列，对于联合索引来说，必须是所有索引列同时出现的情形。

      • 例如，以下语句是会触发索引合并的：
      SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
      • 以下语句不会(联合索引中的索引列没有全部出现)：
      SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';

    • 需要注意是，范围查询同样不会触发该索引合并。

  • 主键列可以是范围查询，如：

    SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

解释：

  • 对于二级索引：
  • 只有等值查询，从两个索引树中查找出的主键才会是按照主键排好序的，这样子就好取交集。在取好后，到群簇索引上进行 顺序IO的查询了。
  • 对于包含主键：
  • 由于二级索引的叶子节点上的数据，是包括主键并且是排序好的，因此在确定最底层的数据后，可以很快地找到所有连续的主键，再回表顺序IO查找。因此主键在此场景下不需要确定的值的。

按有序主键值回表取数据称为Rowid Ordered Retrieval简称ROR。

出现场景

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

union合并 - OR

intersection意思为交集，那么union就意味着并集。那么，union合并的情形，就应该是与or关联的。

场景

  • 二级索引列等值匹配。和intersection中的情形相同，为了保证最终是ROR形式的读取，需要索引列中的所有值，包括联合索引中的全部索引列。

    • 此情形下，主键列可以是范围匹配。

  • 使用Intersection索引合并的搜索条件

    • 例如：

      SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

      优化器可能采用这样的方式来执行这个查询：

      • 先按照搜索条件key1 = 'a' AND key3 = 'b'从索引idx_key1和idx_key3中使用Intersection索引合并的方式得到一个主键集合。
      • 再按照搜索条件key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'从联合索引idx_key_part中得到另一个主键集合。
      • 采用Union索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。

上两个场景的原因，和在intersection合并部分的原因是大致相同的。

出现场景

优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过Union索引合并后进行访问的代价比全表扫描更小时才会使用Union索引合并。

Sort-Union合并 -OR强化

Union索引合并条件太苛刻：需要保证等值匹配才可以使用。例如以下这个语句就不可以

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

这里，无论是从key1，还是key3的索引中获取的主键集合值都是未排序好的，但是如果将取出的主键集合值排序，那么接下来就可以进行和union合并相同的操作。操作如下：

  • 先根据key1 < 'a'条件从idx_key1二级索引中获取记录，并按照记录的主键值进行排序
  • 再根据key3 > 'z'条件从idx_key3二级索引中获取记录，并按照记录的主键值进行排序
  • 因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。

我们把上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

相关说明

  • 显然索引合并，是一个优于全表扫描，但显然弱于直接通过索引检索（const,ref,range）的一个折衷方案。
    • 那么，出现了intersection索引合并的情形，使用联合索引替代，可提高效率。
avatar
文章
阅读
粉丝