schema_index_statistics,x$schema_index_statistics

• 索引统计信息，默认按照使用索引执行增删改查操作总的等待时间(执行时间)降序排序，数据来源：table_io_waits_summary_by_index_usage

• schema_index_statistics和x$schema_index_statistics视图字段含义如下：

  • TABLE_SCHEMA：包含索引的表所在的schema名称
  • TABLE_NAME：包含索引的表名
  • INDEX_NAME：索引的名称
  • rows_selected：使用索引执行读操作的总数据行数
  • select_latency：使用索引执行读操作的总等待时间(执行时间)
  • rows_inserted：插入索引的总行数
  • insert_latency：插入索引行的总等待时间(执行时间)
  • rows_updated：索引更新的总行数
  • update_latency：索引更新行的总等待时间(执行时间)
  • rows_deleted：从索引中删除的总行数
  • delete_latency：从索引中删除行的总等待时间(执行时间)

• 视图定义语句

# schema_index_statistics
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW 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 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$schema_index_statistics
CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW x$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 OBJECT_SCHEMA AS table_schema,
      OBJECT_NAME AS table_name,
      INDEX_NAME as index_name,
      COUNT_FETCH AS rows_selected,
      SUM_TIMER_FETCH AS select_latency,
      COUNT_INSERT AS rows_inserted,
      SUM_TIMER_INSERT AS insert_latency,
      COUNT_UPDATE AS rows_updated,
      SUM_TIMER_UPDATE AS update_latency,
      COUNT_DELETE AS rows_deleted,
      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;

• 视图查询信息示例

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)

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)

• 参考链接：https://dev.mysql.com/doc/refman/5.7/en/sys-schema-index-statistics.html

---

上一篇： schema_auto_increment_columns视图 | 下一篇： schema_object_overview视图