mysql 表情况统计

1、查询数据库下所有表名，行数，数据大小，索引大小、碎片大小

select table_name
       ,table_rows
	   ,concat(truncate(DATA_LENGTH/1024/1024,2),' MB') as data_size
	   ,concat(truncate(INDEX_LENGTH /1024/1024,2),' MB') as index_size
		 ,concat(truncate(data_free/1024/1024,2),' MB') as data_free
from information_schema.tables
where TABLE_SCHEMA = '数据库名称'
order by table_rows desc

2、查看未关闭事物

select
a.trx_id,
a.trx_state,
a.trx_started,
a.trx_query,
b.id,
b.user,
b.db,
b.command,
b.time,
b.state,
b.info,
c.processlist_user,
c.processlist_host,
c.processlist_db,
d.sql_text
from
information_schema.innodb_trx a
left join information_schema.processlist b on a.trx_mysql_thread_id = b.id
and b.command = 'sleep'
left join performance_schema.threads c on b.id = c.processlist_id
left join performance_schema.events_statements_current d on d.thread_id = c.thread_id;

3、查看当前所有运行的事物

SELECT * FROM information_schema.INNODB_TRX