https://juejin.im/book/5bffcbc9f265da614b11b731/section/5c061afee51d451ddc06e7aa
单表查询就是 From后面只有一个表名, 最简单的那种
以这个表为例
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 uk_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
# key1/2/3都有索引, 2 是唯一索引,key_part1/2/3 联合索引
根据阿里规范
主键索引名为 pk_ 字段名； 唯一索引名为 uk _字段名 ； 普通索引名则为 idx _字段名。
访问方法/类型: 优化器拿到查询语句以后,决定的查询方式,explain看的执行计划就是解释这个的
const
意思是常数级别
认为是最快的, 最多去二级索引拿到id再回表
select * From single_table where 唯一索引的=常数
ref
和const区别是 等值的列是非唯一索引列
select * From single_table where 非唯一索引的=常数
如果满足的记录比较少,就是需要回表的id比较少, 还是很快的
ref_or_null
和上一个区别是 除了常数才查个null
select * From single_table where 索引列=常数 OR 索引列 IS NULL
range
比如
SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);
有区间的能用到索引的查询
注意
SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';这样不是range , 因为不满足key2 > 100的数据里面仍然有可能满足common_field = 'abc这种情况key2的索引是用不到的, 只能全表扫描
index
在二级索引上扫描,如
SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';
有个key_part1, key_part2, key_part3的联合索引 , 但是查询的条件是key_part2 并不是联合索引的最右
这种情况一定只能扫描全树了, 但是二级索引比聚餐索引的数据少,而且key_part1, key_part2, key_part3联合索引 的树上刚好有要查的数据,还不用回表, 因此直接扫描 key_part1, key_part2, key_part3的联合索引的树比扫描聚簇索引的树划算
只是比全表扫描好一点点, 根据阿里规范,这个index也是禁止的
all
没啥说的,全表扫描
Intersection合并
比如
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
刚好可以
- 1.去key1和key3的树上分别查询 到满足id集合
- 2.两个id集合取交集
- 3.交集拿去回表
分别查询是是顺序IO, 回表是随机IO, 回表要尽量少才行, 因此先取到很小的id集合再去回表比较划算
其实 弄个
key1key3联合索引 不就能用ref(非唯一索引列的等值查询)了嘛
mysql用这种方式查询有必须是这2种情况之一(满足了不一定用):
1.联合索引的时候,联合索引的每个列都必须出现等值条件,
比如这样是可能会用到Intersection合并的
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
-
key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'联合索引的索引列都有等值条件了 -
key1 = 'a'和它配合的索引是等值查询
这个就不行
SELECT * FROM single_table WHERE key1 = 'a' AND key2 = 'a';
因为联合索引少了一个列的出现key3=常量
2.二级索引必须都是等值查询
但是主键列查区间和二级索引的等值查询配合 也有机会用Intersection合并
比如
SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';
这是因为,步骤2 2个id集合取交集, 这个取交集的过程要很快, 就是要id集合都是按大小排好序了只有才可能快, 只有二级索引列=常量的情况,id集合才可能是按大小排序的,
为什么一定id集合不能给先排序再求交集? Intersection索引合并的适用场景是单独根据搜索条件从某个二级索引中获取的记录数太多(就是很多id需要排序咯,代价太大了)，导致回表开销太大，合并后可以明显降低回表开销，
Union合并
和Intersection合并的区别在于, and 改成or
必要条件也差不多,如
SELECT * FROM single_table WHERE key1 = 'a' OR key3 = 'b'
Sort-Union合并
如
SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'
比Union合并多了一步给id集合排序
