SQL优化【InnoDB-表优化】

image.png

innodb表优化

8.5.1优化InnoDB表的存储布局

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
一旦数据达到稳定的大小,或者增长表增加了几十或几百兆字节,请考虑使用该OPTIMIZE TABLE语句来重新组织表并压缩任何浪费的空间。

重新组织的表需要更少的磁盘I / O来执行全表扫描。
当其他技术(如改进索引使用或调整应用程序代码)不切实际时,这是一种直接技术,可以提高性能。


OPTIMIZE TABLE复制表格的数据部分并重建索引。
好处来自改进索引内数据的打包,并减少表空间和磁盘内的碎片。
好处取决于每个表中的数据。
您可能会发现某些人有显着的收益,而不是其他人,或者收益会随着时间的推移而下降,直到您再次优化表。
如果表很大或者重建的索引不适合缓冲池,则此操作可能会很慢。
向表中添加大量数据后的第一次运行通常比后期运行慢得多。


在中InnoDB,有一个很长的PRIMARY KEY(无论是一个长的值的单个列,还是多个形成一个长复合值的列)浪费了大量的磁盘空间。
一行中的主键值在所有指向同一行的二级索引记录中都是重复的。
(请参见第14.8.2.1节“集群索引和二级索引”。
)AUTO_INCREMENT如果主键很长,或者索引长VARCHAR列的前缀而不是整列,则创建一个列作为主键。


使用VARCHAR数据类型而不是CHAR存储可变长度的字符串或具有多个NULL值的列 。
甲 列总是占据字符来存储数据,即使该字符串是较短,或者其值 。
较小的表适合缓冲池,并减少磁盘I / O。
CHAR(N)NNULL

使用COMPACT行格式(默认InnoDB格式)和可变长度字符集(如 utf8或)时sjis, 列占用可变数量的空间,但仍至少为字节。
CHAR(N)N

对于大的表或者包含大量重复的文本或数字数据的表,请考虑使用 COMPRESSED行格式。
将数据带入缓冲池或执行全表扫描需要较少的磁盘I / O。
在做出永久性决定之前,请测量使用行格式COMPRESSED与 您可以实现的压缩量 COMPACT。

{———-}

8.5.2优化InnoDB事务管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
要优化InnoDB事务处理,请在事务功能的性能开销和服务器的工作负载之间找到理想的平衡点。
例如,如果应用程序每秒提交数千次,则应用程序可能会遇到性能问题,如果应用程序每2-3小时提交一次,则会出现不同的性能问题。


默认的MySQL设置AUTOCOMMIT=1 可能会对繁忙的数据库服务器造成性能限制。
在可行的情况下,在进行所有更改后,通过发布SET AUTOCOMMIT=0或START TRANSACTION声明将几个相关的数据更改操作包括到单个事务中 ,然后包含 COMMIT声明。


InnoDB如果该事务对数据库进行了修改,则必须在每次事务提交时将日志刷新到磁盘。
当每次更改之后都进行提交(与默认的自动提交设置一样)时,存储设备的I / O吞吐量会对每秒潜在操作的数量设置上限。


或者,对于仅由单个SELECT语句组成的事务,开启AUTOCOMMIT有助于 InnoDB识别只读事务并优化它们。
有关要求,请参见 第8.5.3节“优化InnoDB只读事务”。


避免在插入,更新或删除大量行后执行回滚。
如果一个大事务正在降低服务器的性能,那么回滚会导致问题变得更糟,可能需要几次才能执行原始数据更改操作。
杀死数据库进程无济于事,因为在服务器启动时会再次开始回滚。


为了尽量减少发生此问题的机会:

增加缓冲池的大小, 以便可以缓存所有数据更改更改,而不是立即写入磁盘。


设置 innodb_change_buffering=all 以便更新和删除操作在插入之外进行缓冲。


考虑COMMIT在大数据更改操作期间定期发布语句,可能会将单个删除或更新分为多个在较少数量的行上操作的语句。


为避免出现失控回滚,请增加缓冲池,以便回滚变为受CPU限制且运行速度很快,或者innodb_force_recovery=3按照第14.18.2节“InnoDB恢复”中的说明关闭服务器并重新启动 。


