前言

文章由本人通过互联网自学、整理、总结的教程，仅作为学习笔记记录，并分享给有需要的同学。

索引概述

在我理解看来，索引是具有高效查询的一种数据结构，也可以理解为满足特定查找算法的数据结构，这种数据结构以某种方式指向数据。

索引特点

索引结构

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

上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持情况

注意： 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

B-Tree

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。 以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5 个指针：

知识小贴士: 树的度数指的是一个节点的子节点个数。

我们可以通过一个数据结构可视化的网站来演示：https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+Tree

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一 下其结构示意图：

我们可以看到，两部分：

• 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
• 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

最终我们看到，B+Tree 与 B-Tree相比，主要有以下三点区别：

• 所有的数据都会出现在叶子节点。
• 叶子节点形成一个单向链表。
• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

上述我们所看到的结构是标准的B+Tree的数据结构，接下来，我们再来看看MySQL中优化之后的 B+Tree。

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点 的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

Hash

MySQL中除了支持B+Tree索引，还支持一种索引类型—-Hash索引。

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在 hash表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可 以通过链表来解决。

特点

• Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
• 无法利用索引完成排序操作
• 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

存储引擎支持

• 在MySQL中，支持hash索引的是Memory存储引擎。

• 而InnoDB中具有自适应hash功能，hash索引是 InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

为什么InnoDB存储引擎选择使用B+tree索引结构?

• 相对于二叉树，层级更少，搜索效率高；
• 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储 的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；这里说的性能大部分是指查询性能。
• 相对Hash索引，B+tree支持范围匹配及排序操作；

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

聚集索引&二级索引

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

聚集索引选取规则

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引和二级索引的具体结构如下：

• 聚集索引的叶子节点下挂的是这一行的数据 。
• 二级索引的叶子节点下挂的是该字段值对应的主键值、主键Id。

当我们执行如下的SQL语句时，具体的查询过程如下：

具体过程如下:

• 由于是根据name字段进行查询，所以先根据name=’Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
• 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最 终找到10对应的行row。
• 最终拿到这一行的数据，直接返回即可。

这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取 数据的方式，就称之为回表查询。

索引语法

创建索引

1

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

查看索引

1

SHOW INDEX FROM TABLE_NAME;

删除索引

1

DROP INDEX INDEX_NAME ON TABLE_NAME;

演示操作

创建表

1
2
3
4
5
6
7
8
9
10
11

create table tb_user(
    id int primary key auto_increment comment '主键',
    name varchar(50) not null comment '用户名',
    phone varchar(11) not null comment '手机号',
    email varchar(100) comment '邮箱',
    profession varchar(11) comment '专业',
    age tinyint unsigned comment '年龄',
    gender char(1) comment '性别 , 1: 男, 2: 女',
    status char(1) comment '状态',
    createtime datetime comment '创建时间'
) comment '系统用户表';

创建索引

1
2

CREATE INDEX idx_user_name ON tb_user(name);
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

创建联合索引

1

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

查看索引

1

show index from tb_user;

SQL性能分析

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信 息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

1
2
3

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

1
2
3
4

Com_delete: 删除次数
Com_insert: 插入次数
Com_select: 查询次数
Com_update: 更新次数

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以 查询为主，那么就要考虑对数据库的索引进行优化了。

通过SQL执行的频率，就能知道是以查询为主还是增删改为主，如果是以查询为主,我们可以查看慢查询日志来分析。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL语句的日志。

MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。

1
2
3
4
5
6
7

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF    |
+----------------+-------+
1 row in set (0.00 sec)

开启慢查询

在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

1
2
3
4

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息

1

systemctl restart mysqld

再次查看 show variables like 'slow_query_log';

可以看到为ON了

查看日志文件: /var/lib/mysql/localhost-slow.log。

测试

慢查询设置为2秒,为了达到2秒以上,可以使用navicat Premium 16进行数据生成,生成几百万条即可

当执行一条查询语句时间超过2秒以上时，我们再去查看/var/lib/mysql/localhost-slow.log。

慢查询的作用主要是定位，查询效率比较低的SQL，从而进行针对性的优化。

profile

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling 参数，能够看到当前MySQL是否支持profile操作：

1
2

SELECT @@have_profiling;			## 查看是否支持Profile操作
select  @@profiling;				## 查看Prifile是否开启

可以看到，当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句在 session/global级别开启profiling：

1

SET profiling = 1;

开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去 了。 我们直接执行如下的SQL语句：

1
2
3
4

select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';
select count(*) from tb_sku;

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

