【MySQL】MySQL压缩表的一个bug
2022-07-17 11:34 abce 阅读(154) 评论(0) 编辑 收藏 举报版本：MySQL5.7
创建一张测试表：
create table abce_comp( 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_abce_comp_result(result), key index_abce_comp_timestamp(timestamp) );
插入10万行数据，用于测试：
for NUM in {1..100000}; do mysql -uroot -p"xxxxxxxx" abce -e "insert into abce_comp (id, identification_id, timestamp, action, result) values ($NUM,$NUM*100,now(),concat('string',$NUM),concat('VeryVeryLargeString',$NUM))"; done
我们来看看表的大小。再执行analyze table之前，将innodb_stats_persistent_sample_pages=100000。
> set global innodb_stats_persistent_sample_pages=100000; Query OK, 0 rows affected (0.00 sec) > analyze table abce_comp; +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | abce.abce_comp | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (0.08 sec) > select table_schema, table_name, table_rows,(data_length+index_length+data_free)/1024/1024 TOTAL_MB, create_options from information_schema.tables where table_name='abce_comp'; +--------------+------------+------------+-------------+----------------+ | table_schema | table_name | table_rows | TOTAL_MB | create_options | +--------------+------------+------------+-------------+----------------+ | abce | abce_comp | 100000 | 22.57812500 | | +--------------+------------+------------+-------------+----------------+ 1 row in set (0.00 sec)
现在，开始压缩表，将key_block_size设置成4(块大小是我随机选的)。
> alter table abce_comp row_format=compressed,key_block_size=4,algorithm=inplace,lock=none; Query OK, 0 rows affected (1.98 sec) Records: 0 Duplicates: 0 Warnings: 0
按照上面的步骤，再次收集表的统计信息：
> set global innodb_stats_persistent_sample_pages=100000; Query OK, 0 rows affected (0.00 sec) > analyze table abce_comp; +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | abce.abce_comp | analyze | status | OK | +----------------+---------+----------+----------+ 1 row in set (0.06 sec) > select table_schema, table_name, table_rows,(data_length+index_length+data_free)/1024/1024 TOTAL_MB, create_options from information_schema.tables where table_name='abce_comp'; +--------------+------------+------------+------------+----------------------------------------+ | table_schema | table_name | table_rows | TOTAL_MB | create_options | +--------------+------------+------------+------------+----------------------------------------+ | abce | abce_comp | 100000 | 7.14843750 | row_format=COMPRESSED KEY_BLOCK_SIZE=4 | +--------------+------------+------------+------------+----------------------------------------+ 1 row in set (0.00 sec)
可以看到，表已经被压缩了。再来看看表的结构：
> show create table abce_comp\G
*************************** 1. row ***************************
Table: abce_comp
Create Table: CREATE TABLE `abce_comp` (
`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_abce_comp_result` (`result`),
KEY `index_abce_comp_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
1 row in set (0.00 sec)
如何给表取消压缩呢？是不是应该很简单。
> alter table abce_comp row_format=default,algorithm=inplace,lock=none; Query OK, 0 rows affected (1.85 sec) Records: 0 Duplicates: 0 Warnings: 0
语句执行成功，看起来是起效果了：
> select table_schema, table_name, table_rows,(data_length+index_length+data_free)/1024/1024 TOTAL_MB, create_options from information_schema.tables where table_name='abce_comp'; +--------------+------------+------------+------------+------------------+ | table_schema | table_name | table_rows | TOTAL_MB | create_options | +--------------+------------+------------+------------+------------------+ | abce | abce_comp | 100000 | 7.14843750 | KEY_BLOCK_SIZE=4 | +--------------+------------+------------+------------+------------------+
再看看表结构：
> show create table abce_comp\G
*************************** 1. row ***************************
Table: abce_comp
Create Table: CREATE TABLE `abce_comp` (
`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_abce_comp_result` (`result`),
KEY `index_abce_comp_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 KEY_BLOCK_SIZE=4
哪里出了点差错，表的KEY_BLOCK_SIZE仍然是4。
再来一次尝试：
> alter table abce_comp row_format=default,key_block_size=0,algorithm=inplace,lock=none; Query OK, 0 rows affected (0.91 sec) Records: 0 Duplicates: 0 Warnings: 0
> select table_schema, table_name, table_rows,(data_length+index_length+data_free)/1024/1024 TOTAL_MB, create_options from information_schema.tables where table_name='abce_comp';
+--------------+------------+------------+-------------+----------------+
| table_schema | table_name | table_rows | TOTAL_MB | create_options |
+--------------+------------+------------+-------------+----------------+
| abce | abce_comp | 100000 | 18.54687500 | |
+--------------+------------+------------+-------------+----------------+
> show create table abce_comp\G
*************************** 1. row ***************************
Table: abce_comp
Create Table: CREATE TABLE `abce_comp` (
`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_abce_comp_result` (`result`) KEY_BLOCK_SIZE=4,
KEY `index_abce_comp_timestamp` (`timestamp`) KEY_BLOCK_SIZE=4
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
又出错了！主键和辅助索引仍然是KEY_BLOCK_SIZE=4
尽管当表从压缩转换为非压缩表时，索引的KEY_BLOCK_SIZE在内部会遵循表的索引，但CREATE TABLE语句不会。起初这是一个美学/外观问题，但是当你进行dump时这是一个真正的问题，因为CREATE TABLE留下了KEY_BLOCK_SIZE值，这一点很不好。这是mysqldump的输出：
# mysqldump -uroot -pxxxxxxxx abce abce_comp --no-data > abce_comp.sql # more abce_comp.sql -- MySQL dump 10.13 Distrib 5.7.37, for linux-glibc2.12 (x86_64) -- -- Host: localhost Database: abce -- ------------------------------------------------------ -- Server version 5.7.37-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0; -- -- Table structure for table `abce_comp` -- DROP TABLE IF EXISTS `abce_comp`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `abce_comp` ( `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_abce_comp_result` (`result`) KEY_BLOCK_SIZE=4, KEY `index_abce_comp_timestamp` (`timestamp`) KEY_BLOCK_SIZE=4 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; /*!40101 SET character_set_client = @saved_cs_client */; -- -- GTID state at the end of the backup -- SET @@GLOBAL.GTID_PURGED='10ccf388-efb0-11ec-b477-08002762387f:1-100014'; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2022-07-11 21:49:52
如你所见，似乎无法使用全局ALTER TABLE命令（如果我们可以这样称呼它）来反转表定义中的索引的key_block_size，因此我们将进行最后一次尝试：
> alter table abce_comp
-> drop primary key, add primary key (id),
-> drop key index_abce_comp_result, add key index_abce_comp_result (result),
-> drop key index_abce_comp_timestamp, add key index_abce_comp_timestamp (timestamp),
-> row_format=default,key_block_size=0,algorithm=inplace,lock=none;
现在再看看结果：
> show create table abce_comp\G
*************************** 1. row ***************************
Table: abce_comp
Create Table: CREATE TABLE `abce_comp` (
`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_abce_comp_result` (`result`),
KEY `index_abce_comp_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
> select table_schema, table_name, table_rows,(data_length+index_length+data_free)/1024/1024 TOTAL_MB, create_options from information_schema.tables where table_name='abce_comp';
+--------------+------------+------------+-------------+----------------+
| table_schema | table_name | table_rows | TOTAL_MB | create_options |
+--------------+------------+------------+-------------+----------------+
| abce | abce_comp | 100000 | 18.54687500 | |
+--------------+------------+------------+-------------+----------------+
显然，这是一个bug：https://bugs.mysql.com/bug.php?id=56628
在MySQL 5.7中，完全还原的唯一方法（至少在表及其索引的定义中）是重新生成主键及其所有索引。这听起来像是一个终端解决方案，但如果你使用mysqldump进行备份（我们总是建议使用 Percona XtraBackup 来实现这些目的，它更快更高效）这是一个需要考虑的问题，因为它在其定义中保留了那些错误的定义。
幸运的是，这在MySQL 8中已修复。MySQL 8中，执行以下语句，就可以将压缩表转换成非压缩表
> alter table abce_comp ROW_FORMAT=DEFAULT, KEY_BLOCK_SIZE=0,ALGORITHM=INPLACE,LOCK=NONE;
