mysql整理表空间 碎片_进阶教程 | MySQL表空间碎片整理操作指导

可用空间检查

1. 登录MySQL 数据库所在主机，查看磁盘可用空间。

#df –h

2. 登录数据库查看磁盘碎片率较高的表。

Mysql>use information_schema;

Mysql>select TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH/1024/1024/1024 TOTAL_SIZE_GB, round(INDEX_LENGTH/1024/1024/1024,2) index_size,DATA_FREE/1024/1024/1024 DATA_FREE_GB from tables where TABLE_SCHEMA='mytest' and TABLE_TYPE='BASE TABLE' order by DATA_FREE_GB desc;

+--------------+------------+----------------+---------------+----------------+

| TABLE_SCHEMA | TABLE_NAME | TOTAL_SIZE_GB  | index_size_GB | DATA_FREE_GB   |

+--------------+------------+----------------+---------------+----------------+

| mytest       | sbtest1    | 0.969573974609 |          0.03 | 0.179687500000 |

| mytest       | sbtest2    | 1.145507812500 |          0.04 | 0.005859375000 |

| mytest       | t1         | 0.000015258789 |          0.00 | 0.000000000000 |

| mytest       | t2         | 0.606445312500 |          0.06 | 0.000000000000 |

| mytest       | t3         | 0.000015258789 |          0.00 | 0.000000000000 |

| mytest       | t4         | 0.000015258789 |          0.00 | 0.000000000000 |

+--------------+------------+----------------+---------------+----------------+

注：如上SQL 语句中schema名称请根据实际情况进行修改。

3. 开启Performane_shema相关统计参数，以便观察DDL 进度。

Mysql>user performance_schema;

Mysql> update setup_instruments set ENABLED='YES' where NAME like 'stage/innodb/alter%';

Mysql> update setup_consumers set ENABLED='YES' where NAME like '%stages%%';