MySQL总结

一、执行一条 select 语句，期间发生了什么？

1. MySQL整体架构

首先，我们了解MySQL整体架构分为两层：Server层和存储引擎层：

1. Server 层负责建立连接、分析和执行 SQL。大多数核心功能模块都在Server层实现，包括连接器、查询缓存、解析器、预处理器、优化器、执行器等。（内置函数时间、日期、数学、加密函数等以及所有跨存储引擎的功能，存储过程、触发器、试图等也都在Server层实现）
2. 存储引擎层负责数据的存储和读取。支持多种存储引擎，比如InnoDB、Memory和MyISAM。（不同存储引擎是共用一个Server层，其中InnoDB最为常用，是5.5版本之后的默认存储引擎，默认支持的索引类型是B+树）

2. 查询过程简述

查询过程总共分为4步：

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中了查询缓存则直接返回，否则继续执行；
• 解析SQL：解析器对SQL语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 执行SQL：执行SQL共有三个阶段：
  • 预处理阶段：检查表或者字段是否存在；

			客户端/应用程序
			    │
			    ▼
			连接器（建立连接、权限认证、管理连接）
			    │
			    ▼
			查询缓存（命中则返回，否则继续）
			    │
			    ▼
			解析器（词法分析、语法分析、构建语法树）
			    │
			    ▼
			 执行SQL
____________________________________________________   
|预处理器（检查表/字段是否存在，*号展开）				|
|    │												|
|    ▼											    |
|优化器（生成最优执行计划，选择索引/扫描方式/下推等）   |
|    │												|
|    ▼												|		
|执行器（调用存储引擎API，按执行计划逐条获取数据）      |
|    │												|
|    ▼												|		
|存储引擎（InnoDB/MyISAM等，实际存取数据、事务、锁等）	|
|    │												|
|    ▼												|	
|结果返回客户端										|	
|___________________________________________________|

二、MySQL索引

从索引的基本原理到索引使用场景有一系列问题：

• 什么是索引？
• 索引分类？
  • 按数据结构：B+树；Hash；Full-text
  • 按物理存储：
  • 按字段特性：
  • 按字段个数：
• 何时需要/不需要创建索引？
  • 需要场景：
  • 不需要场景：
• 优化索引方法？

1. 什么是索引？

在数据库中，索引是能够帮助存储引擎快速获取数据的一种数据结构，可以形象的理解为索引是数据的目录。

索引和数据都位于存储引擎中，存储引擎是各种数据操作的实现方法，包括：如何存储数据、如何为存储的数据建立索引、如何更新数据、如何查询数据等这些功能的具体实现都是存储引擎负责。

2. 索引分类

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

2.1 按数据结构分类

从数据结构角度来说，MySQL常见索引有三种： B+Tree 索引、HASH 索引、Full-Text 索引。每一种存储引擎支持的索引类型不同。

在创建数据表时，InnoDB存储引擎会根据不同场景来选择不同的列作为索引：

• 如果有主键，默认使用主键作为聚簇索引的索引键（key）；
• 如果没有主键，就选择第一个不包含NULL值得唯一列作为聚簇索引的索引键（key）；
• 如果都不行，InnDB自动生成一个隐式自增id列作为聚簇索引的索引键（key）；

其他索引都属于辅助索引（二级索引、非聚簇索引）。创建的主键索引和二级索引默认使用的是B+树索引。

2.1.1 B+树索引存储和查询过程

存储过程

这里涉及到面试重点题：「为什么 MySQL 采用 B+ 树作为索引？」

要理解这个问题，我们不仅仅要从数据结构复杂度的角度出发，还要考虑磁盘 I/O 操作极慢，要尽可能减少磁盘 I/O 操作次数，需要查询效率非常高，我们期望在最低的查询次数（磁盘I/O操作次数）下就定位到目标数据。

1）怎样的索引数据结构是好的？

首先，我们理解MySQL数据库的数据是需要持久化的，即存储在磁盘上，这样即使是设备断电，数据不会丢失。

然而，磁盘是个慢的离谱的存储设备，内存访问速度是纳秒级，而磁盘访问速度是毫秒级，慢几十万倍！

（磁盘读写最小单位是 512B 大小的扇区，Linux操作系统最小读写单位是大小为 4KB 的块，八个扇区）一次磁盘I/O会读写八个扇区。

