小白学习MySQL - 表空间碎片整理方法

痛定思痛。 2022-09-03 10:29 4阅读 0赞

《[小白学习MySQL - MySQL会不会受到“高水位”的影响？][MySQL - MySQL]》曾提到了MySQL中数据删除的空间清理和文件释放的问题。碰巧看到姚老师这篇文章，《MySQL表空间碎片整理方法》，学习一下。

MySQL数据库中的表在进行了多次delete、update和insert后，表空间会出现碎片。定期进行表空间整理，消除碎片可以提高访问表空间的性能。

# **检查表空间碎片** #

下面这个实验用于验证进行表空间整理后对性能的影响，首先检查这个有100万记录表的大小，

mysql> analyze table sbtest1;
    +----------------+---------+----------+-----------------------------+
    | Table          | Op      | Msg_type | Msg_text                    |
    +----------------+---------+----------+-----------------------------+
    | sbtest.sbtest1 | analyze | status   | Table is already up to date |
    +----------------+---------+----------+-----------------------------+
    1 row in set (0.06 sec)
    
    mysql> show table status like 'sbtest1'\G
    *************************** 1. row ***************************
               Name: sbtest1
             Engine: MyISAM
            Version: 10
         Row_format: Fixed
               Rows: 1000000
     Avg_row_length: 729
        Data_length: 729000000
    Max_data_length: 205195258022068223
       Index_length: 20457472
          Data_free: 0
     Auto_increment: 1000001
        Create_time: 2021-05-31 18:54:22
        Update_time: 2021-05-31 18:54:43
         Check_time: 2021-05-31 18:55:05
          Collation: utf8mb4_0900_ai_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.00 sec)
    
    mysql> system ls -l /var/lib/mysql/sbtest/sbtest1.*
    -rw-r----- 1 mysql mysql 729000000 May 31 08:24 /var/lib/mysql/sbtest/sbtest1.MYD
    -rw-r----- 1 mysql mysql  20457472 May 31 08:25 /var/lib/mysql/sbtest/sbtest1.MYI

命令show table status和从OS层看到的数据文件大小一致，这时的Data\_free为零。

删除这个表三分之二的记录，

mysql> delete from sbtest1 where id%3<>0;
    Query OK, 666667 rows affected (51.72 sec)

重新收集这个表的统计信息后再查看表的状态，

mysql> analyze table sbtest1;
    +----------------+---------+----------+----------+
    | Table          | Op      | Msg_type | Msg_text |
    +----------------+---------+----------+----------+
    | sbtest.sbtest1 | analyze | status   | OK       |
    +----------------+---------+----------+----------+
    1 row in set (0.13 sec)
    
    mysql> show table status like 'sbtest1'\G
    *************************** 1. row ***************************
               Name: sbtest1
             Engine: MyISAM
            Version: 10
         Row_format: Fixed
               Rows: 333333
     Avg_row_length: 729
        Data_length: 729000000
    Max_data_length: 205195258022068223
       Index_length: 20457472
          Data_free: 486000243
     Auto_increment: 1000001
        Create_time: 2021-05-31 18:54:22
        Update_time: 2021-05-31 19:03:59
         Check_time: 2021-05-31 18:55:05
          Collation: utf8mb4_0900_ai_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.01 sec)
    
    mysql> select 486000243/729000000;
    +---------------------+
    | 486000243/729000000 |
    +---------------------+
    |              0.6667 |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> system ls -l /var/lib/mysql/sbtest/sbtest1.*
    -rw-r----- 1 mysql mysql 729000000 May 31 08:33 /var/lib/mysql/sbtest/sbtest1.MYD
    -rw-r----- 1 mysql mysql  20457472 May 31 08:34 /var/lib/mysql/sbtest/sbtest1.MYI

发现这个表中的三分之二的记录已经被删除，但数据文件的大小还和原来一样。因为被删除的记录只是被标记成删除，他们占用的存储空间并没有被释放。

进行全表扫描，看看性能，

mysql> select count(*) from sbtest1 where c<>'aaa';
    +----------+
    | count(*) |
    +----------+
    |   333333 |
    +----------+
    1 row in set (0.82 sec)

发现这个全表扫描SQL用时0.82秒，查看sys.session视图中的last\_statement\_latency可以看到一样的用时。

**整理表空间与性能提升**

进行表空间整理，

