MySQL-原理篇-②索引（重点）

holic-x...大约 77 分钟

MySQL-原理篇-②索引（重点）

学习核心

索引概念核心
- 什么是索引？
- 为什么索引可以提升检索效率？
- 索引的数据结构
MySQL为什么采用B+树作为索引？
- 结合数组、链表、B树、红黑树、B+树的场景演进进行理解分析
MySQL中的数据存储
索引失效的场景
索引的选择
索引的引用

学习资料

索引核心概念

1.什么是索引？

什么是索引？

索引是提升查询速度的一种数据结构，可以理解为类似书的目录。索引之所以能提升查询速度，在于它在插入时对数据进行了排序（显而易见，其缺点是影响插入或者更新的性能）。有效的设计并创建索引可以提升数据库系统的整体性能，但是也要考虑索引维护带来的成本消耗

在MySQL-架构篇的学习中，在介绍InnoDB存储引擎的时候也梳理了B+树（索引）的构建由来和核心（其梳理思路是通过检索优化来反推为什么要构建索引？为什么要引入B+树），在索引篇章中对B+树的学习则是正向学习，首先理解B+树这一数据结构（对比其他数据结构的优缺点）

MySQL目前支持B+树索引、全文索引、R 树索引，此处以B+树索引为学习核心进行学习。

2.B+树索引结构

B+ 树索引是数据库系统中最为常见的一种索引数据结构，几乎所有的关系型数据库都支持它

为什么关系型数据库都热衷支持 B+树索引？

因为B+树索引是目前为止排序最有效率的数据结构。像二叉树，哈希索引、红黑树、SkipList，在海量数据基于磁盘存储效率方面远不如 B+ 树索引高效

B+树索引的特点

基于磁盘的平衡树，但树非常矮，通常为 3~4 层，能存放千万到上亿的排序数据。树矮意味着访问效率高，从千万或上亿数据里查询一条数据，只用 3、4 次 I/O

且现在的固态硬盘每秒能执行至少 10000 次 I/O ，所以查询一条数据，哪怕全部在磁盘上，也只需要 0.003 ~ 0.004 秒。由于 B+ 树矮，在做排序时，也只需要比较 3~4 次就能定位数据需要插入的位置，排序效率非常不错。

B+ 树索引由根节点（root node）、中间节点（non leaf node）、叶子节点（leaf node）组成，其中叶子节点存放所有排序后的数据。

存在一种特殊的情况：当B+树索引高度为1时，只有一个页，该页既是根节点也是叶子节点

B+树的构建都是从高度为1的树开始，随着数据的插入慢慢增加树的高度。随着B+树记录变多，1页无法容纳这么多数据，就会发生B+树的分裂，B+树高度变为2，以此类推存储数据。索引是对记录的排序，因此在检索记录的时候根据索引值结合二分法快速定位记录页和记录信息

B+树可以存储多少数据？

以User表为例，主键为id列。以一个高度为2的B+树索引，拆解其理论上可以存放多少条行记录

CREATE TABLE User (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  sex CHAR(6) NOT NULL,
  registerDate DATETIME NOT NULL,
  ...
)

在MySQL InnoDB 存储引擎中，一个页的大小为 16K，在上面的表 User 中，键值 userId 是BIGINT 类型，则根节点能最多存放以下多个键值对 = 16K / 键值对大小(8+6) ≈ 1100

假设User表中每条记录的大小为500字节，则叶子节点能存放的最多记录为 = 16K / 每条记录大小 ≈ 32

则树高度为 2 的 B+ 树索引，最多能存放的记录数为总记录数 = 1100 * 32 = 35,200

即在35200条记录中，根据索引键检索一条记录只需要查询2个页：1个根节点定位数据页，1个叶子节点定位记录信息（如果是非聚簇索引需要检索完整记录则还需要额外一次回表操作）

以此类推，高度为3的B+树索引可存储的记录数拆解如下：

第1层：1个根节点存放键值对：1100
第2层：每个中间节点存放键值对：1100
第3层：叶子节点存放记录：32

存放总记录数：1100 × 1100 × 32 = 38720000（3800w），即在3800w条记录中，根据索引键检索一条记录只需要查询3个页：1个根节点定位目录项页，然后到中间节点定位到数据页，最后根据1个叶子节点定位记录信息（如果是非聚簇索引需要检索完整记录则还需要额外一次回表操作）

基于上述场景，都是基于理想状态下充分利用得到的数据，但实际上页的利用率并没有那么高，可能还会存在一些碎片的情况，假设每个页的利用率为60%，则不同高度的B+树的存储记录数参考如下表所示（还是以上述案例为参考）

B+树高度	每个非叶子节点存放键值对数（理想 / 折算）	叶子节点存放总记录数（理想 / 折算）
1	/	32 / 19
2	1100 / 660	35200 / 12540
3	1100 / 660	38.72W / 8276400
4	1100 / 660	4259200W / 546242.4W

结合上述表格初步估计，在50多亿的数据中，根据索引键值查询记录，只需4次I/O（大概0.004s），如果这些查询的页已经被缓存在内存缓冲池中，其查询性能会更快。可以通过explain关键字来查看SQL的执行计划。虽然B+树的引入大大提升了查询性能，但是B+树的维护（索引的维护）还是需要一定成本的。

优化B+树索引的插入性能

B+ 树在插入时就对要对数据进行排序，但排序的开销其实并没有你想象得那么大，因为排序是 CPU 操作（当前一个时钟周期 CPU 能处理上亿指令）

真正的开销在于 B+ 树索引的维护，保证数据排序，这里存在两种不同数据类型的插入情况

数据顺序（或逆序）插入： B+ 树索引的维护代价非常小，叶子节点都是从左往右进行插入，比较典型的是自增 ID 的插入、时间的插入（若在自增 ID 上创建索引，时间列上创建索引，则 B+ 树插入通常是比较快的）
数据无序插入： B+ 树为了维护排序，需要对页进行分裂、旋转等开销较大的操作，另外，即便对于固态硬盘，随机写的性能也不如顺序写，所以磁盘性能也会收到较大影响。比较典型的是用户昵称，每个用户注册时，昵称是随意取的，若在昵称上创建索引，插入是无序的，索引维护需要的开销会比较大

实际上不可能要求所有插入的数据都是有序的，因为索引的本身就是用于数据的排序，如果插入数据都已经是排序的也就不需要 B+ 树索引进行数据查询了。所以对于 B+ 树索引，在 MySQL 数据库设计中，仅要求主键的索引设计为顺序，比如使用自增，或使用函数 UUID_TO_BIN 排序的 UUID，而不用无序值做主键。在表结构设计中主键的设计要尽可能地使用顺序值，这样才能保证在海量并发业务场景下的性能

MySQL中B+树索引的设计和管理

回顾前面提到的索引概念，我们在享受索引带来的查询性能提升时，也要相应承担维护索引的成本，因此合理的构建索引是提升数据库系统性能的关键，需要把握两个维度：

不要因为索引维护的成本而望而却步不敢创建索引，实际上数据库对创建索引的场景并没有所谓的个数限制，而是要结合业务场景去创建合适的索引
不要盲目陷入追求通过索引提升查询性能，如果盲目创建了多个索引，但实际上MySQL优化器在生成执行计划的时候有可能并不会走这些低效的索引，则这些低效索引的存在既占用了空间又影响了插入的性能，无形中增加了数据库的维护成本

因此，为了更好的利用MySQL索引，要学会如何分析现有数据库创建的索引以及其使用情况

通过查询表 mysql.innodb_index_stats 查看每个索引的大致情况
通过查询表sys.schema_unused_indexes查看有哪些索引一直未被使用过，可以被废弃

# 查询指定表索引的大致情况
SELECT table_name,index_name,stat_name,stat_value,stat_description 
FROM mysql.innodb_index_stats 
WHERE table_name = 'student' and index_name = 'PRIMARY';

# 查询哪些索引一直未被使用过(如果数据库运行时间比较长，而且索引的创建时间也比较久，索引还出现在上述结果中，DBA试着分析这些索引的设计初衷进而考虑是否需要剔除)
SELECT * 
FROM sys.schema_unused_indexes
WHERE object_schema != 'performance_schema';

MySQL 8.0 版本推出了索引不可见（Invisible）功能。在删除废弃索引前，用户可以将索引设置为对优化器不可见，然后观察业务是否有影响。若无，DBA 可以更安心地删除这些索引：

ALTER TABLE t1 
ALTER INDEX idx_name INVISIBLE/VISIBLE;

👻MySQL为什么采用B+树作为索引？

1.MySQL 索引方案

要理解MySQL为什么采用B+树作为索引，可以结合数据检索场景中不同数据结构的演进，进而切入B+树

索引的数据结构选择标准

MySQL 的数据是持久化的，意味着数据（索引+记录）是保存到磁盘上的，因为这样即使设备断电了，数据也不会丢失。

磁盘是一个慢的离谱的存储设备，有多离谱呢？内存的访问速度是纳秒级别的，而磁盘访问的速度是毫秒级别的，也就是说读取同样大小的数据，磁盘中读取的速度比从内存中读取的速度要慢上万倍，甚至几十万倍。

磁盘读写的最小单位是扇区，扇区的大小只有 512B 大小，操作系统一次会读写多个扇区，所以操作系统的最小读写单位是块（Block）。Linux 中的块大小为 4KB，也就是一次磁盘 I/O 操作会直接读写 8 个扇区。

由于数据库的索引是保存到磁盘上的，因此当通过索引查找某行数据的时候，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读入到内存，也就是说查询过程中会发生多次磁盘 I/O，而磁盘 I/O 次数越多，所消耗的时间也就越大。

