如何启用innodb表的压缩功能

前言
     innodb存储引擎有一个参数或系统变量innodb_file_per_table，用于控制innodb表及索引是存储在1个文件还是分别各自存储在2个文件中
 


技术根本在于实战，百炼成钢，大家一起看下述的测试：


1，innodb_file_per_table默认关闭
[root@mygirl ~]# mysql -u -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 69
Server version: 5.5.58-log MySQL Community Server (GPL)


Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)




2，默认表的行格式为compact
mysql> select table_catalog,table_schema,table_name,engine,row_format from information_schema.tables where table_name='t_com';
+---------------+--------------+------------+--------+------------+
| table_catalog | table_schema | table_name | engine | row_format |
+---------------+--------------+------------+--------+------------+
| def           | zxy          | t_com      | InnoDB | Compact    |
+---------------+--------------+------------+--------+------------+
1 row in set (0.00 sec)




3,表的行格式不能变更为compressed(注：执行结果显示warnings不为0)
mysql> alter table t_com row_format=compressed;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 2


mysql> select table_catalog,table_schema,table_name,engine,row_format from information_schema.tables where table_name='t_com';
+---------------+--------------+------------+--------+------------+
| table_catalog | table_schema | table_name | engine | row_format |
+---------------+--------------+------------+--------+------------+
| def           | zxy          | t_com      | InnoDB | Compact    |
+---------------+--------------+------------+--------+------------+
1 row in set (0.00 sec)




mysql> alter table t_com row_format=compact;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0


4，开启innodb_file_per_table系统变量，方可启用表的行压缩功能（即row_format=compressed）
mysql> set global innodb_file_per_table=on;
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)


5,仍旧报错
mysql> alter table t_com row_format=compressed;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 2


mysql> select table_catalog,table_schema,table_name,engine,row_format from information_schema.tables where table_name='t_com';
+---------------+--------------+------------+--------+------------+
| table_catalog | table_schema | table_name | engine | row_format |
+---------------+--------------+------------+--------+------------+
| def           | zxy          | t_com      | InnoDB | Compact    |
+---------------+--------------+------------+--------+------------+
1 row in set (0.00 sec)






mysql> drop table t_com;
Query OK, 0 rows affected (0.01 sec)


mysql> create table t_com(a int) row_format=compressed;
Query OK, 0 rows affected, 2 warnings (0.04 sec)


mysql> select table_catalog,table_schema,table_name,engine,row_format from information_schema.tables where table_name='t_com';
+---------------+--------------+------------+--------+------------+
| table_catalog | table_schema | table_name | engine | row_format |
+---------------+--------------+------------+--------+------------+
| def           | zxy          | t_com      | InnoDB | Compact    |
+---------------+--------------+------------+--------+------------+
1 row in set (0.00 sec)


6，查看执行结果warnings的具体信息
mysql> show warnings;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
+---------+------+--------------------------------------+
1 row in set (0.00 sec)




7，从官方手册可知，启用行格式compressed，须满足几个条件，一则5.5以上，二则使用barracuda文件格式，三则使用行格式compressed,四则innodb_file_per_table,


The table compression feature requires using MySQL 5.5 or higher, or the InnoDB Plugin in MySQL 5.1 or earlier, and creating the table
 using the Barracuda file format and compressed row format, with the innodb_file_per_table setting enabaled.


mysql> show 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.00 sec)




mysql> set global innodb_file_format='barracuda';
Query OK, 0 rows affected (0.00 sec)


mysql> set global innodb_file_format_max='Barracuda';
Query OK, 0 rows affected (0.00 sec)




mysql> show 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)


8，创建行格式compressed的表成功
mysql> create table t_com(a int) row_format=compressed;
Query OK, 0 rows affected (0.05 sec)




   mysql的innodb表的一项新功能的配置，是由多方面因素构成，一定要充分学习官方手册，进行全方位测试，切勿直接上线，造成不可弥补的后果

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-2148784/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-2148784/