淺析MySQL的基數(shù)統(tǒng)計(jì)

更新時(shí)間：2020年11月27日 08:37:11 作者：賜我白日夢(mèng)

這篇文章主要介紹了MySQL的基數(shù)統(tǒng)計(jì)的相關(guān)資料，幫助大家更好的理解和學(xué)習(xí)MySQL數(shù)據(jù)庫(kù)的使用，感興趣的朋友可以了解下

一、基數(shù)是啥？
Cardinality指的就是MySQL表中某一列的不同值的數(shù)量。

如果這一類(lèi)是唯一索引，那基數(shù) = 行數(shù)。

如果這一列是sex，枚舉類(lèi)型只有男女，那它是基數(shù)就是2

Cardinality越高，列就越有成為索引的價(jià)值。MySQL執(zhí)行計(jì)劃也會(huì)基于Cardinality選擇索引。

通過(guò)下面的方式可以看到表中各列的基數(shù)。

比如這個(gè)經(jīng)典的例子：
有一列為sex，那對(duì)于sex列中存儲(chǔ)的值來(lái)說(shuō) 非男即女，它的基數(shù)最大就是2。
那也就完全沒(méi)有必要為sex建立索引。因?yàn)?，為了提升你基于sex的查詢速度，MySQL會(huì)為你選擇的這個(gè)新索引創(chuàng)建一棵全新的B+Tree。但你sex只有兩種值，對(duì)于MySQL來(lái)說(shuō)，即使它為你指定的列建立了B+Tree索引，真正執(zhí)行查詢時(shí)，最多進(jìn)行一次二分查詢，剩下的操作只能是遍歷，所以為sex創(chuàng)建索引意義不大。

二、InnoDB更新基數(shù)的時(shí)機(jī)？
參數(shù)：innodb_stats_auto_recalc控制MySQL是否主動(dòng)重新計(jì)算這些持久性的信息。默認(rèn)為1表示true，0表示false。
默認(rèn)情況下當(dāng)表中的行變化超過(guò)10%時(shí)，重新計(jì)算基數(shù)信息。

三、基數(shù)是估算出來(lái)

基數(shù)并不會(huì)實(shí)時(shí)更新！而且它是通過(guò)采樣估算出來(lái)的值！

至于基數(shù)的公式是怎樣的，可能并不重要。

重要的是你得知道，他是通過(guò)隨機(jī)采樣數(shù)據(jù)頁(yè)的方式統(tǒng)計(jì)出來(lái)的一個(gè)估算值。

而且隨機(jī)采樣的頁(yè)數(shù)可以通過(guò)參數(shù)innodb_stats_persistent_sample_pages 設(shè)置，默認(rèn)值是20。

這就意味著基數(shù)值并不準(zhǔn)確，甚至你每次計(jì)算的結(jié)果相擦還是蠻大的。

四、持久化基數(shù)

可以通過(guò)參數(shù)innodb_stats_persistent 控制是否持久化基數(shù)，默認(rèn)為off。

當(dāng)然你可以為一個(gè)單獨(dú)的表設(shè)置 STATS_PERSISTENT=1 那么它的 innodb_stats_persistent將自動(dòng)被啟用。

開(kāi)啟它的好處是：重啟MySQL不會(huì)再重復(fù)計(jì)算這個(gè)值，加快重啟速度。

五、如何主動(dòng)更新基數(shù)？

執(zhí)行下面的SQL時(shí)都會(huì)觸發(fā)InnoDB更新基數(shù)（即使你并沒(méi)有意識(shí)到它會(huì)更新基數(shù)）。

所以盡量選擇一個(gè)業(yè)務(wù)低峰期

analyze table tableName;

如果因?yàn)椴蓸拥臄?shù)量太少了，計(jì)算的基數(shù)錯(cuò)的離譜。那很可能會(huì)導(dǎo)致MySQL的優(yōu)化器選錯(cuò)索引。這是你可以將這個(gè)值適當(dāng)調(diào)大。但是增加太多可能會(huì)導(dǎo)致 ANALYZE TABLE運(yùn)行緩慢。

反之， ANALYZE TABLE運(yùn)行太慢。你可以適度調(diào)整參數(shù)innodb_stats_persistent_sample_pages 的值。但是這又可能導(dǎo)致基數(shù)計(jì)算的不準(zhǔn)確。