数据库的索引和数据都是要保存到磁盘的，我们通过索引查找某行数据，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读取到内存。查询过程中会发生多次的磁盘I/O，不断通过读取索引到内存，定位目标行。

所以一个好的索引数据结构一定是能够用最少的磁盘I/O操作完成查询工作，因为这样查询时间最短。
另外，由于MySQL还支持范围查找，所以索引数据结构不仅要支持查找某一行数据，还得支持高效执行范围查找。

总结，要设计一个适合 MySQL 索引的数据结构，至少满足以下要求：

• 尽可能少的磁盘I/O操作中完成查询工作；
• 高效查询某一个记录，也要高效执行范围查找；

2）二分查找？

3）二分查找树？

4）自平衡二叉树？

5）B树？

B树本质是一个多叉树，将所有数据（索引＋记录数据）有序分配到B树每个节点上，解决了自平衡二叉树每个节点只能有两个子节点，随着节点数增加，树的高度变得越来越高，导致磁盘I/O次数骤增，严重影响查询效率的问题。

B树不再限制每个节点只能有两个节点，有效降低了树的高度，每个节点最多可以包含M个子节点，B树数据查询效率比自平衡二叉树是高很多的。

但是B树也有很大问题，B树每个节点包含具体数据（索引和记录），而用户记录的数据量很可能远远超过了索引数据，这就会花费更多磁盘I/O次数，读取没用的记录信息占用内存资源，影响我们查找我们的目标信息。

另外，MySQL需要支持范围查找（查找某个范围内全部数据），如果用B树做范围查找的话呢，需要使用中序遍历（中序遍历的结果是有序的）这会涉及多个节点的磁盘I/O问题，从而拖垮整体查询效率。

MySQL 需要支持范围查找（核心需求），若用 B 树实现这种范围查找（选择的实现方式），由于范围查找需要获取有序数据，而 B 树只有通过中序遍历才能得到有序结果（中序遍历的必要性），但中序遍历过程中会访问多个 B 树节点（节点访问的必然性），这些节点存储在磁盘而非内存，未加载到内存的节点就会产生磁盘 I/O（磁盘 I/O 的成因），从而（导致整体速度下降）

6）B+树？

MySQL 的存储方式根据存储引擎的不同而不同，我们最常用的就是 Innodb 存储引擎，它就是采用了 B+ 树作为了索引的数据结构。

B+树是B树的升级：

• 叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；

• 所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；

• 非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。

• 非叶子节点中有多少个子节点，就有多少个索引；

B+ 和 B 树的性能区别：
从三个方面比较性能差异，

1. 单点搜索：

• B树每个节点是既存索引又存记录，所以单点查询最快可以达到O(1)，有时需要递归到叶子节点才能访问到需要的数据，查询波动比较大。
• B+树的非叶子节点不存放实际记录，只存放索引，所以数据量相同情况下，B+树的非叶子节点可以存放更多的索引，因此B+树相对更加矮胖，查询底层节点的磁盘I/O次数会少很多。
• 单点查询，平均时间代价来看，B+树更快一些。特殊情况，B树更快。

2. 插入/删除效率：

• B树的插入删除都涉及复杂的树的变形过程。
• B+树由于存在大量的冗余节点，并且所有索引数据都会在叶子节点存在，叶子节点直接还有链表相连，所以可以直接从叶子节点中删除，甚至可以不动非叶子节点，这样删除非常快！
• 插入也一样，由于有着冗余节点，在节点饱和的情况，插入可能存在节点的分裂，但是最多只涉及树的一条路径。并且B+树会自动平衡就不需要复杂的变形算法。

3. 范围查询：

• B树和B+树的等值查询（精确匹配：某个字段等于一个明确的值）原理基本一致的，从根节点开始查找，然后对比目标数据的范围，递归进入子节点查找。
• 由于B+ 树所有叶子节点间还有一个链表进行连接，这种设计对范围查找非常有帮助。比如说我们想知道 12 月 1 日和 12 月 12 日之间的订单，这个时候可以先查找到 12 月 1 日所在的叶子节点，然后利用链表向右遍历，直到找到 12 月12 日的节点，这样就不需要从根节点查询了，进一步节省查询需要的时间。
• 然而B树没有将叶子节点用链表串联，只能通过树的中序遍历来完成范围查询，这回涉及多个节点的I/O操作，范围查询效率显然不如B+树。
• 因此，存在大量范围检索的场景，适合使用 B+树，比如数据库。而对于大量的单个索引查询的场景，可以考虑 B 树，比如 nosql 的MongoDB。

