1.作用
5.6.6开始，MySQL默认使用了持久化统计信息，即INNODB_STATS_PERSISTENT=ON，持久化统计信息保存在表mysql.innodb_table_stats和mysql.innodb_index_stats。
持久化统计信息在以下情况会被自动更新：
1>INNODB_STATS_AUTO_RECALC=ON情况下，表中10%的数据被修改
2>增加新的索引
innodb_index_stats：是索引的统计信息
innodb_table_stats：是表的统计信息
2.详解
| innodb_table_stats |
|
| database_name |
数据库名 |
| table_name |
表名 |
| last_update |
统计信息最后一次更新时间 |
| n_rows |
表的行数 |
| clustered_index_size |
聚集索引的页的数量 |
| sum_of_other_index_sizes |
其他索引的页的数量 |
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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
| mysql> select * from mysql.innodb_table_stats\G
*************************** 1. row ***************************
database_name: mysql
table_name: gtid_executed
last_update: 2021-03-31 01:16:24
n_rows: 0
clustered_index_size: 1
sum_of_other_index_sizes: 0
*************************** 2. row ***************************
database_name: school
table_name: course
last_update: 2021-05-25 19:01:33
n_rows: 3
clustered_index_size: 1
sum_of_other_index_sizes: 0
*************************** 3. row ***************************
database_name: school
table_name: sc
last_update: 2021-05-25 19:01:43
n_rows: 17
clustered_index_size: 1
sum_of_other_index_sizes: 0
*************************** 4. row ***************************
database_name: school
table_name: student
last_update: 2021-05-25 19:01:53
n_rows: 10
clustered_index_size: 1
sum_of_other_index_sizes: 0
*************************** 5. row ***************************
database_name: school
table_name: teacher
last_update: 2021-05-25 19:01:22
n_rows: 3
clustered_index_size: 1
sum_of_other_index_sizes: 0
*************************** 6. row ***************************
database_name: sys
table_name: sys_config
last_update: 2021-03-31 01:16:25
n_rows: 6
clustered_index_size: 1
sum_of_other_index_sizes: 0
6 rows in set (0.00 sec)
|
| innodb_index_stats |
|
| database_name |
数据库名 |
| table_name |
表名 |
| index_name |
索引名 |
| last_update |
统计信息最后一次更新时间 |
| stat_name |
统计信息名 |
| stat_value |
统计信息的值 |
| sample_size |
采样大小 |
| stat_description |
类型说明 |
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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
| mysql> select * from mysql.innodb_index_stats\G
*************************** 1. row ***************************
database_name: mysql
table_name: gtid_executed
index_name: PRIMARY
last_update: 2021-03-31 01:16:24
stat_name: n_diff_pfx01
stat_value: 0
sample_size: 1
stat_description: source_uuid
*************************** 2. row ***************************
database_name: mysql
table_name: gtid_executed
index_name: PRIMARY
last_update: 2021-03-31 01:16:24
stat_name: n_diff_pfx02
stat_value: 0
sample_size: 1
stat_description: source_uuid,interval_start
*************************** 3. row ***************************
database_name: mysql
table_name: gtid_executed
index_name: PRIMARY
last_update: 2021-03-31 01:16:24
stat_name: n_leaf_pages
stat_value: 1
sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 4. row ***************************
database_name: mysql
table_name: gtid_executed
index_name: PRIMARY
last_update: 2021-03-31 01:16:24
stat_name: size
stat_value: 1
sample_size: NULL
stat_description: Number of pages in the index
*************************** 5. row ***************************
database_name: school
table_name: course
index_name: PRIMARY
last_update: 2021-05-25 19:01:33
stat_name: n_diff_pfx01
stat_value: 3
sample_size: 1
stat_description: cno
*************************** 6. row ***************************
database_name: school
table_name: course
index_name: PRIMARY
last_update: 2021-05-25 19:01:33
stat_name: n_leaf_pages
stat_value: 1
sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 7. row ***************************
database_name: school
table_name: course
index_name: PRIMARY
last_update: 2021-05-25 19:01:33
stat_name: size
stat_value: 1
sample_size: NULL
stat_description: Number of pages in the index
*************************** 8. row ***************************
database_name: school
table_name: sc
index_name: GEN_CLUST_INDEX
last_update: 2021-05-25 19:01:43
stat_name: n_diff_pfx01
stat_value: 17
sample_size: 1
stat_description: DB_ROW_ID
*************************** 9. row ***************************
database_name: school
table_name: sc
index_name: GEN_CLUST_INDEX
last_update: 2021-05-25 19:01:43
stat_name: n_leaf_pages
stat_value: 1
sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 10. row ***************************
database_name: school
table_name: sc
index_name: GEN_CLUST_INDEX
last_update: 2021-05-25 19:01:43
stat_name: size
stat_value: 1
sample_size: NULL
stat_description: Number of pages in the index
*************************** 11. row ***************************
database_name: school
table_name: student
index_name: PRIMARY
last_update: 2021-05-25 19:01:53
stat_name: n_diff_pfx01
stat_value: 10
sample_size: 1
stat_description: sno
*************************** 12. row ***************************
database_name: school
table_name: student
index_name: PRIMARY
last_update: 2021-05-25 19:01:53
stat_name: n_leaf_pages
stat_value: 1
sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 13. row ***************************
database_name: school
table_name: student
index_name: PRIMARY
last_update: 2021-05-25 19:01:53
stat_name: size
stat_value: 1
sample_size: NULL
stat_description: Number of pages in the index
*************************** 14. row ***************************
database_name: school
table_name: teacher
index_name: PRIMARY
last_update: 2021-05-25 19:01:22
stat_name: n_diff_pfx01
stat_value: 3
sample_size: 1
stat_description: tno
*************************** 15. row ***************************
database_name: school
table_name: teacher
index_name: PRIMARY
last_update: 2021-05-25 19:01:22
stat_name: n_leaf_pages
stat_value: 1
sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 16. row ***************************
database_name: school
table_name: teacher
index_name: PRIMARY
last_update: 2021-05-25 19:01:22
stat_name: size
stat_value: 1
sample_size: NULL
stat_description: Number of pages in the index
*************************** 17. row ***************************
database_name: sys
table_name: sys_config
index_name: PRIMARY
last_update: 2021-03-31 01:16:25
stat_name: n_diff_pfx01
stat_value: 6
sample_size: 1
stat_description: variable
*************************** 18. row ***************************
database_name: sys
table_name: sys_config
index_name: PRIMARY
last_update: 2021-03-31 01:16:25
stat_name: n_leaf_pages
stat_value: 1
sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 19. row ***************************
database_name: sys
table_name: sys_config
index_name: PRIMARY
last_update: 2021-03-31 01:16:25
stat_name: size
stat_value: 1
sample_size: NULL
stat_description: Number of pages in the index
19 rows in set (0.00 sec)
|