MySQL:压缩，索引和两个吸烟桶

MySQL压缩大家好，我们都知道在MySQL中表压缩的好处，以目前的技术，处理速度和压缩磁盘，在大多数情况下，这是一个好处。

那么，如果没有这种情况会发生什么呢?

在这个简单的博客中，我将向您展示一些有趣的事情，当您试图从压缩表转换到普通表时，会发生这种情况。我们不打算详细讨论或讨论其他问题，例如:为什么表被压缩是件坏事?性能如何，系统性能如何，甚至不包括压缩需要消耗多少硬件资源?我们也不打算讨论内存中的页面是压缩的、未压缩的，还是两者都有。这已经讨论得够多了，你可以在我们的网站上找到一些非常有趣的博客，甚至在文档中也可以找到。

我将向您展示的是一些非常有趣的东西，为此，我们将从头到尾以一种您可以自己完成的方式执行这个练习(好吧，除了数据加载部分，呵呵!)，我将给出一个示例脚本(稍后)。

让我们在数据库实例中创建表雷竞技下载官网Percona Server for MySQL 5.7安装。

CentOS7-S1 PS_57> 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;查询OK, 0行受影响(0.02秒)

CentOS7-S1 PS_57 > 创建表格 test_compress （

id 长整型数字（ 20. ）无符号不零，

identification_id int （ 10 ）无符号默认的零，

时间戳 datetime 不零，

行动 varchar （ 50 ）不零，

结果 varchar （ 50 ）不零，

主要的关键（ id ），

关键 INDEX_test_compress_result （结果），

关键 INDEX_test_compress_timestamp （时间戳）

）引擎＝InnoDB 默认的字符集＝latin1 ；

查询好吧， 0 行影响（ 0.02 证券交易委员会）

之后，让我们用一些数据(100,000行)加载表。

CentOS7-S1 $为NUM在{1..100000};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))";完成

1	CentOS7-S1 ＄为全国矿工工会在｛ 1 . . 100000 ｝；做 mysql -h 本地主机 PS_57 -e "insert into test_compress (id, identification_id, timestamp, action, result) values ($NUM，$NUM*100,now()，concat('string'，$NUM)，concat('VeryVeryLargeString'，$NUM))" ；完成

让我们验证表的大小(之前使用innodb_stats_persistent_sample_pages=100000执行ANALYZE table，以便统计数据尽可能真实)。

set global innodb_stats_persistent_sample_pages=100000;查询OK, 0行受影响(0.00秒)

1	CentOS7-S1 PS_57 > 集全球 innodb_stats_persistent_sample_pages＝100000 ；查询好吧， 0 行影响（ 0．00 证券交易委员会）

CentOS7-S1 PS_57>分析表test_compress;+------------------------+---------+----------+----------+ | 表| Op | Msg_type | Msg_text  | +------------------------+---------+----------+----------+ | PS_57。test_compress |分析| |好状态  | +------------------------+---------+----------+----------+ 查询好,0行影响(0.00秒)

CentOS7-S1 PS_57 > 分析表格 test_compress ；

+------------------------+---------+----------+----------+

| 表格 | 人事处 | Msg_type | Msg_text |

+------------------------+---------+----------+----------+

| PS_57 .test_compress | 分析 | 状态 | 好吧 |

+------------------------+---------+----------+----------+

查询好吧， 0 行影响（ 0．00 证券交易委员会）

CentOS7-S1 PS_57> 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。表table_name='test_compress';+--------------+---------------+------------+----------+----------------+ | table_schema | table_name | table_rows | TOTAL_MB | create_options  | +--------------+---------------+------------+----------+----------------+ | PS_57 | test_compress | 100000 | 37  | | +--------------+---------------+------------+----------+----------------+

CentOS7-S1 PS_57 > 选择 table_schema ， table_name ， table_rows ，轮（ data_length / 1024 / 1024 ）+轮（ index_length / 1024 / 1024 ）+轮（ data_free / 1024 / 1024 ） TOTAL_MB ， create_options 从 information_schema .tables 在哪里 table_name＝“test_compress” ；

+--------------+---------------+------------+----------+----------------+

+--------------+---------------+------------+----------+----------------+

| PS_57 | test_compress | 100000 | 37 | |

+--------------+---------------+------------+----------+----------------+

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

