统计信息查询视图|全方位认识 sys 系统库

灰太狼 2022-12-25 06:59 186阅读 0赞

在上一篇[《会话和锁信息查询视图|全方位认识 sys 系统库》][sys]中，我们介绍了如何使用 sys 系统库总的视图来查询会话状态信息以及锁等待信息，本期的内容先给大家介绍查询表和索引相关的统计信息快捷视图。下面请跟随我们一起开始 sys 系统库的系统学习之旅吧。

PS：由于本文中所提及的视图功能的特殊性（DBA日常工作中可能需要查询一些统计信息做一些数据分析使用），所以下文中会列出部分视图中的select语句文本，以便大家更直观地学习它们。

### 01 ###

### schema\_auto\_increment\_columns ###

在所有数据库中（排除系统字典库 mysql，sys，INFORMATION\_SCHEMA，performance\_schema)查找带有自增列的基表及其相关的信息，默认按照自增值使用率和自增列类型最大值进行降序排序。数据来源：INFORMATION\_SCHEMA的COLUMNS、TABLES

*  此视图在MySQL 5.7.9中新增

视图查询语句文本

SELECT TABLE_SCHEMA,
      TABLE_NAME,
      COLUMN_NAME,
      DATA_TYPE,
      COLUMN_TYPE,
      (LOCATE('unsigned', COLUMN_TYPE) = 0) AS is_signed,
      (LOCATE('unsigned', COLUMN_TYPE) > 0) AS is_unsigned,
      (
          CASE DATA_TYPE
            WHEN 'tinyint' THEN 255
            WHEN 'smallint' THEN 65535
            WHEN 'mediumint' THEN 16777215
            WHEN 'int' THEN 4294967295
            WHEN 'bigint' THEN 18446744073709551615
          END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
      ) AS max_value,
      AUTO_INCREMENT,
      AUTO_INCREMENT / (
        CASE DATA_TYPE
          WHEN 'tinyint' THEN 255
          WHEN 'smallint' THEN 65535
          WHEN 'mediumint' THEN 16777215
          WHEN 'int' THEN 4294967295
          WHEN 'bigint' THEN 18446744073709551615
        END >> IF(LOCATE('unsigned', COLUMN_TYPE) > 0, 0, 1)
      ) AS auto_increment_ratio
    FROM INFORMATION_SCHEMA.COLUMNS
    INNER JOIN INFORMATION_SCHEMA.TABLES USING (TABLE_SCHEMA, TABLE_NAME)
    WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys', 'INFORMATION_SCHEMA', 'performance_schema')
    AND TABLE_TYPE='BASE TABLE'
    AND EXTRA='auto_increment'
    ORDER BY auto_increment_ratio DESC, max_value;

下面我们看看使用该视图查询返回的结果

admin@localhost : sys 11:11:58> select * from schema_auto_increment_columns limit 5;
    +--------------+------------+-------------+-----------+------------------+-----------+-------------+------------+----------------+----------------------+
    | table_schema | table_name | column_name | data_type | column_type      | is_signed | is_unsigned | max_value  | auto_increment | auto_increment_ratio |
    +--------------+------------+-------------+-----------+------------------+-----------+-------------+------------+----------------+----------------------+
    | sbtest      | sbtest1    | id          | int      | int(10) unsigned |        0 |          1 | 4294967295 |      10713891 |              0.0025 |
    | sbtest      | sbtest2    | id          | int      | int(10) unsigned |        0 |          1 | 4294967295 |      10710865 |              0.0025 |
    | sbtest      | sbtest3    | id          | int      | int(10) unsigned |        0 |          1 | 4294967295 |      10714919 |              0.0025 |
    | sbtest      | sbtest4    | id          | int      | int(10) unsigned |        0 |          1 | 4294967295 |      10714039 |              0.0025 |
    | sbtest      | sbtest5    | id          | int      | int(10) unsigned |        0 |          1 | 4294967295 |      10713075 |              0.0025 |
    +--------------+------------+-------------+-----------+------------------+-----------+-------------+------------+----------------+----------------------+
    5 rows in set (1.50 sec)

