前言

本文章做了把mysql表從壓縮表過渡到普通表的實驗程序，看看壓縮表變成普通表會發生什么？本文針對mysql5.7和mysql8分別進行了實驗，

1、什么是表壓縮

在介紹壓縮表變成普通表前，首先給大家普及下，什么是表壓縮，

表壓縮，意思是使表中的資料以壓縮格式存盤，壓縮能夠顯著提高處理速度和壓縮磁盤，壓縮意味著在硬碟和記憶體之間傳輸的資料更小且占用相對少的記憶體及硬碟，對于輔助索引，這種壓縮帶來更加明顯的好處，因為索引資料也被壓縮了，

表壓縮是有很大好處的，能減少磁盤的I/O，還能提高系統吞吐量，節約空間，壓縮率越大，占用的磁盤空間越小，檔案傳輸時間提升，降低資料的存盤和網路傳輸成本，

2、如何表壓縮( mysql的版本需要大于5.5 )

1、首先設定my.inf引數

#打開組態檔
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

2、對表壓縮

mysql> alter table t1 ROW_FORMAT=COMPRESSED;

3、壓縮表轉換為普通表

mysql> alter table t1 ROW_FORMAT=DEFAULT;

針對mysql5.7開始實驗

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 |
+--------------+---------------+------------+----------+----------------------------------------+

該表已被壓縮，讓我們檢查其結構，

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

解壓縮成功，讓我們檢查看看，

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 命令（如果可以這樣稱呼它）在表定義索引方面反轉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;

現在，它具有正確的定義，沒有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里有解釋這個bug: https://bugs.mysql.com/bug.php?id=56628

針對mysql8實驗

在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出現的問題，

更多內容請關注微信公眾號【編程達人】，分享優質好文章，編程黑科技，助你成為編程達人！

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/499115.html

標籤：MySQL

上一篇：分庫分表真的適合你的系統嗎？聊聊分庫分表和NewSQL如何選擇

下一篇：MVCC - Read View的可見性判斷理解

當mysql表從壓縮表變成普通表會發生什么

前言