Mysql（9）优化数据库结构

最新推荐文章于 2024-05-05 17:38:17 发布

java编程小帅

最新推荐文章于 2024-05-05 17:38:17 发布

阅读量1k

点赞数

分类专栏： MySQL 文章标签：数据库 mysql database 后端 java

本文链接：https://blog.csdn.net/qq_37107851/article/details/122826305

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

Row Size Limit Examples

1、优化数据大小

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

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

Table Columns

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

Row Format

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

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

当使用可变长度字符集（例如 utf8mb3 或 utf8mb4）时，紧凑的行格式系列还优化了 CHAR 列存储。当 ROW_FORMAT=REDUNDANT 时，CHAR(N) 占用 N × 字符集的最大字节长度。许多语言可以主要使用单字节 utf8 字符编写，因此固定的存储长度通常会浪费空间。使用紧凑的行格式系列，InnoDB 通过去除尾随空格为这些列分配 N 到 N × 字符集的最大字节长度范围内的可变存储量。最小存储长度为 N 字节，以便在典型情况下进行就地更新。
要通过以压缩形式存储表数据来进一步最小化空间，请在创建 InnoDB 表时指定 ROW_FORMAT=COMPRESSED，或在现有 MyISAM 表上运行 myisampack 命令。（InnoDB 压缩表是可读可写的，而 MyISAM 压缩表是只读的。）
对于 MyISAM 表，如果我们没有任何可变长度列（VARCHAR、TEXT 或 BLOB 列），则使用固定大小的行格式。这更快，但可能会浪费一些空间。

Indexes

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

Joins

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

Normalization

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

2、优化数据类型

优化数值（Numeric）数据

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

优化字符和字符串类型

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

针对 BLOB 类型进行优化

存储包含文本数据的大型 blob 时，请考虑先对其进行压缩。当整个表被 InnoDB 或 MyISAM 压缩时，不要使用这种技术。
对于具有多个列的表，为了减少不使用 BLOB 列的查询的内存需求，请考虑将 BLOB 列拆分为单独的表，并在需要时使用连接查询引用它。
由于检索和显示 BLOB 值的性能要求可能与其他数据类型有很大不同，因此我们可以将特定于 BLOB 的表放在不同的存储设备甚至单独的数据库实例上。例如，检索 BLOB 可能需要大的顺序磁盘读取，这更适合传统硬盘驱动器而不是 SSD 设备。

3、表列数和行大小的限制

Column Count Limits

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

表的最大行大小限制了列的数量（可能还有大小），因为所有列的总长度不能超过此大小。
单个列的存储要求限制了适合给定最大行大小的列数。某些数据类型的存储要求取决于存储引擎、存储格式和字符集等因素。
存储引擎可能会施加额外的限制来限制表列数。例如，InnoDB 每个表有 1017 列的限制。
每个表都有一个包含表定义的 .frm 文件。该定义以可能影响表中允许的列数的方式影响此文件的内容。

Row Size Limits

MySQL 表的内部表示具有 65,535 字节的最大行大小限制，即使存储引擎能够支持更大的行。 BLOB 和 TEXT 列仅对行大小限制贡献 9 到 12 个字节，因为它们的内容与行的其余部分分开存储。
对于 4KB、8KB、16KB 和 32KB innodb_page_size 设置，适用于本地存储在数据库页面中的数据的 InnoDB 表的最大行大小略小于半页。例如，对于默认的 16KB InnoDB 页大小，最大行大小略小于 8KB。对于 64KB 页面，最大行大小略小于 16KB。
不同的存储格式使用不同数量的页眉和尾数据，这会影响可用于行的存储量。

Row Size Limit Examples

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

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 除了可变长度列长度字节所需的空间外，还需要 NULL 列的空间，导致行大小超过 65,535 字节：
```
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 页的行大小限制。

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.

java编程小帅

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Mysql（9）优化数据库结构

目录1、优化数据大小Table ColumnsRow FormatIndexesJoinsNormalization2、优化数据类型优化数值（Numeric）数据优化字符和字符串类型针对 BLOB 类型进行优化3、表列数和行大小的限制Column Count LimitsRow Size LimitsRow Size Limit Examples1、优化数据大小设计我们的表以最小化它们在磁盘上的空间。这可以通过减少写入磁盘和从磁盘读取的数
复制链接

扫一扫

专栏目录