MySQL 5.6為什麼關閉中繼資料統計資訊自動更新&統計資訊收集原始碼探索

標籤：統計資訊   mysql原始碼   

問題描述：

MySQL 5.5.15 原sql如下：

select constraint_schema,table_name,constraint_name,constraint_type from information_schema.table_constraints where table_schema not in (‘information_schema‘, ‘mysql‘, ‘test‘，‘performance_schema’);  

 不只是上面提到的table_constraints，information_schema庫下的一下幾個表，訪問時候都會觸發這個“順手”操作。

information_schema.TABLES

information_schema.STATISTICS

information_schema.PARTITIONS

information_schema.KEY_COLUMN_USAGE

information_schema.TABLE_CONSTRAINTS

information_schema.REFERENTIAL_CONSTRAINTS

show table status  . .

show index from ...

當innodb_stats_on_metadata=on 都會觸發自動更新統計資訊。

問題：

5.6 開始預設innodb_stats_on_metadata=off，why？？？ 答：為了防止自動更新統計資訊在DB高峰時導致BP的swap；查詢效能大幅度抖動。

沒有定期更新統計資料了嗎？？答：有啊，而且可以是持久化的。

我看到的MySQL 5.5.15 這個版本還是條件是====>

counter > 2000000000 || ((ib_int64_t)counter > 16 + table->stat_n_rows / 16)

下面做了對MySQL 收集統計資訊做了擴充：

一.下面我們來看下對MySQL 5.5.36 的原始碼的分析：

---------------------------------------------------------------------------

#通過更新統計資料stat_modified_counter，每個表都有這個表裡來維護：

./storage/innobase/row/row0mysql.c  

/*********************************************************************//**Updates the table modification counter and calculates new estimatesfor table and index statistics if necessary. */UNIV_INLINEvoidrow_update_statistics_if_needed(/*============================*/        dict_table_t*   table)  /*!< in: table */{        ulint   counter;        counter = table->stat_modified_counter;        table->stat_modified_counter = counter + 1;        if (DICT_TABLE_CHANGED_TOO_MUCH(table)) {                dict_update_statistics(                        table,                        FALSE, /* update even if stats are initialized */                        TRUE /* only update if stats changed too much */);        }}/*********************************************************************/

規則：每一次DML操作導致1row更新,stat_modified_counter加1，直到滿足更新統計資料的條件，stat_modified_counter的值自動重設為0。

* 這樣有個效能問題，若有多個線程同時檢測到閾值，也即是並發調用會多次，，會導致dict_update_statistics函數多次的調用，浪費了系統資源。

解決方案：在dict_update_statistics{}函數對stat_modified_counter加鎖，避免並發執行。

#統計新跟更新函數：dict_update_statistics

./storage/innobase/dict/dict0dict.c

/*********************************************************************//**Calculates new estimates for table and index statistics. The statisticsare used in query optimization. */UNIV_INTERNvoiddict_update_statistics(/*===================*/        dict_table_t*   table,          /*!< in/out: table */        ibool           only_calc_if_missing_stats,/*!< in: only                                        update/recalc the stats if they have                                        not been initialized yet, otherwise                                        do nothing */        ibool           only_calc_if_changed_too_much)/*!< in: only                                        update/recalc the stats if the table                                        has been changed too much since the                                        last stats update/recalc */{        dict_index_t*   index;        ulint           sum_of_index_sizes      = 0;        DBUG_EXECUTE_IF("skip_innodb_statistics", return;);-----------------------------------------------------------------------------

可以最佳化成：

1）  加x鎖

2）  索引統計

3）  stat_modified_counter 置0

4）   解鎖

---------------------------------------------------------------------------

二.MySQL 5.6的改進：

可以配置統計資訊的持久化和非持久化（非持久化：5.6之前都是這種）

相關參數：

持久化：

innodb_stats_persistent：on(1)

innodb_stats_persistent_sample_pages：20

非持久化：

innodb_stats_sample_pages:8

相關表：

mysql.innodb_index_stats

mysql.innodb_table_stats

From 5.6.6 開始,統計資訊預設是持久化的（即innodb_stats_persistent=on）,使用參數innodb_stats_persistent_sample_pages的值，來採樣，此時非持久化的參數innodb_stats_sample_pages就無效。

From 5.6.6 開始，使用非持久化的統計資訊：

1.set innodb_stats_persistent=0;

2.create|alter table stats_persistent=0; 

對單個表開啟：

create|alter table...STATS_PERSISTENT [=] {DEFAULT|0|1}

DEFAULT：table的統計資訊是否持久化由參數 innodb_stats_persistent 決定。\

總結:From 5.6.6 開始,要麼開啟統計資訊持久化，要麼是還用以前的非持久化，二者選一。

參考相關參數：

innodb_stats_method： nulls_equal, nulls_unequal, and nulls_ignored
myisam_stats_method：nulls_equal, nulls_unequal, and nulls_ignored

--------------------------------------------------------------

基數即value group=N/s (N:表行數 S:average group size)
基數(VG)|值組為不重複的值的個數


nulls_equal：所有的NULL都相等，算作一個值組，這樣一旦null值很多的情況下，average group size偏大，導致基數偏小。

nulls_unequal：每一個NULL都相等，算作一個值組，這樣一旦null值很多的情況下，如果non-null值組大，而null的值組過多，導致average group size偏小，導致基數偏大，可能導致誤走索引

nulls_ignored：所有的null都忽略，不記錄索引。

--------------------------------------------------------------

參考：

# http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_method

本文出自 “My DBA life” 部落格，請務必保留此出處http://huanghualiang.blog.51cto.com/6782683/1596170

MySQL 5.6為什麼關閉中繼資料統計資訊自動更新&統計資訊收集原始碼探索