一、数据库执行过程介绍1.1 SQL 执行全流程MySQL 执行一条 SQL 查询的完整流程如下text客户端发送 SQL ↓ 连接管理器 ↓ 查询缓存MySQL 8.0 已移除 ↓ ┌───────────────────────────────────────┐ │ SQL 解析器 │ │ • 词法分析 → 生成 Token 序列 │ │ • 语法分析 → 生成 AST抽象语法树 │ └───────────────────────────────────────┘ ↓ ┌───────────────────────────────────────┐ │ 查询优化器 │ │ • 预处理语义检查、权限检查 │ │ • 逻辑优化视图合并、谓词下推等 │ │ • 物理优化选择最优执行计划 │ │ • 生成执行计划 │ └───────────────────────────────────────┘ ↓ ┌───────────────────────────────────────┐ │ 执行引擎 │ │ • 调用存储引擎接口 │ │ • 读取数据 │ │ • 返回结果集 │ └───────────────────────────────────────┘ ↓ 返回结果给客户端1.2 查询优化器的核心工作查询优化器是数据库的大脑其核心任务是从多个可能的执行计划中选择代价最小的一个。优化器考虑的代价因素代价维度说明I/O 代价磁盘读取次数最主要CPU 代价数据处理、排序、聚合等计算内存代价临时表、排序缓冲区的使用网络代价数据传输量优化器如何估算代价使用表统计信息行数、索引基数、数据分布等采用基于代价的优化CBO策略当统计信息过时时可能选择次优计划导致性能问题可通过ANALYZE TABLE更新统计信息优化器的关键决策表连接顺序决定多表查询的驱动表顺序索引选择选择哪个索引或全表扫描连接算法选择 Nested Loop、Hash Join 还是 Sort-Merge子查询优化决定是否将子查询转换为 JOIN 或 EXISTS1.3 存储引擎的作用执行引擎通过存储引擎接口Handler API与存储引擎通信每种存储引擎的实现不同存储引擎特点适用场景InnoDB支持事务、行级锁、MVCC大多数 OLTP 场景默认MyISAM不支持事务、表级锁读多写少的场景Memory数据在内存中临时表、缓存表二、EXPLAIN 详解2.1 基本用法sql-- 查看执行计划 EXPLAIN SELECT * FROM users WHERE age 18; -- 显示更多信息MySQL 8.0 EXPLAIN FORMATTREE SELECT * FROM users WHERE age 18; -- 显示 JSON 格式的详细执行计划 EXPLAIN FORMATJSON SELECT * FROM users WHERE age 18; -- 实际执行并显示执行计划包含实际行数 EXPLAIN ANALYZE SELECT * FROM users WHERE age 18; -- MySQL 8.0.182.2 EXPLAIN 结果字段详解字段说明关键点id查询标识符id 相同表示同一组id 越大越先执行select_type查询类型SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION 等table表名或别名type访问类型性能从好到差systemconsteq_refrefrangeindexALLpossible_keys可能使用的索引key实际使用的索引如果为 NULL说明没有使用索引key_len索引使用的字节数可判断复合索引的使用情况ref与索引比较的列rows预估需要扫描的行数越小越好优化器的估算值filtered过滤后的百分比越高越好Extra额外信息包含 Using index, Using where, Using filesort 等重要信息2.3 select_type 详解值说明示例SIMPLE简单查询不包含子查询或 UNIONSELECT * FROM usersPRIMARY复杂查询的最外层 SELECT带子查询的外层SUBQUERY子查询非 FROM 子句WHERE id IN (SELECT ...)DERIVEDFROM 子句中的子查询派生表FROM (SELECT ...) AS tUNIONUNION 中的第二个或之后的 SELECTSELECT ... UNION SELECT ...UNION RESULTUNION 的结果合并三、EXPLAIN 结果深度分析3.1 type 访问类型性能排序textsystem const eq_ref ref range index ALLtype说明性能典型场景system系统表只有一行数据最优SHOW TABLES等系统查询const使用主键或唯一索引查询且结果为一条记录极优WHERE id 1eq_ref使用主键或唯一索引进行 JOIN极优JOIN ON a.id b.idref使用非唯一索引进行等值查询良好WHERE name 张三range使用索引进行范围查询一般WHERE age 18或INindex全索引扫描差SELECT count(*)覆盖索引扫描ALL全表扫描最差无索引或索引失效3.2 Extra 关键信息解读Extra含义处理建议Using index覆盖索引无需回表✅ 最佳无需优化Using where使用 WHERE 条件过滤正常Using index condition索引条件下推ICP✅ 已优化Using filesort需要额外排序⚠️ 建议优化考虑建立联合索引Using temporary使用临时表⚠️ 建议优化常见于 GROUP BY、DISTINCTUsing join buffer使用 JOIN 缓冲区⚠️ 考虑索引优化Impossible WHEREWHERE 条件永远为假❌ 检查查询逻辑3.3 案例慢查询分析sql-- 慢查询示例 EXPLAIN SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.age 18 AND o.status PAID ORDER BY u.create_time DESC; -- 可能的执行计划问题 -- 1. type ALL全表扫描 users -- 2. Extra Using filesort排序 -- 3. 预估 rows 100000 -- 4. key NULL未使用索引四、索引优化注意事项4.1 索引设计原则原则说明选择性高的列优先为基数cardinality高的列建索引WHERE 条件列经常出现在 WHERE 子句的列JOIN 关联列ON 条件中的列必须建索引ORDER BY 列避免 filesort可考虑建立联合索引GROUP BY 列减少临时表的使用覆盖索引查询需要的字段都在索引中避免回表4.2 复合索引联合索引最佳实践最左前缀原则sql-- 创建联合索引 (a, b, c) CREATE INDEX idx_a_b_c ON table (a, b, c); -- 能够使用索引的场景 WHERE a 1 -- ✅ 使用索引 WHERE a 1 AND b 2 -- ✅ 使用索引 WHERE a 1 AND b 2 AND c 3 -- ✅ 使用索引全部字段 WHERE a 1 AND c 3 -- ⚠️ 只用到 a跳过 bc 用不上 WHERE b 2 -- ❌ 不使用索引不满足最左前缀 -- ✅ 索引键顺序建议 -- 等值查询列在前范围查询列在后 CREATE INDEX idx_age_name ON users (age, name); -- 场景age 18 AND name LIKE 张% → 优先 age 等值再 name 范围索引键排序建议等值条件或IN的列放在前面范围条件、、LIKE的列放在后面高选择性列优先4.3 索引的权衡考虑因素说明查询性能提升索引能大幅减少扫描行数写入性能下降索引需要维护INSERT/UPDATE/DELETE 变慢存储空间增加索引占用额外的磁盘空间维护成本索引过多会增加优化器选择难度可能导致执行计划不稳定经验法则单表索引数量建议不超过 5-6 个不适合建立索引性别、状态等低选择性字段五、索引失效问题5.1 常见索引失效场景场景示例原因LIKE 以 % 开头WHERE name LIKE %张三无法从 B 树根节点定位使用函数WHERE DATE(create_time) 2026-07-28对列进行函数操作类型隐式转换WHERE phone 13800138000phone 是 VARCHAR类型转换导致索引失效OR 条件WHERE age 18 OR name 张三部分条件无索引使用 ! 或 WHERE age ! 18无法利用索引IS NOT NULLWHERE age IS NOT NULL索引不存储 NULL 值记录少量优化NOT IN / NOT EXISTSWHERE id NOT IN (1,2,3)范围排除复合索引不满足最左前缀WHERE b 2 AND c 3索引 a,b,c跳过了索引的第一列范围查询后的列WHERE a 1 AND b 2索引 a,bb 无法使用5.2 场景举例LIKE查询sql-- ❌ 索引失效 SELECT * FROM users WHERE name LIKE %张%; -- ✅ 使用索引 SELECT * FROM users WHERE name LIKE 张%; -- ✅ 使用覆盖索引仅查询索引字段 SELECT name FROM users WHERE name LIKE %张%;5.3 场景举例类型隐式转换sql-- 表结构phone VARCHAR(11) CREATE INDEX idx_phone ON users (phone); -- ❌ 索引失效phone 是字符串比较时转为数字 SELECT * FROM users WHERE phone 13800138000; -- ✅ 使用索引 SELECT * FROM users WHERE phone 13800138000;六、索引为什么会失效底层原理6.1 B 树索引结构B 树是 InnoDB 存储引擎使用的索引数据结构其核心特性决定了索引的失效场景text[根节点] / \ [内部节点] [内部节点] / \ / \ [叶子] [叶子] [叶子] [叶子] (1,row) (2,row) (3,row) (4,row) ↑ 双向链表连接 ↑B 树关键特性所有数据存储在叶子节点非叶子节点只存索引键和指针叶子节点之间通过双向链表连接支持范围查询查询必须从根节点开始逐步向下定位6.2 索引失效的底层原理失效场景底层原因LIKE %xxxB 树按前缀有序无法确定 %xxx 的起始位置只能全表扫描函数操作对列做函数操作改变了列的值B 树无法用于函数计算后的值类型转换类型转换在比较前发生B 树存储的是原始类型值无法匹配OR 条件OR 两边可能使用不同索引优化器选择全表扫描不满足最左前缀B 树索引按(a, b, c)顺序组织跳过 a 无法确定 b 的位置范围查询后列范围查询后后续列的排序被打断无法继续使用索引B 树查找的两种方式查找方式复杂度条件索引定位O(log n)能确定键的精确位置或范围起始点全表扫描O(n)无法确定起始位置或需要访问大部分数据6.3 InnoDB 索引存储细节聚簇索引Clustered Index主键即为聚簇索引叶子节点存储完整行数据表数据按主键顺序组织存储二级索引Secondary Index叶子节点存储主键值查询时需要回表走聚簇索引获取完整数据回表代价分析二级索引查询 → 获取主键值 → 回表访问聚簇索引 → 获取完整行数据当type index且Extra Using index时表示使用了覆盖索引无需回表覆盖索引是性能最优的方式应尽量让查询只访问索引就能获取所需字段七、索引数据结构和算法分析7.1 B 树 vs B 树对比B 树B 树数据存储位置所有节点存储数据仅叶子节点存储数据非叶子节点存储数据 指针只存索引键 指针查询复杂度O(log n)O(log n)更稳定范围查询需要中序遍历叶子节点链表直接遍历磁盘 I/O更多非叶子存数据高度更高更少非叶子只存键可存更多索引InnoDB 使用❌✅7.2 索引查找算法等值查询sql-- 走 B 树查找 SELECT * FROM users WHERE id 123; -- 复杂度: O(log n)范围查询sql-- 走 B 树查找 叶子链表遍历 SELECT * FROM users WHERE id BETWEEN 100 AND 200; -- 复杂度: O(log n m)m 为结果行数覆盖索引sql-- 只访问二级索引无需回表 SELECT id, name FROM users WHERE name 张三; -- 如果建立了 (name, id) 联合索引7.3 索引选择率与基数基数Cardinality索引中唯一值的数量代表索引的选择性基数越高索引的选择性越好优化器根据基数决定是否使用索引sql-- 查看索引基数 SHOW INDEX FROM users; -- Cardinality 列表示基数示例分析性别列基数为 2男/女选择性极低手机号列基数接近行数选择性极高选择性低于 15% 的索引优化器可能放弃使用八、使用索引的性能提升结果度量8.1 关键性能指标指标说明目标扫描行数查询扫描的行数尽可能小查询耗时SQL 执行耗时控制在业务容忍范围内回表次数二级索引查询后回表次数越少越好索引命中率缓存命中率越高越好QPS/TPS每秒查询/事务数越高越好8.2 性能度量工具sql-- 1. 查看查询执行时间 SET profiling 1; SELECT * FROM users WHERE age 18; SHOW PROFILES; -- 2. 查看查询总执行次数和耗时 SHOW STATUS LIKE Com_select; SHOW STATUS LIKE Innodb_rows_read; -- 3. 查看索引使用情况 SHOW STATUS LIKE Handler_read%; -- 4. 查看慢查询日志配置 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time;8.3 度量示例索引前后的性能对比sql-- 1. 无索引 EXPLAIN SELECT * FROM users WHERE phone 13800138000; -- type ALL, rows 100000, 耗时 ≈ 200ms -- 2. 创建索引 CREATE INDEX idx_phone ON users (phone); -- 3. 有索引 EXPLAIN SELECT * FROM users WHERE phone 13800138000; -- type ref, rows 1, 耗时 ≈ 5ms -- 性能提升扫描行数减少 99.999%耗时减少 97.5%8.4 实战度量方法根据高可用架构团队的实践经验以下优化策略能显著提升查询性能高性能分页优化对于深度分页大偏移量查询应优先使用延迟关联技术先通过覆盖索引获取主键再回表获取完整数据避免大偏移量带来的全表扫描开销。MySQL 架构优化在大型互联网项目中常采用MySQL 架构三剑客分库分表 读写分离 缓存的组合方案。其中索引优化与这些架构方案协同工作例如在分库分表后需要确保全局唯一 ID 的索引策略一致性。全链路压测验证索引优化的效果最终需要通过全链路压测来验证观察优化后的查询在业务高峰期的实际表现确保优化方案在生产环境中的稳定性。监控与持续优化利用慢查询监控系统定期分析执行计划中的type、rows、Extra等关键指标识别新增的慢查询并持续优化索引策略。九、EXPLAIN type 比较all、ref、const 等9.1 type 完整对比表type含义性能典型 SQL优化方向system系统表只有一行⭐⭐⭐⭐⭐SHOW TABLES无需优化const主键/唯一索引等值查询结果 1 行⭐⭐⭐⭐⭐WHERE id 1无需优化eq_refJOIN 中使用主键/唯一索引⭐⭐⭐⭐⭐JOIN ON a.id b.id确保 JOIN 列有索引ref非唯一索引等值查询⭐⭐⭐⭐WHERE name 张三确保有索引ref_or_nullref NULL 值查询⭐⭐⭐⭐WHERE name 张三 OR name IS NULL注意 NULL 值处理range索引范围查询⭐⭐⭐WHERE age 18考虑减少范围index全索引扫描⭐⭐SELECT count(*)评估是否需要优化ALL全表扫描⭐无索引查询必须添加索引9.2 ref 详解ref表示使用非唯一索引进行等值匹配通常出现在sql-- 普通索引等值查询 SELECT * FROM users WHERE name 张三; -- type ref, key idx_name, rows 5 -- 复合索引最左前缀匹配 SELECT * FROM users WHERE age 18 AND name 张三; -- 如果索引是 (age, name)type refref 的特征扫描行数 索引中匹配的行数性能取决于匹配行数基数越低匹配行越多9.3 ALL 详解ALL表示全表扫描是最差的访问类型sql-- 典型场景 SELECT * FROM users WHERE name 张三; -- 如果 name 没有索引type ALL为什么 ALL 是最差的需要扫描整个表的所有数据页I/O 开销巨大尤其是数据量大的表读取的数据量远多于实际需要的数据优化建议为 WHERE 条件列创建索引避免在索引列上使用函数避免使用!、等操作符优化 OR 条件或拆分为 UNION9.4 NULL无索引当key NULL时表示没有使用任何索引原因表中没有合适的索引优化器判断全表扫描比索引更优如小表、低选择性索引失效如函数操作、隐式转换sql-- 排查方法 SHOW INDEX FROM table_name; EXPLAIN SELECT ...;9.5 性能对比示例sql-- 示例表users (100万行) -- 场景1全表扫描无索引 EXPLAIN SELECT * FROM users WHERE phone 13800138000; -- type ALL, rows 1000000, 耗时 ≈ 500ms -- 场景2普通索引 CREATE INDEX idx_phone ON users (phone); EXPLAIN SELECT * FROM users WHERE phone 13800138000; -- type ref, rows 1, 耗时 ≈ 5ms -- 性能提升 100 倍 -- 场景3覆盖索引只查索引字段 CREATE INDEX idx_phone_id ON users (phone, id); EXPLAIN SELECT phone, id FROM users WHERE phone 13800138000; -- type ref, Extra Using index, 无需回表 -- 相比场景2省去了回表开销性能进一步提升十、总结知识点核心要点SQL 执行过程解析 → 优化 → 执行优化器决定使用索引还是全表扫描EXPLAIN 关键type 从优到差const → ref → range → index → ALL索引失效LIKE %xx、函数操作、类型转换、不满足最左前缀B 树原理索引按顺序组织失效本质是破坏了有序性性能度量关注扫描行数、回表次数、Extra 信息优化核心让 SQL 走索引、减少扫描行数、尽量使用覆盖索引EXPLAIN 快速检查清单type是否为 ALL→ 考虑添加索引key是否为 NULL→ 检查索引是否可用rows是否过大→ 优化查询条件Extra是否有 Using filesort/Using temporary→ 优化排序/分组Extra是否有 Using index→ 覆盖索引理想状态ref是否正确指向驱动表的列→ 检查 JOIN 条件
数据库执行过程与索引优化深度分析
发布时间：2026/7/28 22:48:35
一、数据库执行过程介绍1.1 SQL 执行全流程MySQL 执行一条 SQL 查询的完整流程如下text客户端发送 SQL ↓ 连接管理器 ↓ 查询缓存MySQL 8.0 已移除 ↓ ┌───────────────────────────────────────┐ │ SQL 解析器 │ │ • 词法分析 → 生成 Token 序列 │ │ • 语法分析 → 生成 AST抽象语法树 │ └───────────────────────────────────────┘ ↓ ┌───────────────────────────────────────┐ │ 查询优化器 │ │ • 预处理语义检查、权限检查 │ │ • 逻辑优化视图合并、谓词下推等 │ │ • 物理优化选择最优执行计划 │ │ • 生成执行计划 │ └───────────────────────────────────────┘ ↓ ┌───────────────────────────────────────┐ │ 执行引擎 │ │ • 调用存储引擎接口 │ │ • 读取数据 │ │ • 返回结果集 │ └───────────────────────────────────────┘ ↓ 返回结果给客户端1.2 查询优化器的核心工作查询优化器是数据库的大脑其核心任务是从多个可能的执行计划中选择代价最小的一个。优化器考虑的代价因素代价维度说明I/O 代价磁盘读取次数最主要CPU 代价数据处理、排序、聚合等计算内存代价临时表、排序缓冲区的使用网络代价数据传输量优化器如何估算代价使用表统计信息行数、索引基数、数据分布等采用基于代价的优化CBO策略当统计信息过时时可能选择次优计划导致性能问题可通过ANALYZE TABLE更新统计信息优化器的关键决策表连接顺序决定多表查询的驱动表顺序索引选择选择哪个索引或全表扫描连接算法选择 Nested Loop、Hash Join 还是 Sort-Merge子查询优化决定是否将子查询转换为 JOIN 或 EXISTS1.3 存储引擎的作用执行引擎通过存储引擎接口Handler API与存储引擎通信每种存储引擎的实现不同存储引擎特点适用场景InnoDB支持事务、行级锁、MVCC大多数 OLTP 场景默认MyISAM不支持事务、表级锁读多写少的场景Memory数据在内存中临时表、缓存表二、EXPLAIN 详解2.1 基本用法sql-- 查看执行计划 EXPLAIN SELECT * FROM users WHERE age 18; -- 显示更多信息MySQL 8.0 EXPLAIN FORMATTREE SELECT * FROM users WHERE age 18; -- 显示 JSON 格式的详细执行计划 EXPLAIN FORMATJSON SELECT * FROM users WHERE age 18; -- 实际执行并显示执行计划包含实际行数 EXPLAIN ANALYZE SELECT * FROM users WHERE age 18; -- MySQL 8.0.182.2 EXPLAIN 结果字段详解字段说明关键点id查询标识符id 相同表示同一组id 越大越先执行select_type查询类型SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION 等table表名或别名type访问类型性能从好到差systemconsteq_refrefrangeindexALLpossible_keys可能使用的索引key实际使用的索引如果为 NULL说明没有使用索引key_len索引使用的字节数可判断复合索引的使用情况ref与索引比较的列rows预估需要扫描的行数越小越好优化器的估算值filtered过滤后的百分比越高越好Extra额外信息包含 Using index, Using where, Using filesort 等重要信息2.3 select_type 详解值说明示例SIMPLE简单查询不包含子查询或 UNIONSELECT * FROM usersPRIMARY复杂查询的最外层 SELECT带子查询的外层SUBQUERY子查询非 FROM 子句WHERE id IN (SELECT ...)DERIVEDFROM 子句中的子查询派生表FROM (SELECT ...) AS tUNIONUNION 中的第二个或之后的 SELECTSELECT ... UNION SELECT ...UNION RESULTUNION 的结果合并三、EXPLAIN 结果深度分析3.1 type 访问类型性能排序textsystem const eq_ref ref range index ALLtype说明性能典型场景system系统表只有一行数据最优SHOW TABLES等系统查询const使用主键或唯一索引查询且结果为一条记录极优WHERE id 1eq_ref使用主键或唯一索引进行 JOIN极优JOIN ON a.id b.idref使用非唯一索引进行等值查询良好WHERE name 张三range使用索引进行范围查询一般WHERE age 18或INindex全索引扫描差SELECT count(*)覆盖索引扫描ALL全表扫描最差无索引或索引失效3.2 Extra 关键信息解读Extra含义处理建议Using index覆盖索引无需回表✅ 最佳无需优化Using where使用 WHERE 条件过滤正常Using index condition索引条件下推ICP✅ 已优化Using filesort需要额外排序⚠️ 建议优化考虑建立联合索引Using temporary使用临时表⚠️ 建议优化常见于 GROUP BY、DISTINCTUsing join buffer使用 JOIN 缓冲区⚠️ 考虑索引优化Impossible WHEREWHERE 条件永远为假❌ 检查查询逻辑3.3 案例慢查询分析sql-- 慢查询示例 EXPLAIN SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.age 18 AND o.status PAID ORDER BY u.create_time DESC; -- 可能的执行计划问题 -- 1. type ALL全表扫描 users -- 2. Extra Using filesort排序 -- 3. 预估 rows 100000 -- 4. key NULL未使用索引四、索引优化注意事项4.1 索引设计原则原则说明选择性高的列优先为基数cardinality高的列建索引WHERE 条件列经常出现在 WHERE 子句的列JOIN 关联列ON 条件中的列必须建索引ORDER BY 列避免 filesort可考虑建立联合索引GROUP BY 列减少临时表的使用覆盖索引查询需要的字段都在索引中避免回表4.2 复合索引联合索引最佳实践最左前缀原则sql-- 创建联合索引 (a, b, c) CREATE INDEX idx_a_b_c ON table (a, b, c); -- 能够使用索引的场景 WHERE a 1 -- ✅ 使用索引 WHERE a 1 AND b 2 -- ✅ 使用索引 WHERE a 1 AND b 2 AND c 3 -- ✅ 使用索引全部字段 WHERE a 1 AND c 3 -- ⚠️ 只用到 a跳过 bc 用不上 WHERE b 2 -- ❌ 不使用索引不满足最左前缀 -- ✅ 索引键顺序建议 -- 等值查询列在前范围查询列在后 CREATE INDEX idx_age_name ON users (age, name); -- 场景age 18 AND name LIKE 张% → 优先 age 等值再 name 范围索引键排序建议等值条件或IN的列放在前面范围条件、、LIKE的列放在后面高选择性列优先4.3 索引的权衡考虑因素说明查询性能提升索引能大幅减少扫描行数写入性能下降索引需要维护INSERT/UPDATE/DELETE 变慢存储空间增加索引占用额外的磁盘空间维护成本索引过多会增加优化器选择难度可能导致执行计划不稳定经验法则单表索引数量建议不超过 5-6 个不适合建立索引性别、状态等低选择性字段五、索引失效问题5.1 常见索引失效场景场景示例原因LIKE 以 % 开头WHERE name LIKE %张三无法从 B 树根节点定位使用函数WHERE DATE(create_time) 2026-07-28对列进行函数操作类型隐式转换WHERE phone 13800138000phone 是 VARCHAR类型转换导致索引失效OR 条件WHERE age 18 OR name 张三部分条件无索引使用 ! 或 WHERE age ! 18无法利用索引IS NOT NULLWHERE age IS NOT NULL索引不存储 NULL 值记录少量优化NOT IN / NOT EXISTSWHERE id NOT IN (1,2,3)范围排除复合索引不满足最左前缀WHERE b 2 AND c 3索引 a,b,c跳过了索引的第一列范围查询后的列WHERE a 1 AND b 2索引 a,bb 无法使用5.2 场景举例LIKE查询sql-- ❌ 索引失效 SELECT * FROM users WHERE name LIKE %张%; -- ✅ 使用索引 SELECT * FROM users WHERE name LIKE 张%; -- ✅ 使用覆盖索引仅查询索引字段 SELECT name FROM users WHERE name LIKE %张%;5.3 场景举例类型隐式转换sql-- 表结构phone VARCHAR(11) CREATE INDEX idx_phone ON users (phone); -- ❌ 索引失效phone 是字符串比较时转为数字 SELECT * FROM users WHERE phone 13800138000; -- ✅ 使用索引 SELECT * FROM users WHERE phone 13800138000;六、索引为什么会失效底层原理6.1 B 树索引结构B 树是 InnoDB 存储引擎使用的索引数据结构其核心特性决定了索引的失效场景text[根节点] / \ [内部节点] [内部节点] / \ / \ [叶子] [叶子] [叶子] [叶子] (1,row) (2,row) (3,row) (4,row) ↑ 双向链表连接 ↑B 树关键特性所有数据存储在叶子节点非叶子节点只存索引键和指针叶子节点之间通过双向链表连接支持范围查询查询必须从根节点开始逐步向下定位6.2 索引失效的底层原理失效场景底层原因LIKE %xxxB 树按前缀有序无法确定 %xxx 的起始位置只能全表扫描函数操作对列做函数操作改变了列的值B 树无法用于函数计算后的值类型转换类型转换在比较前发生B 树存储的是原始类型值无法匹配OR 条件OR 两边可能使用不同索引优化器选择全表扫描不满足最左前缀B 树索引按(a, b, c)顺序组织跳过 a 无法确定 b 的位置范围查询后列范围查询后后续列的排序被打断无法继续使用索引B 树查找的两种方式查找方式复杂度条件索引定位O(log n)能确定键的精确位置或范围起始点全表扫描O(n)无法确定起始位置或需要访问大部分数据6.3 InnoDB 索引存储细节聚簇索引Clustered Index主键即为聚簇索引叶子节点存储完整行数据表数据按主键顺序组织存储二级索引Secondary Index叶子节点存储主键值查询时需要回表走聚簇索引获取完整数据回表代价分析二级索引查询 → 获取主键值 → 回表访问聚簇索引 → 获取完整行数据当type index且Extra Using index时表示使用了覆盖索引无需回表覆盖索引是性能最优的方式应尽量让查询只访问索引就能获取所需字段七、索引数据结构和算法分析7.1 B 树 vs B 树对比B 树B 树数据存储位置所有节点存储数据仅叶子节点存储数据非叶子节点存储数据 指针只存索引键 指针查询复杂度O(log n)O(log n)更稳定范围查询需要中序遍历叶子节点链表直接遍历磁盘 I/O更多非叶子存数据高度更高更少非叶子只存键可存更多索引InnoDB 使用❌✅7.2 索引查找算法等值查询sql-- 走 B 树查找 SELECT * FROM users WHERE id 123; -- 复杂度: O(log n)范围查询sql-- 走 B 树查找 叶子链表遍历 SELECT * FROM users WHERE id BETWEEN 100 AND 200; -- 复杂度: O(log n m)m 为结果行数覆盖索引sql-- 只访问二级索引无需回表 SELECT id, name FROM users WHERE name 张三; -- 如果建立了 (name, id) 联合索引7.3 索引选择率与基数基数Cardinality索引中唯一值的数量代表索引的选择性基数越高索引的选择性越好优化器根据基数决定是否使用索引sql-- 查看索引基数 SHOW INDEX FROM users; -- Cardinality 列表示基数示例分析性别列基数为 2男/女选择性极低手机号列基数接近行数选择性极高选择性低于 15% 的索引优化器可能放弃使用八、使用索引的性能提升结果度量8.1 关键性能指标指标说明目标扫描行数查询扫描的行数尽可能小查询耗时SQL 执行耗时控制在业务容忍范围内回表次数二级索引查询后回表次数越少越好索引命中率缓存命中率越高越好QPS/TPS每秒查询/事务数越高越好8.2 性能度量工具sql-- 1. 查看查询执行时间 SET profiling 1; SELECT * FROM users WHERE age 18; SHOW PROFILES; -- 2. 查看查询总执行次数和耗时 SHOW STATUS LIKE Com_select; SHOW STATUS LIKE Innodb_rows_read; -- 3. 查看索引使用情况 SHOW STATUS LIKE Handler_read%; -- 4. 查看慢查询日志配置 SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time;8.3 度量示例索引前后的性能对比sql-- 1. 无索引 EXPLAIN SELECT * FROM users WHERE phone 13800138000; -- type ALL, rows 100000, 耗时 ≈ 200ms -- 2. 创建索引 CREATE INDEX idx_phone ON users (phone); -- 3. 有索引 EXPLAIN SELECT * FROM users WHERE phone 13800138000; -- type ref, rows 1, 耗时 ≈ 5ms -- 性能提升扫描行数减少 99.999%耗时减少 97.5%8.4 实战度量方法根据高可用架构团队的实践经验以下优化策略能显著提升查询性能高性能分页优化对于深度分页大偏移量查询应优先使用延迟关联技术先通过覆盖索引获取主键再回表获取完整数据避免大偏移量带来的全表扫描开销。MySQL 架构优化在大型互联网项目中常采用MySQL 架构三剑客分库分表 读写分离 缓存的组合方案。其中索引优化与这些架构方案协同工作例如在分库分表后需要确保全局唯一 ID 的索引策略一致性。全链路压测验证索引优化的效果最终需要通过全链路压测来验证观察优化后的查询在业务高峰期的实际表现确保优化方案在生产环境中的稳定性。监控与持续优化利用慢查询监控系统定期分析执行计划中的type、rows、Extra等关键指标识别新增的慢查询并持续优化索引策略。九、EXPLAIN type 比较all、ref、const 等9.1 type 完整对比表type含义性能典型 SQL优化方向system系统表只有一行⭐⭐⭐⭐⭐SHOW TABLES无需优化const主键/唯一索引等值查询结果 1 行⭐⭐⭐⭐⭐WHERE id 1无需优化eq_refJOIN 中使用主键/唯一索引⭐⭐⭐⭐⭐JOIN ON a.id b.id确保 JOIN 列有索引ref非唯一索引等值查询⭐⭐⭐⭐WHERE name 张三确保有索引ref_or_nullref NULL 值查询⭐⭐⭐⭐WHERE name 张三 OR name IS NULL注意 NULL 值处理range索引范围查询⭐⭐⭐WHERE age 18考虑减少范围index全索引扫描⭐⭐SELECT count(*)评估是否需要优化ALL全表扫描⭐无索引查询必须添加索引9.2 ref 详解ref表示使用非唯一索引进行等值匹配通常出现在sql-- 普通索引等值查询 SELECT * FROM users WHERE name 张三; -- type ref, key idx_name, rows 5 -- 复合索引最左前缀匹配 SELECT * FROM users WHERE age 18 AND name 张三; -- 如果索引是 (age, name)type refref 的特征扫描行数 索引中匹配的行数性能取决于匹配行数基数越低匹配行越多9.3 ALL 详解ALL表示全表扫描是最差的访问类型sql-- 典型场景 SELECT * FROM users WHERE name 张三; -- 如果 name 没有索引type ALL为什么 ALL 是最差的需要扫描整个表的所有数据页I/O 开销巨大尤其是数据量大的表读取的数据量远多于实际需要的数据优化建议为 WHERE 条件列创建索引避免在索引列上使用函数避免使用!、等操作符优化 OR 条件或拆分为 UNION9.4 NULL无索引当key NULL时表示没有使用任何索引原因表中没有合适的索引优化器判断全表扫描比索引更优如小表、低选择性索引失效如函数操作、隐式转换sql-- 排查方法 SHOW INDEX FROM table_name; EXPLAIN SELECT ...;9.5 性能对比示例sql-- 示例表users (100万行) -- 场景1全表扫描无索引 EXPLAIN SELECT * FROM users WHERE phone 13800138000; -- type ALL, rows 1000000, 耗时 ≈ 500ms -- 场景2普通索引 CREATE INDEX idx_phone ON users (phone); EXPLAIN SELECT * FROM users WHERE phone 13800138000; -- type ref, rows 1, 耗时 ≈ 5ms -- 性能提升 100 倍 -- 场景3覆盖索引只查索引字段 CREATE INDEX idx_phone_id ON users (phone, id); EXPLAIN SELECT phone, id FROM users WHERE phone 13800138000; -- type ref, Extra Using index, 无需回表 -- 相比场景2省去了回表开销性能进一步提升十、总结知识点核心要点SQL 执行过程解析 → 优化 → 执行优化器决定使用索引还是全表扫描EXPLAIN 关键type 从优到差const → ref → range → index → ALL索引失效LIKE %xx、函数操作、类型转换、不满足最左前缀B 树原理索引按顺序组织失效本质是破坏了有序性性能度量关注扫描行数、回表次数、Extra 信息优化核心让 SQL 走索引、减少扫描行数、尽量使用覆盖索引EXPLAIN 快速检查清单type是否为 ALL→ 考虑添加索引key是否为 NULL→ 检查索引是否可用rows是否过大→ 优化查询条件Extra是否有 Using filesort/Using temporary→ 优化排序/分组Extra是否有 Using index→ 覆盖索引理想状态ref是否正确指向驱动表的列→ 检查 JOIN 条件
