Shuo
Shuo I'm a DBA(Database Administrator), we can share and discuss MySQL, MongoDB, Redis and other databases here, also including learning Python, Shell, Golang together.

MySQL索引统计信息

背景

上一篇文章中(MySQL索引统计信息information_schema.INDEX_STATISTICS)提到了在MySQL的社区版本中,都提供了对于索引统计信息的表,但是,在官方的MySQL版本中,是怎么维护索引(或表)的统计信息的呢?

官方MySQL中,有performance_schema数据库,主要功能即为监控MySQL server的性能指标,不同的指标对应不同表(表的存储引擎为PERFORMANCE_SCHEMA),在MySQL5.7开始,有sys数据库,其中包含了多个视图,展示了PERFORMANCE_SCHEMA中的数据集合,使其更具可读性。

本片文章主要阐述使用索引统计信息的过程,对于performance_schema的说明,可以查阅官方文档,有更详细的说明:Chapter 26 MySQL Performance Schema

在 MySQL 中,有两种存储索引统计的方式,可以通过设置参数 innodb_stats_persistent 的值来选择:
设置为 on 的时候,表示统计信息会持久化存储。这时,默认的 N 是 20,M 是 10。
设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。

.

在performance_schema中查看统计信息

使用performance_schema进行统计信息的手机,需要在数据库启动时加入参数:performance_schema=ON,不可以在运行时进行更改。

1
2
3
mysql> set global performance_schema=on;
ERROR 1238 (HY000): Variable 'performance_schema' is a read only variable

打开之后,可以查看统计信息表:

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
mysql> show create table performance_schema.table_io_waits_summary_by_index_usage \G
*************************** 1. row ***************************
       Table: table_io_waits_summary_by_index_usage
