schema_object_overview

• 每个schema中所有对象的类型列表及其数量信息，默认按照schema名称和对象类型进行排序，数据来源：information_schema的routines、tables、statistics、triggers、events

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

• schema_object_overview视图字段含义如下：

  • db：schema名称
  • OBJECT_TYPE：对象类型，有效值为：BASE TABLE，INDEX（index_type），EVENT，FUNCTION，PROCEDURE，TRIGGER，VIEW
  • count：给定类型的schema中的各个对象的数量

• 视图定义语句

CREATE OR REPLACE
  ALGORITHM = TEMPTABLE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW schema_object_overview (
  db,  object_type,  count
) AS
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)

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

---

上一篇： schema_index_statistics,x$schema_index_statistics视图 |

下一篇： schema_redundant_indexes视图