Mysql利用Innodb的表压缩节省空间

如今的数据库容量增长一天一个变化,随着不断变大的数据,很容易出现以下两个问题;

问题1:IO问题。DB由于数据量过大,内存缓冲池不能完全cache数据,IO瓶颈制约DB整体性能;

问题2:存储空间不足。由于数据量过大需要不断增加硬件;

当用户的数据量比较大时,通常需要对数据进行压缩,以减少磁盘占用;然而利用Mysql的InnoDB的表压缩功能,对应用层透明并且节省了硬件成本也能有效解决该问题。

 5.6.7 版本后中关于压缩表部分,引进了大多数Facebook的改进;

本次环境在Centos6.5_x64系统下;Mysql使用5.7.9版本; 

1.可配置的zlib压缩级别
 
  1. # innodb_compression_level 
用于动态调整zlib的压缩级别,从1-9,默认为6,越小压缩效果越差,但压缩速度越快;值越大,压缩效果越好,可能会减小压缩失败及page split, 但速度越慢,有更多的CPU开销。
 
  1. # innodb_log_compressed_pages 
控制压缩page是否被记录到redo中,这会减少redo产生的数据量,可能会提升throughput
3.增加adaptive padding功能,通过在page中留下空白来防止减少page分裂,Facebook的实现是在启动mysqld后,对压缩表中的压缩失败的page进行采样,以计算出合适的留白数。
增加了两个参数:
 
  1. innodb_compression_failure_threshold_pct  #当压缩失败rate大于这个值时,会增加padding来减小失败率,为0表示禁止该padding; 
  2. innodb_compression_pad_pct_max  #一个page中最大允许留白的百分比 
4.新的关于压缩表的i_s表:
 
  1. innodb_cmp_per_index 
  2. innodb_cmp_per_index_reset 
由于这些表里记载了每个索引的压缩信息,因此会产生一些额外的开销,通过选项 innodb_cmp_per_index_enabled 来控制开关;默认在5.7版本中是OFF的;
 
首先,我们先说说怎么使用innodb的压缩.
 
  1. 第一,mysql的版本需要大于5.5 
  2. 第二,设置innodb_file_format=Barracuda 
  3. 第三,create table或者alter talble 增加 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;(默认值16) 
 
 
本次使用正式库上的一部分数据作为测试表;
 
  1. mysql> select count(*) from SALE_DTL; 
  2. +----------+ 
  3. | count(*) | 
  4. +----------+ 
  5. |   272417 | 
  6. +----------+ 
  7. 1 row in set (0.62 sec) 
 
压缩前:
  1. mysql> show table status like 'SALE_DTL'\G 
  2. *************************** 1. row *************************** 
  3.            Name: SALE_DTL 
  4.          Engine: InnoDB 
  5.         Version: 10 
  6.      Row_format: Dynamic 
  7.            Rows: 269834 
  8.  Avg_row_length: 99 
  9.     Data_length: 26804224 
  10. Max_data_length: 0 
  11.    Index_length: 38387712 
  12.       Data_free: 0 
  13.  Auto_increment: NULL 
  14.     Create_time: 2016-12-17 09:04:00 
  15.     Update_time: NULL 
  16.      Check_time: NULL 
  17.       Collation: utf8_general_ci 
  18.        Checksum: NULL 
  19.  Create_options:  
  20.         Comment: 销售数据表 
  21. 1 row in set (0.00 sec) 
 
修改表结构,添加分页大小,启动压缩;
 
  1. alter table SALE_DTL row_format=compressed key_block_size=8
压缩后的变化:
 
  1. mysql> show table status like 'SALE_DTL'\G 
  2. *************************** 1. row *************************** 
  3.            Name: SALE_DTL 
  4.          Engine: InnoDB 
  5.         Version: 10 
  6.      Row_format: Compressed 
  7.            Rows: 270332 
  8.  Avg_row_length: 53 
  9.     Data_length: 14450688 
  10. Max_data_length: 0 
  11.    Index_length: 11296768 
  12.       Data_free: 0 
  13.  Auto_increment: NULL 
  14.     Create_time: 2016-12-16 14:30:45 
  15.     Update_time: NULL 
  16.      Check_time: NULL 
  17.       Collation: utf8_general_ci 
  18.        Checksum: NULL 
  19.  Create_options: row_format=COMPRESSED KEY_BLOCK_SIZE=8 
  20.         Comment: 销售数据表 
  21. 1 row in set (0.00 sec) 
 
这里有Rows表示行数,但并非准确的行数这里只是一个估算值;
 
官方的意思:真实的值要count(*)统计;
参考:https://dev.mysql.com/doc/refman/5.7/en/show-table-status.html
 Rows:
The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.
 
查看表数据和索引所占用多少空间:
 
  1. 切换到库: 
  2. use information_schema 
  3. 查询命令: 
  4. select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB,ROW_FORMAT, TABLE_ROWS from tables where table_schema='test' and table_name = 'SALE_DTL'
 
 
 
通过上面的测试,发现一般压缩比例可以达到30%-40%以上;这样能有效提升DB的性能,同时节约硬件成本节省空间;由于目前了解到Innodb压缩使用的人不多,也只能在测试环境中使用;

 更多可参考:

https://yq.aliyun.com/articles/64891

http://djt.qq.com/article/view/1189