MySQL 中表空间的查看和清理

/*

查看所有数据库容量大小

*/

SELECT table_schema, table_name, data_free, ENGINE

FROM information_schema.tables

WHERE table_schema NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test')

AND data_free > 0;

/*

查看所有数据库容量大小

*/

SELECT

table_schema AS '数据库',

SUM( table_rows ) AS '记录数',

SUM(

TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',

SUM(

TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'

FROM

information_schema.TABLES

GROUP BY

table_schema

ORDER BY

SUM( data_length ) DESC,

SUM( index_length ) DESC;

/*

查看数据库中各个表容量大小

*/

SELECT

table_schema AS '数据库',

table_name AS '表名',

table_rows AS '记录数',

TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',

TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'

FROM

information_schema.TABLES

ORDER BY

data_length DESC,

index_length DESC;

/*

查看指定数据库的容量大小

*/

SELECT

table_schema AS '数据库',

SUM( table_rows ) AS '记录数',

SUM(

TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',

SUM(

TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'

FROM

information_schema.TABLES

WHERE

table_schema = 'test_db';

/*

查看指定的数据库中各个表容量大小

*/

SELECT

table_schema AS '数据库',

table_name AS '表名',

table_rows AS '记录数',

TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',

TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'

FROM

information_schema.TABLES

WHERE

table_schema = 'test_db'

ORDER BY

data_length DESC,

index_length DESC;

/*

查看指定数据库中指定表的空间大小

*/

SELECT

table_schema AS '数据库',

table_name AS '数据表名',

SUM( table_rows ) AS '记录数',

SUM(

TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',

SUM(

TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'

FROM

information_schema.TABLES

WHERE

table_schema = 'test_db'

AND table_name = 't1';

/*

查看所有产生碎片的表

*/

SELECT table_schema db,

table_name,

data_free,

ENGINE

FROM information_schema.tables

WHERE table_schema NOT IN ('information_schema', 'mysql')

AND data_free > 0

ORDER BY DATA_FREE DESC;

/*

查看某个表的碎片大小

SHOW TABLE STATUS LIKE '表名';

如果data_free字段不为0则表示有碎片存在

*/

SHOW TABLE STATUS LIKE 't1';

-- 清理表碎片

/1. MyISAM表/

-- OPTIMIZE TABLE 表名

/*

Optimize语句可以重新组织表、索引的物理存储，减少存储空间，提高访问的IO效率

【注意】

1.使用这个语句需要对目标表具有select、insert权限

2.检查磁盘空间：剩余空间必须 > 被optimize的表大小

3.Optimize只对独立表空间（innodb_file_per_table=1）才有作用

4.数据量越大的表，优化耗时越长，百万条数据大约耗时30s(约25000-30000行/秒,此数据根据机器磁盘性能会有差异)。所以，在磁盘优化时，所有的增删操作将受限，请选择一个业务空档期执行。

*/

/2. InnoDB表/

-- ALTER TABLE 表名 engine = InnoDB

/*

本质上是recreate，Alter期间支持DML查询和更新操作

执行过程

1.获取MDL（meta data lock）写锁，innodb内部创建与原表结构相同的临时文件

2.拷贝数据前，MDL写锁退化成读锁，支持DML更新操作

3.根据主键递增顺序，将一行一行的数据读出并写到临时文件，直到全部写入完成，并且拷贝期间的DML更新操作会记录到Row log中

4.上锁，再将Row log中的数据应用到临时文件

5.互换原表和临时表的名字

6.删除临时表

*/

-- 在需要备份数据库里面的数据时，我们需要知道数据库占用了多少磁盘大小

-- 将表的数据和索引进行相加

SELECT SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) FROM information_schema.tables

WHERE table_schema='test_db';

-- 查询所有数据的大小

SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'M') FROM information_schema.tables WHERE table_schema='test_db';

-- 查询某个表的数据大小

SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'M') FROM information_schema.tables WHERE table_schema='test_db' AND table_name='t1';

/*

在mysql中有一个information_schema数据库，这个数据库中装的是mysql的元数据，包括数据库信息、数据库中表的信息等。所以要想查询数据库占用磁盘的空间大小可以通

过对information_schema数据库进行操作。

information_schema中的表主要有：

schemata表：这个表里面主要是存储在mysql中的所有的数据库的信息

tables表：这个表里存储了所有数据库中的表的信息，包括每个表有多少个列等信息。

columns表：这个表存储了所有表中的表字段信息。

statistics表：存储了表中索引的信息。

user_privileges表：存储了用户的权限信息。

schema_privileges表：存储了数据库权限。

table_privileges表：存储了表的权限。

column_privileges表：存储了列的权限信息。

character_sets表：存储了mysql可以用的字符集的信息。

collations表：提供各个字符集的对照信息。

collation_character_set_applicability表：相当于collations表和character_sets表的前两个字段的一个对比，记录了字符集之间的对照信息。

table_constraints表：这个表主要是用于记录表的描述存在约束的表和约束类型。

key_column_usage表：记录具有约束的列。

routines表：记录了存储过程和函数的信息，不包含自定义的过程或函数信息。

views表：记录了视图信息，需要有show view权限。

triggers表：存储了触发器的信息，需要有super权限。

1Byte = 8 bits;

1KB = 1024Byte = 2的10次方Byte = 1024 Byte

1MB = 1024KB = 2的20次方Byte = 1048576 Byte

1GB = 1024MB = 2的30次方Byte = 1073741824 Byte

1TB = 1024GB = 2的40次方Byte = 1099511627776 Byte

1PB = 1024TB = 2的50次方Byte = 1125899906842624 Byte

1EB = 1024PB = 2的60次方Byte = 1152921504606846976 Byte

1ZB = 1024EB = 2的70次方Byte = 1180591620717411303424 Byte

1YB = 1024ZB = 2的80次方Byte = 1208925819614629174706176 Byte

1DB = 1024YB = 2的90次方Byte = 1237940039285380274899124224 Byte

1NB = 1024DB = 2的100次方Byte = 1267650600228229401496703205376 Byte

*/

MySQL 查看事物和锁情况

-- 查看事务的等待情况

SELECT

r.trx_id waiting_trx_id,

r.trx_mysql_thread_id waiting_thread,

r.trx_query waiting_query,

b.trx_id blocking_trx_id,

b.trx_mysql_thread_id blocking_thread,

b.trx_query blocking_query

FROM

information_schema.innodb_lock_waits w

INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 查看具体的事物等待情况

SELECT

b.trx_state,

e.state,

e.time,

d.state AS block_state,

d.time AS block_time,

a.requesting_trx_id,

a.requested_lock_id,

b.trx_query,

b.trx_mysql_thread_id,

a.blocking_trx_id,

a.blocking_lock_id,

c.trx_query AS block_trx_query,

c.trx_mysql_thread_id AS block_trx_mysql_tread_id

FROM

information_schema.INNODB_LOCK_WAITS a

LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id

LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id

LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id

LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id

ORDER BY

a.requesting_trx_id;

-- 查看未关闭的事务

-- MySQL 5.6

SELECT

a.trx_id,

a.trx_state,

a.trx_started,

a.trx_query,

b.ID,

b.USER,

b.DB,

b.COMMAND,

b.TIME,

b.STATE,

b.INFO,

c.PROCESSLIST_USER,

c.PROCESSLIST_HOST,

c.PROCESSLIST_DB,

d.SQL_TEXT

FROM

information_schema.INNODB_TRX a

LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id

AND b.COMMAND = 'Sleep'

LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID

LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

-- MySQL 5.5

SELECT

a.trx_id,

a.trx_state,

a.trx_started,

a.trx_query,

b.ID,

b. USER,

b. HOST,

b.DB,

b.COMMAND,

b.TIME,

b.STATE,

b.INFO

FROM

information_schema.INNODB_TRX a

LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id

WHERE

b.COMMAND = 'Sleep';

-- 查询某段时间以来未关闭的事务

SELECT

trx_id,

trx_started,

trx_mysql_thread_id

FROM

INFORMATION_SCHEMA.INNODB_TRX

WHERE

trx_started < date_sub(now(), INTERVAL 1 MINUTE)

AND trx_operation_state IS NULL

AND trx_query IS NULL;