问题描述：

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=http://www.mamicode.com/on 都会触发自动更新统计信息。

问题：

5.6 开始默认innodb_stats_on_metadata=http://www.mamicode.com/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 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操作导致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 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）   解锁

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

二.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为什么关闭元数据统计信息自动更新&统计信息收集源代码探索