mysql 常用查询语句

查看当前正在运行的sql语句执行最时间：
select * from information_schema.PROCESSLIST where info is not null order by time desc ;

查看当前有那些表在使用：
show OPEN TABLES where In_use > 0;

查看与innodb事务先关的语句：
SELECT FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT from INFORMATION_SCHEMA.INNODB_TRX;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

查看当前数据库的链接情况：
SELECT count(*) AS count,USER,db,SUBSTRING_INDEX(HOST, ‘:‘, 1) AS ip FROM information_schema. PROCESSLIST GROUP BY USER,db,ip ORDER BY count desc;

查看数据库表的碎片情况：
select round(sum(data_length/1024/1024),2) as data_length_MB,
round(sum(index_length/1024/1024),2) as index_length_MB ,
round(sum(data_free/1024/1024),2) as data_free_MB ,table_name
from information_schema.tables where TABLE_SCHEMA= ‘db_name‘ group by table_name order by 3 desc;

查看死锁，未提交事物、CHECKPIONT、BUFFER POOL、THREAD等innodb的内部信息：
show engine innodb status；