MySQL中Cardinality值的介紹

1)         什么是Cardinality

不是所有的查詢(xún)條件出現(xiàn)的列都需要添加索引。對(duì)于什么時(shí)候添加B+樹(shù)索引。一般的經(jīng)驗(yàn)是，在訪問(wèn)表中很少一部分時(shí)使用B+樹(shù)索引才有意義。對(duì)于性別字段、地區(qū)字段、類(lèi)型字段，他們可取值范圍很小，稱(chēng)為低選擇性。如

SELECT * FROM student WHERE sex='M'

按性別進(jìn)行查詢(xún)時(shí)，可取值一般只有M、F。因此SQL語(yǔ)句得到的結(jié)果可能是該表50%的數(shù)據(jù)(加入男女比例1:1)這時(shí)添加B+樹(shù)索引是完全沒(méi)有必要的。相反，如果某個(gè)字段的取值范圍很廣，幾乎沒(méi)有重復(fù)，屬于高選擇性。則此時(shí)使用B+樹(shù)的索引是最合適的。例如對(duì)于姓名字段，基本上在一個(gè)應(yīng)用中不允許重名的出現(xiàn)

怎樣查看索引是否有高選擇性？通過(guò)SHOW INDEX結(jié)果中的列Cardinality來(lái)觀察。非常關(guān)鍵，表示所以中不重復(fù)記錄的預(yù)估值，需要注意的是Cardinality是一個(gè)預(yù)估值，而不是一個(gè)準(zhǔn)確值基本上用戶(hù)也不可能得到一個(gè)準(zhǔn)確的值，在實(shí)際應(yīng)用中，Cardinality/n_row_in_table應(yīng)盡可能的接近1，如果非常小,那用戶(hù)需要考慮是否還有必要?jiǎng)?chuàng)建這個(gè)索引。故在訪問(wèn)高選擇性屬性的字段并從表中取出很少一部分?jǐn)?shù)據(jù)時(shí)，對(duì)于字段添加B+樹(shù)索引是非常有必要的。如

SELECT * FROM member WHERE usernick='David';

表member大約有500W行數(shù)據(jù),usernick字段上有一個(gè)唯一索引。這是如果查找用戶(hù)名為David的用戶(hù)，將得到如下執(zhí)行計(jì)劃

可以看到使用了usernick這個(gè)索引。這也符合之前提到的高可選擇性，即SQL語(yǔ)句取表中較少行的原則

2)         InnoDB存儲(chǔ)引擎的Cardinality統(tǒng)計(jì)

建立索引的前提是高選擇性。這對(duì)數(shù)據(jù)庫(kù)來(lái)說(shuō)才具有實(shí)際意義，那么數(shù)據(jù)庫(kù)是怎樣統(tǒng)計(jì)Cardinality的信息呢?因?yàn)镸ySQL數(shù)據(jù)庫(kù)中有各種不同的存儲(chǔ)引擎，而每種存儲(chǔ)引擎對(duì)于B+樹(shù)索引的實(shí)現(xiàn)又各不相同。所以對(duì)Cardinality統(tǒng)計(jì)時(shí)放在存儲(chǔ)引擎層進(jìn)行的

在生成環(huán)境中，索引的更新操作可能非常頻繁。如果每次索引在發(fā)生操作時(shí)就對(duì)其進(jìn)行Cardinality統(tǒng)計(jì)，那么將會(huì)對(duì)數(shù)據(jù)庫(kù)帶來(lái)很大的負(fù)擔(dān)。另外需要考慮的是，如果一張表的數(shù)據(jù)非常大，如一張表有50G的數(shù)據(jù)，那么統(tǒng)計(jì)一次Cardinality信息所需要的時(shí)間可能非常長(zhǎng)。這樣的環(huán)境下，是不能接受的。因此，數(shù)據(jù)庫(kù)對(duì)于Cardinality信息的統(tǒng)計(jì)都是通過(guò)采樣的方法完成

