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

閒道發表於2022-07-12
MySql

前言

本文章做了把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出現的問題。

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

相關文章