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

泉缘泉  金牌会员 | 2022-8-25 20:21:17 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 548|帖子 548|积分 1654

前言

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

在介绍压缩表变成普通表前，首先给大家普及下，什么是表压缩。
表压缩，意思是使表中的数据以压缩格式存储，压缩能够显著提高处理速度和压缩磁盘 。压缩意味着在硬盘和内存之间传输的数据更小且占用相对少的内存及硬盘，对于辅助索引，这种压缩带来更加明显的好处，因为索引数据也被压缩了。
表压缩是有很大好处的，能减少磁盘的I/O，还能提高系统吞吐量，节约空间，压缩率越大，占用的磁盘空间越小，文件传输时间提升，降低数据的存储和网络传输成本。
2、如何表压缩( mysql的版本需要大于5.5 )

1、首先设置my.inf参数
  1. #打开配置文件
  2. vim /etc/my.inf
  4. #加入配置项
  5. innodb_file_per_table=1
  6. innodb_file_format=Barracuda
  7. innodb_strict_mode=1 #建议加上
  8. innodb_default_row_format = COMPRESSED #在整个库默认启用行压缩格式时设定，一边不改变此值
  10. #重启数据库
  11. systemctl restart mysqld
复制代码
2、对表压缩
  1. mysql> alter table t1 ROW_FORMAT=COMPRESSED;
复制代码
3、压缩表转换为普通表
  1. mysql> alter table t1 ROW_FORMAT=DEFAULT;
复制代码
针对mysql5.7开始实验


  • mysql数据库版本：5.7.31
  • linux版本：centos5.7
