问题描述： 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库下的一下几个表，访问时候都会触发这个“顺手”操作。
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 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 */);
}
}从上面可以看出更新统计信息的条件是：
counter > 2000000000 || ((ib_int64_t)counter > 16 + table->stat_n_rows / 16)
2.下面是对MySQL-5.5.36 的源代码的分析：
---------------------------------------------------------------------------
#通过更新统计信息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 */);
}
}
/*********************************************************************/规则：每一次DML操作导致1 行更新,stat_modified_counter加1，直到满足更新统计信息的条件，stat_modified_counter的值自动重置为0。
#更新统计信息的条件：（有超过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) /*********************************************************************/
* 这样有个性能问题，若有多个线程同时检测到阈值，也即是并发调用会多次，，会导致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 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;);
-----------------------------------------------------------------------------可以优化成：
---------------------------------------------------------------------------
1） 加x锁
2） 索引统计
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表统计信息持久化时，表的row发生变化大于10%(counter > n_rows / 10 /* 10%)并且<span style="color:rgb(79,129,189);">innodb_stats_auto_recalc</span><span style="color:rgb(79,129,189);">=on，统计信信息会更新（虽然</span><span style="color:rgb(79,129,189);">innodb_stats_auto_recalc</span><span style="color:rgb(79,129,189);"><span style="color:rgb(2,103,137);font-family:'Courier New', Courier, fixed, monospace;font-size:13.63636302948px;font-weight:bold;line-height:25.2000007629395px;background-color:rgb(255,255,255);">=on是自动重新计算，但是也是异步的，可能会延时，比如当瞬间的DML批量操作就可能有延时</span>）</span>
2.统计信息非持久化还是和5.5 一致的（表的row发生变化大于1/16时更新统计信息）
3.切记：不能完全依赖于MySQL本身的机制来更新统计信息，线上一些表不及时更新统计信息的我遇到过多次，针对这样的表，我在夜间定期analyze table xxx;
二.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
<span style="font-size:19px;">--------------------------------------------------------------</span>
<span style="color:#488CF2;">基数即value group=N/s (N:表行数 S:average group size)<br>基数(VG)|值组为不重复的值的个数<br></span>
<span style="color:#488CF2;">nulls_equal：所有的NULL都相等，算作一个值组，这样一旦null值很多的情况下，<span style="font-size:13.6000003814697px;line-height:25.2000007629395px;">average group size偏大，导致基数偏小。</span></span>
<span style="color:#488CF2;">nulls_unequal：<span style="font-size:13.6000003814697px;line-height:25.2000007629395px;">每一个NULL都相等，算作一个值组，这样一旦null值很多的情况下，如果non-null值组大，而null的值组过多，导致average group size偏小，导致基数偏大，可能导致误走索引</span></span>
<span style="color:#488CF2;">nulls_ignored：所有的null都忽略，不记录索引。</span>
<span style="font-size:19px;">--------------------------------------------------------------</span>
参考：
# http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_method
