本文章做了把mysql表从压缩表过渡到普通表的实验过程，看看压缩表变成普通表会发生什么？本文针对mysql5.7和mysql8分别进行了实验。
1、什么是表压缩
在将压缩表引入普通表之前，让我们首先普及表压缩是什么。
[En]
Before introducing compressed tables to ordinary tables, let’s first popularize what table compression is.
表压缩，即表中的数据是以压缩格式存储的，压缩可以显著提高处理速度，压缩磁盘。压缩意味着硬盘和内存之间传输的数据更少，占用的内存和硬盘相对更少，这种压缩给二级索引带来的好处更明显，因为索引数据也是压缩的。
[En]
Table compression, which means that the data in the table is stored in a compressed format, compression can significantly improve processing speed and compress the disk. Compression means that less data is transferred between the hard disk and memory and takes up relatively less memory and the hard disk, and this compression brings more obvious benefits to secondary indexes because the index data is also compressed.
表压缩是有很大好处的，能减少磁盘的I/O，还能提高系统吞吐量，节约空间，压缩率越大，占用的磁盘空间越小，文件传输时间提升，降低数据的存储和网络传输成本。
2、如何表压缩( mysql的版本需要大于5.5 )
#打开配置文件
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
mysql> alter table t1 ROW_FORMAT=COMPRESSED;
mysql> alter table t1 ROW_FORMAT=DEFAULT;
- 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 的 ANALYZE 表，以便统计信息尽可能真实）。
set global innodb_stats_persistent_sample_pages=100000;
Query OK, 0 rows affected (0.00 sec)
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 | 19 | row_format=COMPRESSED KEY_BLOCK_SIZE=4 |
+--------------+---------------+------------+----------+----------------------------------------+
这张桌子已经被压缩了，让我们检查一下它的结构。
[En]
The table has been compressed, let’s check its structure.
show create table test_compress;
*************************** 1. row ***************************
Table: test_compress
Create Table: 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 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
1 row in set (0.00 sec)
4、压缩表解压缩（变成普通表）
ALTER TABLE test_compress ROW_FORMAT=DEFAULT,ALGORITHM=INPLACE,LOCK=NONE;
Query OK, 0 rows affected (6.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
拉链解压成功，让我们来检查一下。
[En]
Unzipped successfully, let’s check it.
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 | 25 | KEY_BLOCK_SIZE=4 |
+--------------+---------------+------------+----------+--------------------+
更好的检查：
show create table test_compress;
*************************** 1. row ***************************
Table: test_compress
Create Table: 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 KEY_BLOCK_SIZE=4
出了点问题！KEY_BLOCK_SIZE仍然是4。
第二次尝试：
ALTER TABLE test_compress ROW_FORMAT=DEFAULT,KEY_BLOCK_SIZE=0,ALGORITHM=INPLACE,LOCK=NONE;
Query OK, 0 rows affected (2.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
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 | 25 | |
+--------------+---------------+------------+----------+--------------------+
更好的检查：
show create table test_compress\G
*************************** 1. row ***************************
Table: test_compress
Create Table: 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_BLOCK_SIZE=4,
KEY INDEX_test_compress_result (result) KEY_BLOCK_SIZE=4,
KEY INDEX_test_compress_timestamp (timestamp) KEY_BLOCK_SIZE=4
) ENGINE=InnoDB DEFAULT CHARSET=latin1
出了点问题！主键和二级索引都继续显示 KEY_BLOCK_SIZE=4。
尽管当表从压缩转换为未压缩时，在内部，索引的KEY_BLOCK_SIZE支持表的索引，但 CREATE TABLE 语句则不然。起初，这将是一个美学/外观问题，但是当您进行转储时，这是一个真正的问题，因为CREATE TABLE保留了KEY_BLOCK_SIZE值，这并不好。以下是 mysqldump 的输出：
mysqldump -h localhost PS_57 test_compress --no-data > test_compress.sql
cat test_compress.sql
...
--
-- Table structure for table test_compress
--
DROP TABLE IF EXISTS test_compress;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
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_BLOCK_SIZE=4,
KEY INDEX_test_compress_result (result) KEY_BLOCK_SIZE=4,
KEY INDEX_test_compress_timestamp (timestamp) KEY_BLOCK_SIZE=4
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
ALTER TABLE test_compress
DROP PRIMARY KEY, add PRIMARY KEY (id),
DROP key INDEX_test_compress_result, add key INDEX_test_compress_result (result),
DROP key INDEX_test_compress_timestamp, add key INDEX_test_compress_timestamp (timestamp),
ROW_FORMAT=DEFAULT,KEY_BLOCK_SIZE=0,ALGORITHM=INPLACE,LOCK=NONE;
现在，它具有正确的定义，没有KEY_BLOCK_SIZE：
show create table test_compress;
*************************** 1. row ***************************
Table: test_compress
Create Table: 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
1 row in set (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 | 25 | |
+--------------+---------------+------------+----------+----------------+
5、针对第4步出现问题的bug
在MySQL 8中，情况如下：
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_8 | test_compress | 31000 | 15 | |
+--------------+---------------+------------+----------+----------------+
让我们执行 ALTER 来压缩表：
alter table test_compress ROW_FORMAT=COMPRESSED,KEY_BLOCK_SIZE=4,ALGORITHM=INPLACE,LOCK=NONE;
Query OK, 0 rows affected (4.54 sec)
Records: 0 Duplicates: 0 Warnings: 0
让我们再检查一下：
analyze table test_compress;
+-----------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| PS_8.test_compress | analyze | status | OK |
+-----------------------+---------+----------+----------+
1 row in set (0.07 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_8 | test_compress | 100000 | 19 | row_format=COMPRESSED KEY_BLOCK_SIZE=4 |
+--------------+---------------+------------+----------+----------------------------------------+
show create table test_compress;
*************************** 1. row ***************************
Table: test_compress
Create Table: CREATE TABLE test_compress (
id bigint unsigned NOT NULL,
identification_id int 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 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
1 row in set (0.01 sec)
到目前为止，一切都与MySQL 5.7相同：KEY_BLOCK_SIZE保留在整个表的定义中，而不是索引的定义中。
同样的，也能通过下面sql对表进行解压缩：
alter table test_compress ROW_FORMAT=DEFAULT, KEY_BLOCK_SIZE=0,ALGORITHM=INPLACE,LOCK=NONE;
Query OK, 0 rows affected (2.56 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看解压缩情况
show create table test_compress;
*************************** 1. row ***************************
Table: test_compress
Create Table: CREATE TABLE test_compress (
id bigint unsigned NOT NULL,
identification_id int 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
1 row in set (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_8 | test_compress | 100000 | 25 | |
+--------------+---------------+------------+----------+----------------+
在MySQL 5.7中，完全解压缩一张压缩表的唯一方法（至少在表及其索引的定义中）是重新生成主键及其所有索引。否则， 主键和二级索引都继续显示压缩表时候的KEY_BLOCK_SIZE。
然后在MySQL8里，修复了这个问题在MySQL5.7出现的问题。
更多资讯，敬请关注微信公众号[程序员]，分享优质文章，编程酷科技，帮你成为程序员！
[En]
For more information, please follow the Wechat official account [programmer], share high-quality articles, programming cool techs, to help you become a programmer!
Original: https://www.cnblogs.com/zhbeier/p/16470091.html
Author: 奇想派
Title: 当mysql表从压缩表变成普通表会发生什么
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505242/
转载文章受原作者版权保护。转载请注明原作者出处！