1、建表和初始化测试数据
  1. #1、建表
  2. CREATE TABLE test_compress (
  3.    id bigint(20) unsigned NOT NULL,
  4.    identification_id int(10) unsigned DEFAULT NULL,
  5.    timestamp datetime NOT NULL,
  6.    action varchar(50) NOT NULL,
  7.    result varchar(50) NOT NULL,
  8.    PRIMARY KEY (id),
  9.    KEY INDEX_test_compress_result (result),
  10.    KEY INDEX_test_compress_timestamp (timestamp)
  11.    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  13. #2、插入测试数据（linux里执行脚本）
  14. 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 表，以便统计信息尽可能真实）。
  1. set global innodb_stats_persistent_sample_pages=100000;
  2. analyze table test_compress;
  4. +------------------------+---------+----------+----------+
  5. | Table                  | Op      | Msg_type | Msg_text |
  6. +------------------------+---------+----------+----------+
  7. | PS_57.test_compress    | analyze | status   | OK       |
  8. +------------------------+---------+----------+----------+
  9. Query OK, 0 rows affected (0.00 sec)
复制代码
  1. 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';
  3. +--------------+---------------+------------+----------+----------------+
  4. | table_schema | table_name    | table_rows | TOTAL_MB | create_options |
  5. +--------------+---------------+------------+----------+----------------+
  6. | PS_57        | test_compress |     100000 |       37 |                |
  7. +--------------+---------------+------------+----------+----------------+
复制代码
3、对表压缩

接下来，我们将用KEY_BLOCK_SIZE=4压缩表（这个大小是任意选择的，在任何时候都没有指示或决定它是否是最优值，事实上，它不是）。
  1. ALTER TABLE test_compress ROW_FORMAT=COMPRESSED,KEY_BLOCK_SIZE=4,ALGORITHM=INPLACE,LOCK=NONE;
  3. Query OK, 0 rows affected (3.33 sec)
复制代码
我们再次验证表的大小（以前执行innodb_stats_persistent_sample_pages=100000 的 ANALYZE 表，以便统计信息尽可能真实）。
  1. set global innodb_stats_persistent_sample_pages=100000;
  3. Query OK, 0 rows affected (0.00 sec)
复制代码
  1. analyze table test_compress;
  3. +------------------------+---------+----------+----------+
  4. | Table                  | Op      | Msg_type | Msg_text |
  5. +------------------------+---------+----------+----------+
  6. | PS_57.test_compress    | analyze | status   | OK       |
  7. +------------------------+---------+----------+----------+
  8. Query OK, 0 rows affected (0.00 sec)
复制代码
  1. 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';
  3. +--------------+---------------+------------+----------+----------------------------------------+
  4. | table_schema | table_name    | table_rows | TOTAL_MB | create_options                         |
  5. +--------------+---------------+------------+----------+----------------------------------------+
  6. | PS_57        | test_compress |     100000 |       19 | row_format=COMPRESSED KEY_BLOCK_SIZE=4 |
  7. +--------------+---------------+------------+----------+----------------------------------------+
复制代码
该表已被压缩，让我们检查其结构。
  1. show create table test_compress;
  2. *************************** 1. row ***************************
  3.       Table: test_compress
  4. Create Table: CREATE TABLE `test_compress` (
  5.  `id` bigint(20) unsigned NOT NULL,
  6.  `identification_id` int(10) unsigned DEFAULT NULL,
  7.  `timestamp` datetime NOT NULL,
  8.  `action` varchar(50) NOT NULL,
  9.  `result` varchar(50) NOT NULL,
  10.  PRIMARY KEY (`id`),
  11.  KEY `INDEX_test_compress_result` (`result`),
  12.  KEY `INDEX_test_compress_timestamp` (`timestamp`)
  13. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
  15. 1 row in set (0.00 sec)
复制代码
4、压缩表解压缩（变成普通表）
  1. ALTER TABLE test_compress ROW_FORMAT=DEFAULT,ALGORITHM=INPLACE,LOCK=NONE;
  3. Query OK, 0 rows affected (6.25 sec)
  4. Records: 0  Duplicates: 0  Warnings: 0
复制代码
解压缩成功，让我们检查看看。
  1. 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';
  3. +--------------+---------------+------------+----------+--------------------+
  4. | table_schema | table_name    | table_rows | TOTAL_MB | create_options     |
  5. +--------------+---------------+------------+----------+--------------------+
  6. | PS_57        | test_compress |     100000 |       25 | KEY_BLOCK_SIZE=4   |
  7. +--------------+---------------+------------+----------+--------------------+
复制代码
更好的检查：
  1. show create table test_compress;
  3. *************************** 1. row ***************************
  4.       Table: test_compress
  5. Create Table: CREATE TABLE `test_compress` (
  6.  `id` bigint(20) unsigned NOT NULL,
  7.  `identification_id` int(10) unsigned DEFAULT NULL,
  8.  `timestamp` datetime NOT NULL,
  9.  `action` varchar(50) NOT NULL,
  10.  `result` varchar(50) NOT NULL,
  11.  PRIMARY KEY (`id`),
  12.  KEY `INDEX_test_compress_result` (`result`),
  13.  KEY `INDEX_test_compress_timestamp` (`timestamp`)
  14. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4
复制代码
出了点问题！KEY_BLOCK_SIZE仍然是4。
第二次尝试：
  1. ALTER TABLE test_compress ROW_FORMAT=DEFAULT,KEY_BLOCK_SIZE=0,ALGORITHM=INPLACE,LOCK=NONE;
  3. Query OK, 0 rows affected (2.05 sec)
  4. Records: 0  Duplicates: 0  Warnings: 0
复制代码
  1. 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';
  2. +--------------+---------------+------------+----------+--------------------+
  3. | table_schema | table_name    | table_rows | TOTAL_MB | create_options     |
  4. +--------------+---------------+------------+----------+--------------------+
  5. | PS_57        | test_compress |     100000 |       25 |                    |
  6. +--------------+---------------+------------+----------+--------------------+
复制代码
更好的检查：
  1. show create table test_compress\G
  2. *************************** 1. row ***************************
  3.       Table: test_compress
  4. Create Table: CREATE TABLE `test_compress` (
  5.  `id` bigint(20) unsigned NOT NULL,
  6.  `identification_id` int(10) unsigned DEFAULT NULL,
  7.  `timestamp` datetime NOT NULL,
  8.  `action` varchar(50) NOT NULL,
  9.  `result` varchar(50) NOT NULL,
  10.  PRIMARY KEY (`id`) KEY_BLOCK_SIZE=4,
  11.  KEY `INDEX_test_compress_result` (`result`) KEY_BLOCK_SIZE=4,
  12.  KEY `INDEX_test_compress_timestamp` (`timestamp`) KEY_BLOCK_SIZE=4
  13. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
复制代码
出了点问题！主键和二级索引都继续显示 KEY_BLOCK_SIZE=4。
尽管当表从压缩转换为未压缩时，在内部，索引的KEY_BLOCK_SIZE支持表的索引，但 CREATE TABLE 语句则不然。起初，这将是一个美学/外观问题，但是当您进行转储时，这是一个真正的问题，因为CREATE TABLE保留了KEY_BLOCK_SIZE值，这并不好。以下是 mysqldump 的输出：
  1. mysqldump -h localhost PS_57 test_compress --no-data > test_compress.sql
复制代码
  1. cat test_compress.sql
  2. ...
  3. --
  4. -- Table structure for table `test_compress`
  5. --
  7. DROP TABLE IF EXISTS `test_compress`;
  8. /*!40101 SET @saved_cs_client     = @@character_set_client */;
  9. /*!50503 SET character_set_client = utf8mb4 */;
  10. CREATE TABLE `test_compress` (
  11.  `id` bigint(20) unsigned NOT NULL,
  12.  `identification_id` int(10) unsigned DEFAULT NULL,
  13.  `timestamp` datetime NOT NULL,
  14.  `action` varchar(50) NOT NULL,
  15.  `result` varchar(50) NOT NULL,
  16.  PRIMARY KEY (`id`) KEY_BLOCK_SIZE=4,
  17.  KEY `INDEX_test_compress_result` (`result`) KEY_BLOCK_SIZE=4,
  18.  KEY `INDEX_test_compress_timestamp` (`timestamp`) KEY_BLOCK_SIZE=4
  19. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  20. /*!40101 SET character_set_client = @saved_cs_client */;
复制代码
如您所见，似乎没有办法使用全局 ALTER TABLE 命令（如果可以这样称呼它）在表定义索引方面反转KEY_BLOCK_SIZE，因此我们将进行最后一次尝试：
  1. ALTER TABLE test_compress
  2. DROP PRIMARY KEY, add PRIMARY KEY (id),
  3. DROP key INDEX_test_compress_result, add key INDEX_test_compress_result (result),
  4. DROP key INDEX_test_compress_timestamp, add key INDEX_test_compress_timestamp (timestamp),
  5. ROW_FORMAT=DEFAULT,KEY_BLOCK_SIZE=0,ALGORITHM=INPLACE,LOCK=NONE;
复制代码
现在，它具有正确的定义，没有KEY_BLOCK_SIZE：
  1. show create table test_compress;
  2. *************************** 1. row ***************************
  3.        Table: test_compress
  4. Create Table: CREATE TABLE `test_compress` (
  5.   `id` bigint(20) unsigned NOT NULL,
  6.   `identification_id` int(10) unsigned DEFAULT NULL,
  7.   `timestamp` datetime NOT NULL,
  8.   `action` varchar(50) NOT NULL,
  9.   `result` varchar(50) NOT NULL,
  10.   PRIMARY KEY (`id`),
  11.   KEY `INDEX_test_compress_result` (`result`),
  12.   KEY `INDEX_test_compress_timestamp` (`timestamp`)
  13. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  14. 1 row in set (0.00 sec)
复制代码
  1. 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';
  2. +--------------+---------------+------------+----------+----------------+
  3. | table_schema | table_name    | table_rows | TOTAL_MB | create_options |
  4. +--------------+---------------+------------+----------+----------------+
  5. | PS_57        | test_compress |     100000 |       25 |                |
  6. +--------------+---------------+------------+----------+----------------+
复制代码
5、针对第4步出现问题的bug

mysql里有解释这个bug: https://bugs.mysql.com/bug.php?id=56628
针对mysql8实验

在MySQL 8中，情况如下：
  1. 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';
  3. +--------------+---------------+------------+----------+----------------+
  4. | TABLE_SCHEMA | TABLE_NAME    | TABLE_ROWS | TOTAL_MB | CREATE_OPTIONS |
  5. +--------------+---------------+------------+----------+----------------+
  6. | PS_8         | test_compress |      31000 |       15 |                |
  7. +--------------+---------------+------------+----------+----------------+
复制代码
让我们执行 ALTER 来压缩表：
  1. alter table test_compress ROW_FORMAT=COMPRESSED,KEY_BLOCK_SIZE=4,ALGORITHM=INPLACE,LOCK=NONE;
  3. Query OK, 0 rows affected (4.54 sec)
  4. Records: 0  Duplicates: 0  Warnings: 0
复制代码
让我们再检查一下：
  1. analyze table test_compress;
  3. +-----------------------+---------+----------+----------+
  4. | Table                 | Op      | Msg_type | Msg_text |
  5. +-----------------------+---------+----------+----------+
  6. | PS_8.test_compress    | analyze | status   | OK       |
  7. +-----------------------+---------+----------+----------+
  8. 1 row in set (0.07 sec)
复制代码
  1. 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';
  3. +--------------+---------------+------------+----------+----------------------------------------+
  4. | TABLE_SCHEMA | TABLE_NAME    | TABLE_ROWS | TOTAL_MB | CREATE_OPTIONS                         |
  5. +--------------+---------------+------------+----------+----------------------------------------+
  6. | PS_8         | test_compress |     100000 |       19 | row_format=COMPRESSED KEY_BLOCK_SIZE=4 |
  7. +--------------+---------------+------------+----------+----------------------------------------+
复制代码
  1. show create table test_compress;
  2. *************************** 1. row ***************************
  3.       Table: test_compress
  4. Create Table: CREATE TABLE `test_compress` (
  5.  `id` bigint unsigned NOT NULL,
  6.  `identification_id` int unsigned DEFAULT NULL,
  7.  `timestamp` datetime NOT NULL,
  8.  `action` varchar(50) NOT NULL,
  9.  `result` varchar(50) NOT NULL,
  10.  PRIMARY KEY (`id`),
  11.  KEY `INDEX_test_compress_result` (`result`),
  12.  KEY `INDEX_test_compress_timestamp` (`timestamp`)
  13. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
  14. 1 row in set (0.01 sec)
复制代码
到目前为止，一切都与MySQL 5.7相同：KEY_BLOCK_SIZE保留在整个表的定义中，而不是索引的定义中。
同样的，也能通过下面sql对表进行解压缩：
  1. alter table test_compress ROW_FORMAT=DEFAULT, KEY_BLOCK_SIZE=0,ALGORITHM=INPLACE,LOCK=NONE;
  3. Query OK, 0 rows affected (2.56 sec)
  4. Records: 0  Duplicates: 0  Warnings: 0
复制代码
查看解压缩情况
  1. show create table test_compress;
  3. *************************** 1. row ***************************
  4.       Table: test_compress
  5. Create Table: CREATE TABLE `test_compress` (
  6.  `id` bigint unsigned NOT NULL,
  7.  `identification_id` int unsigned DEFAULT NULL,
  8.  `timestamp` datetime NOT NULL,
  9.  `action` varchar(50) NOT NULL,
  10.  `result` varchar(50) NOT NULL,
  11.  PRIMARY KEY (`id`),
  12.  KEY `INDEX_test_compress_result` (`result`),
  13.  KEY `INDEX_test_compress_timestamp` (`timestamp`)
  14. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  15. 1 row in set (0.00 sec)
复制代码
  1. 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';
  3. +--------------+---------------+------------+----------+----------------+
  4. | TABLE_SCHEMA | TABLE_NAME    | TABLE_ROWS | TOTAL_MB | CREATE_OPTIONS |
  5. +--------------+---------------+------------+----------+----------------+
  6. | PS_8         | test_compress |     100000 |       25 |                |
  7. +--------------+---------------+------------+----------+----------------+
复制代码
结论

在MySQL 5.7中，完全解压缩一张压缩表的唯一方法（至少在表及其索引的定义中）是重新生成主键及其所有索引。否则， 主键和二级索引都继续显示压缩表时候的KEY_BLOCK_SIZE。
然后在MySQL8里，修复了这个问题在MySQL5.7出现的问题。
更多内容请关注微信公众号【编程达人】，分享优质好文章，编程黑科技，助你成为编程达人！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

泉缘泉

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

挺好的 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表