本文 的 原文 地址
原始的内容，请参考 本文 的 原文 地址
本文作者：
- 第一作者 老架构师 肖恩（肖恩 是尼恩团队 高级架构师，负责写此文的第一稿，初稿 ）
- 第二作者 老架构师 尼恩 （45岁老架构师， 负责 提升此文的 技术高度，让大家有一种 俯视 技术、俯瞰技术、 技术自由 的感觉）
连环炮1：InnoDB存储引擎
尼恩的叙事风格：故事从最基础的地方讲起。
先简单了解一下MySQL大概的架构：
- 第1层：连接层
对来自客户端的连接进行权限验证并将相关的连接信息维护到连接池中，以便于下次连接。
- 第2层：server 服务层：
提供NoSQL，SQL的API,SQL解析，SQL语句优化，SQL语句缓存等相关组件。
- 第3层：存储引擎层：
提供了一系列可插拔的存储引擎，我们可以通过存储引擎来进行数据的写入与读取，
通过存储引擎，我们可以真正的与硬盘中的数据和日志进行交互，
我们可以根据需求来选择合适的存储引擎进行使用。
- 第4层：文件系统层：
该层包含了具体的日志文件和数据文件以及MySQL相关的程序。
上面的四个部分，MySQL 的架构核心两个部分：服务层（Server Layer）和 存储引擎层（Storage Engine Layer）。
其中，服务层负责 MySQL 的核心逻辑处理，而存储引擎层负责实际的数据存取。
一条 sql 执行总体过程
一条SQL语句的执行过程可以大致分为以下几个步骤：
Client层：接收用户输入的SQL，显示响应的结果
Server层：对SQL进行格式的校验、语言分析、优化和执行，并对执行结果进行返回
- 连接器：用户的认证和授权，对接口进行链接
- 缓存：对查询结果进行缓存，并在对缓存进行查询时返回命中结果
- 分析器：SQL的词法分析和语法分析
- 优化器：生成SQL执行计划，操作索引进行数据的查询
- 执行器：SQL操作引擎（如innodb 引擎），利用文件系统返回查询结果
文件系统层：对数据进行持久化
MySql中SQL语句核心执行流程如下：
Mysql中sql语句执行顺序全景图:
SQL输入 → 解析器（词法分析 → 语法分析 → 语义分析）→ AST → 预处理器（类似语义分析）→ 优化器（逻辑优化 → 物理优化） → 物理查询计划 → 执行引擎 → 结果返回
核心步骤如下：
- **词法分析：**将SQL字符串拆解为原子单元（如关键字、表名、运算符等），生成Token序列
- **语法分析：**验证Token组合是否符合SQL语法规则，构造抽象语法树（AST）描述查询结构
- 预处理器 ，根据一些 mysql规则进一步检查解析树是否合法。
- **逻辑优化：**优化查询语义（如重写子查询、消除冗余列），生成逻辑执行计划（Logical Plan）
- **物理优化：**将逻辑计划映射到物理操作（如选择索引、Join算法、数据分片策略），生成物理执行计划
- **执行代码：**编译器或引擎将物理计划转换为底层可执行指令（如机器码/字节码），驱动数据库完成计算并返回结果。
InnoDB存储引擎
MySQL 中的数据最终是保存在硬盘上的，但具体保存在哪些文件里，要看你用了哪种存储引擎。
MySQL 支持好几种存储引擎，每种引擎的存数据方式不一样。
InnoDB是MySQL最常用的存储引擎，也是默认的存储引擎，下面重点分析下
InnoDB存储结构，分为一下4部分
(1) 表空间（Tablespace）：MySQL中存储表的最高层逻辑结构，由多个段组成，对应磁盘上的一个或多个数据文件。
(2) 段（Segment）：表空间的组成部分，是逻辑上连续的数据集合（如索引段、数据段），由多个区（extent）构成。
(3) 区（Extent）：InnoDB中空间分配的基本单位（默认1MB，含64个连续页），用于减少频繁单页分配的开销。
(4) 页（Page）：InnoDB磁盘管理的最小单元（默认16KB），存储实际数据或索引，是内存与磁盘交互的基本单位。
其中最重要的是“页”的结构。InnoDB 在磁盘上存储数据，但处理数据是在内存中完成的。
为了提高效率，InnoDB 不是一条一条地读写数据，而是以“页”为单位进行读写操作。
页的大小可以通过参数 innodb_page_size 设置，常见的值有 64KB、32KB、16KB（默认）、8KB 和 4KB。也就是说，每次从磁盘读取或写入数据时，至少是以这个页的大小为单位进行操作。
**总结一句话：**InnoDB 的存储结构就像一本书的层级：书（表空间） → 章节（段） → 小节（区） → 页面（页），最终每一页上写着真正的内容（数据）。
InnoDB行格式
记录在磁盘上的存储方式称为 行格式。
InnoDB支持 四种行格式：
(1) Compact
(2) Redundant
(3) Dynamic
(4) Compressed
可以通过以下SQL语句指定或查看行格式：
-- 创建表时指定行格式
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
-- 修改表的行格式
ALTER TABLE 表名 ROW_FORMAT=行格式名称;
-- 查看表的行格式
SHOW TABLE STATUS LIKE '<表名>';
MySQL版本与默认行格式：
- 5.0之前：Redundant
- 5.0-5.7：Compact
- 5.7之后：Dynamic
Compact行格式
Compact行格式包含三部分额外信息：
（1）变长字段长度列表：
- 存储所有变长字段(VARCHAR、TEXT等)的实际长度
- 按列顺序逆序排列
- 只存储非NULL值的长度
（2）NULL值列表：
- 统一管理所有可为NULL的列
- 用1表示NULL，0表示非NULL
- 如果没有可为NULL的列，则不存在此部分
为什么有Null值列表？Compact行格式将NULL值统一存储在NULL值列表中，用1表示NULL、0表示非NULL，避免了在真实数据区浪费空间存储NULL值。
假设表有3个允许为NULL的列（col1, col2, col3），插入一行数据为 (NULL, 'abc', NULL)，则NULL值列表的大致二进制表示为 101
（3）记录头信息：
包含记录的各种状态信息
| 字段名 | 大小(bit) | 描述 |
|---|---|---|
| 预留位1 | 1 | 未使用 |
| 预留位2 | 1 | 未使用 |
| delete_mask | 1 | 标记记录是否被删除 |
| min_rec_mask | 1 | B+树非叶子节点最小记录标记 |
| n_owned | 4 | 当前记录拥有的记录数 |
| heap_no | 13 | 记录在页中的位置 |
| record_type | 3 | 记录类型(0:普通, 1:B+树节点, 2:最小, 3:最大) |
| next_record | 16 | 下一条记录的相对位置 |
Compressed在Dynamic基础上增加了数据压缩功能，特别是对大字段(BLOB、TEXT等)使用zlib算法压缩。虽然节省空间，但会降低性能，一般业务场景不推荐使用。
Redundant行格式
Redundant是MySQL 5.0之前的行格式，现已很少使用。
Redundant 与Compact的主要区别：
- 用"偏移地址表"代替变长字段列表和NULL值列表
- 记录头信息中多了n_field和1byte_offs_flag字段
- 少了record_type字段
Dynamic行格式
Dynamic是MySQL 5.7后的默认行格式，与Compact基本相同，主要区别在于处理溢出数据的方式：
- Compact：存储前768字节+20字节溢出页地址
- Dynamic：直接存储20字节溢出页地址，不存前768字节
行溢出机制：
这种在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中的情况就叫做行溢出，存储超出768字节的那些页面也被称为溢出页（uncompresse blob page）。
行溢出临界点：
InnoDB规定每页至少存储2条记录，加上额外信息(约132字节)和每条记录的额外信息(27字节)，计算得出：
- 单列数据小于8099字节时不会溢出
- 多列时这个值会更小
InnoDB数据页结构
InnoDB的数据页(16KB)被划分为多个部分：
| 名称 | 描述 | 大小 |
|---|---|---|
| File Header | 页通用信息 | 38字节 |
| Page Header | 页专有信息 | 56字节 |
| Infimum+Supremum | 最小和最大记录 | 26字节 |
| User Records | 实际存储的记录 | 可变 |
| Free Space | 未使用空间 | 可变 |
| Page Directory | 记录位置索引 | 可变 |
| File Trailer | 页完整性校验 | 8字节 |
每当我们插入一条记录，都会从Free Space部分（尚未使用的存储空间） 中申请一个记录大小的空间划分到User Records部分，
当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，
如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：
记录存储示例
创建测试表并插入数据：
CREATE TABLE test(
a1 INT,
a2 INT,
a3 VARCHAR(100),
PRIMARY KEY (a1)
) CHARSET=ascii ROW_FORMAT=Compact;
INSERT INTO test VALUES(1, 10, 'aaa');
INSERT INTO test VALUES(2, 20, 'bbb');
INSERT INTO test VALUES(3, 30, 'ccc');
INSERT INTO test VALUES(4, 40, 'ddd');
这些记录，就如下图所示，存储在User Rcords里
InnoDB 记录头信息属性总结
(1)delete_mask（删除标记）
- 标记记录是否被删除（1=已删除，0=未删除）。
- 删除的记录不会立即物理移除，而是加入垃圾链表，其空间可被后续插入的记录复用，避免频繁数据移动带来的性能损耗。
(2)min_rec_mask（B+树最小记录标记）
- 仅B+树非叶子节点的最小记录会设置该标记（1），普通记录和叶子节点记录均为0。
- 用于快速定位索引层级中的最小节点。
(3)n_owned（分组记录数）
- 在页目录分组中，每个组的最后一条记录（即组内最大记录）存储该组的记录总数。
- 例如，某组有4条记录，则其末尾记录的
n_owned=4。
(4)heap_no（记录物理位置）
- 表示记录在页中的存储顺序，从2开始编号（0=Infimum伪记录，1=Supremum伪记录）。
- 例如，用户插入的4条记录的
heap_no依次为2、3、4、5。
(4)record_type（记录类型）
0=普通记录1=B+树非叶子节点记录2=Infimum（最小伪记录）3=Supremum（最大伪记录）
（5）next_record（记录链指针）
- 存储按主键排序的下一条记录的偏移量，形成有序单链表。
- 链表顺序：
Infimum → 最小主键记录 → ... → 最大主键记录 → Supremum。 - 例如，
next_record=32表示从当前记录向后32字节即下一条记录。
从图中可以看出来，我们的记录按照主键从小到大的顺序形成了一个单链表。
Page Directory（页目录）
现在我们知道，页中的记录是按照主键从小到大排列的单链表。
虽然单链表插入删除很方便，但查找效率不高，最坏情况下要遍历所有记录。
为此，InnoDB设计了页目录结构，就像书的目录一样，可以快速定位记录位置。
页目录的工作原理
（1）分组管理：
- 所有正常记录（包括最大最小记录，不包括已删除记录）被分成若干组
- 每组最后一条记录的
n_owned字段记录本组的记录数量 - 提取每组最后记录的地址偏移量作为"槽"(slot)，用于快速查找
（2）分组规则：
- 第一组（最小记录组）只能有1条记录
- 最后一组（最大记录组）可以有1-8条记录
- 中间组保持4-8条记录
（3）动态分组：
- 初始只有最小和最大记录两个组
- 插入新记录时，找到合适组并增加
n_owned计数 - 当组记录数达到8条时，会分裂成4条和5条两个新组
实际案例演示
我们继续向测试表插入8条记录：
-- 插入8条测试数据
INSERT INTO test VALUES(5, 50, 'eee'); -- 主键5
INSERT INTO test VALUES(6, 60, 'fff'); -- 主键6
INSERT INTO test VALUES(7, 70, 'ggg'); -- 主键7
INSERT INTO test VALUES(8, 80, 'hhh'); -- 主键8
INSERT INTO test VALUES(9, 90, 'iii'); -- 主键9
INSERT INTO test VALUES(10, 100, 'jjj');-- 主键10
INSERT INTO test VALUES(11, 110, 'kkk');-- 主键11
INSERT INTO test VALUES(12, 120, 'lll');-- 主键12
查找记录的过程
查找特定主键记录只需两步：
（1）二分查找定位槽位：
- 比较中间槽位的主键值与目标值
- 根据比较结果缩小查找范围
（2）遍历组内记录：
- 找到对应槽位后，通过
next_record指针遍历组内记录 - 直到找到目标记录
查找示例：主键6的记录
(1) 计算中间槽位：(0+3)/2=1
(2) 槽1的主键是4 < 6，所以调整low=1
(3) 槽2包含主键5-8的记录
(4) 从主键5开始遍历，找到主键6的记录
注意：如果查到数据在最后一个槽位组，需要先定位到上一个槽位的最后一条记录，再向下查找。
这种设计既保持了链表的灵活性，又通过目录结构大幅提升了查找效率，是典型的空间换时间策略。
File Header(文件头部)
File Header是各种类型页的通用头部，包含：
- FIL_PAGE_OFFSET：页的唯一编号
- FIL_PAGE_PREV/FIL_PAGE_NEXT：前后页的页号，形成双向链表
通过这些信息，InnoDB将所有页组织成一个完整的结构。
页之间通过 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 相互连接，形成一个双向链表的结构，参考下面的图：
InnoDB 和 MyISAM对比
以下是 InnoDB 和 MyISAM 的核心区别对比表：
| 对比项 | InnoDB | MyISAM |
|---|---|---|
| 事务支持 | 支持（ACID兼容） | 不支持 |
| 锁机制 | 行级锁（并发性能高） | 表级锁（并发性能差） |
| 外键约束 | 支持 | 不支持 |
| 崩溃恢复 | 通过redo log自动恢复 | 崩溃后易丢失数据 |
| 存储文件 | .ibd（数据+索引） |
.MYD（数据）+ .MYI（索引） |
| 索引类型 | 聚簇索引（数据按主键物理排序） | 非聚簇索引（数据与索引分离） |
| 全文索引 | 5.6+版本支持 | 原生支持（适合文本搜索） |
| COUNT(*)性能 | 需全表扫描（较慢） | 直接存储行数（极快） |
| 压缩特性 | 不支持表压缩 | 支持表压缩（节省空间） |
| 适用场景 | OLTP（高并发事务，如订单、支付） | OLAP（读多写少，如日志分析） |
（1）**事务与锁：**InnoDB的行级锁适合高并发写入，MyISAM的表级锁在写入时会阻塞全表。
（2）**索引设计：**InnoDB的聚簇索引让主键查询极快，但二级索引需要回表；MyISAM的索引和数据分离，所有查询均需通过索引指针访问数据。
（3）性能取舍：MyISAM的COUNT(*)和全文索引性能更好，但牺牲了事务安全和并发能力。
（4）**恢复能力：**InnoDB的redo log保证崩溃后数据一致，MyISAM修复需手动REPAIR TABLE。
总结：
- InnoDB：需要事务、高并发写入或数据安全。
- MyISAM：只读业务、全文搜索或临时分析（但MySQL 8.0+已逐步弱化MyISAM支持）。
连环炮2：MySQL索引
为什么要有索引
InnoDB 中的数据页是存储记录的基本单位。多个数据页之间通过双向链表连接，每个数据页内部的记录按主键从小到大排列成一个单向链表。每个页还会生成一个页目录，用来加快查找速度。
（1）在一个数据页中查找：
- 根据主键查找：可以通过页目录使用二分法快速找到对应的槽（slot），然后在该槽对应的记录组里逐条查找目标记录。
- 根据非主键列查找：因为没有为非主键列建立页目录，所以只能从最小记录开始，一条一条地遍历整个链表，直到找到符合条件的记录。
（2）在多个数据页中查找：
先找到记录可能所在的那个页；
再在那个页中查找具体的记录。
**结论：**如果没有索引，不管你是用主键还是其他列来查，都得从第一个页开始，顺着双向链表一个个找下去，也就是全表扫描，效率非常低。
基本索引方案
我们以test表为例（假设已经清空数据），为了方便理解，可以把test表的行格式简化如下：
CREATE TABLE test(
a1 INT,
a2 INT,
a3 VARCHAR(100),
PRIMARY KEY (a1)
) CHARSET=ascii ROW_FORMAT=Compact;
索引就像书的目录，帮助我们快速找到数据位置。在数据库中，每个数据页都有一个"页目录"，用来快速定位记录。这些数据页需要满足：后一个页中的所有记录主键值必须大于前一个页中的最大主键值。
假设每个数据页最多存放3条记录（实际能存更多），我们先插入3条记录：
-- 插入三条测试数据
INSERT INTO test VALUES(1, 10, 'aa');
INSERT INTO test VALUES(2, 20, 'bb');
INSERT INTO test VALUES(4, 40, 'dd');
当插入第4条记录时：
INSERT INTO test VALUES(3, 30, 'cc');
因为每页只能放3条记录，需要新建一个页。但新页中的主键值3小于前一页的最大值4，这违反了规则。所以系统会：
(1) 把主键4的记录移到新页
(2) 把主键3的记录插入前一页
这个过程叫做页分裂。
实际存储中，数据页的物理位置可能不连续：
为了快速找到特定主键的记录，我们需要为这些页建立目录。目录项包含每页的最小主键值和页号：
查找过程示例（找主键5的记录）：
(1) 用二分法在目录中定位：4 < 5 < 7 → 目录项2 → 页23
(2) 在页23中查找具体记录
关键结论：这个目录结构就是我们说的索引。
InnoDB中的索引方案
InnoDB使用数据页来存储目录项，为了区分：
- 普通用户记录
- 目录项记录（使用record_type字段区分）
当目录项太多时，会使用多级目录结构，也就是会再多整一个存储目录项记录的页。
所以如果此时我们再向上图中插入一条主键值为10的用户记录的话：
这种结构就像：是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据
- 最上层：根节点
- 中间层：非叶子节点（内节点）
- 最下层：叶子节点（存储实际数据）
聚簇索引
我们上面介绍的B+树就是聚簇索引，特点：
按主键值排序存储记录和页
叶子节点存储完整的用户记录
聚簇索引特点：
- InnoDB自动创建，无需手动建立
- 索引和数据存储在一起（索引即数据）
- 叶子节点包含所有列的值
二级索引
二级索引（辅助索引）与聚簇索引的区别：
(1) 排序依据不同
- 聚簇索引：是按照主键排序的
- 二级索引：是按照某个普通列（比如 a2）来排序的
（2）叶子节点存的数据不同
- 聚簇索引：叶子节点保存的是完整的数据行
- 二级索引：叶子节点只保存了索引列的值和对应的主键，不是整条记录
（3）目录项内容不同
- 聚簇索引：目录项里是主键 + 页号
- 二级索引：目录项里是索引列值 + 页号
这样设计的好处是：
- 聚簇索引查找效率高，因为数据就在叶子节点
- 二级索引节省空间，但需要回表查询才能拿到完整数据
索引的代价
使用索引虽然能加快查询速度，但也有两个主要缺点：
(1) 占用更多空间
每个索引都对应一棵B+树结构，而B+树的每个节点都是一个数据页，默认占16KB空间。所以索引建得越多，占用的存储就越大。
(2) 影响写入速度
每次新增、删除或修改数据时，不仅要操作表本身，还要同步更新所有相关的索引。为了保持排序顺序，可能需要做这些事：
- 记录移位
- 页面分裂
- 页面回收
全表扫描、 索引扫描、 回表查询、索引覆盖扫描的对比
当执行一个 SQL 查询语句时，Mysql 会根据优化器的选择，使用不同的执行计划来执行。
其中，最常见的执行计划有以下几种：
(1) 全表扫描
(2) 索引扫描
(3) 回表查询
(4) 索引覆盖扫描
（1）全表扫描：
顾名思义，就是扫描整张表的所有数据记录，逐条检查是否满足条件。
这种执行计划通常在没有合适的索引或者条件过于复杂时使用。
SELECT * FROM user WHERE phone='13812345678';
若没有 对phone 字段建立索引，数据库会逐行扫描全部用户数据，检查每一行的手机号是否符合条件。
（2）索引扫描：
就是根据条件， 在索引上进行查找，并返回满足条件的记录。
简单的说：就是 通过 索引树结构 快速定位目标数据。
索引扫描 比 全表扫描性能高，为啥呢？
B+树是一种高度平衡的多叉树结构，其树高通常仅为3-4层， 通过B+树 快速定位目标路径。
例如查询age=20 从根节点出发，逐层比较节点键值，最终定位到叶子节点中的年龄=20的记录 。仅需访问3-4个磁盘块（对应树高），无需遍历全部数据
全表扫描 的话， 需读取所有数据页（如用户表中所有用户记录的物理存储块），数据量大时I/O次数剧增 。
这种执行计划通常在有合适的索引且条件较为简单时使用。
SELECT * FROM user WHERE phone='13812345678';
若 对phone 字段建立索引，数据库会逐行 phone 字段 索引的 B+树 进行 快速定位 扫描 ，而不需要 全表扫描 了。
（3）回表查询：
首先 根据条件在 二级索引 上进行查找，并返回满足条件的记录，然后再根据索引指针去 主键索引（聚族索引） 访问数据记录，获取查询所需的其他字段。
原因：SELECT的 字段 存在 非索引列， 而 二级索引叶子节点存储的是主键值而非数据地址， 所以先 通过二级索引找到主键后，需回到 主键索引树 查找完整数据。
假设执行
SELECT name, phone FROM user WHERE age=20
若 对age字段建立索引， 而 name, phone 没有索引， 就需要回表查询。
回表查询 降低性能, 因为 多一次索引树查询。 需要通过下面的 索引覆盖 来进行优化。
（4）索引覆盖扫描：
根据条件在索引上进行查找，并通过 索引直接 返回满足条件的记录，但是不需要再访问 主键索引树 ，因为查询所需的所有字段都在索引中。
这种执行计划通常在有合适的索引且查询字段较少时使用。
假设执行
SELECT name, phone FROM user WHERE age=20
优化方案：建立(age, name, phone)联合索引 → 实现索引覆盖扫描。
全表扫描、 索引扫描、索引覆盖扫描、 回表查询 的性能对比：
| 类型 | 扫描对象 | 是否需要回表 | 性能 |
|---|---|---|---|
| 全表扫描 | 整张表数据 | 否 | 最差（大数据量时） |
| 索引扫描 | 索引树 | 视SELECT字段而定 | 中等 |
| 索引覆盖扫描 | 索引树 | 否 | 最优 |
| 回表查询 | 索引树 + 主键索引树 | 是 | 较差 |
回表查询
什么是回表查询？
什么是回表查询：
MySQL回表查询是指在使用非聚簇索引检索数据时，索引未能完全覆盖查询所需字段，需要根据索引记录的主键值回到聚簇索引（主键索引）中二次查询完整数据行的过程。
回表查询原因：
InnoDB中，聚簇索引的叶子节点存储数据行，而非聚簇索引叶子节点仅存储主键值。
若通过非聚簇索引查询非索引字段（如：索引为name，但查询SELECT *），需先扫描非聚簇索引获取主键，再通过主键查询聚簇索引获取完整数据。
回表查询影响：
回表增加磁盘I/O次数，降低查询效率，在大数据量或高并发场景下尤为明显。
回表查询优化方式：
可通过覆盖索引（索引包含查询字段）避免回表。
例如，索引包含(name, age)时，查询SELECT age可直接从索引获取数据，无需回表。合理设计索引结构可显著提升查询性能。
通过一张图，解释下回表查询，下图：那么数据库server层 通过 Engine 利用 age字段的 非聚集索引，查到 age=19的数据的id 为47。
server层 再通过 Engine 利用 id 上的聚集索引，快速地找到47 对应的员工recode记录"张5"，这就导致了回表查询。
上图案例磁盘IO数：非聚族索引3次+获取记录（聚族索引）回表3次
什么情况下索引会失效
SQL 索引不生效 十大经典场景
参考文章：凌晨2点，报警群炸了：一条SQL执行200秒！搞定之后，我总结了一个慢SQL查询、定位分析解决的完整套路
（1）使用 != 或 <> 操作符
如果你用 != 或者 <> 来做条件判断，索引通常不会起作用。因为数据库不知道要查哪些数据，只能全表扫描。
（2）使用 OR 连接多个条件
当查询中用了 OR，如果其中一个条件没有索引，整个查询可能就不会走索引。
（3）在字段上使用函数或表达式
比如写成这样：
WHERE YEAR(create_time) = 2023
或者：
WHERE id + 1 = 100
这种情况下，索引会失效，因为数据库需要先计算结果，再比对。
（4）模糊查询以 % 开头
像这样的语句：
WHERE name LIKE '%张'
索引就不起作用了。只有 % 在后面才有效，比如 '张%'。
（5）类型转换导致索引失效
比如字段是字符串类型，但你传的是数字：
WHERE mobile = 13800000000
这时候可能会自动转类型，导致索引失效。
（6）联合索引没用最左前缀原则
联合索引 (a, b, c)，如果你只查 b 或 c，或者跳过前面的字段，索引也不会生效。
（7）查询返回太多数据（超过一定比例）
如果一个查询返回的数据太多，比如占整张表的大部分，数据库会觉得直接扫表更快，就不走索引了。
（8）使用 NOT IN 或 NOT EXISTS
这些否定型条件会让数据库无法高效利用索引，容易变成全表扫描。
（9）字段允许为 NULL，且查询未处理 NULL 值
有些数据库对 NULL 的处理比较特殊，如果没有特别处理，也可能影响索引的使用。
（10）统计信息不准或索引损坏
有时候索引虽然存在，但数据库的统计信息过时，或者索引本身损坏，也会导致索引没被使用。
索引创建原则有哪些
(1) 数据量大、查询频繁的表才加索引。比如单张表超过10万条数据时，加索引能明显提升查询速度，改善用户体验。
(2) 对经常用来做查询条件（where）、排序（order by）、分组（group by）的字段加索引，这样可以加快这些操作的速度。
(3) 优先选择区分度高的字段建索引。区分度越高，查找越快。最好能建唯一索引，效率更高。
(4) 如果字段是字符串且比较长，可以考虑前缀索引。也就是只取字段的前几个字符建立索引，节省空间又不影响性能。
(5) 尽量用联合索引，少用单列索引。联合索引很多时候可以覆盖查询字段，减少回表查询，提高效率，还省空间。
(6) 不要随便加太多索引。索引多了虽然查得快，但会影响插入、更新、删除的速度，维护成本高。
(7) 如果字段不需要为空，建表时要加上NOT NULL约束。这样优化器能更好地判断该不该用这个索引。
连环炮3：慢查询问题
慢查询详细内容参考：凌晨2点，报警群炸了：一条sql 执行200秒！搞定之后，我总结了一个慢SQL查询、定位分析解决的完整套路
什么是慢查询
表象上看，页面加载过慢、接口压测响应时间过长（超过1s）就是慢查询
经常出现慢查询的场景如下：
- 聚合查询
- 多表查询
- 表数据量过大查询
- 深度分页查询
- 各种索引失效场景
如何定位慢查询
Arthas 定位慢查询
假设有一个电商系统，用户反馈"订单列表接口"加载缓慢，经常需要5-6秒才能返回结果。我们需要使用Arthas快速定位问题原因。
基本步骤
(1) watch DispatcherServlet 找慢接口
(2) trace *Controller 追踪调用链
(3) watch PreparedStatement 抓SQL
1. 启动Arthas
java -jar arthas-boot.jar
//选择目标Java进程
2. 定位慢接口入口
watch org.springframework.web.servlet.DispatcherServlet doDispatch '#cost>1000' -n 3 -x 2
watch命令是 Arthas 的 实时监控工具，用于观察方法调用时的 参数、返回值和异常信息，支持 条件过滤 和 结果展开，常用于快速定位方法执行细节。
**
#cost>500：**只显示执行时间超过500毫秒的请求**
-n 5：**最多只显示5条匹配结果**
-x 2：**将输出结果展开显示2层深度
-x 1： 对象会显示为Object@1234这种简略形式-x 2(常用)： 对象会显示为User{name="张三", age=25, department=Department@1234}- ``-x 3
：对象会展开User{ name=“张三”,age=25,department=Department{name=“技术部”,manager=User@4567}}`
输出结果：
ts=2023-09-01 14:22:15; [cost=5200ms]
target=DispatcherServlet@4948b8c;
params[0]=GET /api/orders?page=1&size=50;
params[1]=Response@3a4b35c;
returnObj=ModelAndView[view=null; model={orderList=ArrayList[50]}];
分析：
- 确认
/api/orders接口确实很慢（5.2秒） - 返回了50条订单数据
3. 追踪Controller方法
trace com.example.controller.OrderController getOrderList '#cost>100' -n 3
trace是 Arthas 的 调用链追踪工具，能显示方法内部每一层的执行耗时，帮你快速定位代码中的性能瓶颈
输出结果：
`---[5100ms] com.example.controller.OrderController.getOrderList()
+---[5000ms] com.example.service.OrderService.queryOrders()
`---[100ms] com.example.controller.OrderController.buildResponse()
分析：
- 99%的时间消耗在OrderService.queryOrders()
4. 深入Service层分析
trace com.example.service.OrderService queryOrders '#cost>100' -n 5
输出结果：
`---[5000ms] com.example.service.OrderService.queryOrders()
+---[4800ms] com.example.repository.OrderRepository.findByUserId()
+---[150ms] com.example.client.InventoryService.checkStock()
`---[50ms] com.example.service.OrderService.formatResult()
分析：
- 主要耗时在数据库查询(4800ms)
- 次要耗时在库存服务调用(150ms)
5. 捕获问题SQL
watch com.mysql.cj.jdbc.ClientPreparedStatement executeQuery 'params[0]' '#cost>100' -x 1
输出结果：
ts=2023-09-01 14:25:33; [cost=4800ms]
params[0]=SELECT * FROM orders o
LEFT JOIN order_items i ON o.id=i.order_id
LEFT JOIN products p ON i.product_id=p.id
WHERE o.user_id=12345 AND o.status='ACTIVE'
ORDER BY o.create_time DESC
LIMIT 50 OFFSET 0
后面再根据SQL语句进行调优，也就是SQL调优，或者缓存优化
Skywalking 定位慢查询
使用 SkyWalking 定位慢查询，只需在 Trace 页面筛选高耗时请求，展开 MySQL/JDBC Span 查看 db.statement（SQL语句）和 db.execute_time（执行时间），快速锁定问题SQL并优化。
示例：
Trace → 过滤 Duration>1000ms → 点击MySQL Span → 发现慢SQL: SELECT * FROM orders WHERE无索引
如何在 Trace 列表中筛选高耗时请求：
点击某个 MySQL 请求后，看到具体慢 SQL 的详情
Prometheus 定位慢查询
要找出数据库中的慢查询，可以用 Prometheus 搭配数据库监控工具（比如 mysqld_exporter），收集关键指标，再通过 Grafana 展示数据并设置告警。
主要流程如下：
(1) 安装并配置数据库监控插件（如 mysqld_exporter）
(2) Prometheus 抓取这些监控数据，比如mysql_global_status_slow_queries 和 jdbc_query_duration_seconds 等关键指标
(3) 在 Grafana 中查看慢查询趋势
(4) 设置告警规则，发现异常时及时通知
示例：
# 监控慢查询率突增
rate(mysql_global_status_slow_queries[5m]) > 10
MySQL自带慢日志
MySQL慢查询日志是记录执行时间超过设定阈值（如long_query_time=1秒）的SQL语句的日志文件，包含执行时间、扫描行数等关键信息，用于定位性能瓶颈，可通过mysqldumpslow或pt-query-digest工具分析优化。
开启慢日志
# 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
分析慢日志
mysqldumpslow -s t /var/log/mysql/mysql-slow.log
-s c：按出现次数排序-s l：按锁定时间排序-s r：按返回行数排序-s at：按平均查询时间排序
分析结果：
输出结果：
Count: 25 Time=3.24s (81s) Lock=0.00s (0s) Rows=10.0 (250), root[root]@localhost
SELECT * FROM orders WHERE user_id=N AND status='S'
Count: 120 Time=1.52s (182s) Lock=0.01s (1s) Rows=5.0 (600), app[app]@[10.0.0.1]
SELECT * FROM products WHERE category='S' AND price>N
字段解析：
| 字段 | 说明 |
|---|---|
Count |
该模式SQL出现的总次数 |
Time=3.24s (81s) |
平均执行时间3.24秒，总执行时间81秒 |
Lock=0.00s (0s) |
平均锁定时间0秒，总锁定时间0秒 |
Rows=10.0 (250) |
平均返回10行，总共返回250行 |
user@host |
执行该SQL的用户和来源IP |
SQL语句 |
抽象化的SQL模式（N代表数字，S代表字符串） |
如何分析慢查询
详细内容参考：凌晨2点，报警群炸了：一条sql 执行200秒！搞定之后，我总结了一个慢SQL查询、定位分析解决的完整套路
慢SQL分析三歩曲：
(1) 通过 EXPLAIN 快速定位执行计划问题
(2) 用 OPTIMIZER_TRACE 透视优化器为何选择该计划
(3) 通过 PROFILE 精确定位耗时瓶颈阶段
EXPLAIN SELECT...; -- 发现全表扫描
SET optimizer_trace=on; SELECT...; -- 发现因成本估算放弃索引
SHOW PROFILE; -- 确认"Sending data"阶段耗时90%
然后再进行优化，改写SQL，联合索引等。
以下以深度分页案例 SELECT * FROM store.my_order ORDER BY id LIMIT 7000000,1; 为例，完整演示慢查询分析三歩曲：
第一步：EXPLAIN 分析执行计划
mysql> select * from store.my_order order by id limit 100,1;
+-----+-------------+------------+----------+-------------+--------------+---------------------+
| id | customer_id | product_id | quantity | total_price | order_status | created_at |
+-----+-------------+------------+----------+-------------+--------------+---------------------+
| 101 | 630708 | 101 | 1 | 22 | 3 | 2023-08-25 17:24:50 |
+-----+-------------+------------+----------+-------------+--------------+---------------------+
1 row in set (0.00 sec)
mysql> select * from store.my_order order by id limit 7000000,1;
+---------+-------------+------------+----------+-------------+--------------+---------------------+
| id | customer_id | product_id | quantity | total_price | order_status | created_at |
+---------+-------------+------------+----------+-------------+--------------+---------------------+
| 7000001 | 765891 | 1 | 9 | 666 | 0 | 2023-08-25 17:25:43 |
+---------+-------------+------------+----------+-------------+--------------+---------------------+
1 row in set (1.88 sec)
mysql> explain select * from my_order order by id limit 100,1;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| 1 | SIMPLE | my_order | NULL | index | NULL | PRIMARY | 4 | NULL | 101 | 100.00 | NULL |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from my_order order by id limit 7000000,1;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
| 1 | SIMPLE | my_order | NULL | index | NULL | PRIMARY | 4 | NULL | 7000001 | 100.00 | NULL |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)
关键发现：
(1) type=index：虽然使用了主键索引，但需要扫描全部索引树
(2) rows=7000001：优化器预估需扫描700万行（与实际一致）
(3) 局限性：无法解释为何比 LIMIT 100,1 慢200倍
第二步：OPTIMIZER_TRACE 分析优化器决策
SET optimizer_trace="enabled=on";
SELECT * FROM store.my_order ORDER BY id LIMIT 7000000,1;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
关键输出片段：
{
"range_analysis": {
"table_scan": {
"rows": 10000000, -- 表总行数
"cost": 2.2e6 -- 全表扫描成本
},
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "`my_order`",
"best_access_path": {
"access_type": "index_scan", -- 选择索引扫描
"index": "PRIMARY",
"rows": 7000001, -- 需扫描700万行
"cost": 1.8e6 -- 成本略低于全表扫描
}
}
]
}
}
核心结论：
- 优化器选择索引扫描而非全表扫描（成本1.8e6 < 2.2e6）
- 但无法避免扫描700万行数据（
LIMIT机制导致）
第三步：PROFILE 分析执行耗时分布
SET profiling = 1;
SELECT * FROM store.my_order ORDER BY id LIMIT 7000000,1;
SHOW PROFILE FOR QUERY 1;
关键输出：
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000083 |
| checking permissions | 0.000005 |
| Opening tables | 0.000025 |
| init | 0.000033 |
| System lock | 0.000011 |
| optimizing | 0.000004 |
| statistics | 0.000008 |
| preparing | 0.000010 |
| executing | 0.000001 |
| Sorting result | 0.000019 |
| Sending data | 2.841762 | -- 99.9%耗时在此
| end | 0.000006 |
| query end | 0.000004 |
| closing tables | 0.000003 |
| freeing items | 0.000049 |
| cleaning up | 0.000012 |
+----------------------+----------+
问题定位：
Sending data阶段耗时2.84秒（占总耗时99.9%）- 根本原因：MySQL需要从索引树定位并读取700万行数据，虽然最终只返回1行
三歩曲联合诊断结论
| 分析工具 | 关键证据 | 问题本质 |
|---|---|---|
| EXPLAIN | type=index, rows=7000001 |
需扫描全部索引树 |
| OPTIMIZER_TRACE | "access_type": "index_scan" |
优化器选择最低成本方案 |
| PROFILE | Sending data 耗时占比99.9% |
无效数据传输是性能瓶颈 |
连环炮4：谈谈你对sql的优化经验
由于平台的字数限制，这里请参见原文
连环炮5：三大日志 undo log 、redo log、bin log
由于平台的字数限制，这里请参见原文