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

 

自我总结
在 MySQL 中，主键索引和非主键索引是数据库索引的两种重要类型，它们在功能、使用方式和性能特点上存在明显区别，以下为你详细介绍并举例说明。

定义和特点

主键索引

• 定义：主键索引是一种特殊的唯一索引，每个表只能有一个主键索引。主键用于唯一标识表中的每一行记录，确保记录的唯一性和完整性。当创建主键时，MySQL 会自动创建对应的主键索引。
• 特点：
  • 唯一性：主键索引中的键值必须是唯一的，不允许出现重复值。
  • 非空性：主键字段的值不能为 NULL。
  • 加速查找：可以通过主键快速定位到特定的记录，提高查询效率。

非主键索引

• 定义：非主键索引是除主键索引之外的其他索引，也称为辅助索引。它可以基于一个或多个字段创建，用于提高特定查询条件下的数据检索速度。
• 特点：
  • 键值可重复：非主键索引的键值可以重复，不像主键索引那样要求唯一。
  • 可包含 NULL 值：非主键索引的字段可以包含 NULL 值。
  • 辅助查询：主要用于加速特定条件下的查询，如根据某个字段进行过滤、排序等操作。

数据结构和查询过程

主键索引

• 数据结构：在 InnoDB 存储引擎中，主键索引采用 B+ 树的数据结构。B+ 树的叶子节点存储了完整的行数据，也称为聚簇索引。
• 查询过程：当通过主键进行查询时，MySQL 直接在主键索引的 B+ 树中查找对应的键值，找到叶子节点后即可获取完整的行数据。

非主键索引

• 数据结构：同样采用 B+ 树的数据结构，但非主键索引的叶子节点存储的是主键值，而不是完整的行数据，也称为非聚簇索引。
• 查询过程：当通过非主键索引进行查询时，MySQL 先在非主键索引的 B+ 树中查找对应的键值，找到叶子节点后获取主键值，然后再通过主键值在主键索引的 B+ 树中查找完整的行数据，这个过程称为回表查询。

示例说明

创建表和索引

-- 创建一个示例表
CREATE TABLE users (
    id INT PRIMARY KEY,  -- 主键字段
    name VARCHAR(50),
    age INT,
    INDEX idx_name (name)  -- 非主键索引
);

-- 插入一些示例数据
INSERT INTO users (id, name, age) VALUES
(1, 'Alice', 25),
(2, 'Bob', 30),
(3, 'Alice', 35);

主键索引查询

-- 通过主键进行查询
SELECT * FROM users WHERE id = 2;

在这个查询中，MySQL 直接在主键索引的 B+ 树中查找 id 为 2 的记录，找到叶子节点后即可获取完整的行数据。由于主键索引的叶子节点存储了完整的行数据，所以查询速度非常快。

非主键索引查询

-- 通过非主键索引进行查询
SELECT * FROM users WHERE name = 'Alice';

在这个查询中，MySQL 先在非主键索引 idx_name 的 B+ 树中查找 name 为 'Alice' 的记录，找到叶子节点后获取对应的主键值（如 1 和 3），然后再通过主键值在主键索引的 B+ 树中查找完整的行数据，这个过程涉及回表查询，相对主键索引查询会慢一些。

总结

主键索引和非主键索引在 MySQL 中都起着重要的作用。主键索引用于唯一标识表中的记录，保证数据的完整性，查询时可以直接获取完整的行数据，速度较快；非主键索引用于加速特定条件下的查询，但查询时可能需要进行回表操作，性能相对较弱。在实际应用中，应根据具体的业务需求合理创建和使用索引，以提高数据库的查询性能。