当进入一个新环境，需要对数据库整体有个大致了解，就需要我们dba做个大概数据字典。
下面提供一个简单的数据字典统计sql，可以将结果导到excel，然后结合excel的图形，一个小时内，就能大致了解整个数据库的概要。
sql语句如下(巧用information_schema数据库)：
SELECT t1.table_schema,t1.table_name,`ENGINE`,table_rows,CAST(data_length/1024.0/1024.0 AS DECIMAL(10,2)) `data_size(M)`,
CAST(index_length/1024.0/1024.0 AS DECIMAL(10,2)) `index_size(M)`,
t2.ct col_count,t3.ct idx_count,create_time,table_comment
FROM information_schema.tables t1
LEFT JOIN
-- 字段总数
(SELECT table_name,COUNT(1) ct FROM information_schema.columns
GROUP BY table_name
) t2
ON t1.table_name=t2.table_name
LEFT JOIN
-- 索引总数
(SELECT table_name,COUNT(DISTINCT index_name) ct FROM information_schema.STATISTICS
GROUP BY table_name
) t3
ON t1.table_name=t3.table_name
WHERE t1.table_schema NOT IN ('mysql','information_schema','performance_schema')
ORDER BY t1.data_length DESC;
可得到结果如下图
本文出自 “原下” 博客，请务必保留此出处http://imysqldba.blog.51cto.com/1222376/1618234