mysql维护

mysql表占用空间查询

mysql> use information_schema;
	Database changed
2、查询所有数据的大小
	mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2), 'MB')
	    -> as data from TABLES;
	+-----------+
	| data      |
	+-----------+
	| 6674.48MB |
	+-----------+
	1 row in set (16.81 sec)
3、查看指定数据库实例的大小，比如说数据库 forexpert
	mysql> select concat(round(sum(DATA_LENGTH/1024/1024), 2), 'MB')
	    -> as data from TABLES where table_schema='forexpert';
	+-----------+
	| data      |
	+-----------+
	| 6542.30MB |
	+-----------+
	1 row in set (7.47 sec)
4、查看指定数据库的表的大小，比如说数据库 forexpert 中的 member 表
	mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data
	    -> from TABLES where table_schema='forexpert'
	    -> and table_name='member';
	+--------+
	| data   |
	+--------+
	| 2.52MB |
	+--------+
	1 row in set (1.88 sec)

mysql查看执行的sql进程，并解锁

show processlist;
//杀掉进程
kill id
//查看被锁表
show OPEN TABLES where In_use > 0;