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 聯(lián)合索引
根據(jù)阿里規(guī)范
主鍵索引名為 pk_ 字段名腐碱； 唯一索引名為 uk _字段名 趁冈； 普通索引名則為 idx _字段名。
訪問方法/類型: 優(yōu)化器拿到查詢語句以后,決定的查詢方式,explain看的執(zhí)行計劃就是解釋這個的
const
意思是常數(shù)級別
認(rèn)為是最快的, 最多去二級索引拿到id再回表
select * From single_table where 唯一索引的=常數(shù)
ref
和const區(qū)別是 等值的列是非唯一索引列
select * From single_table where 非唯一索引的=常數(shù)
如果滿足的記錄比較少,就是需要回表的id比較少, 還是很快的
ref_or_null
和上一個區(qū)別是 除了常數(shù)才查個null
select * From single_table where 索引列=常數(shù) OR 索引列 IS NULL
range
比如
SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);
有區(qū)間的能用到索引的查詢
注意
SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';這樣不是range , 因為不滿足key2 > 100的數(shù)據(jù)里面仍然有可能滿足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的聯(lián)合索引 , 但是查詢的條件是key_part2 并不是聯(lián)合索引的最右
這種情況一定只能掃描全樹了, 但是二級索引比聚餐索引的數(shù)據(jù)少,而且key_part1, key_part2, key_part3聯(lián)合索引 的樹上剛好有要查的數(shù)據(jù),還不用回表, 因此直接掃描 key_part1, key_part2, key_part3的聯(lián)合索引的樹比掃描聚簇索引的樹劃算
只是比全表掃描好一點點, 根據(jù)阿里規(guī)范,這個index也是禁止的
all
沒啥說的,全表掃描
Intersection合并
比如
SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';
剛好可以
- 1.去key1和key3的樹上分別查詢 到滿足id集合
- 2.兩個id集合取交集
- 3.交集拿去回表
分別查詢是是順序IO, 回表是隨機IO, 回表要盡量少才行, 因此先取到很小的id集合再去回表比較劃算
其實 弄個
key1key3聯(lián)合索引 不就能用ref(非唯一索引列的等值查詢)了嘛
mysql用這種方式查詢有必須是這2種情況之一(滿足了不一定用):
1.聯(lián)合索引的時候,聯(lián)合索引的每個列都必須出現(xiàn)等值條件,
比如這樣是可能會用到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'聯(lián)合索引的索引列都有等值條件了 -
key1 = 'a'和它配合的索引是等值查詢
這個就不行
SELECT * FROM single_table WHERE key1 = 'a' AND key2 = 'a';
因為聯(lián)合索引少了一個列的出現(xiàn)key3=常量
2.二級索引必須都是等值查詢
但是主鍵列查區(qū)間和二級索引的等值查詢配合 也有機會用Intersection合并
比如
SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';
這是因為,步驟2 2個id集合取交集, 這個取交集的過程要很快, 就是要id集合都是按大小排好序了只有才可能快, 只有二級索引列=常量的情況,id集合才可能是按大小排序的,
為什么一定id集合不能給先排序再求交集? Intersection索引合并的適用場景是單獨根據(jù)搜索條件從某個二級索引中獲取的記錄數(shù)太多(就是很多id需要排序咯,代價太大了)铃慷，導(dǎo)致回表開銷太大，合并后可以明顯降低回表開銷蜕该，
Union合并
和Intersection合并的區(qū)別在于, 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集合排序
