innodb中怎么启用表压缩功能

本篇文章为大家展示了innodb中怎么启用表压缩功能，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

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)

上述内容就是innodb中怎么启用表压缩功能，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。