视图字段含义如下：

*  TABLE\_SCHEMA：包含自增值的表的schema名称
 *  TABLE\_NAME：包含AUTO\_INCREMENT值的表名
 *  column\_name：AUTO\_INCREMENT值的列名称
 *  data\_type：自增列的数据类型
 *  COLUMN\_TYPE：自增列的列属性类型，即在数据类型基础上加上一些其他信息。例如：对于bigint(20) unsigned，整个信息就被称为列属性类型，而数据类型只是指的bigint
 *  is\_signed：列类型是否是有符号的
 *  is\_unsigned：列类型是否是无符号的
 *  MAX\_VALUE：自增列的最大自增值
 *  auto\_increment：自增列的当前AUTO\_INCREMENT属性值
 *  auto\_increment\_ratio：自增列当前使用的自增值与自增列最大自增值的比例，表示当前自增列的使用率

### 02 ###

### schema\_index\_statistics,x$schema\_index\_statistics ###

索引统计信息，默认按照使用索引执行增删改查操作的总延迟时间(执行时间)降序排序，数据来源：performance\_schema.table\_io\_waits\_summary\_by\_index\_usage

视图查询语句文本

# 不带x$前缀的视图
    SELECT OBJECT_SCHEMA AS table_schema,
      OBJECT_NAME AS table_name,
      INDEX_NAME as index_name,
      COUNT_FETCH AS rows_selected,
      sys.format_time(SUM_TIMER_FETCH) AS select_latency,
      COUNT_INSERT AS rows_inserted,
      sys.format_time(SUM_TIMER_INSERT) AS insert_latency,
      COUNT_UPDATE AS rows_updated,
      sys.format_time(SUM_TIMER_UPDATE) AS update_latency,
      COUNT_DELETE AS rows_deleted,
      sys.format_time(SUM_TIMER_INSERT) AS delete_latency
    FROM performance_schema.table_io_waits_summary_by_index_usage
    WHERE index_name IS NOT NULL
    ORDER BY sum_timer_wait DESC;
    
    # 带x$前缀的视图查询语句与不带x$前缀的视图查询语句相比，只是少了单位格式化函数
    ......

下面我们看看使用该视图查询返回的结果

# 不带x$前缀的视图
    admin@localhost : sys 11:19:43> select * from schema_index_statistics limit 5;
    +--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
    | table_schema | table_name | index_name | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
    +--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
    | xiaoboluo    | test      | PRIMARY    |          1159 | 3.57 s        |            0 | 0 ps          |            0 | 0 ps          |            0 | 0 ps          |
    | sys          | sys_config | PRIMARY    |            1 | 62.53 ms      |            0 | 0 ps          |            0 | 0 ps          |            0 | 0 ps          |
    | sbtest      | sbtest1    | i_c        |            20 | 31.43 ms      |            0 | 0 ps          |            0 | 0 ps          |            0 | 0 ps          |
    | xiaoboluo    | test      | i_test    |          400 | 3.77 ms        |            0 | 0 ps          |            0 | 0 ps          |            0 | 0 ps          |
    | luoxiaobo    | public_num | PRIMARY    |            0 | 0 ps          |            0 | 0 ps          |            0 | 0 ps          |            0 | 0 ps          |
    +--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
    5 rows in set (0.45 sec)
    
    # 带x$前缀的视图
    admin@localhost : sys 11:20:21> select * from x$schema_index_statistics limit 5;
    +--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
    | table_schema | table_name | index_name | rows_selected | select_latency | rows_inserted | insert_latency | rows_updated | update_latency | rows_deleted | delete_latency |
    +--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
    | xiaoboluo    | test      | PRIMARY    |          1159 |  3573795058125 |            0 |              0 |            0 |              0 |            0 |              0 |
    | sys          | sys_config | PRIMARY    |            1 |    62528964375 |            0 |              0 |            0 |              0 |            0 |              0 |
    | sbtest      | sbtest1    | i_c        |            20 |    31429669125 |            0 |              0 |            0 |              0 |            0 |              0 |
    | xiaoboluo    | test      | i_test    |          400 |    3765146625 |            0 |              0 |            0 |              0 |            0 |              0 |
    | luoxiaobo    | public_num | PRIMARY    |            0 |              0 |            0 |              0 |            0 |              0 |            0 |              0 |
    +--------------+------------+------------+---------------+----------------+---------------+----------------+--------------+----------------+--------------+----------------+
    5 rows in set (0.00 sec)

