InnoDB一定会在索引中加上主键吗?

最新推荐文章于 2023-02-12 21:49:25 发布

babydavic

最新推荐文章于 2023-02-12 21:49:25 发布

阅读量2.1k

点赞数

分类专栏： DBA

DBA 专栏收录该内容

23 篇文章

订阅专栏

本文深入探讨了InnoDB存储策略中的主键与索引的关系，解释了索引末尾是否包含主键对索引大小的影响。通过具体实例展示了如何合理利用主键字段优化索引结构，以提升查询效率并减少存储空间消耗。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

转： http://www.penglixun.com/tech/database/will_innodb_store_pk_in_index.html

我之前看代码记得是如果索引末尾就是主键，那么InnoDB就不再添加主键了，如果索引末尾不是主键，那么会添加主键，但是这跟测试结果不符：

CREATE TABLE t (
  a char(32) not null primary key,
  b char(32) not null,
  KEY idx1 (a,b),
  KEY idx2 (b,a)
) Engine=InnoDB;

插入部分数据后可以看到idx1和idx2两个索引的大小相同。这说明idx1和idx2的内部结构是一样的，因此 不可能 是idx1在内部存为(a,b,a)。

重点：

因此只要用户定义的索引字段中包含了主键中的字段，那么这个字段就不会再被InnoDB自动加到索引中了，如果用户的索引字段中没有完全包含主键字段，InnoDB就会把剩下的主键字段加到索引末尾。

因此我们最初的例子中， idx1 和 idx2 两个索引内部大小完全一样，没有区别。

最后再补充下组合主键的例子：

CREATE TABLE t (
  a char(32) not null,
  b char(32) not null,
  c char(32) not null,
  d char(32) not null,
  PRIMARY KEY (a,b)
  KEY idx1 (c,a),
  KEY idx2 (d,b)
) Engine=InnoDB;

这个表InnoDB会自动补全主键字典，idx1 实际上内部存储为 (c,a,b)，idx2 实际上内部存储为 (d,b,a)。
但是这个自动添加的字段，Server层是不知道的，所以MySQL优化器并不知道这个字段的存在，所以如果你有一个查询：

SELECT * FROM t WHERE d=x1 AND b=x2 ORDER BY a;

其实内部存储的idx2(d,b,a)可以让这个查询完全走索引，但是由于Server层不知道，所以最终MySQL优化器可能选择 idx2(d,b) 做过滤然后排序 a 字段，或者直接用PK扫描避免排序。

而如果我们定义表结构的时候就定义为 KEY idx2(d,b,a) ，那么MySQL就知道(d,b,a)三个字段索引中都有，并且InnoDB发现用户定义的索引中包含了所有的主键字段，也不会再添加了，并没有增加存储空间。

因此，由衷的建议，所有的DBA建索引的时候，都在业务要求的索引字段后面补上主键字段，这没有任何损失，但是可能给你带来意外的惊喜。