用于提升数据库性能的顶级 ySQL 架构检查

数据库模式定义了数据库的逻辑结构，包括表、列、关系、索引以及影响数据组织和访问方式的约束。这不仅涉及数据的存储方式，还涉及数据如何与查询、事务和其他操作交互。

这些检查可以帮助您在任何新的或挥之不去的问题像滚雪球一样发展成更大的问题之前掌握它们。您可以深入研究下面的这些架构检查，并准确了解如何解决数据库未通过的问题。请记住，在进行任何架构更改之前，请务必备份数据，以防止修改期间可能发生的潜在风险。

1.主键检查（缺少主键）

主键是任何表的关键部分，它唯一标识每一行并实现高效查询。如果没有主键，表可能会遇到性能问题，并且复制和架构更改实用程序等某些工具可能无法正常运行。

设计模式时定义主键可以避免几个问题：

1. 如果未指定主键或唯一键，MySQL 会创建一个内部主键或唯一键，该主键或唯一键无法使用。
2. 缺少主键可能会降低复制性能，尤其是基于行或混合的复制。
3. 主键允许可扩展的数据归档和清除。 pt-online-schema-change 等工具需要主键或唯一键。
4. 主键唯一标识行，这从应用程序的角度来看至关重要。

例子

要在创建表时在“ID”列上创建 PRIMARY KEY 约束，请使用以下 SQL：

ALTER TABLE Persons ADD PRIMARY KEY (ID);

要在多个列上定义主键：

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

注意：如果使用 ALTER TABLE 命令，则在首次创建表时必须声明主键列不包含 NULL 值。

2.表引擎检查（已弃用的表引擎）

MyISAM存储引擎已被弃用，仍在使用它的表应迁移到InnoDB。由于其卓越的性能、数据恢复功能和事务支持，InnoDB 是大多数用例的默认和推荐引擎。从 MyISAM 迁移到 InnoDB 可以显着提高写入密集型应用程序的性能，提供更好的容错能力，并允许更高级的 MySQL 功能，例如全文搜索和外键。

为什么首选 InnoDB：

• 崩溃恢复功能使其能够从数据库服务器或主机崩溃中自动恢复，而不会损坏数据。
• 仅锁定受查询影响的行，从而在高并发环境中获得更好的性能。
• 将数据和索引缓存在内存中，这对于读取繁重的工作负载来说是首选。
• 完全符合 ACID，确保数据完整性并支持事务。
• InnoDB 引擎受到了 MySQL 开发社区的大部分关注，使其成为最新且支持良好的引擎。

如何迁移到InnoDB

ALTER TABLE Persons ADD PRIMARY KEY (ID);

3.表排序规则检查（混合排序规则）

跨表甚至在表内使用不同的排序规则可能会导致性能问题，特别是在字符串比较和连接期间。如果两个字符串列的排序规则不同，MySQL 可能需要在运行时转换字符串，这会阻止使用索引并减慢查询速度。

当您对混合排序规则表进行更改时，可能会出现一些问题：

• 列级别的排序规则可能不同，因此如果联接中的相关列具有匹配的排序规则，表级别的不匹配不会导致问题。
• 更改表的排序规则，尤其是使用字符集切换，并不总是那么简单。可能需要进行数据转换，并且不支持的字符可能会变成损坏的数据。
• 如果创建表时未指定排序规则或字符集，它将继承数据库默认值。如果在数据库级别未设置任何内容，则将应用服务器默认值。 为了避免这些问题，标准化整个数据集的排序规则非常重要，特别是对于连接操作中经常使用的列。

如何更改排序规则设置

在对数据库的排序规则设置进行任何更改之前，请在非生产环境中测试您的方法，以避免出现意外后果。如果您有任何不确定的地方，最好咨询 DBA。

检索所有数据库的默认字符集和排序规则：

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

检查特定表的排序规则：

ALTER TABLE <table_name> ENGINE=InnoDB;

查找服务器的默认字符集：

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

查找服务器的默认排序规则：

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

更新特定数据库的排序规则：

SELECT @@GLOBAL.character_set_server;

更新特定表的排序规则：

SELECT @@GLOBAL.collation_server;

4.表字符集检查（混合字符集）

混合字符集与混合排序规则类似，因为它们可能会导致性能和兼容性问题。当不同的列或表使用不同的编码格式来存储数据时，就会出现混合字符集。

