執(zhí)行計劃:成本最低的方案
possible key: 查詢中可能用到的索引
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;
單表查詢
查詢例句:
SELECT * FROM single_table WHERE
key1 IN ('a', 'b', 'c') // idx_key1
AND
key2 > 10 AND key2 < 1000// uk_key2
AND
key3 > key2 // 不可能用到索引
AND
key_part1 LIKE '%hello%' //不可能用到索引
AND
common_field = '123';//不可能用到索引
possible key:idx_key1,uk_key2
查詢成本 有2種,
I/O成本:加載到內(nèi)存的成功
CPU成本:讀取,檢測記錄是否滿足,對結(jié)果排序
mysql為每個表維護了統(tǒng)計信息, 有 Rows(大概幾行) Data_length(基礎索引多大,單位B)
一頁16k,因此能算出聚簇索引有幾頁
這里假設97頁,9693行
現(xiàn)在計算比較上一個查詢成本:
-
全表掃描成本 估算
I/O成本: 97(總頁數(shù))*1(I/O成本參數(shù)默認,可調(diào))+1.1(微調(diào)值不用管)
CPU成本: 9693(行)* 0.2(成本常數(shù))+1.0(微調(diào)值)
雖然,聚簇索引只有最下層的葉子節(jié)點是真的放著數(shù)據(jù), 只要沿著最下層的雙向鏈表 從左往右就能遍歷了, 實際是不用遍歷內(nèi)節(jié)點的, 這里的估算比較粗暴, 但是就是這么估的
-
用唯一索引 uk_key2 的成本估算
key2 > 10 AND key2 < 1000
1.把這個區(qū)間的二級索引全部讀入磁盤, 的I/O成本 是1
就是查(10,1000)區(qū)間, 估算時 查詢優(yōu)化器認為, 一個區(qū)間的I/O成本 就是讀取一頁的成本
2.從中拿到主鍵id, 查看一條成本0.2
首先要沽出(10,1000)區(qū)間大概有幾條記錄(肯定少于1000-10),如果最左和最右相隔不遠,就直接得出精確值,不然的話 平均每個頁幾條(統(tǒng)計左邊10頁) * 左右間有幾頁(往上找共同父節(jié)點)
注意: 這樣直接去訪問樹, 來估算條數(shù),叫
index dive
是真的去訪問了, 這樣這個得到估算值的本身的動作,就會產(chǎn)生成本
為了不讓這個為了估算產(chǎn)生的成本太大, 如果 in(參數(shù)) 參數(shù)多于200就不用index dive, 而是直接:
Rows/Cardinality來估算條數(shù)
Rows: 統(tǒng)計的大概行數(shù)
Cardinality:基數(shù),表示重復程度的,1是完全相同,和行數(shù)相等是每行都不相等
這種估算代價小,但是不怎么精確
這里假設是95
95 * 0.2 +0.01=19.01
3.憑著符合key2 > 10 AND key2 < 1000 的id 去聚簇索引拿到完整一條的記錄(忽略不計), 主要是 查看對其他查詢條件的滿足(key1 IN ('a', 'b', 'c'), key3 > key2,key_part1 LIKE '%hello%' ,common_field = '123' 這些)
95*0.2
-
普通二級索引 idx_key1 的成本估算
key1 IN ('a', 'b', 'c')
- 把3個單點區(qū)間讀入磁盤, 還是和上文說的一樣,一個區(qū)間當一頁算
3*1 - 同上, 查看1讀入的數(shù)據(jù)拿到id, 需要先估滿足條件的有幾條(不是三條哦.key1不限制值不能相同), 和上文一樣估, 這里即使一共118條件
118*2+0.01
3.同上, 用id回表(忽略不計), 查看完整的記錄,應用其他篩選條件
118*0.2=23.6
這里明顯不能 Index Merge (idx_key1,uk_key2 并行查詢?nèi)缓蠛喜?), 因為二級索引是等值查詢 是必要條件 (因為取交集必須是id是有序的取交集才快)
連接查詢的成本
成本= 訪問驅(qū)動表+ 驅(qū)動表扇出數(shù)* 單詞訪問被驅(qū)動表成本
扇出數(shù)的計算 叫 Condition filtering
主要靠猜, 沒有篩選條件, 直接用驅(qū)動表的統(tǒng)計的大概Rows, key2 > 10 AND key2 < 1000 和上文說的一樣 index dive
外連接的最優(yōu)查詢方案: 分別選擇最優(yōu)訪問方法
外連接的最優(yōu)查詢方案:還要選擇驅(qū)動順序