因此，在设计上希望索引的数据结构能在尽可能少的磁盘的 I/O 操作中完成查询工作，因为磁盘 I/O 操作越少，所消耗的时间也就越小。且MySQL 是支持范围查找的，所以索引的数据结构不仅要能高效地查询某一个记录，而且也要能高效地执行范围查找。

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

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

不同存储引擎的索引方案

根据不同的存储引擎（MyISAM、InnoDB），B+树的实现有些许区别

MySQL中的索引是通过B+树实现的。B+树是一种多叉树（多路搜索树，对B树的一种改进），它可以将数据按照一定的顺序组织起来，从而提高查询效率。

B+树与B树的区别在于，B+树的所有数据都存储在叶子节点上，而非叶子节点只存储索引，查询的时候只需要遍历一次树就能找到目标数据，可以提高数据查询效率。B+树的叶子节点连接成一个有序的链表，可以实现区间查找（范围查找：可以快速定位某个区间内的数据），适用于数据库存储大量数据的场景

B树是一个平衡树，每个节点都存储有序的关键字，每个节点既可以存储数据、也可以存储索引信息，查询时需要遍历多个节点才能找到目标数据，适用于文件系统等存储大量数据的场景

MySQL之所以采用B+树作为索引的实现方式，主要是因为B+树具有以下优点：查询性能好、支持范围查询、支持数据分页（此外，B+树索引结构相对简单便于实现和维护，能够满足高并发、高可用的数据库要求）

能够支持高效的范围查找和排序
叶子节点之间使用指针相连，能够支持高效的区间查询
B+ 树具有较高的数据密度，可以减少磁盘I/O次数，提高查询效率
B+树对于插入和删除操作也比较高效

MyISAM	InnoDB
无聚簇索引	分为聚簇索引和非聚簇索引
叶子存放数据地址	聚簇索引（根据主键创建的索引）只有一个，根据主键实现B+树非聚簇索引（根据非主键列创建的索引）有多个，叶子结点存储索引+主键
数据和索引分别存储，每次查询都要回表	数据和索引一个文件，只需遍历一次树就能找到数据

在MySQL中，B+树的实现主要是通过InnoDB存储引擎来实现的。InnoDB存储引擎中的索引主要有聚簇索引和辅助索引两种类型，聚簇索引是根据主键创建的索引，而辅助索引是根据非主键列创建的索引。对于辅助索引，MySQL中会同时创建一个对应的聚簇索引，这样可以提高查询效率

如何理解大数据量场景下索引提供的优化查询概念？假设有1亿条数据，查询磁盘中的数据其决定性的因素就是

遍历数据的次数（可进一步说明B+ & B）
磁盘加载到内存的IO时间（遍历次数少、加载IO时间也少)

👻2.MySQL为什么采用B+树作为索引?

引入线性查找和二叉树查找、B、B+的扩展概念（结合场景分析其引用设计的理念）=》总结分析
结合场景理解每种数据结构的应用（查询、插入场景、大数据量）和现存问题，进而理解B+树选择的“版本演进”

【1】线性查找：线性存储数据，依次遍历检索（存在问题：运气不好每次都要追溯到最后一个数据块才能查询）

【2】二叉树查找（二分查找）：在一开始存储数据的时候就将数据按照顺序二叉树排列，按照二分法进行查找（存在问题：存在高度差，如要查找叶子结点的数据其效率和线性查找无异）

【3】平衡二叉树：每增/删一个节点，相应节点位置会转换反转以保证二叉树的平衡；此处引入红黑树（一种特化的AVL树/平衡二叉树）概念：在输入和删除操作时通过特点操作保持二叉树的平衡，插入过程中如果存在左右子树高度差大于1则其会进行自旋操作保持树的平衡。（存在问题：数据量大的情况下高度不可控，IO查找越下沉性能就会越差）

为什么不选用Hash地址法查找？因为查询过程中不仅只有等值查找场景，还有范围查询、模糊查询等操作，使用Hash存储其位置具备不确定性不适用于范围查询，如果要查询范围则需遍历全表，而二叉树只需要遍历左右节点即可

【4】B树：由于平衡二叉树的特点（每个节点最多2个叉），当数据量增大的时候，树的深度将会非常深，每次比较都是拿树节点和内存地址比较，每比较一次IO操作就会下降一层，层数越多时间越久。因此引入B树（多叉平衡树），每个节点维护多个比较范围（多个子节点）。每个节点都存储数据，靠近根节点的先查到，靠近叶子节点的后查到。同样范围查找可能会出现多次回退到父节点又去兄弟节点查找的低效率问题。

【5】B+树：每个非叶子节点只存储索引，真实数据存在于叶子节点。每次查询都遍历到叶子结点才会结束，但由于单个非叶子占用空间减少可以增大分叉数量，如此一来树的深度减少，还可进一步把每个叶子节点用双向链表链接起来，范围查询就更快

线性查找

通过数组存储索引，定义一组数据并存入数组

线性查找：传统的查找方式是依次遍历检索是否匹配，其时间复杂度是O（n），查询效率并不高。极端情况下可能运气不好的话可能每次都要遍历到最后一位，在大数据量场景下这种检索效率是非常低下的。

线性二分查找：二分查找的核心思路：将数据按照一定顺序排列，随后通过二分查找法高效定位数据。采用二分法对有序数组进行检索，每次查找都可将查询范围减半，进而将时间复杂度降低到O（logN），但是每次查找都要计算中间位置。

现存问题：使用数组的方式来实现数据线性排序的方式虽然好用，但是插入新元素的性能太低（因为每插入一个元素都需要将这个元素之后的所有元素后移动一位。如果这个操作发生在磁盘中，其操作必然是灾难性的。因为磁盘的速度远远比内存慢上几十万倍），且有序数组在使用二分查找的时候每次都要计算中间位置。因此考虑引入一个非线性且天然适合二分查找的数据结构

二叉查找树

二叉查找树：二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点，通过引入二叉查找树来解决线性二分查找的缺陷：

插入元素：二叉查找树是一种非线性结构，其不像数组那样的线性结构要求连续排列，其插入的时候可以放置在任意位置，通过指针关联
中间位置的计算：二叉查找树本身就满足左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点，因此在查询的时候不需要计算中间节点的配置，只需要进行比较

现存问题：往极端情况下思考，二叉查找树会出现一种“失衡”的现象（变成一个“单边瘸子”，从而导致其在极端情况下会退化成链表态）

例如插入一组数据：1、2、3、4、5、6，那么这种情况下二叉查找树就会变成一个一边倒的单边瘸子，其查询效率无异于线性查找，时间复杂度为O（n）

由于树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作（假设此处一个节点的大小「小于」操作系统的最小读写单位块的大小），也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能。

即二叉查找树会面临一个问题：由于存在退化成链表的可能性，会使得查询操作的时间复杂度从 O(logn) 升为 O(n)。且随着插入元素越来越多，树的高度也会变高，失衡的高度差意味着磁盘 IO 操作的次数就越多，进而导致查询性能严重下降，再加上不能范围查询，因此不适合作为数据库的索引结构

自平衡二叉树

为了解决上述二叉查找树会在极端情况下退化成链表的问题，引入**平衡二叉查找树（AVL树）**概念。其核心是在二叉查找树的基础上增加一些条件约束：每个节点的左子树和右子树的高度差不能超过 1，进而将查询操作的时间复杂度维持在O（logn）

除了平衡二叉查找树，还有很多自平衡的二叉树，比如【红黑树】也是通过一些约束条件来达到自平衡（红黑树的约束条件更为复杂）

现存问题：结合上述概念分析，自平衡二叉查找树的引入虽然解决了二叉查找树在极端情况下退化成链表的问题，但是随着数据量的增大，数据高度也会逐渐变高（即数据量大的情况下高度不可控）也就意味着磁盘I/O的操作次数也会增加，进而影响整体数据的查询效率。

B树

结合上述概念分析，既然有“恐高症”，何不考虑通过一种方式降低树的高度？例如上述树的子节点都是2个，如果将二叉树改为M叉树则可大大降低树的高度，进而减少IO交互次数。当树的节点越多的时候，并且树的分叉数 M 越大的时候，M 叉树的高度会远小于二叉树的高度

为了解决高度问题，引入了B树概念，它不再限制一个节点只能有2个子节点，而是允许设定M个子节点进而降低树的高度。B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，所以 B 树就是一个多叉树。

例如M=3，则其为一棵3阶的B树，特点是每个节点最多有2 个（M-1个）数据和最多有 3 个（M个）子节点，超过这些要求的话，就会分裂节点

现存问题：B树的每个节点都包含数据（索引+记录），每个节点维护多个比较范围（多个子节点）。每个节点都存储数据，靠近根节点的先查到，靠近叶子节点的后查到。同样范围查找可能会出现多次回退到父节点又去兄弟节点查找的低效率问题（B树做范围查询的时候需要使用中序遍历，会涉及多个节点的磁盘I/O问题，从而导致整体查找效率低下）

B+树

结合上述概念分析，B+树对B树做了相应的升级，B+树设定在非叶子节点只存储索引，在叶子节点存储索引和数据，并将叶子节点通过链表连接起来。单从这点分析，B+树实现了两方面的优化：

由于设定非叶子节点只存储索引，叶子节点存储索引和数据，对比B树其单个节点占用空间减少，一个节点可以分叉更多的子节点，进而有效降低树高度
将每个叶子节点通过链表连接起来，进而提升范围检索效率

可以进一步通过三个方面对比B树和B+树的性能差异：

单点查询效率

B树在进行单个索引查询的时候，越靠近根节点的数据越快被检索（最快为O（1）），但是越远离根节点的数据就需要消耗一定的IO磁盘交互成本，可以理解为B树的查询效率差波动会比较大（旱的旱死涝的涝死）。从平均的时间代价来看会比 B+ 树稍快一些。

