Mysql数据库结构优化汇总-Toy模板网

这篇具有很好参考价值的文章主要介绍了Mysql数据库结构优化汇总。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一、优化数据大小

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

1、表列

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

2、行格式

InnoDB表默认使用DYNAMIC行格式创建。要使用DYNAMIC以外的行格式，请配置innodb_default_row_format，或在CREATE TABLE或ALTER TABLE语句中显式指定row_format选项。
紧凑的行格式系列，包括compact、DYNAMIC和COMPRESED，以增加某些操作的CPU使用为代价，减少了行存储空间。如果您的工作负载是受缓存命中率和磁盘速度限制的典型工作负载，那么它可能会更快。如果这是一种罕见的情况，受到CPU速度的限制，它可能会更慢。
当使用诸如utf8mb3或utf8mb4之类的可变长度字符集时，紧凑的行格式系列还优化了CHAR列存储。ROW_FORMAT=REDUNDANT时，CHAR（N）占据N×字符集的最大字节长度。许多语言可以主要使用单字节utf8mb3或utf8mb4字符编写，因此固定的存储长度通常会浪费空间。对于紧凑的行格式家族，InnoDB通过剥离尾部空格，为这些列分配N到N×字符集最大字节长度的可变存储量。最小存储长度为N字节，以便于在典型情况下进行就地更新。
要通过以压缩形式存储表数据来进一步减少空间，请在创建InnoDB表时指定ROW_FORMAT=CCOMPRESED，或在现有的MyISAM表上运行myisampack命令。（InnoDB压缩表可读写，MyISAM压缩表只读。）
对于MyISAM表，如果没有任何可变长度列（VARCHAR、TEXT或BLOB列），则使用固定大小的行格式。这更快，但可能会浪费一些空间。您可以提示您想要固定长度的行，即使您有带有CREATE TABLE选项ROW_FORMAT=fixed的VARCHAR列。

3、Indexes

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

4、Joins

在某些情况下，将经常扫描的表一分为二是有益的。如果它是一个动态格式表，并且可以使用较小的静态格式表，在扫描表时可以使用该表来查找相关行，则情况尤其如此。
在具有相同数据类型的不同表中声明具有相同信息的列，以加快基于相应列的联接。
保持列名简单，这样您就可以在不同的表中使用相同的名称，并简化联接查询。例如，在名为customer的表中，使用name的列名，而不是customer_name。要使您的名称可移植到其他SQL服务器，请考虑将其长度控制在18个字符以内。

5、标准化

通常，尽量保持所有数据不冗余（观察数据库理论中称为第三范式的内容）。不要重复名称和地址等冗长的值，而是为它们分配唯一的ID，根据需要在多个较小的表中重复这些ID，并通过引用联接子句中的ID来联接查询中的表。
如果速度比磁盘空间和保留多个数据副本的维护成本更重要，例如在分析大型表中的所有数据的商业智能场景中，可以放宽规范化规则，复制信息或创建摘要表以获得更高的速度。

二、优化 MySQL 数据类型

1、数值数据优化

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

2 、优化字符和字符串类型

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

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

3、针对 BLOB 类型进行优化

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

三、对多个表进行优化

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

1、 MySQL如何打开和关闭表

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

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

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

table_open_cache和max_connections系统变量会影响服务器保持打开的最大文件数。如果增加其中一个或两个值，则可能会遇到操作系统对每个进程打开的文件描述符数量的限制。许多操作系统允许您增加打开文件的限制，尽管不同系统的方法差异很大。请参阅您的操作系统文档，以确定是否可以提高限制以及如何提高限制。
table_open_cache与max_connections相关。例如，对于200个并发运行的连接，指定一个至少为200*N的表缓存大小，其中N是您执行的任何查询中每个联接的最大表数。您还必须为临时表和文件保留一些额外的文件描述符。

确保您的操作系统能够处理table_open_cache设置所暗示的打开文件描述符的数量。如果table_open_cache设置得太高，MySQL可能会用完文件描述符，并出现拒绝连接或无法执行查询等症状。
还要考虑到MyISAM存储引擎需要为每个唯一的打开表提供两个文件描述符。要增加MySQL可用的文件描述符数量，请设置open_files_limit系统变量。请参阅第B.3.2.16节“找不到文件和类似错误”。
打开的表的缓存保持在table_open_cache条目的级别。服务器在启动时自动调整缓存大小。要明确设置大小，请在启动时设置table_open_cache系统变量。MySQL可能会临时打开更多的表来执行查询，如本节稍后所述。

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

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

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

释放当前未使用的表，从最近最少使用的表。
如果必须打开新表，但缓存已满并且无法释放任何表，缓存是暂时的根据需要扩展。当缓存暂时位于扩展状态和表从已使用变为未使用状态，则表已关闭并从缓存中释放。

