MySQL：MySQL索引种类详解

一、前言

  MySQL中的索引是帮助数据库系统高效获取数据的数据结构。索引通过保存表中一列或多列的值以及相应的指向表中物理数据的指针来提高检索速度。

二、索引的定义

  1. 索引是数据库管理系统中的一个数据结构，用于快速查找和定位数据。

  2. 索引通常与表关联，并基于表中的一列或多列创建。

  3. 索引以某种方式指向数据，使得数据库系统可以基于索引快速定位到数据。

三、索引的分类

MySQL支持多种类型的索引，主要包括：

普通索引（Non-unique Index）：最基本的索引类型，没有唯一性的限制，允许在定义索引的列中插入重复值和空值。

代码示例：

CREATE TABLE users (  
    id INT NOT NULL AUTO_INCREMENT,  
    name VARCHAR(255) NOT NULL,  
    age INT,  
    INDEX idx_age (age)  
);


或者你也可以在表创建后添加普通索引：

ALTER TABLE users ADD INDEX idx_age (age);

唯一索引（Unique Index）：确保索引列中的值是唯一的，但允许有空值。一个表可以有多个唯一索引。

CREATE TABLE users (  
    id INT NOT NULL AUTO_INCREMENT,  
    name VARCHAR(255) NOT NULL,  
    email VARCHAR(255) NOT NULL,  
    UNIQUE INDEX idx_email (email)  
);


或者你也可以在表创建后添加唯一索引：

ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

主键索引（Primary Key Index）：特殊的唯一索引，一个表只能有一个主键，不允许有空值。主键索引的创建通常是在建表的时候指定某列为主键。

CREATE TABLE users (  
    id INT NOT NULL AUTO_INCREMENT,  
    name VARCHAR(255) NOT NULL,  
    email VARCHAR(255) NOT NULL,  
    PRIMARY KEY (id)  
);

联合索引（Composite Index 或 Multi-column Index）：基于表中的多个列创建的索引。查询时，只有当查询条件使用了联合索引中的最左边列时，索引才会被使用。

CREATE TABLE users (  
    id INT NOT NULL AUTO_INCREMENT,  
    first_name VARCHAR(255) NOT NULL,  
    last_name VARCHAR(255) NOT NULL,  
    age INT,  
    INDEX idx_name_age (first_name, last_name, age)  
);

最左前缀原则：查询条件中必须使用联合索引的最左列，索引才会被使用。

  在这个例子中：first_name, last_name, age | first_name, last_name | first_name，是可以走索引的，但是如果你只按照last_name或age进行查询，这个索引则不会被使用。

全文索引（Fulltext Index）：用于在文本字段上创建，用于查找文本中的关键字。

mysql5.6.4版本

CREATE TABLE articles (  
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,  
    title VARCHAR(200),  
    body TEXT,  
    FULLTEXT(title, body)  
) ENGINE=InnoDB;  
  
-- 查询全文索引  
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('keyword');

空间索引（Spatial Index）：用于地理空间数据类型。
mysql5.7及之后的版本

CREATE TABLE geom (g GEOMETRY NOT NULL, SPATIAL INDEX(g)) ENGINE=InnoDB;  
  
-- 插入空间数据（例如，一个点）  
INSERT INTO geom (g) VALUES (ST_GeomFromText('POINT(1 1)'));  
  
-- 使用 MBRContains() 函数查询空间数据  
SELECT * FROM geom WHERE MBRContains(ST_GeomFromText('LINESTRING(0 0, 2 2)'), g) = 1;

四、聚簇索引和非聚簇索引

  聚簇索引（Clustered Index） ：根据主键（PRIMARY KEY）构建的B+树索引，叶子节点直接包含数据记录。

特点：
  1. 数据记录按照主键的顺序进行排序和存储。

  2. 表的物理数据存储顺序与主键索引的顺序一致。

  3. 表中只能有一个聚簇索引，因为数据的物理顺序只能有一种。

  InnoDB存储引擎的表总是根据主键来组织数据（即使没有显式定义主键，InnoDB也会选择一个唯一且非空的列作为主键，或者生成一个隐藏的聚簇索引）。

  非聚簇索引（Non-clustered Index 或 Secondary Index）：非聚簇索引（也称为二级索引或辅助索引）是根据非主键列构建的索引，叶子节点包含的是指向主键的指针（而不是直接包含数据记录）。

特点：

  1. 索引中的顺序与数据文件中的记录物理顺序无关。

  2. 表中可以有多个非聚簇索引。

  3. 查询时，首先通过非聚簇索引找到主键的值，然后再通过主键索引找到具体的数据记录，这个过程称为“回表”。

回表示例
  假设我们有一个名为users的表，其中包含id（主键）、name和age三个列。
  如果我们在id列上建立了聚簇索引，那么users表中的数据将按照id的顺序进行排序和存储。
  如果我们还在age列上建立了非聚簇索引，那么这个索引的叶子节点将包含age的值和对应id的指针。当我们根据age查询数据时，MySQL会先通过非聚簇索引找到对应的id，然后再通过聚簇索引找到具体的数据记录。

总结
  1.聚簇索引决定了表数据的物理存储顺序，并且表中只能有一个。

  2.非聚簇索引的叶子节点包含的是指向主键的指针，并且表中可以有多个非聚簇索引。

  3.查询时，如果使用了非聚簇索引，可能需要通过“回表”操作来获取完整的数据记录。

五、索引的数据结构

  MySQL的索引数据结构是用于加速数据检索的关键组件。不同的索引类型和数据结构在查询性能和适用场景上有所不同。关于MySQL中常见的一些索引数据结构及其特点的详细介绍请查看《MySQL索引的数据结构》