主键索引和非主键索引的区别

4分钟阅读

来源：go-interview / Mysql

在MySQL中，主键索引和非主键索引（普通索引）都是用于提高查询效率的机制，但它们有不同的特性和用途。以下是它们的主要区别：

1. 主键索引（Primary Key Index）

定义：主键索引是一种特殊的索引，它在表中唯一标识每一行数据。每个表只能有一个主键索引。

特性：

  • 唯一性：主键索引要求主键列的值必须唯一，不能为NULL。
  • 自动创建：当你创建表时，如果定义了主键，MySQL会自动创建主键索引。
  • 聚集索引：InnoDB存储引擎中的主键索引通常是聚集索引（Clustered Index）。这意味着表的数据实际上是按主键的顺序存储在磁盘上的。其他索引会包含主键值作为其键的一部分。
  • 效率：由于主键索引是唯一的，因此在查找、插入和删除操作时具有很高的效率。

使用场景：

  • 用于唯一标识表中的每一行数据。
  • 常用于需要唯一性约束的列，如用户ID、订单号等。

示例：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

2. 非主键索引（普通索引）

定义：非主键索引是指除了主键索引之外的所有索引。它们用于加速对表的查询操作，但不具有主键索引的唯一性要求。

特性：

  • 非唯一性：非主键索引的列值可以重复，也可以为NULL（除非显式指定UNIQUE约束）。
  • 非聚集索引：在InnoDB存储引擎中，非主键索引是非聚集索引（Secondary Index）。非主键索引存储的是索引列的值和主键值的组合。数据的实际存储是按主键排序的，非主键索引会通过主键来访问数据。
  • 灵活性：可以在表中创建多个非主键索引，用于加速不同的查询条件。

使用场景：

  • 用于提高特定查询条件的性能，如频繁用于WHERE子句中的列。
  • 当需要根据非主键列快速查找数据时使用。

示例：

CREATE INDEX idx_name ON users (name);

主要区别总结

  • 唯一性：主键索引要求唯一性，非主键索引不要求唯一性。
  • 数量：每个表只能有一个主键索引，但可以有多个非主键索引。
  • 索引类型：主键索引通常是聚集索引，而非主键索引通常是非聚集索引。
  • 数据存储：主键索引直接决定了表的数据存储顺序（对于InnoDB），而非主键索引则通过主键值访问数据。

主键索引和非主键索引各有其独特的作用和适用场景。在设计数据库表时，需要根据具体的查询需求和数据特性选择合适的索引策略。

反向链接