下图是 Innodb 里的 B+ 树：

Innodb 使用的 B+ 树有一些特别的点，比如：

• B+ 树的叶子节点之间是用「双向链表」进行连接，这样的好处是既能向右遍历，也能向左遍历。
• B+ 树点节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB。

Innodb 根据索引类型不同，分为聚集和二级索引。他们区别在于，聚集索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点，而二级索引的叶子节点存放的是主键值，而不是实际数据。

因为表的数据都是存放在聚集索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚集索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个，而二级索引可以创建多个。

从数据结构角度：为什么要用B+树做MySQL索引？

MySQL 是需要将数据持久化在硬盘，而存储功能是由 MySQL 存储引擎实现的，所以讨论 MySQL 使用哪种数据结构作为索引，实际上是在讨论存储引使用哪种数据结构作为索引，InnoDB 是 MySQL 默认的存储引擎，它就是采用了 B+ 树作为索引的数据结构。

要设计一个 MySQL 的索引数据结构，不仅仅考虑数据结构增删改的时间复杂度，更重要的是要考虑磁盘 I/0 的操作次数。因为索引和记录都是存放在硬盘，硬盘是一个非常慢的存储设备，我们在查询数据的时候，最好能在尽可能少的磁盘 I/0 的操作次数内完成。

二分查找树虽然是一个天然的二分结构，能很好的利用二分查找快速定位数据，但是它存在一种极端的情况，每当插入的元素都是树内最大的元素，就会导致二分查找树退化成一个链表，此时查询复杂度就会从 O(logn)降低为 O(n)。

为了解决二分查找树退化成链表的问题，就出现了自平衡二叉树，保证了查询操作的时间复杂度就会一直维持在 O(logn) 。但是它本质上还是一个二叉树，每个节点只能有 2 个子节点，随着元素的增多，树的高度会越来越高。

而树的高度决定于磁盘 I/O 操作的次数，因为树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作，也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能。

B 树和 B+ 都是通过多叉树的方式，会将树的高度变矮，所以这两个数据结构非常适合检索存于磁盘中的数据。

但是 MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；

B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

查询过程

（1）首先，了解InnoDB两种索引类型，聚簇索引和二级索引核心区别是“数据怎么存储”：

1. 聚簇索引（主键索引）：

• 通俗类比：字典的“拼音正文页”：
  • 字典中，汉字是按照拼音顺序物理排序的，这里的拼音顺序就是一种聚簇索引，并且每个拼音位置还直接存储着汉字的解释。
• 聚簇索引的存储逻辑：
  • 物理顺序存储：聚簇索引的数据行按照id的大小顺序存储在磁盘，id=1挨着id=2，id=2挨着id=3…
  • 叶子节点内容：聚簇索引的B+树叶子节点直接存完整数据行，比如，id=2的叶子节点，存储着id=2，product_num=0002，name=banana，price=2.00，所有的完整信息数据行。
• 聚簇索引键的选择规则：
  • 有主键 → 主键作为聚簇索引键；
  • 无主键，但有非空唯一列 → 选定第一个非空唯一列为聚簇索引键；
  • 以上都无 → InnoDB 自动生成隐式自增 id 作为聚簇索引键。

代码举例：

CREATE TABLE product (
  id INT PRIMARY KEY,        -- 聚簇索引键
  product_no VARCHAR(20),    -- 商品编码
  name VARCHAR(255),         -- 商品名
  price DECIMAL(10,2)        -- 价格
);

数据行（简化）：

2. 二级索引（辅助索引）：数据和索引“分开住”，索引只存“导航地址”**

• 类比：字典的“部首索引页”
  字典里，除了拼音正文，还有 部首索引：
• 部首索引里，每个部首（如“木”“水”）的条目下，只存“该部首在拼音正文的页码”（比如“木”部对应页码 200），不存汉字的解释。
• 查字时，先看部首索引找到页码（如“木”→ 页码200），再翻到拼音正文的页码200，才能看到具体字的解释（这就是 “回表”）。

对应数据库的二级索引
给 product 表的 product_no 建二级索引：

ALTER TABLE product ADD INDEX idx_product_no (product_no);  

