innodb表壓縮 · sql

[TOC] # 如何設置mysql innodb 表的壓縮設置innodb 表的壓縮第一，mysql的版本需要大于5.5 第二，設置innodb_file_format=barracuda 第三，create table或者alter talble 增加 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;(默認的key_block_size=16) 根據經驗，一般壓縮比例可以達到30%-40% 順序不能改變，先設置字符集給事為 innodb_file_format=barracuda，然后再建表或者修改表的compaesed # 步驟二 ---設置innodb字符集 set global innodb_file_format=Barracuda vi /etc/my.cnf 添加 innodb_file_format=Barracuda --修改表壓縮 alter table t row_format=COMPRESSED; 或者建表 create table t(id int,a varchar(10)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED 注意：在innodb_file_format=antelope的情況下，建立壓縮表（表結構中帶有row_format=compressed），然后在設置innodb_file_format=barracuda ，此時建立的壓縮表會忽略壓縮參數 # 實驗(先看再做) ## Antelope 字符集下建立壓縮innodb表 ~~~ mysql> show global variables like 'innodb_file_format%'; +--------------------------+----------+ | Variable_name | Value | +--------------------------+----------+ | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | +--------------------------+----------+ 3 rows in set (0.02 sec) ~~~ ~~~ mysql> create table t(id int,a varchar(10)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED ; Query OK, 0 rows affected, 2 warnings (0.24 sec) ~~~ ~~~ mysql> show warnings; +---------+------+-----------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------------------------+ | Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_format > Antelope. | | Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. | +---------+------+-----------------------------------------------------------------------+ 2 rows in set (0.00 sec) ~~~ 查看壓縮表的狀態 ~~~ mysql> show table status like 't'\G; *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Compact //顯示沒有壓縮 Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2017-02-03 17:48:56 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: row_format=COMPRESSED Comment: 1 row in set (0.01 sec) ~~~ 結論：我們也就得出字符集是innodb_file_format=antelope，是不能夠壓縮的，壓縮選項會被忽略掉 ## Barracuda 字符集下建立壓縮innodb表修改字符集： ~~~ set global innodb_file_format=Barracuda ~~~ ~~~ mysql> set global innodb_file_format=Barracuda ; Query OK, 0 rows affected (0.00 sec) ~~~ ~~~ mysql> show global variables like 'innodb_file_format%'; +--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | innodb_file_format | Barracuda | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | +--------------------------+-----------+ 3 rows in set (0.00 sec) ~~~ ~~~ mysql> drop table t; Query OK, 0 rows affected (0.16 sec) ~~~ ~~~ mysql> create table t(id int,a varchar(10)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED ; Query OK, 0 rows affected (0.27 sec) ~~~ ~~~ mysql> show table status like 't'\G; *************************** 1. row *************************** Name: t Engine: InnoDB Version: 10 Row_format: Compressed //正確壓縮 Rows: 0 Avg_row_length: 0 Data_length: 8192 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2017-02-03 17:53:10 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: row_format=COMPRESSED Comment: 1 row in set (0.00 sec) ~~~ ~~~ mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL, `a` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED 1 row in set (0.00 sec) ~~~ 如果是這種方式不能正確的查看表是否已經壓縮 # 注意 ~~~ mysql> show global variables like '%innodb_file_format%'; +--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | innodb_file_format | Barracuda | | innodb_file_format_check | ON | | innodb_file_format_max | Barracuda | +--------------------------+-----------+ 3 rows in set (0.00 sec) ~~~ ~~~ mysql> exit Bye [root@dg mysql]# service mysql restart Shutting down MySQL.. [確定] Starting MySQL.. [確定] ~~~ ~~~ mysql> show global variables like '%innodb_file_format%'; +--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | innodb_file_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Barracuda | +--------------------------+-----------+ 3 rows in set (0.00 sec) ~~~ 重啟后字符集又變了，所以要在參數文件中標明`innodb_file_format=Barracuda` ，重啟后才不會改變注意：我們可以通過命令來修改 innodb_file_format= Barracuda的字符集格式，但是往往我們會忽略掉，或者忘記了參數文件中曾經設置過字符集是 innodb_file_format=Antelope或者參數文件中曾經沒有設置過 innodb_file_format，重啟后參數還原成了innodb_file_format=Antelope，而表可能會讓我們的壓縮重新回到不壓縮的狀態，所以一定要記住在參數文件中設置字符集 ~~~ mysql> show global variables like '%innodb_strict_mode%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | innodb_strict_mode | OFF | +--------------------+-------+ 1 row in set (0.00 sec) ~~~ # 總結單個表轉換的SQL語句為： ~~~ ALTER TABLE $tableName ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8; ~~~ 壓縮最終還是通過消耗更多的cpu資源來換取減少IO消耗，最終帶來性能的提升，如果應用是IO密集型，而不是cpu密集型，那么可以利用剩余的cpu來提升應用性能。 innodb壓縮的優劣:https://zhuanlan.zhihu.com/p/24334129