主要观点：系统表由 DBMS 管理，可了解数据库内部工作情况，本文介绍了 MySQL、Oracle、SQL Server 和 PostgreSQL 数据库的系统表及其用例。
关键信息：

• 各数据库针对不同需求有相应的查询语句，如 MySQL 可查看表大小、索引、表关系、运行查询等；Oracle 可识别大表、优化索引等；SQL Server 可找大表、审查索引等；PostgreSQL 可估计表大小、揭示索引等。
  重要细节：

• MySQL 查询语句及用途：

  • 查看表大小及行计数：SELECT table_schema AS 'Database', table_name AS 'Table', table_rows AS 'Row Count', ROUND((data_length + index_length) / (1024 * 1024), 2) AS 'Total Size (MB)' FROM information_schema.TABLES WHERE table_schema NOT IN ('performance_schema', 'sys’,’mysql', 'information_schema') ORDER BY table_rows DESC;
  • 检查表索引：SELECT table_schema, table_name, index_name, column_name, non_unique FROM information_schema.statistics WHERE table_schema NOT IN ('performance_schema', 'sys’,’mysql', 'information_schema') ORDER BY table_name, index_name, seq_in_index;
  • 理解表关系等：SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE referenced_table_name IS NOT NULL ORDER BY table_name, constraint_name;等。

• Oracle 查询语句及用途：

  • 识别大表：SELECT owner, table_name, num_rows FROM all_tables WHERE owner NOT IN ('SYS', 'SYSTEM') ORDER BY num_rows DESC;
  • 优化索引：SELECT ind.owner, ind.index_name, ind.table_name, col.column_name, col.column_position FROM all_indexes ind JOIN all_ind_columns col ON ind.index_name = col.index_name AND ind.owner = col.index_owner WHERE ind.owner NOT IN ('SYS', 'SYSTEM') ORDER BY ind.table_name, ind.index_name, col.column_position;等。

• SQL Server 查询语句及用途：

  • 找大表：SELECT t.name AS TableName, SUM(p.rows) AS RowCount FROM sys.tables t JOIN sys.partitions p ON t.object_id = p.object_id WHERE p.index_id IN (0, 1) GROUP BY t.name ORDER BY RowCount DESC;
  • 审查索引：SELECT tbl.name AS TableName, idx.name AS IndexName, col.name AS ColumnName, idx.type_desc AS IndexType FROM sys.tables tbl JOIN sys.indexes idx ON tbl.object_id = idx.object_id JOIN sys.index_columns idx_col ON idx.object_id = idx_col.object_id AND idx.index_id = idx_col.index_id JOIN sys.columns col ON tbl.object_id = col.object_id AND idx_col.column_id = col.column_id WHERE idx.is_primary_key = 0 AND idx.is_unique_constraint = 0 ORDER BY tbl.name, idx.name;等。

• PostgreSQL 查询语句及用途：

  • 估计表大小：SELECT relname AS "Table Name", n_live_tup AS "Estimated Rows" FROM pg_stat_user_tables ORDER BY n_live_tup DESC;
  • 揭示索引：SELECT t.relname AS TableName, i.relname AS IndexName, a.attname AS ColumnName FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) ORDER BY t.relname, i.relname;等。

  结论：将这些查询纳入常规维护例程，可在数据库增长时做出数据驱动的决策，且运行复杂系统查询时应避开高峰时段以减少对生产系统的性能影响。