InnoDB 表中没有主键？教你如何优雅解决聚簇索引问题！

在 InnoDB 存储引擎中， 聚簇索引 是表数据存储的基础。一般情况下，聚簇索引是通过主键实现的。但如果表没有显式创建主键，InnoDB 会自动选择一些规则来生成聚簇索引。不过这种自动行为可能会对性能优化和数据管理带来困扰，因此我们可以通过 主动创建主键 来解决问题。

1. 问题描述：没有创建主键会发生什么？

如果你在 InnoDB 表中没有定义主键，InnoDB 会自动选择以下规则来生成聚簇索引：

如果表中有一个 唯一非空索引，InnoDB 会选择这个索引作为聚簇索引。
如果表中没有唯一非空索引，InnoDB 会创建一个隐藏的 6 字节的内部列（称为 row_id），作为聚簇索引。

问题：

自动生成的聚簇索引不可控：你无法直接访问或操作隐藏的 row_id。
性能和存储问题：没有显式主键会导致聚簇索引的高效性下降，查询性能可能受影响，尤其是当你频繁进行插入、更新或删除操作时。

2. 解决方法：主动创建主键

为了避免 InnoDB 自动生成聚簇索引的默认行为，并提升表的性能和管理便利性，可以主动为表添加一个主键。以下是几种解决方法：

方法一：为表创建自增主键

最常见、最推荐的方式是在表中添加一个自增主键列，用来唯一标识每一行数据。

操作步骤：

如果表已经存在，可以用 ALTER TABLE 来添加主键：

    ALTER TABLE your_table_name ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

id 是新建的主键列。
AUTO_INCREMENT 确保主键值递增。
PRIMARY KEY 将该列定义为主键。

如果是新建表，可以直接在创建表时定义主键：

   CREATE TABLE your_table_name (
       id INT NOT NULL AUTO_INCREMENT,
       column1 VARCHAR(255),
       column2 INT,
       -- 其他列
       PRIMARY KEY (id)
   );

优势：

主键值是自增的，插入数据时总是追加到数据页的末尾，避免页分裂。
查询性能高，因为主键索引可以快速定位每一行。
适合大多数场景，尤其是数据量大且经常插入的表。

方法二：使用已有的唯一字段作为主键

如果表中已经有一个能够唯一标识每行的字段（例如身份证号、订单编号等），可以直接将该字段设置为主键。

操作步骤：

如果表已经存在，可以用 ALTER TABLE 来设置主键：

   ALTER TABLE your_table_name ADD PRIMARY KEY (unique_column);

unique_column 是你要作为主键的字段。

如果是新建表，可以直接在创建表时定义主键：

   CREATE TABLE your_table_name (
       unique_column VARCHAR(255) NOT NULL,
       column1 VARCHAR(255),
       column2 INT,
       -- 其他列
       PRIMARY KEY (unique_column)
   );

优势：

直接利用已有的唯一字段，无需创建额外的列。
当唯一字段是业务字段时（如订单号等），查询更直观。

注意：

如果该字段的数据类型较大（如字符串），索引占用的空间会增大，可能影响查询性能。
如果该字段不是自增的，随机插入数据可能会导致页分裂，影响插入性能。

方法三：合并多个字段作为主键（复合主键）

如果表中没有单独的唯一列，但多个列的组合可以唯一标识每一行数据，可以通过 复合主键 解决问题。

操作步骤：

如果表已经存在，可以用 ALTER TABLE 添加复合主键：

   ALTER TABLE your_table_name ADD PRIMARY KEY (column1, column2);

column1 和 column2 是组成复合主键的字段。

如果是新建表，可以直接在创建表时定义复合主键：

   CREATE TABLE your_table_name (
       column1 INT NOT NULL,
       column2 VARCHAR(255) NOT NULL,
       column3 VARCHAR(255),
       -- 其他列
       PRIMARY KEY (column1, column2)
   );

优势：

不需要新增列，可以直接利用现有字段。
适合业务场景中天然需要多列标识唯一性的表。

注意：

复合主键会增加索引大小，占用更多存储空间。
查询和更新时，必须提供所有主键字段的值，操作稍显复杂。

方法四：重构数据库表

如果表设计不合理且缺少主键，可以考虑重新设计表结构，确保每张表都有一个合适的主键。

重构建议：

添加自增主键或业务主键。
去重，确保数据的唯一性。
优化表的字段设计，使主键选择更加明确。

3. 选择方法时的注意事项

如果表经常插入数据，推荐使用 自增主键，因为它天然按照顺序插入，避免随机插入导致的页分裂。
如果表已经有唯一字段，可以直接用它作为主键，但需要注意字段的存储和查询性能。
如果表需要复合主键，确保复合字段的数量尽量少，否则会增加存储和管理复杂度。

4. 总结

如果 InnoDB 表没有创建主键：

最推荐的方法是 添加自增主键，提高插入性能，减少页分裂和存储浪费。
如果已有唯一字段，可以直接将该字段设置为主键。
对于特殊场景，可以使用复合主键，但要权衡性能和管理的复杂性。

主动创建合适的主键，不仅可以避免 InnoDB 自动生成的隐藏索引带来的不便，还能显著优化数据库性能！

原文阅读