视图字段含义如下：

*  TABLE\_SCHEMA：包含索引的表所在的schema名称
 *  TABLE\_NAME：包含索引的表名
 *  INDEX\_NAME：索引的名称
 *  rows\_selected：使用索引读的总数据行数
 *  select\_latency：使用索引读的总延迟时间(执行时间)
 *  rows\_inserted：插入索引的总行数
 *  insert\_latency：插入索引行的总延迟时间(执行时间)
 *  rows\_updated：索引更新的总行数
 *  update\_latency：索引更新行的总延迟时间(执行时间)
 *  rows\_deleted：从索引中删除的总行数
 *  delete\_latency：从索引中删除行的总延迟时间(执行时间)

### 03 ###

### schema\_object\_overview ###

每个schema中包含的表、视图、索引等对象的统计信息，默认按照schema名称和对象类型进行排序，数据来源：information\_schema的routines、tables、statistics、triggers、events

*  注意：对于具有大量数据库对象的MySQL实例，此视图可能需要很长时间才能执行完成

视图查询语句文本

SELECT ROUTINE_SCHEMA AS db, ROUTINE_TYPE AS object_type, COUNT(*) AS count FROM information_schema.routines GROUP BY ROUTINE_SCHEMA, ROUTINE_TYPE
    UNION
    SELECT TABLE_SCHEMA, TABLE_TYPE, COUNT(*) FROM information_schema.tables GROUP BY TABLE_SCHEMA, TABLE_TYPE
    UNION
    SELECT TABLE_SCHEMA, CONCAT('INDEX (', INDEX_TYPE, ')'), COUNT(*) FROM information_schema.statistics GROUP BY TABLE_SCHEMA, INDEX_TYPE
    UNION
    SELECT TRIGGER_SCHEMA, 'TRIGGER', COUNT(*) FROM information_schema.triggers GROUP BY TRIGGER_SCHEMA
    UNION
    SELECT EVENT_SCHEMA, 'EVENT', COUNT(*) FROM information_schema.events GROUP BY EVENT_SCHEMA
    ORDER BY DB, OBJECT_TYPE;

下面我们看看使用该视图查询返回的结果

视图字段含义如下：

*  db：schema名称
 *  OBJECT\_TYPE：数据库对象类型，有效值为：BASE TABLE，INDEX（index\_type），EVENT，FUNCTION，PROCEDURE，TRIGGER，VIEW
 *  count：在每个schema下各个数据库对象的数量

### **04** ###

### **schema\_redundant\_indexes** ###

查找重复或冗余索引，数据来源：sys.x$schema\_flattened\_keys，该数据来源视图被称作schema\_redundant\_indexes视图的辅助视图

*  schema\_redundant\_indexes视图在MySQL 5.7.9中新增

下面我们看看使用该视图查询返回的结果

admin@localhost : sys 11:21:13> select * from schema_redundant_indexes limit 1\G;
    *************************** 1. row ***************************
              table_schema: test
                table_name: test
      redundant_index_name: i_id
      redundant_index_columns: id
      redundant_index_non_unique: 1
      dominant_index_name: i_id_id2
    dominant_index_columns: id,id2
    dominant_index_non_unique: 1
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `test`.`test` DROP INDEX `i_id`
    1 row in set (0.01 sec)

