Carry の Blog
Carry の Blog
2026-03-04
目录

表空间管理与回收

# 表空间管理与回收

# 1. 表空间概述

# 1.1 什么是表空间

表空间（Tablespace）是MySQL中用于存储数据的逻辑容器。在InnoDB存储引擎中，表空间是数据存储的基本单位。

# 1.2 表空间的类型

InnoDB表空间类型：
1. 系统表空间（System Tablespace）
   - 存储系统元数据
   - 包含数据字典、Undo日志等
   - 默认位于data directory下的ibdata1文件

2. 独立表空间（File-Per-Table Tablespace）
   - 每个表拥有独立的表空间文件
   - 文件名为table_name.ibd
   - 需要开启innodb_file_per_table参数

3. 通用表空间（General Tablespace）
   - 可以包含多个表的数据
   - 需要显式创建和管理

4. 回滚表空间（Undo Tablespace）
   - 存储Undo日志
   - 用于事务回滚和MVCC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 2. 表空间管理

# 2.1 查看表空间信息

-- 查看系统表空间信息
SELECT 
    tablespace_name,
    file_name,
    file_size,
    allocated_size
FROM information_schema.file_name_list;

-- 查看表空间使用情况
SELECT 
    tablespace_name,
    file_name,
    total_space,
    used_space,
    free_space
FROM (
    SELECT 
        tablespace_name,
        file_name,
        SUM(data_length + index_length) AS total_space,
        SUM(data_length) AS used_space,
        SUM(data_free) AS free_space
    FROM information_schema.tables
    WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
    GROUP BY tablespace_name, file_name
) AS t;

-- 查看InnoDB表空间详细信息
SELECT 
    tablespace_name,
    file_name,
    total_size,
    used_size,
    free_size
FROM information_schema.innodb_tablespaces;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 2.2 创建和管理表空间

-- 创建通用表空间
CREATE TABLESPACE my_tablespace
ADD DATAFILE 'my_tablespace.ibd'
ENGINE=InnoDB;

-- 创建表并指定表空间
CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) TABLESPACE my_tablespace;

-- 查看表的表空间信息
SELECT 
    table_name,
    tablespace_name,
    engine
FROM information_schema.tables 
WHERE table_schema = 'database_name' 
AND table_name = 'table_name';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 3. 表空间回收

# 3.1 删除表的空间回收

-- 删除表时回收空间
DROP TABLE table_name;

-- 查看表空间使用情况变化
SELECT 
    tablespace_name,
    file_name,
    SUM(data_length + index_length) AS total_space
FROM information_schema.tables 
WHERE table_schema = 'database_name'
GROUP BY tablespace_name, file_name;
1
2
3
4
5
6
7
8
9
10
11

# 3.2 优化表空间回收

-- 优化表空间（回收碎片）
ALTER TABLE table_name ENGINE=InnoDB;

-- 重建表空间
OPTIMIZE TABLE table_name;

-- 清理表空间碎片
-- 适用于独立表空间
ALTER TABLE table_name DISCARD TABLESPACE;
ALTER TABLE table_name IMPORT TABLESPACE;
1
2
3
4
5
6
7
8
9
10

# 3.3 清理历史数据空间

-- 删除历史数据
DELETE FROM table_name WHERE date_column < '2022-01-01';

-- 优化表空间
OPTIMIZE TABLE table_name;

-- 清理临时表空间
-- 删除不再使用的临时表
DROP TEMPORARY TABLE IF EXISTS temp_table;
1
2
3
4
5
6
7
8
9

# 4. 系统表空间管理

# 4.1 系统表空间扩容

-- 查看系统表空间大小
SHOW VARIABLES LIKE 'innodb_data_file_path';

-- 扩展系统表空间（需要重启）
-- 修改配置文件
innodb_data_file_path = ibdata1:12M:autoextend:max:2G
1
2
3
4
5
6

# 4.2 系统表空间收缩

-- 通过备份还原方式收缩
-- 1. 备份所有数据库
mysqldump --all-databases > backup.sql

-- 2. 停止MySQL服务
sudo systemctl stop mysql

-- 3. 删除旧的系统表空间文件
rm /var/lib/mysql/ibdata1
rm /var/lib/mysql/ib_logfile*

-- 4. 重启MySQL服务
sudo systemctl start mysql

-- 5. 恢复数据
mysql < backup.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 5. 独立表空间管理

# 5.1 独立表空间回收

-- 查看独立表空间信息
SELECT 
    table_schema,
    table_name,
    tablespace_name,
    data_length,
    index_length
FROM information_schema.tables 
WHERE engine = 'InnoDB' 
AND tablespace_name IS NOT NULL;

-- 回收独立表空间空间
-- 方式1：优化表
ALTER TABLE table_name ENGINE=InnoDB;

-- 方式2：重建表
ALTER TABLE table_name FORCE;

