MySql常用操作収集

/*       */
SELECT CONCAT( TRUNCATE( SUM( data_length ) /1024 /1024, 2 ) ,  'MB' ) AS data_size,
CONCAT( TRUNCATE( SUM( max_data_length ) /1024 /1024, 2 ) ,  'MB' )AS max_data_size,
CONCAT( TRUNCATE( SUM( data_free ) /1024 /1024, 2 ) ,  'MB' )AS data_free,
CONCAT( TRUNCATE( SUM( index_length ) /1024 /1024, 2 ) ,  'MB' ) AS index_size
FROM information_schema.tables
WHERE TABLE_NAME =  'db_name.table_name'
limit 0, 30;

/*       */
SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) AS SIZE FROM information_schema.TABLES
where TABLE_SCHEMA='db_name';
            ， 1024 K， 1048576(=1024*1024) M。

/*      mysql    */
select TABLE_NAME,UPDATE_TIME
from information_schema.TABLES
where TABLE_SCHEMA='db_name'
order by UPDATE_TIME desc limit 1;
select TABLE_NAME,UPDATE_TIME
from information_schema.TABLES
where TABLE_SCHEMA='db_name'
and information_schema.TABLES.TABLE_NAME = 'tb_name';

/*                  */
explain [extended] select ... from ... where ...
/* */
ANALYZE TABLE tb_name;

/*       */
DESCRIBE db_name.tb_name

/*      */
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
/*       */
SHOW STATUS;
/*    */
show processlist;
/*          */
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
/*kill ID */
kill 7 
/*        */
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
/*        */
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;