B+树中限定非叶子节点只存储索引，叶子节点存储索引和数据。即在同等条件下，单个节点的占用空间减少，则其可以分叉更多的子节点，达到降低树高度=》减少磁盘IO交互次数的目的。（可以理解为同等条件下B+树更加矮胖，其查询原理根节点的数据成本也相对较低）

插入和删除效率

B+树中有大量冗余节点，使得其插入和删除操作并不会太复杂，对比B树的操作，B+树的插入和删除效率更高。

范围查询

B 树和 B+ 树等值查询原理基本一致，先从根节点查找，然后对比目标数据的范围，最后递归的进入子节点查找

B+树中所有叶子节点还有一个链表进行连接，这种设计对范围查找非常有帮助。例如想知道 12-01 和 12-12 之间的订单，这个时候可以先查找到12-01所在的叶子节点，然后利用链表向右遍历，直到找到12-12的节点，这样就不需要从根节点查询了，进一步节省查询需要的时间

如果是B树实现范围搜索，其并没有将叶子节点连接成一个链表，因此只能通过树的遍历来完成范围查询，其涉及到中序遍历概念，可能会出现多次回退到父节点又去其兄弟节点中查找的低效问题（期间就会产生多次的磁盘IO交互），其范围查找效率远不如B+树

因此，结合上述三个方面的拆解，基于大量范围检索场景适合使用B+树（例如数据库），而针对大量的单个索引查询的场景可以考虑使用B树（例如nosql的MongoDB）

3.MySQL中的B+树

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

对比传统的B+树，InnoDB使用的B+树有一些特别的点：

叶子节点之间是通过双向链表进行连接（这样设计的好处是既能向左遍历、也能向右遍历）
B+树的节点内容是“页”，非叶子节点存储的是目录项页、叶子节点存储的是用户记录项页，每个数据页默认大小是16KB

InnoDB根据索引类型的不同分为聚簇索引和二级索引，两者主要区别在于聚簇索引的叶子节点存放是实际数据（完整的用户记录），而二级索引的叶子节点存放的是主键值（当需要通过二级索引定位用户的完整记录时，需要根据获取到的主键进行回表操作）。因为表的数据都是存放在聚集索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚集索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个，而二级索引可以创建多个

MySQL是如何存储数据和索引对象的？

1.索引组织表

数据存储有堆表和索引组织表两种方式

堆表：堆表中的数据无序存放，数据的排序完全依赖于索引（Oracle、Microsoft SQL Server、PostgreSQL 早期默认支持的数据存储都是堆表结构）

堆表的组织结构中，数据和索引分开存储。索引是排序后的数据，而堆表中的数据是无序的，索引的叶子节点存放了数据在堆表中的地址，当堆表的数据发生改变，且位置发生了变更，所有索引中的地址都要更新，这非常影响性能，特别是对于 OLTP 业务

索引组织表：数据根据主键排序存放在索引中，主键索引也叫聚集索引（Clustered Index）。在索引组织表中，数据即索引，索引即数据（MySQL InnoDB 存储引擎就是这样的数据组织方式；Oracle、Microsoft SQL Server 后期也推出了支持索引组织表的存储方式）

参考MySQL中基于InnoDB存储引擎创建的User表，其就是通过索引组织表的方式存储数据，图示示例如下：表 User 的主键是 id，所以表中的数据根据 id 排序存储，叶子节点存放了表中完整的记录，可以看到表中的数据存放在索引中，即数据就是索引，索引就是数据

二级索引：InnoDB 存储引擎的数据是根据主键索引排序存储的，除了主键索引外，其他的索引都称之为二级索引（Secondeary Index）， 或非聚集索引（None Clustered Index），二级索引也是一颗 B+ 树索引，但它和主键索引不同的是叶子节点存放的是索引键值、主键值。

假设用户表中在name列上创建了索引，如果通过name进行查询：select * from User where name = 'Amy';通过二级索引只能定位主键值，此处需要检索所有的用户记录，因此需要通过主键索引再查询一次（即回表概念）

索引组织表中的二级索引设计有一个好处：如果记录发生了修改，除主键索引外的其他索引无需进行维护。除非是记录的主键值发生了修改，二级索引才需要进行维护。此处可以对比堆表的索引实现，会发现索引组织表在大量数据变更的场景下其性能优势会非常明显，因为大部分情况下是不需要维护其他二级索引的。

如何理解索引组织表下二级索引中：数据即索引、索引即数据概念？

# 可以将二级索引理解为一张表，例如索引idx_name可以对照如下表所示
CREATE TABLE idx_name (
    name VARCHAR(128) NOT NULL,
    id BIGINT NOT NULL,
    PRIAMRY KEY(name,id)
);

# 查询数据：根据name进行查询的SQL会拆解为两个步骤
SELECT id FROM idx_name WHERE name = ?
SELECT * FROM User WHERE id = _id; -- 回表

# 插入数据：可以理解为对主键索引表、二级索引表进行了一个事务操作，要么都成功、要么都不成功
START TRANSATION;
INSERT INTO User VALUES (...) -- 主键索引
INSERT INTO idx_name VALUES (...) -- 二级索引
COMMIT;

对于索引还可加入唯一约束，进而转化为唯一索引（也是二级索引），例如对于表User而言，设定name具有唯一约束，并在name列上构建二级索引。类似地也可将唯一约束也理解为一张表

CREATE TABLE idx_name (
    name VARCHAR(128) NOT NULL,
    id BIGINT NOT NULL,
    PRIAMRY KEY(name,id)
) -- 二级索引

CREATE TABLE check_idx_name (
    name VARCHAR(128),
    PRIMARY KEY(name)，
) -- 唯一约束

2.函数索引

User 表构建

CREATE TABLE User (
    id  BINARY(16) NOT NULL,
    name VARCHAR(255) NOT NULL,
    sex CHAR(1) NOT NULL,
    password VARCHAR(1024) NOT NULL,
    money BIG INT NOT NULL DEFAULT 0,
    register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    uuid CHAR(36) AS (BIN_TO_UUID(id)),
    CHECK (sex = 'M' OR sex = 'F'),
    CHECK (IS_UUID(UUID)),
    PRIMARY KEY(id),
    UNIQUE KEY idx_name(name),
    KEY idx_register_date(register_date),
    KEY idx_last_modify_date(last_modify_date)
);

截至目前为止，索引都是创建在列上，从MySQL5.7版本开始，MySQL 就开始支持创建函数索引 （即索引键是一个函数表达式）。 函数索引有两大用处：

优化业务 SQL 性能；
配合虚拟列（Generated Column）

优化业务SQL性能：

先来看一种索引失效的场景，例如在User表上对register_date字段创建了索引，执行SQL语句：SELECT * FROM User WHERE DATE_FORMAT(register_date,'%Y-%m') = '2021-01';

索引的本质是排序，因此对register_date列上创建索引，其排序规则也只是针对register_date，而不是DATE_FORMAT(register_date,'%Y-%m')，因此上述SQL语句执行并不会走idx_register_date索引，即出现所谓的索引失效的情况。可以通过explain关键字来确实是否走索引。

针对上述SQL写法，可以将其转化为范围查询，该语句主要是查询2021年1月份的数据，则可转化为范围查询SELECT * FROM User WHERE register_date > '2021-01-01' AND register_date < '2021-02-01'，可以看到该语句会走idx_register_date索引。

结合上述场景案例分析，如果线上业务真的没有按正确的 SQL 编写或者没有理解索引的正确应用，那么就可能造成数据库存在很多慢查询 SQL，导致业务缓慢甚至发生雪崩的场景。此处为了尽快解决这个问题，还可采取另一个方案：使用函数索引=》创建一个DATE_FORMAT(register_date) 的索引，进而可以利用排序数据快速定位：

# 为DATE_FORMAT(register_date) 创建索引
ALTER TABLE User ADD INDEX idx_func_register_date((DATE_FORMAT(register_date,'%Y-%m')));

# 查看SQL执行计划
EXPLAIN SELECT * FROM User WHERE DATE_FORMAT(register_date,'%Y-%m') = '2021-01';

上述通过创建函数索引的方案可以解决业务线上的燃眉之急，但是一般情况还是建议通过优化SQL的方式来提升效率，否则类似此处对同一份数据做了两份索引，索引的维护也是需要一定成本的

配合虚拟列使用

CREATE TABLE UserLogin (
    userId BIGINT,
    loginInfo JSON,
    cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
    PRIMARY KEY(userId),
    UNIQUE KEY idx_cellphone(cellphone)
);

以上述SQL为例，列cellphone就是一个虚拟列，它是由后面的函数表达式计算而成，本身这个列不占用任何的存储空间，而此处的索引 idx_cellphone 实质是一个函数索引

-- 不用虚拟列
SELECT  *  FROM UserLogin
WHERE loginInfo->>"$.cellphone" = '13918888888'

-- 使用虚拟列
SELECT  *  FROM UserLogin 
WHERE cellphone = '13918888888'

对于爬虫类的业务，会从网上先爬取很多数据，其中有些是需要关心的数据，有些是不关心的数据。通过虚拟列技术，可以展示想要的那部分数据，再通过虚拟列上创建索引，实现对爬取的数据进行快速的访问和搜索

3.联合索引

在实际业务场景中可能会遇到很多复杂的SQL查询场景，例如对多个列进行查询，因此可能会需要创建多个列组成的索引。这种组合索引的合理创建和应用可以进一步提升系统的性能。

组合索引概念核心

（1）组合索引的最左匹配原则

组合索引（Compound Index）是指由多个列所组合而成的 B+树索引，和上述B+ 树索引的原理完全一样，此处是对多个列排序，组合索引既可以是主键索引，也可以是二级索引。

组合索引的排序规则和其定义息息相关，类似（a,b）、（b,a）两个组合索引，其排序规则是完全不同的，随着组合索引的字段变多，在设计上更加要尤其注意