ALTER TABLE test_compress ROW_FORMAT=COMPRESSED,KEY_BLOCK_SIZE=4,ALGORITHM=INPLACE,LOCK=NONE;查询OK, 0行受影响(3.33秒)

1 2	CentOS7-S1 PS_57 > 改变表格 test_compress ROW_FORMAT＝压缩， KEY_BLOCK_SIZE＝4 ，算法＝原地，锁＝没有一个；查询好吧， 0 行影响（ 3.33 证券交易委员会）

我们再次验证表的大小(之前使用innodb_stats_persistent_sample_pages=100000执行ANALYZE table，以便统计数据尽可能真实)。

set global innodb_stats_persistent_sample_pages=100000;查询OK, 0行受影响(0.00秒)

1 2	CentOS7-S1 PS_57 > 集全球 innodb_stats_persistent_sample_pages＝100000 ；查询好吧， 0 行影响（ 0．00 证券交易委员会）

CentOS7-S1 PS_57>分析表test_compress;+------------------------+---------+----------+----------+ | 表| Op | Msg_type | Msg_text  | +------------------------+---------+----------+----------+ | PS_57。test_compress |分析| |好状态  | +------------------------+---------+----------+----------+ 查询好,0行影响(0.00秒)

CentOS7-S1 PS_57 > 分析表格 test_compress ；

+------------------------+---------+----------+----------+

| 表格 | 人事处 | Msg_type | Msg_text |

+------------------------+---------+----------+----------+

| PS_57 .test_compress | 分析 | 状态 | 好吧 |

+------------------------+---------+----------+----------+

查询好吧， 0 行影响（ 0．00 证券交易委员会）

CentOS7-S1 PS_57> 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。表table_name='test_compress';+--------------+---------------+------------+----------+----------------------------------------+ | table_schema | table_name | table_rows | TOTAL_MB | create_options  | +--------------+---------------+------------+----------+----------------------------------------+ | PS_57 | test_compress | 100000 | | row_format =压缩KEY_BLOCK_SIZE = 4  | +--------------+---------------+------------+----------+----------------------------------------+

+--------------+---------------+------------+----------+----------------------------------------+

+--------------+---------------+------------+----------+----------------------------------------+

| PS_57 | test_compress | 100000 | 19 | row_format＝压缩 KEY_BLOCK_SIZE＝4 |

+--------------+---------------+------------+----------+----------------------------------------+

正如您所看到的，表已经被压缩了，让我们检查一下它的结构。

CentOS7-S1 PS_57 test_compress \ G >显示创建表  *************************** 1。row ***************************表:test_compress创建表: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，主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)

CentOS7-S1 PS_57 > 显示创建表格 test_compress ＼ G

*************************** 1. 行 ***************************

表格： test_compress

创建表格：创建表格｀ test_compress ｀（

｀ id ｀长整型数字（ 20. ）无符号不零，

｀ identification_id ｀ int （ 10 ）无符号默认的零，

｀时间戳｀ datetime 不零，

｀行动｀ varchar （ 50 ）不零，

｀结果｀ varchar （ 50 ）不零，

主要的关键（｀ id ｀），

关键｀ INDEX_test_compress_result ｀（｀结果｀），

关键｀ INDEX_test_compress_timestamp ｀（｀时间戳｀）

）引擎＝InnoDB 默认的字符集＝latin1 ROW_FORMAT＝压缩 KEY_BLOCK_SIZE＝4

1 行在集（ 0．00 证券交易委员会）

我们如何逆转压缩?好问题。应该很简单，对吧?

(澄清一下，在你问之前，我们不会根据表的大小来判断应该怎么做，这个ALTER会对复制产生什么影响，以及其他事情，我们的想法是展示如何反转它，而不是其他。)

我们可以使用这个命令，让我们看看:

ALTER TABLE test_compress ROW_FORMAT=DEFAULT,ALGORITHM=INPLACE,LOCK=NONE;查询OK, 0行受影响(6.25秒)记录:0重复项:0警告:0

CentOS7-S1 PS_57 > 改变表格 test_compress ROW_FORMAT＝默认的，算法＝原地，锁＝没有一个；

查询好吧， 0 行影响（ 6.25 证券交易委员会）

记录： 0 重复的： 0 警告： 0

这似乎起作用了!未压缩的:

