首发于 Java架构度寒冬

Mysql索引：图文并茂，深入探究索引的原理和使用

不秃顶的程序员

前言

相信每个IT界大佬，简历上少不了Mysql索引这个关键字，但如果被问起来，你能说出多少干货呢？先看下面几个问题测试一下吧：

这些问题说不明白？不要慌！请带着问题向下看。

1 索引原理探究

什么是数据库索引？先来个官方一些的定义吧。

在关系数据库中，索引是一种单独的、物理的数对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

这段话有点绕，其实把索引理解为图书目录，就非常好理解了。

如果我们想在图书中查找特定内容，在没有目录的情况下只能逐页翻找。与此类似，当执行下面这样一条SQL语句时，假如没有索引，数据库如何查找到相对应的记录呢？

SELECT * FROM student WHERE name='叶良辰'

搜索引擎只能扫描整个表的每一行，并依次对比判断name的值是否等于“叶良辰”。我们知道，单纯的内存运算是很快的，但从磁盘中取数据到内存中是相对慢的，当表中有大量数据时，内存与磁盘交互次数大大增加，这就导致了查询效率低下。

1.1 B树与B+树

相对于cpu和内存操作，磁盘IO开销很大，非常容易成为系统的性能瓶颈，因此计算机操作系统做了一些优化：

当一次IO时，将相邻的数据也都读取到内存缓冲区内，而不是仅仅读取当前磁盘地址的数据。因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

为什么索引能提升数据库查询效率呢？根本原因就在于索引减少了查询过程中的IO次数。那么它是如何做到的呢？使用B+树。下面先简单了解一下B树和B+树。

B树，即平衡多路查找树（B-Tree），是为磁盘等外存储设备设计的一种平衡查找树。

叶子结点是离散数学中的概念。一棵树当中没有子结点（即度为0）的结点称为叶子结点，简称“叶子”。叶子是指出度为0的结点，又称为终端结点。

但是，为什么是B+树而不是B树呢？原因有两点：

1.2 聚簇索引与非聚簇索引

首先，为了方便理解，我们先了解一下聚集索引（clustered index）和非聚集索引（secondary index，也称辅助索引或普通索引）。这两种索引是按存储方式进行区分的。

聚集索引（clustered）也称聚簇索引，这种索引中，数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表的物理顺序只有一种情况，因此对应的聚集索引只能有一个。如果某索引不是聚集索引，则表中的行物理顺序与索引顺序不匹配，与非聚集索引相比，聚集索引有着更快的检索速度。

如果不好理解，请看下面这个表：

表中id和物理地址是保持一致顺序的，id较大的行，其物理地址也比较靠后。因为聚集索引的特性，它的建立有一定的特殊要求：

大家还记得，自增主键和uuid作为主键的区别么？由于主键使用了聚集索引，如果主键是自增id，那么对应的数据一定也是相邻地存放在磁盘上的，写入性能比较高。如果是uuid的形式，频繁的插入会使innodb频繁地移动磁盘块，写入性能就比较低了。

1.3 索引原理图示

下面用一个通过主键索引查找数据的案例演示一下索引的原理。假如有student表如下，id上建立了聚集索引，name上建立非聚集索引：

1.3.1 聚簇索引

当我们执行下面的语句时，

SELECT name FROM student WHERE id=2

查询过程如下图所示：

用语言描述一下，是这样的：

我们知道，磁盘I/O相对于内存运算（尤其内存中的主键是有序排列的，利用二分查找等算法效率非常高）耗时高得多，因此在数据库查询中，减少磁盘访问时数据库的性能优化的主要手段。

而分析上面过程，发现整个查询只需要3次磁盘I/O操作（其实InnoDB引擎是将根节点常驻内存的，第1次磁盘I/O操作并不存在）和3次内存查找操作。相对于不使用索引的遍历式查找，大大减少了对磁盘的访问，因此查找效率大幅提高。但是，因为索引树要与表中数据保持一致，因此当表发生数据增删改时，索引树也要相应修改，导致写数据比没有索引时开销大一些。

