MySQL索引优化：从原理到实战的全面指南

MySQL是现在最流行的关系型数据库，很多项目都在用。但是很多人对MySQL索引的理解只是停留在表面，知道加索引能提高查询速度，但是对索引的原理、类型、使用规则、优化技巧等等了解不深，导致经常出现索引没建好、查询慢、甚至索引失效的问题。

其实MySQL索引优化是数据库性能优化中最重要的一环，掌握了索引优化能大大提升数据库的查询性能。我做MySQL开发和优化很多年了，踩了很多坑也总结了很多经验，今天就来分享一下MySQL索引优化的全面指南。

一、索引是什么

首先我们要明白索引是什么。索引就像书的目录，我们想在一本很厚的书里找某个内容，如果没有目录，我们只能一页一页地翻，从第一页翻到最后一页才能找到我们要的内容，很慢。但是如果有目录，我们就能先在目录里找到我们要的内容在第几页，然后直接翻到那一页，很快就能找到我们要的内容。

MySQL的索引也是一样的道理。我们想在一张很大的表里查询某条数据，如果没有索引，MySQL只能全表扫描，从第一行扫描到最后一行才能找到我们要的数据，很慢。但是如果有索引，MySQL就能先在索引里找到我们要的数据在哪一行，然后直接去那一行取数据，很快就能找到我们要的数据。

所以索引的本质就是一种数据结构，用来帮助我们快速查询数据，避免全表扫描，提高查询效率。

二、索引的原理：B+树

MySQL最常用的索引数据结构是B+树，InnoDB和MyISAM存储引擎都用B+树作为索引结构。所以我们要理解B+树的原理，才能更好地理解和使用索引。

B+树是一种平衡多路搜索树，它的特点是：

1. 非叶子节点只存储键值和指针，不存储数据： B+树的非叶子节点只存索引键值和指向子节点的指针，不存实际的数据，所以一个节点能存很多键值，树的高度很矮，一般2-4层就能存几千万甚至上亿条数据，查询的时候只需要几次IO就能找到数据，很快。
2. 叶子节点存储所有的键值和数据，并且用链表连接： B+树的叶子节点存储所有的索引键值和对应的数据，并且叶子节点之间用双向链表连接，这样范围查询的时候只需要找到起点，然后沿着链表遍历就行，很方便很高效。
3. 平衡： B+树是平衡的，所有叶子节点都在同一层，查询任何数据都需要相同的IO次数，性能稳定。

因为B+树有这些特点，所以它很适合作为数据库索引的数据结构，查询快，范围查询方便，性能稳定。

InnoDB的索引分为聚簇索引和非聚簇索引（也叫二级索引、辅助索引）：

• 聚簇索引： InnoDB的聚簇索引的叶子节点存储的是整行的数据，也就是说数据本身就存在聚簇索引的叶子节点里，所以一张表只能有一个聚簇索引。InnoDB默认用主键作为聚簇索引，如果没有主键，会选一个唯一非空索引作为聚簇索引，如果也没有，会自动生成一个隐藏的主键作为聚簇索引。
• 非聚簇索引（二级索引）： 非聚簇索引的叶子节点存储的是索引键值和主键值，也就是说通过非聚簇索引查询先找到主键值，然后再用主键值去聚簇索引里找整行数据，这个过程叫回表。所以非聚簇索引查询需要两次索引查找，比聚簇索引慢一些。

理解了聚簇索引和非聚簇索引的区别，我们就能理解很多索引优化的原理和技巧了。

三、索引的类型

MySQL的索引有很多类型，下面介绍常用的几种。

1. 主键索引

主键索引是特殊的唯一索引，不允许有空值，一张表只能有一个主键索引。InnoDB的主键索引就是聚簇索引，查询性能最好，因为不需要回表，直接就能拿到整行数据。

建表的时候用PRIMARY KEY指定主键，比如：

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

