具體細(xì)節(jié) 請去掘金購買《MySQL 是怎樣運(yùn)行的：從根兒上理解 MySQL》

InnoDB 統(tǒng)計(jì)數(shù)據(jù)是如何收集的

統(tǒng)計(jì)數(shù)據(jù)的查看

• 1.通過SHOW TABLE STATUS可以看到關(guān)于表的統(tǒng)計(jì)數(shù)據(jù)
• 2.通過SHOW INDEX可以看到關(guān)于索引的統(tǒng)計(jì)數(shù)據(jù)

InnoDB提供了兩種存儲統(tǒng)計(jì)數(shù)據(jù)的方式

• 1.永久性的統(tǒng)計(jì)數(shù)據(jù):這種統(tǒng)計(jì)數(shù)據(jù)存儲在磁盤上捷绑，也就是服務(wù)器重啟之后這些統(tǒng)計(jì)數(shù)據(jù)還在尤仍。
• 2.非永久性的統(tǒng)計(jì)數(shù)據(jù):這種統(tǒng)計(jì)數(shù)據(jù)存儲在內(nèi)存中箭昵，當(dāng)服務(wù)器關(guān)閉時這些這些統(tǒng)計(jì)數(shù)據(jù)就都被清除掉了怠惶，等到服務(wù)器重啟之后，在某些適當(dāng)?shù)膱鼍跋虏艜匦率占@些統(tǒng)計(jì)數(shù)據(jù)轮傍。
• 3.通過innodb_stats_persistent來決定是采用哪種方式存儲統(tǒng)計(jì)數(shù)據(jù)暂雹，在5.6.6之前默認(rèn)是OFF--即存儲到內(nèi)存
  后來默認(rèn)存儲到磁盤
• 4.以表為單位來收集和存儲統(tǒng)計(jì)數(shù)據(jù)的，即一些表的相關(guān)統(tǒng)計(jì)信息可以到內(nèi)存创夜，另外一些可以到磁盤杭跪。
• 5.創(chuàng)建表的時候指定屬性STATS_PERSISTENT，等于1代表到磁盤 等于0代表到內(nèi)存驰吓，未指定則采用innodb_stats_persistent

基于磁盤的永久性統(tǒng)計(jì)數(shù)據(jù)

• 1.存儲索引和表的統(tǒng)計(jì)數(shù)據(jù)的系統(tǒng)表分別是：innodb_index_stats 和innodb_table_stats

innodb_table_stats

• 1.包含的屬性：
  database_name 數(shù)據(jù)庫名
  table_name 表名
  last_update 本條記錄最后更新時間
  n_rows 表中記錄的條數(shù)
  clustered_index_size 表的聚簇索引占用的頁面數(shù)量
  sum_of_other_index_sizes 表的其他索引占用的頁面數(shù)量

n_rows統(tǒng)計(jì)項(xiàng)的收集

• 1.按照一定算法（并不是純粹隨機(jī)的）選取幾個葉子節(jié)點(diǎn)頁面涧尿，計(jì)算每個頁面中主鍵值記錄數(shù)量
• 2.然后計(jì)算平均一個頁面中主鍵值的記錄數(shù)量乘以全部葉子節(jié)點(diǎn)的數(shù)量就算是該表的n_rows值。
• 3.通過innodb_stats_persistent_sample_pages可以控制采樣的頁面數(shù)量--默認(rèn)是20
• 4.可以在創(chuàng)建表的時候指定STATS_SAMPLE_PAGES來決定不同的表計(jì)算nrows的頁面數(shù)量檬贰。沒有指定的話就用innodb_stats_persistent_sample_pages作為默認(rèn)值

clustered_index_size和sum_of_other_index_sizes統(tǒng)計(jì)項(xiàng)的收集

