当mysql表从压缩表变成普通表会发生什么

前言

本文章做了把mysql表从压缩表过渡到普通表的实验过程，看看压缩表变成普通表会发生什么？本文针对mysql5.7和mysql8分别进行了实验。

1、什么是表压缩

在介绍压缩表变成普通表前，首先给大家普及下，什么是表压缩。

表压缩，意思是使表中的数据以压缩格式存储，压缩能够显著提高处理速度和压缩磁盘 。压缩意味着在硬盘和内存之间传输的数据更小且占用相对少的内存及硬盘，对于辅助索引，这种压缩带来更加明显的好处，因为索引数据也被压缩了。

表压缩是有很大好处的，能减少磁盘的I/O，还能提高系统吞吐量，节约空间，压缩率越大，占用的磁盘空间越小，文件传输时间提升，降低数据的存储和网络传输成本。

2、如何表压缩( mysql的版本需要大于5.5 )

1、首先设置my.inf参数

#打开配置文件
vim /etc/my.inf

#加入配置项
innodb_file_per_table=1
innodb_file_format=Barracuda
innodb_strict_mode=1 #建议加上
innodb_default_row_format = COMPRESSED #在整个库默认启用行压缩格式时设定，一边不改变此值

#重启数据库
systemctl restart mysqld

2、对表压缩

mysql> alter table t1 ROW_FORMAT=COMPRESSED;

3、压缩表转换为普通表

mysql> alter table t1 ROW_FORMAT=DEFAULT;

针对mysql5.7开始实验

• mysql数据库版本：5.7.31

• linux版本：centos5.7

1、建表和初始化测试数据

#1、建表
CREATE TABLE test_compress (
    id bigint(20) unsigned NOT NULL,
    identification_id int(10) unsigned DEFAULT NULL,
    timestamp datetime NOT NULL,
    action varchar(50) NOT NULL,
    result varchar(50) NOT NULL,
    PRIMARY KEY (id),
    KEY INDEX_test_compress_result (result),
    KEY INDEX_test_compress_timestamp (timestamp)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#2、插入测试数据（linux里执行脚本）
for NUM in {1..100000}; do mysql -h localhost PS_57 -e "insert into test_compress (id, identification_id, timestamp, action, result) values ($NUM,$NUM*100,now(),concat('string',$NUM),concat('VeryVeryLargeString',$NUM))"; done

2、验证表的大小

让我们验证表的大小（之前执行innodb_stats_persistent_sample_pages=100000 的 ANALYZE 表，以便统计信息尽可能真实）。

set global innodb_stats_persistent_sample_pages=100000;
analyze table test_compress;

+------------------------+---------+----------+----------+
| Table                  | Op      | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| PS_57.test_compress    | analyze | status   | OK       |
+------------------------+---------+----------+----------+
Query OK, 0 rows affected (0.00 sec)

select table_schema, table_name, table_rows,  round(data_length / 1024 / 1024)+round(index_length / 1024 / 1024)+round(data_free / 1024 / 1024) TOTAL_MB, create_options from information_schema.tables where table_name='test_compress';

+--------------+---------------+------------+----------+----------------+
| table_schema | table_name    | table_rows | TOTAL_MB | create_options |
+--------------+---------------+------------+----------+----------------+
| PS_57        | test_compress |     100000 |       37 |                |
+--------------+---------------+------------+----------+----------------+

3、对表压缩

接下来，我们将用KEY_BLOCK_SIZE=4压缩表（这个大小是任意选择的，在任何时候都没有指示或决定它是否是最优值，事实上，它不是）。

ALTER TABLE test_compress ROW_FORMAT=COMPRESSED,KEY_BLOCK_SIZE=4,ALGORITHM=INPLACE,LOCK=NONE;

Query OK, 0 rows affected (3.33 sec)

我们再次验证表的大小（以前执行innodb_stats_persistent_sample_pages=100000 的 ANAL