【MySQL】MySQL压缩表的一个bug

版本：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;