史上最全的Mysql索引教程

一、什么是索引

1.1 索引简介

索引是数据库中用来提高数据检索效率的数据结构。它类似于书籍的目录，可以帮助用户快速找到所需的数据，而不必扫描整个数据集。在数据库系统中，索引可以显著提高查询性能。

所谓的存储引擎，说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MSQL存储引擎有 MyISAM、InnoDB、Memory，其中 InnoDB 是在 MySQL 5.5 之后成为默认的存储引擎。

以下是简化版的mysql的结构图，其中，索引和数据是存储在存储引擎中的。

1.2 一条sql语句是如何执行的

我们先从mysql的结构层面上帝视角初步的了解一下sql语句是如何从一条sql语法的发送到数据返回的，我们可以初步的看下下面的执行流程图：

简单的来说就是客户端发送一个连接请求和sql语句给service层，service就有对应的对于sql语句的初步的处理过程，到最后就会由执行器发送执行调用存储引擎拿到数据返回给客户端，下面我们详细的介绍每一步都是在干什么。

1.2.1 连接器

第一步：客户端发起连接，客户端首先肯定是需要连接到mysql服务才可以执行sql语句的，连接语句如下：

-u 指定用户
-p 指定密码，密码可以在交互对话框中输入
mysql -uroot -p

 上面就是连接成功到mysql的服务，如果用户密码都没有问题，连接器就会获取该用户的权限，然后保存起来，后续该用户在此连接里的任何操作，都会基于连接开始时读到的权限进行权限逻辑的判断。

那我们怎么可以看到mysql被多少个客户端连接呢？

 我们可以通过show processlist 命令进行查看。

 一个连接可以永久保存吗？一个连接可以保存多久？

一个连接当然无法被永久保存，MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。

 

当然我们在有的时候也会遇到mysql会有死锁导致线程一直等待的问题，这些问题是在mysql的命令中是无法解决的所以我们也可以自己手动的kill进程。

命令：kill connection + id

居然有了连接器，那我们想一个问题mysql可以支持无限的客户端进行连接吗？ 

当然不可以支持无限的客户端连接，MySQL 服务支持的最大连接数由 max_connections 参数控制，如果超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。

 

1.2.2 查询缓存

连接器的工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。

如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。

如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

如果是对于更新操作不是很频繁数据的变化比较少的表，缓存可以大大的加快查询的效率，如果是对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓冲就被清空了，相当于缓存了个寂寞。

所以，MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了。

对于 MySQL 8.0 之前的版本，如果想关闭查询缓存，可以通过将参数 query_cache_type 设置成 DEMAND。

1.2.3 解析sql

在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由「解析器」来完成，在所有的编程语言都会经过这一步。

解析器

第一步：词法分析。MySQL 会根据你输入的字符串识别出关键字出来，例如，SQL语句 select username from userinfo，在分析之后，会得到4个Token，其中有2个Keyword，分别为select和from：
第二步：语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法，如果没问题就会构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。

对于SELECT name, age FROM students WHERE age > 18;sql语句可能的语法树结构为：

- Query

        - SelectClause

                - ColumnRef(name)

                - ColumnRef(age)

- FromClause

        - TableRef(students)

- WhereClause

        - BinaryOp(>)

                - ColumnRef(age)

                - Value(18)

1.2.4 执行sql

经过解析器后，接着就要进入执行 SQL 查询语句的流程了，每条SELECT 查询语句流程主要可以分为下面这三个阶段：

• prepare 阶段，也就是预处理阶段；

• optimize 阶段，也就是优化阶段；

• execute 阶段，也就是执行阶段；

预处理器

我们先来说说预处理阶段做了什么事情。

• 检查 SQL 查询语句中的表或者字段是否存在；

• 将 select * 中的 * 符号，扩展为表上的所有列；

如果sql语句中有不存在的表和字段就会在这个阶段报错。

优化器

优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

要想知道优化器选择了哪个索引，我们可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行计划，然后执行计划中的 key 就表示执行过程中使用了哪个索引，比如下图的 key 为 PRIMARY 就是使用了主键索引。