mysql> alter table sbtest1 force;
    Query OK, 333333 rows affected (10.73 sec)
    Records: 333333  Duplicates: 0  Warnings: 0
    
    mysql> analyze table sbtest1;
    +----------------+---------+----------+-----------------------------+
    | Table          | Op      | Msg_type | Msg_text                    |
    +----------------+---------+----------+-----------------------------+
    | sbtest.sbtest1 | analyze | status   | Table is already up to date |
    +----------------+---------+----------+-----------------------------+
    1 row in set (0.04 sec)
    
    mysql> show table status like 'sbtest1'\G
    *************************** 1. row ***************************
               Name: sbtest1
             Engine: MyISAM
            Version: 10
         Row_format: Fixed
               Rows: 333333
     Avg_row_length: 729
        Data_length: 242999757
    Max_data_length: 205195258022068223
       Index_length: 6820864
          Data_free: 0
     Auto_increment: 1000001
        Create_time: 2021-05-31 19:10:35
        Update_time: 2021-05-31 19:10:41
         Check_time: 2021-05-31 19:10:45
          Collation: utf8mb4_0900_ai_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.48 sec)
    
    mysql> system ls -l /var/lib/mysql/sbtest/sbtest1.*
    -rw-r----- 1 mysql mysql 242999757 May 31 08:40 /var/lib/mysql/sbtest/sbtest1.MYD
    -rw-r----- 1 mysql mysql   6820864 May 31 08:40 /var/lib/mysql/sbtest/sbtest1.MYI

经过整理后，硬盘空间占用剩下原来的三分之一，Data\_free又变成零，被删除的记录的硬盘空间都释放了。

再次执行全表扫描的SQL语句，

mysql> select count(*) from sbtest1 where c<>'aaa';
    +----------+
    | count(*) |
    +----------+
    |   333333 |
    +----------+
    1 row in set (0.29 sec)

发现执行速度也提高到大约原来的三倍。这里使用的是MyISAM表进行测试，如果用InnoDB表，速度的提高没有这么明显，因为InnoDB的数据会缓存到InnoDB缓存中，MyISAM表的数据MySQL不进行缓存，OS可能会缓存，因此要得到准确的测试结果，在Linux系统上每次测试前要使用下面的命令释放系统的缓存，

# echo 3 > /proc/sys/vm/drop_caches

使用alter table force进行表空间整理和OPTIMIZE TABLE命令的作用一样，这个命令适用于InnoDB , MyISAM和ARCHIVE三种引擎的表。但对于InnoDB的表，不支持OPTIMIZE TABLE命令，可以用alter table sbtest1 engine=innodb代替，例如，

mysql> OPTIMIZE TABLE sbtest2;
    +----------------+----------+----------+-------------------------------------------------------------------+
    | Table          | Op       | Msg_type | Msg_text                                                          |
    +----------------+----------+----------+-------------------------------------------------------------------+
    | sbtest.sbtest2 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
    | sbtest.sbtest2 | optimize | status   | OK                                                                |
    +----------------+----------+----------+-------------------------------------------------------------------+
    2 rows in set (1 min 25.24 sec)
    
    mysql> alter table sbtest2 engine=innodb;
    Query OK, 0 rows affected (1 min 3.06 sec)
    Records: 0  Duplicates: 0  Warnings: 0

**使用mysqlcheck进行批量表空间优化**

下面的命令可以找出表空间中可释放空间超过10M的最大10个表，

mysql> select table_name,round(data_length/1024/1024) as data_length_mb,  round(data_free/1024/1024) as data_free_mb   
    from information_schema.tables   where round(data_free/1024/1024) > 10  order by data_free_mb desc limit 10;
    +------------+----------------+--------------+
    | TABLE_NAME | data_length_mb | data_free_mb |
    +------------+----------------+--------------+
    | sbtest2    |            232 |          174 |
    +------------+----------------+--------------+
    1 row in set (0.02 sec)

可以使用MySQL自带的工具mysqlcheck的-o选项进行表空间优化，这个工具适合于在脚本中进行批量处理，可以被Linux中的crontab或Windows中的计划任务调用。

对单个表进行表空间优化的例子如下，

$ mysqlcheck -o sbtest sbtest1

也可以使用下面的命令对某个数据库中的所有表进行表空间优化，

$ mysqlcheck -o sbtest

还可以对整个实例中对所有数据库进行表空间优化，

$ mysqlcheck -o --all-databases

与其要考虑怎么删除数据和回收空间，不如在设计之初，就考虑到表的数据删除策略，根据业务需求，存储有用的数据。

**小白学习MySQL，**

《[小白学习MySQL - 大小写敏感问题解惑][MySQL -]》

《[小白学习MySQL - only\_full\_group\_by的校验规则][MySQL - only_full_group_by]》

《[小白学习MySQL - max\_allowed\_packet][MySQL - max_allowed_packet]》

《[小白学习MySQL - mysqldump保证数据一致性的参数差异][MySQL - mysqldump]》

《[小白学习MySQL - 查询会锁表？][MySQL - 1]》

《[小白学习MySQL - 索引键长度限制的问题][MySQL - 2]》

《[小白学习MySQL - MySQL会不会受到“高水位”的影响？][MySQL - MySQL]》