在使用联合索引时，存在最左匹配原则，具体的规则

规则一（最左优先）：MySQL 会从联合索引从最左边的索引列开始匹配查询条件，然后依次从从左到右的顺序匹配，如果查询条件没有使用到某个列，那么该列右边的所有列都无法使用走索引
规则二（范围匹配）：当查询条件中使用了某个列，但是该列的值包含范围查询，范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引

基于上述概念，此处引出MySQL建立多列索引的最左匹配原则概念：最左优先

如果有一个 2 列的索引 (a, b)，则已经对 (a)、(a, b) 上建立了索引；
如果有一个 3 列索引 (a, b, c)，则已经对 (a)、(a, b)、(a, b, c) 上建立了索引；

一旦违背最左原则则会出现索引失效的情况。可以理解为索引的定义即对应其排序规则，例如此处（a,b）其排序规则是先按照a进行排序，当a相同的时候则根据b进行排序。类似地，针对（a,b,c）其排序规则是先按照a进行排序，当a相同的时候则根据b进行排序，如果b相同则继续根据c进行排序。此处以联合索引（a,b,c）进行扩展讲解

CREATE TABLE t_union_abc(
    id  BINARY(16) NOT NULL,
    a VARCHAR(255) NOT NULL,
    b VARCHAR(255) NOT NULL,
    c VARCHAR(255) NOT NULL,
    PRIMARY KEY(id),
    KEY idx_abc(a,b,c)
);

# 分别执行下述语句，确认其所引用到的索引
explain select * from t_union_abc where a = 'test'; # 走idx_abc
explain select * from t_union_abc where a = 'test' and b = 'test'; # 走idx_abc
explain select * from t_union_abc where b = 'test' and a = 'test'; # 走idx_abc (说明SQL中where条件的定义顺序并不影响优化器对索引的选择)
explain select * from t_union_abc where a = 'test' and b = 'test' and c = 'test'; # 走idx_abc
explain select * from t_union_abc where b = 'test'; # 不走idx_abc(查询条件中缺失优先级最高的索引a，B+树第一步就不知道走哪个节点，则需全表扫描即不走索引)
explain select * from t_union_abc where a = 'test'  and c = 'test'; # 只有a走联合索引，c不能走联合索引(查询条件中缺失优先级居中的索引b,B+树可以先根据a先过滤部分数据，然后根据查找到的主键ID进行回表，再去匹配C列的值。这种情况好过直接全表扫描)

# mysql向右匹配遇到范围查找就会停止匹配(范围查找：>、<、between、like)
explain select * from t_union_abc where a like '%test%' and b = 'test' and c = 'test'; # 不走idx_abc(查询条件a遇到范围查找停止匹配，则a索引失效导致B+树第一步不知道该走哪个节点，需要进行全表扫描)
explain select * from t_union_abc where a = 'test' and b like '%test%' and c = 'test'; # a、b走索引、c不走索引

结合上述案例分析，MySQL优化器实际上偷偷做了一些操作来优化SQL执行计划：

【1】索引顺序和查询条件的定义顺序不相同为什么还可以利用到索引？

例如... where b = 'test' and a = 'test';，MySQL优化器会自动调整where子句的条件顺序来匹配是否存在合适的索引，因此不会存在where 子句的顺序问题而造成索引失效的问题（但是在SQL语句编写还是要保证好习惯以便于更好的跟踪和维护）

【2】MySQL向右匹配遇到范围查询（>、<、between、like）会停止匹配

例如... where a like '%test%' and b = 'test' and c = 'test';，查询条件a遇到范围查找停止匹配，则a索引失效导致B+树第一步不知道该走哪个节点，需要进行全表扫描

例如... where a = 'test' and b like '%test%' and c = 'test';，走（a,b）索引。在a、b索引走完之后c已经无序了，因此c无法走索引，此时优化器会认为还不如全表扫描c字段来的快。所以只使用了(a,b)两个索引，影响了执行效率。对于这种场景的索引优化可以通过调整组合索引的定义顺序来进行优化，例如将索引调整为idx_abc(a,c,b)则可以使三个索引字段都用到索引（即通过调整组合索引顺序来进行优化来解决问题也是DBA优先考虑的一种优化方案）

从索引设计的角度来看，如果遇到一些场景无法很好理解概念，则重温索引的要义是排序，判断一个字段走不走索引则看其是否满足有序（例如联合索引(a,b)select * from t where a>2 and b=2=>a走索引，b不走索引），因为b记录是无序的，因此无法走索引。（b是在a相等的情况下才体现局部有序，但是如果此处设定a>2，则b整体上看是无序的，因此不满足走索引的条件）

（2）索引覆盖概念

通过组合索引避免回表的优化技术也称为索引覆盖（Covering Index），简单理解为查询的字段恰好被包括在组合索引定义的字段中。是否回表则取决于是否select了额外的列

# 案例准备
CREATE TABLE `t_sc` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `class` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 插入数据
INSERT INTO `t_sc` (`id`, `name`, `class`, `score`)
VALUES
	(1, 'AAA', 1, 50),
	(2, 'BBB', 2, 23),
	(3, 'CCC', 1, 25),
	(4, 'DDD', 1, 50),
	(5, 'EEE', 2, 98);
	
# 构建联合索引
ALTER TABLE `t_sc` ADD INDEX idx_sc_class_score(class,score);

# 索引覆盖场景
explain select class from t_sc where class = 1;
explain select class,score from t_sc where class = 1;
explain select class,score,id from t_sc where class = 1;

# 因为select了额外的列name，二级索引没有完全覆盖，需要回表操作检索完整信息
explain select * from t_sc where class = 1;

（3）索引下推概念

索引下推是MySQL5.6引入的优化机制，默认开启。索引下推指的是：可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

# 基于上述案例继续分析，联合索引（class,score）
explain select * from t_sc where class > 2 and score = 50;

# 场景案例分析
delete from user1;
drop table user1;
 
CREATE TABLE `user1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` tinyint(4) NOT NULL,
	`address` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
INSERT INTO `user1` (`name`, `age`, `address`) VALUES
('Alice', 40, 'address1'),
('Amy', 23, 'address2'),
('Tom', 18, 'address3'),
('Mike', 22, 'address4');
 
explain SELECT * FROM user1 WHERE name LIKE 'A%' and age = 23;
 
# 查看索引下推是否开启
select @@optimizer_switch
# 开启索引下推
set optimizer_switch="index_condition_pushdown=on";
# 关闭索引下推
set optimizer_switch="index_condition_pushdown=off";

==不使用索引下推实现：==name走索引，age不走索引。因此该SQL的执行流程是先通过二级索引筛选出匹配name条件的记录节点，然后根据节点中存储的主键ID回表获取到记录的完整信息组合成结果集，最后再进行age的条件过滤

使用索引下推实现：name走索引，age不走索引。该SQL的执行流程是先通过二级索引筛选出匹配name条件的记录节点，但是由于索引下推的引入，此时索引节点恰好存储了age的值（虽然age不走索引，但是在二级索引的检索过程中基于索引下推的引入就顺便把age也判断了，进而达到数据过滤的目的），随后根据过滤好的数据执行回表操作，因此索引下推的引入可以减少回表次数

组合索引业务实战（联合索引的优势）

（1）优化order by子句（避免额外排序）

在真实的业务场景中，经常会遇到根据某个列进行查询，然后按照时间排序的方式逆序展示。比如在微博业务中，用户的微博展示的就是根据用户 ID 查询出用户订阅的微博，然后根据时间逆序展示；又比如在电商业务中，用户订单详情页就是根据用户 ID 查询出用户的订单数据，然后根据购买时间进行逆序展示

