做网站除了有服务器还需要什么,wordpress欢迎新会员,宣传产品的方式,vps远程桌面服务器租用目录 1.表压缩概述
2.创建压缩表
2.1 在FPT表空间中创建压缩表
2.2 在通用表空间中创建压缩表
2.3 压缩表的限制
3.优化InnoDB表的压缩
4.运行时监控InnoDB表压缩
5.InnoDB表的压缩工作原理
5.1 压缩算法
5.2 InnoDB数据存储和压缩
5.3 B树页面的压缩
5.4 压缩BLOB、…目录 1.表压缩概述
2.创建压缩表
2.1 在FPT表空间中创建压缩表
2.2 在通用表空间中创建压缩表
2.3 压缩表的限制
3.优化InnoDB表的压缩
4.运行时监控InnoDB表压缩
5.InnoDB表的压缩工作原理
5.1 压缩算法
5.2 InnoDB数据存储和压缩
5.3 B树页面的压缩
5.4 压缩BLOB、VARCHAR和TEXT列
6.OLTP工作负载的压缩
7.SQL压缩语法警告和错误
7.1 file-per-table表空间的SQL压缩语法警告和错误
7.2 通用空间的SQL压缩语法警告和错误 1.表压缩概述 由于处理器和高速缓冲存储器的速度比磁盘存储设备更快因此许多工作负载都是磁盘有关的。数据压缩能够以提高CPU利用率的小成本实现更小的数据库大小、减少I/O和提高吞吐量。 压缩对于读密集型应用程序尤其有价值因为在具有足够RAM以将频繁使用的数据保存在内存中的系统上。 使用ROW_FORMATCOMPRESED创建的InnoDB表可以在磁盘上使用比配置的innodb_page_size值更小的页面大小。 较小的页面需要较少的I/O来读取和写入磁盘这对SSD设备尤其有价值。 压缩页面大小是通过CREATE TABLE或ALTER TABLE KEY_BLOCK_SIZE参数指定的。不同的页面大小要求将表放在FPT表空间或通用表空间的文件中而不是放在系统表空间中因为系统表空间无法存储压缩的表。 有关更多信息请参阅“FPT表空间”和“通用表空间”。 无论KEY_BLOCK_SIZE值如何压缩级别都是相同的。 当您为KEY_BLOCK_SIZE指定更小的值时您可以获得越来越小的页面带来的I/O好处。 但是如果指定的值太小当数据值无法压缩到足以容纳每页中的多行时则会有额外的开销来重新组织页面。根据表中每个索引的键列长度KEY_BLOCK_SIZE的大小有一个严格的限制。指定的值太小CREATE TABLE或ALTER TABLE语句将失败。 在缓冲池中压缩数据保存在小页面中页面大小基于KEY_BLOCK_SIZE值。为了查询或更新列值MySQL还使用未压缩的数据在缓冲池中创建一个未压缩的页面。在缓冲池中对未压缩页面的任何更新也会被重新写回等效的压缩页面。您可能需要调整缓冲池的大小以容纳压缩页和未压缩页的额外数据尽管在需要空间时会将未压缩页从缓冲池中移出然后在下次访问时再次进行压缩。 2.创建压缩表
压缩表可以在FPT表空间或通用表空间中创建。表压缩不适用于InnoDB系统表空间。
系统表空间space0.ibdata文件可以包含用户创建的表但也包含从不压缩的内部系统数据。因此压缩仅适用于FPT表空间或通用表空间中的表和索引。
2.1 在FPT表空间中创建压缩表
要在FPT表空间中创建压缩表必须启用innodb_file_per_table默认设置。
您可以在MySQL配置文件my.cnf或my.ini中设置此参数也可以使用set语句动态设置此参数。
配置innodb_file_per_table选项后在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMATCOMRESSED子句或KEY_BLOCK_SIZE子句或同时指定这两个子句以在FPT表空间中创建压缩表。
例如您可以使用以下语句
SET GLOBAL innodb_file_per_table1;
CREATE TABLE t1(c1 INT PRIMARY KEY)ROW_FORMATCOMPRESSEDKEY_BLOCK_SIZE8;
2.2 在通用表空间中创建压缩表 要在通用表空间中创建压缩表必须为创建表空间时指定的通用表空间定义FILE_BLOCK_SIZE。
FILE_BLOCK_SIZE值必须是相对于innodb_page_size值的有效压缩页大小并且由CREATE TABLE或ALTER TABLE KEY_BLOCK_IZE子句定义的压缩表的页大小必须等于FILE_BLOCC_SIZE/1024。
例如如果innodb_page_size16384FILE_BLOCK_SIZE8192则表的KEY_BLOCK_IZE必须为8。 有关更多信息请参阅下面博客的第三章“通用表空间” 【MySQL精通之路】InnoDB(6)-磁盘结构(3)-表空间-CSDN博客 以下示例演示了创建一个通用表空间和添加一个压缩表。
该示例假设默认的innodb_page_size为16K。8192的FILE_BLOCK_SIZE要求压缩表的KEY_BLOCK_IZE为8。
mysql CREATE TABLESPACE ts2 ADD DATAFILE ts2.ibd FILE_BLOCK_SIZE 8192 EngineInnoDB;mysql CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMATCOMPRESSED KEY_BLOCK_SIZE8; 注意 从MySQL 8.0开始压缩表的表空间文件是使用物理页面大小而不是InnoDB页面大小创建的这使得空压缩表的初始表空间文件大小小于以前的MySQL版本。 如果指定ROW_FORMATCOMPRESSED则可以省略KEY_BLOCK_SIZE KEY_BLOCK_SIZE 设置默认为innodb_page_size一半的值。 如果指定了有效的KEY_BLOCK_SIZE值则可以省略ROW_FORMATCOMPRESSED 自动启用压缩。要确定KEY_BLOCK_SIZE的最佳值通常需要为该子句创建具有不同值的同一表的多个副本然后查看生成的.ibd文件的大小并查看每个文件在实际工作负载下的性能。 对于通用表空间请记住删除表不会减少常规表空间.ibd文件的大小也不会将磁盘空间返回给操作系统。 有关更多信息请参阅通用表空间”。 【MySQL精通之路】InnoDB(6)-磁盘结构(3)-表空间-CSDN博客 KEY_BLOCK_SIZE值被视为建议值如果需要InnoDB可以使用不同的大小。 对于每个表的文件表空间KEY_BLOCK_SIZE只能小于或等于innodb_page_SIZE值。 如果指定的值大于innodb_page_size值则会忽略指定的值发出警告 并将KEY_BLOCK_SIZE设置为innodb_page _size值的一半。 如果innodb_strict_modeON则指定无效的KEY_BLOCK_SIZE值将返回错误。 对于通用表空间有效的KEY_BLOCK_SIZE值取决于表空间的FILE_BLOCK_IZE设置。 有关更多信息请“通用表空间”。 【MySQL精通之路】InnoDB(6)-磁盘结构(3)-表空间-CSDN博客 InnoDB支持32KB和64KB的页面大小但这些页面大小不支持压缩。 有关更多信息请参阅innodb_page_size文档 InnoDB数据页的默认未压缩大小为16KB。 根据选项值的组合MySQL对表空间数据文件.ibd文件使用1KB、2KB、4KB、8KB或16KB的页面大小。 实际压缩算法不受KEY_BLOCK_SIZE值的影响该值决定每个压缩块的大小进而影响每个压缩页中可以压缩的行数。 当在每个表空间的文件中创建压缩表时将KEY_BLOCK_SIZE设置为等于InnoDB页面大小通常不会导致太多压缩。 例如设置KEY_BLOCK_SIZE16通常不会导致太多压缩因为正常的InnoDB页面大小是16KB。 此设置可能仍然适用于具有许多长BLOB、VARCHAR或TEXT列的表因为这些值通常可以很好地压缩因此可能需要较少的溢出页 如“压缩如何适用于InnoDB表”所述。 对于通用表空间不允许KEY_BLOCK_SIZE值等于InnoDB页面大小。 有关更多信息请参阅“通用表空间”。 【MySQL精通之路】InnoDB(6)-磁盘结构(3)-表空间-CSDN博客 根据CREATE TABLE或ALTER TABLE语句中的指定表的所有索引包括聚集索引都使用相同的页大小进行压缩。 ROW_FORMAT和KEY_BLOCK_SIZE等表属性不是InnoDB表的CREATE INDEX语法的一部分如果指定了它们则会被忽略尽管如果指定了它们会出现在SHOW CREATE Table语句的输出中。 有关性能相关的配置选项请参阅“InnoDB表的调整压缩”。 2.3 压缩表的限制 压缩表不能存储在InnoDB系统表空间中。 通用表空间可以包含多个表但压缩表和未压缩表不能共存于同一个通用表空间中。 压缩应用于整个表及其所有相关索引而不是单独的行尽管子句名称为ROW_FORMAT。 InnoDB不支持压缩的临时表。 启用innodb_strict_mode默认值时如果指定了ROW_FORMATCOMPRESSED或KEY_BLOCK_SIZECREATE TEMPORARY TABLE将返回错误。 如果innodb_strict_mode被禁用则会发出警告并使用非压缩行格式创建临时表。 同样的限制也适用于临时表上的ALTER TABLE操作。 3.优化InnoDB表的压缩
待补充 4.运行时监控InnoDB表压缩 应用程序的总体性能、CPU和I/O利用率以及磁盘文件的大小都很好地指示了压缩对应用程序的有效性。本节基于“InnoDB表的调优压缩”中的性能调优建议并展示了如何查找初始测试过程中可能不会出现的问题。 为了深入了解压缩表的性能注意事项可以使用示例“使用压缩Information Schema表”中描述的Information Schema表在运行时监视压缩性能。这些表反映了内存的内部使用情况和总体使用的压缩率。 INNODB_CMP表报告有关正在使用的每个压缩页面大小KEY_BLOCK_SIZE的压缩活动的信息。这些表中的信息是全系统的它总结了数据库中所有压缩表的压缩统计信息。当没有其他压缩表被访问时您可以通过检查这些表来使用这些数据来帮助决定是否压缩表。它在服务器上的开销相对较低因此您可以在生产服务器上定期查询它以检查压缩功能的总体效率。 INNODB_CMP_PER_INDEX表报告有关各个表和索引的压缩活动的信息。这些信息更有针对性对于评估压缩效率和诊断性能问题更有用每次一个表或索引。因为每个InnoDB表都表示为聚集索引MySQL在这种情况下不会对表和索引进行太大区分。InnoDB_CMP_PER_INDEX表确实涉及大量开销因此它更适合开发服务器在那里可以单独比较不同工作负载、数据和压缩设置的效果。为了防止意外地施加这种监视开销必须先启用innodb_cp_per_index_enabled配置选项然后才能查询innodb_cmp_per_index表。 要考虑的关键统计数据是执行压缩和解压缩操作的次数以及所花费的时间。由于MySQL在B树节点太满而无法包含修改后的压缩数据时会对其进行拆分因此请将“成功”压缩操作的数量与此类操作的总体数量进行比较。根据INNODB_CMP和INNODB_CMP_PER_INDEX表中的信息以及整体应用程序性能和硬件资源利用率您可以更改硬件配置、调整缓冲池的大小、选择不同的页面大小或选择不同的表集进行压缩。 如果压缩和解压缩所需的CPU时间很长那么在相同的数据、应用程序工作负载和一组压缩表的情况下改用更快或多核CPU可以帮助提高性能。增加缓冲池的大小也可能有助于提高性能使更多未压缩的页面可以留在内存中从而减少对仅以压缩形式存在于内存中的页面进行解压缩的需要。 总体而言大量的压缩操作与应用程序中的INSERT、UPDATE和DELETE操作的数量以及数据库的大小相比可能表明某些压缩表的更新过多无法进行有效的压缩。如果是这样请选择更大的页面大小或者对压缩的表更有选择性。 如果“成功”压缩操作COMPRESS_OPS_OK的数量占压缩操作总数COMPREST_OPS的百分比很高则系统可能运行良好。如果比率较低那么MySQL重组、重新压缩和拆分B树节点的频率会高于预期。在这种情况下请避免压缩某些表或增加某些压缩表的KEY_BLOCK_SIZE。对于导致应用程序中“压缩失败”的数量超过总数的1%或2%的表您可能会关闭压缩。在诸如数据加载之类的临时操作期间这样的故障率可能是可接受的。 5.InnoDB表的压缩工作原理
本节描述了一些关于InnoDB表压缩的内部实现细节。这里提供的信息可能有助于调整性能但对于压缩的基本用途来说这不是必须的。
5.1 压缩算法 一些操作系统在文件系统级别实现压缩。 文件通常被划分为固定大小的块这些块被压缩为可变大小的块。这很容易导致碎片化。每次修改块中的某个内容时都会在将整个块写入磁盘之前对其进行重新压缩。这些特性使得这种压缩技术不适合在更新密集型数据库系统中使用。 MySQL借助著名的zlib库实现压缩该库实现了LZ77压缩算法。 这种压缩算法在CPU利用率和减少数据大小方面都是成熟、稳健和高效的。 该算法是“无损”的因此原始的未压缩数据总是可以从压缩的形式中重建。 LZ77压缩的工作原理是找到要压缩的数据中重复的数据序列。数据中的值模式决定了它的压缩效果但典型的用户数据通常压缩50%或更多。 与应用程序执行的压缩或其他一些数据库管理系统的压缩功能不同InnoDB压缩既适用于用户数据也适用于索引。在许多情况下索引可能占数据库总大小的40-50%或更多因此这种差异是显著的。 当数据集的压缩工作正常时InnoDB数据文件的大小每个表空间或通用表空间.ibd文件的文件是未压缩大小的25%到50%或者可能更小。根据工作负载的不同这个较小的数据库反过来可以减少I/O并提高吞吐量而在提高CPU利用率方面的成本并不高。 您可以通过修改innodb_compression_level配置选项来调整压缩级别和CPU开销之间的平衡。 5.2 InnoDB数据存储和压缩
InnoDB表中的所有用户数据都存储在包含B树索引聚集索引的页中。
在其他一些数据库系统中这种类型的索引被称为“索引组织表”。索引节点中的每一行都包含用户指定或系统生成的主键和表中所有其他列的值。
InnoDB表中的二级索引也是B树包含成对的值索引键和指向聚集索引中一行的指针。
指针实际上是表主键的值如果需要索引键和主键以外的列则用于访问聚集索引。辅助索引记录必须始终匹配单个B树页。
B树节点包括聚集索引和二级索引的压缩与用于存储长VARCHAR、BLOB或TEXT列的溢出页的压缩处理方式不同如下节所述。
5.3 B树页面的压缩
由于B树页面经常更新因此需要进行特殊处理。重要的是要尽量减少B树节点的拆分次数 博主PS:回忆一下页分裂和页合并 并尽量减少解压缩和重新压缩其内容的需要。 MySQL使用的一种技术是以未压缩的形式维护B树节点中的一些系统信息从而促进某些即时更新。例如这允许在不进行任何压缩操作的情况下对行进行标记删除标记为删除但未删除和删除。 此外MySQL试图避免在索引页更改时对其进行不必要的解压缩和重新压缩。 在每个B树页面中系统都会保存一个未压缩的“修改日志modification log”以记录对页面所做的更改。可以将小记录的更新和插入写入此修改日志而不需要完全重建整个页面。 当修改日志的空间用完时InnoDB会解压缩页面应用更改并重新压缩页面。如果重新压缩失败这种情况被称为压缩失败B树节点将被拆分并重复该过程直到更新或插入成功。 为了避免在写密集型工作负载如OLTP应用程序中频繁出现压缩故障MySQL有时会在页面中保留一些空白空间填充以便修改日志更快地填满并在仍有足够空间避免拆分的情况下重新压缩页面。随着系统跟踪页面拆分的频率每个页面中剩余的填充空间量会有所不同。在频繁写入压缩表的繁忙服务器上可以调整innodb_compression_failure_threshold_pct和innodb_impression_pad_pct_max配置选项来微调此机制 通常MySQL要求InnoDB表中的每个B树页面至少可以容纳两条记录。对于压缩表这一要求已经放宽。 B树节点的叶页无论是主键还是辅助索引只需要容纳一条记录但该记录必须以未压缩的形式放入每页修改日志中。 如果innodb_strict_mode为ONMySQL将在CREATE TABLE或CREATE INDEX期间检查最大行大小。如果行不适合则会发出以下错误消息ERROR HY000: Too big row. 如果在innodb_strict_mode为OFF时创建表并且随后的INSERT或UPDATE语句试图创建
不适合压缩页大小的索引项则操作将失败并返回错误42000Row size too large.。
此错误消息没有命名记录过大的索引也没有提及索引记录的长度或该特定索引页上的最大记录大小。 要解决此问题请使用ALTER table重建表然后选择更大的压缩页大小KEY_BLOCK_SIZE缩短任何列前缀索引或使用ROW_FORMATDYNAMIC或ROW_FORMATCOMPACT完全禁用压缩。 innodb_strict_mode不适用于同样支持压缩表的通用表空间。一般表空间的表空间管理规则是独立于innodb_strict_mode严格执行的。 有关更多信息请参阅“CREATE TABLESPACE语句”。 5.4 压缩BLOB、VARCHAR和TEXT列 在InnoDB表中不属于主键的BLOB、VARCHAR和TEXT列可以存储在单独分配的溢出页上。我们将这些列称为页外列。它们的值存储在溢出页的单链接列表中。 对于在ROW_FORMATDYNAMIC或ROW_FORMATCOMPRESED中创建的表BLOB、TEXT或VARCHAR列的值可以完全页外存储这取决于它们的长度和整行的长度。 对于页外存储的列聚集索引记录只包含指向溢出页的20字节指针每列一个。 是否有任何列存储在页外取决于页大小和行的总大小。当该行太长而无法完全容纳在聚集索引的页面中时MySQL会选择最长的列作为页外存储直到该行适合聚集索引页面。如上所述如果一行本身不适合压缩页面则会发生错误。 注意 对于在ROW_FORMATDYNAMIC或ROW_FORMATCOMPRESED中创建的表小于或等于40字节的TEXT和BLOB列始终存储在一行中。 使用ROW_FORMATREDUNDANT和ROW_FORMTCOMPACT的表将BLOB、VARCHAR和TEXT列的前768个字节与主键一起存储在聚集索引记录中。
768字节的前缀后面跟着一个20字节的指针指向包含列值其余部分的溢出页。 当表为COMPRESSED格式时所有写入溢出页的数据都会“按原样”压缩也就是说MySQL将zlib压缩算法应用于整个数据项。 除了数据之外压缩的溢出页还包含未压缩的报头和尾部该报头和尾部包括页校验和到下一个溢出页的指针等。因此如果数据是高度可压缩的那么对于较长的BLOB、TEXT或VARCHAR列可以获得非常显著的存储节省文本数据通常就是这样。 图像数据例如JPEG通常已经被压缩因此存储在压缩表中没有太大好处双重压缩会浪费CPU周期而且节省很少的空间或根本不节省空间。 溢出页面的大小与其他页面相同。即使列的总长度只有8K字节一个包含十列的行也会占用十个溢出页。在未压缩的表中10个未压缩的溢出页占用160K字节。在8K页面大小的压缩表中它们只占用80K字节。因此对于具有长列值的表使用压缩表格式通常更有效。 对于每个表的文件表空间使用16K压缩的页面大小可以降低BLOB、VARCHAR或TEXT列的存储和I/O成本因为这些数据通常压缩得很好因此可能需要更少的溢出页面即使B树节点本身占用的页面与未压缩形式的页面一样多。一般表空间不支持16K压缩页面大小KEY_BLOCK_SIZE。 有关更多信息请参阅下面博客的第三节。“通用表空间”。 【MySQL精通之路】InnoDB(6)-磁盘结构(3)-表空间-CSDN博客 6.OLTP工作负载的压缩 传统上InnoDB压缩功能主要推荐用于只读或以读取为主的工作负载例如在数据仓库配置中。 SSD存储设备速度快但相对较小且价格昂贵这使得压缩对OLTP工作负载也很有吸引力高流量、交互式网站可以通过将压缩表与频繁执行INSERT、UPDATE和DELETE操作的应用程序一起使用来降低其存储要求和每秒I/O操作IOPS。 通过这些配置选项您可以调整特定MySQL实例的压缩工作方式重点关注写密集型操作的性能和可扩展性 innodb_compression_level允许您提高或降低压缩程度。更高的值可以将更多的数据放入存储设备而在压缩过程中会消耗更多的CPU开销。当存储空间不是关键的或者您认为数据不是特别可压缩时使用较低的值可以减少CPU开销。 innodb_compression_failure_threshold_pct指定了更新压缩表期间压缩失败的截止点。 当超过该阈值时MySQL开始在每个新的压缩页面中留下额外的可用空间动态调整可用空间量使其达到innodb_compression_pad_pct_max指定的页面大小的百分比 innodb_compression_pad_pct_max允许您调整每个页面中保留的最大空间量以记录对压缩行的更改而无需再次压缩整个页面。值越高在不重新压缩页面的情况下可以记录的更改越多。 MySQL为每个压缩表中的页面使用可变的可用空间只有当指定百分比的压缩操作在运行时“失败”时才需要昂贵的操作来拆分压缩页面。 innodb_log_compressed_pages允许您禁用将重新压缩的页面的图像写入重做日志。当对压缩数据进行更改时可能会发生重新压缩。默认情况下会启用此选项以防止在恢复过程中使用不同版本的zlib压缩算法时可能发生的损坏。如果您确信zlib版本不会更改请禁用innodb_log_compressed_pages以减少修改压缩数据的工作负载的redolog生成。 因为处理压缩数据有时需要同时将页面的压缩版本和未压缩版本保存在内存中所以在OLTP风格的工作负载中使用压缩时请准备增加innodb_buffer_pool_size配置选项的值。 7.SQL压缩语法警告和错误
本节介绍将表压缩功能用于file-per-table表空间和通用表空间时可能遇到的语法警告和错误。 7.1 file-per-table表空间的SQL压缩语法警告和错误
7.2 通用空间的SQL压缩语法警告和错误
如果在创建表空间时没有为通用表空间定义FILE_BLOCK_SIZE则表空间不能包含压缩表。如果尝试添加压缩表则会返回一个错误如以下示例所示
mysql CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd EngineInnoDB;mysql CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMATCOMPRESSEDKEY_BLOCK_SIZE8;
ERROR 1478 (HY000): InnoDB: Tablespace ts1 cannot contain a COMPRESSED table 尝试将KEY_BLOCK_SIZE无效的表添加到通用表空间会返回错误如以下示例所示
mysql CREATE TABLESPACE ts2 ADD DATAFILE ts2.ibd FILE_BLOCK_SIZE 8192 EngineInnoDB;mysql CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMATCOMPRESSEDKEY_BLOCK_SIZE4;
ERROR 1478 (HY000): InnoDB: Tablespace ts2 uses block size 8192 and cannot
contain a table with physical page size 4096
对于通用表空间表的KEY_BLOCK_SIZE必须等于表空间的FILE_BLOCK_IZE除以1024。例如如果表空间的FILE_BLOCK_SIZE为8192则表的KEY_BLOCK_IZE必须为8。 试图将未压缩行格式的表添加到配置为存储压缩表的通用表空间中会返回错误如以下示例所示
mysql CREATE TABLESPACE ts3 ADD DATAFILE ts3.ibd FILE_BLOCK_SIZE 8192 EngineInnoDB;mysql CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts3 ROW_FORMATCOMPACT;
ERROR 1478 (HY000): InnoDB: Tablespace ts3 uses block size 8192 and cannot
contain a table with physical page size 16384
innodb_strict_mode不适用于通用表空间。通用表空间的表空间管理规则是独立于innodb_strict_mode严格执行的。 有关更多信息请参阅“CREATE TABLESPACE语句”。 有关将压缩表与通用表空间一起使用的更多信息请参阅“通用表空间”的第三节。 【MySQL精通之路】InnoDB(6)-磁盘结构(3)-表空间-CSDN博客 下一篇
【MySQL精通之路】InnoDB(9)-表和页压缩(2)-页压缩-CSDN博客
