當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出現的問題。

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

當雜湊表遇上鍊表會發生什麼呢？
2021-07-22
MySQL 5.6的表壓縮
2020-01-10
MySql
MYSQL壓縮表測試
2020-12-11
MySql
當雜湊表遇上鍊表會擦除什麼火花？
2021-07-23
oracle壓縮表(二)
2019-07-19
Oracle
oracle壓縮表(一)
2019-05-21
Oracle
oracle 的表壓縮
2021-05-12
Oracle
MySQL實現MYISAM表批次壓縮的方法
2021-09-09
MySql
Sqlserver表和索引壓縮
2022-10-24
SQLServer索引
壓縮錶轉非壓縮表（線上重定義）
2023-12-25
MySQL什麼時候會使用內部臨時表?
2019-05-24
MySql
MySQL修改表結構到底會不會鎖表？
2021-12-21
MySql
--查詢班級表、課程表、學生選課表、學生表、成績表、15（3+2）計算
2021-09-09
mysql收縮共享表空間
2018-04-20
MySql
oracle 普通表-分割槽表改造流程
2023-12-29
Oracle
隨筆：MySQL 普通3表join順序
2020-01-09
MySql
必看，經典sql面試題2（學生表_課程表_成績表_教師表）
2019-08-09
SQL面試題
必看，經典sql面試題1（學生表_課程表_成績表_教師表）
2019-08-09
SQL面試題
簡單瞭解一下壓縮表
2019-03-31
Echarts自適應：當視窗大小發生變化時，重新渲染圖表
2024-09-03
Echarts
SQLSERVER 臨時表和表變數到底有什麼區別？
2023-02-16
SQLServer變數
MySQL千萬級大表線上變更表結構
2021-04-06
MySql
MySQL鎖（二）表鎖：為什麼給小表加欄位會導致整個庫掛掉？
2020-12-16
MySql
談談什麼是MySQL的表空間？
2020-11-23
MySql
指令碼：線上重定義，從普通表到分割槽表，redefinition_table.sh
2018-03-09
指令碼
精益生產成功的表現是什麼？
2023-05-18
fastreport .net列印普通報表
2024-04-28
AST
MySQL索引失效行鎖變表鎖
2020-10-03
MySql索引
【RMAN】當表空間處於備份狀態時發生了什麼
2021-02-01
mysql怎麼關聯表？
2021-09-11
MySql
oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等
2020-08-19
Oracle型別
MySQL 全域性表和表鎖
2024-10-15
MySql
mysql什麼時候會發生file sort
2022-03-09
MySql
線上重定義與普通表改為分割槽表
2018-10-14
怎麼把資料夾壓縮成壓縮包發給微信好友
2021-11-06
mysql共享表空間擴容,收縮,遷移
2018-03-30
MySql
mysql 從一個表中查詢，插入到另一個表中
2019-01-28
MySql
mysql從一張表中取出資料插入到另一張表
2021-01-01
MySql