MySQL統(tǒng)計(jì)信息以及執(zhí)行計(jì)劃預(yù)估方式初探
數(shù)據(jù)庫(kù)中的統(tǒng)計(jì)信息在不同（精確）程度上描述了表中數(shù)據(jù)的分布情況，執(zhí)行計(jì)劃通過(guò)統(tǒng)計(jì)信息獲取符合查詢條件的數(shù)據(jù)大小（行數(shù)），來(lái)指導(dǎo)執(zhí)行計(jì)劃的生成。
在以O(shè)racle和SQLServer為代表的商業(yè)數(shù)據(jù)庫(kù)，和以開源的PostgreSQL為代表的數(shù)據(jù)庫(kù)中，直方圖是統(tǒng)計(jì)信息的一個(gè)重要組成部分。
在生成執(zhí)行計(jì)劃的時(shí)候，通過(guò)統(tǒng)計(jì)信息以及統(tǒng)計(jì)信息的直方圖來(lái)預(yù)估符合條件的數(shù)據(jù)行數(shù)，從而影響執(zhí)行計(jì)劃的生成。
統(tǒng)計(jì)信息對(duì)執(zhí)行計(jì)劃的影響，具體體現(xiàn)在：索引的查找與掃描，多表連接時(shí)表之間的驅(qū)動(dòng)順序，表之間的JOIN方式，以及對(duì)sql查詢語(yǔ)句的資源分配等等。
但是在MySQL數(shù)據(jù)庫(kù)中，執(zhí)行計(jì)劃的方式相對(duì)簡(jiǎn)單，表之間的JOIN只有LOOPJOIN一種方式，且沒(méi)有并行執(zhí)行計(jì)劃等，也就說(shuō)通過(guò)預(yù)估結(jié)果集的行數(shù)對(duì)執(zhí)行計(jì)劃的影響有限。
但是對(duì)于某些情況，依舊需要預(yù)估的方式來(lái)指導(dǎo)執(zhí)行計(jì)劃的生成，比如常見的多表連接時(shí)驅(qū)動(dòng)順序，多數(shù)情況下是小表驅(qū)動(dòng)大表（不完全一定）的方式來(lái)實(shí)現(xiàn)查詢的，因此MySQL中一樣需要預(yù)估來(lái)指導(dǎo)執(zhí)行計(jì)劃的生成。
不過(guò)MySQL中的統(tǒng)計(jì)信息相對(duì)來(lái)說(shuō)簡(jiǎn)單很多，只有一個(gè)cardinality信息來(lái)預(yù)估索引的選擇性（show index from table），索引統(tǒng)計(jì)信息不包含直方圖的信息，非索引列也不會(huì)生成直方圖，也就是無(wú)法通過(guò)直方圖來(lái)預(yù)估查詢數(shù)據(jù)的大小，mysql是通過(guò)其他方式來(lái)實(shí)現(xiàn)預(yù)估的。
對(duì)于有直方圖的數(shù)據(jù)來(lái)說(shuō)，直方圖為預(yù)估提供了重要的依據(jù)，對(duì)于沒(méi)有直方圖的MySQL，執(zhí)行計(jì)劃是如何預(yù)估的？預(yù)估的準(zhǔn)確性有如何？
筆者在研究這個(gè)問(wèn)題的時(shí)候，一開始也遇到不少疑惑的地方，還是看了博客園大神的問(wèn)題才得以釋惑，后面會(huì)給出鏈接。
首先通過(guò)例子，通過(guò)一個(gè)非常簡(jiǎn)單的查詢來(lái)觀察一個(gè)有意思的現(xiàn)象。
新建測(cè)試表，測(cè)試表如下：
- create table test_statistics
- (
- id int auto_increment primary key,
- col2 varchar(200),
- col3 varchar(200),
- create_date datetime,
- index idx_create_date(create_date)
- )ENGINE=InnoDB;
存儲(chǔ)過(guò)程通過(guò)循環(huán)插入數(shù)據(jù)，調(diào)用存儲(chǔ)過(guò)程生成100W行數(shù)據(jù)（100W行的數(shù)據(jù)，在實(shí)際應(yīng)用中已經(jīng)是一個(gè)非常小的數(shù)據(jù)量了），create_date字段上生成一個(gè)范圍之內(nèi)的隨機(jī)時(shí)間。
- CREATE DEFINER=`root`@`%` PROCEDURE `p_insert_test_data`(
- IN `loop_count` INT
- )
- BEGIN
- declare i int;
- while (loop_count>0)
- do
- insert into test_statistics(col2,col3,create_date) values (uuid(),uuid(), DATE_ADD(sysdate(), INTERVAL -rand()*2400 hour));
- set loop_count = loop_count -1;
- end while;
- END
寫入測(cè)試數(shù)據(jù)完成之后，進(jìn)行如下兩個(gè)查詢做測(cè)試。
簡(jiǎn)單地使用select count(1)的來(lái)做測(cè)試
首先看***個(gè)查詢：查詢的時(shí)間范圍是: where create_date>’2017-11-01 12:00:00′ and create_date<’2017-11-01 16:00:00′
可以發(fā)現(xiàn)：explain預(yù)估的行數(shù)，與實(shí)際行數(shù)完全一致。
繼續(xù)第二個(gè)查詢，擴(kuò)大查詢的時(shí)間范圍，查詢的時(shí)間范圍是:where create_date>’2017-11-01 12:00:00′ and create_date<’2017-11-03 16:00:00′
可以發(fā)現(xiàn)，此時(shí)的explain執(zhí)行計(jì)劃的預(yù)估，與實(shí)際行數(shù)出現(xiàn)了嚴(yán)重的偏差
為什么***個(gè)查詢做到了精確的預(yù)估，而第二個(gè)查詢的預(yù)估出現(xiàn)嚴(yán)重的偏差？
這一點(diǎn)要從預(yù)估的計(jì)算方式入手來(lái)說(shuō)。
首先，***個(gè)查詢和第二個(gè)查詢，唯一的不同是，第二個(gè)查詢的時(shí)間范圍放寬了，為什么時(shí)間放寬之后，執(zhí)行計(jì)劃的預(yù)估的準(zhǔn)確性就大大下降？
既然是“預(yù)估”，就一定是存在誤差，只不過(guò)是誤差大與小的問(wèn)題，誤差的大下與具體的預(yù)估的方式有關(guān)。
任何預(yù)估的實(shí)現(xiàn)，都是以一種在不同程度上“以偏概全”的方式進(jìn)行的，比如SQL Server是以對(duì)相關(guān)數(shù)據(jù)page的通過(guò)某種百分比來(lái)取樣，然后存儲(chǔ)在直方圖中做預(yù)估依據(jù)的。
當(dāng)然，這種“以偏概全”的預(yù)估方式，是在性能與精確度之間權(quán)衡折中的結(jié)果。
在考慮收集統(tǒng)計(jì)信息對(duì)性能和資源影響的前提下，預(yù)估策略各種方式或者代價(jià)盡可能減少對(duì)預(yù)估產(chǎn)生誤差的因素，關(guān)于直方圖的生成這里不細(xì)說(shuō)。
對(duì)于沒(méi)有直方圖的MySQL，它是是在執(zhí)行的時(shí)候，通過(guò)掃描符合查詢條件的部分?jǐn)?shù)據(jù)頁(yè)后做預(yù)估統(tǒng)計(jì)的。
MySQL是在查詢的時(shí)候，直接對(duì)查詢條件范圍內(nèi)的數(shù)據(jù)頁(yè)，取一定比例樣本做統(tǒng)計(jì)之后預(yù)估的，但是這里取樣的數(shù)據(jù)頁(yè)面有一定的限制，不會(huì)***制取樣做統(tǒng)計(jì)預(yù)估。
如果符合條件的數(shù)據(jù)頁(yè)超出了預(yù)定的范圍，則會(huì)取部分頁(yè)進(jìn)行預(yù)估，而不是全部頁(yè)（為什么不是全部樣做統(tǒng)計(jì)預(yù)估，原因就不用說(shuō)了吧）。
比如下圖中，不管是聚集索引還是二級(jí)索引（非聚集索引），理論上說(shuō)都是一顆平衡樹，暫不探究其細(xì)節(jié)。
假如符合條件的數(shù)據(jù)是一個(gè)范圍，位于兩個(gè)矩形框之間。矩形框分別是范圍的左右節(jié)點(diǎn)，中間可以想象成多個(gè)葉子節(jié)點(diǎn)
參考zhanlijun大神的文章，
https://www.cnblogs.com/LBSer/p/3333881.html
上述參考鏈接中得知，MySQL在5.5之后的預(yù)估原理如下：
其預(yù)估掃描的數(shù)據(jù)頁(yè)分別是前后兩個(gè)數(shù)據(jù)頁(yè)，以及從左邊開始連續(xù)8個(gè)數(shù)據(jù)頁(yè)，得到平均每個(gè)page的行數(shù)，根據(jù)總的page個(gè)數(shù)預(yù)估出這個(gè)范圍的數(shù)據(jù)行數(shù)。
具體說(shuō)，也就是取左右兩個(gè)葉子節(jié)點(diǎn)，以及從左葉子節(jié)點(diǎn)開始連續(xù)8個(gè)頁(yè)的數(shù)據(jù)做統(tǒng)計(jì)，中間可能有多個(gè)數(shù)據(jù)頁(yè)，但也會(huì)被忽略，這就是上面提到的“以偏概全”的方式。
這里面就存在一個(gè)最明顯的問(wèn)題，也就是符合條件的數(shù)據(jù)頁(yè)面與預(yù)估時(shí)候采集的頁(yè)面的大小關(guān)系。
如果符合條件的數(shù)據(jù)頁(yè)的分布少于10個(gè)，當(dāng)然在預(yù)估的時(shí)候，會(huì)全部掃描這些page，當(dāng)然預(yù)估是完全精確的，這也是***個(gè)查詢執(zhí)行計(jì)劃預(yù)估的實(shí)際行數(shù)完全不一致的原因。
如果符合條件的數(shù)據(jù)頁(yè)的分布大于10個(gè)，當(dāng)然在預(yù)估的時(shí)候，會(huì)部分掃描這些page，預(yù)估的誤差情況就此產(chǎn)生，這也是第二個(gè)查詢執(zhí)行計(jì)劃預(yù)估的實(shí)際行數(shù)差異較大的原因。
當(dāng)然MySQL的每個(gè)版本可能都有所改進(jìn)或者差異，筆者并沒(méi)有從源碼中找到具體的算法，當(dāng)前測(cè)試的是5.7.20版本。
但目前仍不清楚，
- 在create_date字段上，時(shí)間是按照DATE_ADD(sysdate(), INTERVAL -rand()*2400 hour)生成的，從整體分布看，基本按照時(shí)間均勻分布的.
- 理論上根據(jù)這種方式推到，得到的預(yù)估結(jié)果偏差應(yīng)該不會(huì)很大，但尚不清楚為什么預(yù)估與實(shí)際存在如此大的差異。
- 嘗試找到預(yù)估值從精確到產(chǎn)生差異的臨界點(diǎn)，通過(guò)查詢實(shí)際行數(shù)，根據(jù)key_len的值以及B樹索引的存儲(chǔ)原理（二級(jí)索引葉子節(jié)點(diǎn)存儲(chǔ)的二級(jí)索引的key值+聚集索引的key值）.
理論上計(jì)算出來(lái)當(dāng)前查詢一個(gè)大概的取樣的page個(gè)數(shù)，發(fā)現(xiàn)這個(gè)值預(yù)報(bào)理論上的10個(gè)page差異較大，可能是推到方式有問(wèn)題，或者是MySQL預(yù)估本身有一些不知道的細(xì)節(jié)問(wèn)題。
沒(méi)有詳細(xì)翻MySQL的源碼，尚未找到具體的實(shí)現(xiàn)細(xì)節(jié)。
對(duì)于有直方圖的數(shù)據(jù)庫(kù)來(lái)說(shuō)，直方圖的信息也不是沒(méi)有代價(jià)，或者是***的，直方圖也有直方圖的局限性，這里暫不表述。
對(duì)于尚沒(méi)有直方圖的MySQL數(shù)據(jù)庫(kù)來(lái)說(shuō)，其預(yù)估原理是每次查詢的時(shí)候進(jìn)行對(duì)相關(guān)的數(shù)據(jù)頁(yè)面進(jìn)行采樣預(yù)估的，而不是從直方圖中獲取到預(yù)估信息的，這是一個(gè)很消耗性能的操作。
詳情參考：
http://www.orczhou.com/index.php/2013/04/how-mysql-choose-index-in-a-join/
這可能會(huì)導(dǎo)致MySQL不適合做較大數(shù)據(jù)量或者較為復(fù)雜的JOIN操作，當(dāng)然這也取決于具體的業(yè)務(wù)設(shè)計(jì)方案以及對(duì)數(shù)據(jù)的依賴程度，或者主觀上的查詢提示操作。
說(shuō)這句話是冒著被MySQL的大神以及粉絲們怒噴的風(fēng)險(xiǎn)的。
關(guān)于MySQL的預(yù)估的知識(shí)點(diǎn)，搜索到的文章并不是很多，也拘泥于個(gè)人的認(rèn)識(shí)有限，也希望對(duì)這方面有關(guān)注的大神多多指點(diǎn)。
據(jù)說(shuō)MySQL在8.0之后的版本中會(huì)加入直方圖信息，以及其他JOIN方式（除了LOOP JOIN），這可能對(duì)性能上有比較大的幫助。
參考鏈接
- https://www.cnblogs.com/LBSer/p/3333881.html
- http://www.orczhou.com/index.php/2013/04/how-mysql-choose-index-in-a-join/
