恒富网

8.4 优化数据库结构

8.4.1 优化数据大小

8.4.1 优化数据大小

设计您的表以最小化它们在磁盘上的空间。这可以通过减少写入磁盘和从磁盘读取的数据量来带来巨大的改进。较小的表通常需要较少的主内存，而它们的内容在查询执行期间被主动处理。表数据的任何空间减少也会导致可以更快处理的更小的索引。

MySQL 支持许多不同的存储引擎（表类型）和行格式。对于每个表，您可以决定使用哪种存储和索引方法。为您的应用程序选择合适的表格格式可以大大提高性能。请参阅 第 14 章，InnoDB 存储引擎和 第 15 章，替代存储引擎。

通过使用此处列出的技术，您可以获得更好的表性能并最小化存储空间：

表格列

行格式

索引

加入

正常化

表格列

尽可能使用最有效（最小）的数据类型。MySQL 有许多专门的类型可以节省磁盘空间和内存。例如，尽可能使用较小的整数类型来获得较小的表。 MEDIUMINT通常是一个更好的选择，而不是INT因为 MEDIUMINT列使用的空间减少了 25%。

NOT NULL如果可能，请 声明列。通过更好地使用索引并消除测试每个值是否为NULL. 您还可以节省一些存储空间，每列一位。如果您确实需要NULL表中的值，请使用它们。只需避免允许 NULL在每一列中都有值的默认设置。

行格式

InnoDBDYNAMIC默认情况下使用行格式创建表 。要使用除 之外的行格式，请在or语句 中DYNAMIC配置 innodb_default_row_format或显式指定ROW_FORMAT选项。CREATE TABLEALTER TABLE

紧凑的行格式系列，包括 COMPACT、DYNAMIC和COMPRESSED，以增加某些操作的 CPU 使用为代价来减少行存储空间。如果您的工作负载是典型的受缓存命中率和磁盘速度限制的工作负载，它可能会更快。如果是受 CPU 速度限制的罕见情况，它可能会更慢。

CHAR当使用可变长度字符集（例如 utf8mb3or ）时 ，紧凑的行格式系列还优化 了列存储utf8mb4。与ROW_FORMAT=REDUNDANT, 占用× 字符集的最大字节长度。许多语言可以主要使用单字节 字符编写，因此固定的存储长度通常会浪费空间。使用紧凑的行格式系列，分配可变数量的存储空间，范围 为 CHAR(N)Nutf8InnoDBNN× 通过去除尾随空格，这些列的字符集的最大字节长度。最小存储长度是 N字节，以便在典型情况下进行就地更新。有关更多信息，请参阅 第 14.11 节，“InnoDB 行格式”。

要通过以压缩形式存储表数据来进一步最小化空间， 请ROW_FORMAT=COMPRESSED在创建 InnoDB表时指定，或 在现有 表上运行myisampack命令。MyISAM（InnoDB压缩表是可读可写的，而MyISAM压缩表是只读的。）

对于MyISAM表，如果您没有任何可变长度列（VARCHAR、 TEXT或 BLOB列），则使用固定大小的行格式。这更快，但可能会浪费一些空间。请参阅第 15.2.3 节，“MyISAM 表存储格式”。即使您有VARCHAR带有CREATE TABLE选项 的列，您也可以提示您希望有固定长度的行ROW_FORMAT=FIXED。

索引

表的主索引应尽可能短。这使得每一行的识别变得容易和高效。对于InnoDB表，主键列在每个二级索引条目中都是重复的，因此如果您有许多二级索引，短主键可以节省大量空间。

仅创建提高查询性能所需的索引。索引有利于检索，但会减慢插入和更新操作。如果您主要通过搜索列组合来访问表，请在它们上创建单个复合索引，而不是为每列创建单独的索引。索引的第一部分应该是最常用的列。如果从表中选择时总是使用很多列，则索引中的第一列应该是重复次数最多的列，以获得更好的索引压缩。

如果很可能一个长字符串列的前几个字符有唯一的前缀，最好只索引这个前缀，使用 MySQL 支持在列的最左边创建索引（参见第 13.1.14 节，“创建索引语句”）。更短的索引更快，不仅因为它们需要更少的磁盘空间，还因为它们还可以在索引缓存中提供更多的命中，从而减少磁盘查找。请参见 第 5.1.1 节，“配置服务器”。

加入

在某些情况下，将经常扫描的表拆分为两个可能是有益的。如果它是动态格式的表，则尤其如此，并且可以使用较小的静态格式表，以便在扫描表时找到相关行。

在具有相同数据类型的不同表中声明具有相同信息的列，以加快基于相应列的连接。

保持列名简单，以便您可以在不同的表中使用相同的名称并简化连接查询。例如，在名为 的表中customer，使用列名name代替 customer_name。要使您的名称可移植到其他 SQL 服务器，请考虑将它们保持在 18 个字符以内。

正常化

通常，尽量保持所有数据非冗余（观察数据库理论中称为 第三范式的内容）。不要重复名称和地址等冗长的值，而是为它们分配唯一的 ID，根据需要在多个较小的表中重复这些 ID，并通过引用 join 子句中的 ID 在查询中连接表。

如果速度比磁盘空间和保存多个数据副本的维护成本更重要，例如在您分析大表中的所有数据的商业智能场景中，您可以放宽规范化规则，复制信息或创建汇总表以获得更多的速度。

8.4.2 优化 MySQL 数据类型

8.4.2 优化 MySQL 数据类型

8.4.2.1 优化数值数据

8.4.2.1 优化数值数据

对于可以表示为字符串或数字的唯一 ID 或其他值，首选数字列而不是字符串列。由于大数值可以存储在比相应字符串更少的字节中，因此传输和比较它们更快并且占用更少的内存。

如果您使用数字数据，在许多情况下，从数据库（使用实时连接）访问信息比访问文本文件要快。数据库中的信息可能以比文本文件更紧凑的格式存储，因此访问它涉及更少的磁盘访问。您还可以在应用程序中保存代码，因为您可以避免解析文本文件来查找行和列边界。

8.4.2.2 优化字符和字符串类型

8.4.2.2 优化字符和字符串类型

对于字符和字符串列，请遵循以下准则：

当您不需要特定于语言的整理功能时，使用二进制整理顺序进行快速比较和排序操作。您可以使用 BINARY运算符在特定查询中使用二进制排序规则。

在比较不同列的值时，尽可能使用相同的字符集和排序规则声明这些列，以避免在运行查询时进行字符串转换。

对于大小小于 8KB 的列值，请使用 binary VARCHAR而不是 BLOB. GROUP BY and子句可以生成临时表，如果原表不包含任何 列 ，ORDER BY这些临时表可以使用 存储引擎。MEMORYBLOB

如果表包含字符串列，例如名称和地址，但许多查询不检索这些列，请考虑将字符串列拆分到单独的表中，并在必要时使用带有外键的连接查询。当 MySQL 从行中检索任何值时，它会读取包含该行的所有列（可能还有其他相邻行）的数据块。保持每行较小，只包含最常用的列，可以让更多的行适合每个数据块。这种紧凑的表减少了常见查询的磁盘 I/O 和内存使用。

当您使用随机生成的值作为InnoDB表中的主键时，如果可能，请在其前面加上一个升序值，例如当前日期和时间。当连续的主值物理存储在彼此附近时，InnoDB可以更快地插入和检索它们。