视图字段含义如下：

*  TABLE\_SCHEMA：包含冗余或重复索引的表对应的schema名称
 *  TABLE\_NAME：包含冗余或重复索引的表名
 *  redundant\_index\_name：冗余或重复的索引名称
 *  redundant\_index\_columns：冗余或重复索引中的列名
 *  redundant\_index\_non\_unique：冗余或重复索引中非唯一列的数量
 *  dominant\_index\_name：与重复或冗余索引相比占据优势（最佳）的索引名称
 *  dominant\_index\_columns：占据优势（最佳）的索引中的列名
 *  dominant\_index\_non\_unique：占据优势（最佳）的索引中非唯一列的数量
 *  subpart\_exists：重复或冗余索引是否是前缀索引
 *  sql\_drop\_index：针对重复或冗余索引生成的drop index语句

### **05** ###

### **schema\_table\_statistics,x$schema\_table\_statistics** ###

查看表的统计信息，默认情况下按照增删改查操作的总表I/O延迟时间（执行时间，即也可以理解为是存在最多表I/O争用的表）降序排序，数据来源：performance\_schema.table\_io\_waits\_summary\_by\_table、sys.x$ps\_schema\_table\_statistics\_io

*  这些视图使用了一个辅助视图x$ps\_schema\_table\_statistics\_io

下面我们看看使用该视图查询返回的结果

# 不带x$前缀的视图
    admin@localhost : sys 11:52:25> select * from schema_table_statistics limit 1\G
    *************************** 1. row ***************************
    table_schema: xiaoboluo
      table_name: test
    total_latency: 2.10 m
    rows_fetched: 1561
    fetch_latency: 2.08 m
    rows_inserted: 1159
    insert_latency: 865.33 ms
    rows_updated: 0
    update_latency: 0 ps
    rows_deleted: 0
    delete_latency: 0 ps
    io_read_requests: 43
          io_read: 178.86 KiB
    io_read_latency: 15.00 ms
    io_write_requests: 10
        io_write: 160.00 KiB
    io_write_latency: 76.24 us
    io_misc_requests: 42
    io_misc_latency: 9.38 ms
    1 row in set (0.03 sec)
    
    # 带x$前缀的视图
    admin@localhost : sys 11:52:28> select * from x$schema_table_statistics limit 1\G;
    *************************** 1. row ***************************
    table_schema: xiaoboluo
      table_name: test
    total_latency: 125711643303375
    rows_fetched: 1561
    fetch_latency: 124846318302750
    rows_inserted: 1159
    insert_latency: 865325000625
    rows_updated: 0
    update_latency: 0
    rows_deleted: 0
    delete_latency: 0
    io_read_requests: 43
          io_read: 183148
    io_read_latency: 15001512375
    io_write_requests: 10
        io_write: 163840
    io_write_latency: 76237125
    io_misc_requests: 42
    io_misc_latency: 9384933000
    1 row in set (0.02 sec)

视图字段含义如下：

