InnoDB聚集索引与MyISAM非聚集索引

一、什么是索引

索引是一种数据结构，用于快速查询表中的特定记录。通过索引，可以显著减少数据库检索数据的时间，提高查询效率。

二、InnoDB聚集索引

2.1 聚集索引的定义

在InnoDB存储引擎中，聚集索引（Clustered Index）是一种将数据存储在叶子节点的索引结构。每张表只能有一个聚集索引，因为表的数据行只能按一种顺序存储。

2.2 聚集索引的结构

InnoDB的聚集索引使用B+树结构。每个节点包含键值和指向子节点的指针，叶子节点包含实际的数据行。当创建一个包含主键的表时，InnoDB会自动使用主键创建聚集索引。

示例

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(50),
    PRIMARY KEY (id)
) ENGINE=InnoDB;
​

在上述示例中，id是主键，因此InnoDB会创建一个基于 id列的聚集索引，数据行按照 id的顺序存储在B+树的叶子节点中。

2.3 聚集索引的优点

• 快速数据访问：由于聚集索引将数据行存储在叶子节点，可以更快地访问数据。
• 高效范围查询：聚集索引对范围查询特别高效，因为数据行按顺序存储。

2.4 聚集索引的缺点

• 插入速度较慢：插入新记录时，可能需要调整B+树的结构，导致插入速度较慢。
• 更新和删除代价高：更新或删除操作可能会导致B+树的重排，增加操作开销。

三、MyISAM非聚集索引

3.1 非聚集索引的定义

在MyISAM存储引擎中，非聚集索引（Non-Clustered Index）是一种索引结构，其中索引的叶子节点存储指向数据行的指针，而不是实际的数据行。每张表可以有多个非聚集索引。

3.2 非聚集索引的结构

MyISAM的非聚集索引同样使用B+树结构。与聚集索引不同，非聚集索引的叶子节点包含数据行的地址指针，而数据行存储在单独的文件中。

示例

CREATE TABLE products (
    product_id INT AUTO_INCREMENT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    PRIMARY KEY (product_id),
    KEY (product_name)
) ENGINE=MyISAM;
​

 

在上述示例中，product_id是主键，MyISAM会创建一个基于 product_id的非聚集索引，同时在 product_name列上创建一个额外的非聚集索引。

3.3 非聚集索引的优点

• 插入速度较快：插入新记录时，不需要调整数据行的物理顺序，因此插入速度较快。
• 更新和删除代价低：更新或删除操作只需修改索引指针，而不需要重排数据行。

3.4 非聚集索引的缺点

• 访问速度较慢：由于索引的叶子节点存储的是指向数据行的指针，检索数据时需要额外的IO操作。
• 范围查询效率低：非聚集索引对范围查询不如聚集索引高效，因为数据行在物理上是分散存储的。