预计这个问题很少出现在默认设置下 innodb_change_buffering=all,这样可以将更新和删除操作缓存到内存中,从而使它们在第一时间执行得更快,并且如果需要还可以更快地回滚。
确保在处理长时间运行事务的服务器上使用此参数设置,并进行多次插入,更新或删除操作。


如果发生崩溃时可以承受某些最新已提交事务的丢失,则可以将该innodb_flush_log_at_trx_commit 参数设置 为0. InnoDB尽管无法保证刷新,但试图每秒刷新一次日志。
另外,将值设置 innodb_support_xa为0,这将减少由于在磁盘数据和二进制日志上同步而导致的磁盘刷新次数。


注意
innodb_support_xa已弃用,将在未来版本中删除。
从MySQL 5.7.10开始,InnoDB对XA事务中的两阶段提交的支持始终处于启用状态,并且innodb_support_xa不再允许禁用 。


当行被修改或删除时,行和关联的 撤消日志不会立即被物理删除,甚至在事务提交后立即被删除。
保留旧数据,直到完成较早或同时开始的事务,以便这些事务可以访问修改行或已删除行的先前状态。
因此,长时间运行的事务可以防止InnoDB清除不同事务更改的数据。


当在长时间运行的事务中修改或删除行时,使用READ COMMITTED和 REPEATABLE READ隔离级别的其他事务在 读取相同行时必须执行更多工作才能重新构建旧数据。


当长时间运行的事务修改表时,其他事务对该表的查询不使用覆盖索引技术。
通常可以从辅助索引中检索所有结果列的查询,而是从表格数据中查找适当的值。


如果发现二级索引页面 PAGE_MAX_TRX_ID太新,或者二级索引中的记录被删除标记,则 InnoDB可能需要使用聚簇索引查找记录。

8.5.3优化InnoDB只读事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
InnoDB可以避免与为已知为只读的事务设置事务ID(TRX_ID字段)相关联的开销。
只有可能执行写入操作或锁定读取的事务才需要事务ID , 例如 。
消除不必要的事务ID会减少每次查询或数据更改语句构造读取视图时查阅的内部数据结构的大小。
SELECT ... FOR UPDATE

InnoDB 在以下情况下检测只读事务:

交易以START TRANSACTION READ ONLY声明开始 。
在这种情况下,尝试更改数据库(for InnoDB, MyISAM或其他类型的表)会导致错误,并且事务以只读状态继续:

错误1792(25006):无法在READ ONLY事务中执行语句。

您仍然可以在只读事务中更改特定于会话的临时表,或者为它们发出锁定查询,因为这些更改和锁对任何其他事务都不可见。


该autocommit设置处于打开状态,以便事务保证为单个语句,构成事务的单个语句为“ 非锁定 ” SELECT语句。
也就是说 SELECT,它不使用一个FOR UPDATE或一个LOCK IN SHARED MODE 子句。


事务在没有READ ONLY选项的情况下启动,但没有显式锁定行的更新或语句已经执行。
在需要更新或显式锁定之前,事务处于只读模式。


因此,对于读取密集型应用,如报表生成器,您可以调整的序列,InnoDB 由内而外将它们分组查询 START TRANSACTION READ ONLY和 COMMIT,
或通过打开autocommit 运行之前设置SELECT语句,或简单地避免与查询穿插任何数据更改语句。


有关信息 START TRANSACTION,并 autocommit请参见 13.3.1节,“START TRANSACTION,COMMIT和ROLLBACK语法”。


注意
具有自动提交,非锁定和只读(AC-NL-RO)资格的事务处于特定内部 InnoDB数据结构之外,因此未在SHOW ENGINE INNODB STATUS输出中列出 。

8.5.4优化InnoDB重做日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
考虑以下关于优化重做日志的指导原则:

使您的重做日志文件变大,甚至与缓冲池一样大 。
当 InnoDB写完重做日志文件时,它必须将检查点中缓冲池的修改内容写入磁盘 。