请参阅第 8.4.2.1 节，“优化数字数据”，了解为什么数字列通常优于等效字符串列的原因。

8.4.2.3 优化 BLOB 类型

8.4.2.3 优化 BLOB 类型

存储包含文本数据的大型 blob 时，请考虑先对其进行压缩。InnoDB当整个表由或压缩时，请勿使用此技术 MyISAM。

对于具有多个列的表，为了减少不使用 BLOB 列的查询的内存需求，请考虑将 BLOB 列拆分为单独的表，并在需要时使用连接查询引用它。

由于检索和显示 BLOB 值的性能要求可能与其他数据类型非常不同，因此您可以将特定于 BLOB 的表放在不同的存储设备甚至单独的数据库实例上。例如，检索 BLOB 可能需要大的顺序磁盘读取，这更适合传统硬盘驱动器而不是 SSD 设备。

请参阅第 8.4.2.2 节，“针对字符和字符串类型进行优化”，了解为什么二进制VARCHAR列有时比等效的 BLOB 列更可取的原因。

您可以将列值的散列存储在单独的列中，索引该列，然后在查询中测试散列值，而不是针对非常长的文本字符串测试相等性。（使用MD5()or CRC32()函数产生哈希值。）由于哈希函数可以为不同的输入产生重复的结果，你仍然 在查询中包含一个子句来防止错误匹配；性能优势来自于散列值的更小、更容易扫描的索引。 AND blob_column = long_string_value

8.4.2.4 使用过程分析

8.4.2.4 使用过程分析

ANALYSE([max_elements[,max_memory]])

笔记

PROCEDURE ANALYSE()自 MySQL 5.7.18 起已弃用，并在 MySQL 8.0 中删除。

ANALYSE()检查查询结果并返回对结果的分析，为每列建议可能有助于减小表大小的最佳数据类型。要获得此分析，请附加PROCEDURE ANALYSE到 SELECT语句的末尾：

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])

例如：

SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);

结果显示了查询返回值的一些统计信息，并为列提出了最佳数据类型。这对于检查现有表或导入新数据后很有帮助。您可能需要为参数尝试不同的设置，以便在不合适时 PROCEDURE ANALYSE()不建议 数据类型。ENUM

参数是可选的，用法如下：

max_elementsANALYSE()（默认 256）是每列注意到的不同值的最大数量 。这用于ANALYSE()检查最佳数据类型是否应该是 type ENUM；如果有多个max_elements不同的值，则ENUM不是建议的类型。

max_memoryANALYSE()（默认 8192）是在尝试查找所有不同值时应为每列分配 的最大内存量 。

PROCEDURE语句中不允许 有子句UNION。

8.4.3 多表优化

8.4.3 多表优化

8.4.3.1 MySQL 如何打开和关闭表

8.4.3.1 MySQL 如何打开和关闭表

当您执行mysqladmin status 命令时，您应该看到如下内容：

Uptime: 426 Running threads: 1 Questions: 11082
Reloads: 1 Open tables: 12

Open tables如果您的表少于 12 个，那么 12 的值可能会有些令人费解。

MySQL 是多线程的，因此可能有许多客户端同时对给定表发出查询。为了尽量减少在同一个表上具有不同状态的多个客户端会话的问题，该表由每个并发会话独立打开。这会使用额外的内存，但通常会提高性能。对于MyISAM表，每个打开表的客户端的数据文件都需要一个额外的文件描述符。（相比之下，索引文件描述符在所有会话之间共享。）

table_open_cache和 系统变量影响服务器保持打开 的max_connections最大文件数。如果您增加这些值中的一个或两个，您可能会遇到操作系统对每个进程打开文件描述符的数量施加的限制。许多操作系统允许您增加打开文件的限制，尽管方法因系统而异。请查阅您的操作系统文档以确定是否可以增加限制以及如何增加限制。

table_open_cache相关max_connections。例如，对于 200 个并发运行的连接，将表缓存大小指定为至少，其中 是您执行的任何查询中每个连接的最大表数。您还必须为临时表和文件保留一些额外的文件描述符。 200 * NN

确保您的操作系统可以处理该设置所暗示的打开文件描述符的数量 table_open_cache。如果 table_open_cache设置得太高，MySQL 可能会耗尽文件描述符并出现拒绝连接或无法执行查询等症状。

还要考虑到MyISAM 存储引擎需要两个文件描述符用于每个唯一的打开表。对于分区MyISAM表，打开表的每个分区都需要两个文件描述符。（当MyISAM打开一个分区表时，它会打开这个表的每个分区，无论给定的分区是否实际使用。请参阅 MyISAM 和分区文件描述符的使用。）要增加 MySQL 可用的文件描述符的数量，请设置open_files_limit 系统变量。请参阅 第 B.3.2.16 节，“找不到文件和类似错误”。

打开表的缓存保持在 table_open_cache条目级别。服务器在启动时自动调整缓存大小。要显式设置大小，请 table_open_cache在启动时设置系统变量。MySQL 可能会暂时打开比这更多的表来执行查询，如本节后面所述。

MySQL 在以下情况下关闭未使用的表并将其从表缓存中删除：

当缓存已满并且线程尝试打开不在缓存中的表时。

当缓存包含多个 table_open_cache条目并且缓存中的表不再被任何线程使用时。

发生表刷新操作时。当有人发出FLUSH TABLES语句或执行 mysqladmin flush-tables或 mysqladmin refresh命令时，就会发生这种情况。

当表缓存填满时，服务器使用以下过程来定位要使用的缓存条目：

当前未使用的表被释放，从最近最少使用的表开始。

如果必须打开一个新表，但缓存已满，无法释放任何表，则根据需要临时扩展缓存。当缓存处于临时扩展状态并且表从使用状态变为未使用状态时，表将关闭并从缓存中释放。

MyISAM为每个并发访问打开 一个表。这意味着如果两个线程访问同一个表或一个线程在同一个查询中访问该表两次（例如，通过将表连接到自身），则需要打开表两次。每个并发打开都需要表缓存中的一个条目。任何表的第一次打开 MyISAM需要两个文件描述符：一个用于数据文件，一个用于索引文件。表的每一次额外使用只需要一个数据文件的文件描述符。索引文件描述符在所有线程之间共享。

如果您正在使用该语句打开一个表，则会为该线程分配一个专用的表对象。此表对象不被其他线程共享，并且在线程调用或线程终止之前不会关闭。发生这种情况时，表会被放回表缓存中（如果缓存未满）。请参阅 第 13.2.4 节，“HANDLER 语句”。 HANDLER tbl_name OPENHANDLER tbl_name CLOSE

要确定您的表缓存是否太小，请检查 Opened_tables状态变量，该变量表示自服务器启动以来的开表操作次数：

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+

如果该值非常大或快速增加，即使您没有发出很多FLUSH TABLES语句， 也要table_open_cache在服务器启动时增加该值。

8.4.3.2 在同一个数据库中创建多个表的缺点

8.4.3.2 在同一个数据库中创建多个表的缺点

如果MyISAM同一个数据库目录下有很多表，打开、关闭、创建操作都很慢。如果您SELECT 在许多不同的表上执行语句，当表缓存已满时会有一点开销，因为对于必须打开的每个表，必须关闭另一个表。您可以通过增加表缓存中允许的条目数来减少此开销。