• 1.從數(shù)據(jù)字典里找到表的各個索引對應(yīng)的根頁面位置---系統(tǒng)表SYS_INDEXES里存儲了各個索引對應(yīng)的根頁面信息姑廉。
• 2.從根頁面的Page Header里找到葉子節(jié)點(diǎn)段和非葉子節(jié)點(diǎn)段對應(yīng)的Segment Header。
  -在每個索引的根頁面的Page Header部分都有兩個字段：PAGE_BTR_SEG_LEAF：表示B+樹葉子段的Segment Header信息翁涤。
  PAGE_BTR_SEG_TOP：表示B+樹非葉子段的Segment Header信息桥言。
• 3.從葉子節(jié)點(diǎn)段和非葉子節(jié)點(diǎn)段的Segment Header中找到這兩個段對應(yīng)的INODE Entry結(jié)構(gòu)萌踱。
• 4.從對應(yīng)的INODE Entry結(jié)構(gòu)中可以找到該段對應(yīng)所有零散的頁面地址以及FREE、NOT_FULL号阿、FULL鏈表的基節(jié)點(diǎn)
• 5.直接統(tǒng)計(jì)零散的頁面有多少個并鸵，然后從那三個鏈表的List Length字段中讀出該段占用的區(qū)的大小，每個區(qū)占用64個頁扔涧，所以就可以統(tǒng)計(jì)出整個段占用的頁面
• 6.分別計(jì)算聚簇索引的葉子結(jié)點(diǎn)段和非葉子節(jié)點(diǎn)段占用的頁面數(shù)园担，它們的和就是clustered_index_size的值
• 7.按照同樣的套路把其余索引占用的頁面數(shù)都算出來，加起來之后就是sum_of_other_index_sizes的值
• 8.扰柠，我們說一個段的數(shù)據(jù)在非常多時（超過32個頁面）粉铐，會以區(qū)為單位來申請空間疼约，這里頭的問題是以區(qū)為單位申請空間中有一些頁可能并沒有使用卤档，但是在統(tǒng)計(jì)clustered_index_size和sum_of_other_index_sizes時都把它們算進(jìn)去了，所以說聚簇索引和其他的索引占用的頁面數(shù)可能比這兩個值要小一些程剥。

innodb_index_stats

• 1.包含的屬性：
  database_name 數(shù)據(jù)庫名
  table_name 表名
  index_name 索引名
  last_update 本條記錄最后更新時間
  stat_name 統(tǒng)計(jì)項(xiàng)的名稱
  stat_value 對應(yīng)的統(tǒng)計(jì)項(xiàng)的值
  sample_size 為生成統(tǒng)計(jì)數(shù)據(jù)而采樣的頁面數(shù)量
  stat_description 對應(yīng)的統(tǒng)計(jì)項(xiàng)的描述

stat_name的類型

• 1.n_leaf_pages：表示該索引的葉子節(jié)點(diǎn)占用多少頁面劝枣。
• 2.size：表示該索引共占用多少頁面。
• 3.n_diff_pfxNN：表示對應(yīng)的索引列不重復(fù)的值有多少：n_diff_pfx01表示的是統(tǒng)計(jì)key_part1這單單一個列不重復(fù)的值有多少织鲸。
  n_diff_pfx02表示的是統(tǒng)計(jì)key_part1舔腾、key_part2這兩個列組合起來不重復(fù)的值有多少。
  n_diff_pfx03表示的是統(tǒng)計(jì)key_part1搂擦、key_part2稳诚、key_part3這三個列組合起來不重復(fù)的值有多少。
  類似01代表索引列只有一個瀑踢，02則代表2個扳还，后面依次如此，對于非主鍵或者非唯一索引橱夭，他們的n_diff_pfxNN 不僅僅包含索引列本身氨距，還需要再加上主鍵。
• 4.在計(jì)算某些索引列中包含多少不重復(fù)值時棘劣，需要對一些葉子節(jié)點(diǎn)頁面進(jìn)行采樣俏让，size列就表明了采樣的頁面數(shù)量是多少。
• 5.對于有多個列的聯(lián)合索引來說茬暇，采樣的頁面數(shù)量是：innodb_stats_persistent_sample_pages × 索引列的個數(shù)
• 6.當(dāng)需要采樣的頁面數(shù)量大于該索引的葉子節(jié)點(diǎn)數(shù)量的話首昔，就直接采用全表掃描來統(tǒng)計(jì)索引列的不重復(fù)值數(shù)量了

