Why does MySQL5.6 disable the automatic update of metadata statistics to collect statistics source code?

Why does MySQL5.6 disable the automatic update of metadata statistics to collect statistics source code?

Why does MySQL 5.6 disable automatic update of metadata statistics to collect source code?

Problem description:

The original SQL statement for MySQL 5.5.15 is as follows:

Select constraint_schema, table_name, constraint_name, constraint_type from information_schema.table_constraints where table_schema not in ('information _ scheme', 'mysql', 'test', 'performance _ scheme ');

Not only the table_constraints mentioned above, but several tables in the information_schema database will trigger this "easy" operation during access.

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...

When innodb_stats_on_metadata = on, statistics are automatically updated.

Problem:

From 5.6, the default innodb_stats_on_metadata = off, why ??? A: In order to prevent the automatically updating of statistical information from causing BP swap during DB peaks, the query performance is greatly jitters.

Are statistics not updated regularly ?? A: Yes, and it can be persistent.

The MySQL 5.5.15 version I saw is still conditional ==>

Counter> 2000000000 | (ib_int64_t) counter> 16 + table-> stat_n_rows/16)

The following is an extension of MySQL collection statistics:

I. Let's take a look at the source code analysis of MySQL 5.5.36:

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

# Update the statistical information stat_modified_counter. Each table has this table for maintenance:

./Storage/innobase/row/row0mysql. c

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

/*************************************** ******************************//**

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 */);

}

}

/*************************************** ******************************/

Rule: Each DML operation results in one update. stat_modified_counter increases by 1 until the condition for updating statistics is met. The value of stat_modified_counter is automatically reset to 0.

* This is a personal problem. If multiple threads detect the threshold value simultaneously, that is, concurrent calls are performed multiple times, resulting in multiple calls of the dict_update_statistics function, which wastes system resources.

Solution: Lock stat_modified_counter in the dict_update_statistics {} function to avoid concurrent execution.

# New and updated statistics function: dict_update_statistics

./Storage/innobase/dict/dict0dict. c

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

/*************************************** ******************************//**

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