MYSQL - 索引优化

一、索引相关

索引的分类

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

B树和B+树的区别

1. 数据存储位置：B + 树仅叶子节点存数据，非叶子节点仅存索引键值；B 树所有节点均存储数据。
2. 树结构与查询效率：B + 树因非叶子节点仅存索引，更矮更胖，磁盘 IO 次数少，查询效率更高；B 树节点存储数据，索引存储量少，查询效率较低。
3. 查找功能适配：B + 树叶子节点集中存储数据且用双向链表连接，适配范围查找、排序、分组、去重，操作更简便；B 树无此结构优势，此类查找操作更复杂。

二级索引（非聚集索引）

二级索引（又称“辅助索引”“非聚集索引”）是 MySQL（核心针对 InnoDB 存储引擎）中除聚集索引（主键索引）外的所有索引类型，本质是“辅助查询的索引结构”——自身不存储完整数据，需依赖聚集索引才能获取全量行数据，是优化非主键查询的核心工具。

核心定义
二级索引是与数据物理存储分离的索引，其 B+ 树的 叶子节点仅存储“索引键值 + 对应的主键值”（而非完整行数据），作用是快速定位目标数据的主键，再通过主键回表查询聚集索引获取完整数据（覆盖索引场景除外）。

和聚集索引一样，二级索引的底层也是 B+ 树，但结构有明确区别：

• 非叶子节点：存储「二级索引的键值 + 子节点指针」（比如索引键是 name="张三"，指针指向子节点的磁盘地址），仅用于索引定位，不存任何数据；
• 叶子节点：存储「二级索引的键值 + 对应的主键值」（比如 name="张三" + id=10），不存储完整行数据（如 age、address 等字段）；
• 叶子节点关联：所有叶子节点通过 双向链表 连接，方便范围查询（比如 name like "张%"、age between 20 and 30）。

查询流程（含“回表”与“覆盖索引”）

二级索引的查询核心是“先找主键，再查数据”，流程分两种情况：

1. 普通查询（需“回表”）

、

第一步：二级索引（age 索引）查询
图右侧是age的二级索引（B+树结构）：

• 非叶子节点：存储age的键值（比如最上层的21）和子节点指针；
• 叶子节点：存储age键值 + 对应的主键ID（比如叶子节点中age=35对应的ID=9）。

执行age=35的查询时：

1. 从age二级索引的根节点（最上层21）开始，通过B+树的二分查找，定位到age=35所在的叶子节点；
2. 从该叶子节点中取出对应的主键ID=9。

第二步：回表（查聚集索引）
图左侧是聚集索引（主键ID索引）（B+树结构）：

• 聚集索引的叶子节点存储完整的行数据（比如ID=9对应的Toy、35等信息）。

拿到ID=9后，需要“回表”查聚集索引：

1. 从聚集索引的根节点（最上层6）开始，定位到ID=9所在的叶子节点；
2. 从该叶子节点取出完整的行数据（ID=9、Toy、35等），最终返回结果。

核心逻辑总结
这个流程体现了InnoDB二级索引的本质：

• 二级索引只存“age+ID”，不存完整数据；
• 必须通过二级索引拿到ID，再去聚集索引（主键索引）中取完整数据（即“回表”）；
• 整个过程是“二级索引查主键 → 聚集索引查数据”，完成SELECT *的查询。

2. 覆盖索引查询（无需回表）

如果查询的字段刚好是“二级索引键值 + 主键”（或仅二级索引键值），无需回表，直接从二级索引返回数据：
示例：执行 select id, name from user where name="张三";

• 二级索引的叶子节点已存储 name（索引键）和 id（主键），无需去聚集索引查询，直接取出返回；
• 性能：接近聚集索引，是优化二级索引查询的核心技巧（避免回表开销）。

二级索引的常见类型

所有非聚集索引都属于二级索引，常见类型包括：

