1 使用存儲(chǔ)過(guò)程生成假數(shù)據(jù)
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
2 會(huì)走索引 select * from t where a between 10000 and 20000;
3 一個(gè) mysql 選錯(cuò)索引的案例
4 slow log 可以查看具體執(zhí)行情況
5 force index(a)可以強(qiáng)制使用索引
6 set long_query_time=0; 所有語(yǔ)句都會(huì)被記錄到 slow log
7 slow log 結(jié)果
8 如何查看 slow log
9 對(duì)應(yīng)的是平常 “不斷刪除歷史數(shù)據(jù)和新增數(shù)據(jù)”的場(chǎng)景 , 此時(shí)會(huì)選錯(cuò)索引
10 優(yōu)化器的目的 , 最小的代價(jià)執(zhí)行語(yǔ)句 , 最小代價(jià) : 掃描行數(shù)少 , 則 IO 少,CPU 消耗少
11 優(yōu)化器其他判斷準(zhǔn)則 , 是否使用臨時(shí)表 , 是否需要排序
12 執(zhí)行語(yǔ)句之前優(yōu)化器并不能精確判斷掃描行數(shù)
13 通過(guò)使用”區(qū)分度”進(jìn)行預(yù)估
14 區(qū)分度就是索引上的cardinality(基數(shù)) , 越大區(qū)分度越好
15 cardinality是通過(guò)采樣統(tǒng)計(jì)取值的 , 并不精確
16 采樣統(tǒng)計(jì)的方法 : 取n個(gè)數(shù)據(jù)頁(yè) , 取不同值數(shù)的平均值 , 乘以數(shù)據(jù)頁(yè)數(shù)量
17 當(dāng)變更的數(shù)據(jù)超過(guò)1/M的時(shí)候會(huì)重新采樣統(tǒng)計(jì)
18 采樣統(tǒng)計(jì)可存在磁盤中或只存在內(nèi)存中
19 通過(guò)innodb_stats_persistent修改 , on 是持久化,N是20，M是10 , off 是內(nèi)存中,N是8，M是16
20 采樣統(tǒng)計(jì)的值還是很容易不準(zhǔn)的 , 但大體上依然是差不多的
21 上面那個(gè)案例的兩個(gè)語(yǔ)句的預(yù)估行數(shù)
22 Q1 語(yǔ)句的預(yù)估行數(shù)是接近的 , 但是 Q2 的預(yù)估行數(shù)是錯(cuò)誤的
23 此時(shí)由于優(yōu)化器還需要考慮回表去掃描的代價(jià) , 并且認(rèn)為直接掃描主鍵索引更快 , 因此當(dāng)不 force index 的時(shí)候會(huì)用全表掃描 , 但顯然從執(zhí)行時(shí)間來(lái)看不是最快的
23 但是主要的問(wèn)題還是錯(cuò)誤的預(yù)估行數(shù)造成的 , 圖 1 就沒問(wèn)題啊
24 analyze table t , 重建 統(tǒng)計(jì)
25 另一個(gè)案例 , 說(shuō)明優(yōu)化器不只通過(guò)預(yù)估行數(shù)來(lái)分析
26 select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
27 幾種解決方案 , 1 修改語(yǔ)義 2 刪除不必要的索引 3 force index
28 思考題 : 為什么單獨(dú)delete的話預(yù)估行數(shù)正常 , 但session a沒提交的時(shí)候則不正常 ? 因?yàn)閟ession a沒提交 , 數(shù)據(jù)是不能刪除的 , 相當(dāng)于有兩份數(shù)據(jù)
