索引（index）是一种帮助 MySQL 快速定位数据的数据结构（有序）。

优点：

• 提高数据检索效率，降低磁盘 IO 成本。

• 对索引数据进行排序可以避免 CPU 的额外开销。

缺点：

• 索引需要占用磁盘空间。

• 会降低数据的更新效率。

MySQL 的索引在存储引擎层实现，不同的存储引擎可能使用不同的索引结构，主要包含以下几种实现：

不同存储引擎对索引结构的支持情况：

索引结构

B+tree

要了解 B+tree，要从最简单的二叉树开始：

在极端情况下，如果插入的数据是有序的，二叉树的查找时间复杂度会降低为 O(n)：

此时需要使用平衡二叉树或红黑树来解决这个问题：

红黑树或平衡二叉树都可能需要在插入/删除节点后对树进行旋转操作，以保持树的“平衡”，区别在于前者可以通过颜色标记（红黑）来降低可能的旋转操作频率，以实现算法优化。

即使是使用红黑树或平衡二叉树，在节点过多的情况下，也会导致树的层级过多，查询效率偏低。使用 B tree 可以显著降低树的层级：

这个示例是一个最大度数（max-degree）为 5（5阶）的 b-tree。

n 阶的 b -tree 中，每个节点可以最多有 n-1 个 key，有 n 个指针。当超过 n-1 个 key 时，节点会向上分裂成 1 个父节点和 2 个子节点。

关于 b-tree 生成过程的演示，可以观看这个视频。

B+树是在B树的基础上发展的，它和B树的区别是：

• 所有的数据都在叶子节点

• 叶子节点行程一个单向链表

关于 B+树的生成过程演示，可以观看这个视频。

MySQL 在标准 B+树 的基础上，在相邻叶子节点上增加了一个反向指针，让叶子节节点形成了双向链表，可以提高排序效率。

在 MySQL 中，B+树索引的每个节点都使用一个逻辑存储单元页（Page）进行存储，因此使用 B+树 而不是 B树 具有额外的好处，即非叶子节点不需要存储数据，可以将存储空间都用于存储 key 和指针，这就让 B+ 树可以有更高的最大度数，以及更低的树层级（通常不会超过3层）。

hash

hash 索引利用 hash 表实现：

优点：

• 查询效率高，时间复杂度可以看做O(1)，优于 B+tree。

缺点：

• 只能用于值比较（=，in 等），不支持范围查询（between，>，<，...）

• 无法利用 hash 索引进行排序

在 MySQL 中，Memory 引擎支持 hash 索引，此外 innoDB 具有自适应 hash 功能，可以在指定条件下根据 B+tree 索引自动构建 hash 索引。

索引分类

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引。

• 如果不存在主键，使用第一个唯一索引作为聚集索引。

• 如果没有主键，且没有唯一索引，InnoDB 生成一个 rowid 作为隐藏的聚集索引。

如果 SQL 查询涉及二级索引，需要先通过二级索引查询到对应的主键值，再通过聚合索引用主键值查询到对应的行数据，这个过程叫做回表查询，详细过程演示可以观看这个视频。

语法

创建索引：

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...)

查看索引：

SHOW INDEX FROM table_name;

删除索引：

DROP INDEX index_name ON table_name;

SQL 性能分析

查看不同类型 SQL 执行频率

可以通过以下命令查看当前数据库不同 SQL 语句的执行频率：

SHOW [GLOBAL|SESSION] STATUS LIKE 'Com_______';

比如：

show global status like 'Com_______';

查询结果：

慢查询日志

MySQL 会将执行时间过长（默认为 10s）的 SQL 记录到慢查询日志，可以通过查看慢查询日志对相关 SQL 进行优化以提升性能。

默认 MySQL 不会开启慢查询日志，可以通过以下命令查看慢查询日志是否开启：

show variables like 'slow_query_log';

要开启 MySQL 的慢查询日志，需要修改 MySQL 的配置文件（/etc/my.cnf.d/mysql-server.cnf），在其中添加：

# 开启慢查询日志
slow_query_log=1
# 超过2秒的SQL作为慢查询进行记录
long_query_time=2

修改好后重启服务：

sudo systemctl restart mysqld

可以通过之前提到的命令确认是否已成功开启了慢查询日志。

生成的慢查询日志位于/var/lib/mysql目录下，文件名为xxx-slow.log。比如：

sudo cat 192-slow.log
/usr/libexec/mysqld, Version: 8.0.41 (Source distribution). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

可以通过导入一个 1000 万数据的表人为制造一个慢查询进行验证。

1000 万数据可以从这里获取。

创建表：

CREATE TABLE `tb_sku` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `sn` varchar(100) NOT NULL COMMENT '商品条码',
  `name` varchar(200) NOT NULL COMMENT 'SKU名称',
  `price` int(20) NOT NULL COMMENT '价格（分）',
  `num` int(10) NOT NULL COMMENT '库存数量',
  `alert_num` int(11) DEFAULT NULL COMMENT '库存预警数量',
  `image` varchar(200) DEFAULT NULL COMMENT '商品图片',
  `images` varchar(2000) DEFAULT NULL COMMENT '商品图片列表',
  `weight` int(11) DEFAULT NULL COMMENT '重量（克）',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `category_name` varchar(200) DEFAULT NULL COMMENT '类目名称',
  `brand_name` varchar(100) DEFAULT NULL COMMENT '品牌名称',
  `spec` varchar(200) DEFAULT NULL COMMENT '规格',
  `sale_num` int(11) DEFAULT '0' COMMENT '销量',
  `comment_num` int(11) DEFAULT '0' COMMENT '评论数',
  `status` char(1) DEFAULT '1' COMMENT '商品状态 1-正常，2-下架，3-删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

在 MySQL 客户端中通过以下命令进行导入：

load data local infile '/home/icexmoon/download/sql/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';

需要开启 local_infile 参数（默认关闭）：SET GLOBAL local_infile = ON;。

分批导入tb_sku1~tb_sku5。

事实上导入速度就很慢，会被记录进慢查询日志。

执行查询：

select count(*) from tb_sku;

慢查询中的记录：

show profile

MySQL 提供一个show profile 命令可以查看 SQL 的详细时间消耗情况。

要使用该命令，需要先查看数据库是否支持：

select @@have_profiling;

如果支持，还需要查看是否开启相关参数：

select @@profiling;

如果没有开启，需要开启：

set session profiling = 1;

开启后执行需要统计执行时长的 SQL，比如：

select count(*) from tb_sku;

查看 SQL 的执行时长：

mysql> show profiles;
+----------+-------------+---------------------------------+
| Query_ID | Duration    | Query                           |
+----------+-------------+---------------------------------+
|        1 |  0.00406225 | select @@profiling              |
|        2 |  0.00473225 | show variables like 'profiling' |
|        3 | 15.82208000 | select count(*) from tb_sku     |
+----------+-------------+---------------------------------+

如果要查看某个 SQL 的执行详细时长情况：

mysql> show profile for query 3;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000736 |
| Executing hook on transaction  |  0.000042 |
| starting                       |  0.000045 |
| checking permissions           |  0.000037 |
| Opening tables                 |  0.000170 |
| init                           |  0.000414 |
| System lock                    |  0.000135 |
| optimizing                     |  0.000039 |
| statistics                     |  0.000358 |
| preparing                      |  0.000099 |
| executing                      | 15.817544 |
| end                            |  0.000067 |
| query end                      |  0.000032 |
| waiting for handler commit     |  0.000056 |
| closing tables                 |  0.000046 |
| freeing items                  |  0.000944 |
| logging slow query             |  0.001224 |
| cleaning up                    |  0.000094 |
+--------------------------------+-----------+

可以看到 SQL 执行时每个步骤的执行用时情况。

explain

下面演示所需的表结构和数据可以从这里获取。

使用 explain/desc 命令可以查看一个 SQL 的执行计划：

explain select count(*) from student;

像展示的那样，可以在任意 SELECT 语句前添加explain/desc命令。

返回结果包含以下信息：

• id，SQL 执行顺序，id 越大越先执行，如果 id 相同，执行顺序从上到下。

• select_type，SELECT 语句类型：

  • SIMPLE，简单表，不使用表连接或子查询

  • PRIMARY，主查询，即最外层的查询

  • UNION，UNION 中的第二个或后面的子查询语句

  • SUBQUERY，SELECT/WHERE 之后的子查询语句

• table，查询使用的表

• type，表示连接类型，性能由好到差依次是：NULL、system、const、eq_ref、ref、range、index、all。

• possible_key，可能使用的索引

• key，实际使用的索引

• key_len，索引使用的字节数

• rows，为了完成查询而读取的数据行数，在 innodb 引擎的表中是一个估计值。

• filtered，返回的数据行数/为了完成查询而读取的数据行数的百分比，值越大越好，最佳是 100。

• Extra，其它信息

id

通常来说，对于联表查询，id 是相同的：

explain select s.*,c.*
from student s,student_course sc, course c
where s.id=sc.studentid and sc.courseid=c.id;

结果：

从逻辑上讲，三张表的查询先后顺序并不重要，调整顺序也可以完成查询，所以它们的 id 相同。

如果是嵌套子查询：

explain select *
from student
where id in (select student_course.studentid from student_course where courseid=(select id from course where course.name='Java'));

结果：

course 表是最内层的子查询，其 id 是 3，所以最先执行。

type

通常来说，虽然 type=NULL 表示最佳性能，但无法达到。因为这表示查询不需要使用表，比如：

