起因
服务器磁盘空间不足，且数据库处于冷表状态，即及其少量的数据变动，和较多的查询过程。因此考虑压缩表来释放空间。其中，MySQL版本为5.6，还是比较常用的老版本了。
简单介绍下MyISAM表压缩
因为MyISAM表压缩不符合要求，所以以下仅做简单介绍，并没有进行实际操作，无法给出更具体的说明，仅供参考~
应用场景： 类似数据仓库
缺点： 表压缩后只有只读权限；不支持分区表
使用工具： myisampack、myisamchk
压缩率： 40%~70%
#进行压缩时，需确保表是在非使用状态下进行，避免数据丢失等风险
#打开指定库对应的目录下进行操作
shell> ls -l station.*
-rw-rw-r-- 1 jones my 994128 Apr 17 19:00 station.MYD
-rw-rw-r-- 1 jones my 53248 Apr 17 19:00 station.MYI
-rw-rw-r-- 1 jones my 5767 Apr 17 19:00 station.frm
shell> myisamchk -dvv station #显示表详细信息
MyISAM file: station
Isam-version: 2
Creation time: 1996-03-13 10:08:58
Recover time: 1997-02-02 3:06:43
Data records: 1192 Deleted blocks: 0
Datafile parts: 1192 Deleted data: 0
Datafile pointer (bytes): 2 Keyfile pointer (bytes): 2
Max datafile length: 54657023 Max keyfile length: 33554431
Recordlength: 834
Record format: Fixed length
table description:
Key Start Len Index Type Root Blocksize Rec/key
1 2 4 unique unsigned long 1024 1024 1
2 32 30 multip. text 10240 1024 1
............
shell> myisampack station.MYI #开始压缩表
Compressing station.MYI: (1192 records)
- Calculating statistics
normal: 20 empty-space: 16 empty-zero: 12 empty-fill: 11
pre-space: 0 end-space: 12 table-lookups: 5 zero: 7
Original trees: 57 After join: 17
- Compressing file
87.14%
Remember to run myisamchk -rq on compressed tables
shell> myisamchk -rq station #重建表索引
- check record delete-chain
- recovering (with sort) MyISAM-table 'station'
Data records: 1192
- Fixing index 1
- Fixing index 2
shell> mysqladmin -uroot flush-tables #刷新表
shell> ls -l station.*
-rw-rw-r-- 1 jones my 127874 Apr 17 19:00 station.MYD
-rw-rw-r-- 1 jones my 55296 Apr 17 19:04 station.MYI
-rw-rw-r-- 1 jones my 5767 Apr 17 19:00 station.frm
shell> myisamchk -dvv station
MyISAM file: station
Isam-version: 2
Creation time: 1996-03-13 10:08:58
Recover time: 1997-04-17 19:04:26
Data records: 1192 Deleted blocks: 0
Datafile parts: 1192 Deleted data: 0
Datafile pointer (bytes): 3 Keyfile pointer (bytes): 1
Max datafile length: 16777215 Max keyfile length: 131071
Recordlength: 834
Record format: Compressed
table description:
Key Start Len Index Type Root Blocksize Rec/key
1 2 4 unique unsigned long 10240 1024 1
2 32 30 multip. text 54272 1024 1
..........
InnoDB表压缩
InnoDB表，压缩后仍然可以进行增删改查操作，即操作同未压缩表一样。当然，还是存在一些缺点的，下面我们就来看一下InnoDB表压缩吧~
应用场景
- 内存较大，硬盘是SSD设备：压缩表可以实现较小的数据库大小，减少的I/O和改进的吞吐量。
- 服务器空间不足，数据必须存在，但很少操作：使用压缩表时将占用更多的CPU、内存和操作时间。
重点： InnoDB表压缩对字符串类型更有效（BLOB， VARCHAR，TEXT）；如果是数字类型或二进制类型的，就不用压缩啦（当然我木有实验过哈，有时间大家可以测试下）
前提
- 限制
- InnoDB表压缩仅支持存储在自己单独的表空间中的表和索引，因此不支持系统表压缩；且5.1之前的版本无法处理压缩表，所以需要考虑文件格式的兼容性问题。
- 表进行压缩或者解压时，是需要时间的，而时间取决于表数据类型和大小。一个简单的参考时间：压缩前149G的ibd文件，压缩时间为597m16.410s（约10个小时，天哪），压缩后大小变成62G，压缩比率大约为41.61%（还是很可观的）。
- 表在压缩过程中，磁盘空间是会变大的，大概是原表的一倍多一点，所以如果是空间相当不足的情况下，为避免压缩失败，建议先从小表开始压缩。
- 参数配置
#根据限制要求，在进行压缩之前请检查并更正以下两个参数
#mysql无需重启，可动态修改，记得同步到 my.cnf 配置表中哟 ~
mysql> SET GLOBAL innodb_file_per_table=1; #开启每表一个表空间文件
mysql> SET GLOBAL innodb_file_format=Barracuda; #支持压缩的文件格式
- 压缩命令特征
- KEY_BLOCK_SIZE用于指定页面大小，默认为 innodb_page_size 值的一半；
- 如果指定ROW_FORMAT=COMPRESSED，则可以忽略KEY_BLOCK_SIZE，采用默认值；
- 如果指定KEY_BLOCK_SIZE，则可以省略 ROW_FORMAT=COMPRESSED，压缩会自动启用。
- InnoDB表默认页面大小为16KB，因此如果KEY_BLOCK_SIZE=16或者大于16，对压缩来说，通常是没有多大效果的。
- 查看压缩表效果
#INNODB_CMP表可以简单查看压缩表的效果，compress_ops表示压缩总量，compress_ops_ok表示压缩成功总量
#查看compress_ops_ok /compress_ops 比率，即可查看压缩效果，如果压缩比率较小，则表示压缩没有达到预期效果，一般失败在1%~2%是可以接受的。
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP;
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 | 0 |
| 4096 | 0 | 0 | 0 | 0 | 0 |
| 8192 | 15639582 | 15637354 | 2730 | 120421 | 10 |
| 16384 | 0 | 0 | 0 | 0 | 0 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
5 rows in set (0.05 sec)
#！！！INNODB_CMP_PER_INDEX应在测试开发环境中进行，因其统计信息所付出的代价较为昂贵
#INNODB_CMP_PER_INDEX表统计需开启innodb_cmp_per_index_enabled功能
#在下没有采用该统计方式，所以无法给出该表统计到底需要耗费多少服务器性能哈，欢迎尝试后告知 ~
mysql> SHOW VARIABLES LIKE 'innodb_cmp_per_index_enabled';
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX;
#其它可查看压缩效果的系统表
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM;
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMP_RESET;
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CMPMEM_RESET;
压缩与解压
InnoDB表压缩可以在创建时指定或者对已有表进行更正：
#进行压缩、解压时，需确保表是在非使用状态下进行，避免数据丢失等风险
#压缩
CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
#解压，即更正为原表的格式，如COMPACT
ALTER TABLE t1 ROW_FORMAT=COMPACT;
根据MySQL官方手册原文，建议采用8KB作为KEY_BLOCK_SIZE的赋值。
Typically, you set the compressed page size to 8K or 4K bytes. Given that the maximum row size for an InnoDB table is around 8K, KEY_BLOCK_SIZE=8 is usually a safe choice.
影响参数
针对OLTP（On-Line Transaction Processing联机事务处理过程）工作负载的压缩表进行优化操作，以下五个参数将会影响其写密集型操作的性能和可伸缩性。
mysql> SHOW VARIABLES LIKE '%innodb%compress%';
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_log_compressed_pages | ON |
+------------------------------------------+-------+
4 rows in set (0.03 sec)
mysql> SHOW VARIABLES LIKE '%innodb_buffer_pool_size%';
+-------------------------+-------------+
| Variable_name | Value |
+-------------------------+-------------+
| innodb_buffer_pool_size | 12582912000 |
+-------------------------+-------------+
1 row in set (0.09 sec)
-
名词解析
- innodb_compression_failure_threshold_pct：可接受的压缩失败比率
- innodb_compression_level：压缩水平
- innodb_compression_pad_pct_max：指定可以在每个压缩页面内保留为可用空间的最大百分比，仅在压缩失败时，会用到可用空间，因为拆分压缩页也是很昂贵的操作。
- innodb_log_compressed_pages：指定是否将重新压缩的页面图像写入redo日志，开启则在对压缩数据进行更改时，可能会发生重新压缩。默认是开启的。这是为了避免 zlib 在恢复过程中使用不同版本的压缩算法时可能发生的损坏。如果可以确定 zlib 版本不会变动，则可禁用该参数，以减少工作负载。
- innodb_buffer_pool_size：缓冲池大小调整，也对工作负载有影响，一般在专用数据库的环境下该值设置为物理内存的80%。
压缩过程的影响
因为没有特地记录压缩过程的CPU变化，内存变化，磁盘空间变化等，所以只能简单的说明下，在下比较懒，估计不会再进行测试了，所以仅供参考。
压缩过程中：
- 磁盘空间会变大，大概是压缩表原来大小的一倍，需要提前评估预留时间；
- CPU负载明显变高，不过在可控范围内（服务器的CPU内核：8核）
- 内存变化不大，但也有波动，在可空范围之内（服务器的物理内存：16G）
以上经过了MySQL官方手册指导和实操，遗憾没有进行更科学的测试阶段，但是也够用啦~
记录一下过程，方便自己，方便大家。
扫一扫
2888
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