*  TABLE\_SCHEMA：包含TABLE\_NAME字段的表所在的schema名称
 *  TABLE\_NAME：表名
 *  total\_latency：表的I/O事件的总延迟时间(执行时间)，针对表增删改查操作
 *  rows\_fetched：表读取操作的总数据行数，针对表查询操作
 *  fetch\_latency：表select操作的I/O事件的总延迟时间(执行时间)，针对表查询操作
 *  rows\_inserted：表插入操作的总数据行数，针对表插入操作
 *  insert\_latency：表insert操作的I/O事件的延迟时间(执行时间)，针对表插入操作
 *  rows\_updated：表更新操作的总数据行数，针对表更新操作
 *  update\_latency：表更新操作的I/O事件的总延迟时间(执行时间)，针对表更新操作
 *  rows\_deleted：表删除操作的总数据行数，针对表删除操作
 *  delete\_latency：表删除操作的I/O事件的总延迟时间(执行时间)，针对表删除操作
 *  io\_read\_requests：表读取操作总请求次数，针对表.ibd和.frm文件的读I/O操作
 *  io\_read：表读操作相关的所有文件读取操作的总字节数，针对表.ibd和.frm文件的读I/O操作
 *  io\_read\_latency：表读操作相关的所有文件读取操作的总延迟时间(执行时间)，针对表.ibd和.frm文件的读I/O操作
 *  io\_write\_requests：表写操作的总请求次数，针对表.ibd和.frm文件的写I/O操作
 *  io\_write：表写操作相关的所有文件写操作的总字节数，针对表.ibd和.frm文件的写I/O操作
 *  io\_write\_latency：表写操作相关的所有文件写操作的总延迟时间(执行时间)，针对表.ibd和.frm文件的写I/O操作
 *  io\_misc\_requests：表其他各种混杂操作相关的所有文件的I/O请求总次数，针对表.ibd和.frm文件的其他混杂I/O操作
 *  io\_misc\_latency：表其他各种混杂操作相关的所有文件的I/O请求的总延迟时间(执行时间)，针对表.ibd和.frm文件的其他混杂I/O操作

### **06** ###

### **schema\_table\_statistics\_with\_buffer,x$schema\_table\_statistics\_with\_buffer** ###

查询表的统计信息，其中还包括InnoDB缓冲池统计信息，默认情况下按照增删改查操作的总表I/O延迟时间（执行时间，即也可以理解为是存在最多表I/O争用的表）降序排序，数据来源：performance\_schema.table\_io\_waits\_summary\_by\_table、sys.x$ps\_schema\_table\_statistics\_io、sys.x$innodb\_buffer\_stats\_by\_table

*  这些视图使用了辅助视图sys.x$ps\_schema\_table\_statistics\_io

下面我们看看使用该视图查询返回的结果

# 不带x$前缀的视图
    admin@localhost : sys 12:36:57> select * from schema_table_statistics_with_buffer limit 1\G;
    *************************** 1. row ***************************
              table_schema: xiaoboluo
                table_name: test
              rows_fetched: 1561
            fetch_latency: 2.08 m
            rows_inserted: 1159
            insert_latency: 865.33 ms
              rows_updated: 0
            update_latency: 0 ps
              rows_deleted: 0
            delete_latency: 0 ps
          io_read_requests: 48
                  io_read: 179.29 KiB
          io_read_latency: 15.02 ms
        io_write_requests: 10
                  io_write: 160.00 KiB
          io_write_latency: 76.24 us
          io_misc_requests: 47
          io_misc_latency: 9.47 ms
        innodb_buffer_allocated: 112.00 KiB
        innodb_buffer_data: 48.75 KiB
        innodb_buffer_free: 63.25 KiB
      innodb_buffer_pages: 7
    innodb_buffer_pages_hashed: 0
    innodb_buffer_pages_old: 0
    innodb_buffer_rows_cached: 1162
    1 row in set (2.21 sec)
    
    # 带x$前缀的视图
    admin@localhost : sys 12:37:35> select * from x$schema_table_statistics_with_buffer limit 1\G;
    *************************** 1. row ***************************
              table_schema: xiaoboluo
                table_name: test
              rows_fetched: 1561
            fetch_latency: 124846318302750
            rows_inserted: 1159
            insert_latency: 865325000625
              rows_updated: 0
            update_latency: 0
              rows_deleted: 0
            delete_latency: 0
          io_read_requests: 48
                  io_read: 183595
          io_read_latency: 15019373250
        io_write_requests: 10
                  io_write: 163840
          io_write_latency: 76237125
          io_misc_requests: 47
          io_misc_latency: 9465938250
    innodb_buffer_allocated: 114688
        innodb_buffer_data: 49917
        innodb_buffer_free: 64771
      innodb_buffer_pages: 7
    innodb_buffer_pages_hashed: 0
    innodb_buffer_pages_old: 0
    innodb_buffer_rows_cached: 1162
    1 row in set (2.12 sec)