1.3.2 非聚簇索引

好，聚集索引看完后，再看非聚集索引。

如上图，多加一个索引，就会多生成一颗非聚簇索引树。因此，索引不能随意增加。在做写库操作的时候，需要同时维护这几颗树的变化，导致效率降低！

另外，仔细观察的人一定会发现，不同于聚集索引，非聚集索引叶子节点上不再是真实数据，而是存储了索引字段自身值和主键索引。因此，当我们执行以下SQL语句时：

SELECT id,name FROM student WHERE name='叶良辰';

整个查询过程与聚集索引的过程一样，只需要扫描一次索引树（n次磁盘I/O和内存查询），即可拿到想要的数据。

但是，如果查询name索引树没有的数据时，情况就不一样了：

SELECT score FROM student WHERE name='叶良辰';

注意看上图中的红色箭头，因为扫描完name索引后，Mysql只能获取到对应的id和name，然后用id的值再去聚集索引中去查询score的值。这个过程相对于聚集索引查询的效率下降，可以理解了吧。

这就是通常所说的回表或者二次查询：使用聚集索引查询可以直接定位到记录，而普通索引通常需要扫描两遍索引树，即先通过普通索引定位到主键值，在通过聚集索引定位到行记录，这就是所谓的回表查询，它的性能比扫描一遍索引树低。

既然普通索引会导致回表二次查询，那么有什么办法可以应对呢？建立联合索引！

1.3.3 联合索引

所谓联合索引，也称多列所谓，就是建立在多个字段上的索引，这个概念是跟单列索引相对的。联合索引依然是B+树，但联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。

例如在a和b字段上建立联合索引，索引结构将如下图所示：

一目了然，当我们再执行SELECT score FROM student WHERE name='叶良辰';时，可以直接通过扫描非聚集索引直接获取score的值，而不再需要到聚集索引上二次扫描了。

最左前缀匹配

联合索引中有一个重要的课题，就是最左前缀匹配。

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

这是为什么呢？我们再仔细观察索引结构，可以看到索引key在排序上，首先按a排序，a相等的节点中，再按b排序。因此，如果查询条件是a或a和b联查时，是可以应用到索引的。如果查询条件是单独使用b，因为无法确定a的值，因此无法使用索引。

假如在table表的a,b,c三个列上建立联合索引，简要分类分析下联合索引的最左前缀匹配。

首先看等值查询：

1、全值匹配查询时（where子句搜索条件顺序调换不影响索引使用，因为查询优化器会自动优化查询顺序），可以用到联合索引

SELECT * FROM table WHERE a=1 AND b=3 AND c=2
SELECT * FROM table WHERE b=3 AND c=4 AND a=2

2、匹配左边的列时，可以用到联合索引

SELECT * FROM table WHERE a=1
SELECT * FROM table WHERE a=1 AND b=3

3、未从最左列开始时，无法用到联合索引

SELECT * FROM table WHERE b=1 AND b=3

4、查询列不连续时，无法使用联合索引（会用到a列索引，但c排序依赖于b，所以会先通过a列的索引筛选出a=1的记录，再在这些记录中遍历筛选c=3的值，是一种不完全使用索引的情况）

SELECT * FROM table WHERE a=1 AND c=3

再看范围查询：

1、范围查询最左列，可以使用联合索引

SELECT * FROM table WHERE a>1 AND a<5;

2、精确匹配最左列并范围匹配其右一列（a值确定时，b是有序的，因此可以使用联合索引）

SELECT * FROM table WHERE a=1 AND b>3;

3、精确匹配最左列并范围匹配非右一列（a值确定时，c排序依赖b，因此无法使用联合索引，但会使用a列索引筛选出a>2的记录行，再在这些行中条件 c >3逐条过滤）

SELECT * FROM table WHERE a>2 AND c>5;

索引的原理探究到此结束，这部分内容堪称最难啃的骨头。不过，能坚持读下来的朋友，你的收获也一定良多。接下来的内容就轻松愉悦多了。

2 索引的正确使用姿势

索引的优点如下：

既然索引这么好，那么我们是不是尽情使用索引呢？非也，索引优点明显，但相对应，也有缺点：

因此，使用索引时要兼顾索引的优缺点，寻找一个最有利的平衡点。

2.1 索引的类型区分

以InnoDB引擎为例，Mysql索引可以做如下区分。

首先，索引可以分为聚集索引和非聚集索引，它们的区别和含义在前文有大幅介绍，此处不再赘述。

其次，从逻辑上，索引可以区分为：

索引在实际使用上分为单列索引和多列索引。

单列索引：单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。

例如在student 表中的 address 字段上建立名为 index_addr 的单列索引，address 字段的数据类型为 VARCHAR(20)，索引的数据类型为 CHAR(4)。SQL 语句如下：

CREATE INDEX index_addr ON student(address(4));

这样，查询时可以只查询 address 字段的前 4 个字符，而不需要全部查询。

**多列索引也称为复合索引或组合索引。**相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。

多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

下面在 student 表中的 name 和 address 字段上建立名为 index_na 的索引，SQL 语句如下：

CREATE INDEX index_na ON tb_student(name,address);

该索引创建好了以后，查询条件中必须有 name 字段才能使用索引。

一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

2.2 索引的查看

查看索引的语法格式如下：

SHOW INDEX FROM <表名>

查询结果说明如下：

2.3 索引的创建

创建索引有3种方式：

1、CREATE INDEX直接创建：

可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

语法说明如下：

例如，在student表name字段上创建索引：

创建普通索引使用的关键字，例如在student表name字段上创建一个普通索引index_name

2、 CREATE TABLE时创建

索引也可以在创建表（CREATE TABLE）的同时创建。在 CREATE TABLE 语句中添加以下语句。例如创建student表时在name字段添加索引：

3、 ALTER TABLE时创建

ALTER TABLE 语句也可以在一个已有的表上创建索引。例如在student表name字段上创建一个普通索引index_name：

2.4 索引失效场景

创建了索引并不意味着高枕无忧，在很多场景下，索引会失效。下面列举了一些导致索引失效的情形，是我们写SQL语句时应尽量避免的。

1、条件字段原因

2、<>、NOT、in、not exists

当查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。否则，索引定位困难（结合我们查字典的例子去理解），执行计划此时可能更倾向于全表扫描，这类的查询条件有：<>、NOT、in、not exists

3、查询条件中使用OR

如果条件中有or，即使其中有条件带索引也不会使用(因此SQL语句中要尽量避免使用OR)。要想使用OR，又想让索引生效，只能将OR条件中的每个列都加上索引。

4、查询条件使用LIKE通配符

SQL语句中，使用后置通配符会走索引，例如查询姓张的学生（SELECT * FROM student WHERE name LIKE '张%'），而前置通配符(SELECT * FROM student WHERE name LIKE '%东')会导致索引失效而进行全表扫描。

5、索引列上做操作（计算，函数，（自动或者手动）类型装换）

有以下几种例子：

6、在索引列上使用mysql的内置函数，索引失效

例如，SELECT * FROM student WHERE create_time

7、索引列数据类型不匹配

例如，如果age字段有索引且类型为字符串（一般不会这么定义，此处只是举例）但条件值为非字符串，索引失效，例如SELECT * FROM student WHERE age=18会导致索引失效。

8、索引列使用IS NOT NULL或者IS NULL可能会导致无法使用索引

B-tree索引IS NULL不会使用索引，IS NOT NULL会使用，位图索引IS NULL、IS NOT NULL都会使用索引。

最后，对索引的使用做一个总结吧：

作者：云深i不知处
原文链接： https://blog.csdn.net/mu_wind/article/details/110128016

发布于 2020-12-10 17:42

MySQL

索引

Java 程序员

文章被以下专栏收录

Java架构度寒冬

java高级程序猿①578486082，学习领资料