如果我现在使用到name字段进行查询的话explain会选择哪个扫描方式，如下图所示，下面是key为null而且type也为all所有走的是全表查询。

如果我现在给name加上一个普通的索引，key是什么。

索引现在就是我创建的idx_name而且使用到的type是ref普通索引。

后面就是执行语句调用api访问存储引擎拿到数据了。

到这个地方我们就知道了一条sql语句在mysql的底层中是如何执行的，我们就可以详细的来看一下索引部分的知识了。

二、索引的分类

我们可以按照四个角度来分类索引。
按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引(主键索引)、二级索引(辅助索引)
按「字段特性」分类：主键索引、唯一索引、前缀索引、普通索引。
按「字段个数」分类：单列索引、联合索引。

2.1 按照数据结构分类

1.B+Tree索引：这是MySQL中最常用的索引类型，几乎所有的存储引擎都支持。B+Tree索引适用于全值匹配、范围查询、排序和分组查询等操作。B+Tree索引在InnoDB和MyISAM存储引擎中的实现略有不同。InnoDB的B+Tree索引是聚集索引，数据和索引是存储在一起的，而MyISAM的B+Tree索引是非聚集索引，索引和数据是分开存储的。B+Tree索引由于其结构特点，支持范围查询和顺序访问，效率较高,下面的图中为了方便双向链表就用一个箭头表示了。

2.HASH索引：只有Memory存储引擎支持HASH索引，它适用于等值查询，但不支持范围查询。HASH索引通过哈希函数将键值转换为索引值，然后存储在哈希表中。由于哈希表的特性，HASH索引的查询速度非常快，但是它不支持索引值的顺序访问，也不支持部分索引列查找和范围查询。

3.Full-Text索引：主要用于文本数据的全文搜索，从MySQL 5.6开始，InnoDB和MyISAM存储引擎都支持Full-Text索引。Full-Text索引通过倒排索引的方式，可以快速匹配文档中的关键词。Full-Text索引适用于搜索大量文本数据，但是它可能会占用较多的磁盘空间，并且创建和维护索引的速度相对较慢。

2.2 按照物理存储分类

从物理存储的角度来看，索引分为聚簇索引(主键索引)、二级索引(辅助索引)。

• 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree的叶子节点里;
• 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

所以回表查询的过程其实就是：首先在二级索引中查询到主键值，然后在到聚餐索引中去查找拿到数据。

2.3 按照字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引(复合索引)

• 建立在单列上的索引称为单列索引，比如主键索引;
• 建立在多列上的索引称为联合索引;

联合索引

create index idx_name_age on user(name,id);

 下面是联合索引的数据结构，联合索引是按照顺序保证局部顺序性，如果我是按照id和name的顺序进行排序的，那就是先把id进行排序好，然后在排序好的id中进行name排序。

因为联合索引是满足的局部顺序性，因此，使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。

比如，如果创建了一个(a，b，c)联合索引，如果査询条件是以下这几种，就可以匹配上联合索引:

• where a=1;
• where a=1 and b=2 and c=3;
• where a=1 and b=2;

需要注意的是，因为有查询优化器，所以a字段在 where 子句的顺序并不重要。也就是说对于sql语句where b=1 and a=2;也会生效，我们关注的只是在where语句中的左边的字段有没有。
但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

• where b=2;
• where c=3;
• where b=2 and c=3;

2.4 按照字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

主键索引
主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引索引列的值不允许有空值。

在创建表时，创建主键索引的方式如下:

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

 唯一索引

保证索引列中的所有值都是唯一的，但允许有空值。在业务有唯一性的情况下，我们就可以对字段建立唯一索引，因为区分度很高。

ALTER TABLE table_name ADD UNIQUE (column1);

普通索引

最基本的索引类型，没有唯一性的要求。 

CREATE INDEX index_name ON table_name (column1);

前缀索引

只对字符串类型的列的前几个字符创建索引，可以节省空间。在对于uuid查找的时候我们就可以建立前缀索引调高查询的效率和节约索引使用空间。

