8.4 优化数据库结构

在作为数据库设计者的角色中，寻找组织方案，表和列的最有效的方法。 与调整应用程序代码时一样，您可以最大限度地减少I / O，将相关项目保留在一起，并提前进行计划，以便随着数据量的增加性能保持较高水平 从有效的数据库设计开始，团队成员可以更轻松地编写高性能的应用程序代码，并使数据库在应用程序演变和重写时可能会持续下去。

8.4.1 优化数据大小

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

MySQL支持许多不同的存储引擎（表类型）和行格式。 对于每个表格，您可以决定使用哪种存储和索引方法。 为您的应用程序选择适当的表格格式可以为您带来巨大的性能提升。

--第15章 存储引擎

您可以通过使用下面列出的技术来获得更好的表性能和最小的存储空间：

表字段

• 尽可能使用最有效（最小）的数据类型。 MySQL有许多专门的类型可以节省磁盘空间和内存。 例如，如果可能，请使用较小的整数类型以获得较小的表格.MEDIUMINT通常是比INT更好的选择，因为MEDIUMINT列使用的空间减少了25％。
• 如果可能，声明列不为NULL。 通过更好地使用索引并消除测试每个值是否为NULL的开销，它使SQL操作更快。 您还可以节省一些存储空间，每列一位。 如果你真的需要表中的NULL值，使用它们。 只要避免在每个列中允许NULL值的默认设置。

--也就是说，尽量在保证此列不可能为null的情况下，设置字段的not null 属性

行格式

• 在MySQL 5.7.8及更早版本中，InnoDB表格默认以COMPACT行格式创建。 从MySQL 5.7.9开始，默认的行格式是DYNAMIC，默认的行格式可以使用innodb_default_row_format配置选项进行配置。

要请求除DYNAMIC行格式以外的行格式，可以配置innodb_default_row_format或在CREATE TABLE或ALTER TABLE语句中显式指定ROW_FORMAT选项。

compact的行格式使行存储空间减少了大约20％，代价是增加了某些操作的CPU使用量。 如果您的工作负载是受缓存命中率和磁盘速度限制的典型负载，则可能会更快。 如果是受到CPU速度的限制的情况，可能会变慢。

--这种行格式 消耗CPU 来换取对I/O和内存的性能提升。

compact 的InnoDB格式也改变了如何存储包含utf8或utf8mb4数据的CHAR列。 使用ROW_FORMAT=REDUNDANT时，utf8或utf8mb4 CHAR（N）列占用最大字符字节长度×N个字节。 许多语言可以主要使用单字节的utf8或utf8mb4字符编写，所以固定的存储长度通常会浪费空间。

如果需要的话，InnoDB通过去除尾部空格来为这些列分配可变数量的存储空间。 最小存储长度保持为N字节，以便在典型情况下进行就地更新。

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

• 对于MyISAM表，如果没有任何可变长度的列（VARCHAR，TEXT或BLOB列），则使用固定大小的行格式。 这会更快，但可能会浪费一些空间。即使具有CREATE TABLE选项ROW_FORMAT=FIXED的VARCHAR列，您也可以hint您想要固定长度的行。

索引

• 表格的主键索引应尽可能短。 这使得每一行的识别简单而有效。 对于InnoDB表，主键列在每个二级索引条目中都是重复的，所以如果有很多二级索引，则较短的主键可以节省相当多的空间。
• 只创建您需要提高查询性能的索引。 索引很适合检索，但会减慢插入和更新操作。 如果您主要通过在列组合上进行搜索来访问表，请在其上创建一个复合索引，而不是每个列的单独索引。 索引第一部分应该是最常用的列。 如果从表中选择时总是使用多列，则索引中的第一列应该是重复次数最多的列，以获得更好的索引压缩。
• 如果长字符串列很可能在第一个字符数上有一个唯一的前缀，那么最好只索引这个前缀，使用MySQL支持在列的最左边部分创建一个索引。 更短的索引速度更快，不仅是因为它们需要更少的磁盘空间，还因为它们还会在索引缓存中提供更多的hits，从而减少磁盘查找次数。

joins

• 在某些情况下，分割成两个经常扫描的表格可能是有益的。 如果它是一个动态格式的表，并且可以使用一个较小的静态格式表可用于在扫描表时查找相关行。
• 在具有相同数据类型的不同表中声明具有相同信息的列，以便根据相应的列加快连接。
• 保持列名简单，以便可以在不同的表中使用相同的名称，并简化连接查询。 例如，在名为customer的表中，使用name的列名称而不是customer_name。 为了使您的名字可以移植到其他SQL服务器上，请考虑将其保留为18个字符以内。

Normalization

• 通常，尽量保持所有的数据不是冗余的（观察数据库理论中提到的第三范式）。 替代重复冗长的值，如名称和地址，为其分配唯一的ID，根据需要在多个较小的表中重复这些ID，并通过引用join子句中的ID来加入查询中的表。

---配置表，然后需要显示的时候和小配置表连接。

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

8.4.2 优化器

8.4.2.1 优化数值数据