explain select 1+1;

type=system 也不常见，因为它表示查询使用的是系统表。

通常最佳的性能是 type=const，这表示查询使用了唯一索引，其性能接近常数 O(1)。

比如，为tb_user表的phone字段添加一个唯一索引，并进行查询：

explain select * from tb_user
where phone='17799990006';

如果查询使用的是普通索引，比如为tb_user表的name字段添加一个普通索引：

explain select *
from tb_user
where name='赵云';

对于普通索引，有可能出现查询结果为多个的情况，其时间复杂度是 O(1)+O(n)，在这里就是 type=ref。

index 表示一种较差性能的查询，它说明虽然查询使用了索引，但对索引进行了完整遍历，其性能只略微优于全表扫描（all）。

比如：

explain select count(*) from tb_user;

虽然这里可以使用主键索引，但为了计算整张表的数据行数，依然要对完整索引进行遍历才能知道。

性能最差的查询是 type=all，这表示将会进行全表扫描。

索引使用原则

最左前缀法则

如果索引包含多列（联合索引），查询条件要包含索引最左边的列，否则不会使用索引。此外，如果查询条件不包含索引中间的某列，索引将部分失效（不会使用该列和后边列的索引）。

通过演示进行说明，创建一个联合索引：

create index idx_profession_age_status
    on tb_user (profession, age, status);

查询条件包含索引全部的列时：

explain select * from tb_user
where profession='软件工程' and age=31 and status='0';

使用了索引，长度是 54。

如果只包含前两个字段：

explain select * from tb_user
where profession='软件工程' and age=31;

同样会使用索引，索引长度49，用于索引字段 status 的索引长度是5（54-49），没有用于这次查询。

如果仅包含第一个索引字段：

explain select * from tb_user
where profession='软件工程';

同样使用索引，但没有使用 age 和 status 字段的索引，所以索引长度是 47。

如果查询条件不包含第一个索引字段：

explain select * from tb_user
where age=31 and status='0';

查询将不会使用索引，只能进行全表扫描（type=all）。

相应的，如果查询条件缺少索引中间的某个字段：

explain select * from tb_user
where profession='软件工程' and status='0';

会使用索引，但部分索引（age 和 status）会失效。

索引列运算

如果在条件语句中对索引列进行运算，索引将失效。

比如：

explain select *
from tb_user
where phone='17799990006';

一般查询时会使用 phone 字段上的唯一索引 idx_phone。

如果要查询手机号最后两位是指定值的用户：

explain select *
from tb_user
where substring(phone, 10,2)='06';

此时不会使用索引，是全表扫描。

字符串比较不使用引号

对字符串类型的列进行比较时，需要使用引号，如果不使用，索引会失效。

比如：

select *
from tb_user
where phone=17799990006;

虽然phone字段是varchar类型，但是这样不使用引号，依然可以查询到正确结果。但是如果查看 SQL 执行计划：

explain select *
from tb_user
where phone=17799990006;

没有使用索引，是全表扫描。

头部模糊匹配

对索引列进行头部模糊匹配时，不会使用索引。

比如：

explain select * from tb_user
where profession like '%工程';

没有使用索引。

这很好理解，索引是基于 B+树实现的，而 B+树在检索时需要对树节点中的 key 与检索值进行大小比较，而字符串左边使用通配符是没法进行大小比较的。

or 条件缺少索引

用 or 连接多个查询条件时，任意的查询条件缺少索引，会导致整合查询不会使用索引。

这同样很好理解，无论其他条件是否可以使用索引，只要其中一个条件没有可使用的索引，就需要为了这个条件的查询结果进行全表扫描，此时对其它条件使用索引进行查询是没有意义的（无论如何都需要全表扫描）。

比如：

explain select * from tb_user
where phone='17799990005' or age=38;

虽然 phone 字段有索引，但 age 字段并没有可用索引，所以整个查询不会使用索引，是全表扫描。

解决的方式也简单，确保每个 or 条件都有可用索引，在这里只需要为 age 字段添加索引：

create index idx_age
            on tb_user (age)

再次查看执行计划：

使用了两个索引。

数据分布影响

并不是说可以使用索引查询时就一定会使用索引，MySQL 的优化器会进行评估，如果使用索引时比全表扫描更慢，就不会使用索引，而是进行全表扫描。

比如：

explain select * from tb_user
where phone>='17799990000';

此时不会使用索引，会全表扫描，原因是tb_user表中phone字段最小的值就是17799990000，这里实际上需要返回全部数据，MySQL 会在评估后认为此时使用索引反而效率低于全表扫描，所以不会使用索引。

再比如：

explain select * from tb_user
where phone>='17799990006';

此时返回的虽然不是全表数据，而是大部分数据，但 MySQL 依然认为全表扫描更快，所以依然不使用索引。