二级索引的存储逻辑：

• 独立的 B+Tree：二级索引是一个 独立的 B+Tree，和聚簇索引的 B+Tree 分开存。
• 叶子节点内容：只存 product_no（索引键） + id（聚簇索引键，即主键）（比如 product_no=0002 的叶子节点，存着 0002 + 2）。

二级索引的查询过程（两种情况）

情况1：需要“回表”（查完整数据，如 SELECT * FROM product WHERE product_no='0002'）

1. 查二级索引：在 idx_product_no 的 B+Tree 里，找到 product_no=0002 对应的 id=2（叶子节点数据）。
2. 回表查聚簇索引：拿着 id=2，去 聚簇索引的 B+Tree 里找完整数据（name=banana、price=2.00）。

→ 相当于：先查部首索引找到页码，再翻正文查解释。

情况2：覆盖索引（无需回表，如 SELECT id FROM product WHERE product_no='0002'）

• 因为查询的字段是 id，而二级索引的叶子节点 已经存了 id（product_no=0002 对应 id=2），所以直接返回 id=2，不用回表。

→ 相当于：部首索引里已经标了“页码200”，而你只需要“页码”，不用翻正文。

三、核心区别总结（用表格对比）

一句话通俗总结：

• 聚簇索引是 “数据本身的排序”，像字典正文按拼音排，找东西直接定位；
• 二级索引是 “给数据贴的导航标签”，像部首索引，找东西得先看标签，再跳转到数据。

InnoDB 索引类型 到 B+Tree 查询过程

一、InnoDB 的两种索引类型

InnoDB 中，索引分为 聚簇索引（主键索引） 和 二级索引（辅助索引），核心区别是 “数据怎么存”：

1. 聚簇索引（Clustered Index）

• 定义：数据行 按索引键的物理顺序存储，索引的叶子节点直接存放完整数据行。
• 索引键选择规则：
  • 有主键（PRIMARY KEY）→ 主键作为聚簇索引键；
  • 无主键，但有非空唯一列 → 第一个非空唯一列作为聚簇索引键；
  • 以上都没有 → InnoDB 自动生成隐式自增 id 作为聚簇索引键。

2. 二级索引（Secondary Index）

• 定义：独立于数据的索引，叶子节点存放 “索引键 + 聚簇索引键（主键值）”，不直接存完整数据。
• 特点：一个表可建多个二级索引（如对 product_no、name 分别建索引）。

二、B+Tree 的结构（支撑索引的底层结构）

InnoDB 的聚簇索引和二级索引，默认都基于 B+Tree 实现，结构特点：

• 非叶子节点：只存索引键（用于快速路由），不存数据；
• 叶子节点：
  • 聚簇索引的叶子节点 → 存完整数据行（如 id、product_no、name、price）；
  • 二级索引的叶子节点 → 存索引键 + 主键值（如 product_no='0002' + id=2）；
• 叶子节点链表：所有叶子节点通过双向链表连接（方便范围查询，如 id BETWEEN 1 AND 10）。

三、查询过程：聚簇索引（主键查询）

以 product 表为例（id 是主键，聚簇索引基于 id 构建 B+Tree）：

CREATE TABLE `product` (
  `id` int NOT NULL,
  `product_no` varchar(20),
  `name` varchar(255),
  `price` decimal(10,2),
  PRIMARY KEY (`id`)  -- 聚簇索引
);

数据行：

B+Tree 结构示意图（简化）

• 根节点：存索引键分段（如 1、10、20），用于快速定位；
• 中间节点：进一步细分区间（如 1~4、4~7）；
• 叶子节点：存完整数据行（如 id=1 对应 apple 数据，id=2 对应 banana 数据），且叶子节点双向链表连接。

等值查询：SELECT * FROM product WHERE id=5

1. 根节点判断：id=5 落在 1~10 区间，进入中间节点；
2. 中间节点判断：id=5 落在 4~7 区间，进入对应叶子节点；
3. 叶子节点查找：在叶子节点直接找到 id=5 的完整数据行。

• 磁盘 I/O 分析：每层节点对应一次磁盘 I/O（根→中间→叶子，共 3 次）。
• B+Tree 优势：千万级数据只需 3~4 层，因此即使大数据量，等值查询也只需 3~4 次 I/O，效率极高。

