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

问题描述：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.TABLESinformation_schema.STATISTICSinformation_schema.PARTITIONSinformation_schema.KEY_COLUMN_USAGEinformation_schema.TABLE_CONSTRAINTSinformation_schema.REFERENTIAL_CONSTRAINTSshow 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  

12345678910111213141516171819202122232425

/*********************************************************************//**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操作导致1 行更新,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

12345678910111213141516171819202122

/*********************************************************************//**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 置04）   解锁---------------------------------------------------------------------------二.MySQL 5.6的改进：可以配置统计信息的持久化和非持久化（非持久化：5.6之前都是这种）相关参数：持久化：innodb_stats_persistent：on（1）innodb_stats_persistent_sample_pages：20非持久化：innodb_stats_sample_pages:8相关表：mysql.innodb_index_statsmysql.innodb_table_statsFrom 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都忽略，不记录索引。----------------------------------------------------------------------------------------------------分割线 --------------------------------------用mysqldump和mysqlbinlog的MySQL数据恢复实验 http://www.linuxidc.com/Linux/2011-10/45068.htmUbuntu 14.04下安装MySQL http://www.linuxidc.com/Linux/2014-05/102366.htm《MySQL权威指南（原书第2版）》清晰中文扫描版 PDF http://www.linuxidc.com/Linux/2014-03/98821.htmUbuntu 14.04 LTS 安装 LNMP NginxPHP5 （PHP-FPM）MySQL http://www.linuxidc.com/Linux/2014-05/102351.htmUbuntu 14.04下搭建MySQL主从服务器 http://www.linuxidc.com/Linux/2014-05/101599.htmUbuntu 12.04 LTS 构建高可用分布式 MySQL 集群 http://www.linuxidc.com/Linux/2013-11/93019.htmUbuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb http://www.linuxidc.com/Linux/2013-08/89270.htmMySQL-5.5.38通用二进制安装 http://www.linuxidc.com/Linux/2014-07/104509.htm--------------------------------------分割线 --------------------------------------本文永久更新链接地址