小的重做日志文件导致许多不必要的磁盘写入。
虽然历史上重要的重做日志文件会导致冗长的恢复时间,但现在恢复速度更快,
您可以放心地使用大型重做日志文件。


重做日志文件的大小和数量使用innodb_log_file_size 和 innodb_log_files_in_group 配置选项进行配置。

有关修改现有重做日志文件配置的信息,请参见 第14.7.2节“更改InnoDB重做日志文件的数量或大小”。


考虑增加日志缓冲区的大小 。
大型日志缓冲区允许大型 事务运行,而无需在事务提交之前将日志写入磁盘。

因此,如果您有更新,插入或删除多行的事务,则使日志缓冲区更大可节省磁盘I / O。

日志缓冲区大小使用innodb_log_buffer_size 配置选项进行 配置。


配置 innodb_log_write_ahead_size 配置选项以避免“ 读写 ”。
该选项定义重做日志的预写块大小。

设置 innodb_log_write_ahead_size 为匹配操作系统或文件系统缓存块大小。

由于重做日志的预写块大小与操作系统或文件系统高速缓存块大小之间的不匹配,
重做日志块未完全缓存到操作系统或文件系统时发生了写入时读写。


有效值 innodb_log_write_ahead_size 是InnoDB日志文件块大小(2 n)的倍数。
最小值是InnoDB日志文件块大小(512)。

指定最小值时不发生预写。
最大值等于该 innodb_page_size值。

如果您指定的值 innodb_log_write_ahead_size 大于该 innodb_page_size值,则该 innodb_log_write_ahead_size 设置将被截断为该 innodb_page_size值。


innodb_log_write_ahead_size 相对于操作系统或文件系统缓存块大小 设置 值太低会导致写入时读写。
fsync由于一次写入多个块,因此将值设置得过高可能会对日志文件写入的性能产生轻微影响 。

8.5.5 InnoDB表的批量数据加载

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
这些性能提示补充了第8.2.4.1节“优化INSERT语句”中有关快速插入的一般准则。


将数据导入时InnoDB,请关闭自动提交模式,因为它会为每个插入操作执行日志刷新到磁盘。
为了您的导入操作过程中禁用自动提交,与环绕它 SET autocommit和 COMMIT语句:

SET autocommit = 0;
... SQL import statements ...
承诺;
该mysqldump的选项 --opt创建这样的快速导入到转储文件InnoDB 表,即使没有与他们包装 SET autocommit和 COMMIT报表。


如果您UNIQUE对辅助键有限制,可以通过在导入会话期间暂时关闭唯一性检查来加快表导入的速度:

SET unique_checks = 0;
... SQL import statements ...
SET unique_checks = 1;
对于大表而言,这可以节省大量的磁盘I / O,因为 InnoDB可以使用其更改缓冲区来批量写入二级索引记录。
确保数据不包含重复的密钥。


如果您FOREIGN KEY的表中存在约束,则可以通过在导入会话期间关闭外键检查来加速表导入:

SET foreign_key_checks = 0;
... SQL import statements ...
SET foreign_key_checks = 1;
对于大表,这可以节省大量的磁盘I / O。


INSERT 如果您需要插入多行, 请使用多行语法来减少客户端和服务器之间的通信开销:

INSERT INTO的值(1,2),(5,5),...;
此提示适用于插入任何表格,而不仅限于 InnoDB表格。


当对具有自动增量列的表进行批量插入时,请将其设置 innodb_autoinc_lock_mode为2而不是默认值1.有关详细信息,
请参见 第14.8.1.5节“InnoDB中的AUTO_INCREMENT处理”。


执行批量插入时,按PRIMARY KEY顺序插入行速度更快 。
InnoDB表使用 聚集索引,这使得使用数据的顺序相对较快PRIMARY KEY。

按PRIMARY KEY顺序执行批量插入对于完全不适合缓冲池的表格尤为重要。


为了将数据加载到InnoDB FULLTEXT索引时获得最佳性能 ,请按照以下步骤操作:

FTS_DOC_ID在创建表的时候 定义一个类型为BIGINT UNSIGNED NOT NULL,具有唯一索引的列 FTS_DOC_ID_INDEX。
例如:

CREATE TABLE t1(
FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,
title varchar(255)NOT NULL DEFAULT'',
文本中文NOT NULL,
PRIMARY KEY(`FTS_DOC_ID`)
)ENGINE = InnoDB DEFAULT CHARSET = latin1;
在t1(FTS_DOC_ID)上创建唯一索引FTS_DOC_ID_INDEX;
将数据加载到表中。


FULLTEXT数据加载完成后 创建索引。


注意
FTS_DOC_ID在创建表格 时添加列时,确保在 FTS_DOC_ID更新 FULLTEXT索引列时更新列,因为FTS_DOC_ID每个INSERTor 必须单调递增 UPDATE。

如果您选择不添加FTS_DOC_IDat表创建时间并InnoDB为您管理DOC ID,InnoDB则会FTS_DOC_ID在下一次CREATE FULLTEXT INDEX调用时将其添加 为隐藏列。

但是,这种方法需要重建表格,这会影响性能。

8.5.6优化InnoDB查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
要调整InnoDB表的查询,请在每个表上创建一组适当的索引。
有关详细信息,请参见 第8.3.1节“MySQL如何使用索引”。

遵循这些InnoDB索引的指标:

由于每个InnoDB表都有一个 主键(无论您是否要求),请为每个表指定一组主键列,这些列用于最重要且时间关键的查询中。


不要在主键中指定太多或太长的列,因为这些列值在每个二级索引中都是重复的。

当索引包含不必要的数据时,读取此数据和内存以进行缓存的I / O会降低服务器的性能和可伸缩性。


不要 为每列创建单独的 二级索引,因为每个查询只能使用一个索引。
仅有少数不同值的很少测试的列或列索引可能对任何查询都没有帮助。

如果您对同一个表有很多查询,那么测试不同的列组合,尝试创建少量 连接索引而不是大量单列索引。

如果索引包含结果集所需的所有列(称为 覆盖索引),则查询可能完全避免读取表数据。


如果索引列不能包含任何 NULL值,请NOT NULL在创建表时声明它。
当知道每列是否包含NULL值时,
优化器可以更好地确定哪个索引最有效地用于查询 。


您可以InnoDB使用第8.5.3节“优化InnoDB只读事务”中的技术优化表的 单一查询事务 。

8.5.7优化InnoDB DDL操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
对于表和索引(CREATE,ALTER和 DROP语句)的DDL操作,
表中最重要的方面InnoDB是在MySQL 5.5和更高版本中创建和删除二级索引比在早期版本中快得多。

有关详细信息,请参见 第14.13.1节“在线DDL概述”。


“ 快速索引创建 ”使得在某些情况下,在将数据加载到表中之前删除索引,然后在加载数据后重新创建索引会更快。


使用TRUNCATE TABLE空表,不。
外键约束,可以使一个语句的工作像一个普通的声明,在这种情况下,命令序列喜欢 和 可能是最快的。

DELETE FROM tbl_nameTRUNCATEDELETEDROP TABLECREATE TABLE

因为主键是每个InnoDB表的存储布局不可分割的组成部分,并且更改主键的定义涉及重新组织整个表,
所以始终将主键设置为CREATE TABLE语句的一部分 ,并提前进行计划,以便您不需要 ALTER或DROP之后的主键。

8.5.8优化InnoDB磁盘I / O

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
如果您遵循SQL操作的数据库设计和调优技术的最佳实践,但由于繁重的磁盘I / O活动导致数据库仍然很慢,请考虑这些磁盘I / O优化。

如果Unix top工具或Windows任务管理器显示工作负载的CPU使用百分比低于70%,那么您的工作负载可能是磁盘限制的。


增加缓冲池大小

当表数据缓存在InnoDB 缓冲池中时,可以通过查询重复访问它,而不需要任何磁盘I / O。

用innodb_buffer_pool_size 选项指定缓冲池的大小 。

该内存区域非常重要,通常建议将 innodb_buffer_pool_size其配置为系统内存的50%至75%。

有关更多信息,请参见第8.12.4.1节“MySQL如何使用内存”。


调整冲洗方法