1. 单字段索引：基于单个字段创建（如 name、age、phone 索引），适用于单个字段的查询条件（where name="xxx"）；
2. 联合索引（复合索引）：基于多个字段创建（如 name+age、address+phone 索引），适用于多字段组合查询（where name="张三" and age=25）；
   • 注意：联合索引遵循“最左前缀原则”（比如 name+age 索引，能匹配 where name="xxx"，但不能匹配 where age=25）；
3. 唯一二级索引：基于唯一非空字段创建（如 phone 唯一索引），叶子节点存储「唯一键值 + 主键值」，既能保证字段唯一性，又能辅助查询（避免全表扫描）；
   • 区别于聚集索引：唯一二级索引的叶子节点不存完整数据，仍需回表（覆盖索引除外）。

关键特性与注意事项

1. 数量限制：一张表可以创建多个二级索引（无硬性数量限制，但不宜过多），而聚集索引仅能有 1 个；
2. 依赖聚集索引：二级索引的叶子节点必须存储主键值（而非数据物理地址），因为 InnoDB 中数据的物理存储与聚集索引绑定，主键是数据的唯一标识；
3. 索引维护成本：创建二级索引会增加写操作（插入、更新、删除）的开销——修改数据时，不仅要更新聚集索引，还要同步更新所有相关的二级索引；
4. 适用场景：适用于非主键查询、范围查询、联合查询（如 name+age），核心是减少全表扫描的开销；
5. 与 MyISAM 的区别：MyISAM 中没有聚集索引，所有索引（包括主键索引）都是“非聚集索引”，其叶子节点存储的是“数据的物理地址”（而非主键值），无需回表，但不支持事务和行锁。

联合索引（复合索引）

联合索引是基于多个字段组合创建的索引（比如 name+age+id），本质是将多个字段的键值按“最左前缀”规则组合成一个索引键，用B+树结构存储。核心特点是遵循“最左前缀原则”，查询时只有匹配索引的“最左字段”才能触发索引查询。

一、联合索引的核心特性

1. 最左前缀原则
   联合索引 a+b+c 能匹配的查询条件是：

   • ✅ a=?（仅最左字段）
   • ✅ a=? AND b=?（最左+中间字段）
   • ✅ a=? AND b=? AND c=?（全字段）
   • ❌ b=?（跳过最左字段，索引失效）
   • ❌ b=? AND c=?（跳过最左字段，索引失效）

2. B+树存储结构
   联合索引的B+树中，非叶子节点存储“组合键值+子节点指针”，叶子节点存储“完整组合键值+主键ID”（二级索引特性），叶子节点之间用双向链表连接（方便范围查询）。

3. 二级索引属性
   联合索引属于二级索引，叶子节点只存“组合键值+主键ID”，查询时需通过主键回表（聚集索引）获取完整数据。

二、例子（name+age+id联合索引）

你的图展示的是 name+age+id 联合索引的B+树结构，我们分“结构”和“查询流程”拆解：

1. 联合索引的B+树结构

• 非叶子节点（索引页113）：
  存储“name+age的组合键值 + 子节点指针”，比如：

  • name=张三、age=18 → 指针指向“索引页110”
  • name=王五、age=19 → 指针指向“索引页111”
  • name=刘七、age=20 → 指针指向“索引页112”
    （注：非叶子节点只存“用于定位的键值”，这里name+age是定位依据）

• 叶子节点（索引页110/111/112）：
  存储“完整的name+age组合键值 + 主键id”，且叶子节点之间用双向链表连接：

  • 索引页110：(张三,18,2)、(张三,23,3)、(李四,20,1)
  • 索引页111：(王五,19,5)、(赵六,22,4)、(赵六,24,6)
  • 索引页112：(刘七,20,7)、(刘七,22,8)、(王九,9,9)

2. 实际查询案例（匹配最左前缀）

以查询 WHERE name='王五' AND age=19 为例，流程是：