Create Table: CREATE TABLE if not exists `table_io_waits_summary_by_index_usage` (
  `OBJECT_TYPE` varchar(64) DEFAULT NULL,
  `OBJECT_SCHEMA` varchar(64) DEFAULT NULL,
  `OBJECT_NAME` varchar(64) DEFAULT NULL,
  `INDEX_NAME` varchar(64) DEFAULT NULL,
  `COUNT_STAR` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL,
  `COUNT_READ` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_READ` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_READ` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_READ` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_READ` bigint(20) unsigned NOT NULL,
  `COUNT_WRITE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_WRITE` bigint(20) unsigned NOT NULL,
  `COUNT_FETCH` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_FETCH` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_FETCH` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_FETCH` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_FETCH` bigint(20) unsigned NOT NULL,
  `COUNT_INSERT` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_INSERT` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_INSERT` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_INSERT` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_INSERT` bigint(20) unsigned NOT NULL,
  `COUNT_UPDATE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_UPDATE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_UPDATE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_UPDATE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_UPDATE` bigint(20) unsigned NOT NULL,
  `COUNT_DELETE` bigint(20) unsigned NOT NULL,
  `SUM_TIMER_DELETE` bigint(20) unsigned NOT NULL,
  `MIN_TIMER_DELETE` bigint(20) unsigned NOT NULL,
  `AVG_TIMER_DELETE` bigint(20) unsigned NOT NULL,
  `MAX_TIMER_DELETE` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `OBJECT` (`OBJECT_TYPE`,`OBJECT_SCHEMA`,`OBJECT_NAME`,`INDEX_NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

查看文档中对于该表的介绍:
26.12.16.8.2 The table_io_waits_summary_by_index_usage Table

table_io_waits_summary_by_index_usage表整合了所有索引的I/O操作信息该表的列分类(同table_io_waits_summary_by_table 相同),按照(OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME)这三个字段进行group。

指标分类:

COUNT_STAR, SUM_TIMER_WAIT, MIN_TIMER_WAIT, AVG_TIMER_WAIT, MAX_TIMER_WAIT
These columns aggregate all I/O operations. They are the same as the sum of the corresponding xxx_READ and xxx_WRITE columns.
对应xxx_READ 和 xxx_WRITE 列的和

COUNT_READ, SUM_TIMER_READ, MIN_TIMER_READ, AVG_TIMER_READ, MAX_TIMER_READ
These columns aggregate all read operations. They are the same as the sum of the corresponding xxx_FETCH columns.
对应xxx_FETCH 列的和

COUNT_WRITE, SUM_TIMER_WRITE, MIN_TIMER_WRITE, AVG_TIMER_WRITE, MAX_TIMER_WRITE
These columns aggregate all write operations. They are the same as the sum of the corresponding xxx_INSERT, xxx_UPDATE, and xxx_DELETE columns.
对应 xxx_INSERT,xxx_UPDATE,xxx_DELETE三个值的和

COUNT_FETCH, SUM_TIMER_FETCH, MIN_TIMER_FETCH, AVG_TIMER_FETCH, MAX_TIMER_FETCH
These columns aggregate all fetch operations.
所有的select操作次数

COUNT_INSERT, SUM_TIMER_INSERT, MIN_TIMER_INSERT, AVG_TIMER_INSERT, MAX_TIMER_INSERT
These columns aggregate all insert operations.
所有的insert操作次数

COUNT_UPDATE, SUM_TIMER_UPDATE, MIN_TIMER_UPDATE, AVG_TIMER_UPDATE, MAX_TIMER_UPDATE
These columns aggregate all update operations.
所有的update 操作次数

COUNT_DELETE, SUM_TIMER_DELETE, MIN_TIMER_DELETE, AVG_TIMER_DELETE, MAX_TIMER_DELETE
These columns aggregate all delete operations.
所有的delete 操作次数
.

对于INDEX_NAME列:
1. A value of PRIMARY indicates that table I/O used the primary index.
2. A value of NULL means that table I/O used no index.
3. Inserts are counted against INDEX_NAME = NULL

举例

1
2
3
4
5
6
7
8
9
10
<br></br>mysql&gt; select * from performance_schema.table_io_waits_summary_by_index_usage where object_schema='wstest' and object_name='test2' limit 10 ;
+-------------+---------------+-------------+------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------+-----------------+-----------------+-----------------+-----------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | COUNT_FETCH | SUM_TIMER_FETCH | MIN_TIMER_FETCH | AVG_TIMER_FETCH | MAX_TIMER_FETCH | COUNT_INSERT | SUM_TIMER_INSERT | MIN_TIMER_INSERT | AVG_TIMER_INSERT | MAX_TIMER_INSERT | COUNT_UPDATE | SUM_TIMER_UPDATE | MIN_TIMER_UPDATE | AVG_TIMER_UPDATE | MAX_TIMER_UPDATE | COUNT_DELETE | SUM_TIMER_DELETE | MIN_TIMER_DELETE | AVG_TIMER_DELETE | MAX_TIMER_DELETE |
+-------------+---------------+-------------+------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------+-----------------+-----------------+-----------------+-----------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+
| TABLE       | wstest        | test2       | PRIMARY    |          6 |      139617054 |        8265600 |       23269509 |       42271164 |          0 |              0 |              0 |              0 |              0 |           6 |       139617054 |         8265600 |        23269509 |        42271164 |           0 |               0 |               0 |               0 |               0 |            0 |                0 |                0 |                0 |                0 |            6 |        139617054 |          8265600 |         23269509 |         42271164 |            0 |                0 |                0 |                0 |                0 |
| TABLE       | wstest        | test2       | idx_name   |          7 |       69869781 |        6575211 |        9981081 |       23699394 |          7 |       69869781 |        6575211 |        9981081 |       23699394 |           0 |               0 |               0 |               0 |               0 |           7 |        69869781 |         6575211 |         9981081 |        23699394 |            0 |                0 |                0 |                0 |                0 |            0 |                0 |                0 |                0 |                0 |            0 |                0 |                0 |                0 |                0 |
| TABLE       | wstest        | test2       | NULL       |         25 |      249500088 |         836154 |        9979974 |       72511083 |         19 |       76699233 |         836154 |        4036491 |       28997865 |           6 |       172800855 |        10806903 |        28800081 |        72511083 |          19 |        76699233 |          836154 |         4036491 |        28997865 |            6 |        172800855 |         10806903 |         28800081 |         72511083 |            0 |                0 |                0 |                0 |                0 |            0 |                0 |                0 |                0 |                0 |
+-------------+---------------+-------------+------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------+-----------------+-----------------+-----------------+-----------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+
3 rows in set (0.00 sec)

可以看出,对于test2表,PRIMARY主键共使用了6次(COUNT_WRITE=6,即按照主键顺序写入了6行数据),idx_name供使用了7次(COUNT_READ=7,读了7行),再次读取后查看状态:

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
mysql&gt; explain select * from wstest.test2 where name='111xyz';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test2 | NULL       | ref  | idx_name      | idx_name | 93      | const |    6 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


mysql&gt; select * from wstest.test2 where name='111xyz';   
+----+--------+--------+------+-------+
| id | name   | status | addr | addr2 |
+----+--------+--------+------+-------+
|  1 | 111xyz |    110 |      |     0 |
|  2 | 111xyz |    110 |      |     0 |
|  3 | 111xyz |    110 |      |     0 |
|  4 | 111xyz |    110 |      |     0 |
|  5 | 111xyz |      5 |      |     0 |
|  6 | 111xyz |    110 |      |     0 |
+----+--------+--------+------+-------+
6 rows in set (0.00 sec)

mysql&gt; select * from performance_schema.table_io_waits_summary_by_index_usage where object_schema='wstest' and object_name='test2' limit 10  ;
+-------------+---------------+-------------+------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------+-----------------+-----------------+-----------------+-----------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ | SUM_TIMER_READ | MIN_TIMER_READ | AVG_TIMER_READ | MAX_TIMER_READ | COUNT_WRITE | SUM_TIMER_WRITE | MIN_TIMER_WRITE | AVG_TIMER_WRITE | MAX_TIMER_WRITE | COUNT_FETCH | SUM_TIMER_FETCH | MIN_TIMER_FETCH | AVG_TIMER_FETCH | MAX_TIMER_FETCH | COUNT_INSERT | SUM_TIMER_INSERT | MIN_TIMER_INSERT | AVG_TIMER_INSERT | MAX_TIMER_INSERT | COUNT_UPDATE | SUM_TIMER_UPDATE | MIN_TIMER_UPDATE | AVG_TIMER_UPDATE | MAX_TIMER_UPDATE | COUNT_DELETE | SUM_TIMER_DELETE | MIN_TIMER_DELETE | AVG_TIMER_DELETE | MAX_TIMER_DELETE |
+-------------+---------------+-------------+------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------+-----------------+-----------------+-----------------+-----------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+
| TABLE       | wstest        | test2       | PRIMARY    |          6 |      139617054 |        8265600 |       23269509 |       42271164 |          0 |              0 |              0 |              0 |              0 |           6 |       139617054 |         8265600 |        23269509 |        42271164 |           0 |               0 |               0 |               0 |               0 |            0 |                0 |                0 |                0 |                0 |            6 |        139617054 |          8265600 |         23269509 |         42271164 |            0 |                0 |                0 |                0 |                0 |
| TABLE       | wstest        | test2       | idx_name   |         13 |      164544849 |        6575211 |       12657069 |       74654973 |         13 |      164544849 |        6575211 |       12657069 |       74654973 |           0 |               0 |               0 |               0 |               0 |          13 |       164544849 |         6575211 |        12657069 |        74654973 |            0 |                0 |                0 |                0 |                0 |            0 |                0 |                0 |                0 |                0 |            0 |                0 |                0 |                0 |                0 |
| TABLE       | wstest        | test2       | NULL       |         25 |      249500088 |         836154 |        9979974 |       72511083 |         19 |       76699233 |         836154 |        4036491 |       28997865 |           6 |       172800855 |        10806903 |        28800081 |        72511083 |          19 |        76699233 |          836154 |         4036491 |        28997865 |            6 |        172800855 |         10806903 |         28800081 |         72511083 |            0 |                0 |                0 |                0 |                0 |            0 |                0 |                0 |                0 |                0 |
+-------------+---------------+-------------+------------+------------+----------------+----------------+----------------+----------------+------------+----------------+----------------+----------------+----------------+-------------+-----------------+-----------------+-----------------+-----------------+-------------+-----------------+-----------------+-----------------+-----------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+--------------+------------------+------------------+------------------+------------------+
3 rows in set (0.00 sec)


得到,再次使用idx_name查询数据时,返回了6条数据,在统计结果中,idx_name的COUNT_STAR从7增加到了13(均为COUNT_READ,COUNT_READ从7变为13),据此,可使用该统计信息判断索引使用的频次。

sys库中的统计信息

sys数据库中整合了performance_schema数据库的指标,例如:

1
2
3
4
5
6
7
8
mysql&gt; show create table sys.schema_index_statistics \G    
*************************** 1. row ***************************
                View: schema_index_statistics
         Create View: CREATE ALGORITHM=MERGE DEFINER=`mysql.sys`@`localhost` SQL SECURITY INVOKER VIEW `sys`.`schema_index_statistics` (`table_schema`,`table_name`,`index_name`,`rows_selected`,`select_latency`,`rows_inserted`,`insert_latency`,`rows_updated`,`update_latency`,`rows_deleted`,`delete_latency`) AS select `performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_SCHEMA` AS `table_schema`,`performance_schema`.`table_io_waits_summary_by_index_usage`.`OBJECT_NAME` AS `table_name`,`performance_schema`.`table_io_waits_summary_by_index_usage`.`INDEX_NAME` AS `index_name`,`performance_schema`.`table_io_waits_summary_by_index_usage`.`COUNT_FETCH` AS `rows_selected`,`sys`.`format_time`(`performance_schema`.`table_io_waits_summary_by_index_usage`.`SUM_TIMER_FETCH`) AS `select_latency`,`performance_schema`.`table_io_waits_summary_by_index_usage`.`COUNT_INSERT` AS `rows_inserted`,`sys`.`format_time`(`performance_schema`.`table_io_waits_summary_by_index_usage`.`SUM_TIMER_INSERT`) AS `insert_latency`,`performance_schema`.`table_io_waits_summary_by_index_usage`.`COUNT_UPDATE` AS `rows_updated`,`sys`.`format_time`(`performance_schema`.`table_io_waits_summary_by_index_usage`.`SUM_TIMER_UPDATE`) AS `update_latency`,`performance_schema`.`table_io_waits_summary_by_index_usage`.`COUNT_DELETE` AS `rows_deleted`,`sys`.`format_time`(`performance_schema`.`table_io_waits_summary_by_index_usage`.`SUM_TIMER_DELETE`) AS `delete_latency` from `performance_schema`.`table_io_waits_summary_by_index_usage` where (`performance_schema`.`table_io_waits_summary_by_index_usage`.`INDEX_NAME` is not null) order by `performance_schema`.`table_io_waits_summary_by_index_usage`.`SUM_TIMER_WAIT` desc
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

与之对应的sys.x$schema_index_statistics则提供了更为精确的value,方便工具进行调用。

举例

查询:

1
2
3
4
5
6
7
8
9
10
mysql&gt; select * from sys.schema_index_statistics where table_schema='wstest' and table_name='test2';
+--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| table_schema | table_name | index_name | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
+--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| wstest       | test2      | PRIMARY    |             0 | 0 ps           |             0 | 0 ps           |            6 | 139.62 us      |            0 | 0 ps           |
| wstest       | test2      | idx_name   |             7 | **69.87 us**       |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
+--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
2 rows in set (0.00 sec)


对应之前的再次按照idx_name查询,再次查询sys的统计信息:

1
2
3
4
5
6
7
8
9
mysql&gt; select * from sys.schema_index_statistics where table_schema='wstest' and table_name='test2';
+--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| table_schema | table_name | index_name | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
+--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
| wstest       | test2      | idx_name   |            13 | **164.54 us**      |             0 | 0 ps           |            0 | 0 ps           |            0 | 0 ps           |
| wstest       | test2      | PRIMARY    |             0 | 0 ps           |             0 | 0 ps           |            6 | 139.62 us      |            0 | 0 ps           |
+--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
2 rows in set (0.00 sec)

comments powered by Disqus