The ability to compress user data is an important new capability of the InnoDB Plugin. Compressed tables reduce the size of the database on disk, resulting in fewer reads and writes needed to access the user data. For many InnoDB workloads and many typical user tables (especially with read-intensive applications where sufficient memory is available to keep frequently-used data in memory), compression not only significantly reduces the storage required for the database, but also improves throughput by reducing the I/O workload, at a modest cost in processing overhead. The storage cost savings can be important, but the reduction in I/O costs can be even more valuable. 从文档中可以看到compress最大的优势不仅在于空间的压缩上，同时可以降低i/o的请求，特别在read-intensive的场景下[注意这里是在内存能够容纳所读取的数据]非常有效。 测试创建可压缩的表： create table compress_test( id int,name varchar(255) ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;可以看到比我们平常的建表的参数中多加了ROW_FORMAT=COMPRESSED和 KEY_BLOCK_SIZE=8两个参数，需要注意的是：Available only with innodb_file_format=Barracuda , Compresses the table and indexes using zlib to default compressed page size of 8K bytes;只有在文件格式在Barracuda时候才能使用compress特性，那么就需要配置innodb_file_per_table和innodb_file_format两个参数； 默认情况下，mysql会将每个页压缩为8k，但也可以压缩到1KB, 2KB, 4KB, 8KB, 16KB，普通innodb的page大小为16k，所以设置为key_block_size=16k不会收到很大的压缩效果，但是对于一些blob，txt大字段来说通常会很有用； MySQL的压缩不仅对于表的数据有效，对索引页(聚簇和非聚簇)同样压缩到key_block_size大小，但是我们却不可以在创建索引的时候指定该索引的压缩，而只能通过表的压缩来实现索引的压缩； mysql> show variables like '%innodb_file_per_table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (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)原文：http://hidba.org/?p=310 转载请保留固定链接： https://linuxeye.com/database/1946.html |