定期更新統(tǒng)計(jì)數(shù)據(jù)

• 1.innodb_table_stats和innodb_index_stats表更新的方式：
• 2.innodb_stats_auto_recalc：決定著服務(wù)器是否自動重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)，它的默認(rèn)值是ON糙俗，如果發(fā)生變動的記錄數(shù)量超過了表大小的10%勒奇，并且自動重新計(jì)算統(tǒng)計(jì)數(shù)據(jù)的功能是打開的
• 3.STATS_AUTO_RECALC參數(shù)可以指定某個表是否采用這種方式。
• 4.手動調(diào)用ANALYZE TABLE語句來更新統(tǒng)計(jì)信息臼节，如果是innodb_stats_auto_recalc是OFF的情況撬陵，可以手動珊皿。該操作是同步的比較損耗性能。

讓修改的統(tǒng)計(jì)數(shù)據(jù)生效

• 1.首先通過update修改統(tǒng)計(jì)表
• 2.然后調(diào)用FLUSH TABLE table_name--這個tablename是我們統(tǒng)計(jì)的table巨税，而不是統(tǒng)計(jì)表

基于內(nèi)存的非永久性統(tǒng)計(jì)數(shù)據(jù)

• 1.與永久性的統(tǒng)計(jì)數(shù)據(jù)不同蟋定，非永久性的統(tǒng)計(jì)數(shù)據(jù)采樣的頁面數(shù)量是由innodb_stats_transient_sample_pages控制的，這個系統(tǒng)變量的默認(rèn)值是8
• 2.所以導(dǎo)致MySQL查詢優(yōu)化器計(jì)算查詢成本的時候依賴的是經(jīng)常變化的統(tǒng)計(jì)數(shù)據(jù)草添，也就會生成經(jīng)常變化的執(zhí)行計(jì)劃

innodb_stats_method

• 1.我們知道索引列不重復(fù)的值的數(shù)量這個統(tǒng)計(jì)數(shù)據(jù)對于MySQL查詢優(yōu)化器十分重要驶兜，因?yàn)橥ㄟ^它可以計(jì)算出在索引列中平均一個值重復(fù)多少行
• 2.通過重復(fù)多少行可以應(yīng)用到單表查詢中單點(diǎn)區(qū)間太多，--SELECT * FROM tbl_name WHERE key IN ('xx1', 'xx2', ..., 'xxn');所以直接依賴統(tǒng)計(jì)數(shù)據(jù)中的平均一個值重復(fù)多少行來計(jì)算單點(diǎn)區(qū)間對應(yīng)的記錄數(shù)量远寸。
• 3.連接查詢時抄淑，如果有涉及兩個表的等值匹配連接條件，該連接條件對應(yīng)的被驅(qū)動表中的列又擁有索引時驰后，則可以使用ref訪問方法來對被驅(qū)動表進(jìn)行查詢
• 4.通過innodb_stats_method 來可以設(shè)置對于列值為NULL的處理肆资，當(dāng)結(jié)果為nulls_equal:認(rèn)為所有NULL值都是相等的。這個值也是innodb_stats_method的默認(rèn)值
  如果某個索引列中NULL值特別多的話灶芝，這種統(tǒng)計(jì)方式會讓優(yōu)化器認(rèn)為某個列中平均一個值重復(fù)次數(shù)特別多郑原，所以傾向于不使用索引進(jìn)行訪問。
  nulls_unequal：認(rèn)為所有NULL值都是不相等的夜涕。--如果某個索引列中NULL值特別多的話犯犁，這種統(tǒng)計(jì)方式會讓優(yōu)化器認(rèn)為某個列中平均一個值重復(fù)次數(shù)特別少，所以傾向于使用索引進(jìn)行訪問女器。
  nulls_ignored：直接把NULL值忽略掉酸役。
• 5.最好不在索引列中存放NULL值才是正解。