《[小白学习MySQL - 数据库软件和初始化安装][MySQL - 3]》

《[小白学习MySQL - 闲聊聊][MySQL - 4]》

**近期更新的文章：**

《[Oracle字符串类型扩容隐患][Oracle]》

[《最近碰到的几个问题][Link 1][》][Link 1]

《[PG逻辑复制的REPLICA IDENTITY设置][PG_REPLICA IDENTITY]》

《[Java List集合转数组的两种重载方法][Java List]》

《[Oracle、SQL Server和MySQL的隐式转换异同][Oracle_SQL Server_MySQL]》

**文章分类和索引：**

[《公众号800篇文章分类和索引][800]》

[MySQL - MySQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a485ddf52d936279d52b0e62463f699412cf886e9b51eaf48114e7561dec55ccc21189be&idx=1&mid=2247491958&scene=21&sn=6917dcc719cab7b5537194eed27b4ed2#wechat_redirect
[MySQL -]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a06fddf529791d829e1e2c976d5b9d9a9e9825bb35c7a9e8b85ccd9cafe962115ba90e4c&idx=1&mid=2247493020&scene=21&sn=ced36198e3ad9d003445ea6d37679358#wechat_redirect
[MySQL - only_full_group_by]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a070ddf52966a270c13763d5068d58ca8c16a3655f088ddb1c856b3f2394178452b751b2&idx=1&mid=2247492995&scene=21&sn=bfeca96384e6310326f2309da5ae0853#wechat_redirect
[MySQL - max_allowed_packet]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a0a2ddf529b48f7a84a3c6e29f3db6034e41951fb003a83d4ba39de501c1b74ea5a4d488&idx=1&mid=2247492945&scene=21&sn=86c99c128149dc975541eaf98127f880#wechat_redirect
[MySQL - mysqldump]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a169ddf5287f13b6f3ce6092e7645d9c4a50ff14a9e1965afda26514fee38691d5dbea9f&idx=1&mid=2247492762&scene=21&sn=6f412753ba32dc5f4560d5d32ed28314#wechat_redirect
[MySQL - 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a751ddf52e47df120a4c3f4cd841cd06383a3cc406398d6feb9185e17d1167c53e1c0a56&idx=1&mid=2247492258&scene=21&sn=d1859e3c1e3f2bee0e2c7801c8dc0b5b#wechat_redirect
[MySQL - 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a466ddf52d701aa8f23065ac5d0ef4e0ff1bf1b6deb53a923e3d3ab007d6d41d92a73088&idx=1&mid=2247491989&scene=21&sn=3979107e0b99ebddc6b6ccfc8f9b901c#wechat_redirect
[MySQL - 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4c8ddf52dde849a70b34f2b62ff16593f81898cca7bc1fd7f0321588823016f0becaa18&idx=1&mid=2247491899&scene=21&sn=7affd185a81761eeaa5c3e143fc6f894#wechat_redirect
[MySQL - 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a4dcddf52dcad1708150cc6ceb101423eb5f4326ceff0f9dd53c05103bea76c1c5b7ccbc&idx=1&mid=2247491887&scene=21&sn=91485c182868ef19ad822c3ac9bf2ea9#wechat_redirect
[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a055ddf52943c9699cd6e4ccd7ab18c6f181921a97cf9c35cb962b9898c6eed1a8fb553f&idx=1&mid=2247493030&scene=21&sn=5bc4c2487b1ac96d507ce7468f02d535#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a066ddf52970187fa073a6c47e94944047199bd3599283fae35292a4a768417c58fda18d&idx=1&mid=2247493013&scene=21&sn=5023b84a70b327ab5991ebc28b48b2be#wechat_redirect
[PG_REPLICA IDENTITY]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a0b7ddf529a1c70e180e944651c2330c2be943b1ef07c95b78b9936a2d32c43b166b9637&idx=1&mid=2247492932&scene=21&sn=b3dc4ee09e8517446d356e31c4b17a87#wechat_redirect
[Java List]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a0bbddf529adb58e882b0038330f2efca7eb144d3de827ba3d8b137a6761a2059bbe2196&idx=1&mid=2247492936&scene=21&sn=c1beba89533c4b8b4bf7a6b5734e3e23#wechat_redirect
[Oracle_SQL Server_MySQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a0f4ddf529e2a052e6e34e71931074e0a662e1472d9a8725c5584363bb054761715d061e&idx=1&mid=2247492871&scene=21&sn=db396b9b0d0b9483c34f3a28fc897349#wechat_redirect
[800]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a084ddf529920f2572e843967c4cb4248b6adc854a001315dd54264080349d3a5cc2ea14&idx=1&mid=2247492983&scene=21&sn=5e0514b86258eebed20740dce83a6d45#wechat_redirect