做网站平台的公司有哪些,ios开发网站app,建筑公司网站常用长尾词,店铺首页图片前言
前面介绍了MySQL表空间相关的内容。包括区、段、碎片区#xff0c;还有一些不同的页类型的作用。
#xff08;如果没有看前面五篇文章#xff0c;不建议看此篇文章#xff09; 传送门#xff1a;
MySQL-1、InnoDB行格式
MySQL-2、InnoDB数据页
MySQL-3、索引
M… 前言
前面介绍了MySQL表空间相关的内容。包括区、段、碎片区还有一些不同的页类型的作用。
如果没有看前面五篇文章不建议看此篇文章 传送门
MySQL-1、InnoDB行格式
MySQL-2、InnoDB数据页
MySQL-3、索引
MySQL-4、B树索引的使用
MySQL-5、InnoDB的表空间 访问方法的概念
我们平时写的最多的就是查询语句本质上是一种声明式的语法是MySQL规定的语法。同一个查询语句可以使用多种不同的访问方法来执行虽然最后的结果是一样的但是不同的执行方式所耗费的时间成本差距是很大的。
MySQL把执行查询语句的方式称为访问方法或者访问类型。
const
const翻译过来就是常量意思就是常量级别的访问方法 。
通过主键或者唯一二级索引列来定位一条记录的访问方法就是const级别常量级别代价是可以忽略不计的。不过这种访问方法只能在主键或唯一二级索引列与一个值进行等值比较才有效。因为采用等值查询才可以保证最多只有一条记录符合搜索条件。
由于唯一二级索引列不限制NULL值的数量当进行NULL查询时就可能访问到多条记录也就不会常采用const级别。
ref
通过二级索引列进行等值比较形成的单点扫描区间这种查询的访问方法称为ref。
这里再啰嗦以下采用二级索引来执行查询操作时每获取一条二级索引记录就会立刻对其执行回表操作而不是将所有二级索引记录的主键值都收集起来之后再统一执行回表操作。这里说的回表的前提是要查询的字段数据在聚簇索引里才有
使用二级索引查询还有两点情况
二级索引列允许为NULL时无论是普通二级索引还是唯一二级索引。在执行 二级索引列 IS NULL查询时最多只能使用ref访问方法。如果是联合索引也是二级索引只要最左边连续的列进行等值比较就可以才用ref访问方法。如果最左边的列不全是等值比较的话那就不能用ref。
ref_or_null
ref_or_null从名字上看就是在ref的基础上再判断为NULL的情况。实际上也就是这样。
通过二级索引列进行等值比较形成的单点扫描区间同时还有 OR 二级索引列 IS NULL的条件这种查询的访问方法称为ref_or_null。形成的扫描区间就是 [值,值]和[NULL,NULL]。
ref_or_null访问方法只是比ref访问方法多扫描了一些值为NULL的二级索引记录。
值为NULL的记录会被放在索引的最左边
range
range从名字上也可以看出是范围级别。通过使用索引执行查询时对应的扫描区间为若干个单点扫描区间或者范围是范围扫描区间这种访问方法 称为 range。
这种扫描区间不能叫做 range那就是(-∞,∞)这种是全部扫描。zong
index
查询的列和筛选条件都是索引列不需要回表称为index。也称为索引覆盖。
all
all没有什么可说的就是全表扫描。 索引合并
一般情况下MySQL执行一条sql只会为单个索引生成扫描区间但是也有一些特殊情况。在这些特殊情况下MySQL会为多个索引生成扫描区间。MySQL把这种使用多个索引来完成一次查询 的执行方法称为 索引合并index merge。共有3种特殊情况说特殊情况前先把之前的表结构搬过来。
create table demo_table
(id int not null auto_increment,key1 varchar(50),key2 int,key3 varchar(100),key_part1 varchar(100),key_part2 varchar(100),key_part3 varchar(100),des varchar(255),primary key(id),key idx_key(key1),unique key uk_key2(key2),key idx_key3(key3),key idx_key_part(key_part1,key_part2,key_part3)
)
该表iid为自增长主键key1和key3为普通索引key2为唯一索引key_part1,key_part2,key_part3为联合索引。
1.Intersection 索引合并
现在有下面一条sql
select * from demo_table where key1 a and key3 c;
了解之前B树使用的文章知道会有三种执行方式来减少扫描数据。
第一种全表扫描。。有索引这肯定不用第二种 使用key1的二级索引对应的扫描区间就是[a,a]。然后每获取一条二级索引记录回表查询其他字段数据。最后再判断key3 是否等于 c依次循环。直到找到第一条不符合key1 a的数据为止。第三种使用key3的二级索引对应的扫描区间就是[b,b]。然后每获取一条二级索引记录回表查询其他字段数据。最后再判断key1 是否等于 a依次循环。直到找到第一条不符合key3 c的数据为止。
其实还有一种可能之前我们说过二级索引会根据列值进行排序如果列值相同会根据主键值再排序。上面的sql就符合这种条件。
第四种同时使用key1的二级索引和key3的二级索引。key1索引的扫描区间是[a,a]key3的扫描区间是[b,b]然后在两者的操作结果中找出主键值相同的记录就是共有的主键值。然后根据共有的主键值回表去聚簇索引中查找完整记录。
使用Intersection 索引合并有个必要条件从使用的每个索引中获取到的二级索引记录都是按照主键值排序的。为什么要有这个必要条件呢主要从以下两点
从两个有序的集合中取交集比从两个无序的集合中取交集要容易的多。如果获取到的主键值是有序的则根据主键值进行回表时就不再是进行单纯的随机I/O从而提高效率。
下面大致说下第四种方式的执行过程
假设key1的扫描区间[a,a]其中主键值是 269假设key3的扫描区间[b,b]对应的主键值是 3615。先从key1的扫描区间取出第一条记录该记录主键值为1。然后从key3的扫描区间取出一条记录该记录主键值为3。因为 2 3所以直接丢弃key1中取出的第一条记录。接着继续从key1的扫描区间取出第二条记录该记录主键值为5。然后根据上一步骤中key3的扫描区间取出第一条记录进行比较该记录主键值为3。因为 6 3所以直接丢弃key3中取出的第一条记录。接着从key3的扫描区间取出第二条记录该记录主键值为6。然后根据上一步骤中key1的扫描区间取出第二条记录。因为 6 6也就意味着取交集成功然后进行回表将回表获取到的完整数据发送给客户端。这个客户端不是真正的我们用户而是MySQL的程序最后将所有符合的数据一起返回给用户同理依次执行上面的步骤直到将所有扫描区间遍历完毕。
下面看下不能用Intersection 索引合并的sql
select * from demo_table where key1 a and key_part1 b;
key1的二级索引记录是按照主键值排序的但是key_part1是聚合索引显示按照key_part1排序然后按照key_part排序然后按照key_part3排序。key_part1的扫描区间中的主键值不是有序的。
2.Union索引合并
Intersection 索引合并就是把相同的主键值取交集。有交集就有并集Union索引合并就是取并集。同理我们看下面的sql
select * from demo_table where key1 a or key3 b;
参照上面Intersection 索引合并上面sql也有三种执行方案。分别是使用key1二级索引、key3二级索引、全表扫描。当然也有第四种那就是Union索引合并方案。
可以同时使用key1二级索引和key3二级索引来执行查询在key1二级索引的扫描区间[a,a]在key3二级索引的扫描区间[b,b]。然后根据两个记录主键值的集合去重最后根据去重后的主键值集合回表这样重复的主键值只会回表一次。
同理使用Union索引合并也有一个必要条件从使用的每个索引中获取到的二级索引记录都是按照主键值排序的。原因同上。
3.Sort-Union索引合并
上面两种索引合并的前提条件都是二级索引下的主键值必须是有序的。Sort-Union索引合并就是解决如果是无序的一种情况我们看下面的sql
select * from demo_table where key1 a or key3 y;
这种情况二级索引下的主键值肯定是无序的。但是我们又想使用索引合并那该怎么办呢这就用到了Sort-Union看字面意思就是排序合并。真是的也是这样的先将key1二级索引和key3二级索引查出来后分别对查询的主键值进行排序然后将两个主键值集合进行合并。最后是回表操作。所以与Union索引合并的区别就是合并前会进行一次排序操作。
不知道大家会不会有个疑问为什么没有Sort-Intersection??
原因如下
Union 的目标是合并结果集所以排序后的合并是有效的优化。Intersection 的目标是找到结果集的共同元素重点在于匹配而不是合并顺序。因为排序好了之后还是会继续检索后面的数据是否有交集。Intersection 索引合并有序是为了在回表的时候减少随机I/O。
总结
MySQL的访问方法根据性能排序const ref ref_or_null range index all。
我们怎么知道这条sql的执行方法是const、ref、range、index、还是全表扫描呢。又怎么知道这条sql用到了索引合并用到的具体是哪种索引合并呢。在后面会具体写一篇关于MySQL EXPLAIN用法的文章后面再做介绍。
