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;

本文出自 “某人说我技术宅” 博客，请务必保留此出处http://1992mrwang.blog.51cto.com/3265935/1249997