1.什么是表压缩简单讲表压缩就是通过一些手段降低数据表磁盘空间的占用通常有以下几种方式优化表OPTIMIZE TABLE命令重新组织表中的数据清理碎片以减少空间使用引擎层面解决设置表的存储格式例如在创建innodb表时设置ROW_FORMATCOMPRESSED能够使用比默认的16K更小的页。方式1请移步https://blog.csdn.net/weixin_43816711/article/details/135648862?spm1001.2014.3001.5501方式2目前 myisam、innodb、tokudb、MyRocks 等引擎都支持表的压缩。这里只对 InnoDB 做详细说明。innodb 在 MySQL 5.5 的时候就支持了压缩功能只是压缩比比较低通常在 50%左右。而 tokuDB 能达到 80%左右MyRocks 的压缩比能达到 70%左右。2. 为什么要表压缩存储成本压缩意味着在硬盘和内存之间传输的数据更小且占用相对少的内存及硬盘对于辅助索引这种压缩带来更加明显的好处因为索引数据也被压缩了。压缩对于硬盘是SSD的存储设备尤为重要因为它们相对普通的HDD硬盘比较贵且容量有限。使用场景采用压缩表一般都用在数据量太大磁盘空间不足IO负载大但服务器CPU有比较多的余量的场景。CPU和内存的速度远远大于磁盘对于数据库服务器磁盘IO可能会成为紧要资源或者瓶颈。数据压缩能够让数据库变得更小从而减少磁盘的I/O,还能提高系统吞吐量以很小的成本牺牲一部分CPU资源。对于读比重较多的应用压缩是特别有用。2.MySQL表压缩方法使用 innodb 压缩的前提条件是innodb_file_per_table这个参数要启用innodb_file_format这个参数设置成Barracuda。可以使用ROW_FORMATCOMPRESSED来create或者alter表来开启 innodb 的压缩功能如果没有指定KEY_BLOCK_SIZE的大小默认KEY_BLOCK_SIZE为innodb_page_size大小的一半也可以通过指定KEY_BLOCK_SIZEn参数来开启 innodb 的压缩功能n 可以为 1、2、4、8、16单位是 K。n 的值越小压缩比越高消耗的 CPU 资源也越多。注意 32K 或者 64K 的页不支持压缩。启用压缩后索引数据也同样会被压缩。你也可以通过调整 innodb_compression_level 来设置压缩的级别级别从 1~9默认是 6。级别越低意味着压缩比越高同时也意味着需要更多的 CPU 资源。注意压缩比和存储的数据组成有很大的关系并不是所有的数据都能达到上面所说的压缩比。如果大部分都是字符串并且重复的数据比较多压缩比会很好。2.MySQL表压缩原理数据库中的表是由一行行记录rows所组成每行记录被存储在一个页中在 MySQL 中一个页的大小默认为 16K一个个页又组成了每张表的表空间。如果一个页中存放的记录数越多数据库的性能越高。这是因为数据库表空间中的页是存放在磁盘上MySQL 数据库先要将磁盘中的页读取到内存缓冲池然后以页为单位来读取和管理记录。那么一个页中存放的记录越多内存中能存放的记录数也就越多那么存取效率也就越高。若想将一个页中存放的记录数变多可以启用压缩功能。此外启用压缩后存储空间占用也变小了同样单位的存储能存放的数据也变多了。若要启用压缩技术数据库可以根据记录、页、表空间进行压缩不过在实际工程中我们普遍使用页压缩技术这是为什么呢压缩每条记录每次读写数据都要压缩和解压对CPU的计算过于依赖会导致性能明显下降另外每条数据的大小一般都不会太大对于每条数据都进行压缩的话压缩效率也不是很好。压缩表空间对表空间的压缩其实压缩效率还是不错的但是它要求表空间文件要保持静态这对关系型数据库来讲又不现实。不过业务中的历史数据倒是可以考虑而基于页的压缩既能提升压缩效率又能在性能之间取得一种平衡。这里大家可能要担心了启用页压缩的话性能会有损失因为压缩需要额外的CPU计算。确实压缩会给CPU带来一定的消耗。但压缩并不意味着性能下降有可能还能提升性能呢因为大部分的数据库业务系统CPU的处理能力是有余力的也就是计算时过剩的。IO负载才是数据库的主要瓶颈。通过页压缩技术MySQL可以把16K的页压缩到8K或是4K。这样一来从磁盘读取或写入时就能将IO请求大小减半。从而提升数据库的整体性能。1、COMPRESS 页压缩COMPRESS 页压缩是 MySQL 5.7 版本之前提供的页压缩功能。只要在创建表时指定ROW_FORMATCOMPRESS并设置通过选项 KEY_BLOCK_SIZE 设置压缩的比例。虽然是通过选项 ROW_FORMAT 启用压缩功能但这并不是记录级压缩依然是根据页的维度进行压缩。比如以下示例我们将一张日志表ROW_FROMAT 设置为 COMPRESS表示启用 COMPRESS 页压缩功能KEY_BLOCK_SIZE 设置为 8表示将一个 16K 的页压缩为 8K。CREATETABLEsys_log(logIdBINARY(16)PRIMARYKEY,......)ROW_FORMATCOMPRESSED KEY_BLOCK_SIZE8COMPRESS 页压缩就是将一个页压缩到指定大小比如从16K压缩到8K但是如果无法压缩到8K则会产生两个8K的页。COMPRESS 页压缩适合用于一些对性能不敏感的业务表例如日志表、监控表、告警表等压缩比例通常能达到 50% 左右。虽然 COMPRESS 压缩可以有效减小存储空间但 COMPRESS 页压缩的实现对性能的开销是巨大的性能会有明显退化。主要原因是一个压缩页在内存缓冲池中存在压缩和解压两个页。为了 解决压缩性能下降的问题从MySQL 5.7 版本开始推出了 TPC 压缩功能。如何评估 KEY_BLOCK_SIZE 是否合适为了更深入地了解压缩表对性能的影响在 Information Schema 库中有对应的表可以用来评估内存的使用和压缩率等指标。INNODB_CMP 是收集的是某一类的 KEY_BLOCK_SIZE 压缩表的整体状况的信息汇总的是所有 KEY_BLOCK_SIZE 压缩表的统计。而 INNODB_CMP_PER_INDEX 表则是收集各个表和索引的压缩情况信息这些信息对于在某个时间评估某个表的压缩效率或者诊断性能问题很有帮助。INNODB_CMP_PER_INDEX 表的收集会导致系统性能受到影响必须 innodb_cmp_per_index_enabled 选项才会记录生产环境最好不要开启。我们可以通过观察 INNODB_CMP 表的压缩失败情况如果失败比较多则需要调大 KEY_BLOCK_SIZE。一般建议 KEY_BLOCK_SIZE 设置为 8。2、TPC压缩TPCTransparent Page Compression是 5.7 版本推出的一种新的页压缩功能其利用文件系统的空洞Punch Hole特性进行压缩。可以使用下面的命令创建 TPC 压缩表CREATETABLEsys_log logidBINARY(16)PRIMARYKEY,.....)COMPRESSIONZLIB|LZ4|NONE;要使用 TPC 压缩首先要确认当前的操作系统是否支持空洞特性。通常来说当前常见的 Linux 操作系统都已支持空洞特性。由于空洞是文件系统的一个特性利用空洞压缩只能压缩到文件系统的最小单位 4K且其页压缩是 4K 对齐的。比如一个 16K 的页压缩后为 7K则实际占用空间 8K压缩后为 3K则实际占用空间是 4K若压缩后是 13K则占用空间依然为 16K。空洞压缩的另一个好处是它对数据库性能的侵入几乎是无影响的小于 20%甚至可能还能有性能的提升。这是因为不同于 COMPRESS 页压缩TPC 压缩在内存中只有一个 16K 的解压缩后的页对于缓冲池没有额外的存储开销。另一方面所有页的读写操作都和非压缩页一样没有开销只有当这个页需要刷新到磁盘时才会触发页压缩功能一次。但由于一个 16K 的页被压缩为了 8K 或 4K其实写入性能会得到一定的提升。对一些对性能不敏感的业务表例如日志表、监控表、告警表等它们只对存储空间有要求因此可以使用 COMPRESS 页压缩功能。在一些较为核心的业务表上更推荐使用 TPC压缩。因为核心信息是一种非常重要的数据通常伴随高频、重要业务。比如上面提到的订单数据大部分的电商公司都会对历史订单数据去做单独存储。以确保近期订单数据可以秒查。那么针对这个业务场景我们可以将历史数据启用TPC压缩功能对近三个月或六个月的订单数据不启用压缩。需要特别注意的是 通过命令ALTER TABLE xxx COMPRESSION ZLIB可以启用 TPC页压缩功能但是这只对后续新增的数据会进行压缩对于原有的数据则不进行压缩。所以上述ALTER TABLE操作只是修改元数据瞬间就能完成。若想要对整个表进行压缩需要执行OPTIMIZE TABLE命令ALTER TABLE sys_log COMPRESSIONZLIBOPTIMIZE TABLE sys_log;【注意】表压缩操作可能会影响数据库性能因此建议在低峰时段进行这些操作。此外MySQL表压缩不会改变表中数据的内容只会减少其存储所需的磁盘空间。