MySQL 性能优化:碎片整理

avatar
作者
筋斗云
阅读量: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 性能。

广告一刻

为您即时展示最新活动产品广告消息,让您随时掌握产品活动新动态!