阅读量:10
MySQL 碎片是什么
MySQL 碎片就是 MySQL 数据文件中一些不连续的空白空间，这些空间无法再被全部利用，久而久之越来多，越来越零碎，从而造成物理存储和逻辑存储的位置顺序不一致，这就是碎片。
碎片是如何产生的
delete 操作
在 MySQL 中删除数据，在存储中就会产生空白的空间，当有新数据插入时，MySQL 会试着在这些空白空间中保存新数据，但是呢总是用不满这些空白空间。所以日积月累，亦或是一下有大量的 delete 操作，一下就会有大量的空白空间，慢慢的会大到比表的数据使用的空间还大。
update 操作
在 MySQL 中更新数据，在可变长度的字段（比如 varchar）中更新数据，innodb 表存储数据的单位是页，update 操作会造成页分裂，分裂以后存储变的不连续，不规则，从而产生碎片。比如说原始字段长度 varchar(100)，我们大量的更新数据长度位为 50，这样的话，有 50 的空间被空白了，新入库的数据不能完全利用剩余的 50，这就会产生碎片。
碎片到底产生了什么影响
MySQL 既然产生了碎片，你可能比较豪横说磁盘空间够大，浪费空间也没事，但是这些碎片也会产生性能问题，碎片会有什么影响呢？
空间浪费
空间浪费不用多说，碎片占用了大量可用空间。
读写性能下降
由于存在大量碎片，数据从连续规则的存储方式变为随机分散的存储方式，磁盘 IO 会变的繁忙，数据库读写性能就会下降。
找一找有哪些碎片
现在我们有一个测试库 employees，在找碎片清理碎片前我们先查询一下表的数据，记录一下时间，以便后边做对比。
mysql> select count(*) from current_dept_emp;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (1.17 sec)
mysql> select count(*) from departments;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
| 331603 |
+----------+
1 row in set (0.08 sec)
mysql> select count(*) from dept_emp_latest_date;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.49 sec)
mysql> select count(*) from dept_manager;
+----------+
| count(*) |
+----------+
| 24 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.09 sec)
mysql> select count(*) from salaries;
+----------+
| count(*) |
+----------+
| 2844047 |
+----------+
1 row in set (0.60 sec)
mysql> select count(*) from titles;
+----------+
| count(*) |
+----------+
| 443308 |
+----------+
1 row in set (0.11 sec)
接下来我们开始看看都有哪些碎片吧。这里介绍两种方式查看表碎片。
1. 通过表状态信息查看
show table status like '%table_name%';
mysql> show table status like 'salaries'\G;
*************************** 1. row ***************************
Name: salaries
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2838918
Avg_row_length: 31
Data_length: 90832896
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2021-01-14 14:33:47
Update_time: 2021-01-14 14:34:42
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
data*length 表数据大小 index*length 表索引大小 data_free 碎片大小
根据返回信息，我们知道碎片大小为 4194304（单位 B）
2. 通过数据库视图信息查看
查询 information_schema.tables 的 data_free 列的值：
mysql> select
t.table_schema,
t.table_name,
t.table_rows,
t.data_length,
t.index_length,
concat(round(t.data_free/1024/1024,2),'m') as data_free
from information_schema.tables t
where t.table_schema = 'employees';
+--------------+----------------------+------------+-------------+--------------+-----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+--------------+----------------------+------------+-------------+--------------+-----------+
| employees | current_dept_emp | NULL | NULL | NULL | NULL |
| employees | departments | 9 | 16384 | 16384 | 0.00M |
| employees | dept_emp | 331143 | 12075008 | 5783552 | 4.00M |
| employees | dept_emp_latest_date | NULL | NULL | NULL | NULL |
| employees | dept_manager | 24 | 16384 | 16384 | 0.00M |
| employees | employees | 299069 | 15220736 | 0 | 4.00M |
| employees | salaries | 2838426 | 100270080 | 0 | 4.00M |
| employees | titles | 442902 | 20512768 | 0 | 4.00M |
+--------------+----------------------+------------+-------------+--------------+-----------+
8 rows in set (0.01 sec)
根据结果显示，data_free 列数据就是我们要查询的表的碎片大小内容，是 4M。
如何清理碎片
找到表碎片了，我们如何清理呢？有两种方法。
1. 分析表
命令：
optimize table table_name;
这个方法主要针对 MyISAM 引擎表使用，因为 MyISAM 表的数据和索引是分离的，optimize 表可以整理数据文件，重新排列索引。
注意：optimize 会锁表，时间长短依据表数据量的大小。
2. 重建表引擎
命令：
alter table table_name engine = innodb;
这个方法主要针对 InnoDB 引擎表使用，该操作会重建表的存储引擎，重组数据和索引的存储。
刚才我们查到表 salaries 有 4M 的碎片，我们清理一下 salaries 表碎片：
mysql> alter table salaries engine = innodb;
查询一下该表的碎片是否被清理：
mysql> select
t.table_schema,
t.table_name,
t.table_rows,
t.data_length,
t.index_length,
concat(round(t.data_free/1024/1024,2),'m') as data_free
from information_schema.tables t
where t.table_schema = 'employees' and table_name='salaries';
+--------------+------------+------------+-------------+--------------+-----------+
| table_schema | table_name | table_rows | data_length | index_length | data_free |
+--------------+------------+------------+-------------+--------------+-----------+
| employees | salaries | 2838426 | 114950144 | 0 | 2.00m |
+--------------+------------+------------+-------------+--------------+-----------+
1 row in set (0.00 sec)
碎片从原来的 4M 清理到现在的 2M。
我们看看查询表是否提高了速度：
mysql> select count(*) from salaries;
+----------+
| count(*) |
+----------+
| 2844047 |
+----------+
1 row in set (0.16 sec)
速度还是提高了不少，清理碎片后提高了查询速度。
总结一下：清理表的碎片可以提高 MySQL 性能，在日常工作中我们可以定期执行表碎片整理，从而提高 MySQL 性能。