• 混合字符集可能会阻止索引使用或需要值转换，从而损害字符串列的连接性能。
• 可以在列级别定义字符集，只要连接涉及的列具有匹配的字符集，性能就不会受到表级别不匹配的影响。
• 更改表的字符集可能涉及数据转换，如果遇到不支持的字符，可能会导致数据损坏。
• 如果未指定字符集或排序规则，表将继承数据库的默认值，数据库将继承服务器的默认字符集和排序规则。

如何更改角色设置

在调整数据库的字符设置之前，请务必在临时环境中测试更改，以防止出现任何意外问题。如果您对任何步骤不确定，请咨询 DBA 以获得指导。

检索所有数据库的默认字符集和排序规则：

ALTER TABLE Persons ADD PRIMARY KEY (ID);

获取某列的字符集：

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

查找服务器的默认字符集：

ALTER TABLE <table_name> ENGINE=InnoDB;

查找服务器的默认排序规则：

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

查看表的结构：

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

示例输出：

SELECT @@GLOBAL.character_set_server;

要更改列字符集：

SELECT @@GLOBAL.collation_server;

5. 列自增检查（自增列类型）

对于预计无限增长并使用主键自动递增的表，建议切换到 UNSIGNED BIGINT 数据类型。这允许列处理更大范围的值，从而防止将来达到最大值后需要进行昂贵的表更改。通过指定 UNSIGNED，仅存储正值，从而有效地将数据类型的范围加倍。

如何更改角色设置

要将列类型修改为 UNSIGNED BIGINT：

ALTER DATABASE <db-name> COLLATE=<collation-name>;

6. 表外键检查（外键是否存在）

外键通过维护父表和子表之间的关系来提供数据一致性，但它们也会影响数据库性能。每次发生写操作时，都需要进行额外的查找来验证相关数据的完整性。这可能会导致速度变慢，尤其是在高流量环境中。

如果性能是一个问题，您可能需要考虑删除外键，特别是在可以在应用程序级别处理数据一致性的场景中。

如何删除外键

从表中删除外键约束：

ALTER TABLE Persons ADD PRIMARY KEY (ID);

7. 重复索引检查

MySQL 中的重复索引会消耗不必要的磁盘空间，并在写入操作期间产生额外的开销，因为每个索引都必须更新。这可能会使查询优化复杂化，可能导致执行计划效率低下，而不会带来任何实际好处。

识别并删除重复索引以简化查询优化并减少开销。但在删除索引之前，请确保该索引没有用于关键查询。

8. 未使用的索引检查

MySQL 中未使用的索引可能会消耗磁盘空间，增加插入、更新和删除期间的处理开销，并减慢整体操作，从而对数据库性能产生负面影响。虽然索引对于加快查询速度很有价值，但不使用的索引可能会给您的系统带来不必要的压力。
删除未使用或重复的索引的其他好处包括：

• 索引越少，MySQL 优化器评估的选择就越少，从而简化查询执行并减少 CPU/内存使用。
• 删除未使用的索引可以释放宝贵的磁盘空间，可用于更关键的数据，同时提高 I/O 效率。
• 当索引数量最小化时，索引维护任务（例如重建或重新组织）会变得更快且资源占用更少。这使得操作更加顺畅，特别是在需要 24/7 正常运行时间的环境中。

要识别 MySQL 或 MariabDB 中未使​​用的索引，请使用以下 SQL 语句：

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);

如何删除未使用或重复的索引

在 MySQL 8.0 及更高版本中，您可以使索引不可见以测试它们是否需要，而无需完全删除它们：

ALTER TABLE <table_name> ENGINE=InnoDB;

如果性能不受影响，则可以安全删除索引：

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, 
DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;

如果需要，您可以将索引恢复为可见：

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM
information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL ORDER BY
TABLE_SCHEMA, TABLE_COLLATION;

Releem 现已提供架构检查

通过最新更新，Releem 现在包含全面的架构健康检查。这些检查可以实时洞察数据库的结构完整性，并提供修复任何检测到的问题的可行建议。

通过自动化模式监控过程，Releem 消除了手动检查中的猜测，为数据库工程师节省了大量的时间和精力。您现在可以专注于更紧迫的任务，而不是花费大量时间处理架构细节。

以上是用于提升数据库性能的顶级 ySQL 架构检查的详细内容。更多信息请关注PHP中文网其他相关文章！