問題描述： 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’); 不
問題描述：
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庫下的一下幾個表，訪問時候都會觸發(fā)這個“順手”操作。
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 都會觸發(fā)自動更新統(tǒng)計信息。
問題：
5.6 開始默認innodb_stats_on_metadata=off，why？？？ 答：為了防止自動更新統(tǒng)計信息在DB高峰時導致BP的swap；查詢性能大幅度抖動。
沒有定期更新統(tǒng)計信息了么？？答：有啊，而且可以是持久化的。
我看到的MySQL 5.5.15 這個版本還是條件是====>
counter > 2000000000 || ((ib_int64_t)counter > 16 + table->stat_n_rows / 16)
下面做了對MySQL 收集統(tǒng)計信息做了擴展：
一.下面針對MySQL community（5.5.15、5.5.36、5.6.16）源代碼分析：
1.下面是對MySQL-5.5.15 的源代碼的分析：
./storage/innobase/row/row0mysql.c
/*********************************************************************//**
Updates the table modification counter and calculates new estimates
for table and index statistics if necessary. */
UNIV_INLINE
void
row_update_statistics_if_needed(
/*============================*/
dict_table_t* table) /*!< in: table */
{
ulint counter;
counter = table->stat_modified_counter;
table->stat_modified_counter = counter + 1;
/* Calculate new statistics if 1 / 16 of table has been modified
since the last time a statistics batch was run, or if
stat_modified_counter > 2 000 000 000 (to avoid wrap-around).
We calculate statistics at most every 16th round, since we may have
a counter table which is very small and updated very often. */
if (counter > 2000000000
|| ((ib_int64_t)counter > 16 + table->stat_n_rows / 16)) {
dict_update_statistics(table, FALSE /* update even if stats
are initialized */);
}
}從上面可以看出更新統(tǒng)計信息的條件是：
counter > 2000000000 || ((ib_int64_t)counter > 16 + table->stat_n_rows / 16)
2.下面是對MySQL-5.5.36 的源代碼的分析：
---------------------------------------------------------------------------
#通過更新統(tǒng)計信息stat_modified_counter，每個表都有這個表里來維護：
./storage/innobase/row/row0mysql.c
/*********************************************************************//**
Updates the table modification counter and calculates new estimates
for table and index statistics if necessary. */
UNIV_INLINE
void
row_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 */);
}
}
/*********************************************************************/規(guī)則：每一次DML操作導致1 行更新,stat_modified_counter加1，直到滿足更新統(tǒng)計信息的條件，stat_modified_counter的值自動重置為0。
#更新統(tǒng)計信息的條件：（有超過1/16的row被更改過會更新表的條件信息）
./storage/innobase/include/dict0dict.h
/** Calculate new statistics if 1 / 16 of table has been modified since the last time a statistics batch was run. We calculate statistics at most every 16th round, since we may have a counter table which is very small and updated very often. @param t table @return true if the table has changed too much and stats need to be recalculated */ #define DICT_TABLE_CHANGED_TOO_MUCH(t) \ ((ib_int64_t) (t)->stat_modified_counter > 16 + (t)->stat_n_rows / 16) /*********************************************************************/
* 這樣有個性能問題，若有多個線程同時檢測到閾值，也即是并發(fā)調用會多次，，會導致dict_update_statistics函數(shù)多次的調用，浪費了系統(tǒng)資源。
解決方法：在dict_update_statistics{}函數(shù)對stat_modified_counter加鎖，避免并發(fā)執(zhí)行。
#統(tǒng)計新跟更新函數(shù)：dict_update_statistics
./storage/innobase/dict/dict0dict.c
/*********************************************************************//**
Calculates new estimates for table and index statistics. The statistics
are used in query optimization. */
UNIV_INTERN
void
dict_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;);
-----------------------------------------------------------------------------可以優(yōu)化成：
---------------------------------------------------------------------------
1） 加x鎖
2） 索引統(tǒng)計
3） stat_modified_counter 置0
4） 解鎖
---------------------------------------------------------------------------
3.下面我們來看下對MySQL 5.6.16 的源代碼的分析：
MySQL版本：MySQL 5.6.16-log。
./storage/innobase/row/row0mysql.cc
void
row_update_statistics_if_needed(
/*============================*/
dict_table_t* table) /*!< in: table */
{
ib_uint64_t counter;
ib_uint64_t n_rows;
if (!table->stat_initialized) {
DBUG_EXECUTE_IF(
"test_upd_stats_if_needed_not_inited",
fprintf(stderr, "test_upd_stats_if_needed_not_inited "
"was executed\n");
);
return;
}
counter = table->stat_modified_counter++;
n_rows = dict_table_get_n_rows(table);
if (dict_stats_is_persistent_enabled(table)) {
if (counter > n_rows / 10 /* 10% */
&& dict_stats_auto_recalc_is_enabled(table)) {
dict_stats_recalc_pool_add(table);
table->stat_modified_counter = 0;
}
return;
}
/* Calculate new statistics if 1 / 16 of table has been modified
since the last time a statistics batch was run.
We calculate statistics at most every 16th round, since we may have
a counter table which is very small and updated very often. */
if (counter > 16 + n_rows / 16 /* 6.25% */) {
ut_ad(!mutex_own(&dict_sys->mutex));
/* this will reset table->stat_modified_counter to 0 */
dict_stats_update(table, DICT_STATS_RECALC_TRANSIENT);
}
}
/*********************************************************************/從上面的代碼看可以看出：
1.對InnoDB表統(tǒng)計信息持久化時，表的row發(fā)生變化大于10%(counter > n_rows / 10 /* 10%)并且innodb_stats_auto_recalc=on，統(tǒng)計信信息會更新（雖然innodb_stats_auto_recalc=on是自動重新計算，但是也是異步的，可能會延時，比如當瞬間的DML批量操作就可能有延時）
2.統(tǒng)計信息非持久化還是和5.5 一致的（表的row發(fā)生變化大于1/16時更新統(tǒng)計信息）
3.切記：不能完全依賴于MySQL本身的機制來更新統(tǒng)計信息，線上一些表不及時更新統(tǒng)計信息的我遇到過多次，針對這樣的表，我在夜間定期analyze table xxx;
二.MySQL 5.6的改進：
可以配置統(tǒng)計信息的持久化和非持久化（非持久化：5.6之前都是這種）
相關參數(shù)：
持久化：
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 開始,統(tǒng)計信息默認是持久化的（即innodb_stats_persistent=on）,使用參數(shù)innodb_stats_persistent_sample_pages的值，來采樣，此時非持久化的參數(shù)innodb_stats_sample_pages就無效。
From 5.6.6 開始，使用非持久化的統(tǒng)計信息：
1.set innodb_stats_persistent=0;
2.create|alter table stats_persistent=0;
對單個表開啟：
create|alter table...STATS_PERSISTENT [=] {DEFAULT|0|1}DEFAULT：table的統(tǒng)計信息是否持久化由參數(shù) innodb_stats_persistent 決定。\
總結:From 5.6.6 開始,要么開啟統(tǒng)計信息持久化，要么是還用以前的非持久化，二者選一。
參考相關參數(shù)：
innodb_stats_method： nulls_equal, nulls_unequal, and nulls_ignored
myisam_stats_method：nulls_equal, nulls_unequal, and nulls_ignored
--------------------------------------------------------------
基數(shù)即value group=N/s (N:表行數(shù) S:average group size)
基數(shù)(VG)|值組為不重復的值的個數(shù)
nulls_equal：所有的NULL都相等，算作一個值組，這樣一旦null值很多的情況下，average group size偏大，導致基數(shù)偏小。
nulls_unequal：每一個NULL都相等，算作一個值組，這樣一旦null值很多的情況下，如果non-null值組大，而null的值組過多，導致average group size偏小，導致基數(shù)偏大，可能導致誤走索引
nulls_ignored：所有的null都忽略，不記錄索引。
--------------------------------------------------------------
參考：
# http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_method
聲明：本網(wǎng)頁內容旨在傳播知識，若有侵權等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright ? 2019-2025 m.alabama37th.com 版權所有
違法及侵權請聯(lián)系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市萬商天勤律師事務所王興未律師提供法律服務
