面试题：MySQL为什么一定要有一个主键

关注

原创 2024-10-19 09:48:56 943 阅读 AI 写同款· GEO 优化 ›

面试题：MySQL自增主键为什么不是连续的？提出这个问题，是因为在工作中发现 mysql 中的 user 表的 id 默认是自增的，但是数据库存储的结果却不是连续的。`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '递增id',`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', 阅读详情

什么是主键？

唯一标识一行记录
msql：innodb：mysql会根据主键创建主键索引
主键可以一个列，也可以联合主键
主键的选择要：不重复、唯一、没有空值，长度固定、自增
MySQL中只要有主键会立刻根据主键创建主键索引表（B+树），叶子结点存储数据和索引，非叶子节点存储索引
自增主键插入比较快，不会涉及到叶子结点分裂问题。

为什么一定要有主键？

有主键查询速度会更快。如果没有主键，那么这些表就会依赖一个全局序列计数器生成的ROW_ID来构造一个隐式的聚簇索引，就会导致竞争引起性能问题。
没有主键，更新或删除表中特定行会很困难。

延申

聚簇索引和非聚簇索引

很简单记住一句话：找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引，修改聚簇索引其实就是修改主键。（索引就是我们要找的数据）
索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。（找到了索引还要根据索引查询数据）

索引覆盖，索引下推，回表

索引覆盖

索引覆盖是指查询操作可以完全通过索引来完成，而无需访问表中的数据行。当一个查询的所有数据需求都可以通过索引中的数据来满足时，就发生了索引覆盖。这样，数据库就不需要进行额外的数据查找，从而减少了磁盘I/O操作，提高了查询性能1。

例如，如果有一个查询语句是：

SELECT name, age FROM users WHERE name = '张三';

如果name和age都包含在一个索引中，那么这个查询就可以通过索引覆盖来完成，不需要回表查询。

索引下推

索引下推是MySQL 5.6及以上版本引入的优化技术。它允许在索引遍历过程中应用查询条件，从而减少访问不必要数据的次数。这意味着数据库引擎会在索引层面过滤数据，而不是在找到所有可能的索引后再在服务器层面进行过滤2。

例如，对于查询：

SELECT * FROM users WHERE name LIKE '张%' AND age = 30;

在启用索引下推的情况下，数据库引擎会在遍历索引时直接应用age = 30的条件，而不是在找到所有姓张的记录后再进行过滤。

重要考虑事项

索引覆盖的一个关键要求是查询中的所有列都必须在索引中。如果查询引用了索引中没有的列，那么就无法实现索引覆盖。

索引下推可以显著减少因为回表操作而导致的性能开销。它通过在存储引擎层面进行更多的数据过滤，减少了服务器层面的工作量。

在设计索引时，应该考虑查询模式，以便最大限度地利用索引覆盖和索引下推的优势。

回表

回表查询是数据库操作中的一个术语，特别是在使用索引进行数据检索时。它发生在使用非聚集索引（Secondary Index）进行查询时，索引中只包含了索引列的副本以及指向对应主键的引用。当查询语句中需要返回的列不在索引列上时，即使通过索引定位了相关行，仍然需要回表获取其他列的值。这意味着数据库需要进行两次查找：一次在非聚集索引上定位数据，一次在聚集索引或主键索引上获取完整的数据行。

代码示例

在MySQL中，如果有一个表，其中id是主键，而name有一个普通索引。当执行如下查询时：