1
2
3
4
5
6

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

查看每一条SQL的耗时情况:

查看指定SQL各个阶段的耗时情况:

可以看到select * from tb_user where name = '白起';这sql语句查询的时间是比较久的,因为他是先通过的二级索引查找,找到对应的ID值,在通过ID查找的所有数据。

explain

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行 过程中表如何连接和连接的顺序。

语法:

1
2

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

Explain 执行计划中各个字段的含义:

利用explain我们可以查看sql查询语句是否有走索引,

索引使用规范

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始， 并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

举例

在tb_user表中 profession 、 age 、status 组建了联合索引为idx_user_pro_age_sta，可以看到Seq_in_index 列的顺序从上往下依次为1-3

分别执行一下查询语句：

1
2
3

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

三次执行过程结果分析：

以上的这三组测试中，我们发现只要联合索引最左边的字段 profession存在，索引就会生效，只不 过索引的长度不同。 而且由以上三组测试，我们也可以推测出profession字段索引长度为36、age 字段索引长度为2、status字段索引长度为4。

分别执行一下查询语句：

1
2

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

而通过上面的这两组测试，我们也可以看到索引并未生效，原因是因为不满足最左前缀法则，联合索引最左边的列profession不存在,所以那type为ALL走的是全局搜索。

1

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

上述的SQL查询时，存在profession字段，最左边的列是存在的，索引满足最左前缀法则的基本条件。但是查询时，跳过了age这个列，所以后面的列索引是不会使用的，也就是索引部分生效，所以索引的长度就是36。

思考：

1

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

可以看到也走了索引，所以条件得顺序并不影响，同样也是满足最左前缀法则。

最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是 第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

1
2

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

当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为38，就说明范围查询右边的status字 段是没有走索引的。

当范围查询使用>= 或 <=时，走联合索引了，索引的长度为42，就说明所有的字段都是走索引的。

所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 < 。

索引失效情况

索引列运算

不要在索引列上进行运算操作， 索引将失效。

在tb_user表中，除了前面介绍的联合索引之外，还有一个索引，是phone字段的单列索引。

当根据phone字段进行等值匹配查询时, 索引生效。

1

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

当根据phone字段进行函数运算操作之后，索引失效。

1

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

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效。

1
2

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

1
2

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

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

由于下面查询语句中，都是根据profession字段查询，符合最左前缀法则，联合索引是可以生效的， 我们主要看一下，模糊查询时，%加在关键字之前，和加在关键字之后的影响。

1
2
3

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

经过上述的测试，我们发现，在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字 前面加了%，索引将会失效。

or连接条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

1
2

explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

1
2

explain select * from tb_user where profession is null;
explain select * from tb_user where profession is not null;

在查询SQl时，MYSQL会评估是走索引快，还是走全表扫描快，如果全表 扫描更快，则放弃索引走全表扫描。 因此，is null 、is not null是否走索引，得具体情况具体 分析，并不是固定的。

假设
表中有1000条数据，age字段有15条是大于10的数据，此时MYSQL大概率是会走索引的，因为只有一部分的数据满足条件，所以MYSQL评估走索引可能更快

如果大于10的数据有900条，那MYSQL大概率是会走全表扫描不会走索引的，因为大多树数据都满足条件

SQL提示

tb_user表数据如下

这里可以看到，profession有两个索引，一个联合索引一个单列索引

执行SQL

1

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

测试结果，我们可以看到，possible_keys中 idx_user_pro_age_sta,idx_user_pro 这两个 索引都可能用到，最终MySQL选择了idx_user_pro_age_sta索引。这是MySQL自动选择的结果。

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优 化操作的目的。

use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进 行评估）。

1

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

ignore index ： 忽略指定的索引。

1

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

force index ： 强制使用索引。

1

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

覆盖索引

尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。

使用select * 很容易出现回表查询

前缀索引

故名意思就是把某个字段的前缀作为索引进行查询，通常用来处理大文本类型的如：varchar，text，longtext等，这些文本类型的字段如果不做前缀处理，那么会让索引变得很大，查询时，浪费大量的磁盘IO操作，影响查询效率，此时可以只将字符串的一部分前缀，建 立索引，这样可以大大节约索引空间，从而提高索引效率。

语法

1

create index idx_xxxx on table_name(column(n)) ;

前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值， 索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

1
2

select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

索引设计原则

• 针对于数据量较大，且查询比较频繁的表建立索引。
• 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
• 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间， 避免回表，提高查询效率。
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增 删改的效率。
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时，它可以更好地确定哪个索引最有效地用于查询。