保持单个查询快速的一些技术涉及跨多个表拆分数据。当表的数量达到数千甚至数百万时，处理所有这些表的开销成为新的性能考虑因素。

8.4.4 MySQL内部临时表的使用

8.4.4 MySQL内部临时表的使用

在某些情况下，服务器在处理语句时会创建内部临时表。用户无法直接控制何时发生这种情况。

服务器在以下条件下创建临时表：

语句的评估UNION ，有一些例外情况稍后描述。

评估某些视图，例如使用 TEMPTABLE算法 UNION、或聚合的视图。

派生表的评估（参见 第 13.2.10.8 节，“派生表”）。

为子查询或半连接物化创建的表（请参阅 第 8.2.2 节，“优化子查询、派生表和视图引用”）。

对包含一个ORDER BY子句和一个不同GROUP BY子句的语句的评估，或者对于其ORDER BYorGROUP BY包含来自连接队列中第一个表以外的表的列的语句。

评估与DISTINCT结合 ORDER BY可能需要一个临时表。

对于使用SQL_SMALL_RESULT 修饰符的查询，MySQL 使用内存中的临时表，除非查询还包含需要磁盘存储的元素（稍后描述）。

为了评估 INSERT ... SELECT从同一个表中选择和插入的语句，MySQL 创建一个内部临时表来保存来自 的行 SELECT，然后将这些行插入到目标表中。请参阅 第 13.2.5.1 节，“INSERT ... SELECT 语句”。

评估多表 UPDATE语句。

GROUP_CONCAT() orCOUNT(DISTINCT) 表达式 的求值。

要确定语句是否需要临时表，请使用 EXPLAIN并检查 Extra列以查看它是否显示 Using temporary（请参阅 第 8.8.1 节，“使用 EXPLAIN 优化查询”）。EXPLAIN 不一定说Using temporary派生或物化临时表。

一些查询条件阻止使用内存中的临时表，在这种情况下，服务器使用磁盘表来代替：

表中存在一个BLOB或 TEXT列。这包括具有字符串值的用户定义变量，因为它们被视为 列，具体取决于它们的值是二进制字符串还是非二进制字符串 BLOB。 TEXT

列表中存在最大长度大于 512 的任何字符串列（二进制字符串为字节，非二进制字符串为字符）SELECT，如果使用UNION或 UNION ALL 。

SHOW COLUMNSand DESCRIBE语句 用作某些列 的BLOB类型，因此用于结果的临时表是磁盘表。

服务器不对 UNION满足特定条件的语句使用临时表。相反，它从临时表创建中只保留执行结果列类型转换所需的数据结构。该表没有完全实例化，没有行被写入或读取；行直接发送到客户端。结果是减少了内存和磁盘需求，并且在将第一行发送到客户端之前的延迟更小，因为服务器不需要等到最后一个查询块被执行。EXPLAIN和优化器跟踪输出反映了这种执行策略： UNION RESULT查询块不存在，因为该块对应于从临时表中读取的部分。

这些条件有资格在UNION没有临时表的情况下进行评估：

工会是UNION ALL，不是 UNION或UNION DISTINCT。

没有全局ORDER BY条款。

联合不是语句的顶级查询块 {INSERT | REPLACE} ... SELECT ... 。

内部临时表存储引擎

内部临时表可以保存在内存中并由存储引擎处理，也可以由存储 MEMORY引擎存储在磁盘上。 InnoDBMyISAM

如果内部临时表创建为内存表但变得太大，MySQL 会自动将其转换为磁盘表。内存中临时表的最大大小由 tmp_table_sizeor max_heap_table_size值定义，以较小者为准。这与 使用 .MEMORY显式创建的表 不同CREATE TABLE。对于这样的表，只有max_heap_table_size 变量决定了表可以增长到多大，并且没有转换为磁盘格式。

该 internal_tmp_disk_storage_engine 变量定义了服务器用来管理磁盘内部临时表的存储引擎。允许的值为 INNODB（默认值）和 MYISAM。

笔记

使用 internal_tmp_disk_storage_engine=INNODB时，生成超出 InnoDB行或列限制的磁盘内部临时表的查询会返回Row size too large或Too many columns 错误。解决方法是设置 internal_tmp_disk_storage_engine 为MYISAM.

当在内存或磁盘上创建内部临时表时，服务器会增加该 Created_tmp_tables值。在磁盘上创建内部临时表时，服务器会增加该 Created_tmp_disk_tables 值。如果在磁盘上创建了太多内部临时表，请考虑增加 tmp_table_size和 max_heap_table_size设置。

内部临时表存储格式

内存中的临时表由 MEMORY存储引擎管理，它使用固定长度的行格式。VARCHAR和 VARBINARY列值被填充到最大列长度，实际上将它们存储为 CHAR和BINARY列。

InnoDB磁盘上的 临时表由 MyISAM存储引擎管理（取决于 internal_tmp_disk_storage_engine 设置）。两个引擎都使用动态宽度行格式存储临时表。与使用固定长度行的磁盘表相比，列只占用所需的存储空间，从而减少了磁盘 I/O、空间要求和处理时间。

对于最初在内存中创建内部临时表，然后将其转换为磁盘表的语句，跳过转换步骤并开始在磁盘上创建表可能会获得更好的性能。该 big_tables变量可用于强制内部临时表的磁盘存储。

8.4.5 数据库和表的数量限制

8.4.5 数据库和表的数量限制

MySQL 对数据库的数量没有限制。底层文件系统可能对目录的数量有限制。

MySQL 对表的数量没有限制。底层文件系统可能对代表表的文件数量有限制。单独的存储引擎可能会施加特定于引擎的约束。InnoDB允许多达 40 亿张表。

8.4.6 表大小限制

8.4.6 表大小限制

MySQL 数据库的有效最大表大小通常由操作系统对文件大小的限制决定，而不是由 MySQL 内部限制决定。有关操作系统文件大小限制的最新信息，请参阅特定于您的操作系统的文档。

Windows 用户请注意，FAT 和 VFAT (FAT32) 不适合与 MySQL 一起使用。请改用 NTFS。

如果您遇到全表错误，可能有几个原因：

磁盘可能已满。

您正在使用表并且表空间文件InnoDB中的空间已用完。InnoDB最大表空间大小也是表的最大大小。有关表空间大小限制，请参阅 第 14.23 节，“InnoDB 限制”。

对于大于 1TB 的表，一般建议将表分区为多个表空间文件。

您已达到操作系统文件大小限制。例如，您MyISAM在仅支持最大 2GB 文件的操作系统上使用表，并且您已达到数据文件或索引文件的此限制。

您正在使用一个MyISAM表，并且该表所需的空间超出了内部指针大小所允许的范围。MyISAM默认情况下允许数据和索引文件增加到 256TB，但可以将此限制更改为最大允许大小 65,536TB（256 7 - 1 字节）。

如果您需要一个MyISAM大于默认限制的表并且您的操作系统支持大文件，则该CREATE TABLE 语句支持AVG_ROW_LENGTH和 MAX_ROWS选项。请参阅 第 13.1.18 节，“CREATE TABLE 语句”。服务器使用这些选项来确定允许的表有多大。

如果指针大小对于现有表来说太小，您可以更改选项ALTER TABLE以增加表的最大允许大小。请参阅第 13.1.8 节，“ALTER TABLE 语句”。

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