如果返回结果只包含少部分数据：

explain select * from tb_user
where phone>='17799990019';

此时会使用索引。

is null 或 is not null 是否会使用索引实际上也是受此影响。

比如在一般情况下，字段的值大部分都是非 null 的，null 值只有少量数据，因此使用 is null 查询会使用索引：

explain select *
from tb_user
where profession is null;

相反的是，如果查询非空值，会全表扫描：

explain select *
from tb_user
where profession is not null;

但这并非固定的，如果表中该字段大部分都是null：

update tb_user
set profession=null where id!=20;

显然，此时查询非空结果（is not null）会使用索引，而查询空结果（is null）会全表扫描。

SQL 提示

如果查询时有多个索引可以使用，MySQL 会自己挑选一个，比如 tb_user 表有两个索引：

  KEY `idx_pro_age_status` (`profession`,`age`,`status`),
  KEY `idx_pro` (`profession`)

在使用profession字段进行查询时：

explain select * from tb_user
where profession='金属材料';

这两个索引都可以使用：

MySQL 选择使用联合索引。

可以通过 SQL 提示修改这种默认行为：

• use index，建议 MySQL 使用某个索引

• ignore index，让 MySQL 忽略（不使用）某个索引

• force index，强制 MySQL 使用某个索引。

比如：

explain select * from tb_user use index (idx_pro)
where profession='金属材料';

现在 MySQL 可能（不一定）改用我们建议的索引：

也可以让 MySQL 忽略联合索引，效果是类似的：

explain select * from tb_user ignore index (idx_pro_age_status)
where profession='金属材料';

还可以强制 MySQL 使用单列索引：

explain select * from tb_user force index (idx_pro)
where profession='金属材料';

覆盖索引

如果查询结果只包含主键和该次查询使用的索引中的列，就不需要回标查询，索引就可以提供返回结果。

比如：

explain select id,profession,age,status
from tb_user
where profession='软件工程' and age>10 and status='0';

这里的Using where; Using index 表示使用的索引idx_pro_age_status就可以提供数据，不需要回表查询。

如果查询结果超过索引覆盖的列：

explain select id,name,profession,age,status
from tb_user
where profession='软件工程' and age>10 and status='0';

就需要进行回表查询，相应的，这里的额外信息变成了Using index condition。

从这个角度上讲，要尽量避免使用select *，否则大概率需要进行回表查询，除非创建一个包含所有列（不包含主键）的联合索引（这种行为本身就是需要避免的）。

前缀索引

如果需要索引的表字段是较长的字符串（varchar）或大文本（text）时，创建的索引文件本身可能会体积较大，此时进行查询时就需要消耗较大的磁盘 IO 将其读取到内存。

对于这种问题，可以使用前缀索引，只针对字符串或文本的前 n 个字符建立索引，以缩小索引文件体积，起到减少磁盘 IO 和提高性能的作用。

前缀长度（n）的选取可以通过选择性来决定，选择性表示某个字段不可重复的值对总数据的比值。其最大为 1，表示每个值都不相同，值越大越好。

比如：

select count(distinct tb_user.email)/count(*)
from tb_user;
-- 结果为 1

如果前缀长度取 10：

select count(distinct substring(tb_user.email,1,10))/count(*)
from tb_user;
-- 结果为 1

前缀长度取 8：

select count(distinct substring(tb_user.email,1,8))/count(*)
from tb_user;
-- 结果为 0.9583

前缀长度取 6：

select count(distinct substring(tb_user.email,1,6))/count(*)
from tb_user;
-- 结果为 0.9583

前缀长度取 5：

select count(distinct substring(tb_user.email,1,5))/count(*)
from tb_user;
-- 结果为 0.9583

前缀长度取 4：

select count(distinct substring(tb_user.email,1,4))/count(*)
from tb_user;
-- 结果为 0.9167

此时选择性进一步下降，说明以 4 为前缀长度进行索引效果会很差。因此综合考虑，如果要让索引的查询性能最佳，前缀长度应该取 10，如果要让查询性能和 IO 性能均衡，前缀长度应该取 5。

创建前缀长度为 5 的前缀索引：

create index idx_email on tb_user(email(5));

查看索引：

show index from tb_user;

idx_email索引的 sub_part 是 5。

单列索引与联合索引

尽量使用联合索引而非单列索引，这样查询效率更高，且可以通过覆盖索引避免回表查询。

索引设计原则

• 针对数据量较大，且查询比较频繁的表建立索引。

• 针对常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

• 如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引。

• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，以提高查询效率。

• 要控制索引的数量，索引越多，维护索引结构的代价也越大，会影响增删改的效率。

• 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束，当优化器知道每列是否包含 NULL 时，它可以更好地确定哪个索引最有效地用于查询。

参考资料

• 黑马程序员 MySQL数据库入门到精通