2. 唯一索引

唯一索引要求索引列的值必须唯一，但是允许有空值，一张表可以有多个唯一索引。唯一索引能保证数据的唯一性，查询性能也很好。

用UNIQUE KEY或者CREATE UNIQUE INDEX创建，比如：

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(100),
    PRIMARY KEY (id),
    UNIQUE KEY uk_email (email)
);

3. 普通索引

普通索引是最基本的索引，没有什么限制，就是用来提高查询速度，一张表可以有多个普通索引。

用KEY或者INDEX或者CREATE INDEX创建，比如：

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    PRIMARY KEY (id),
    KEY idx_name (name),
    KEY idx_age (age)
);

4. 联合索引（复合索引）

联合索引是把多个字段组合在一起建一个索引，比如KEY idxnameage (name, age)。联合索引遵循最左前缀原则，查询的时候必须从索引的最左边的字段开始才能用到索引，后面的字段才能依次用到，这个后面会详细讲。

联合索引能提高多个字段组合查询的效率，也能减少索引的数量，是很常用的索引类型。

5. 全文索引

全文索引用来做全文搜索，适合大文本字段的模糊查询，比如文章内容等等。普通的LIKE查询在大文本上效率很低，全文索引能大大提高全文搜索的效率。

用FULLTEXT KEY创建，比如：

CREATE TABLE article (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    PRIMARY KEY (id),
    FULLTEXT KEY ft_content (content)
);

查询的时候用MATCH AGAINST语法，比如：

SELECT * FROM article WHERE MATCH(content) AGAINST('关键词');

四、索引的使用规则和注意事项

建了索引不代表查询就一定能用到索引，有很多情况会导致索引失效，下面介绍一些索引的使用规则和注意事项。

1. 最左前缀原则

联合索引遵循最左前缀原则。比如我们建了一个联合索引KEY idxab_c (a, b, c)，那么查询的时候只有包含a的查询才能用到这个索引，而且必须从a开始依次是b、c才能都用到。比如：

• WHERE a = 1，能用到索引的a部分。
• WHERE a = 1 AND b = 2，能用到索引的a和b部分。
• WHERE a = 1 AND b = 2 AND c = 3，能用到整个索引。
• WHERE b = 2，不能用到索引，因为没有从最左边的a开始。
• WHERE c = 3，不能用到索引。
• WHERE a = 1 AND c = 3，只能用到索引的a部分，c部分用不到，因为跳过了b。

所以建联合索引的时候要把查询频率高的字段放在左边，把范围查询的字段放在右边，这样能最大化索引的利用率。

2. 不要在索引列上做运算和函数

在索引列上做运算或者用函数会导致索引失效，因为索引存的是原始的字段值，不是运算或者函数处理后的值，所以MySQL无法用索引来快速查找，只能全表扫描。

比如：

-- 错误，索引失效
SELECT * FROM user WHERE YEAR(create_time) = 2017;
SELECT * FROM user WHERE id + 1 = 10;

-- 正确，能用到索引
SELECT * FROM user WHERE create_time >= '2017-01-01' AND create_time < '2018-01-01';
SELECT * FROM user WHERE id = 9;

所以查询的时候尽量不要在索引列上做运算和函数，要把运算和函数放在等号右边，或者改写查询条件。

3. 字符串要加引号

如果索引列是字符串类型，查询的时候值必须加引号，不然MySQL会做隐式类型转换，导致索引失效。

比如user表的phone字段是varchar类型，有索引：

-- 错误，索引失效，因为phone是字符串，13800138000是数字，会做隐式类型转换
SELECT * FROM user WHERE phone = 13800138000;

-- 正确，能用到索引
SELECT * FROM user WHERE phone = '13800138000';

所以字符串类型的字段查询的时候值一定要加引号，避免隐式类型转换导致索引失效。

4. LIKE查询不要以%开头

