MySQL磁盘占用太多的一种优化思路
今天分享一个线上的经典MySQL磁盘问题。
01
背景介绍
线上的MySQL实例在使用时间长了之后，会保存很多的业务数据，通常情况下，磁盘使用量也会随着业务的接入时间上升。
对于InnoDB存储引擎来说，如果业务部门的SQL有大量的随机insert和delete操作，那么会导致这个数据表中有很多空洞，删除了部分数据之后，磁盘空间并不会立即释放，这个问题的原因是delete数据仅仅是在数据记录的位置打上一个delete标识，并不会真正的删除数据。这样，表中就形成了一个一个的"空洞"，随着时间变长，表中的空洞会越来越多。我曾经遇到过，一个表保存了1w条左右的数据，但是容量达到了40GB，这明显是不正常的。
针对上面这个问题，其实我们可以使用下面的方法来处理：
alter table tbl_name engine=innodb;
或者：
optimize table tbl_name;
但是，今天我们讨论的，不是这个问题，如果你做了alter table之后，磁盘空间还是不够用，怎么办？
今天在线上就遇到了这样一个问题，答案是：修改存储引擎为tokudb
02
Tokudb存储引擎
Tokudb是一个比较冷门的引擎，但是它有很多优点，例如支持事务、支持MVCC、有着很高的数据压缩比，目前由percona公司负责维护。
Percona公司，搞MySQL的应该都听过，它是MySQL的两大分支中很重要的一个，也是大名鼎鼎的PT工具的母公司。Percona MySQL Server上就自带TokuDB这个存储引擎。
MySQL的插件式存储引擎设计，允许我们在线将innodb的存储引擎修改为tokudb的存储引擎。
我们修改innodb存储引擎为tokudb存储引擎的目的是想使用tokudb的高压缩比，那么这个压缩比有多大收益呢？来看我的测试：
1、查看表的数据量，并直接使用SQL语法修改，如下：
代码语言：javascript复制mysql > select count(1) from table_name ;
----------
| count(1) |
----------
| 6313530 |
----------
1 row in set (1.70 sec)
mysql > alter table table_name engine=tokudb;
Query OK, 6313530 rows affected (21 min 42.91 sec)
Records: 6313530 Duplicates: 0 Warnings: 0
数据量大概631w，修改时间大概20min左右，很明显，它不是online DDL的，当然，你可以使用pt-osc工具去操作。
针对这个alter table修改时间20min，其实已经比较长了，在这个过程中，我们可以用另外的mysql shell，使用show processlist命令去查看alter table的执行进度，其实进度展示还是比较友好的，如下：
- 加载ibd文件的速度展示：
Loading of data t ./table_name/#sql-5063_310d24f1 about 77.6% done | alter table table_name engine=tokudb
这里可以看到进度是77.6%
- 加载完毕后的唯一性校验
Verifying index uniqueness: Checked 4600000 of 0 rows in key-PRIMARY. | alter table table_name engine=tokudb
2、查看修改前后的磁盘收益：
代码语言：javascript复制[root ]# ll | grep table_name
-rw-r----- 1 my5500 mysql 43196 Feb 25 2021 table_name.frm
-rw-r----- 1 my5500 mysql 24482152448 Jan 11 15:58 table_name.ibd
简单计算下ibd文件的容量
mysql > select 24482152448/1024/1024/1024;
----------------------------
| 24482152448/1024/1024/1024 |
----------------------------
| 22.800781250000 |
----------------------------
1 row in set (0.00 sec)
修改前，innodb累积：22.8 G
-------------------------分割线-----------------------
[root ]# du -sh -c table_name*
44K table_name.frm
117M table_name_key_ch_gr_7ee403500_2_1d.tokudb
129M table_name_key_idx_xxx_7ee403500_3_1d.tokudb
129M table_name_key_idx_xxx_7ee403500_4_1d.tokudb
129M table_name_key_idx_xxx_7ee403500_6_1d.tokudb
129M table_name_key_idx_xxx_7ee403500_5_1d.tokudb
122M table_name_key_idx_xxx_time_7ee403500_7_1d.tokudb
170M table_name_key_idx_xxx_cmnt_7ee403500_8_1d.tokudb
127M table_name_key_idx_xxx_time_7ee403500_9_1d.tokudb
121M table_name_key_m_xxx_xxx_7ee403500_a_1d.tokudb
121M table_name_key_m_xxx_7ee403500_b_1d.tokudb
123M table_name_key_m_time_7ee403500_c_1d.tokudb
1.4G table_name_main_7ee403500_d_1d.tokudb
32K table_name_status_7ee403500_e_1d.tokudb
2.8G total
修改后，tokudb累积：2.8G
不难看出，修改之前，innodb的数据量有22.8G，修改之后tokudb的数据量仅有2.8G
通过修改存储引擎为tokudb，压缩了8倍磁盘容量。
03
tokudb这么好，为什么还要用innodb呢？
既然tokudb的也支持事务，也支持mvcc，占用的磁盘还少，那为什么还要使用innodb呢？
1、试想这样一个问题，tokudb高压缩比的存储方法，其实在磁盘上存储的文件已经是压缩过了的，如果我们访问的数据在内存中，那么访问innodb和tokudb的时间差不多；
但如果我们访问的数据在磁盘上，那么tokudb要先将压缩的数据解压，然后在存入内存中访问，这个解压的过程是有代价的，需要消耗CPU，也会带来一定的时延，因此，性能上会有损耗；
如果业务对性能要求比较高，可能不太合适。
2、其次，tokudb是需要使用percona版本的MySQL Server的，社区版本并不支持。可能需要调整线上的MySQL服务版本。
3、其他一些Innodb的新特性。
总之：
如果你的业务对MySQL访问性能要求不高，但是数据量非常大，建议使用tokudb，它可以帮你节省磁盘空间；
如果你的业务对性能要求也高，数据量也比较大，建议直接扩充磁盘，或者上tidb吧。。。
