mysql琐碎操作杂记

 

1、索引相关

   查看表索引

   show index from `user`

   查看sql的执行计划

   explain select * from where user

 

2、存储过程相关

   查看存储过程

   show procedure status

   查看存储过程AAA的ddl

   show create procedure AAA

   调用存储过程

   call AAA()

 

3、查看数据库的连接情况

 show processlist;

 

4、查看数据库大小

MySQL [information_schema]> select concat(round(sum(DATA_LENGTH/1024/1024), 2),'MB') as data from TABLES where table_schema='db_a';
MySQL [information_schema]> select concat(round(sum(DATA_LENGTH/1024/1024), 2),'MB') as data from TABLES where table_schema='db_b';   
MySQL [information_schema]> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;
select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema=’数据库名’ AND table_name=’表名’;
select sum(DATA_LENGTH)+sum(INDEX_LENGTH) from information_schema.tables where table_schema='数据库名';

5、show engine innodb status 查看引擎日志，包含如下面内容，常用的比如查看死锁日志

background thread

semaphores

Latest detected Deadlock

transactions

file i/o

insert buffer and adaptive hash index

log

buffer pool and memory

individual buffer pool info

Row operations

 

6、information_schema中的三张表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，通过这三张表，可以更简单地监控当前的事务并分析可能存在的锁等待问题。

      比如直接查SQL  SELECT  *  FROM INNODB_TRX，排查分析锁等待的问题，可以查出有问题的事务，然后处理它，比如kill掉等操作