• 对于可以表示为字符串或数字的唯一ID或其他值，来对与字符串来说首选数字列。 由于较大的数值可以以比相应的字符串更少的字节存储，所以速度更快并且需要更少的内存来传输和比较它们。
• 如果您使用的是数字数据，则在许多情况下访问数据库（使用实时连接）的信息比访问文本文件要快。 数据库中的信息很可能以比文本文件更紧凑的格式存储，因此访问它涉及更少的磁盘访问。 您还可以将代码保存在应用程序中，因为您可以避免解析文本文件以查找行和列边界。

8.4.2.2 为字符和字符串类型进行优化

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

• 当不需要特定于语言的归类特性时，使用二进制collation顺序进行快速比较和排序操作。 您可以使用BINARY运算符在特定查询中使用二进制排序规则。
• 比较不同列的值时，尽可能使用相同的字符集和collation来声明这些列，以避免在运行查询时进行字符串转换。
• 对于小于8KB的列值，请使用二进制VARCHAR而不是BLOB。 GROUP BY和ORDER BY子句可以生成临时表，如果原始表不包含任何BLOB列，则可以使用MEMORY存储引擎。
• 如果表包含名称和地址等字符串列，但许多查询不检索这些列，请考虑将字符串列拆分为单独的表，并在必要时使用具有外键的连接查询。 当MySQL从行中检索任何值时，它将读取一个包含该行所有列（可能还有其他相邻行）的数据块。 保持每行较小，只有最常用的列，允许更多的行适合每个数据块。 这种紧凑的表格减少了常见查询的磁盘I/O和内存使用量。
• 当您将一个随机生成的值用作InnoDB表中的主键时，如果可能，请使用升序值（如当前日期和时间）作为前缀。 当连续的主值被物理地存储在彼此附近时，InnoDB可以更快地插入和检索它们。

8.4.2.3 为BLOB 类型优化

• 存储包含文本数据的大块时，请考虑先压缩它。 整个表由InnoDB或MyISAM压缩时，不要使用这种技术。
• 对于包含多个列的表，为了减少不使用BLOB列的查询的内存要求，请考虑将BLOB列拆分为单独的表，并在需要时使用连接查询引用它。
• 由于检索和显示BLOB值的性能要求可能与其他数据类型非常不同，因此可以将BLOB特定的表放在不同的存储设备上，甚至是单独的数据库实例中。 例如，要检索BLOB可能需要大量顺序磁盘读取，这比较适合于传统硬盘驱动器而不是SSD设备。
• 您可以将列值的hash存储在单独的列中，将该列索引，并在查询中测试散列值，而不是针对非常长的文本字符串进行相等性测试。 （使用MD5（）或CRC32（）函数来生成散列值）。由于散列函数可能会为不同的输入产生重复的结果，因此在查询中仍然包含一个AND blob_column = long_string_value以防止错误匹配; 由于hash值更小更容易扫描的索引，所以可以提升性能。

8.4.2.4 使用PROCEDURE ANALYSE

ANALYSE([max_elements[,max_memory]])

ANALYZE（）检查查询的结果，并返回结果分析，为每列提供最佳数据类型，以帮助减小表的大小。 要获得此分析，请将PROCEDURE ANALYZE附加到SELECT语句的末尾：

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

例如：

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

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

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

• max_elements（默认值为256）是ANALYZE（）每列distinct values的最大数量。 这被ANALYZE（）用来检查最佳数据类型是否应该是ENUM类型; 如果有超过max_elements不同的值，那么ENUM不是建议的类型。
• max_memory（默认值8192）是ANALYZE（）在尝试查找所有不同值时每列应分配的最大内存量。

8.4.3 优化多个表

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

8.4.3.1 MySQL 如何打开和关闭表

当你执行一个mysqladmin status命令时，你应该看到如下所示：

Uptime: 426 Running threads: 1 Questions: 11082

Reloads: 1 Open tables: 12

如果您只有六个表格，Open表的数值可能会有点令人费解。

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

table_open_cache和max_connections系统变量影响服务器保持打开的最大文件数量。 如果您增加这些值中的一个或两个，值会根据您的操作系统会收到限制。 许多操作系统允许您增加打开文件的限制，尽管这个方法在不同系统之间差别很大。 请参阅您的操作系统文档，以确定是否有可能增加限制以及如何这样做。

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

确保您的操作系统可以处理由table_open_cache设置隐含的打开文件描述符的数量。 如果table_open_cache设置得太高，MySQL可能会耗尽文件描述符并拒绝连接，无法执行查询，并且非常不可靠。

您还应该考虑到MyISAM存储引擎为每个独特的打开表格需要两个文件描述符的事实。 对于分区的MyISAM表，打开的表的每个分区都需要两个文件描述符。 （进一步注意，当MyISAM打开一个分区表时，它会打开这个表的每个分区，无论是否实际使用了一个给定的分区，请参见MyISAM和分区文件描述符的用法）。可以为mysqld 增加MySQL可用的文件描述符的数量 --open-files-limit的启动选项改。

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

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