CentOS7-S1 PS_57> 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。表table_name='test_compress';+--------------+---------------+------------+----------+--------------------+ | table_schema | table_name | table_rows | TOTAL_MB | create_options  | +--------------+---------------+------------+----------+--------------------+ | PS_57 | test_compress | 100000 | | KEY_BLOCK_SIZE = 4  | +--------------+---------------+------------+----------+--------------------+

+--------------+---------------+------------+----------+--------------------+

+--------------+---------------+------------+----------+--------------------+

| PS_57 | test_compress | 100000 | 25 | KEY_BLOCK_SIZE＝4 |

+--------------+---------------+------------+----------+--------------------+

更好的检查:

CentOS7-S1 PS_57 test_compress \ G >显示创建表  *************************** 1。row ***************************表:test_compress Create表: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，主键(' id ')， KEY ' INDEX_test_compress_result ' (' result ')， KEY ' INDEX_test_compress_timestamp ' (' timestamp ')) ENGINE=InnoDB DEFAULT CHARSET=latin1 KEY_BLOCK_SIZE=4

CentOS7-S1 PS_57 > 显示创建表格 test_compress ＼ G

*************************** 1. 行 ***************************

表格： test_compress

创建表格：创建表格｀ test_compress ｀（

｀ id ｀长整型数字（ 20. ）无符号不零，

｀ identification_id ｀ int （ 10 ）无符号默认的零，

｀时间戳｀ datetime 不零，

｀行动｀ varchar （ 50 ）不零，

｀结果｀ varchar （ 50 ）不零，

主要的关键（｀ id ｀），

关键｀ INDEX_test_compress_result ｀（｀结果｀），

关键｀ INDEX_test_compress_timestamp ｀（｀时间戳｀）

）引擎＝InnoDB 默认的字符集＝latin1 KEY_BLOCK_SIZE＝4

有点不对劲!KEY_BLOCK_SIZE仍然是4。

第二次尝试:

ALTER TABLE test_compress ROW_FORMAT=DEFAULT,KEY_BLOCK_SIZE=0,ALGORITHM=INPLACE,LOCK=NONE;查询OK, 0行受影响(2.05秒)记录:0重复:0警告:0

CentOS7-S1 PS_57 > 改变表格 test_compress ROW_FORMAT＝默认的， KEY_BLOCK_SIZE＝0 ，算法＝原地，锁＝没有一个；

查询好吧， 0 行影响（ 2.05 证券交易委员会）

记录： 0 重复的： 0 警告： 0

CentOS7-S1 PS_57> 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。表table_name='test_compress';+--------------+---------------+------------+----------+--------------------+ | table_schema | table_name | table_rows | TOTAL_MB | create_options  | +--------------+---------------+------------+----------+--------------------+ | PS_57 | test_compress | 100000 | 25  | | +--------------+---------------+------------+----------+--------------------+

+--------------+---------------+------------+----------+--------------------+

+--------------+---------------+------------+----------+--------------------+

| PS_57 | test_compress | 100000 | 25 | |

+--------------+---------------+------------+----------+--------------------+

更好的检查:

CentOS7-S1 PS_57 test_compress \ G >显示创建表  *************************** 1。row ***************************表:test_compress Create表: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

CentOS7-S1 PS_57 > 显示创建表格 test_compress ＼ G

*************************** 1. 行 ***************************

表格： test_compress

创建表格：创建表格｀ test_compress ｀（

｀ id ｀长整型数字（ 20. ）无符号不零，

｀ identification_id ｀ int （ 10 ）无符号默认的零，

｀时间戳｀ datetime 不零，

｀行动｀ varchar （ 50 ）不零，

｀结果｀ varchar （ 50 ）不零，

主要的关键（｀ id ｀） KEY_BLOCK_SIZE＝4 ，

关键｀ INDEX_test_compress_result ｀（｀结果｀） KEY_BLOCK_SIZE＝4 ，

关键｀ INDEX_test_compress_timestamp ｀（｀时间戳｀） KEY_BLOCK_SIZE＝4

）引擎＝InnoDB 默认的字符集＝latin1

有点不对劲!主键和辅助索引都继续显示KEY_BLOCK_SIZE=4。