-- 方式3：删除并重新创建表
CREATE TABLE table_name_new LIKE table_name;
INSERT INTO table_name_new SELECT * FROM table_name;
RENAME TABLE table_name TO table_name_old, table_name_new TO table_name;
DROP TABLE table_name_old;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 5.2 独立表空间文件清理

-- 查找孤立的表空间文件
SELECT 
    table_name,
    tablespace_name
FROM information_schema.tables 
WHERE engine = 'InnoDB' 
AND tablespace_name LIKE '%.ibd'
AND table_name NOT IN (
    SELECT table_name 
    FROM information_schema.tables 
    WHERE table_schema = 'database_name'
);

-- 删除孤立表空间文件（谨慎操作）
-- 需要先确认表已不存在
-- rm /var/lib/mysql/database_name/table_name.ibd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 6. 表空间监控与告警

# 6.1 空间使用监控

-- 创建表空间使用监控视图
CREATE VIEW tablespace_usage AS
SELECT 
    table_schema,
    table_name,
    table_type,
    engine,
    table_rows,
    data_length,
    index_length,
    (data_length + index_length) AS total_size,
    data_free,
    ROUND((data_free / (data_length + index_length)) * 100, 2) AS free_percentage
FROM information_schema.tables 
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND engine = 'InnoDB';

-- 查询空间使用情况
SELECT * FROM tablespace_usage ORDER BY total_size DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 6.2 空间告警脚本

#!/bin/bash
# 表空间监控脚本

THRESHOLD=80  # 空间使用率阈值

# 获取数据库空间使用情况
mysql -e "
SELECT 
    table_schema,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size(MB)',
    ROUND(SUM(data_free) / 1024 / 1024, 2) AS 'Free(MB)'
FROM information_schema.tables 
GROUP BY table_schema
HAVING SUM(data_length + index_length) > 0
ORDER BY (SUM(data_length + index_length)) DESC
" > /tmp/db_space_report.txt

# 检查是否超过阈值
while read line; do
    if [[ $line =~ [0-9]+\.[0-9]+ ]]; then
        size=$(echo $line | awk '{print $2}')
        if (( $(echo "$size > $THRESHOLD" | bc -l) )); then
            echo "警告: 数据库空间使用率过高: $line"
            # 发送告警通知
        fi
    fi
done < /tmp/db_space_report.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 7. 表空间维护最佳实践

# 7.1 定期维护计划

-- 创建定期维护任务
CREATE EVENT optimize_tables
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    -- 优化所有InnoDB表
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT table_name FROM information_schema.tables 
        WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
        AND engine = 'InnoDB';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('OPTIMIZE TABLE ', tbl_name);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# 7.2 空间回收策略

-- 1. 定期清理无用数据
DELETE FROM log_table WHERE created_time < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 2. 优化表空间
OPTIMIZE TABLE log_table;

-- 3. 清理临时表
DROP TEMPORARY TABLE IF EXISTS temp_result;

-- 4. 分析表统计信息
ANALYZE TABLE log_table;
1
2
3
4
5
6
7
8
9
10
11

# 7.3 备份与恢复

# 备份表空间
mysqldump --single-transaction --routines --triggers database_name > backup.sql

# 验证备份
mysql -e "USE database_name; SHOW TABLES;"

# 恢复表空间
mysql database_name < backup.sql
1
2
3
4
5
6
7
8

# 8. 常见问题及解决方案

# 8.1 表空间不足

-- 检查表空间容量
SHOW VARIABLES LIKE 'innodb_data_file_path';

-- 解决方案：
-- 1. 扩展现有文件
-- 2. 添加新的数据文件
-- 3. 优化数据存储结构
1
2
3
4
5
6
7

# 8.2 表空间碎片化

-- 检查碎片化程度
SELECT 
    table_name,
    data_free,
    (data_length + index_length) AS total_size,
    ROUND((data_free / (data_length + index_length)) * 100, 2) AS fragmentation_pct
FROM information_schema.tables 
WHERE engine = 'InnoDB' 
AND data_free > 0
ORDER BY data_free DESC;
1
2
3
4
5
6
7
8
9
10

# 8.3 独立表空间文件过大

-- 检查独立表空间大小
SELECT 
    table_schema,
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables 
WHERE engine = 'InnoDB' 
AND tablespace_name LIKE '%.ibd'
ORDER BY (data_length + index_length) DESC;
1
2
3
4
5
6
7
8
9

# 9. 总结

表空间管理是MySQL数据库运维中的重要环节：

  1. 理解不同类型表空间的特性
  2. 建立完善的监控体系
  3. 制定定期维护计划
  4. 合理配置参数
  5. 及时处理空间问题

通过合理的表空间管理，可以有效提高数据库性能，避免存储空间不足的问题，确保数据库系统的稳定运行。定期的维护和监控是保证表空间健康的关键。

上次更新: 3/4/2026

← count函数详解→

最近更新
01
MySQL抖动刷脏页
03-04
02
count函数详解
03-04
03
读写分离实践与问题
03-04
更多文章>
Theme by Vdoing