# orders表构建（MySQL5.7.44）
CREATE TABLE `orders` (
  `o_orderkey` int NOT NULL,
  `O_CUSTKEY` int NOT NULL,
  `O_ORDERSTATUS` char(1) COLLATE utf8mb4_general_ci NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) COLLATE utf8mb4_general_ci NOT NULL,
  `O_CLERK` char(15) COLLATE utf8mb4_general_ci NOT NULL,
  `O_SHIPPRIORITY` int NOT NULL,
  `O_COMMENT` varchar(79) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`o_orderkey`),
  KEY `idx_orders_O_CUSTKEY` (`O_CUSTKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

- 主键：O_ORDERKEY
- 索引：idx_orders_O_CUSTKEY

# 模拟插入数据
INSERT INTO `orders` (`o_orderkey`, `O_CUSTKEY`, `O_ORDERSTATUS`, `O_TOTALPRICE`, `O_ORDERDATE`, `O_ORDERPRIORITY`, `O_CLERK`, `O_SHIPPRIORITY`, `O_COMMENT`)
VALUES
	(1, 0, '', 0.00, '2024-06-27', '', '', 0, ''),
	(2, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(3, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(4, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(5, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(6, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(7, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(8, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(9, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(10, 0, '', 0.00, '2024-06-28', '', '', 0, '');

# SQL 查询，通过explain验证执行计划
EXPLAIN SELECT * FROM orders WHERE o_custkey = 147601 ORDER BY o_orderdate DESC
- 根据索引o_custkey检索数据，然后执行一次回表操作获取到完整的记录，随后需要一次额外的o_orderdate排序
# 执行计划:
1	SIMPLE	orders	NULL	ref	idx_orders_O_CUSTKEY	idx_orders_O_CUSTKEY	4	const	1	100.00	Using index condition; Using filesort

# MySQL8.0中可以通过EXPLAIN额外选项（FORMAT=tree）得到更直观的执行计划（MySQL8.0.21）
EXPLAIN FORMAT=tree SELECT * FROM orders WHERE o_custkey = 147601 ORDER BY o_orderdate DESC
-- output
-> Sort: orders.O_ORDERDATE DESC  (cost=0.35 rows=1)
    -> Index lookup on orders using idx_orders_O_CUSTKEY (O_CUSTKEY=147601)

由于已对列 o_custky 创建索引，因此上述 SQL 语句并不会执行得特别慢，但是在海量的并发业务访问下，每次 SQL 执行都需要排序就会对业务的性能产生非常明显的影响，比如 CPU 负载变高，QPS 降低。要解决这个问题，最好的方法是：在取出结果时已经根据字段 o_orderdate 排序，这样就不用额外的排序操作。为此可以在表 orders 上创建新的组合索引 idx_custkey_orderdate，对字段（o_custkey，o_orderdate）进行索引：

# 创建联合索引
ALTER TABLE orders ADD INDEX idx_custkey_orderdate(o_custkey,o_orderdate);

# 再次查看执行计划
EXPLAIN SELECT * FROM orders WHERE o_custkey = 147601 ORDER BY o_orderdate DESC;
# 执行计划：（走idx_custkey_orderdate索引，且没有额外排序这一步了，但需要回表）
1	SIMPLE	orders	NULL	ref	idx_orders_O_CUSTKEY,idx_custkey_orderdate	idx_custkey_orderdate	4	const	1	100.00	Using where

（2）索引覆盖（避免回表）

结合回表概念理解，SQL需要通过二级索引查询得到主键值，然后再根据主键值搜索主键索引，最后定位到完整的数据。在上述案例中通过selec *需要获取到orders的完整数据，因此需要回表。

回表的核心在于二级索引的叶子节点中只存储了相关的索引和主键，因此当需要检索额外的字段信息时，则需要执行回表过程（即根据主键检索主键索引最后定位到完整的数据信息）。

假设一种场景是查询某些字段，例如select o_custkey,o_orderdate,o_totalprice from orders where = o_custkey = 1001;，如果使得二级索引的叶子节点恰好存储了这些字段值可以直接返回，则是不是可以避免额外的回表操作。这种通过组合索引避免回表的优化技术也称为索引覆盖（Covering Index）

# 先查看当前的执行计划（使用到前面创建的idx_orders_O_CUSTKEY、idx_custkey_orderdate索引，但是二级索引节点中并没有o_totalprice信息，因此需要回表获取到相应信息）
explain select o_custkey,o_orderdate,o_totalprice from orders where o_custkey = 0;
explain select o_custkey,o_orderdate,o_totalprice from orders where o_custkey = 1;

首先执行上述语句，查看两条语句各自的执行计划，然后分析这种诡异情况的出现：正常逻辑来说这条SQL语句应该会用到前面创建的idx_custkey_orderdate索引，但由于二级索引节点中并没有o_totalprice信息，因此需要回表获取到相应信息。（此处为了避免现存索引的影响，记得删掉前面案例中测试用的idx_orders_O_CUSTKEY，只保留idx_custkey_orderdate，否则MySQL优化器会优先选择idx_orders_O_CUSTKEY这个索引）

结合执行结果会发现在等值判断的时候如果指定的查询值是0，它不会走索引（走全表扫描）；指定的查询值是其他则走索引。

这其实也是一种索引失效的特殊场景，一开始思考的方向是考虑可能是0会不会是一种特殊标识？但实际上不仅是如此，而是MySQL优化器在作妖。在MySQL中，当遇到使用等于（=）操作符且值为0时，索引可能不会被使用。这是因为MySQL的优化器认为，在某些情况下，使用索引可能不会比全表扫描更高效，因此可能选择跳过索引。这种情况通常发生在列的基数（即列中不同值的数量）非常低的情况下，比如某个列的所有行的值都是0。在这种情况下，使用索引可能会比全表扫描更慢，因为索引的查找成本可能高于直接扫描全表，因此不选择走索引。要解决这种“索引失效”的场景，可以通过拆解其定义来破解：

确保列的基数足够高，即列中有多种不同的值
如果列的基数很低，但仍希望使用索引，可以尝试以下方法：
- 使用不等于（<>）或范围查询（>、<、BETWEEN等）来强制使用索引
- 使用CASE语句或者IF函数在查询中对0值做特殊处理（将0值转化为其他）
如果在等值查询中跳过的索引，可以尝试强制使用索引（但还是需要结合实际场景分析，如果确认索引能带来更高的查询效率则可考虑强制使用）

上述场景只是测试过程中偶然发现的一个小扩展，继续回归正题，通过构建索引覆盖优化查询效率

# 引入索引覆盖优化执行计划（如果要避免回表，可通过索引覆盖技术，创建(o_custkey,o_orderdate,o_totalprice)组合索引）
ALTER TABLE `orders` ADD INDEX idx_custkey_orderdate_totalprice(o_custkey,o_orderdate,o_totalprice);

# 再次查看优化后的执行计划
explain select o_custkey,o_orderdate,o_totalprice from orders where o_custkey = 0;

# MySQL8.0通过 EXPLAIN 的额外选项 FORMAT=tree，查看上述 SQL 的执行成本
explain FORMAT=tree select o_custkey,o_orderdate,o_totalprice from orders where o_custkey = 0;
-- output
-> Index lookup on orders using idx_custkey_orderdate_totalprice (O_CUSTKEY=0)  (cost=1.25 rows=10)

可以看到引入覆盖索引之后，MySQL优化器选择走idx_custkey_orderdate_totalprice索引来提升检索效率，由于联合索引中存储了o_custkey,o_orderdate,o_totalprice和主键字段，因此不需要额外的回表操作。

那么结合上述等值查找=0不走索引的场景分析，为什么此处又走了索引呢（MySQL表中o_custkey还是全部为0），自然还是归结于MySQL优化器的选择，是因为MySQL优化器认为通过idx_custkey_orderdate_totalprice索引避免回表操作，其性能比全表扫描要高，因此选择了走索引，这点并不矛盾。

# 案例2：将上述创建的idx_custkey_orderdate_totalprice索引设置为不可见(MySQL8的不可见索引功能)，然后执行语句查看执行计划
ALTER TABLE orders ALTER INDEX idx_custkey_orderdate_totalprice INVISIBLE;

# 测试在大数据量场景下覆盖索引强大的优化性能（通过MySQL8的explain format=tree可以更好地分析执行计划）
EXPLAIN SELECT o_custkey,SUM(o_totalprice) FROM orders GROUP BY o_custkey;

索引失效的场景

1.索引的存储结构

先了解索引存储的结构，然后结合案例拆解索引的应用场景，分析其失效的情况。

索引的存储结构跟 MySQL 使用哪种存储引擎有关，因为存储引擎就是负责将数据持久化在磁盘中，而不同的存储引擎采用的索引数据结构也会不相同。MySQL默认的存储引擎是InnoDB，它采用 B+Tree 作为索引的数据结构

在创建表时，InnoDB 存储引擎默认会创建一个主键索引，也就是聚簇索引，其它索引都属于二级索引。

MySQL 的 MyISAM 存储引擎支持多种索引数据结构，比如 B+ 树索引、R 树索引、Full-Text 索引。MyISAM 存储引擎在创建表时，创建的主键索引默认使用的是 B+ 树索引。

InnoDB 和 MyISAM 都支持 B+ 树索引，但是它们数据的存储结构实现方式不同。不同之处在于：

InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身；
MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址；

案例分析

# 案例准备
CREATE TABLE `t_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `assress` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 插入数据
INSERT INTO `t_user` (`id`, `name`, `age`, `assress`, `phone`)
VALUES
	(1, '张某', 18, '北京市海淀区', '13800138000'),
	(2, '林某', 21, '深圳市南山区', '13800138000'),
	(3, '陈某', 17, '广州市海珠区', '13800138000'),
	(4, '周某', 35, '上海市松江区', '13800138000'),
	(5, '曾某', 49, '深圳市宝安区', '13800138000'),
	(6, '黄某', 59, '北京市海淀区', '13800138000'),
	(7, '谢某', 48, '深圳市宝安区', '13800138000'),
	(8, '周某', 29, '深圳市福田区', '13800138000'),
	(9, '吴某', 39, '广州市白云区', '13800138000'),
	(10, '李某', 69, '上海市浦东新区', '13800138000');

如果是MyISAM存储引擎：B+树索引的叶子节点保存数据的物理地址（用户数据的指针），参考下图所示结构（数据仅供参考，不完全一一对照，理解存储数据结构即可）

如果是InnoDB存储引擎：B+树索引的叶子节点保存的是索引+数据本身（索引即数据、数据即索引），叶子节点之间通过双向链表连接（既可以向左又可以向右，提供范围检索支持），参考下图所示结构

InnoDB存储引擎根据索引类型还可分为聚簇索引和二级索引，聚簇索引的存储结构如上图所示。而针对二级索引，其叶子节点存储的是索引+主键值，叶子节点之间使用双向链表连接，参考如下图所示。（此处还可扩展回表、覆盖索引、索引下推等概念，参考前面介绍的案例扩展理解）

2.索引失效的场景

# 案例准备（基于t_user表为name字段构建索引）-MySQL5.7.44
ALTER TABLE `t_user` ADD INDEX idx_name(name);

对索引使用左或者左右模糊like匹配

结合上述组合索引案例分析，如果打破最左原则可能导致组合索引失效的场景。此处拆解单个索引字段中当使用左或者左右模糊匹配导致索引失效的场景（拆解下述8条语句，理解索引失效的场景）

explain select id,name from t_user where name like '某';   -- 走idx_name索引
explain select id,name from t_user where name like '某%';  -- 走idx_name索引
explain select id,name from t_user where name like '%某';  -- 走idx_name索引（基于覆盖索引优化，虽然可能使用的索引为null，但是MySQL优化器还是选择了走idx_name索引）=》覆盖索引下like匹配不会导致索引失效
explain select id,name from t_user where name like '%某%'; -- 走idx_name索引（基于覆盖索引优化，虽然可能使用的索引为null，但是MySQL优化器还是选择了走idx_name索引）=》覆盖索引下like匹配不会导致索引失效

-------------------------------------------------------------

explain select * from t_user where name like '某';    -- 走idx_name索引
explain select * from t_user where name like '某%';   -- 走idx_name索引
explain select * from t_user where name like '%某';   -- 不走索引
explain select * from t_user where name like '%某%';  -- 不走索引

执行计划中type=ALL代表全表扫描（没有走索引），type=range代表走索引扫描（从key=idx_name可以跟踪到实际走了idx_name索引）

从索引定义的角度理解，为什么like关键字模糊匹配无法走索引呢？=》因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较，当使用了'%like、%like%'这类检索条件，就可能存在类似【张某、李某、周某】等多个查询结果，不知道从哪个索引值开始比较，于是只能通过全表扫描的方式来进行查询。

而针对索引覆盖的情况，基于MySQL优化器的选择，索引覆盖遇到like匹配（任意匹配）并不会出现索引失效的现象

对索引使用函数

在SQL语句中经常会使用到一些MySQL自带函数来处理检索结果，而如果只是在字段上创建索引，则索引的排序规则也只是基于该字段。这个时候如果使用了函数对字段进行检索，就会出现索引失效的情况（因为创建的字段索引仅仅是针对该字段进行排序，而不是针对函数处理之后的结果进行排序，因此就会出现所谓的索引失效场景）

explain select * from t_user where name ='张某'; -- 走idx_name索引
explain select * from t_user where length(name)=6; -- 不走索引（对索引使用函数导致失效）

结合前面的学习案例，针对索引使用函数导致索引失效的场景，可以采用MySQL8.0新引入的函数索引来解决（如果是线上业务优化可以采取该应急方案，实际解决建议还是通过优化SQL来进行处理，以减少对同一字段的索引维护成本）

# MySQL8.0 函数索引解决方案
alter table t_user add key idx_name_length ((length(name)));
# 引入函数索引后再次查看SQL执行计划
explain select * from t_user where length(name)=6;

对索引进行表达式计算

# 对索引进行表达式计算导致索引失效
explain select * from t_user where id + 1 = 10; -- 不走索引

# 调整where子句结构
explain select * from t_user where id  = 10 - 1; -- 走索引 （可以理解为等价于id=9）

案例分析：因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。而针对where id = 10 - 1其执行流程实现直接根据id和10-1这一计算结果进行比较，因此可以直接走索引

==思考：==既然MySQL优化器这么智能，为什么不针对这种对索引进行简单计算的场景优化一下，使得其在特殊代码处理下自动转化一下，进而让其可以充分利用索引优势。但实际上MySQL并没有做到这一优化，可能也是考虑表达式计算的复杂性，如果每种情况都要考虑则可能会使得代码很臃肿，倒不如直接干脆告诉程序员这是一种会让索引失效的场景，进而界定边界。

对索引进行隐式类型转换

# 给phone字段添加索引
alter table t_user add key idx_phone (phone);

# 使用int类型查询varchar字段
explain select * from t_user where phone = 13800138000;  -- 存在隐式类型转换，不走索引

# 使用varchar类型查询int字段
explain select * from t_user where id = '1'; -- 走索引

针对上述案例分析，首先要理解MySQL的数据类型转换规则是什么？（看MySQL会将字符串转化为数组处理还是将数组转化为字符串处理）。例如可以通过select "10">9（返回结果是1）的结果来确认MySQL的数据类型转换规则

如果规则是 MySQL 会将自动「字符串」转换成「数字」，就相当于 select 10 > 9，这个就是数字比较，所以结果应该是 1；
如果规则是 MySQL 会将自动「数字」转换成「字符串」，就相当于 select "10" > "9"，这个是字符串比较，字符串比较大小是逐位从高位到低位逐个比较（按ascii码），那么"10"字符串相当于 “1”和“0”字符的组合，所以先是拿 “1” 字符和 “9” 字符比较，因为 “1” 字符比 “9” 字符小，所以结果应该是 0。

# MySQL数据类型转化规则：将字符串转化数字进行比较
select * from t_user where phone = 13800138000; 
等价于=》elect * from t_user where CAST(phone AS signed int) = 13800138000;

select * from t_user where id = '1'; 
等价于：select * from t_user where id = CAST("1" AS signed int);

联合索引最左匹配原则

在使用联合索引时，存在最左匹配原则，具体的规则（结合前面介绍联合索引的案例进行拆解分析）

规则一（最左优先）：MySQL 会从联合索引从最左边的索引列开始匹配查询条件，然后依次从从左到右的顺序匹配，如果查询条件没有使用到某个列，那么该列右边的所有列都无法使用走索引
规则二（范围匹配）：当查询条件中使用了某个列，但是该列的值包含范围查询，范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引

WHERE 子句中的 OR

在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效

# 查询语句(id 主键，age普通列)
explain select * from t_user where id = 1 or age = 18;

OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。解决思路也很简单，给age也设置索引即可

索引选择

1.索引选择的核心：MySQL优化器的执行计划（基于COST）

基于上述对索引的核心概念学习，在这个过程中经常会感觉懵的情况是有时候MySQL生成执行计划和预期效果并不一致（就会陷入究竟是自身原理拆解错误还是说这是MySQL设定的一种特例优化思路），最常见的就是MySQL没有按照预想选择设定的索引（例如创建了索引但是走了全表扫描，即索引失效场景），要理解MySQL数据库中的优化器是怎么执行的，才能更好理解索引的选择。

# SQL 数据准备（t_orders）
# orders表构建（MySQL5.7.44）
CREATE TABLE `t_orders` (
  `o_orderkey` int NOT NULL,
  `O_CUSTKEY` int NOT NULL,
  `O_ORDERSTATUS` char(1) COLLATE utf8mb4_general_ci NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) COLLATE utf8mb4_general_ci NOT NULL,
  `O_CLERK` char(15) COLLATE utf8mb4_general_ci NOT NULL,
  `O_SHIPPRIORITY` int NOT NULL,
  `O_COMMENT` varchar(79) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`o_orderkey`),
  KEY `idx_custkey` (`O_CUSTKEY`),
  KEY `idx_custkey_orderdate` (`O_CUSTKEY`,`O_ORDERDATE`),
  KEY `idx_custkey_orderdate_totalprice` (`O_CUSTKEY`,`O_ORDERDATE`,`O_TOTALPRICE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

- 主键：O_ORDERKEY
- 索引：idx_custkey、idx_custkey_orderdate、idx_custkey_orderdate_totalprice

# 模拟插入数据
INSERT INTO `t_orders` (`o_orderkey`, `O_CUSTKEY`, `O_ORDERSTATUS`, `O_TOTALPRICE`, `O_ORDERDATE`, `O_ORDERPRIORITY`, `O_CLERK`, `O_SHIPPRIORITY`, `O_COMMENT`)
VALUES
	(1, 0, '', 0.00, '2024-06-27', '', '', 0, ''),
	(2, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(3, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(4, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(5, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(6, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(7, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(8, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(9, 0, '', 0.00, '2024-06-28', '', '', 0, ''),
	(10, 0, '', 0.00, '2024-06-28', '', '', 0, '');

基于上述SQL语句，创建了t_orders表，并构建了idx_custkey、idx_custkey_orderdate、idx_custkey_orderdate_totalprice 3个索引。在查询字段 o_custkey 时，理论上可以使用到这3个索引，那 MySQL 优化器是怎么从这三个索引中进行选择的呢？=》基于MySQL优化器生成的执行计划，其选择则是基于成本（COST）

2.执行计划操作成本拆解（CBO）

在关系型数据库中，B+ 树索引只是存储的一种数据结构，具体怎么使用，还要依赖数据库的优化器，优化器决定了具体某一索引的选择（即执行计划），而优化器的选择是基于成本（cost），哪个索引的成本越低，优先使用哪个索引

MySQL的执行过程：一般来说，MySQL数据库由Server层、Engine层组成

Server 层有 SQL 分析器、SQL优化器、SQL 执行器，用于负责 SQL 语句的具体执行过程；
Engine 层负责存储具体的数据，如最常使用的 InnoDB 存储引擎，还有用于在内存中存储临时结果集的 TempTable 引擎

其中SQL 优化器会分析所有可能的执行计划，选择成本最低的执行，这种优化器称之为：CBO（Cost-based Optimizer，基于成本的优化器）

在MySQL中，一条SQL的计算成本公式为：Cost = Server Cost + Engine Cost = CPU Cost + IO Cost

CPU Cost 表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序……这些操作都在 Server 层完成
IO Cost 表示引擎层 IO 的开销，MySQL 8.0 可以通过区分一张表的数据是否在内存中，分别计算读取内存 IO 开销以及读取磁盘 IO 的开销
数据库 mysql 下的表 server_cost、engine_cost 则记录了对于各种成本的计算

# 以MySQL8.0为例拆解
select * from mysql.server_cost;
select * from mysql.engine_cost;

操作成本开销拆解

【1】表 server_cost 记录了 Server 层优化器各种操作的成本，这里面包括了所有 CPU Cost，其具体含义如下

disk_temptable_create_cost：创建磁盘临时表的成本，默认为20
disk_temptable_row_cost：磁盘临时表中每条记录的成本，默认为0.5
key_compare_cost：索引键值比较的成本，默认为0.05，成本最小
memory_temptable_create_cost：创建内存临时表的成本：默认为1
memory_temptable_row_cost：内存临时表中每条记录的成本，默认为0.1
row_evaluate_cost：记录间的比较成本，默认为0.1

即基于此，MySQL任务创建基于磁盘的临时表开销是最大的，其成本是内存临时表开销的20倍。而索引键值比较、记录之间比较开销是比较低的，但是如果比较记录数比较多成本也会很大

【2】表 engine_cost 记录了存储引擎层各种操作的成本，这里包含了所有的 IO Cost，具体含义如下

io_block_read_cost：从磁盘读取一个页的成本，默认值为1
memory_block_read_cost：从内存读取一个页的成本，默认值为0.25

即基于此，MySQL任务从磁盘读取的开销是内存开销的4倍

但上述成本都是可以修改的，如果数据库使用是传统的 HDD 盘，性能较差，其随机读取性能要比内存读取慢 50 倍，则可以通过下面的 SQL 修改成本（可通过介入成本参数来指向性地间接调整SQL优化计划的选择）

INSERT INTO 
engine_cost(engine_name,device_type,cost_name,cost_value,last_update,comment) 
VALUES ('InnoDB',0,'io_block_read_cost',12.5,CURRENT_TIMESTAMP,'Using HDD for InnoDB');
FLUSH OPTIMIZER_COSTS;

案例分析：group by 分析

# MySQL 8.0 指定explain format = json 查看完整的执行计划过程
explain format = json select o_custkey,SUM(o_totalprice) from t_orders group by o_custkey;

# 执行计划过程
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.25"    // 总成本
    },
    "grouping_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "t_orders",  // 涉及表
        "access_type": "index",
        "possible_keys": [         // 可能会使用到的索引
          "idx_custkey",
          "idx_custkey_orderdate",
          "idx_custkey_orderdate_totalprice"
        ],
        "key": "idx_custkey_orderdate_totalprice",   // 真实使用到的索引
        "used_key_parts": [
          "O_CUSTKEY",
          "O_ORDERDATE",
          "O_TOTALPRICE"
        ],
        "key_length": "14",
        "rows_examined_per_scan": 10,
        "rows_produced_per_join": 10,
        "filtered": "100.00",
        "using_index": true,
        "cost_info": {
          "read_cost": "0.25",           // IO COST(engine cost)
          "eval_cost": "1.00",           // CPU COST(server cost) 
          "prefix_cost": "1.25",         // 总成本
          "data_read_per_join": "4K"     // 总的读取记录字节数
        },
        "used_columns": [
          "o_orderkey",
          "O_CUSTKEY",
          "O_TOTALPRICE"
        ]
      }
    }
  }
}

理解了MySQL索引选择是基于SQL执行成本这一概念，则可进一步分析所谓的索引出错问题

3.MySQL索引出错案例分析

MySQL优化器的选择永远是基于成本（SQL的执行成本），因此不要陷入一些自认为的原理拆解场景，要结合实际去分析

案例1：未能使用创建的索引

对于同一个场景，就算是同一条SQL语句，只要检索指定范围不同，优化器的选择也会有所不同

# 对t_orders表创建o_orderdate二级索引
alter table t_orders add key idx_orderdate(o_orderdate);

# SQL 语句1
SELECT * FROM t_orders WHERE o_orderdate > '1994-01-01' and o_orderdate < '1994-12-31';

# SQL 语句2
SELECT * FROM t_orders WHERE o_orderdate > '1994-02-01' and o_orderdate < '1994-12-31';

当数据量比较大的场景下，执行上述两条语句，可能会出现截然不同的MySQL执行计划选择，实际上还是以相应的SQL执行成本作为参考。例如两条语句都能够满足的走二级索引，但语句1执行MySQL可能会选择走ALL全表扫描。原因在于此处的二级索引需要执行回表操作来获取完整的用户记录，当回表的记录数非常大的时候，这个成本就可能存在比直接扫描还要慢（取决于回表的记录数）。而针对SQL语句2中检索的范围缩小，可能需要回表的记录数也会减少，如果此时MySQL发现两种方案对比走索引会更快的话，自然会选择走idx_orderdate。

因此，针对同一SQL的场景，有时候并不是MySQL选择索引出错，更多的是结合实际场景MySQL优化器智能地为程序设计选择最优化地方案来生成SQL执行计划。

案例2：索引创建在有限状态上

这点和前面案例中遇到的一种场景和类似：在讲解组合索引（索引覆盖）的场景下遇到指定了索引但是却走了全表扫描的情况。其核心还是因为MySQL认为当下场景中走ALL的成本更低

结合索引的定义来进行拆解，例如在使用到二级索引、组合索引遇到需要回表的场景，如果说指定检索的索引行的不同的列值数量表少，极端下假设只有一个值0，此时再来分析SQL语句

# 案例准备
CREATE TABLE `t_user` (
  `id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `status` char(1) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

# 数据初始化
insert into t_user values 
	('1','小明','0'),('2','小明','0'),('3','小明','0'),('4','小明','0'),('5','小明','0'),('6','小明','0'),('7','小明','0'),
	('8','小明','0'),('9','小明','0'),('10','小明','0'),('11','小明','0'),('12','小明','0'),('13','小明','0'),('14','小明','0');

# user表上为status创建了索引,假设此时status的不同值只有一个（极端情况考虑），分析下述语句执行过程
explain select * from t_user where status = '0';
- 如果不走索引，则直接执行全表扫描，进行where过滤
- 如果走索引idx_status,则需要进行回表查询完整的记录信息。基于现有的设定会发现如果记录数量特别大的话，则需要回表的次数也会越来越多,因此MySQL会分析其SQL执行开销成本来选择一个最佳的执行计划

这种情况通常发生在列的基数（即列中不同值的数量）非常低的情况下，比如某个列的所有行的值都是0。在这种情况下，使用索引可能会比全表扫描更慢，因为索引的查找成本可能高于直接扫描全表，因此不选择走索引。常见设计场景有类似性别（只有男、女、隐藏这几个字段）、状态值（结合业务设定的几种状态值）

要解决这种“索引失效”的场景，可以通过拆解其定义来破解：

确保列的基数足够高，即列中有多种不同的值
如果列的基数很低，但仍希望使用索引，可以尝试以下方法：
- 使用不等于（<>）或范围查询（>、<、BETWEEN等）来强制使用索引
- 使用CASE语句或者IF函数在查询中对0值做特殊处理（将0值转化为其他）
如果在等值查询中跳过的索引，可以尝试强制使用索引（但还是需要结合实际场景分析，如果确认索引能带来更高的查询效率则可考虑强制使用）

B+ 树索引通常要建立在高选择性的字段或字段组合上，如性别、订单 ID、日期等，因为这样每个字段值大多并不相同

基于上述场景分析，本质上还是结合引入索引和不引入索引两种方式对SQL的业务执行情况进行拆解，MySQL会根据各自的开销成本来选择一种最优的解决方案。那么此处继续思考一个问题，是不是基于上述场景的设定，对于一些status状态的索引就干脆不创建了呢？（答案显而易见，并不能一竿子打翻所有场景应用，不能因这种极端的情况而衍生此处的索引无用论）

深入理解分析，上述场景是由于字段值的有限性，可能导致大部分数据会聚集在某个字段，当记录数越来越多的情况下，这种情况就会演变成案例1中的场景，需要回表的次数越来越多，因此MySQL就会认为在某个节点时使用ALL全表扫描的性能优于使用索引。那么除了上述的解决思路，可以思考一种场景，虽然不同列值的数量有限，但是如果存在一种数据倾斜的情况，是不是就能让它如预期一样走索引，此处继续查看一个例子explain select * from t_user where status = '1';会发现MySQL选择了idx_status（看到此处，会发现目前这种情况实际上又会回归到案例1的场景了，因为status指定的检索范围不同，因此需要回表的次数不同，MySQL基于SQL执行成本来选择最优的执行计划）

因此综合上述案例分析，就算索引如果创建在有效的状态上，也不能抹杀索引的作用。此处可结合实际场景案例分析，例如电商业务中会有一个这样的逻辑：即会定期扫描字段 o_orderstatus 为支付中的订单，然后强制让其关闭，从而释放库存，给其他有需求的买家进行购买。但字段 o_orderstatus 的状态是有限的，一般仅为已完成、支付中、超时已关闭这几种。通常订单状态绝大部分都是已完成，只有绝少部分因为系统故障原因，会在 15 分钟后还没有完成订单，因此订单状态是存在数据倾斜的。当需要筛选出一些异常的订单状态时，索引便可发挥其作用：因为异常的订单状态始终是占少量的（虽然订单状态有限，但数据具有倾斜性），因此当对异常状态的订单进行检索的时候，MySQL就会结合实际的情况来选择是否要走索引（实际上MySQL可能并不明确具体订单状态的数据分布，可能只是粗略根据数据分布占比来决定是否引入全表扫描进而避免二级索引的回表消耗）

可以借助MySQL8中引入的直方图，让优化器直到数据的分布，进而更好的选择执行计划（以上述的t_user为例），校准执行计划

# MySQL 8.0 创建直方图
ANALYZE TABLE t_user UPDATE HISTOGRAM ON status;

# 直方图创建完成，MySQL会收集到字段status的数值分布，可通过命令查询
SELECT v value, CONCAT(round((c - LAG(c, 1, 0) over()) * 100,1), '%') ratio
FROM information_schema.column_statistics, 
JSON_TABLE(histogram->'$.buckets','$[*]' COLUMNS(v VARCHAR(60) PATH '$[0]', c double PATH '$[1]')) hist
WHERE column_name = 'status';

-- output
value   ratio
0	    66.7%
1	    33.3%
EXPLAIN SELECT * FROM t_user WHERE status = '1';

索引应用

索引的优缺点

索引是一把双刃剑，基于前面的学习需清楚的认识到在享受索引带来的检索性能提升的同时也要承担其相应的维护成本，只有合理地创建和利用索引，才能提升整体的数据库性能，除却上述索引的检索性能优势，索引也具备如下劣势（瑕不掩瑜）

①建立索引会生成本地磁盘文件，需要额外的空间存储索引数据，磁盘占用率会变高
②写入数据时，需要额外维护索引结构，增、删、改数据时，都需要额外操作索引
③写入数据时维护索引需要额外的时间开销，执行写SQL时效率会降低，性能会下降

1.MySQL各个索引的优劣分析

主键索引：需考虑主键索引的维护成本，为确保索引的有序性，建议引入有序主键字段

主键索引是以聚簇的形式存放（聚簇索引在存放数据时表数据和索引数据是一起存放的），也就意味着在插入主键数据的时候会进行索引的重排，如果此时主键不是有序的，为了确保索引数据的有序性，则对B+Tree的重构成本会非常大。因此一般建议主键采用自增的方式进行构建而不是UUID，还可通过自定义主键字段的形式通过代码限定确保自增主键的有序性。

联合索引：需注意联合索引失效的场景，考虑联合索引的命中率

为了提高多条件查询d 效率，往往会对多个字段建立索引（构建联合索引），但在构建联合索引的同时，也要考虑到联合索引的命中率，不要无脑构建。

在构建联合索引的时候需要结合实际业务的检索条件进行分析，构建最合理的联合索引，进而有效保证联合索引的命中率

前缀索引：无法支持排序、分组等需要完整字段的场景

前缀索引的特点是短小精悍，可以利用一个字段的前N个字符创建索引，以这种形式创建的索引也被称之为前缀索引，相较于使用一个完整字段创建索引，前缀索引能够更加节省存储空间，当数据越多时，带来的优势越明显。

前缀索引虽然可以有效节省空间，但由于其索引字段中并没有保存一个字段的完整值，因此无法通过前缀索引来完成order by、group by等分组排序工作，也无法完成覆盖扫描工作

全文索引：模糊查询场景应用

做模糊查询时，通常都会使用like%语法，不过这种方式虽然能够实现效果，但随着表越来越大，数据越来越多时，其性能会出现明显下降。通过引入全文索引代替like%语法实现模糊查询，它的性能会比like%快上N倍，但也存在一系列硬伤

①需占用更多的存储：由于全文索引是基于分词实现的：当一个字段建立全文索引后，MySQL会对该字段做分词处理，这些分词结果也会被存储在全文索引中，因此全文索引的文件会额外的大
②分词需时间：由于全文索引对每个字段值都会做分词，因此当修改字段值后，分词是需要时间的，所以修改字段数据后不会立马自动更新全文索引，需要额外写存储过程并调用它手动更新全文索引中的数据
③对中文支持不够友好：类似于英文可以直接通过符号、空格来分词，但针对中文场景，其无法精准的对一段文字做分词，因此全文索引在检索中文时，存在些许精准度问题

如果项目规模比较大，可以引入ElasticSearch、Solr、MeiliSearch等搜索引擎来实现全文检索是一个更佳的选择

唯一索引：唯一索引VS普通索引

在前面的场景案例中需注意区分数据库读、写等不同场景中两种索引的执行流程，以更好地理解唯一索引执行地快慢问题

读数据
- 唯一索引：遇到第一个满足条件的记录就会停止检索
- 普通索引：遇到第一个满足条件的记录会继续检索下一个记录是否满足，直至不满足则停止检索（考虑到MySQL内部机制的按页读取和预读机制，其检索下一个记录的性能损耗基本可以忽略不记）

在对于确保数据唯一的等值查找场景来说，两种方式的读性能都是差不多的；但是如果是存在多个字段值相同的记录的话，普通索引可能需要耗费一定的时间

写数据：会判断要写入的记录是否在内存中
- 唯一索引：
  - 记录在内存中：判断是否重复，不重复则直接在内存中更新或插入数据
  - 记录不在内存中：会先进行一次检索操作，将记录读取到内存中然后判断是否重复，不重复则直接在内存中更新或插入数据
- 普通索引：
  - 记录在内存中：直接在内存中更新或插入数据
  - 记录不在内存中：会将要操作的记录先存储到change buffer中，然后再定期的更新数据

因此大数据量更新的场景下，普通索引的change buffer便能充分发挥其作用

哈希索引

哈希索引，也就是数据结构为Hash类型的索引，一般接触的比较少，毕竟创建索引时都默认用的B+树结构。但要比起查询速度，哈希索引绝对是MySQL中当之无愧的魁首！因为采用哈希结构的索引，会以哈希表的形式存储索引字段值，当基于该字段查询数据时，只需要经过一次哈希计算就可获取到数据。

但哈希结构的致命问题在于无序，也就是无法基于哈希索引的字段做排序、分组等工作。

2.建立和使用索引的正确思路

考虑索引查询的回表问题

在构建二级索引的时候要考虑查询场景的回表问题，如果回表次数过多，其对检索效率也是有一定影响的。为了避免回表，往往借助索引覆盖特性来避免额外的回表消耗。

这也是为什么尽量避免select *写法的主要原因，要跟踪其本质，而不是无脑背答案

建立索引需要遵守的原则

首先要理解索引的原理和核心定义，始终记住一句话合理建立索引可以有效提升检索效率，但也需考虑索引维护的成本，以及避免一些索引失效的场景，不要死记硬背

索引建立需要遵守的原则
- 对于频繁作为查询条件的字段可以酌情创建索引
- 对于经常作为范围取值、排序、分组的字段，通过构建索引提升效率（因为索引具有有序性）
- 主外键关联的字段：在连接查找的时候会频繁使用，需为外键建立索引以提升多表查询性能
- 建立联合索引：需注意最左匹配原则，按照字段的优先级顺序进行组合。尽可能考虑到联合索引的命中率，可以使用联合索引替代一些单值索引的场景，以减少对同一字段的索引维护
- 覆盖索引的充分利用：索引建立也要考虑到索引的正确使用，结合联合索引概念提高索引的命中率同时，借助覆盖索引机制来有效提升检索效率
- 对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为Hash结构
- 对于前缀索引，一般建议索引值不应过长，因此可以引入前缀索引
索引建立时的注意事项
- 对于频繁增删改的字段不建议建立索引，因为索引为了保证有序性需维护相应的结构，存在一定的成本消耗
- 索引在大数据量场景更能发挥优势，如果一个表的数据量并不是特别的大，不建议建立索引，因为索引具有一定的维护成本（不要捡了芝麻丢西瓜）
- 索引一般不建议建立在有限状态的列字段上（涉及到大数据量回表问题），但如果业务场景需要还是可以考虑按需引入
- 如果索引字段无序不建议添加索引（尤其是主键索引最好确保有序性），字段无需容易造成页分裂，维护成本也高
- 注意索引失效的场景：一些需要进行函数等参与计算的字段（考虑建立函数索引，普通索引会失效）、联合索引中不满足最左原则的场景也会导致失效
- 索引的数量不是越多也好，但也不是非要限定在一个数值，从整体上看索引对于大数据量的检索效率提升是非常客观的，合理创建索引而不拘束于限制索引数量，以更好地提升数据库性能

联合索引的最左前缀原则

联合索引的构建尤其要结合实际业务场景去设计：

考虑字段的优先级=》进而确定索引的排序规则
充分利用覆盖索引、索引下推的特性优势=》尽量减少回表次数

3.索引失效排查

在前面的案例中穿插着一些索引相关执行计划的分析，实际上对执行计划的拆解能够帮助开发者更好地理解索引设计和应用。因此要想深入理解索引的场景应用，需对explain要熟练应用并能够从执行计划中拆解SQL的执行方案流程

# explain关键字
explain select * from t_user;

| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t_user | ALL  | NULL          | NULL | NULL    | NULL |    3 |       

# MySQL 8.0 引入format 以更好地观察执行计划
explain format=tree select * from t_user;
-> Table scan on t_user  (cost=1.65 rows=14)

explain format=json select * from t_user;
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.65"
    },
    "table": {
      "table_name": "t_user",
      "access_type": "ALL",
      "rows_examined_per_scan": 14,
      "rows_produced_per_join": 14,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "1.40",
        "prefix_cost": "1.65",
        "data_read_per_join": "14K"
      },
      "used_columns": [
        "id",
        "name",
        "status"
      ]
    }
  }
}