四、查询过程：二级索引（辅助查询）

给 product_no 建二级索引：

ALTER TABLE product ADD INDEX idx_product_no (product_no);  -- 二级索引

二级索引的 B+Tree 结构：

• 非叶子节点：存 product_no（如 0001、0004、0007）；
• 叶子节点：存 product_no + 主键 id（如 0002 + 2、0003 + 3）。

场景 1：需要“回表”的查询（SELECT * FROM product WHERE product_no='0002'）

1. 二级索引查找：在二级索引的 B+Tree 中，找到 product_no='0002' 对应的 主键 id=2（叶子节点数据）；
2. 回表查询：拿着 id=2，到 聚簇索引的 B+Tree 中查找完整数据行（如 name=banana、price=2.00）。

• 磁盘 I/O 分析：查二级索引（3 次 I/O） + 查聚簇索引（3 次 I/O），共 6 次（假设树高相同）。
• “回表”的本质：二级索引只存主键，必须通过主键到聚簇索引取完整数据。

场景 2：覆盖索引（SELECT id FROM product WHERE product_no='0002'）

• 为什么不用回表？ 因为查询的字段 id 正好是 二级索引叶子节点已存的数据（二级索引叶子节点存 product_no + id）。
• 查询过程：只需在二级索引的 B+Tree 中，找到 product_no='0002' 对应的 id=2，直接返回结果。
• 优势：仅需 3 次 I/O（和主键查询一样），避免回表开销。

核心总结

理解这些逻辑后，就能明白：

• 为什么主键查询更快？因为聚簇索引直接存数据，无需回表；
• 为什么要尽量用覆盖索引？因为减少一次 B+Tree 查找（避免回表）；
• 为什么 B+Tree 适合磁盘存储？因为树高低，I/O 次数少，适配磁盘慢读写的特点。

2.2 按物理存储分类

按照物理存储的角度来看，索引分为聚簇索引（主键索引）和二级索引（辅助索引）。

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

回表和覆盖索引的概念：

• 如果要查询的数据不在二级索引里面，检查二级索引找到对应的叶子节点，获取主键值，再检查主键索引，就能查询到数据，这个过程就是回表。
• 如果要查询的数据直接能够在二级索引里面找到，那么就不需要回表，这个过程称为覆盖索引。

2.3 按字段特性分类

从字段特性分类，分为主键索引、唯一索引、普通索引、前缀索引。

• 主键索引：就是建立在主键字段的索引，一张表最多只有一个主键索引，索引列不允许有空值，通常在创建表的时候一起创建好主键索引。
• 唯一索引：建立在UNIQUE（唯一）字段的索引，一张表可以有多个唯一索引，
• 普通索引：建立在普通字段的索引，既不要求字段为主键，也不要求字段唯一。

2.4 按字段个数分类

• 单列索引
  • 建立在单列上的索引，比如主键索引就是一种单列索引；
• 联合索引（复合索引）
  • 将多个字段组合成一个索引，该索引就称为联合索引。
  • 比如将商品表中的product_no和name字段组成联合索引（product_no，name），创建联合索引的方式如下：

CREATE INDEX index_product_no_name ON product(product_no, name);

联合索引的B+树示意图：

使用联合索引时，存在最左匹配优先原则，即B+树先安装product_no排序，在product_no相同的情况下再按照name排序。
查询联合索引时，也遵守最左匹配优先原则：

比如，如果创建了一个（a，b，c）如果查询条件是以下这几种，就可以匹配上联合索引：

• where a=1；
• where a=1 AND b=2 AND c=3；

由于有查询优化器的存在，所以a字段再where子句的顺序并不重要。
但是如果查询条件是以下几种，由于不符合最左匹配原则，所以就无法匹配上联合索引，联合索引会失效：

• WHERE b=2；
• WHERE c=2 AND b=2；

为什么如果用联合索引不遵守最左匹配原则，联合 索引就会失效？

举个例子， 联合索引（a，b），该联合索引的 B+ Tree 如下（实际是双向链表！）：

可以见得，a是全局有序的（1，2，3，4，5…）而b是全局无序的（12，7，8，2…）
因此不遵守最左匹配原则，直接输入WHERE b=2；这种查询条件是没办法利用联合索引的，利用索引的前提是，索引的key是有序的。

只有a相同情况下，b才是有序的，才能有效利用联合索引。