在GNU / Linux和Unix的某些版本中fsync(),InnoDB使用Unix 调用( 默认使用)和类似方法将文件刷新到磁盘的速度惊人地慢。

如果数据库写入性能出现问题,请使用innodb_flush_method 参数集进行基准测试 O_DSYNC。


在Linux上使用本机AIO的noop或截止日期I / O调度程序

InnoDB使用Linux上的异步I / O子系统(本机AIO)来执行数据文件页面的预读和写入请求。

此行为innodb_use_native_aio 由默认情况下启用的配置选项控制 。

使用本机AIO时,I / O调度程序的类型对I / O性能有更大的影响。

通常,建议使用noop和截止日期I / O调度程序。
进行基准测试以确定哪个I / O调度程序为您的工作负载和环境提供最佳结果。

有关更多信息,请参见 第14.6.8节“在Linux上使用异步I / O”。


在x86_64体系结构的Solaris 10上使用直接I / O

在InnoDBx86_64体系结构(AMD Opteron)的Solaris 10上使用存储引擎时,请使用直接I / O InnoDB相关文件以避免性能下降InnoDB。

要为用于存储InnoDB相关文件的整个UFS文件系统使用直接I / O,请 使用该forcedirectio选项安装它 ; 见 mount_ufs(1M)。