LIKE查询的时候如果以%开头会导致索引失效，因为索引是按前缀排序的，以%开头就无法利用索引的排序来快速查找，只能全表扫描。

比如：

-- 错误，索引失效
SELECT * FROM user WHERE name LIKE '%张三%';
SELECT * FROM user WHERE name LIKE '%张三';

-- 正确，能用到索引，前缀匹配
SELECT * FROM user WHERE name LIKE '张三%';

所以如果需要模糊查询尽量用前缀匹配，不要以%开头。如果必须要前后都模糊查询，可以考虑用全文索引或者搜索引擎比如Elasticsearch等等。

5. OR查询要注意

OR查询的时候如果OR两边的条件都有索引才能用到索引，如果有一边没有索引，整个查询都不会用到索引，会全表扫描。

比如：

-- 如果name有索引，age没有索引，整个查询都不会用到索引
SELECT * FROM user WHERE name = '张三' OR age = 18;

-- 正确，两边都有索引才能用到索引
SELECT * FROM user WHERE name = '张三' OR id = 18;

所以OR查询的时候要注意两边的条件都要有索引，不然会全表扫描。或者也可以把OR改成UNION分别查询再合并，这样能用到索引。

6. 不要用SELECT *

查询的时候尽量不要用SELECT *，要查询需要的字段，这样能减少数据传输，也能提高用到覆盖索引的概率。覆盖索引就是查询的字段都在索引里，不需要回表，直接就能从索引拿到数据，性能更好。

比如我们建了联合索引KEY idxnameage (name, age)，查询：

-- 能用到覆盖索引，不需要回表，因为name和age都在索引里
SELECT name, age FROM user WHERE name = '张三';

-- 需要回表，因为查了*，包含了索引外的字段
SELECT * FROM user WHERE name = '张三';

所以尽量查询需要的字段，不要用SELECT *，能提高性能，也能提高用到覆盖索引的概率。

7. 索引不是越多越好

很多人以为索引越多查询越快，其实不是。索引也有代价，索引会占用存储空间，而且插入更新删除数据的时候需要维护索引，会降低写操作的性能。所以索引不是越多越好，要合理建索引，只在查询频率高的字段上建索引，不要在很少查询的字段上建索引，也不要建重复的索引和冗余的索引。

一般一张表的索引数量控制在5个以内比较合适，太多了会影响写性能，也会让MySQL查询优化器选择索引的时候困惑，可能选不到最优的索引。

五、索引优化实战技巧

下面分享一些索引优化的实战技巧，能帮大家更好地优化索引，提升查询性能。

1. 用EXPLAIN分析查询执行计划

优化索引首先要会用EXPLAIN分析查询的执行计划，看看查询有没有用到索引、用到了哪个索引、扫描了多少行等等，这样才能知道查询慢在哪里怎么优化。

用法很简单，在SELECT前面加EXPLAIN就行：

EXPLAIN SELECT * FROM user WHERE name = '张三';

会输出执行计划的各个字段，重要的字段有：

• type： 访问类型，从好到差依次是system > const > eq_ref > ref > range > index > ALL，ALL就是全表扫描最差，要尽量避免，至少要达到range以上。
• key： 实际用到的索引，如果是NULL就是没用到索引。
• rows： 扫描的行数，越少越好。
• Extra： 额外信息，比如Using index表示用到了覆盖索引，Using where表示用了WHERE过滤，Using filesort表示用了文件排序需要优化，Using temporary表示用了临时表需要优化。

学会看EXPLAIN的结果是索引优化的基础，一定要掌握。

2. 联合索引的字段顺序很重要

联合索引的字段顺序很重要，遵循最左前缀原则，所以建联合索引的时候要把查询频率高的等值查询的字段放在左边，把范围查询的字段放在右边，这样能最大化索引的利用率。

比如我们经常有这样的查询：

SELECT * FROM user WHERE name = '张三' AND age > 18;