1. 定位非叶子节点：
   从根节点（索引页113）开始，找到name=王五、age=19对应的指针，定位到叶子节点“索引页111”。
2. 取主键ID：
   在索引页111中找到(王五,19)对应的主键id=5。
3. 回表查数据：
   拿着id=5去聚集索引（主键索引）中查询，获取id=5对应的完整行数据（比如name=王五、age=19、address=xxx等）。

3. 最左前缀原则的体现

如果查询条件是 WHERE age=19（跳过最左字段name）：

• 索引页113中没有单独的age键值，无法通过非叶子节点定位，只能全表扫描，索引失效。

二、索引优化

数据准备

CREATE TABLE `employees` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',

`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',

`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',

`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',

`remark` varchar(255) DEFAULT NULL COMMENT '备注',

PRIMARY KEY (`id`),

KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='员工记录表';


-- 批量插入10条员工数据（id从10开始自增）

INSERT INTO `employees` (name, age, position, hire_time, remark)

VALUES

('张三', 18, 'beijing', '2024-03-15 09:20:30', '负责北京区域业务，5年工作经验'),

('李四', 32, 'chongqing', '2024-01-08 10:15:22', '重庆分部负责人，熟悉西南市场'),

('王五', 25, 'beijing', '2024-05-20 14:30:10', '北京技术组，应届生，计算机专业'),

('赵六', 30, 'chongqing', '2024-02-28 11:05:45', '重庆运营岗，3年电商运营经验'),

('孙七', 35, 'beijing', '2024-04-10 08:40:00', '北京运维岗，熟悉服务器管理'),

('周八', 27, 'chongqing', '2024-06-12 15:10:28', '重庆客服岗，普通话/重庆话流利'),

('吴九', 18, 'beijing', '2024-07-05 13:25:15', '北京数据分析岗，熟练SQL/Python'),

('郑十', 31, 'chongqing', '2024-08-18 09:50:33', '重庆招聘岗，负责西南区域招聘'),

('冯十一', 26, 'beijing', '2024-09-01 10:00:00', '北京产品岗，参与过3个核心项目'),

('陈十二', 33, 'chongqing', '2024-10-22 16:05:12', '重庆财务岗，中级会计师职称');

1. 最左前缀原则

1. 全部匹配

explain select * from employees where name='张三' and age=18 and `position` ='beijing'

字段名	值	解读（补充 utf8mb4 适配）
id	1	仅 1 个简单 SELECT，无复杂子查询 / 联表，id=1（和字符集无关）
select_type	SIMPLE	简单查询（无 UNION / 子查询等，和字符集无关）
table	e	对应 employees 表（别名 e，和字符集无关）
partitions	空	表未分区（和字符集无关）
type	ref	非唯一索引等值匹配（联合索引不是唯一索引，所以用 ref 而非 const，和字符集无关），效率极高
possible_keys	idx_name_age_position	优化器认为仅这个联合索引符合查询条件（name/age/position 等值匹配）
key	idx_name_age_position	最终选择的索引（最优选择，和字符集无关）
key_len	140	索引使用的长度（字节），核心反映「索引用到了多少列」： 你的联合索引是 name(varchar24)+age(int)+position(varchar20)，字符集 utf8（3 字节 / 字符）： - name：24×3 + 2（字符串结束符）=74 字节 - age：int 占 4 字节 - position：20×3 +2=62 字节 总计： 140，如果差异是 MySQL 对字符集 / NULL 的小兼容，核心是「3 个字段都用到了」）。
ref	const,const,const	3 个字段都用「常量等值匹配」（比如 name='张三' AND age=28 AND position='beijing'），和字符集无关
rows	1	预估扫描 1 行（索引精准定位，和字符集无关）
filtered	100.0	扫描的行 100% 符合条件（无额外过滤，和字符集无关）
Extra	空	无排序 / 回表 / 临时表等性能损耗，查询最优（和字符集无关）

name varchar(24) NOT NULL
key_len = 24 * 3 +2 = 74

场景	额外字节数	适用版本 / 说明
VARCHAR（变长字符串）+ 非 NULL	+2	MySQL 5.1+ 主流版本（5.6/5.7/8.0）：VARCHAR 用 2 字节 存储「字符串实际长度」（长度标识），和字符集无关
VARCHAR + NULL	+2 +1=3	2 字节长度标识 + 1 字节 NULL 标记（NULL 字段需要额外 1 字节标记是否为 NULL）
CHAR（定长字符串）+ 非 NULL	+0	CHAR 是固定长度，无需存储「实际长度」，仅计算字符本身的字节数
CHAR + NULL	+1	仅加 1 字节 NULL 标记（无长度标识）
旧版本 MySQL（<5.1）	+1	早期版本 VARCHAR 用 1 字节存储长度（仅支持 0-255 字符），现在几乎不用

2. 带头大哥步能死
　

-- 带头大哥步能死
explain select * from employees where age=18 and `position` ='beijing'

possible_keys 为空 + type=ALL，说明你的查询条件不满足索引使用规则

3. 中间兄弟不能断

-- 中间兄弟不能断
explain select * from employees where name='张三' and `position` ='beijing'

key_len	74	索引使用字节数（核心！）： 对应 name varchar(24) NOT NULL + utf8 字符集（3 字节 / 字符）： 计算逻辑 = 24×3（字符） + 2（字符串长度标识） = 74 字节（和显示值完全一致）。 验证：若字符集是 utf8mb4，key_len 应是 24×4+2=98，因此可确定表字符集是 utf8。
ref	const	索引匹配的参考值：const 表示 name 的匹配值是常量（如 name='张三'），等值匹配无歧义。
rows	1	预估扫描行数：MySQL 认为通过索引仅需扫描 1 行就能找到目标数据（精准定位）。
filtered	10	过滤后有效行占比：仅 10% 的扫描行符合最终条件（说明查询除了 name='张三'，可能还有其他非索引条件过滤，如 hire_time > '2024-01-01'）。
Extra	Using index condition	额外逻辑：索引条件下推（ICP），MySQL 优化器的性能优化手段： 将「非索引字段的过滤条件」下推到存储引擎层执行，减少服务器层需要处理的数据量（比如先通过索引找到 name='张三' 的行，再在存储引擎层过滤 hire_time 条件，而非返回服务器层后再过滤）。

2. 索引列上少计算

破坏有序性。

例子1

虽然看起 left(name, 2) = '张三' 和 like '张三%' 差不多，但是会每个name都去计算，会扫表

LIKE '张三%'（前缀匹配，% 在末尾）触发等值匹配类的索引访问（ref/range 中的等值逻辑）

匹配方式	是否使用索引	索引访问类型（type）	是否属于「范围索引」
LIKE '张三%'	✅ 是	ref（优先）/range	❌ 否（是「前缀等值匹配」）
LIKE '%张三'	❌ 否	ALL（全表扫描）	-
LIKE '%张三%'	❌ 否	ALL（全表扫描）	-

例子2

-- 新增一个索引
ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) ;

explain select * from employees where date(hire_time) ='2024-08-18';

EXPLAIN select * from employees where hire_time >='2024-08-18 00:00:00' and hire_time <= '2024-08-18 23:59:59';

3. 范围后面全失效

例子1

EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age > 18 AND position = 'beijing';

这里的 Key_len = 74 + 4 = 78 ，说明事由 name 和 age 使用了索引。

例子2

这里开始我插入了10W条SQL。

explain SELECT * FROM employees WHERE name > '张三' AND age = 18 AND position = 'beijing';

type	ALL	全表扫描（效率最低的扫描类型），MySQL 放弃使用索引
possible_keys	idx_name_age_position	优化器评估后认为「理论上可用」的索引（仅存在可能性，不一定实际使用）
key	（空）	实际未使用任何索引（与type=ALL对应）
key_len	（空）	未用索引，无索引长度
ref	（空）	无等值匹配的字段关联（无=条件能关联到常量 / 其他表）
rows	99,612	优化器预估需要扫描约 9.96 万行（接近 10 万条总数据，印证全表扫描）
filtered	0.5	优化器预估扫描后仅能过滤出 0.5% 的数据（即约 500 行符合WHERE条件）
Extra	Using where	全表扫描后，在服务器层通过WHERE条件过滤数据（而非索引层过滤）

explain SELECT * FROM employees force index(idx_name_age_position) WHERE name > '张三' AND age = 18 AND position = 'beijing';

字段	取值	核心解读（对比全表扫描版）
type	range	从ALL（全表扫描）变为range（范围索引扫描），强制索引后仅用name字段做范围扫描
key	idx_name_age_position	从空变为指定索引，证明FORCE INDEX生效，MySQL 必须使用该联合索引
key_len	74	仅用到索引的第一个字段name（utf8 下varchar(24)：24*3+2=74），age/position仍未用到
rows	49,806	优化器预估扫描name > '张三'的索引行约 4.98 万行（是全表行数的一半，符合name范围过滤逻辑）
filtered	1	优化器预估索引扫描后，仅 1% 的数据能通过age=18 AND position='beijing'过滤（比全表版的 0.5% 略高，无本质差异）
Extra	Using index condition	从Using where变为ICP（索引条件下推）：存储引擎层先过滤name > '张三'，再回表，减少回表行数

4. like 百分写最右

其实还是不建议使用。图中使用的 index 就比全表扫描好一点点，确实没得法的时候解决方案吧。

5. 范围查询优化

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;

explain select * from employees where age >=1 and age <=2000;

没走索引原因：mysql 内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引

优化方法：可以将大的范围拆分成多个小范围

explain select * from employees where age >=1 and age <=1000;

explain select * from employees where age >=1000 and age <=2000;

总结

还原最初索引状态

ALTER TABLE `employees` DROP INDEX `idx_age`;

索引使用总结：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

三、Trace 监控

set session optimizer_trace='enabled=on',end_markers_in_json=on;
-- 两行一起查询
SELECT * FROM employees  WHERE name= '张三' AND age = 18;
select * from information_schema.OPTIMIZER_TRACE;

{
  "steps": [
    {
      "join_preparation": {     -- 第一步：连接准备 (join_preparation)
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time`,`employees`.`remark` AS `remark` from `employees` where ((`employees`.`name` = '张三') and (`employees`.`age` = 18))"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {   -- 第二步：连接优化 (join_optimization)
        "select#": 1,  
        "steps": [
          {
            "condition_processing": {                                                                      -- 条件处理 (condition_processing)
              "condition": "WHERE",  
              "original_condition": "((`employees`.`name` = '张三') and (`employees`.`age` = 18))",
              "steps": [
                {
                  "transformation": "equality_propagation",                                               -- 等值传播 (equality_propagation)
                  "resulting_condition": "((`employees`.`name` = '张三') and multiple equal(18, `employees`.`age`))"
                },
                {
                  "transformation": "constant_propagation",                                               -- 常量传播 (constant_propagation)
                  "resulting_condition": "((`employees`.`name` = '张三') and multiple equal(18, `employees`.`age`))"
                },
                {
                  "transformation": "trivial_condition_removal",                                          -- 平凡条件删除 (trivial_condition_removal)
                  "resulting_condition": "((`employees`.`name` = '张三') and multiple equal(18, `employees`.`age`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [                                      -- 表依赖 (table_dependencies)
              {
                "table": "`employees`",
                "row_may_be_null": false,                                -- 行可能为空 (row_may_be_null)
                "map_bit": 0,                                            -- 映射位 (map_bit)
                "depends_on_map_bits": [                                 -- 依赖的映射位 (depends_on_map_bits) 
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [                                  -- 引用优化器键使用 (ref_optimizer_key_uses)
              {
                "table": "`employees`",
                "field": "name",
                "equals": "'张三'",
                "null_rejecting": true
              },
              {
                "table": "`employees`",
                "field": "age",
                "equals": "18",
                "null_rejecting": true
              },
              {
                "table": "`employees`",
                "field": "age",
                "equals": "18",
                "null_rejecting": true
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [                                        -- 行估计 (rows_estimation)
              {
                "table": "`employees`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 99612,                                        -- 扫描行 (rows)
                    "cost": 10132                                         -- 成本 (cost)
                  } /* table_scan */,
                  "potential_range_indexes": [                            -- 潜在范围索引 (potential_range_indexes)
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"                           -- not_applicable 是无法使用索引的原因 (cause)
                    },
                    {
                      "index": "idx_name_age_position",                   
                      "usable": true,                                     
                      "key_parts": [
                        "name",
                        "age",
                        "position",
                        "id"
                      ] /* key_parts */
                    },
                    {
                      "index": "idx_hire_time",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_age",
                      "usable": true,
                      "key_parts": [
                        "age",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [                           -- 设置范围条件 (setup_range_conditions)
                  ] /* setup_range_conditions */,
                  "group_index_range": {                                -- 分组索引范围 (group_index_range)
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "skip_scan_range": {                                  -- 跳过扫描范围 (skip_scan_range)
                    "potential_skip_scan_indexes": [                       -- 潜在跳过扫描索引 (potential_skip_scan_indexes)
                      {
                        "index": "idx_name_age_position",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      },
                      {
                        "index": "idx_age",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ] /* potential_skip_scan_indexes */
                  } /* skip_scan_range */,
                  "analyzing_range_alternatives": {                     -- 分析范围替代 (analyzing_range_alternatives)
                    "range_scan_alternatives": [                          -- 范围扫描替代 (range_scan_alternatives)
                      {
                        "index": "idx_name_age_position",
                        "ranges": [
                          "张三 <= name <= 张三 AND 18 <= age <= 18"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,                -- 索引 dive 用于等范围 (index_dives_for_eq_ranges)
                        "rowid_ordered": false,                           -- 行 ID 有序 (rowid_ordered)       
                        "using_mrr": false,                               -- 使用 MRR (using_mrr)
                        "index_only": false,                              -- 仅索引 (index_only)
                        "rows": 1,                                        -- 扫描行 (rows)
                        "cost": 0.61,                                     -- 成本 (cost)
                        "chosen": true                                    -- 已选择 (chosen)
                      },
                      {
                        "index": "idx_age",
                        "ranges": [
                          "18 <= age <= 18"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 2350,
                        "cost": 822.76,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {    -- 分析行顺序交集 (analyzing_roworder_intersect)
                      "usable": false,                      -- 可用于行顺序交集 (usable)
                      "cause": "too_few_roworder_scans"     -- 行顺序扫描数量不足 (cause)
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {         -- 选择范围访问摘要 (chosen_range_access_summary)
                    "range_access_plan": {                    -- 最佳访问计划 (range_access_plan) 
                      "type": "range_scan",                     -- 范围扫描 (type)
                      "index": "idx_name_age_position",         -- 索引 (index)
                      "rows": 1,                                -- 扫描行 (rows)  
                      "ranges": [
                        "张三 <= name <= 张三 AND 18 <= age <= 18"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 0.61,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [         -- 考虑执行计划 (considered_execution_plans)
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`employees`",
                "best_access_path": {               -- 最佳访问路径 (best_access_path)
                  "considered_access_paths": [          -- 考虑访问路径 (considered_access_paths)
                    {
                      "access_type": "ref",             -- 访问类型 (access_type)
                      "index": "idx_name_age_position", -- 索引 (index)
                      "rows": 1,                        -- 扫描行 (rows)
                      "cost": 0.35,                     -- 成本 (cost)
                      "chosen": true                    -- 已选择 (chosen)
                    },
                    {
                      "access_type": "ref",
                      "index": "idx_age",
                      "rows": 2350,
                      "cost": 739.75,
                      "chosen": false
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_name_age_position"
                      } /* range_details */,
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,     -- 条件过滤百分比 (condition_filtering_pct)
                "rows_for_plan": 1,                   -- 扫描行 (rows_for_plan) 
                "cost_for_plan": 0.35,                -- 成本 (cost_for_plan)
                "chosen": true                        
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {       -- 将条件附加到表 (attaching_conditions_to_tables)
              "original_condition": "((`employees`.`age` = 18) and (`employees`.`name` = '张三'))",
              "attached_conditions_computation": [       -- 附加条件计算 (attached_conditions_computation)
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [           -- 附加条件摘要 (attached_conditions_summary)
                {
                  "table": "`employees`",
                  "attached": "((`employees`.`age` = 18) and (`employees`.`name` = '张三'))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [          -- 最终确定表条件 (finalizing_table_conditions)
              {
                "table": "`employees`",
                "original_table_condition": "((`employees`.`age` = 18) and (`employees`.`name` = '张三'))",
                "final_table_condition   ": null
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [                          -- 精炼计划 (refine_plan)
              {
                "table": "`employees`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {      -- 第三步：执行计划 (join_execution)
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

四、SQL 优化

小表驱动大表

在 student（30条，小表）和 Score（80万条，大表）关联查询时，小表驱动大表 是数据库优化的核心原则，本质是最小化嵌套循环的总执行次数、降低IO/CPU开销，以下从原理、执行逻辑、性能对比三方面讲透核心原因：

小表（student 30条）驱动大表（Score 80万条）的核心原因总结

小表驱动大表是MySQL嵌套循环Join的核心优化原则，其优势由「嵌套循环执行逻辑」和「Join Buffer（连接缓冲区）」两大核心因素共同决定，最终实现总执行开销、IO成本的最小化：

一、核心原因1：嵌套循环逻辑——最小化外层循环次数

MySQL处理两表Join的底层是「嵌套循环连接」，总执行开销 = 外层循环次数 × 内层单次匹配开销：

1. 小表驱动大表（最优）：
   外层循环仅执行30次（遍历student小表），内层每次循环用关联字段（如student_id）匹配Score大表（有索引时为O(logN)索引查找，无索引时为全表扫描）。
   • 有索引总开销：30 × log₂(80万) ≈ 570次核心操作；
   • 无索引总开销：30 × 80万 = 2400万次操作（已是无索引场景下的最优解）。
2. 大表驱动小表（反例）：
   外层循环需执行80万次（遍历Score大表），内层每次匹配student小表。
   • 有索引总开销：80万 × log₂(30) ≈ 400万次核心操作（是小表驱动的7000倍）；
   • 无索引总开销：80万 × 30 = 2.4亿次操作（性能崩盘）。
     外层循环的「上下文切换、条件计算」等固定开销，会随循环次数呈指数级放大，小表驱动可将这部分开销降到最低。

二、核心原因2：Join Buffer（连接缓冲区）——最大化内存缓存效率

Join Buffer是MySQL为无索引Join分配的内存缓冲区，作用是缓存驱动表记录、减少被驱动表扫描次数（默认大小有限，如256KB）：

1. 小表驱动大表（Buffer利用率100%）：
   30条student记录的内存占用极小（仅几KB），可完整放入Join Buffer；MySQL只需扫描1次Score大表，就能一次性匹配Buffer中所有小表记录，被驱动表仅需1次扫描。
2. 大表驱动小表（Buffer分批缓存）：
   80万条Score记录远超出Buffer容量，需分批次缓存（如每批2560条），每批都要扫描1次student小表，最终小表被重复扫描约312次，触发大量冗余IO。

补充关键结论

1. 优化器的自动选择：MySQL优化器会通过表行数统计（EXPLAIN的rows字段）自动选择小表作为驱动表，但需确保表统计信息准确（执行ANALYZE TABLE更新）、大表关联字段有索引（核心优化点）。
2. 适用场景延伸：
   • 大表关联字段有索引时：小表驱动的优势体现在“减少索引查找次数”；
   • 大表关联字段无索引时：Join Buffer成为性能关键，小表驱动可避免被驱动表重复扫描。

综上，小表驱动大表既通过减少外层循环次数降低了基础执行开销，又通过最大化Join Buffer利用率减少了被驱动表的扫描次数，是Join查询中兼顾“有索引/无索引”场景的最优策略。

limit 分页

百万级表 Limit 翻页越往后越慢咋办

为什么 offset 偏大之后 limit 查找会变慢？这需要了解 limit 操作是如何运作的，以下面这句查询为例：

SELECT * FROM employees limit 990000,10

这句 SQL 的执行逻辑是

1. 从数据表中读取第 N 条数据添加到数据集中
2. 重复第一步直到 N = 990000 + 10
3. 根据 offset 抛弃前面 990000 条数
4. 返回剩余的 10 条数据

优化一：利用自增索引

SELECT * FROM employees where (id >= 990000) limit 10

以下是图中的完整文本内容：

第二次优化: in

说起数据库查询优化，第一时间想到的就是索引，所以便有了第二次优化：先查找出需要数据的索引列（假设为 id），再通过索引列查找出需要的数据。

Select * From table_name Where id in (Select id From table_name where ( user = xxx ) limit 10000,10)
select * from table_name where( user = xxx ) limit 10000,10

相比较结果是（500w条数据）：第一条花费平均耗时约为第二条的 1/3 左右。
同样是较大的 offset，第一条的查询更为复杂，为什么性能反而得到了提升？

• 子查询只用到了索引列，没有取实际的数据，所以不涉及到磁盘 IO，所以即使是比较大的 offset 查询速度也不会太差。
• 利用子查询的方式，把原来的基于 user 的搜索转化为基于主键（id）的搜索，主查询因为已经获得了准确的索引值，所以查询过程也相对较快。

搞了一个100W的例子

select * from employees where ( position = 'beijing' ) order by id limit 200000,10

WITH temp_emp AS (
SELECT id
FROM employees
WHERE position = 'beijing'
order by id
LIMIT 200000, 10
)
select * from employees where id in (SELECT id FROM temp_emp)

回表率只有 10

第三次优化：inner join

WITH temp_emp AS (
SELECT id
FROM employees
WHERE position = 'beijing'
order by id
LIMIT 200000, 10
)
select * from employees inner join temp_emp on temp_emp.id = employees.id

这两种优化写法最终执行逻辑、性能几乎无差异（MySQL 8.0 优化器会将 IN 子查询 等价优化为 INNER JOIN）

1. 优先选 JOIN 写法：
   • 执行计划更直观，便于排查问题；
   • 无 “IN + 子查询” 的潜在语法限制（即使后续调整 CTE 为普通子查询，也不会触发 1235 错误）；
   • 团队协作时，JOIN 的 “关联” 语义更易被理解。
2. IN 写法可用但不推荐：
   • 仅适合个人临时查询，语义虽直观，但执行计划多一层 “物化表” 展示，排查时稍复杂；
   • 若需兼容 MySQL 5.6-，需将 CTE 改为普通子查询（避免语法不支持）。

使用 union all 不使用 union

union all: 合并
union: 合并去重

union all与union如果当然它业务数据容许出现重复的记录，我们更推荐使用union all，因为union去重数据需要遍历、排序和比较，它更耗时，更消耗cpu资源，但是数据结果最完整。