• 当缓存已满并且线程尝试打开不在缓存中的表时。
• 当缓存包含多于table_open_cache条目并且缓存中的表不再被任何线程使用时。
• 表flushing操作发生时。 当有人发出FLUSH TABLES语句或执行mysqladmin flush-tables或mysqladmin refresh命令时，会发生这种情况。

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

• 当前未使用的表被释放，从最近使用的表开始。
• 如果需要打开新表，但高速缓存已满并且没有表可以被释放，则根据需要临时扩展cache。 当缓存处于临时扩展状态并且表从已使用状态变为未使用状态时，表被关闭并从缓存中释放。

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

--也就是说，2个线程访问一张表，或者2个表连接到自己，需要打开3个描述符，2个数据文件，1个索引文件。

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

您可以通过检查mysql状态变量Opened_tables来确定您的表缓存是否太小，该变量指示自服务器启动以来的表开放操作数：

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';

| Variable_name | Value |

| Opened_tables | 2741 |

如果值非常大或快速增加，则即使未发出多个FLUSH TABLES语句，也要增加表缓存大小。

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

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

---表太多了会占用大量的缓存，并且当占满之后，每次新开表，都要去释放旧表。

8.4.4 MySQL如何使用内部临时表

在某些情况下，服务器在处理查询时创建内部临时表。 用户无法直接控制服务器何时执行此操作。 一个内部临时表可以放在内存中，由MEMORY存储引擎处理，或者存储在磁盘上。 服务器最初可能会创建一个内部临时表作为内存表，如果它变得太大，则将其转换为磁盘上的表。 从MySQL 5.7.5开始，internal_tmp_disk_storage_engine系统变量确定服务器使用哪个存储引擎来管理磁盘上的内部临时表。 值可以是MYISAM或INNODB。 MySQL 5.7.5中的默认值是MYISAM。 在MySQL 5.7.6中，默认是INNODB。 在MySQL 5.7.5之前，服务器总是使用MyISAM。

临时表可以在如下条件下创建：

• UNION查询使用临时表，稍后会介绍一些例外情况
• 某些视图需要使用TEMPTABLE算法评估的临时表，或使用UNION或聚合的临时表。
• 如果存在ORDER BY子句和不同的GROUP BY子句，或者ORDER BY或GROUP BY包含来自联接队列中第一个表以外的表的列，则会创建一个临时表。
• DISTINCT与ORDER BY结合可能需要一个临时表。
• 如果使用SQL_SMALL_RESULT选项，MySQL将使用内存中的临时表，除非查询还包含需要磁盘存储的元素（稍后介绍）。
• 多表的UPDATE 语句
• GROUP_CONCAT()或COUNT(DISTINCT)评估。
• 派生表（在FROM子句中的子查询）。
• 为子查询或半连接materialization创建表。

要确定语句是否需要临时表，请使用EXPLAIN并检查Extra列以查看是否使用了临时表。 EXPLAIN不一定会为派生或物化临时表说Using temporary。

如果内部临时表最初是作为内存表创建的，但是变得太大，MySQL会自动将其转换为磁盘上的表。内存中临时表的最大大小是tmp_table_size和max_heap_table_size值中的最小值。 这与使用CREATE TABLE显式创建的MEMORY表不同：对于这样的表，只有max_heap_table_size系统变量决定允许表被增长到多少，并且不会转换为磁盘格式。

从MySQL 5.7.5开始，可以通过设置internal_tmp_disk_storage_engine系统变量来指定服务器用于磁盘内部临时表的存储引擎。 允许的值是MYISAM（默认值）和INNODB。

当服务器创建一个内部临时表（无论是在内存中还是在磁盘上），它都会增加Created_tmp_tables状态变量。 如果服务器在磁盘上创建表（initially或通过转换内存表），它将增加Created_tmp_disk_tables状态变量。

注意：

当使用internal_tmp_disk_storage_engine = INNODB时，生成超过InnoDB行或列限制的磁盘内部临时表的查询将返回行大小过大或列错误过多。 解决方法是将internal_tmp_disk_storage_engine设置为MYISAM。

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

• 表中存在BLOB或TEXT列
• 对于二进制字符串，GROUP BY或DISTINCT子句中存在大于512字节的任何字符串列，对于非二进制字符串则大于512个字符。 （在MySQL 5.7.3之前，无论字符串类型如何，限制为512字节。）
• 如果使用UNION或UNION ALL，则在SELECT列表中存在最大长度大于512（二进制字符串的字节，非二进制字符串的字符）的任何字符串列
• SHOW COLUMNS和DESCRIBE语句使用BLOB作为某些列的类型，因此用于结果的临时表是一个磁盘表。

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

不具备临时表的union评估条件是：

• union 是 UNION ALL，不是UNION 或者 UNION DISTINCT
• 没有全局的ORDER BY子句
• union 不是{INSERT |的top-level 查询块 REPLACE} ... SELECT ...语句

内部临时表存储引擎

一个内部临时表可以保存在内存中并由MEMORY存储引擎处理，或由InnoDB或MyISAM存储引擎存储在磁盘上。

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

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

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