每次并发访问都会打开一个MyISAM表。这意味着，如果两个线程访问同一个表，或者一个线程在同一查询中访问该表两次（例如，通过将表连接到其自身），则需要打开该表两遍。每次并发打开都需要在表缓存中有一个条目。任何MyISAM表的第一次打开都需要两个文件描述符：一个用于数据文件，一个用于索引文件。表的每一次额外使用只为数据文件获取一个文件描述符。索引文件描述符在所有线程之间共享。
如果使用HANDLER tbl_name OPEN语句打开表，则会为线程分配一个专用的表对象。此表对象不由其他线程共享，并且在线程调用HANDLER tbl_name CLOSE或线程终止之前不会关闭。发生这种情况时，表会放回表缓存中（如果缓存未满）。
要确定表缓存是否太小，请检查Opened_tables状态变量，该变量指示自服务器启动以来打开表的操作次数：

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

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

四、数据库和表的数量限制

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

五、表大小限制

MySQL数据库的有效最大表大小通常由操作系统对文件大小的限制决定，而不是由MySQL的内部限制决定。有关操作系统文件大小限制的最新信息，请参阅特定于您的操作系统的文档。
Windows用户，请注意，FAT和VFAT（FAT32）不适用于MySQL的生产使用。请改用NTFS。
如果您遇到一个完整的表错误，可能发生这种错误的原因有几个：

磁盘可能已满。
您正在使用InnoDB表，并且InnoDB表空间文件中的空间已用完。表空间的最大大小也是表的最大大小。
通常，对于大小大于1TB的表，建议将表划分为多个表空间文件。
您已达到操作系统文件大小限制。例如，您在只支持大小不超过2GB的文件的操作系统上使用MyISAM表，而数据文件或索引文件已达到此限制。
您正在使用MyISAM表，而该表所需的空间超出了内部指针大小所允许的范围。默认情况下，MyISAM允许数据和索引文件增长到256TB，但此限制可以更改为65536TB（2567−1字节）的最大允许大小。
如果您需要大于默认限制的MyISAM表，并且您的操作系统支持大文件，那么CREATE table语句支持AVG_ROW_LENGTH和MAX_ROWS选项。服务器使用这些选项来确定表允许的大小。
如果指针大小对于现有表来说太小，则可以使用ALTER table更改选项以增加表的最大允许大小。
```
ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;
```
您必须仅为具有BLOB或TEXT列的表指定AVG_ROW_LENGTH；在这种情况下，MySQL不能仅根据行数来优化所需的空间。
要更改MyISAM表的默认大小限制，请设置MyISAM_data_pointer_size，它设置用于内部行指针的字节数。如果未指定MAX_ROWS选项，则该值用于设置新表的指针大小。 myisam_data_pointer_size的值可以是2到7。例如，对于使用动态存储格式的表，值4允许表达到4GB；值为6允许表的容量高达256TB。使用固定存储格式的表具有较大的最大数据长度。

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

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

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

如果你的大表是只读的，你可以使用myisampack来压缩它。myisampaack通常会将一个表压缩至少50%，所以实际上你可以拥有更大的表。myisampack还可以将多个表合并为一个表。
MySQL包含一个MERGE库，使您能够处理与单个MERGE表具有相同结构的MyISAM表集合。

六、表列数和行大小限制

1、列计数限制

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

表的最大行大小限制了列的数量（可能还有大小），因为所有列的总长度不能超过此大小。
单个列的存储要求限制了在给定的最大行大小内可容纳的列数。某些数据类型的存储要求取决于存储引擎、存储格式和字符集等因素。
存储引擎可能会施加限制表列数的附加限制。例如，InnoDB对每个表的列数限制为1017列。
功能键部分被实现为隐藏的虚拟生成的存储列，因此表索引中的每个功能键部分都计入表总列限制。

2、行大小限制

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

MySQL表的内部表示具有65535字节的最大行大小限制，即使存储引擎能够支持更大的行。BLOB和TEXT列只占行大小限制的9到12个字节，因为它们的内容与行的其余部分分开存储。
对于4KB、8KB、16KB和32KB InnoDB_page_size设置，应用于本地存储在数据库页面中的数据的InnoDB表的最大行大小略小于页面的一半。例如，对于默认的16KB InnoDB页面大小，最大行大小略小于8KB。对于64KB的页面，最大行大小略小于16KB。如果包含可变长度列的行超过了InnoDB的最大行大小，InnoDB会选择可变长度列用于外部页外存储，直到该行符合InnoDB的行大小限制。对于页外存储的可变长度列，本地存储的数据量因行格式而异。

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

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可以避免65535字节的行大小限制，并允许操作成功，因为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 65535字节的行大小限制，而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的语句之所以成功，是因为列需要32765+2个字节和32766+2个字节，这两个字节位于65535字节的最大行大小范围内：

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的语句失败，因为尽管列长度在65535字节的最大长度内，但需要另外两个字节来记录长度，这会导致行大小超过65535字节：

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

将列长度减少到65533或更少可以使语句成功:

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还需要NULL列的空间，导致行大小超过65535字节：

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

对于4KB、8KB、16KB和32KB InnoDB_page_size设置，InnoDB将行大小（用于本地存储在数据库页面内的数据）限制为略小于数据库页面的一半，对于64KB页面，限制为略低于16KB。
创建表t4的语句失败，因为定义的列超过了16KB InnoDB页面的行大小限制:文章来源地址https://www.toymoban.com/news/detail-468538.html

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=DYNAMIC DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.

到了这里，关于Mysql数据库结构优化汇总的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！