【MySQL】查询数据库表、列、索引、事务信息等

 

一. 表信息

?

1 2 3 4 5 6 7 8 9

select table_schema as '数据库', table_name as '表名', table_rows as '记录数', truncate(data_length/1024/1024, 2) as '数据容量(MB)', truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables where table_schema='dbname' order by table_rows desc, index_length desc;

 

table_schema: 记录数据库名
table_name: 记录数据表名
engine : 存储引擎
table_rows: 关于表的粗略行估计
data_length : 记录表的大小（单位字节）
index_length : 记录表的索引的大小
row_format: 可以查看数据表是否压缩过

 

二. 列信息

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '数据库名'
ORDER BY TABLE_NAME DESC ;

 

三. 索引信息

SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = '数据库名' and table_name='表名'
ORDER BY INDEX_NAME DESC,SEQ_IN_INDEX ;

 

四. 事务

执行时间超过1秒的事务语句

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>1