MySQL--聚集索引、辅助索引、回表查询和覆盖索引的原理

在MySQL中，索引是提高查询性能的核心工具。理解聚集索引、辅助索引、回表查询和覆盖索引的原理，对于优化数据性能至关重要。以下是对这些概念的详细解释以及优化方法。

一、聚集索引（Clustered Index）

聚集索引决定了表中数据的物理存储顺序。每个表只能有一个聚集索引。（也叫聚簇索引）

特点

• 数据存储：聚集索引的叶子节点存储的是整行数据

• 默认索引：如果表定义了主键（PRIMARY KEY），MySQL会自动将主键作为聚集索引

• 物理顺序：表中的数据按照聚集索引的顺序存储

示例：

CREATE TABLE users (
    id INT PRIMARY KEY,  -- id列是聚集索引
    username VARCHAR(255),
    age INT
);

• 数据存储

  • 数据按照id列的值排序存储

  • 查询id列时，可以直接定位到数据行

优化建议

• 选择合适的主键：主键应选择唯一且递增的列（如自增ID），以减少插入数据时的页分裂

• 避免频繁更换主键：更新主键会导致数据行的物理位置发生变化，影响性能。

（当然很多也用uuid或者string形式的id当作主键，但由于目前MySQL默认引擎为InnoDB，而且InnoDB的数据结构为B+树，B+树的特点是叶子节点存储真实数据，而且从左到右叶子节点是有序的，uuid和Strng类型id的代入势必会导致新数据和老数据之间的id是无序的）

（关于页分裂是因为InnoDB存储引擎中，数据是以页为基础单位存储的，每一页通常为16KB，当16KB满了之后，结果发现又有在这个id范围内的id插入，就会导致超过页内存大小，这个时候就会页分裂）

（页分裂：当某个页中的数据超出容量限制（例如插入大量新数据或更新现有数据）时，MySQL会将当前页的数据分成两个页，新创建的页会接收部分数据。这一过程通常会导致性能开销，特别是在频繁写操作的场景下。 页合并：当删除或更新操作使页内数据过于稀疏时（例如利用率低于50%），MySQL可能会将该页的数据与相邻的页合并。这种操作虽然可以回收空间，但在执行过程中也会带来额外的性能消耗。）

（综上所述，页分裂和叶子节点有序的角度，id设为自增长，数据库的开销要小很多）

二、辅助索引（Secondary Index）

辅助索引是除聚集索引以外的其他索引，每个表可以有多个辅助索引

特点

• 数据存储：辅助索引的叶子节点存储是主键值（聚集索引的键值）

• 查询过程：通过辅助索引找到数据时，需要先找到主键值，再通过主键值查找整行数据（回表查询）

示例：

CREATE INDEX idx_username ON users(username);  -- 创建辅助索引

• 数据存储

  • idx_username索引的叶子节点存储的是username和对应的id值

  • 查询username时，先通过idx_username找到id，再通过id找到整行数据

优化建议：

• 选择性高的列：为选择性高的列（如唯一列或高基数列）创建辅助索引，以提高查询效率

• 避免过多索引：过多的辅助索引会增加操作的开销（如INSERT、UPDATE、DELETE）

三、回表查询（Row Lookup）

回表查询是指通过辅助索引找到主键值后，再通过主键值查找整行数据的过程

示例：

-- 查询username为'john_doe'的用户
SELECT * FROM users WHERE username = 'john_doe';

• 查询过程：

  1. 通过idx_username索引找到username='john_doe'对应的主键

  2. 通过主键在聚集索引中找到整行数据

优化建议：

• 减少回表查询：使用覆盖索引（Covering Index）避免回表查询

四、覆盖索引（Covering Index）

覆盖索引是指索引包含了查询所需的所有列，查询可以直接从索引中获取数据，而无需回表。

特点：

• 查询性能：覆盖索引可以显著提高查询性能，因为它避免了回表操作

• 索引设计：覆盖索引需要包含查询中使用的所有列

示例：

-- 创建覆盖索引
CREATE INDEX idx_username_age ON users(username, age);
​
-- 使用覆盖索引查询
EXPLAIN SELECT username, age FROM users WHERE username = 'john_doe';

• 执行计划分析：

  • EXPLAIN结果中的Extra列会显示Using index，表明查询使用了覆盖索引。

优化建议：

• 设计覆盖索引：根据查询需求，设计包含所有查询列的覆盖索引。

• 避免过多列：覆盖索引不应包含过多列，以免增加索引大小和维护开销。

五、回表查询的优化方法

回表查询会增加查询开销，尤其是在大数据量和高并发场景下。以下是优化回表查询的常见方法：

方法1：使用覆盖索引

通过覆盖索引避免回表查询

-- 原始查询（需要回表）
SELECT * FROM users WHERE username = 'john_doe';
​
-- 优化查询（使用覆盖索引）
CREATE INDEX idx_username_age ON users(username, age);
SELECT username, age FROM users WHERE username = 'john_doe';

方法2：减少查询列

只查询需要的列，避免查询整行数据。

示例：

-- 原始查询（查询整行数据）
SELECT * FROM users WHERE username = 'john_doe';
​
-- 优化查询（只查询需要的列）
SELECT username, age FROM users WHERE username = 'john_doe';

方法3：优化索引设计

根据查询需求设计合适的索引，避免不必要的回表查询。

示例：

-- 原始索引
CREATE INDEX idx_username ON users(username);
​
-- 优化索引（覆盖索引）
CREATE INDEX idx_username_age ON users(username, age);