MySQL索引优化实战：B+树原理与查询性能调优方案

MySQL索引底层数据结构：B+树原理详解

MySQL索引优化是数据库运维中提升查询性能最直接有效的手段。InnoDB存储引擎使用B+树作为索引数据结构，理解B+树的工作原理是做好索引优化的前提。B+树的所有数据存储在叶子节点，非叶子节点只存索引键和子节点指针，单个节点可以存储更多键值，树的高度更低，磁盘IO次数更少。

InnoDB的聚簇索引将索引和数据存储在同一棵B+树中，叶子节点存储完整行数据。二级索引的叶子节点存储主键值，查询二级索引后可能需要回表（用主键查聚簇索引获取完整行）。覆盖索引（Covering Index）指查询所需的列全部包含在索引中，无需回表，性能显著提升。数据库高可用架构设计中，合理的索引策略可以大幅降低查询延迟。

索引设计原则与最左前缀匹配

联合索引遵循最左前缀匹配原则：(a, b, c)索引可以用于a、(a,b)、(a,b,c)查询，但不能用于b、c或(b,c)查询。理解这一原则对设计联合索引至关重要。SQL查询优化中，索引列顺序的选择直接决定查询能否命中索引。

-- 创建联合索引
CREATE INDEX idx_order ON orders(user_id, status, create_time);

-- 能命中索引
SELECT * FROM orders WHERE user_id = 100;
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid' AND create_time > '2026-01-01';

-- 不能命中索引（缺少最左列user_id）
SELECT * FROM orders WHERE status = 'paid';
SELECT * FROM orders WHERE create_time > '2026-01-01';
SELECT * FROM orders WHERE status = 'paid' AND create_time > '2026-01-01';

-- 部分命中（user_id命中，create_time无法利用索引，因为中间status缺失）
SELECT * FROM orders WHERE user_id = 100 AND create_time > '2026-01-01';

EXPLAIN执行计划分析与索引失效排查

EXPLAIN是MySQL索引优化的核心工具，通过执行计划判断索引使用情况。重点关注type、key、rows、Extra四个字段：

EXPLAIN SELECT order_id, user_id, amount FROM orders 
WHERE user_id = 100 AND status = 'paid' AND create_time > '2026-08-01'\G

type字段表示访问类型，从好到差依次是：const > eq_ref > ref > range > index > ALL。const为主键或唯一索引等值查询，ref为非唯一索引等值查询，range为范围查询，index为扫描整棵索引树，ALL为全表扫描。生产环境应避免ALL和index类型。

key字段显示实际使用的索引名，NULL表示未使用索引。rows字段是预估扫描行数，越小越好。Extra字段包含额外信息：

-- Using index：覆盖索引，无需回表（理想状态）
-- Using where：需要回表后过滤
-- Using filesort：需要额外排序（需优化）
-- Using temporary：使用临时表（需优化）
-- Using index condition：索引下推（ICP优化）

常见索引失效场景与修复方案

-- 1. 函数操作导致索引失效
-- 错误：对索引列使用函数
SELECT * FROM orders WHERE DATE(create_time) = '2026-08-01';
-- 修复：改为范围查询
SELECT * FROM orders WHERE create_time >= '2026-08-01' AND create_time < '2026-08-02';

-- 2. 隐式类型转换导致索引失效
-- 错误：status是varchar，传入整数
SELECT * FROM orders WHERE status = 1;
-- 修复：传入字符串
SELECT * FROM orders WHERE status = '1';

-- 3. LIKE前导通配符导致索引失效
-- 错误：以%开头的LIKE
SELECT * FROM users WHERE name LIKE '%张';
-- 修复：使用后缀通配符
SELECT * FROM users WHERE name LIKE '张%';

-- 4. OR条件中部分列无索引导致整体失效
-- 错误：user_id有索引但order_no无索引
SELECT * FROM orders WHERE user_id = 100 OR order_no = 'ORD20260831001';
-- 修复：为order_no也创建索引
CREATE INDEX idx_order_no ON orders(order_no);

-- 5. 范围查询后的列无法使用索引
-- 对于联合索引(user_id, status, create_time)
-- create_time > '2026-08-01'是范围查询，其后的列无法使用索引
SELECT * FROM orders WHERE user_id = 100 AND create_time > '2026-08-01' AND status = 'paid';
-- 优化：调整联合索引列顺序为(user_id, create_time, status)或拆分查询

索引下推（ICP）与覆盖索引优化

索引下推（Index Condition Pushdown）是MySQL 5.6引入的优化，将WHERE条件过滤下推到存储引擎层，减少回表次数。对于联合索引(a,b,c)，查询WHERE a=1 AND c=3，无ICP时先用a=1查索引获取所有匹配行回表，再用c=3过滤；有ICP时在索引层直接用c=3过滤，减少回表数据量。分库分表方案中，索引下推可以显著减少跨分片查询的数据传输量。

覆盖索引是查询列全部包含在索引中，直接从索引返回数据无需回表。设计索引时考虑将高频查询的SELECT列纳入索引：

-- 高频查询：根据user_id查询order_id和amount
SELECT order_id, amount FROM orders WHERE user_id = 100;

-- 创建覆盖索引，避免回表
CREATE INDEX idx_user_cover ON orders(user_id, order_id, amount);

-- EXPLAIN结果：type=ref, Extra=Using index（覆盖索引生效）

索引选择性计算与冗余索引清理

索引选择性是指不重复值数量与总行数的比值，取值范围0到1，越接近1选择性越好。主键选择性为1，性别字段选择性约0.5。低选择性的列单独建索引意义不大，但作为联合索引的组成部分可以提升整体效果。SQL查询优化时应优先关注高选择性列的索引设计。

-- 计算列的选择性
SELECT 
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
    COUNT(DISTINCT create_time) / COUNT(*) AS time_selectivity
FROM orders;

-- 联合索引列顺序：选择性高的列放前面
-- 如果user_id_selectivity=0.8, status_selectivity=0.01
-- 应该是(user_id, status)而不是(status, user_id)

冗余索引会浪费存储空间并降低写入性能。定期检查并清理冗余索引：

-- 查看索引使用情况（MySQL 8.0+）
SELECT 
    object_schema, object_name, index_name,
    count_read, count_write, count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_name = 'orders'
ORDER BY count_read ASC;

-- 未被使用的索引（count_read=0且非唯一索引）可以考虑删除
-- 冗余索引：如果存在(a,b,c)和(a,b)，后者是冗余的

索引不是越多越好，每个索引增加写入时的维护成本。单表索引数量建议不超过5-6个，每个索引应有明确的使用场景。使用sys.schema_unused_indexes视图查找长期未使用的索引，使用sys.schema_redundant_indexes视图查找冗余索引，定期清理以保持数据库性能。数据迁移实战中，索引重建策略也是影响迁移耗时的关键因素，大批量数据导入前临时禁用非唯一索引可以显著提升导入速度。