背景 在QQ群里，一位朋友問了如下的問題：

問題是：只查詢一個(gè)information_schema.tables;表，為啥會(huì)有幾百個(gè)Opening tables？

原因：此操作會(huì)觸發(fā)表統(tǒng)計(jì)信息的收集。關(guān)于information_schema中表的查詢都要去真正的表查看，所以慢。

MySQL如何收集統(tǒng)計(jì)信息？ Analyze table收集表和索引統(tǒng)計(jì)信息，適用于MyISAM和InnoDB； 對(duì)于innodb表，還可以使用以下選項(xiàng)： 1 表第一次打開的時(shí)候

2 表修改的行超過1/16或者20億條 ./row/row0mysql.c:row_update_statistics_if_needed

3 執(zhí)行show index/table或者查詢information_schema.tables/statistics表時(shí) 在訪問以下表時(shí)，innodb表的統(tǒng)計(jì)信息可自動(dòng)收集 information_schema.TABLES information_schema.STATISTICS information_schema.PARTITIONS information_schema.KEY_COLUMN_USAGE information_schema.TABLE_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS information_schema.table_constraints

innodb_stats_on_metadata參數(shù)用來控制此行為，設(shè)置為false時(shí)不更新統(tǒng)計(jì)信息 Innodb_stats_sample_pages每次收集統(tǒng)計(jì)信息時(shí)采樣的頁數(shù)，默認(rèn)為8 每個(gè)表維護(hù)一個(gè)stat_modified_counter，每次DML更新1行就加1，直到滿足閾值則自動(dòng)收集統(tǒng)計(jì)信息，并把此值清0； 函數(shù)dict_update_statistics用于更新統(tǒng)計(jì)信息，但若有多個(gè)線程同時(shí)檢測(cè)到閾值，會(huì)導(dǎo)致多次調(diào)用，浪費(fèi)了系統(tǒng)資源； 可以直接修改代碼，讓dict_update_statistics對(duì)stat_modified_counter加鎖，避免并發(fā)執(zhí)行；http://dinglin.iteye.com/blog/1815392

5.6提供選項(xiàng)innodb_stats_persistent，默認(rèn)on，將analyze table產(chǎn)生的統(tǒng)計(jì)信息保存于磁盤，直至下次analyze table為止，此舉避免了統(tǒng)計(jì)信息動(dòng)態(tài)更新，保證了執(zhí)行計(jì)劃的穩(wěn)定，對(duì)于大表也節(jié)省了收集統(tǒng)計(jì)信息的所需資源; 除非當(dāng)前sql執(zhí)行計(jì)劃不佳，否則不應(yīng)經(jīng)常analyze table收集統(tǒng)計(jì)信息。

Innodb_stats_method和myisam_stats_method 計(jì)算統(tǒng)計(jì)信息時(shí)，擁有相同key PRefix的行算作一個(gè)value group(類似Oracle索引中的num_distinct，其值越多意味著索引選擇性越好)，average group size是非常重要的指標(biāo)，即平均一個(gè)索引值返回的表行數(shù)，主要有兩個(gè)用途： 1估算每次ref access要讀取多少行 2 估算一個(gè)partial join要產(chǎn)生多少行 (…) join tab on tab.key = expr 由此可知，average group size越高則索引選擇性越低，表基數(shù)即value group數(shù)量計(jì)算公式為N/S(N:表行數(shù) S:average group size)，可通過show index查看

除了主鍵，索引不可避免的會(huì)遇到Null(對(duì)于<=>操作符，NULL和Non-null被同等對(duì)待，而Null = Null則會(huì)返回false)，mysql將NULL視作無窮小； 收集統(tǒng)計(jì)信息時(shí)，為了靈活的處理Null，InnoDB/MyISAM各引入一個(gè)參數(shù)Innodb_stats_method/myisam_stats_method，分別三個(gè)候選值：nulls_equal/nulls_unequal/nulls_ignored(其中innod_stats_method只有全局變量) Nulls_equal：所有Null都相等，即算作一個(gè)value group；若Null過多則會(huì)導(dǎo)致average group size偏大 Nulls_unequal：所有Null互不相同，每個(gè)算作一個(gè)value group；如果non-null group size過大且null數(shù)量過多，此設(shè)置會(huì)拉低整體的average group size，可能導(dǎo)致濫用索引 Nulls_ignored：忽略Null 對(duì)于已經(jīng)收集的統(tǒng)計(jì)信息，無法分辨其采用了那種方式；對(duì)于非InnoDB/MyISAM表，只有一種收集方式，即nulls_equal； 手工收集統(tǒng)計(jì)信息需要調(diào)用analyze table，但若表自上次analye至今沒有任何改動(dòng)，即便調(diào)用此命令實(shí)際也不會(huì)收集統(tǒng)計(jì)信息，需先讓統(tǒng)計(jì)信息過期(插入一行再刪除即可) Mysql也可自動(dòng)收集，諸如bulk insert/delete以及某些alter table語句均會(huì)觸發(fā)。

如何查看統(tǒng)計(jì)信息 Show index from table或查看information_schema.statistics表 Show table status或information_schema.tables表

http://www.mysqlperformanceblog.com/2011/10/06/when-does-innodb-update-table-statistics-and-when-it-can-bite/ http://www.mysqlperformanceblog.com/2008/09/03/analyze-myisam-vs-innodb/

iostat中的util、srvctm、await %util與硬盤設(shè)備飽和度

%util表示該設(shè)備有I/O（即非空閑）的時(shí)間比率，不考慮I/O有多少，只考慮有沒有。由于現(xiàn)代硬盤設(shè)備都有并行處理多個(gè)I/O請(qǐng)求的能力，所以%util即使達(dá)到100%也不意味著設(shè)備飽和了。舉個(gè)簡(jiǎn)化的例子：某硬盤處理單個(gè)I/O需要0.1秒，有能力同時(shí)處理10個(gè)I/O請(qǐng)求，那么當(dāng)10個(gè)I/O請(qǐng)求依次順序提交的時(shí)候，需要1秒才能全部完成，在1秒的采樣周期里%util達(dá)到100%；而如果10個(gè)I/O請(qǐng)求一次性提交的話，0.1秒就全部完成，在1秒的采樣周期里%util只有10%?？梢?，即使%util高達(dá)100%，硬盤也仍然有可能還有余力處理更多的I/O請(qǐng)求，即沒有達(dá)到飽和狀態(tài)。那么iostat(1)有沒有哪個(gè)指標(biāo)可以衡量硬盤設(shè)備的飽和程度呢？很遺憾，沒有。

await是單個(gè)I/O所消耗的時(shí)間，包括硬盤設(shè)備處理I/O的時(shí)間和I/O請(qǐng)求在kernel隊(duì)列中等待的時(shí)間，正常情況下隊(duì)列等待時(shí)間可以忽略不計(jì)，姑且把a(bǔ)wait當(dāng)作衡量硬盤速度的指標(biāo)吧，那么多大算是正常呢？ 對(duì)于SSD，從0.0x毫秒到1.x毫秒不等，具體看產(chǎn)品手冊(cè)； 對(duì)于機(jī)械硬盤，可以參考以下文檔中的計(jì)算方法： http://cseweb.ucsd.edu/classes/wi01/cse102/sol2.pdf 大致來說一萬轉(zhuǎn)的機(jī)械硬盤是8.38毫秒，包括尋道時(shí)間、旋轉(zhuǎn)延遲、傳輸時(shí)間。

svctm：已被廢棄的指標(biāo)，沒什么意義，svctm=[util/tput]