评论

收藏

[MySQL] MySQL 5.6为什么关闭元数据统计信息自动更新&统计信息收集源代码探索

数据库 数据库 发布于:2021-07-03 21:45 | 阅读数:464 | 评论:0

  问题描述:

  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_constraintsinformation_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 */
{
ulintcounter;
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%)并且innodb_stats_auto_recalc=on,统计信信息会更新(虽然innodb_stats_auto_recalc=on是自动重新计算,但是也是异步的,可能会延时,比如当瞬间的DML批量操作就可能有延时
  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
  --------------------------------------------------------------
  基数即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

  
关注下面的标签,发现更多相似文章