您必须AVG_ROW_LENGTH只为带有BLOB或 TEXT列的表指定；在这种情况下，MySQL 无法仅根据行数优化所需的空间。

要更改 MyISAM表的默认大小限制，请设置 myisam_data_pointer_size，它设置用于内部行指针的字节数。MAX_ROWS 如果不指定该选项，则该值用于设置新表的指针大小。的值 myisam_data_pointer_size 可以是 2 到 7。例如，对于使用动态存储格式的表，值 4 允许表最大为 4GB；值 6 允许表最大为 256TB。使用固定存储格式的表具有更大的最大数据长度。有关存储格式特征，请参阅 第 15.2.3 节，“MyISAM 表存储格式”。

您可以使用以下语句检查最大数据和索引大小：

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

您也可以使用myisamchk -dv /path/to/table-index-file。请参阅 第 13.7.5 节，“SHOW 语句”或第 4.6.3 节，“myisamchk - MyISAM 表维护实用程序”。

解决 MyISAM表文件大小限制的其他方法如下：

如果你的大表是只读的，你可以使用 myisampack来压缩它。 myisampack通常将表格压缩至少 50%，因此实际上您可以拥有更大的表格。myisampack还可以将多个表合并为一个表。请参阅 第 4.6.5 节，“myisampack - 生成压缩的只读 MyISAM 表”。

MySQL 包含一个MERGE库，使您能够处理 MyISAM具有与单个MERGE表相同结构的表的集合。请参阅第 15.7 节，“MERGE 存储引擎”。

您正在使用MEMORY ( HEAP) 存储引擎；在这种情况下，您需要增加 max_heap_table_size系统变量的值。请参阅第 5.1.7 节，“服务器系统变量”。

8.4.7 表列数和行大小的限制

8.4.7 表列数和行大小的限制

本节介绍对表中列数和各行大小的限制。

列数限制

行大小限制

列数限制

MySQL 对每个表有 4096 列的硬性限制，但对于给定的表，有效最大值可能会更少。确切的列限制取决于几个因素：

表的最大行大小限制了列的数量（可能还有大小），因为所有列的总长度不能超过此大小。请参阅 行大小限制。

单个列的存储要求限制了适合给定最大行大小的列数。某些数据类型的存储要求取决于存储引擎、存储格式和字符集等因素。请参阅第 11.7 节，“数据类型存储要求”。

存储引擎可能会施加额外的限制来限制表列数。例如， InnoDB每个表限制为 1017 列。请参阅第 14.23 节，“InnoDB 限制”。有关其他存储引擎的信息，请参阅 第 15 章，替代存储引擎。

每个表都有一个.frm包含表定义的文件。该定义以可能影响表中允许的列数的方式影响此文件的内容。请参阅 .frm 文件结构施加的限制。

行大小限制

给定表的最大行大小由几个因素决定：

MySQL 表的内部表示具有 65,535 字节的最大行大小限制，即使存储引擎能够支持更大的行。 BLOB并且 TEXT列仅对行大小限制贡献 9 到 12 个字节，因为它们的内容与行的其余部分分开存储。

对于 4KB、8KB、16KB 和 32KB设置，适用于本地存储在数据库页面中的数据 的表的最大行大小InnoDB 略小于半页 。例如，对于默认的 16KB页大小innodb_page_size，最大行大小略小于 8KB 。InnoDB对于 64KB 页面，最大行大小略小于 16KB。请参阅 第 14.23 节，“InnoDB 限制”。

如果包含 可变长度列的行超过InnoDB 最大行大小，则InnoDB选择可变长度列用于外部页外存储，直到该行符合InnoDB 行大小限制。对于在页外存储的可变长度列，本地存储的数据量因行格式而异。有关更多信息，请参阅 第 14.11 节，“InnoDB 行格式”。

不同的存储格式使用不同数量的页眉和尾数据，这会影响可用于行的存储量。

有关InnoDB行格式的信息，请参阅第 14.11 节，“InnoDB 行格式”。

有关MyISAM 存储格式的信息，请参阅 第 15.2.3 节，“MyISAM 表存储格式”。

行大小限制示例

InnoDB MySQL 最大行大小限制为 65,535 字节， 在以下MyISAM示例中进行了演示。无论存储引擎如何，都会强制执行该限制，即使存储引擎可能能够支持更大的行。

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),

       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),

       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;

ERROR 1118 (42000): Row size too large. The maximum row size for the used

table type, not counting BLOBs, is 65535. This includes storage overhead,

check the manual. You have to change some columns to TEXT or BLOBs
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

在以下MyISAM示例中，更改列以TEXT 避免 65,535 字节的行大小限制并允许操作成功，因为 BLOB列 TEXT仅对行大小贡献 9 到 12 个字节。

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

该操作对InnoDB 表成功，因为更改列以 TEXT避免 MySQL 65,535 字节的行大小限制，并且InnoDB 可变长度列的页外存储避免了 InnoDB行大小限制。

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

可变长度列的存储包括长度字节，这些字节计入行大小。例如，一 VARCHAR(255) CHARACTER SET utf8mb3列需要两个字节来存储值的长度，因此每个值最多可以占用 767 个字节。

创建表的语句t1 成功，因为列需要 32,765 + 2 字节和 32,766 + 2 字节，这在 65,535 字节的最大行大小内：

