如何设置mysql innodb 表的压缩

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/wll_1017/article/details/54860881

一、 如何设置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 ,此时建立的压缩表会忽略压缩参数

三、实验

3.1 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,是不能够压缩的,压缩选项会被忽略掉

3.2  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)

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页