虽然当表从压缩转换为未压缩时，在内部索引的KEY_BLOCK_SIZE尊重表的KEY_BLOCK_SIZE，但CREATE table语句不这样做。一开始，这可能是一个美观/表面的问题，但当您执行转储时，这是一个真正的问题，因为CREATE TABLE留下了KEY_BLOCK_SIZE值，这并不好。下面是mysqldump的输出:

$ mysqldump -h localhost PS_57 test_compress——no-data > test_compress.sql

1	＄ , mysqldump -h 本地主机 PS_57 test_compress --没有-数据 > test_compress . sql

$ cat test_compress。sql……-- --表' test_compress '的表结构——DROP Table IF EXISTS ' test_compress ';/ * !40101集@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 */; ...

20.

＄猫 test_compress . sql

．．．

-- 表格结构为表格｀ test_compress ｀

下降表格如果存在｀ test_compress ｀；

／*！ 40101 集 @ saved_cs_client ＝ @ @ character_set_client * /；

／*！ 50503 集 character_set_client ＝ utf8mb4 * /；

创建表格｀ test_compress ｀（

｀ id ｀长整型数字（ 20. ）无符号不零，

｀ identification_id ｀ int （ 10 ）无符号默认的零，

｀时间戳｀ datetime 不零，

｀行动｀ varchar （ 50 ）不零，

｀结果｀ varchar （ 50 ）不零，

主要的关键（｀ id ｀） KEY_BLOCK_SIZE＝4 ，

关键｀ INDEX_test_compress_result ｀（｀结果｀） KEY_BLOCK_SIZE＝4 ，

关键｀ INDEX_test_compress_timestamp ｀（｀时间戳｀） KEY_BLOCK_SIZE＝4

）引擎＝InnoDB 默认的字符集＝latin1 ；

／*！ 40101 集 character_set_client ＝ @ saved_cs_client * /；

．．．

正如你所看到的，似乎没有办法用全局ALTER table命令(如果我们可以这样称呼它的话)来反转表定义索引中的KEY_BLOCK_SIZE，所以我们将做最后一次尝试:

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;

改变表格 test_compress

下降主要的关键，添加主要的关键（ id ），

下降关键 INDEX_test_compress_result ，添加关键 INDEX_test_compress_result （结果），

下降关键 INDEX_test_compress_timestamp ，添加关键 INDEX_test_compress_timestamp （时间戳），

ROW_FORMAT＝默认的， KEY_BLOCK_SIZE＝0 ，算法＝原地，锁＝没有一个；

现在，它有了没有KEY_BLOCK_SIZE的正确定义:

CentOS7-S1 PS_57 test_compress \ G >显示创建表  *************************** 1。表:Create表: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，主键(' 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)

CentOS7-S1 PS_57 > 显示创建表格 test_compress ＼ G

*************************** 1. 行 ***************************

表格： test_compress

创建表格：创建表格｀ test_compress ｀（

｀ id ｀长整型数字（ 20. ）无符号不零，

｀ identification_id ｀ int （ 10 ）无符号默认的零，

｀时间戳｀ datetime 不零，

｀行动｀ varchar （ 50 ）不零，

｀结果｀ varchar （ 50 ）不零，

主要的关键（｀ id ｀），

关键｀ INDEX_test_compress_result ｀（｀结果｀），

关键｀ INDEX_test_compress_timestamp ｀（｀时间戳｀）

）引擎＝InnoDB 默认的字符集＝latin1

1 行在集（ 0．00 证券交易委员会）

CentOS7-S1 PS_57> 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。表table_name='test_compress';+--------------+---------------+------------+----------+----------------+ | table_schema | table_name | table_rows | TOTAL_MB | create_options  | +--------------+---------------+------------+----------+----------------+ | PS_57 | test_compress | 100000 | 25  | | +--------------+---------------+------------+----------+----------------+

+--------------+---------------+------------+----------+----------------+

+--------------+---------------+------------+----------+----------------+

| PS_57 | test_compress | 100000 | 25 | |

+--------------+---------------+------------+----------+----------------+

显然，MariaDB中存在与案件相关的bug:

https://jira.mariadb.org/browse/MDEV-26400

https://jira.mariadb.org/browse/MDEV-11757

MySQL中也有类似的情况:

https://bugs.mysql.com/bug.php?id=56628

在MySQL 8中，情况如下:

CentOS7-S2 PS_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。表table_name='test_compress';+--------------+---------------+------------+----------+----------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | TOTAL_MB | CREATE_OPTIONS  | +--------------+---------------+------------+----------+----------------+ | PS_8 | test_compress | 31000 | 15  | | +--------------+---------------+------------+----------+----------------+

CentOS7-S2 PS_8 > 选择 table_schema ， table_name ， table_rows ，轮（ data_length / 1024 / 1024 ）+轮（ index_length / 1024 / 1024 ）+轮（ data_free / 1024 / 1024 ） TOTAL_MB ， create_options 从 information_schema .tables 在哪里 table_name＝“test_compress” ；

+--------------+---------------+------------+----------+----------------+

+--------------+---------------+------------+----------+----------------+

| PS_8 | test_compress | 31000 | 15 | |

+--------------+---------------+------------+----------+----------------+

让我们执行ALTER来压缩表:

CentOS7-S2 PS_8>修改表test_compress ROW_FORMAT=COMPRESSED,KEY_BLOCK_SIZE=4,ALGORITHM=INPLACE,LOCK=NONE查询OK, 0行受影响(4.54秒)记录:0重复:0警告:0

CentOS7-S2 PS_8 > 改变表格 test_compress ROW_FORMAT＝压缩， KEY_BLOCK_SIZE＝4 ，算法＝原地，锁＝没有一个；

查询好吧， 0 行影响（ 4.54 证券交易委员会）

记录： 0 重复的： 0 警告： 0

让我们再检查一下:

CentOS7-S2 PS_8>分析表test_compress;+-----------------------+---------+----------+----------+ | 表| Op | Msg_type | Msg_text  | +-----------------------+---------+----------+----------+ | PS_8。test_compress |分析| |好状态  | +-----------------------+---------+----------+----------+ 1行集(0.07秒)

CentOS7-S2 PS_8 > 分析表格 test_compress ；

+-----------------------+---------+----------+----------+

| 表格 | 人事处 | Msg_type | Msg_text |

+-----------------------+---------+----------+----------+

| PS_8 .test_compress | 分析 | 状态 | 好吧 |

+-----------------------+---------+----------+----------+

1 行在集（ 0.07 证券交易委员会）

CentOS7-S2 PS_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。表table_name='test_compress';+--------------+---------------+------------+----------+----------------------------------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | TOTAL_MB | CREATE_OPTIONS  | +--------------+---------------+------------+----------+----------------------------------------+ | PS_8 | test_compress | 100000 | | row_format =压缩KEY_BLOCK_SIZE = 4  | +--------------+---------------+------------+----------+----------------------------------------+

+--------------+---------------+------------+----------+----------------------------------------+

+--------------+---------------+------------+----------+----------------------------------------+

| PS_8 | test_compress | 100000 | 19 | row_format＝压缩 KEY_BLOCK_SIZE＝4 |

+--------------+---------------+------------+----------+----------------------------------------+

CentOS7-S2 PS_8 test_compress \ G >显示创建表  *************************** 1。row ***************************表:test_compress Create表: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，主键(' 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秒)

CentOS7-S2 PS_8 > 显示创建表格 test_compress ＼ G

*************************** 1. 行 ***************************

表格： test_compress

创建表格：创建表格｀ test_compress ｀（

｀ id ｀长整型数字无符号不零，

｀ identification_id ｀ int 无符号默认的零，

｀时间戳｀ datetime 不零，

｀行动｀ varchar （ 50 ）不零，

｀结果｀ varchar （ 50 ）不零，

主要的关键（｀ id ｀），

关键｀ INDEX_test_compress_result ｀（｀结果｀），

关键｀ INDEX_test_compress_timestamp ｀（｀时间戳｀）

）引擎＝InnoDB 默认的字符集＝latin1 ROW_FORMAT＝压缩 KEY_BLOCK_SIZE＝4

1 行在集（ 0.01 证券交易委员会）

到目前为止，一切都与MySQL 5.7相同:KEY_BLOCK_SIZE保留在整个表的定义中，而不是索引的定义中。

幸运的是，通过运行这个命令，我们有效地扭转了一切:

修改表test_compress ROW_FORMAT=DEFAULT, KEY_BLOCK_SIZE=0,ALGORITHM=INPLACE,LOCK=NONE;查询OK, 0行受影响(2.56秒)记录:0重复:0警告:0

CentOS7-S2 PS_8 > 改变表格 test_compress ROW_FORMAT＝默认的， KEY_BLOCK_SIZE＝0 ，算法＝原地，锁＝没有一个；

查询好吧， 0 行影响（ 2.56 证券交易委员会）

记录： 0 重复的： 0 警告： 0

CentOS7-S2 PS_8 test_compress \ G >显示创建表  *************************** 1。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)

CentOS7-S2 PS_8 > 显示创建表格 test_compress ＼ G

*************************** 1. 行 ***************************

表格： test_compress

创建表格：创建表格｀ test_compress ｀（

｀ id ｀长整型数字无符号不零，

｀ identification_id ｀ int 无符号默认的零，

｀时间戳｀ datetime 不零，

｀行动｀ varchar （ 50 ）不零，

｀结果｀ varchar （ 50 ）不零，

主要的关键（｀ id ｀），

关键｀ INDEX_test_compress_result ｀（｀结果｀），

关键｀ INDEX_test_compress_timestamp ｀（｀时间戳｀）

）引擎＝InnoDB 默认的字符集＝latin1

1 行在集（ 0．00 证券交易委员会）

CentOS7-S2 PS_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。表table_name='test_compress';+--------------+---------------+------------+----------+----------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | TOTAL_MB | CREATE_OPTIONS  | +--------------+---------------+------------+----------+----------------+ | PS_8 | test_compress | 100000 | 25  | | +--------------+---------------+------------+----------+----------------+

+--------------+---------------+------------+----------+----------------+

+--------------+---------------+------------+----------+----------------+

| PS_8 | test_compress | 100000 | 25 | |

+--------------+---------------+------------+----------+----------------+

与往常一样，我们建议在生产环境中运行之前在开发环境中进行初步测试。

结论

在MySQL 5.7中，完全恢复(至少在表及其索引的定义中)的唯一方法是重新生成主键及其所有索引。这听起来像一个终端解决方案，但如果您使用mysqldump(我们总是建议使用rayben雷竞技出于这些目的，它更快更有效)，这是一个需要考虑的问题，因为在其定义中，它保留了那些错误的定义。幸运的是，这在MySQL 8中得到了修复。

很有趣，不是吗?

雷竞技下载官网
平台

雷竞技下载官网
raybet雷竞技竞猜在线官网监测和
管理

软件
下载

产品
文档

资源中心

金融
服务

雷竞技下载官网Percona博客

雷竞技下载官网Percona社区中心

雷竞技下载官网Percona事件中心

关于Perc雷竞技下载官网ona

雷竞技下载官网Percona在新闻中

我们的客户

我们的合作伙伴

职业生涯

联系我们

结论

相关的

相关博客文章

推荐的文章

开源数据库的终极指南

MongoDB中的压缩方法:Snappy vs. Zstd

修复分区表中错位的行

最受欢迎文章

使用Percona Advisors确保数据库安全雷竞技下载官网

雷竞技下载官网Percona XtraBackup现在支持IAM实例配置文件

雷竞技下载官网Percona XtraBackup和MySQL 5.7等待刷新表状态查询

雷竞技下载官网平台

雷竞技下载官网raybet雷竞技竞猜在线官网监测和管理

软件下载

产品文档

资源中心

金融服务

雷竞技下载官网Percona博客

雷竞技下载官网Percona社区中心

雷竞技下载官网Percona事件中心

关于Perc雷竞技下载官网ona

雷竞技下载官网Percona在新闻中

我们的客户

我们的合作伙伴

职业生涯

联系我们

MySQL:压缩，索引和两个吸烟桶

结论

相关的

分享这篇文章!

想要每周更新最新的博客文章?

相关博客文章

推荐的文章

开源数据库的终极指南

MongoDB中的压缩方法:Snappy vs. Zstd

修复分区表中错位的行

最受欢迎文章

使用Percona Advisors确保数据库安全雷竞技下载官网

雷竞技下载官网Percona XtraBackup现在支持IAM实例配置文件

雷竞技下载官网Percona XtraBackup和MySQL 5.7等待刷新表状态查询

雷竞技下载官网
平台

雷竞技下载官网
raybet雷竞技竞猜在线官网监测和
管理

软件
下载

产品
文档

金融
服务