主页 / dba / 问题 / 189217
Accepted
Aaron
Asked: 2017-10-25 09:18:43 +0800 CST

MySQL - 在表 DDL 语句中或单独创建索引

  • 772

我正在从 SQL Server 过渡到 MySQL，我试图了解创建索引作为 DDL 表创建语句的一部分与为要创建的每个索引创建单独的 DDL 语句之间的区别。

关于创建索引状态的 MySQL 文档

通常，在使用 CREATE TABLE 创建表本身时，您会在表上创建所有索引。请参阅第 13.1.18 节，“CREATE TABLE 语法”。该准则对于 InnoDB 表尤其重要，其中主键决定了数据文件中行的物理布局。CREATE INDEX 使您能够向现有表添加索引。

为什么 InnoDB 表将索引创建为创建表 DDL 的一部分很重要？（在下面更新更多细节）

创建表后创建索引的潜在问题是什么？

更新

我的问题是针对 MySQL 站点上关于在创建表期间创建索引与在创建表之后但在用数据填充之前创建索引的声明。MySQL 网站上的声明使它看起来有好处，性能或其他方面，当使用 InnoDB 表时，在表的 DDL 语句中声明索引是最佳实践，但他们没有解释原因。

参考我将如何在 SQL Server 中处理这个问题，部分是因为 DDL 限制，部分是因为我喜欢明确的性质，我将制作我的 DDL 语句并分配主键以及任何外键约束。之后我会分配我的聚簇索引（如果它不是主键）和二级索引（唯一和非唯一）。但是在任何一种方法中，如果我要使用表 DDL 创建聚簇索引或将其分开，最终结果都是相同的。他们的声明听起来像是 RDMS 将根据方法以不同方式处理表创建，即使最终结果是相同的，一个主键和一个聚集索引。

mysql index

2 个回答

  1. Best Answer

    在 InnoDBPRIMARY KEY中，根据定义， 是聚类的和 BTree 组织的。如果在指定所需的 PK 之前填充表，则会生成一个隐藏的 PK。这个隐藏的 PK 必须被删除并替换为所需的 PK ，并且必须根据新的 PK 对表进行重新排序。没有造成任何“伤害”，但会花费额外的时间并且 BTree 可能会变得更加碎片化。

    单独添加辅助密钥（除 PK 之外的任何密钥）CREATE TABLE可能效率低下，也可能不会。但是，同样，没有造成任何“伤害”。效率低下取决于版本、索引的性质等。

    FOREIGN KEYs是另一回事 - 它们必须以“正确的顺序”应用。这可以通过以CREATE TABLEs“正确顺序”设置或通过以“正确顺序”单独添加 FK 来实现。

    好吧，这方面的文档很弱。您可以在 bugs.mysql.com 报告它。

    我更喜欢拥有（并看到）CREATE TABLEDDL 中的所有内容。

    • 2

  2. 如果将主键放在 InnoDB 表上，它就像 SQL Server 中的索引组织表一样。换句话说，索引和实际数据存储在一起，通常按照索引的顺序存储。这就像按姓氏的字母顺序创建电话簿，然后决定要按名字“索引”它——你必须求助于整个事情。

    这有更多细节：

    http://www.ovaistariq.net/521/understanding-innodb-clustered-indexes/

    • 1

相关问题