id：这是执行计划的ID值，这个值越大，表示执行的优先级越高。

select_type：当前查询语句的类型，有如下几个值：

simple：简单查询。
primary：复杂查询的外层查询
subquery：包含在查询语句中的子查询
derived：包含在FROM中的子查询

table：表示当前这个执行计划是基于哪张表执行的

type：当前执行计划查询的类型，有几种情况：

all：表示走了全表查询，未命中索引或索引失效
system：表示要查询的表中仅有一条数据
const：表示当前SQL语句的查询条件中，可以命中索引查询
range：表示当前查询操作是查某个区间
eq_ref：表示目前在做多表关联查询
ref：表示目前使用了普通索引查询
index：表示目前SQL使用了辅助索引查询

possible_keys：执行SQL时，优化器可能会选择的索引（最后执行不一定用）

key：查询语句执行时，用到的索引名字

key_len：这里表示索引字段使用的字节数

ref：这里显示使用了那种查询的类型

rows：当前查询语句可能会扫描多少行数据才能检索出结果

Extra：这里是记录着额外的一些索引使用信息，有几种状态：

using index：表示目前使用了覆盖索引查询（稍后讲）
using where：表示使用了where子句查询，通常表示没使用索引
using index condition：表示查询条件使用到了联合索引的前面几个字段
using temporary：表示使用了临时表处理查询结果
using filesort：表示以索引字段之外的方式进行排序，效率较低
select tables optimized away：表示在索引字段上使用了聚合函数

昵称

邮箱

网址

按正序
按倒序
按热度

MySQL-原理篇-②索引（重点）

预览: