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