MySQL索引

目录

一、为什么需要索引？

1.1 创建索引会进行锁表操作吗？

1.2 什么是DDL？什么是DML？

二、索引的分类

2.1 聚簇索引（聚集索引）和非聚簇索引的区别？

2.1.0 聚簇索引

2.1.1 如果没有主键索引，聚簇索引等于哪种索引类型？

2.1.2 非聚簇索引

 2.1.3 扩展：MySQL中常见的三大引擎

2.1.4 扩展：什么是物理外键？什么是逻辑外键？

三 、索引底层原理

3.1 索引底层是如何实现的？

3.2 索引为什么要使用B+树，而不是使用其他数据结构？

四、索引覆盖、索引下推

4.0 什么是回表查询

4.1 索引覆盖 

4.2 索引下推

 4.3 什么是最左匹配？

五、如何排查索引失效问题？

5.1 索引失效场景

---

一、为什么需要索引？

索引主要是用于提高数据检索速度的一种机制，通过索引数据库可以快速定位到目标数据的位置，而不需要遍历整个数据集，它就像书籍的目录部分，有它的存在，可以大大加速查询的效率。

1.1 创建索引会进行锁表操作吗？

在 MySQL 5.6 之前，创建索引时会锁表，所以，在早期 MySQL 版本中一定要在线上慎用，因为创建索引时会导致其他会话阻塞(select 查询命令除外)。
但这个问题，在 MySQL 5.6.7 版本中得到了改变，因为在 MySQL 5.6.7 中引入了 Online DDL 技术(在线 DDL 技术)，它允许在创建索引时，不阻塞其他会话(所有的 DML操作都可以一起并发执行)。

1.2 什么是DDL？什么是DML？

DDL(Data Definition Language，数据库定义语言):用于定义和管理数据库的结构，它主要包括以下语句:

1. CREATE:用于创建数据库、表、索引、视图等对象,
2.  ALTER:用于修改数据库、表、索引、视图等已存在的对象的结构。
3. DROP:用于删除数据库、表、索引、视图等对象。
4. TRUNCATE:用于删除表中的所有数据，但保留表的结构。
5. RENAME:用于重命名数据库、表等对象

DML(Data Manipulation Language，数据操作语言):用于查询和修改数据，它主要包括以下语句：

1. INSERT:用于向表中插入新的数据行。
2. UPDATE:用于更新表中已存在的数据行。
3. DELETE:用于删除表中的数据行。
4. SELECT:用于从表中检索数据。虽然 SELECT 主要用于查询，但某些包含数据修改的扩展 SQL功能(如LIMIT、ORDER BY、GROUP BY 等)也属于 DML 的范畴。

二、索引的分类

MySQL中可以按照不同维度将其大致分为三大类：

按照字段特性可分为：主键索引，唯一索引，普通索引，全文索引。

按照物理特性结构分为：聚簇索引，非聚簇索引。、

按照索引数量分类：单列索引，联合索引。

2.1 聚簇索引（聚集索引）和非聚簇索引的区别？

2.1.0 聚簇索引

在InnoDB存储引擎中，每个表只能由一共聚集索引，其余的索引都是非聚集索引（也称为二级索引），聚簇索引的叶子节点存储的是数据本身，而非叶子节点存储的是索引（通常是主键），如下图所示：

2.1.1 如果没有主键索引，聚簇索引等于哪种索引类型？

在InnoDB中，如果表定义了主键，则主键索引是聚集索引，如果表没有定义主键，则第一个唯一的非空索引是聚集索引，如果都没有，那么InnoDB会隐式的创建一个隐藏的聚集索引。

例如，我们创建一张 student 表，它的构建 SQL 如下：

drop table if exists student;
create table student(
    id int primary key, 
    name varchar(16),
    class_id int not null, 
    index (class_id)
)engine=InnoDB;
-- 添加测试数据
insert into student(id,name,class_id) values(100,'张三',1),
    (200,'李四',2),(300,'王五',3);

以上 student 表中有一个聚簇索引（也就是主键索引）id，和一个非聚簇索引 class_id。 聚簇索引 id 对应存储结构如下图所示：

2.1.2 非聚簇索引

非聚集索引也叫二级索引，其叶子节点保存着索引字段和指向对应数据行的聚簇索引键，相当于主键 ID，通过这个主键可以找到对应的数据行。在查询中，如果使用的是非聚集索引，则需要先根据非聚簇索引查找到对应的聚簇索引键，再通过这个聚簇索引键查找数据行，这个过程叫做回表查询，因此他的查询速度相对于聚集索引要慢一些。

非聚簇索引的叶子节点存储的是聚簇索引键（通常是主键），非数据本身，如下所示：

MyISAM 索引和上面非聚簇索引略有不同，它的叶子节点存储的是数据的内存地址（上面是主键），如下所示：

 虽然这两个存储的不一样，但是由于叶子节点都不是存储数据本身，所以都是非聚簇索引。

 2.1.3 扩展：MySQL中常见的三大引擎

MySQL 有很多数据引擎，也叫存储引擎，所谓的存储引擎是指用于存储、检索、更新和删除数据的服务。

在 MySQL 中可以使用“show engines”来查询数据库的所有存储引擎，如下图所示：

在上述列表中，我们最常用的存储引擎有以下 3 种：

InnoDB:MySQL(5.5+)的默认存储引擎，支持事务处理、行级锁定和物理外键约束。

• 特性:提供良好的数据一致性、崩溃恢复和高并发性能。
• 使用场景:适用于需要事务支持和多用户读写操作的应用场景。

MyISAM:MySQL 早期的默认存储引擎，不支持事务和行级锁定（只支持表级锁），和物理外键。

• 特性:提供快速的读取速度和较小的数据存储文件。
• 使用场景:适用于只读或读多写少的应用场景，不需要事务的场景。

MEMORY:将表的数据存储在内存中，提供极快的访问速度，但是它不支持事务和外键。

• 特性:数据在服务器重启后会丢失。
• 使用场景:适用于临时表、缓存表或者需要快速查询的小型表。

如何查看存储引擎？

存储引擎的设置粒度是表级别的，也就是每张表可以设置不同的存储引擎，我们可以使用以下命令来查询某张表的存储引擎：

show create table t;

如图所示： 

如何设置存储引擎？

 在创建一张表的时候设置存储引擎：

修改一张已经存在表的存储引擎： 

2.1.4 扩展：什么是物理外键？什么是逻辑外键？

物理外键指的是使用foreign key 作为外键关联另一张的字段的连接方法，而且限定了引擎为InnoDB。

图中9就无法插入从表，因为在主表中的部门id是没有9的。另外需要注意的是，在构建物理外键的时候，从表的外键列，必须引用主表的主键或者唯一约束列。也就是说图中的部门id最好是主键id或者是unique key。

而逻辑外键，又叫做事实外键，是因为存在语法上的逻辑关联而产生的外键，例如left join，inner join等。

别人博客里面看到的，觉得写的很好：

我们从代码角度分析一下：

两张表使用物理外键进行关联：

1 已有一张表为Persons表，表结构为
P_Id LastName  FirstName Address City；
2 创建一个带有外键的Order表
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
 
此时使用navicat查看时，会更明显看到有个foreignkey的标识，
查询起来可以更方便的联表查询，通过外键可以直接调用另一张表的查询。

 两张表使用逻辑外键进行关联：

1 已有一张表为Persons表，表结构为
P_Id LastName  FirstName Address City；
2 创建一个带有外键的Order表
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int
)
 
此时使用navicat查看时，不会看到foreignkey的标识，此时两张表从数据库的角度看是毫无关联的。
其中Orders表的P_Id 与 Persions表的 P_Id 是有关联关系，增删改查上都是有关系的，而这种关系是通过代码逻辑层面进行控制。所以叫逻辑外键。

从维护上来看，逻辑外键由于需要使用代码来进行维护，好像更麻烦；物理外键无需代码维护直接使用mysql自身维护，好像更简单。

实际上物理外键是有性能问题的

1. 数据库需要维护外键的内部管理；
2. 外键等于把数据的一致性事务实现，全部交给数据库服务器完成；
3. 有了外键，当做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，而不得不消耗资源；
4. 外键还会因为需要请求对其他表内部加锁而容易出现死锁情况;

针对这里第四点的死锁情况，我们可以举例说明：

首先我们需要明白，

外键的主要作用是保证数据的一致性和完整性，并且减少数据冗余。
主要体现在以下两个方面：

阻止执行

• 从表插入新行，其外键值不是主表的主键值便阻止插入。
• 从表修改外键值，新值不是主表的主键值便阻止修改。
• 主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)。
• 主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。

级联执行

• 主表删除行，连带从表的相关行一起删除。
• 主表修改主键值，连带从表相关行的外键值一起修改。

这是前置知识，明白了这些，大概也可以猜出死锁是什么个情况了：

死锁是指两个或多个事务互相持有对方需要的资源，同时又在等待对方释放资源，从而导致所有事务都无法继续执行的情况。比如，

具体来说，当一个事务需要锁定某个表的一行数据时，如果这行数据的外键关联了另一个表的数据，那么它还需要铁定另一个表的某一行数据。而如果另一个事务同时需要锁定这两个表的数据，就可能会导致死锁的发生。

2.1.5 扩展：唯一索引和主键索引的区别？

主键索引和唯一索引都要求值唯一，但是它们有如下区别：

1. 一张表只能有一个主键，但可以创建多个唯一索引
2. 主键创建后一定包含一个唯一索引，唯一索引并一定是主键
3. 主键不能为null，唯一索引可以为null

三 、索引底层原理

3.1 索引底层是如何实现的？

MySQL索引的底层实现取决于存储引擎，但大部分的存储引擎的底层是通过B+树实现的（Memory是通过Hash索引实现的）。

以默认的存储引擎InnoDB为例，其底层是通过B+树实现的，如下所示：

B+ 树是一种自平衡的、多路搜索树，它的主要特征包含以下几点:

• 非叶子节点只存储键值和指向子节点的指针。
• 所有叶子节点(最底层的节点)都在同一个级别，并且包含所有的键值和对应的数据行指针或行数据。
• 所有叶子节点在同一层上，并通过双向链表连接，便于范围查询。 

3.2 索引为什么要使用B+树，而不是使用其他数据结构？

首先需要明确一点，树结构的查询效率肯定是比其他数据类型要高的，所以大方向肯定是树。

PS：虽然哈希效率查询也很高，但是没办法进行范围查询，所以也不适合作为大部分存储引擎的底层数据结构。

而树又分为二叉树搜索树和多叉搜索树，而二又搜索树的每个节点只有一个或两个子节点，这意味着查找一个元素可能需要多次 I/0 操作，而多路搜索树(如 B树和 B+ 树)的每个节点可以有多个子节点，这使得每个层级可以包含更多的数据，从而减少了查询过程中所需的 I/0 次数。而 I/0 次数是查询中最慢的操作，所以使用多叉搜索树比二叉树更适合做索引。

而作为的B+树相比于B树又有以下几点作为主要优势，所以它更适合做索引：

1. IO次数更少(查询效率更高):B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比即存储索引又存储数据的 B树来说，B+ 树的非叶子节点可以存放更多的索引，因此在查询时I/0 次数更少，查询效率更高。
2. 范围查询性能更高:B+ 树叶子节点之间用链表连接了起来，有利于范围査询;而 B树要实现范围查询，因此2只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/0 操作，范围査询效率不如 B+ 树。
3. 插入和删除性能更好:B+ 树有大量的冗余节点(所有非叶子节点都是冗余索引)，这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化。

四、索引覆盖、索引下推

4.0 什么是回表查询

create table xttblog(
    id int primary key, 
    age int not null, 
    name varchar(16),
    index (age)
)engine = InnoDB;

假设，现在我们要查询出 id 为 2 的数据。那么执行 select * from xttblog where ID = 2; 这条 SQL 语句就不需要回表。原因是根据主键的查询方式，则只需要搜索 ID 这棵 B+ 树。主键是唯一的，根据这个唯一的索引，MySQL 就能确定搜索的记录。

但当我们使用 age 这个索引来查询 age = 2 的记录时就要用到回表。select * from xttblog where age = 2; 原因是通过 age 这个普通索引查询方式，则需要先搜索 age 索引树，然后得到主键 ID 的值为 1，再到 ID 索引树搜索一次。这个过程虽然用了索引，但实际上底层进行了两次索引查询，这个过程就称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

4.1 索引覆盖 

索引覆盖和索引下推都是 MySQL 中用于优化查询性能的机制，但二者又完全不同。

索引覆盖

索引覆盖(Index Covering):是指查询语句可以完全通过索引来满足，而无需进一步访问表的数据。当一个查询仅需要从索引中获取所需的数据列，而不需要访问表的实际数据行时，就称为索引覆盖。通过索引覆盖，可以减少对磁盘和内存的读取，提高查询的性能。

例如，select id from table where age between 18 and 22，其中 id 为主键，而 age 为二级索引，这时的 SOL 只需要查询主键 id 的值，而 id 的值已经在 age 索引树上了，因此可以直接提供查询结果，不需要回表，这就叫做覆盖索引。

4.2 索引下推

索引下推：是指在索引的遍历过程中，对索引中的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数次数。

其实可以把索引下推看成我们对联表查询的优化。因为在下面这个例子中，由于联合索引中的name使用到了范围查询，由于最左匹配原则，停止了后续索引的匹配。但是有了索引下推机制的存在，可以很好的避免这种情况。

索引下推是MySQL5.6版本中引入的功能 

例如，以市民表的联合索引(name,age)为例，如果现在有一个需求:检索出表中“名字第一个字是张，而且年龄是 10 岁的男孩”，SQL 语句是这么写的:

select * from tuser where name like '张%' and age = 10 and ismale = 1;

这个语句在搜索索引树的时候，只能用“张”，找到第一个满足条件的记录 ID3，如下图所示:

在MySQL 5.6之前，查询流程如下所示，只能根据name查询到结构，然后开始回表匹配age和ismale，如下所示：

而到了MySQL5.6之后，因为有了索引下推技术，它会对索引中的字段先做判断，直接过滤掉不满足条件的记录，然后再进行回表查询，所有它的执行流程是这样的：

 4.3 什么是最左匹配？

最左匹配原则是指在使用多列联合索引时，索引可以从左到右按顺序匹配查询条件，并提供有效的索引访问。最左匹配原则要求，查询中的条件必须按照联合索引的顺序，从最左边的列开始出现，并且不能跳过任何中间的列。

需要注意的是，在最左匹配的过程中如果遇到范围查询(>、<、between、like)就会停止匹配，其中范围列可以用到索引，但是范围列后面的列无法用到索引，即索引最多用于一个范围列。

例如我们用(name，age)这个联合索引进行分析，索引结构如下图所示:

如果要査的是所有名字第一个字是“张”的人，你的 SOL 语句的条件是"where name like '张 %"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。生效原则来看以下示例，比如表中有一个联合索引字段 index(a,b,c):

• where a=1 只使用了索引 a。
• where a=1 and b=2 只使用了索引 a,b.
• where a=1 and b=2 and c=3 使用 a,b,c.
• where b=l or c=1不使用索引。
• where a=1 and c=3 只使用了索引 a。
• where a=3 and b like 'xx%' and c=3 只使用了索引 a,b。

最左匹配原则都是针对联合索引来说的，所以我们可以从联合索引的原理来了解最左匹配原则。

我们都知道索引的底层是一颗 B+ 树，那么联合索引当然还是一颗 B+ 树，只不过联合索引的键值数量不是一个，而是多个。构建一颗 B+ 树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建 B+ 树。例子：假如创建一个（a,b,c)的联合索引，那么它的索引树是这样的：

该图就是一个形如(a,b,c)联合索引的 b+ 树，其中的非叶子节点存储的是第一个关键字的索引 a，而叶子节点存储的是三个关键字的数据。这里可以看出 a 是有序的，而 b，c 都是无序的。但是当在 a 相同的时候，b 是有序的，b 相同的时候，c 又是有序的。

通过对联合索引的结构的了解，那么就可以很好的了解为什么最左匹配原则中如果遇到范围查询就会停止了。以 select * from t where a=5 and b>0 and c =1; #这样a,b可以用到（a,b,c），c不可以 为例子，当查询到 b 的值以后（这是一个范围值），c 是无序的。所以就不能根据联合索引来确定到底该取哪一行。 

五、如何排查索引失效问题？

可以使用SQL关键字：explain来排除某个SQL是否使用了索引，以此来实现索引失效排查的问题。

explain查询计划如下：

type:表示查询时使用的访问方法或策略，描述了 MySQL 在执行查询时如何访问数据。常见的 type 值包括:

• ALL:全表扫描，表示 MySQL 将扫描整个表来找到匹配的行。
• index:索引扫描，表示 MySOL将通过索引进行扫描，但可能需要回表访问数据行。
• range:范围扫描，表示 MySOL将使用索引的范围条件来定位匹配的行。
• ref:使用非唯一索引进行查找，返回匹配某个值的所有行。
• eq_ref:使用唯一索引进行查找，返回匹配某个值的单个行,
• const:使用常量值进行查找，通常是通过主键或唯一索引进行精确匹配。。
• NULL:无效或未知的访问类型。

key:表示查询时使用的索引。如果查询使用了索引，key 字段将显示使用的索引名称;如果查询没有使用索引，key 字段将显示 NULL。

也就说，当 explain 查询计划中的 key 不等于 NULL，并且 type 等于 index、range、ref、eq_ref、const 等（等于ALL）时都表示此语句执行了索引查询，也就是索引并未失效。 

5.0 如何排查慢SQL

当然explain也能用于排查慢SQL，慢 SQL 指的是 MySQL 中执行比较慢的 SQL，排查慢 SQL 最常用的方法是通过慢查询日志来查找慢 SQL。 MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的 SQL，就会被记录到慢查询日志中，long_query_time 的默认值为 10s，意思是运行超过 10s 以上的语句就会被当做慢 SQL 记录到日志中。

可以使用 SQL 命令来查看慢 SQL 记录功能是否开启，使用 mysql> show variables like '%slow_query_log%'; 来查询慢查询日志是否开启，执行效果如下图所示：

如果slow_query_log 的值为 OFF 时，表示未开启慢查询日志。

开启慢查询日志，可以使用如下 MySQL 命令：

mysql> set global slow_query_log=1

不过这种设置方式，只对当前数据库生效，如果 MySQL 重启也会失效，如果要永久生效，就必须修改 MySQL 的配置文件 my.cnf，配置如下：

slow_query_log =1

slow_query_log_file=/tmp/mysql_slow.log

如果 type=all 说明没走索引，此时就需要给查询慢的字段加上相应索引就可以提高查询效率。 当然，优化慢 SQL 需要综合考虑的因素有很多，比如索引、查询优化（减少联表查询等）、减少锁竞争等因素，所以具体的慢 SQL 优化，需要根据实际的业务场景再做优化决策。

5.1 索引失效场景

导致索引失效的场景有以下这些：

1. 未遵循最左匹配原则。
2. 使用列运算：例如使用 where age + 10 = 20 
3. 使用函数方法：查询的时候使用了任意MySQL提供的函数就会导致索引失效。
4. 类型转换：比如某字符串类型，查询的时候设置了int类型的值就会导致索引失效。例如 执行select * from users where username = 0. 这里username本来是varchar，但是这里使用int。
5. 使用 is not null：查询中使用is not null会导致索引失效，但是 is null会正常触发索引。
6. 错误的模糊匹配：例如 like'张%'，like‘%张’，like ‘%张%’，只有第一个会走索引。
7. 使用 or 操作符：当查询条件包含or连接时，索引也会失效。

为什么这些会索引失效，其实大致就是因为不确定性，比如is not null，不为空，那么到底结果是什么？不得而知，又或者是列运算，结果是什么？不知道，or操作符和模糊匹配更不用多说了。