视图字段含义如下：

*  表相关的统计信息字段的含义与视图schema\_table\_statistics的字段含义相同，这里省略，详见schema\_table\_statistics,x$schema\_table\_statistics 视图解释部分
 *  innodb\_buffer\_allocated：当前已分配给表的buffer pool总字节数
 *  innodb\_buffer\_data：当前已分配给表的数据部分使用的buffer pool字节总数
 *  innodb\_buffer\_free：当前已分配给表的非数据部分使用的buffer pool字节总数(即空闲页所在的字节数，计算公式：innodb\_buffer\_allocated - innodb\_buffer\_data)
 *  innodb\_buffer\_pages：当前已分配给表的buffer pool总页数
 *  innodb\_buffer\_pages\_hashed：当前已分配给表的自适应hash索引页总数
 *  innodb\_buffer\_pages\_old：当前已分配给表的旧页总数（位于LRU列表中的旧块子列表中的页数）
 *  innodb\_buffer\_rows\_cached：buffer pool中为表缓冲的总数据行数

### 07 ###

### schema\_unused\_indexes ###

查看不活跃的索引（没有任何事件发生的索引，这表示该索引从未使用过），默认情况下按照schema名称和表名进行排序。数据来源：performance\_schema.table\_io\_waits\_summary\_by\_index\_usage

*  该视图在server启动之后运行足够长的时间之后，所查询出的数据才比较适用，否则该视图查询的数据可能并不十分可靠，因为统计的数据可能并不精确，有一部分业务查询逻辑可能还来不及查询

视图查询语句文本

SELECT object_schema,
      object_name,
      index_name
    FROM performance_schema.table_io_waits_summary_by_index_usage
    WHERE index_name IS NOT NULL
    AND count_star = 0
    AND object_schema != 'mysql'
    AND index_name != 'PRIMARY'
    ORDER BY object_schema, object_name;

下面我们看看使用该视图查询返回的结果

视图字段含义如下：

*  object\_schema：schema名称
 *  OBJECT\_NAME：表名
 *  INDEX\_NAME：未使用的索引名称

本期内容就介绍到这里，本期内容参考链接如下：

*  https://dev.mysql.com/doc/refman/5.7/en/sys-schema-unused-indexes.html
 *  https://dev.mysql.com/doc/refman/5.7/en/sys-schema-auto-increment-columns.html
 *  https://dev.mysql.com/doc/refman/5.7/en/sys-schema-index-statistics.html
 *  https://dev.mysql.com/doc/refman/5.7/en/sys-schema-object-overview.html
 *  https://dev.mysql.com/doc/refman/5.7/en/sys-schema-redundant-indexes.html
 *  https://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-statistics.html
 *  https://dev.mysql.com/doc/refman/5.7/en/sys-schema-table-statistics-with-buffer.html

**| 作者简介**

### **罗小波·数据库技术专家** ###

《千金良方——MySQL性能优化金字塔法则》、《数据生态：MySQL复制技术与生产实践》作者之一。熟悉MySQL体系结构，擅长数据库的整体调优，喜好专研开源技术，并热衷于开源技术的推广，在线上线下做过多次公开的数据库专题分享，发表过近100篇数据库相关的研究文章。

全文完。

Enjoy MySQL :)

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

![format_png][]

[sys]: http://mp.weixin.qq.com/s?__biz=MzU0MTczNzA1OA%3D%3D&chksm=fb242a64cc53a3723c91aa2ed307df41387ea0bca71907a911c82fc0c365abcdb7028fe385c3&idx=1&mid=2247484265&scene=21&sn=9b3b0c934bb6f820cbb8b044ae8802b6#wechat_redirect
[format_png]: /images/20221120/6129b1b2f08e40549526357172433022.png