问题描述:MySQL5.5.15原sql如下:selectconstraint_schema,table_name,constraint_name,constraint_typefrominformation_schema.table_constraintswheretable_s
./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) /*!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 */); } }---------------------------------------------------------------------------
#通过更新统计信息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) /*!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