查看事务
select * from information_schema.innodb_trx\G;
select * from information_schema.innodb_trx where trx_mysql_thread_id= \G;
查看processlist
select * from information_schema.processlist;
根据INFO查看连接
select * from information_schema.processlist where INFO like '%%';
根据用户名查看连接
select * from information_schema.processlist where user like '%%'
根据数据库查看连接
select * from information_schema.processlist where db like '%%';
根据command查看连接
select * from information_schema.processlist where COMMAND not in ('Sleep') ;
根据command查看连接,并按照time排序
select * from information_schema.processlist where COMMAND not in ('Sleep') order by TIME;
根据Host查看连接
select * from information_schema.processlist where Host like '%%' ;
生成kill的sql
select concat('kill ',ID,';')from information_schema.processlist where user like '%%' order by TIME ;
select concat('kill ',ID,';') from information_schema.processlist where COMMAND not in ('Sleep') and user='root' and INFO like 'select%' order by TIME\G;
查看慢日志
egrep '# Time|# Query_time' slow.log
egrep '# Query_time' slow.log
egrep '# Query_time' slow.log|awk '{print $3}'|sort -n
01.获取 mysql 指定表是否存在的 sql 语句：
（2）查看所有表
select * from information_schema.tables;
（2）查看指定表
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''\G;
（3）查看表的详细信息
SELECT * FROM information_schema.TABLES WHERE TABLE_NAME LIKE '%%'\G;
02.获取表字段的 sql 语句
select * from information_schema.columns;
03.获取表主键值的 sql 语句
select * from information_schema.key_column_usage
where table_schema='itpux' and table_name='dept';
04.获取表 check 约束的 sql 语句
select * from information_schema.table_constraints;
05.获取表索引的 sql 语句
select * from information_schema.statistics;
06.MySQL 查询某张表在哪个数据库里：
select * from tables where table_name='dept';
07.查 deptno 字段在哪个数据库的哪张表里:
select TABLE_SCHEMA,TABLE_NAME from columns where COLUMN_NAME='deptno';
08.查询 MySQL 中某个数据库中有多少张表：:
select COUNT(*) tables, table_schema from information_schema.TABLES
where table_schema = 'itpux' group by table_schema;
09.查询 MySQL 中某个数据库中某个表里有多少列：
select COUNT(*) from columns where TABLE_NAME='dept' and TABLE_SCHEMA='itpux';
10.获取所有表结构(tables)
select * from information_schema.TABLES where TABLE_SCHEMA='itpux';
11.获取表字段(columns)
select * from information_schema.COLUMNS where TABLE_SCHEMA='itpux' and
TABLE_NAME='dept'
12.获取表键值
select * from information_schema.KEY_COLUMN_USAGE where
TABLE_SCHEMA='itpux' and TABLE_NAME='dept'
13.获取表 Check 约束
select * from information_schema.TABLE_CONSTRAINTS where
TABLE_SCHEMA='itpux' and TABLE_NAME='dept'
14.获取表索引
select * from information_schema.STATISTICS where TABLE_SCHEMA='itpux' and
TABLE_NAME='dept';
15.查看是否存在myisam
select table_schema,table_name,engine from information_schema.tables where engine != 'innodb' and table_schema not in('mysql','information_schema','performance_schema');