在InnoDB存儲(chǔ)引擎中，Cardinality統(tǒng)計(jì)信息的更新發(fā)生在兩個(gè)操作中：insert和update。InnoDB存儲(chǔ)引擎內(nèi)部對(duì)更新Cardinality信息的策略為:

表中1/16的數(shù)據(jù)已發(fā)生了改變

stat_modified_counter>2000 000 000

第一種策略為自從上次統(tǒng)計(jì)Cardinality信息后，表中的1/16的數(shù)據(jù)已經(jīng)發(fā)生過(guò)變化，這是需要更新Cardinality信息

第二種情況考慮的是，如果對(duì)表中某一行數(shù)據(jù)頻繁地進(jìn)行更新操作，這時(shí)表中的數(shù)據(jù)實(shí)際并沒(méi)有增加，實(shí)際發(fā)生變化的還是這一行數(shù)據(jù)，則第一種更新策略就無(wú)法適用這種情況，故在InnoDB存儲(chǔ)引擎內(nèi)部有一個(gè)計(jì)數(shù)器start_modified_counter,用來(lái)表示發(fā)生變化的次數(shù),當(dāng)start_modified_counter>2 000 000 000 時(shí)，則同樣更新Cardinality信息

接著考慮InnoDB存儲(chǔ)引擎內(nèi)部是怎樣進(jìn)行Cardinality信息統(tǒng)計(jì)和更新操作呢？同樣是通過(guò)采樣的方法。默認(rèn)的InnoDB存儲(chǔ)引擎對(duì)8個(gè)葉子節(jié)點(diǎn)Leaf Page進(jìn)行采用。采用過(guò)程如下

取得B+樹(shù)索引中葉子節(jié)點(diǎn)的數(shù)量，記為A

隨機(jī)取得B+樹(shù)索引中的8個(gè)葉子節(jié)點(diǎn)，統(tǒng)計(jì)每個(gè)頁(yè)不同記錄的個(gè)數(shù)，即為P1，P2....P8

通過(guò)采樣信息給出Cardinality的預(yù)估值:Cardinality=(P1+P2+...+P8)*A/8

根據(jù)上述的說(shuō)明可以發(fā)現(xiàn)，在InnoDB存儲(chǔ)引擎中，Cardinality值通過(guò)對(duì)8個(gè)葉子節(jié)點(diǎn)預(yù)估而得的。而不是一個(gè)實(shí)際精確的值。再者，每次對(duì)Cardinality值的統(tǒng)計(jì)，都是通過(guò)隨機(jī)取8個(gè)葉子節(jié)點(diǎn)得到的，這同時(shí)有暗示了另外一個(gè)Cardinality現(xiàn)象，即每次得到的Cardinality值可能不同的，如

SHOW INDEX FROM OrderDetails

上述SQL語(yǔ)句會(huì)觸發(fā)MySQL數(shù)據(jù)庫(kù)對(duì)于Cardinality值的統(tǒng)計(jì)，第一次運(yùn)行得到的結(jié)果如圖5-20

在上述測(cè)試過(guò)程中，并沒(méi)有通過(guò)INSERT、UPDATE這類(lèi)的操作來(lái)改變OrderDetails中的內(nèi)容，但是當(dāng)?shù)诙芜\(yùn)行SHOW INDEX FROM OrderDetails語(yǔ)句是，發(fā)生了變化，如圖5-21

可以看到，當(dāng)?shù)诙芜\(yùn)行SHOW INDEX FROM OrderDetails語(yǔ)句時(shí)，表OrderDetails索引中的Cardinality值發(fā)生了變化，雖然表OrderDetails本身并沒(méi)有發(fā)生任何變化，但是由于Cardinality是隨機(jī)取8個(gè)葉子節(jié)點(diǎn)進(jìn)行分析，所以即使表沒(méi)有發(fā)生變化，用戶(hù)觀察到索引Cardinality值還是會(huì)發(fā)生變化，這本身不是Bug,而是隨機(jī)采樣而導(dǎo)致的結(jié)果

當(dāng)然，有一種情況可以使得用戶(hù)每次觀察到的索引Cardinality值是一樣的。那就是表足夠小，表的葉子節(jié)點(diǎn)樹(shù)小于或者等于8個(gè)。這時(shí)即使隨機(jī)采樣，也總是會(huì)采取倒這些頁(yè)，因此每次得到的Cardinality值是相同的

在InnoDB1.2版本之前，可以通過(guò)innodb_stats_sample_pages用來(lái)設(shè)置統(tǒng)計(jì)Cardinality時(shí)每次采樣頁(yè)的數(shù)量，默認(rèn)為8.同時(shí)，參數(shù)innodb_stats_method用來(lái)判斷如何對(duì)待索引中出現(xiàn)NULL值記錄。該參數(shù)默認(rèn)值為nulls_equal,表示將NULL值記錄為相等的記錄。其有效值還nulls_unequal,nulls_ignored,分別表示將NULL值記錄視為不同的記錄和忽略NULL值記錄。例如某夜中索引記錄為NULL、NULL、1、2、2、3、3、3,在參數(shù)innodb_stats_method默認(rèn)設(shè)置下，該頁(yè)的Cardinality為4；若參數(shù)innodb_stats_method為nulls_unequal,則該頁(yè)的Cardinality為5，若參數(shù)innodb_stats_method為nulls_ignored，則Cardinality值為3

當(dāng)執(zhí)行ANALYZE TABLE、SHOW TABLE STATUS、SHOW INDEX 以及訪問(wèn)INFORMATION_SCHEMA架構(gòu)下的表TABLES和STATISTICS時(shí)會(huì)導(dǎo)致InnoDB存儲(chǔ)引擎會(huì)重新計(jì)算索引Cardinality值，若表中的數(shù)據(jù)量非常大，并且表中存在多個(gè)輔助索引時(shí)，執(zhí)行上述操作可能會(huì)非常慢，雖然用戶(hù)可能并不希望去更新Cardinality值

InnoDB1.2版本提供了更多參數(shù)對(duì)Cardinality進(jìn)行設(shè)置。如表

原文地址：http://blog.csdn.net/alongken2005/article/details/6394016

Analyze TableMySQL 的Optimizer（優(yōu)化元件）在優(yōu)化SQL語(yǔ)句時(shí)，首先需要收集一些相關(guān)信息，其中就包括表的cardinality（可以翻譯為“散列程度”），它表示某個(gè)索引對(duì)應(yīng)的列包含多少個(gè)不同的值——如果cardinality大大少于數(shù)據(jù)的實(shí)際散列程度，那么索引就基本失效了。我們可以使用SHOW INDEX語(yǔ)句來(lái)查看索引的散列程度：SHOW INDEX FROM PLAYERS;TABLE   KEY_NAME COLUMN_NAME CARDINALITY------- -------- ----------- -----------PLAYERS PRIMARY PLAYERNO             14因?yàn)榇藭r(shí)PLAYER表中不同的PLAYERNO數(shù)量遠(yuǎn)遠(yuǎn)多于14，索引基本失效。下面我們通過(guò)Analyze Table語(yǔ)句來(lái)修復(fù)索引：ANALYZE TABLE PLAYERS;SHOW INDEX FROM PLAYERS;結(jié)果是：TABLE   KEY_NAME COLUMN_NAME CARDINALITY------- -------- ----------- -----------PLAYERS PRIMARY PLAYERNO           1000此時(shí)索引已經(jīng)修復(fù)，查詢(xún)效率大大提高。需要注意的是，如果開(kāi)啟了binlog，那么Analyze Table的結(jié)果也會(huì)寫(xiě)入binlog，我們可以在analyze和table之間添加關(guān)鍵字local取消寫(xiě)入。