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

2020-12-29 15:36:57 浏览数 (1)

前言

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

  • 索引是怎么提高查询效率的？可以为了提高查询效率增加索引么？
  • mysql索引系统采用的数据结构是什么？
  • 为什么要使用B 树？
  • 聚集索引相对于非聚集索引的区别？
  • 什么是回表？
  • 什么是索引覆盖？
  • 什么是最左匹配原则？
  • 索引失效场景有哪些，如何避免？

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

1 索引原理探究

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

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

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

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

代码语言：javascript复制
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），是为磁盘等外存储设备设计的一种平衡查找树。

B树简略示意图：

观察上图可见B树的两个特点：

  1. 树内的每个节点都存储数据
  2. 叶子节点之间无指针连接

B 树简略示意图：

再看B 树相对于B树的两个特点：

  1. 数据只出现在叶子节点
  2. 所有叶子节点增加了一个链指针

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

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

  1. B树每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点能存储的key的数量很小，要保存同样多的key，就需要增加树的高度。树的高度每增加一层，查询时的磁盘I/O次数就增加一次，进而影响查询效率。而在B Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B 树的高度。
  2. B 树的叶子节点上有指针进行相连，因此在做数据遍历的时候，只需要对叶子节点进行遍历即可，这个特性使得B 树非常适合做范围查询。

1.2 聚簇索引与非聚簇索引

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

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

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

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

  1. 在Innodb中，聚簇索引默认就是主键索引。
  2. 如果表中没有定义主键，那么该表的第一个唯一非空索引被作为聚集索引。
  3. 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。

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

1.3 索引原理图示

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

1.3.1 聚簇索引

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

代码语言：javascript复制
SELECT name FROM student WHERE id=2

查询过程如下图所示：

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

  1. 先找到根节点所在磁盘块，读入内存。（第1次磁盘I/O操作）
  2. 在内存中判断id=3所在区间(0,8)，找到该区间对应的指针1（第1次内存查找）
  3. 根据指针1记录的磁盘地址，找到磁盘块2并读入内存（第2次磁盘I/O操作）
  4. 在内存中判断id=3所在区间(0,4)，找到该区间对应的指针2（第2次内存查找）
  5. 根据指针2记录的磁盘地址，找到磁盘块4并读入内存（第3次磁盘I/O操作）
  6. 在内存中查找到id=2对应的数据行记录（第3次内存查找）

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

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

1.3.2 非聚簇索引

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

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

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

代码语言：javascript复制
SELECT id,name FROM student WHERE name='叶良辰';

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

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

代码语言：javascript复制
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子句搜索条件顺序调换不影响索引使用，因为查询优化器会自动优化查询顺序 ），可以用到联合索引

代码语言：javascript复制
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、匹配左边的列时，可以用到联合索引

代码语言：javascript复制
SELECT * FROM table WHERE a=1
SELECT * FROM table WHERE a=1 AND b=3

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

代码语言：javascript复制
SELECT * FROM table WHERE b=1 AND b=3

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

代码语言：javascript复制
SELECT * FROM table WHERE a=1 AND c=3

再看范围查询：

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

代码语言：javascript复制
SELECT * FROM table WHERE a>1 AND a<5

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

代码语言：javascript复制
SELECT * FROM table WHERE a=1 AND b>3;

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

代码语言：javascript复制
SELECT * FROM table WHERE a>2 AND c>5;

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

2 索引的正确使用姿势

索引的优点如下：

  • 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快数据的查询速度，这是使用索引最主要的原因。
  • 在实现数据的参考完整性方面可以加速表与表之间的连接。
  • 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。

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

  • 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
  • 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

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

2.1 索引的类型区分

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

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

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

  • 普通索引：普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。
  • 唯一索引：唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。创建唯一索引通常使用 UNIQUE 关键字。例如在student 表中的 id 字段上建立名为 index_id 的索引CREATE UNIQUE INDEX index_id ON tb_student(id);
  • 主键索引：主键索引就是专门为主键字段创建的索引，也属于索引的一种。主键索引是一种特殊的唯一索引，不允许值重复或者值为空。创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。
  • 空间索引：空间索引是对空间数据类型的字段建立的索引，空间索引主要用于地理空间数据类型 ，很少用到。
  • 全文索引：全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许在索引列中插入重复值和空值。

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

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

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

代码语言：javascript复制
CREATE INDEX index_addr ON student(address(4));

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

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

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

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

代码语言：javascript复制
CREATE INDEX index_na ON tb_student(name,address);

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

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

2.2 索引的查看

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

代码语言：javascript复制
SHOW INDEX FROM <表名>

查询结果说明如下：

2.3 索引的创建

创建索引有3种方式：

1、CREATE INDEX直接创建：

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

代码语言：javascript复制
CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

语法说明如下：

  • <索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
  • <表名>：指定要创建索引的表名。
  • <列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。
  • <长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
  • ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。

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

  • 普通索引：CREATE INDEX index_name ON student (name)
  • 唯一索引：CREATE UNIQUE index_name ON student (name)

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

  • 建表创建：CREATE TABLE student(id INT NOT NULL,name CHAR(45) DEFAULT NULL,INDEX(name));
  • ALTER TABLE：ALTER student ADD INDEX index_name (name)

2、 CREATE TABLE时创建

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

  • 主键索引：CREATE TABLE student(name CHAR(45) PRIMARY KEY);
  • 唯一索引：CREATE TABLE student(id INT NOT NULL,name CHAR(45) DEFAULT NULL,UNIQUE INDEX(name));
  • 普通索引：CREATE TABLE student(id INT NOT NULL,name CHAR(45) DEFAULT NULL,INDEX(name));

3、 ALTER TABLE时创建

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

  • 主键索引：ALTER TABLE student ADD PRIMARY KEY (name);
  • 唯一索引：ALTER TABLE student ADD UNIQUE INDEX index_name(name);
  • 普通索引：ALTER TABLE student ADD INDEX index_name(name);

2.4 索引失效场景

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

1、条件字段原因

  • 单字段有索引，WHERE条件使用多字段（含带索引的字段），例如 SELECT * FROM student WHERE name ='张三' AND addr = '北京市'语句，如果name有索引而addr没索引，那么SQL语句不会使用索引。
  • 多字段索引，违反最佳左前缀原则。例如，student表如果建立了(name,addr,age)这样的索引，WHERE后的第一个查询条件一定要是name，索引才会生效。

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、索引列上做操作（计算，函数，（自动或者手动）类型装换）

有以下几种例子：

  • 在索引列上使用函数：例如select * from student where upper(name)='ZHANGFEI';会导致索引失效，而select * from student where name=upper('ZHANGFEI');是会使用索引的。
  • 在索引列上计算：例如select * from student where age-1=17;

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都会使用索引。

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

  1. 索引有利于查询，但不能随意加索引，因为索引不仅会占空间，而且需要在写库时进行维护。
  2. 如果多个字段常常需要一起查询，那么在这几个字段上建立联合索引是个好办法，同时注意最左匹配原则。
  3. 不要在重复度很高的字段上加索引，例如性别。
  4. 避免查询语句导致索引失效，哪些情况会导致索引失效请见前文。
全文检索 云推荐引擎 分布式 数据结构 数据库

0 人点赞