MySQL索引详解----看这一篇就够了

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。
接下来，按照这些角度来说说各类索引的特点。

按数据结构分类

从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

每一种存储引擎支持的索引类型不一定相同，我在表中总结了 MySQL 常见的存储引擎 InnoDB、MyISAM 和 Memory 分别支持的索引类型。

InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

按物理存储分类

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

这两个区别在前面也提到了：

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

在创建表时，创建主键索引的方式如下：

CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);

唯一索引

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

在创建表时，创建唯一索引的方式如下：

CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);

建表后，如果要创建唯一索引，可以使用这面这条命令：

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

在创建表时，创建普通索引的方式如下：

CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);

建表后，如果要创建普通索引，可以使用这面这条命令：

CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

在创建表时，创建前缀索引的方式如下：

CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
);

建表后，如果要创建前缀索引，可以使用这面这条命令：

CREATE INDEX index_name
ON table_name(column_name(length));

按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

建立在单列上的索引称为单列索引，比如主键索引；
建立在多列上的索引称为联合索引；

通过将多个字段组合成一个索引，该索引就被称为联合索引。

比如，将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name)，创建联合索引的方式如下：

CREATE INDEX index_product_no_name ON product(product_no, name);

联合索引(product_no, name) 的 B+Tree 示意图如下（图中叶子节点之间我画了单向链表，但是实际上是双向链表，原图我找不到了，修改不了，偷个懒我不重画了，大家脑补成双向链表就行）。

可以看到，联合索引的非叶子节点用两个字段的值作为 B+Tree 的 key 值。当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 相同的情况下再按 name 字段比较。

也就是说，联合索引查询的 B+Tree 是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。

因此，使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2；
where c=3；
where b=2 and c=3；

上面这些查询条件之所以会失效，是因为(a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。

联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的 B+Tree，部分字段没有用到联合索引的 B+Tree 的情况。

这种特殊情况就发生在范围查询。联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

最左匹配原则

1. 最左匹配原则的定义‌

最左匹配原则是指在联合索引中，查询条件必须从索引的最左端开始，按照索引列的顺序依次匹配。如果查询条件没有从最左端开始，或者中间有列未匹配，索引将无法完全生效或部分失效。

2. 联合索引的结构与匹配过程

联合索引的底层是B+树，其节点按照索引列的顺序存储数据。例如，联合索引(a, b, c)的B+树会先按a排序，a相同的情况下按b排序，b相同的情况下再按c排序。查询时，数据库会依次匹配a、b、c，如果某列未匹配，后续列的索引将无法使用。

3. 最左匹配原则的应用场景

‌完全匹配‌：查询条件包含联合索引的所有列，且顺序一致。例如，索引(a, b, c)的查询WHERE a=1 AND b=2 AND c=3会完全利用索引。
‌部分匹配‌：查询条件从最左端开始，但未包含所有列。例如，WHERE a=1 AND b=2会利用索引的前两列，而WHERE a=1 AND c=3只能利用a列，c列无法通过索引加速。
‌范围查询‌：如果查询条件中包含范围查询（如>、<、BETWEEN），范围查询之后的列将无法使用索引。例如，WHERE a=1 AND b>2 AND c=3只能利用a和b列，c列无法通过索引加速。

4. 索引语法

4.1 创建索引

单列索引：

CREATE INDEX idx_name ON users(name);

复合索引：

CREATE INDEX idx_name_age ON users(name, age);

唯一索引：

CREATE UNIQUE INDEX idx_email ON users(email);

全文索引：

CREATE FULLTEXT INDEX idx_content ON articles(content);

4.2 删除索引

删除索引：

DROP INDEX idx_name ON users;

4.3 修改索引

修改索引：MySQL不支持直接修改索引，需要先删除旧索引，再创建新索引。

5. SQL性能分析

5.1 EXPLAIN命令

EXPLAIN：用于分析SQL查询的执行计划。
示例：

EXPLAIN SELECT * FROM users WHERE name = 'John';

输出字段：
- id：查询的标识符。
- select_type：查询的类型（如SIMPLE、PRIMARY、SUBQUERY等）。
- table：查询的表。
- type：访问类型（如ALL、index、range、ref等）。
- possible_keys：可能使用的索引。
- key：实际使用的索引。
- rows：扫描的行数。
- Extra：额外信息（如Using where、Using index等）。

5.2 慢查询日志

慢查询日志：记录执行时间超过指定阈值的查询。
启用慢查询日志：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 设置阈值为2秒

6. 索引使用

6.1 索引覆盖

索引覆盖：查询的列都包含在索引中，无需回表查询。
示例：

CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = 'John'; -- 索引覆盖

6.2 最左前缀原则

最左前缀原则：复合索引中，查询条件必须从最左列开始，否则索引失效。
示例：

CREATE INDEX idx_name_age ON users(name, age);
SELECT * FROM users WHERE name = 'John'; -- 使用索引
SELECT * FROM users WHERE age = 30; -- 索引失效

6.3 索引下推

索引下推：MySQL 5.6引入的优化技术，将WHERE条件推到存储引擎层执行。
示例：

SELECT * FROM users WHERE name = 'John' AND age > 20;

7. 索引设计原则

7.1 选择性高的列

选择性高的列：索引列的值越唯一，索引的效果越好。
示例：用户表的email列比gender列更适合创建索引。

7.2 避免过度索引

过度索引：过多的索引会增加写操作的开销，降低性能。
建议：只为常用的查询条件创建索引。

7.3 短索引

短索引：索引列的长度越短，索引的效率越高。
示例：对VARCHAR(255)列的前10个字符创建索引：

CREATE INDEX idx_name ON users(name(10));

7.4 复合索引的顺序

复合索引的顺序：将选择性高的列放在前面。
示例：

CREATE INDEX idx_age_name ON users(age, name); -- age的选择性高于name

8. 总结

索引是MySQL中优化查询性能的重要工具。通过理解索引的结构、分类、使用方法和设计原则，可以显著提升数据库的查询效率。在实际应用中，需要根据具体的查询需求和数据特点，合理设计和使用索引。