那么联合索引应该建KEY idxnameage (name, age)，而不是KEY idxagename (age, name)。因为name是等值查询，age是范围查询，等值查询放左边范围查询放右边，这样两个字段都能用到索引。如果反过来age放左边name放右边，因为age是范围查询，后面的name就用不到索引了，只能用到age部分，索引利用率低。

3. 利用覆盖索引避免回表

前面讲了覆盖索引就是查询的字段都在索引里，不需要回表直接就能从索引拿到数据，性能更好。所以我们要尽量利用覆盖索引避免回表。

比如我们经常查询用户的name和age，根据name查询：

SELECT name, age FROM user WHERE name = '张三';

那么我们可以建联合索引KEY idxnameage (name, age)，这样查询的时候name和age都在索引里，不需要回表直接就能拿到数据，性能很好。如果只建KEY idx_name (name)，那么查询age的时候需要回表去聚簇索引拿age，性能差一些。

所以对于经常查询的字段组合可以建联合索引，让查询能用到覆盖索引避免回表提升性能。

4. 分页优化

大表分页查询的时候偏移量大了会很慢，因为MySQL需要扫描前面的所有行然后丢弃，只返回后面的行。比如：

SELECT * FROM user ORDER BY id LIMIT 1000000, 10;

这个查询会扫描1000010行，然后丢弃前面的1000000行，只返回后面的10行，很慢。

优化方法是利用索引延迟关联，先在索引上找到需要的id，然后再回表查询数据。比如：

SELECT * FROM user u INNER JOIN (SELECT id FROM user ORDER BY id LIMIT 1000000, 10) t ON u.id = t.id;

这样先在索引上快速找到需要的10个id，然后再回表查询这10行的数据，不需要扫描前面的1000000行，性能大大提升。

还有一种方法是记录上一页的最后一个id，然后用WHERE id > 上一页最后id LIMIT 10，这样也能利用索引快速查询，不需要扫描前面的行，适合没有跳页的场景，比如瀑布流加载更多。

5. 排序优化

ORDER BY排序的时候如果能利用索引的顺序来排序，就不需要额外的排序操作，性能很好。如果不能利用索引排序，MySQL就需要用filesort文件排序，性能差，特别是数据量大的时候很慢。

所以ORDER BY的字段尽量要有索引，而且要符合索引的顺序，这样能利用索引排序避免filesort。

比如我们建了联合索引KEY idxnameage (name, age)，那么：

-- 能利用索引排序，不需要filesort
SELECT * FROM user WHERE name = '张三' ORDER BY age;

注意ORDER BY的方向要和索引的方向一致，不然也用不到索引排序。不过MySQL 8.0支持降序索引，能更好地支持降序排序。

6. 定期检查和优化索引

索引不是建了就一劳永逸了，要定期检查和优化索引。比如用EXPLAIN检查慢查询有没有用到索引，用SHOW INDEX查看索引的基数和使用情况，删除没用的索引和重复的索引，优化联合索引的字段顺序等等，这样才能保证索引一直高效。

可以开启MySQL的慢查询日志，记录慢查询，然后定期分析慢查询，优化索引和SQL，这样能持续提升数据库性能。

六、写在最后

MySQL索引优化：从原理到实战的全面指南。

MySQL索引优化是数据库性能优化中最重要的一环，掌握了索引的原理、类型、使用规则、优化技巧，能大大提升数据库的查询性能，让我们的应用又快又稳。

这篇文章分享了索引是什么、B+树原理、索引的类型、使用规则注意事项以及实战优化技巧，希望能帮大家更好地理解和使用MySQL索引。

当然索引优化是一个持续的过程，需要我们不断学习不断实践不断优化，才能让数据库保持最佳状态。

最后用一句话结尾：

"索引是数据库性能的基石，建对索引用对索引，能让你的数据库飞起来。"

祝大家的MySQL都又快又稳！