(Solaris 10 / x86_64上的默认设置不是使用此选项。

要仅将直接I / O应用于InnoDB文件操作而不是整个文件系统,请设置 innodb_flush_method = O_DIRECT。

使用此设置, InnoDB呼叫 directio()而不是 fcntl() 用于I / O到数据文件(不用于I / O到日志文件)。


在Solaris 2.6或更高版本中使用原始存储来存储数据和日志文件

在任何版本的Solaris 2.6及更高版本和任何平台(sparc / x86 / x64 / amd64)上InnoDB使用具有较大innodb_buffer_pool_size值 的存储引擎时,
请 InnoDB在裸设备上或单独的直接I / O UFS上使用数据文件和日志文件进行基准测试 文件系统,使用forcedirectio前面所述的安装选项。

(innodb_flush_method如果需要对日志文件进行直接I / O ,则必须使用装入选项而不是设置 。

Veritas文件系统VxFS的用户应使用 convosync=direct装入选项。


不要将其他MySQL数据文件(如MyISAM表的那些文件) 放在直接I / O文件系统上。
不能将可执行文件或库放置在直接I / O文件系统上。


使用额外的存储设备

可以使用其他存储设备来设置RAID配置。
有关相关信息,请参见 第8.12.2节“优化磁盘I / O”。


或者,InnoDB表空间数据文件和日志文件可以放在不同的物理磁盘上。
有关更多信息,请参阅以下部分:

第14.6.1节“InnoDB启动配置”

第14.7.5节“在数据目录之外创建文件 - 表 - 表 - 表空间”

创建一个通用表空间

第14.8.1.3节“移动或复制InnoDB表”

考虑非旋转存储

非循环存储通常为随机I / O操作提供更好的性能; 和顺序I / O操作的旋转存储。

在通过旋转和非旋转存储设备分发数据和日志文件时,请考虑主要在每个文件上执行的I / O操作的类型。


随机I /面向O形文件通常包括 文件的每个表 和普通表空间的数据文件, 撤销表空间 文件和 临时表空间文件。

连续的面向I / O的文件包括InnoDB 系统表空间文件(由于 二次写入缓冲和 更改缓冲)以及日志文件(如二进制日志文件和重做日志文件)。


使用非循环存储时,请查看以下配置选项的设置:

innodb_checksum_algorithm

该crc32选件使用更快的校验和算法,推荐用于快速存储系统。


innodb_flush_neighbors

该选项优化了旋转存储设备的I / O。
禁用它用于非旋转存储或混合旋转和非旋转存储。


innodb_io_capacity

对于较低端的非旋转存储设备,默认设置200通常是足够的。
对于更高端的总线连接设备,请考虑更高的设置,例如1000。


innodb_io_capacity_max

默认值2000适用于使用非循环存储的工作负载。
对于高端,总线连接的非旋转存储设备,考虑更高的设置,如2500。


innodb_log_compressed_pages

如果重做日志位于非循环存储中,请考虑禁用此选项以减少日志记录。
请参阅 禁用压缩页面的记录。


innodb_log_file_size

如果重做日志位于非循环存储中,请配置此选项以最大化高速缓存和写入组合。


innodb_page_size

考虑使用与磁盘的内部扇区大小相匹配的页面大小。
早期的SSD设备通常具有4k扇区大小。
一些较新的设备具有16k扇区大小。

默认InnoDB 页面大小为16k。
保持页面大小接近存储设备块大小可将重写到磁盘的未更改数据量减到最少。


binlog_row_image

如果二进制日志位于非循环存储中,并且所有表都有主键,请考虑设置此选项minimal以减少日志记录。


确保为您的操作系统启用TRIM支持。
它通常默认启用。


增加I / O容量以避免积压

如果由于InnoDB 检查点 操作导致吞吐量周期性下降 ,请考虑增加innodb_io_capacity 配置选项的值 。

较高的值会导致更频繁的 刷新,从而避免可能导致吞吐量下降的工作积压。


如果冲洗不落后,则I / O容量降低

如果系统不会因InnoDB 冲洗操作而落后 ,请考虑降低innodb_io_capacity 配置选项的值 。

通常情况下,您保持该选项的值尽可能低,但不能太低,以至于导致吞吐量的周期性下降,如前面的项目符号所述。

在可以降低选项值的典型场景中,您可能会在以下输出中看到类似的组合 SHOW ENGINE INNODB STATUS:

历史名单长度低,低于几千。


插入缓冲区合并接近插入的行。


修改缓冲池中的页面始终远低于 innodb_max_dirty_pages_pct 缓冲池。
(在服务器没有进行批量插入时进行测量;在批量插入时修改的页面百分比显着增加,这是正常的。


Log sequence number - Last checkpoint 小于7/8或理想情况下小于InnoDB 日志文件总大小的6/8 。


将系统表空间文件存储在Fusion-io设备上

通过在支持原子写入的Fusion-io设备上存储系统表空间文件(“ ibdata文件 ”),您可以利用与双写缓冲区相关的I / O优化。

在这种情况下,innodb_doublewrite自动禁用doublewrite buffering(),并且Fusion-io原子写入用于所有数据文件。

此功能仅在Fusion-io硬件上受支持,并且仅适用于Linux上的Fusion-io NVMFS。

要充分利用此功能,建议使用此 innodb_flush_method设置O_DIRECT。


注意
由于双写缓冲区设置是全局性的,所以对于驻留在非Fusion-io硬件上的数据文件,也会禁用双写缓冲。


禁用压缩页面的日志记录

使用InnoDB表格 压缩功能时,当对压缩数据进行更改时,重新压缩页面的图像 将写入 重做日志。

此行为innodb_log_compressed_pages由默认情况下启用的控制 ,以防止zlib 在恢复期间使用不同版本的压缩算法时可能发生的损坏。

如果您确定zlib版本不会更改,请禁用 innodb_log_compressed_pages 以减少修改压缩数据的工作负载的重做日志生成。

8.5.9优化InnoDB配置变量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
不同的设置对于服务器的工作效率最高,可以预测负载较轻,而服务器则始终处于满负荷运行状态,或者遇到高活动的峰值。


由于InnoDB存储引擎会自动执行许多优化,因此许多性能调整任务都需要进行监视,以确保数据库性能良好,并在性能下降时更改配置选项。

有关详细的性能监控信息,请参见 第14.16节“InnoDB与MySQL性能架构的集成”InnoDB。


您可以执行的主要配置步骤包括:

允许InnoDB在包含它们的系统上使用高性能内存分配器。
请参见 第14.6.4节“为InnoDB配置内存分配器”。


控制哪些InnoDB缓冲区更改数据的数据更改操作的类型 ,以避免频繁的小磁盘写入。
请参见 第14.6.5节“配置InnoDB更改缓冲”。

由于默认设置是缓冲所有类型的数据更改操作,因此只有在需要减少缓冲量时才更改此设置。


使用该innodb_adaptive_hash_index 选项打开和关闭自适应哈希索引功能 。
有关更多信息,请参见第14.4.3节“自适应散列索引”。

您可能会在非正常活动期间更改此设置,然后将其恢复到原始设置。


InnoDB如果上下文切换是瓶颈,则 设置对并发线程数量的 限制。
请参见 第14.6.6节“为InnoDB配置线程并发”。


控制InnoDB与其预读操作相关的预取量 。
当系统没有使用I / O容量时,更多的预读可以提高查询的性能。

预读过多会导致重负载系统的性能周期性下降。
请参见 第14.6.3.5节“配置InnoDB缓冲池预取(预读)”。


如果您有一个高端I / O子系统未被默认值充分利用,则增加读取或写入操作的后台线程数。
请参见 第14.6.7节“配置背景InnoDB I / O线程数”。


控制I / O InnoDB在后台执行多少操作。
请参见 第14.6.9节“配置InnoDB主线程I / O速率”。

如果您观察到性能的周期性下降,您可能会缩减此设置。


控制确定何时InnoDB执行某些类型的背景写入的算法 。
请参见 第14.6.3.6节“配置InnoDB缓冲池刷新”。

该算法适用于某些类型的工作负载,但不适用于其他类型的工作负载,因此如果观察到性能下降,可能会关闭此设置。


利用多核处理器及其高速缓存存储器配置,尽量减少上下文切换的延迟。
请参见 第14.6.10节“配置旋转锁定轮询”。


防止诸如表扫描之类的一次性操作干扰存储在InnoDB缓冲区高速缓存中的经常访问的数据 。
请参见 第14.6.3.4节“使缓冲池抗扫描”。


将日志文件调整为适合可靠性和崩溃恢复的大小。
InnoDB 日志文件通常很小,以避免崩溃后的长时间启动。

MySQL 5.5中引入的优化加速了崩溃恢复过程的某些步骤 。
尤其是,由于改进了内存管理算法,扫描 重做日志和应用重做日志速度更快。

如果为了避免很长的启动时间而将您的日志文件人为保留较小,现在可以考虑增加日志文件大小以减少由于重做日志记录的回收而发生的I / O。


为InnoDB缓冲池配置实例的大小和数量, 对于具有多千兆字节缓冲池的系统尤为重要。
请参见 第14.6.3.3节“配置多个缓冲池实例”。


增加并发事务的最大数量,这极大地提高了最繁忙数据库的可伸缩性。
请参见第14.4.8节“撤消日志”。


将清除操作(一种垃圾收集)移动到后台线程中。
请参见 第14.6.11节“配置InnoDB清除计划”。

要有效测量此设置的结果,请首先调整其他I / O相关和线程相关的配置设置。


减少InnoDB并发线程之间的交换量 ,以便繁忙服务器上的SQL操作不会排队并形成“ 交通拥堵 ”。

为该innodb_thread_concurrency 选项设置一个值, 对于高性能的现代系统,最高可达约32。

增加该innodb_concurrency_tickets 选项的值 ,通常为5000左右。

这些选项的组合设置了线程数量的上限 InnoDB 在任何时候都可以进行处理,并且允许每个线程在被换出之前做大量的工作,
以便等待的线程数量保持低,并且操作可以在没有过度上下文切换的情况下完成。

8.5.10为具有多个表的系统优化InnoDB

1
2
3
4
5
6
7
8
9
10
如果您已配置 的非持久性优化统计(非默认配置), InnoDB计算指标 基数值表中的第一次表,启动后访问,而不是在表中存储这些值。

在将数据分割成多个表的系统上,此步骤可能会花费大量时间。
由于此开销仅适用于初始表打开操作,要“ 预热 ” 表供以后使用,
请在启动后立即通过发出诸如“>”之类的语句来访问它。
SELECT 1 FROM tbl_name LIMIT 1

优化器统计信息默认保存到磁盘,由innodb_stats_persistent 配置选项启用 。
有关持久化优化器统计信息的信息,
请参见 第14.6.12.1节“配置持久性优化器统计信息参数”。