CREATE INDEX index_name ON table_name (column1(10));

三、B+Tree原理

上面我们已经看过B+Tree的结构了，如下所示：

B+树（B+ Tree）是一种自平衡的树数据结构，它是B树的一种变体，广泛用于数据库和操作系统的文件系统中。B+树的设计使得它特别适合用于存储、检索和维护大型数据集，尤其是在磁盘存储上。以下是B+树的一些关键特点：

1. 节点结构：B+树的每个节点包含多个键值和多个指针，这些指针指向子节点。在B+树中，所有的数据记录节点都是按顺序存放在叶子节点中，而非叶子节点仅存储键值信息。

2. 有序性：B+树中的键值是有序的，这使得它可以进行范围查询和顺序访问。

3. 平衡性：B+树通过保持所有叶子节点在相同层级上，确保了树的平衡性，从而保证了操作的效率。

4. 磁盘I/O优化：由于B+树的叶子节点都在同一层，并且叶子节点之间通过指针相连，这使得B+树在磁盘I/O操作上更加高效。因为访问叶子节点的数据不需要回溯到非叶子节点，减少了磁盘访问次数。

5. 查询性能：B+树支持高效的查找、插入、删除和顺序访问操作。由于数据记录节点都集中在叶子节点，并且叶子节点之间是相连的，所以B+树特别适合执行范围查询和顺序扫描。

6. 空间效率：B+树的非叶子节点不存储数据记录，只存储键值和指针，这使得B+树在存储空间上更加高效。

7. 动态调整：B+树可以根据数据的增加或减少动态调整树的高度和节点的分裂与合并，以保持树的平衡。

四、索引失效场景

不满足最左匹配原则：在使用联合索引时，如果查询条件不满足最左匹配原则，即查询没有从联合索引的最左边列开始，索引将不会生效。

-- 假设存在(id, name)的联合索引
SELECT * FROM users WHERE name = 'John';

使用了SELECT *：当使用SELECT *来查询所有列时，可能会阻止使用覆盖索引，因为覆盖索引只需要读取索引中的列数据，而不是全行数据。

SELECT * FROM users WHERE id = 1;

索引列参与运算：如果在WHERE子句中对索引列进行运算或计算，如age + 1 = 7，索引将失效。

SELECT * FROM users WHERE age + 1 = 20;

索引列使用了函数：对索引列使用函数，如SUBSTR或CONCAT，会导致索引失效。

SELECT * FROM users WHERE SUBSTR(username, 1, 3) = 'Joh';

错误的LIKE使用：在使用LIKE进行模糊查询时，如果通配符%在参数的左侧，如LIKE '%value'，索引将失效。

SELECT * FROM users WHERE username LIKE '%Doe';

类型隐式转换：如果查询条件中的数据类型与索引列的类型不匹配，如将字符串类型的索引列与数字进行比较，索引可能失效。

SELECT * FROM users WHERE age = '30';

使用OR关键字：如果使用OR连接多个条件，但不是所有条件都使用索引列，可能会导致索引失效。

SELECT * FROM users WHERE age = 10 OR username = 'John';

两列做比较：在WHERE子句中比较两个索引列，如id = age，可能会导致索引失效。

SELECT * FROM users WHERE age = id;

不等于比较：使用!=或<>进行比较时，可能会导致索引失效，尤其是当结果集占比较大时。

SELECT * FROM users WHERE age != 20;

IS NOT NULL：使用IS NOT NULL作为查询条件可能会导致索引失效。

SELECT * FROM users WHERE age IS NOT NULL;

NOT IN和NOT EXISTS：这些条件可能会导致索引失效，尤其是当NOT IN用于非主键列时。

ORDER BY导致索引失效：如果ORDER BY子句中的列不是索引列，或者排序的列顺序与索引列顺序不一致，可能会导致索引失效，存在order by的sql语句中一定要吧orderby中用到的参数放到最后一个。

//索引必须要是（age,username），(username,age)导致索引失效
SELECT * FROM users where age = 20 ORDER BY username;