统计信息查询视图|全方位认识 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;
下面我们看看使用该视图查询返回的结果
admin@localhost : sys 11:20:27> select * from schema_object_overview limit 10;
+--------------------+---------------+-------+
| db | object_type | count |
+--------------------+---------------+-------+
| information_schema | SYSTEM VIEW | 61 |
| luoxiaobo | BASE TABLE | 3 |
| luoxiaobo | INDEX (BTREE) | 3 |
| mysql | BASE TABLE | 31 |
| mysql | INDEX (BTREE) | 69 |
| performance_schema | BASE TABLE | 87 |
| qfsys | BASE TABLE | 1 |
| qfsys | INDEX (BTREE) | 1 |
| sbtest | BASE TABLE | 8 |
| sbtest | INDEX (BTREE) | 17 |
+--------------------+---------------+-------+
10 rows in set (0.27 sec)
视图字段含义如下：
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;
下面我们看看使用该视图查询返回的结果
admin@localhost : sys 12:40:28> select * from schema_unused_indexes limit 3;
+---------------+-------------+-------------------+
| object_schema | object_name | index_name |
+---------------+-------------+-------------------+
| luoxiaobo | public_num | public_name_index |
| sbtest | sbtest1 | k_1 |
| sbtest | sbtest2 | k_2 |
+---------------+-------------+-------------------+
3 rows in set (0.00 sec)
视图字段含义如下：
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
| 作者简介
罗小波·沃趣科技高级数据库技术专家
IT从业多年，历任运维工程师，高级运维工程师，运维经理，数据库工程师，曾参与版本发布系统，轻量级监控系统，运维管理平台，数据库管理平台的设计与编写，熟悉MySQL的体系结构时，InnoDB存储引擎，喜好专研开源技术，追求完美。
相关链接
更多干货，欢迎来撩~