innodb中怎么啟用表壓縮功能

本篇文章為大家展示了innodb中怎么啟用表壓縮功能，內(nèi)容簡(jiǎn)明扼要并且容易理解，絕對(duì)能使你眼前一亮，通過這篇文章的詳細(xì)介紹希望你能有所收獲。

1，innodb_file_per_table默認(rèn)關(guān)閉
[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，默認(rèn)表的行格式為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(注：執(zhí)行結(jié)果顯示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系統(tǒng)變量，方可啟用表的行壓縮功能（即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,仍舊報(bào)錯(cuò)
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，查看執(zhí)行結(jié)果warnings的具體信息
mysql> show warnings;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. |
+---------+------+--------------------------------------+
1 row in set (0.00 sec)




7，從官方手冊(cè)可知，啟用行格式compressed，須滿足幾個(gè)條件，一則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，創(chuàng)建行格式compressed的表成功
mysql> create table t_com(a int) row_format=compressed;
Query OK, 0 rows affected (0.05 sec)

上述內(nèi)容就是innodb中怎么啟用表壓縮功能，你們學(xué)到知識(shí)或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識(shí)儲(chǔ)備，歡迎關(guān)注億速云行業(yè)資訊頻道。