首页  思维导图  详情

单表访问，多表连接

2019-08-09 23:03:08   21  举报





AI智能生成

Mysql_单表访问，多表连接导图，

数据库

后端

mysql

作者其他创作

大纲/内容

单表访问

访问方法大致分类

使用全表扫描进行查询

使用索引进行查询

针对主键或唯一二级索引的等值查询

针对普通二级索引的等值查询

针对索引列的范围查询

直接扫描整个索引

类型

情景

为id列建立的聚簇索引
为key1列建立的idx_key1二级索引
为key2列建立的idx_key2二级索引，并且该索引是唯一二级索引
为key3列建立的idx_key3二级索引
为key_part1、key_part2、key_part3建立的idx_key_part二级索引，也是一个联合索引

const类型

定义

通过主键或者唯一二级索引列与常数的等值比较来定位一条记录

例子

主键列与常数的等值比较

SELECT * FROM single_table WHERE id = 1438;

唯一二级索引列与常数的等值比较

SELECT * FROM single_table WHERE key2 = 3841;

与NULL值比较不是const类型

SELECT * FROM single_table WHERE key2 IS NULL;

ref类型

定义

搜索条件为二级索引列与常数等值比较

例子

二级索引列与常数等值比较

SELECT * FROM single_table WHERE key1 = 'abc';

最左边的连续索引列是与常数的等值比较

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

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

二级索引列值为NULL的情况

where key IS NULL

最左边的连续索引列不是与常数的等值比较
则不是ref类型

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';

ref_or_null类型

定义

有时我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为NULL的记录也找出来

例子

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

range类型

定义

搜索条件为索引列的范围查询比较

例子

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

上述例子的3个范围可以看作3个区间

key2 = 1438

key2 = 6328

key2 属于【38,79】

index类型

定义

遍历二级索引记录的执行方式

例子

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

上面这个查询符合两个条件

1. 它的查询列表只有3个列，key_part1,key_part2,key_part3，而索引idx_key_part又包含这3个列

2. 搜索条件只有key_part2列，这个列也包含在索引idx_key_part中

因此我们只要遍历联合索引，然后对每一条记录进行过滤就可以啦。也不用进行回表操作
因为联合索引中一条记录存储的信息肯定比聚簇索引的要少，因此遍历的成本也就更低（因为非叶子节点能存储更多的记录）

all

定义

全表扫描，也就是直接扫描聚簇索引，都每一条数据进行过滤

注意事项

二级索引+回表

搜索条件为当前索引某列的，使用二级索引进行定位
搜索条件不涉及当前索引某列的，在回表阶段进行过滤

明确range访问方法使用的区间范围

and 求交集
or 求并集

我们在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换为TRUE就好了

举个栗子

1. 分析WHERE子句中的搜索条件都涉及到了哪些列，哪些列可能使用到索引

这个搜索条件涉及到了key1 , key2 , common_field。
所以可能使用到idx_key1普通二级索引，和idx_key2唯一二级索引

2. 对于那些可能使用到的索引，分析他们的范围区间

假设使用idx_key1索引执行查询

1. 把用不到该索引的搜索条件暂时移除，即替换成TRUE。上诉key2,common_field列，因此替换后为

(key1 > 'xyz' and true) OR
(key1 < 'abc' and key1>'lmn') OR
(true and key1>'zzz' and (true or true ))

再进行化简，最后变成
key1>'zzz'

假设使用idx_key2索引执行查询

过程同上所述

索引合并

什么是索引合并？

在一个查询中使用到多个二级索引，也称为index merge

索引合并的三种算法

Intersection合并

案例

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

合并过程

1. 先从idx_key1二级索引对应的B+树中取出key1='a'的相关记录

2. 从idx_key3二级索引对应的B+树中取出key3='b' 的相关记录

3. 二级索引的记录都是索引列+主键构成的，所以我们可以计算出两个结果集中的id值的交集

4. 按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来给用户

注意事项

Intersection索引合并情况

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

2. 主键列可以是范围匹配

原因：

因为在上诉两种情况下，主键值才是递增的。如果是部分匹配，则主键值不递增。那么取id值的交集就要分两步
一：先将二级索引中的主键排序
二：再以O(n)时间复杂度取id值交集。
因为多了排序过程，优化器会更偏向于使用1个索引，另一个索引的搜索条件则作为回表时的过滤条件

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

Union合并

案例

SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'

合并过程

1. 先从idx_key1二级索引对应的B+树中取出key1='a'的相关记录

2. 从idx_key3二级索引对应的B+树中取出key3='b' 的相关记录

3. 二级索引的记录都是索引列+主键构成的，所以我们可以计算出两个结果集中的id值的并集

4. 按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来给用户

注意事项

Union索引合并情况

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

2. 主键列可以是范围匹配

3. 使用Intersection索引合并的搜索条件（情况1的特殊场景）

原因：

如果不使用Union索引合并，然后又用到了两个索引的过滤条件，并且是OR连接，那就需要扫描。
如果使用Union合并，而两个结果集的id并集比较小，则回表代价也会相对较小，则有可能使用Union合并。

Sort-Union合并

案例

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

合并过程

1. 先从idx_key1二级索引对应的B+树中取出key1='a'的相关记录

2. 从idx_key3二级索引对应的B+树中取出key3='b' 的相关记录

3. 对第一步中的相关记录按主键排序，对第二步中的相关记录按主键排序

4. 二级索引的记录都是索引列+主键构成的，所以我们可以计算出两个结果集中的id值的并集

5. 按照上一步生成的id值列表进行回表操作，也就是从聚簇索引中把指定id值的完整用户记录取出来给用户

为什么没有Sort-Intersection合并

因为使用Intersection合并的情况，是使用1个索引列只能过滤少量记录，而使用2个索引后能过滤大量记录
那如果对1个索引列过滤剩下的大量进行排序，那时间代价也是很高的。因此没有设计这种合并

优化器是否是用索引合并，还是需要根据查询成本来判断的。

索引合并注意事项

使用联合索引替代Intersection索引合并

多表连接