Shulou(Shulou.com)05/31 Report--

This article shows you how to enable table compression in innodb, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1According to the default, innocent database filekeeper perpendicular table is disabled.

[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. The default row format of the table is 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. The row format of the table cannot be changed to compressed (Note: the execution result shows that the warnings is not 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. Enable the innodb_file_per_table system variable to enable the row compression function of the table (i.e. 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, still report an error

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 (an 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. View the details of the warnings of the execution result

Mysql > show warnings

+-+

| | Level | Code | Message | |

+-+

| Warning | 1478 | InnoDB: assuming ROW_FORMAT=COMPACT. | |

+-+

1 row in set (0.00 sec)

7. According to the official manual, several conditions must be met to enable the line format compressed: one is more than 5.5.5.The second is to use the barracuda file format, the third is to use the line format compressed, and four are 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. The table with row format compressed was created successfully

Mysql > create table t_com (an int) row_format=compressed

Query OK, 0 rows affected (0.05 sec)

The above is how to enable table compression in innodb. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.