mysql> CREATE TABLE t1
       (c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

创建表的语句t2失败，因为虽然列长度在 65,535 字节的最大长度范围内，但需要两个额外的字节来记录长度，这导致行大小超过 65,535 字节：

mysql> CREATE TABLE t2
       (c1 VARCHAR(65535) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

将列长度减少到 65,533 或更少允许语句成功。

mysql> CREATE TABLE t2
       (c1 VARCHAR(65533) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

对于MyISAM表， NULL列需要在行中有额外的空间来记录它们的值是否为 NULL. 每NULL 列多占用一位，四舍五入到最接近的字节。

创建表的语句t3失败，因为除了可变长度列长度字节所需的空间之外，还 MyISAM需要列空间，导致行大小超过 65,535 字节：NULL

mysql> CREATE TABLE t3
       (c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
       ENGINE = MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

有关列存储的信息，请参阅 第 14.11 节，“InnoDB 行格式”。 InnoDB NULL

InnoDB对于 4KB、8KB、16KB 和 32KB 设置，将行大小（对于本地存储在数据库页面中的数据）限制为略小于数据库页面的一半， innodb_page_size 对于 64KB 页面，限制为略小于 16KB。

创建表的语句t4失败，因为定义的列超过了 16KBInnoDB页的行大小限制。

mysql> CREATE TABLE t4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=COMPACT DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using
ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768
bytes is stored inline.

作为数据库设计者，寻找最有效的方式来组织架构、表和列。就像在调整应用程序代码时一样，您可以最大限度地减少 I/O，将相关项目放在一起，并提前计划，以便在数据量增加时保持高性能。从高效的数据库设计开始，团队成员可以更轻松地编写高性能的应用程序代码，并使数据库能够随着应用程序的发展和重写而持续存在。

8.5 优化 InnoDB 表

8.5.1 优化 InnoDB 表的存储布局

8.5.1 优化 InnoDB 表的存储布局

一旦您的数据达到稳定大小，或者正在增长的表增加了数十或数百兆字节，请考虑使用该OPTIMIZE TABLE语句重新组织表并压缩任何浪费的空间。重组的表需要更少的磁盘 I/O 来执行全表扫描。这是一种简单的技术，可以在其他技术（例如提高索引使用率或调整应用程序代码）不实用时提高性能。

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

在InnoDB中，有一个 long PRIMARY KEY（具有 long 值的单个列，或形成 long 复合值的几个列）会浪费大量磁盘空间。行的主键值在指向同一行的所有二级索引记录中重复。（请参阅第 14.6.2.1 节，“聚集索引和二级索引”。）如果您的主键很长，请创建一个AUTO_INCREMENT列作为主键，或者索引一个长VARCHAR列的前缀而不是整个列。

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

当使用COMPACT行格式（默认InnoDB格式）和可变长度字符集（例如 utf8or sjis）时， 列会占用可变数量的空间，但仍至少占用字节。 CHAR(N)N

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

8.5.2 优化 InnoDB 事务管理

8.5.2 优化 InnoDB 事务管理

要优化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-bound 并快速运行，或者杀死服务器并重新启动 innodb_force_recovery=3，如第 14.19.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 只读事务

8.5.3 优化 InnoDB 只读事务

InnoDB可以避免与为已知为只读的事务设置事务 ID（字段）相关的开销。TRX_ID只有可能执行写入操作或 锁定读取的事务（例如 . 消除不必要的事务 ID 可以减少每次查询或数据更改语句构造读取视图时所查询的内部数据结构的大小。 SELECT ... FOR UPDATE

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

事务从 START TRANSACTION READ ONLY语句开始。在这种情况下，尝试对数据库（对于 、 或其他类型的表）进行更改InnoDB会 MyISAM导致错误，并且事务会以只读状态继续：

ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

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

设置开启，autocommit保证事务为单条语句，构成事务的单条语句为“非锁定” SELECT语句。即 a SELECT不使用FOR UPDATEorLOCK IN SHARED MODE 子句。

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

因此，对于诸如报告生成器之类的读取密集型应用程序，您可以通过将查询序列InnoDB 分组到 START TRANSACTION READ ONLYand 中，或者通过 在运行语句之前COMMIT打开设置，或者简单地避免任何数据更改语句穿插在查询中来调整查询序列. autocommitSELECT

有关 START TRANSACTIONand 的信息autocommit，请参阅 第 13.3.1 节，“START TRANSACTION、COMMIT 和 ROLLBACK 语句”。

笔记

符合自动提交、非锁定和只读 (AC-NL-RO) 条件的事务被排除在某些内部 InnoDB数据结构之外，因此不会在 SHOW ENGINE INNODB STATUS输出中列出。

8.5.4 优化 InnoDB 重做日志

8.5.4 优化 InnoDB 重做日志

考虑以下优化重做日志的准则：

使您的重做日志文件变大，甚至与 缓冲池一样大。当 InnoDB重做日志文件写满时，它必须将缓冲池的修改内容写入磁盘中的一个 检查点。小的重做日志文件会导致许多不必要的磁盘写入。尽管过去大的重做日志文件会导致恢复时间过长，但现在恢复速度要快得多，您可以放心地使用大的重做日志文件。

innodb_log_file_size 使用和 innodb_log_files_in_group 配置选项 配置重做日志文件的大小和数量。有关修改现有重做日志文件配置的信息，请参阅 更改 InnoDB 重做日志文件的数量或大小。

考虑增加 日志缓冲区的大小。大型日志缓冲区使大型 事务无需在事务提交之前将日志写入磁盘即可运行。因此，如果您有更新、插入或删除许多行的事务，则使日志缓冲区更大可以节省磁盘 I/O。innodb_log_buffer_size 使用配置选项 配置日志缓冲区大小 。

配置 innodb_log_write_ahead_size 配置选项以避免“ read-on-write ”。此选项定义重做日志的预写块大小。设置 innodb_log_write_ahead_size 为匹配操作系统或文件系统缓存块大小。当重做日志块由于重做日志的预写块大小与操作系统或文件系统缓存块大小不匹配而未完全缓存到操作系统或文件系统时，会发生写时读取。

的有效值 是日志文件块大小 (2 n )innodb_log_write_ahead_size 的倍数。最小值是日志文件块大小 (512)。指定最小值时不会发生预写。最大值等于该 值。如果您指定的值 大于该 值，则 设置将被截断为该 值。 InnoDBInnoDBinnodb_page_sizeinnodb_log_write_ahead_sizeinnodb_page_sizeinnodb_log_write_ahead_sizeinnodb_page_size

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

8.5.5 InnoDB 表的批量数据加载

8.5.5 InnoDB 表的批量数据加载

这些性能提示是对第 8.2.4.1 节“优化 INSERT 语句” 中快速插入的一般准则的补充。

将数据导入InnoDB时，请关闭自动提交模式，因为它会为每次插入执行日志刷新到磁盘。要在导入操作期间禁用自动提交，请使用 SET autocommitand COMMIT语句将其括起来：

SET autocommit=0;

... SQL import statements ...

COMMIT;

mysqldump选项 创建 的--opt转储文件可以快速导入到表中，即使没有用and 语句 InnoDB 包装它们 。SET autocommitCOMMIT

如果您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 yourtable VALUES (1,2), (5,5), ...;

此技巧适用于插入任何表，而不仅仅是 InnoDB表。

当对具有自动增量列的表进行批量插入时，设置 innodb_autoinc_lock_mode为 2 而不是默认值 1。有关详细信息，请参阅 第 14.6.1.6 节，“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 '',

text mediumtext NOT NULL,

PRIMARY KEY (`FTS_DOC_ID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);

将数据加载到表中。

FULLTEXT加载数据后 创建索引。

笔记

在创建表时添加FTS_DOC_ID列时，请确保在 FTS_DOC_ID更新 FULLTEXT索引列时更新列，因为FTS_DOC_ID必须随着每个 INSERT或 单调增加UPDATE。如果您选择不添加FTS_DOC_IDat 表创建时间并InnoDB为您管理 DOC ID，请在下次调用时InnoDB将其添加 FTS_DOC_ID为隐藏列。CREATE FULLTEXT INDEX但是，这种方法需要重新构建表，这会影响性能。

8.5.6 优化 InnoDB 查询

8.5.6 优化 InnoDB 查询

要调整表的查询，请InnoDB在每个表上创建一组适当的索引。有关详细信息，请参阅 第 8.3.1 节，“MySQL 如何使用索引”。请遵循以下InnoDB索引指南：

因为每个InnoDB表都有一个 主键（无论您是否请求），请为每个表指定一组主键列，这些列用于最重要和时间关键的查询。

不要在主键中指定太多或太长的列，因为这些列值在每个二级索引中都是重复的。当索引包含不必要的数据时，读取这些数据的 I/O 和缓存它的内存会降低服务器的性能和可伸缩性。

不要为每一列创建单独的 二级索引 ，因为每个查询只能使用一个索引。很少测试的列或只有几个不同值的列上的索引可能对任何查询都没有帮助。如果您对同一个表有很多查询，测试不同的列组合，请尝试创建少量的 连接索引而不是大量的单列索引。如果索引包含结果集所需的所有列（称为 覆盖索引），则查询可能完全避免读取表数据。

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

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

8.5.7 优化 InnoDB DDL 操作

8.5.7 优化 InnoDB DDL 操作

可以在线执行对表和索引（、、和语句）CREATE的ALTER许多 DDL 操作。DROP有关详细信息，请参阅第 14.13 节，“InnoDB 和在线 DDL”。

在线 DDL 支持添加二级索引意味着您通常可以通过创建没有二级索引的表，然后在加载数据后添加二级索引来加快创建和加载表及其关联索引的过程。

用于TRUNCATE TABLE清空表格，而不是. 外键约束可以使语句像常规语句一样工作，在这种情况下， 一系列命令 可能是最快的。 DELETE FROM tbl_nameTRUNCATEDELETEDROP TABLECREATE TABLE

因为主键是每个InnoDB表的存储布局不可或缺的一部分，而更改主键的定义涉及重新组织整个表，因此始终将主键设置为 CREATE TABLE语句的一部分，并提前计划，这样您就不需要 ALTER或DROP之后的主键。

8.5.8 优化 InnoDB 磁盘 I/O

8.5.8 优化 InnoDB 磁盘 I/O

如果您遵循 SQL 操作的数据库设计和调优技术的最佳实践，但由于磁盘 I/O 活动繁重，您的数据库仍然很慢，请考虑这些磁盘 I/O 优化。如果 Unixtop工具或 Windows 任务管理器显示您的工作负载的 CPU 使用率低于 70%，则您的工作负载可能是磁盘绑定的。

增加缓冲池大小

当表数据缓存在InnoDB 缓冲池中时，可以通过查询重复访问，而不需要任何磁盘 I/O。innodb_buffer_pool_size 使用该选项指定缓冲池的大小 。此内存区域非常重要，通常建议将 innodb_buffer_pool_size其配置为系统内存的 50% 到 75%。有关更多信息，请参阅第 8.12.4.1 节，“MySQL 如何使用内存”。

调整冲洗方式

fsync()在某些版本的 GNU/Linux 和 Unix 中，使用 Unix调用（ 默认使用）和类似方法将 文件刷新到磁盘InnoDB非常慢。如果数据库写入性能是一个问题，请使用 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 和deadline I/O 调度器。执行基准测试以确定哪个 I/O 调度程序为您的工作负载和环境提供最佳结果。有关更多信息，请参阅 第 14.8.7 节，“在 Linux 上使用异步 I/O”。

在 x86_64 架构的 Solaris 10 上使用直接 I/O

InnoDB在 Solaris 10 for x86_64 架构 (AMD Opteron) 上 使用存储引擎时，对InnoDB相关文件使用直接 I/O 以避免性能下降InnoDB。要对用于存储相关文件的整个 UFS 文件系统使用直接 I/O，请使用 选项InnoDB挂载它 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使用具有较大 价值 的存储引擎时，在原始设备或单独的直接 I/O UFS 上使用数据文件和日志文件进行基准测试文件系统，使用前面描述的 mount 选项。（如果您想要日志文件的直接 I/O，则必须使用 mount 选项而不是设置 。） Veritas 文件系统 VxFS 的用户应该使用 mount 选项。 innodb_buffer_pool_sizeInnoDBforcedirectioinnodb_flush_methodconvosync=direct

不要将其他 MySQL 数据文件（例如用于 MyISAM表的数据文件）放在直接 I/O 文件系统上。可执行文件或库不得放在直接 I/O 文件系统上。

使用额外的存储设备

额外的存储设备可用于设置 RAID 配置。有关相关信息，请参阅 第 8.12.2 节，“优化磁盘 I/O”。

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

第 14.8.1 节，“InnoDB 启动配置”

第 14.6.1.2 节，“在外部创建表”

创建通用表空间

第 14.6.1.4 节，“移动或复制 InnoDB 表”

考虑非旋转存储

非循环存储通常为随机 I/O 操作提供更好的性能；和用于顺序 I/O 操作的旋转存储。在旋转和非旋转存储设备上分发数据和日志文件时，请考虑主要在每个文件上执行的 I/O 操作的类型。

面向随机 I/O 的文件通常包括 file-per-table 和通用表空间数据文件、 撤消表空间 文件和 临时表空间文件。面向顺序 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 设备通常具有 4KB 的扇区大小。一些较新的设备具有 16KB 的扇区大小。默认InnoDB 页面大小为 16KB。使页面大小接近存储设备块大小可以最大限度地减少重写到磁盘的未更改数据量。

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 checkpointInnoDB 小于日志文件 总大小的 7/8 或理想情况下小于 6/8 。

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

您可以通过在支持原子写入的 Fusion-io 设备上存储系统表空间文件（ “ ibdata 文件” ） 来利用双写缓冲区相关的 I/O 优化。在这种情况下，双写缓冲 ( innodb_doublewrite) 被自动禁用，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 配置变量

8.5.9 优化 InnoDB 配置变量

不同的设置最适合具有轻量、可预测负载的服务器，而不是始终运行接近满负荷或经历高活动峰值的服务器。

由于InnoDB存储引擎会自动执行许多优化，因此许多性能调整任务都涉及监控以确保数据库运行良好，并在性能下降时更改配置选项。有关详细性能监控 的信息，请参阅 第 14.17 节，“InnoDB 与 MySQL 性能模式的集成” 。InnoDB

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

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

控制缓存更改数据的数据更改操作类型 InnoDB，以避免频繁的小磁盘写入。请参阅 配置更改缓冲。因为默认是缓冲所有类型的数据更改操作，所以只有在需要减少缓冲量时才更改此设置。

innodb_adaptive_hash_index 使用该选项 打开和关闭自适应哈希索引功能 。有关详细信息，请参阅第 14.5.3 节，“自适应哈希索引”。您可以在异常活动期间更改此设置，然后将其恢复为原始设置。

InnoDB如果上下文切换是瓶颈，则对处理 的并发线程数设置限制 。请参阅 第 14.8.5 节，“为 InnoDB 配置线程并发”。

InnoDB控制其预读操作 的预取量 。当系统有未使用的 I/O 容量时，更多的预读可以提高查询的性能。过多的预读可能会导致重负载系统的性能周期性下降。请参阅 第 14.8.3.4 节，“配置 InnoDB 缓冲池预取（预读）”。

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

InnoDB控制在后台执行 多少 I/O 。请参阅 第 14.8.8 节，“配置 InnoDB I/O 容量”。如果您观察到性能周期性下降，您可能会缩减此设置。

控制确定何时 InnoDB执行某些类型的后台写入的算法。请参阅 第 14.8.3.5 节，“配置缓冲池刷新”。该算法适用于某些类型的工作负载，但不适用于其他类型的工作负载，因此如果您观察到性能周期性下降，可能会关闭此设置。

利用多核处理器及其高速缓存配置，最大限度地减少上下文切换的延迟。请参见 第 14.8.9 节，“配置自旋锁轮询”。

防止表扫描等一次性操作干扰 InnoDB缓冲区缓存中存储的频繁访问的数据。请参阅 第 14.8.3.3 节，“使缓冲池扫描抗性”。

将日志文件调整为对可靠性和崩溃恢复有意义的大小。InnoDB 日志文件通常保持较小，以避免崩溃后启动时间过长。MySQL 5.5 中引入的优化加速了崩溃 恢复过程的某些步骤。特别是， 由于内存管理算法的改进，扫描重做日志和应用重做日志更快。如果您人为地保持日志文件较小以避免启动时间过长，您现在可以考虑增加日志文件大小以减少由于重做日志记录回收而发生的 I/O。

为缓冲池配置实例的大小和数量， InnoDB对于具有数 GB 缓冲池的系统尤其重要。请参阅 第 14.8.3.2 节，“配置多个缓冲池实例”。

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

将清除操作（一种垃圾收集）移动到后台线程中。请参见 第 14.8.10 节，“清除配置”。要有效测量此设置的结果，请先调整其他 I/O 相关和线程相关的配置设置。

减少 InnoDB并发线程之间的切换量，使繁忙服务器上的 SQL 操作不会排队并形成“交通堵塞”。为该 innodb_thread_concurrency 选项设置一个值，对于高性能现代系统，最高约为 32。增加 innodb_concurrency_tickets 选项的值，通常增加到 5000 左右。这种选项组合设置了线程数的上限 InnoDB在任何时候处理，并允许每个线程在被换出之前做大量的工作，这样等待线程的数量就会保持在低水平，并且操作可以在没有过多上下文切换的情况下完成。

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

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

如果您配置 了非持久优化器统计信息（非默认配置）， 则在启动后第一次访问该表时InnoDB计算该表的索引 基数值，而不是将这些值存储在表中。在将数据划分为多个表的系统上，此步骤可能会花费大量时间。由于此开销仅适用于初始表打开操作，因此要“预热” 表以供以后使用，请在启动后立即通过发出诸如. SELECT 1 FROM tbl_name LIMIT 1

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

InnoDB是 MySQL 客户通常在可靠性和并发性很重要的生产数据库中使用的存储引擎。 InnoDB是 MySQL 中默认的存储引擎。本节介绍如何优化 InnoDB表的数据库操作。

8.6 MyISAM 表的优化

8.6.1 优化 MyISAM 查询

8.6.1 优化 MyISAM 查询

一些加快 MyISAM表查询速度的一般技巧：

为了帮助 MySQL 更好地优化查询， 请在加载数据后对表使用ANALYZE TABLE或运行 myisamchk --analyze 。这会更新每个索引部分的值，该值指示具有相同值的平均行数。（对于唯一索引，这始终是 1。）当您基于非常量表达式连接两个表时，MySQL 使用它来决定选择哪个索引。您可以通过使用 和检查值来检查表分析的结果。myisamchk --description --verbose显示索引分布信息。 SHOW INDEX FROM tbl_nameCardinality

要根据索引对索引和数据进行排序，请使用 myisamchk --sort-index --sort-records=1 （假设您要对索引 1 进行排序）。如果您有一个唯一索引，您希望根据该索引按顺序读取所有行，那么这是一种加快查询速度的好方法。第一次以这种方式对大表进行排序时，可能需要很长时间。

尽量避免对频繁更新SELECT 的表进行复杂查询MyISAM，避免由于读写器争用而导致的表锁定问题。

MyISAM支持并发插入：如果一个表在数据文件的中间没有空闲块，你可以INSERT在其他线程从表中读取的同时将新行插入其中。如果能够做到这一点很重要，请考虑以避免删除行的方式使用该表。另一种可能性是OPTIMIZE TABLE在您从表中删除大量行后运行以对表进行碎片整理。concurrent_insert通过设置变量可以更改此行为 。您可以强制追加新行（因此允许并发插入），即使在已删除行的表中也是如此。请参阅第 8.11.3 节，“并发插入”。

对于MyISAM频繁更改的表，请尽量避免使用所有可变长度列（VARCHAR、 BLOB和 TEXT）。如果该表甚至包含单个可变长度列，则该表使用动态行格式。请参阅第 15 章，替代存储引擎。

仅仅因为行变大而将表拆分为不同的表通常是没有用的。在访问一行时，最大的性能损失是查找行的第一个字节所需的磁盘寻道。找到数据后，大多数现代磁盘可以以足够快的速度读取整个行，以供大多数应用程序使用。拆分表会产生明显差异的唯一情况是，如果它是 MyISAM使用动态行格式的表，您可以将其更改为固定的行大小，或者如果您经常需要扫描表但不需要大多数列. 请参阅第 15 章，替代存储引擎。

如果您通常按顺序检索行， 请使用。通过在对表进行大量更改后使用此选项，您可以获得更高的性能。 ALTER TABLE ... ORDER BY expr1, expr2, ...expr1, expr2, ...

如果您经常需要根据大量行的信息计算计数等结果，则最好引入一个新表并实时更新计数器。以下表格的更新非常快：

UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;

当您使用 MySQL 存储引擎时，这一点非常重要，例如MyISAM只有表级锁定（多个读取器和单个写入器）。这也为大多数数据库系统提供了更好的性能，因为在这种情况下，行锁定管理器的工作较少。

定期使用OPTIMIZE TABLE 以避免动态格式 MyISAM表产生碎片。请参阅 第 15.2.3 节，“MyISAM 表存储格式”。

MyISAM使用 table 选项 声明表DELAY_KEY_WRITE=1会使索引更新更快，因为在表关闭之前它们不会刷新到磁盘。myisam_recover_options 不利的一面是，如果在这样的表打开时某些东西杀死了服务器，您必须通过设置系统变量运行服务器或 在重新启动服务器之前运行myisamchk来确保该表正常 。（但是，即使在这种情况下，使用 也不应该丢失任何东西DELAY_KEY_WRITE，因为关键信息总是可以从数据行中生成。）

MyISAM字符串在索引 中自动压缩前缀和结束空间。请参阅 第 13.1.14 节，“CREATE INDEX 语句”。

您可以通过在应用程序中缓存查询或答案，然后一起执行许多插入或更新来提高性能。在此操作期间锁定表可确保索引缓存仅在所有更新后刷新一次。您还可以利用 MySQL 的查询缓存来实现类似的结果；请参阅 第 8.10.3 节，“MySQL 查询缓存”。

8.6.2 MyISAM 表的批量数据加载

8.6.2 MyISAM 表的批量数据加载

这些性能提示是对第 8.2.4.1 节“优化 INSERT 语句” 中快速插入的一般准则的补充。

对于表，如果数据文件中间没有删除的行MyISAM，您可以在语句运行的同时使用并发插入来添加行 。SELECT请参阅第 8.11.3 节，“并发插入”。

通过一些额外的工作， 当表有许多索引时，可以使表LOAD DATA运行得更快。MyISAM使用以下过程：

执行FLUSH TABLES 语句或mysqladmin flush-tables命令。

使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name 删除表的所有索引使用。

使用 将数据插入表中 LOAD DATA。这不会更新任何索引，因此非常快。

如果您以后只打算从表中读取，请使用myisampack对其进行压缩。请参见 第 15.2.3.3 节，“压缩表特性”。

使用myisamchk -rq /path/to/db/tbl_name 重新创建索引。这会在将索引树写入磁盘之前在内存中创建索引树，这比更新索引要快得多，LOAD DATA因为它避免了大量的磁盘寻道。生成的索引树也是完美平衡的。

执行FLUSH TABLES 语句或mysqladmin flush-tables命令。

LOAD DATAMyISAM如果插入数据的表为空，则会自动执行上述优化 。自动优化和显式使用过程之间的主要区别在于，您可以让 myisamchkLOAD DATA为索引创建分配比您希望服务器在执行语句 时为重新创建索引分配更多的临时内存 。

MyISAM您还可以使用以下语句而不是myisamchk 禁用或启用表的非唯一索引 。如果使用这些语句，则可以跳过 FLUSH TABLES操作：

ALTER TABLE tbl_name DISABLE KEYS;
ALTER TABLE tbl_name ENABLE KEYS;

要加快INSERT对非事务性表使用多个语句执行的操作，请锁定您的表：

LOCK TABLES a WRITE;

INSERT INTO a VALUES (1,23),(2,34),(4,33);

INSERT INTO a VALUES (8,26),(6,29);

...

UNLOCK TABLES;

INSERT这有利于性能，因为索引缓冲区仅在所有语句完成 后刷新到磁盘一次 。通常，索引缓冲区刷新的次数与INSERT 语句的次数一样多。如果您可以使用单个 INSERT.

锁定还降低了多连接测试的总时间，尽管单个连接的最大等待时间可能会因为它们等待锁定而增加。假设五个客户端尝试同时执行插入操作，如下所示：

连接 1 执行 1000 次插入

连接 2、3 和 4 做 1 个插入

连接 5 进行 1000 次插入

如果不使用锁定，则连接 2、3 和 4 在 1 和 5 之前完成。如果使用锁定，连接 2、3 和 4 可能不会在 1 或 5 之前完成，但总时间应该在 40% 左右快点。

INSERT, UPDATE, 和 DELETE操作在 MySQL 中非常快，但是您可以通过在执行超过大约 5 次连续插入或更新的所有内容周围添加锁来获得更好的整体性能。如果你做了很多连续的插入，你可以偶尔做LOCK TABLES一次 UNLOCK TABLES（每 1000 行左右）以允许其他线程访问表。这仍然会带来不错的性能提升。

INSERTLOAD DATA即使使用刚刚概述的策略， 加载数据的速度仍然比 慢得多。

为了提高MyISAM 表的性能，对于LOAD DATA 和INSERT，通过增加 key_buffer_size系统变量来扩大键缓存。请参见第 5.1.1 节，“配置服务器”。

8.6.3 优化 REPAIR TABLE 语句

8.6.3 优化 REPAIR TABLE 语句

REPAIR TABLEfor MyISAMtables 类似于使用 myisamchk进行修复操作，并且应用了一些相同的性能优化：

myisamchk具有控制内存分配的变量。您可以通过设置这些变量来提高性能，如 第 4.6.3.6 节“myisamchk 内存使用”。

对于REPAIR TABLE，同样的原则也适用，但是因为修复是由服务器完成的，所以您设置的是服务器系统变量而不是 myisamchk变量。此外，除了设置内存分配变量之外，增加 myisam_max_sort_file_size 系统变量会增加修复使用更快文件排序方法的可能性，并避免通过键缓存方法进行较慢修复。在检查以确保有足够的可用空间来保存表文件的副本之后，将变量设置为系统的最大文件大小。包含原始表文件的文件系统中必须有可用空间。

假设使用以下选项完成myisamchk表修复操作来设置其内存分配变量：

--key_buffer_size=128M --myisam_sort_buffer_size=256M
--read_buffer_size=64M --write_buffer_size=64M

其中一些myisamchk变量对应于服务器系统变量：

myisamchk变量

   

系统变量

   

key_buffer_size

   

key_buffer_size

   

myisam_sort_buffer_size

   

myisam_sort_buffer_size

   

read_buffer_size

   

read_buffer_size

   

write_buffer_size

   

没有任何

   

每个服务器系统变量都可以在运行时设置，其中一些（myisam_sort_buffer_size, read_buffer_size）除了全局值之外还具有会话值。设置会话值会限制更改对您当前会话的影响，并且不会影响其他用户。更改仅全局变量 ( key_buffer_size, myisam_max_sort_file_size) 也会影响其他用户。对于 key_buffer_size，您必须考虑到缓冲区是与这些用户共享的。例如，如果将myisamchk key_buffer_size变量设置为 128MB，则可以设置相应的 key_buffer_size大于该值的系统变量（如果尚未设置为更大），以允许其他会话中的活动使用密钥缓冲区。但是，更改全局键缓冲区大小会使缓冲区无效，从而导致磁盘 I/O 增加和其他会话变慢。避免此问题的另一种方法是使用单独的键缓存，将要修复的表中的索引分配给它，并在修复完成时释放它。请参阅 第 8.10.2.2 节，“多键缓存”。

根据前面的说明，REPAIR TABLE可以进行如下操作以使用类似于myisamchk命令的设置。这里分配了一个单独的 128MB 密钥缓冲区，并且假定文件系统允许文件大小至少为 100GB。

SET SESSION myisam_sort_buffer_size = 256*1024*1024;
SET SESSION read_buffer_size = 64*1024*1024;
SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;
SET GLOBAL repair_cache.key_buffer_size = 128*1024*1024;
CACHE INDEX tbl_name IN repair_cache;
LOAD INDEX INTO CACHE tbl_name;
REPAIR TABLE tbl_name ;
SET GLOBAL repair_cache.key_buffer_size = 0;

如果您打算更改全局变量，但只想在操作期间这样做以REPAIR TABLE尽量减少对其他用户的影响，请将其值保存在用户变量中并在之后恢复它。例如：

SET @old_myisam_sort_buffer_size = @@GLOBAL.myisam_max_sort_file_size;
SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;
REPAIR TABLE tbl_name ;
SET GLOBAL myisam_max_sort_file_size = @old_myisam_max_sort_file_size;

REPAIR TABLE如果您希望这些值在默认情况下生效，可以在服务器启动时全局设置 影响的系统变量。例如，将这些行添加到服务器my.cnf文件：

[mysqld]
myisam_sort_buffer_size=256M
key_buffer_size=1G
myisam_max_sort_file_size=100G

这些设置不包括 read_buffer_size. 将 read_buffer_size全局设置为一个较大的值会对所有会话执行此操作，并且可能会由于为具有许多同时会话的服务器分配过多的内存而导致性能下降。

MyISAM存储引擎在以读取为主的数据或低并发操作的情况下表现最佳，因为表锁限制了执行同时更新的能力 。在 MySQL 中，InnoDB是默认的存储引擎，而不是MyISAM.

8.7 优化内存表

考虑将MEMORY表用于经常访问、只读或很少更新的非关键数据。根据实际工作负载下的等效 表InnoDB或MyISAM表对您的应用程序进行基准测试，以确认任何额外的性能值得冒丢失数据的风险，或者在应用程序启动时从基于磁盘的表中复制数据的开销。

为获得表的最佳性能MEMORY，请检查针对每个表的查询类型，并指定要用于每个关联索引（B 树索引或哈希索引）的类型。在CREATE INDEX 语句上，使用子句USING BTREEor USING HASH。>B 树索引对于通过诸如or之类的运算符进行大于或小于比较的查询来说是快速的BETWEEN。散列索引仅对通过运算符查找单个值=或通过运算符查找一组受限值的查询快速IN。为什么 USING BTREE通常是比默认更好的选择USING HASH，请参阅 第 8.2.1.20 节，“避免全表扫描”. 有关不同类型MEMORY索引的实现细节，请参见 第 8.3.8 节，“B-Tree 和哈希索引的比较”。

顶一下

(0)

100%

订阅 回复

踩一下

(0)

100%