🚫 MySQL查询性能陷阱

动态可视化 - 深入理解索引失效的原理

1

LIKE '%keyword%' - 前置百分号陷阱

索引失效

📝 代码示例

-- ❌ 错误：前置百分号，索引失效
SELECT * FROM users WHERE name LIKE '%明%';

-- 原因：B+树无法定位起始位置

-- ✅ 正确：后置百分号，可以使用索引
SELECT * FROM users WHERE name LIKE '张%';

🎯 B+树索引查找原理

💡 原理解释

  • B+树索引是有序存储的，支持前缀匹配
  • LIKE '张%'：可以从"张"开始定位，然后顺序扫描
  • LIKE '%明%'：无法知道起始位置，只能全表扫描
  • 解决方案：使用全文索引、搜索引擎（Elasticsearch）
🐌
LIKE '%明%'
全表扫描
100万行 → 扫描100万次
⚡
LIKE '张%'
索引查找
100万行 → 扫描50次
2

NOT IN - 子查询陷阱

性能+NULL陷阱

📝 代码示例

-- ❌ 错误：NOT IN 有NULL陷阱
SELECT * FROM orders WHERE user_id NOT IN (SELECT user_id FROM blacklist);

-- 如果blacklist中有NULL，整个查询返回空！
-- SELECT 1 NOT IN (2, NULL) → 返回NULL，不是TRUE

-- ✅ 正确：使用 NOT EXISTS
SELECT * FROM orders o WHERE NOT EXISTS (
  SELECT 1 FROM blacklist b WHERE b.user_id = o.user_id
);

🎯 NOT IN vs NOT EXISTS 执行过程

💡 原理解释

  • NULL值陷阱：NOT IN (1, 2, NULL) 永远返回FALSE
  • 性能问题：子查询可能执行多次，无法优化
  • NOT EXISTS优势：遇到NULL正确处理，可以使用关联索引
  • 解决方案：用NOT EXISTS、LEFT JOIN + IS NULL替代
3

索引列上计算 - 函数陷阱

索引失效

📝 代码示例

-- ❌ 错误：在索引列上使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2024;
SELECT * FROM users WHERE age + 1 > 20;

-- 原因：索引存储的是原始值，不是计算结果

-- ✅ 正确：将计算放在等号右边
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
SELECT * FROM users WHERE age > 19;

🎯 索引存储 vs 计算后的结果

💡 原理解释

  • 索引存储：B+树中存储的是列的原始值（如：2024-05-01）
  • 函数计算：YEAR(create_time)需要对每一行进行计算
  • 无法使用索引：索引树按原始值排序，不是按函数结果排序
  • 解决方案：将计算放在等号右边，使用范围查询
🚫
YEAR(create_time)
全表扫描
每行都要计算函数
✅
create_time >= '2024-01-01'
索引范围扫描
直接定位索引范围
4

OR 条件 - 索引合并陷阱

可能索引失效

📝 代码示例

-- ❌ 危险：OR 可能导致全表扫描
SELECT * FROM users WHERE name = '张三' OR age = 20;

-- 如果name有索引，age没有索引 → 全表扫描

-- ✅ 正确：使用 UNION 替代 OR
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 20;

🎯 OR vs UNION 执行计划对比

💡 原理解释

  • OR的问题：需要同时满足两个条件的索引才能使用索引合并
  • 索引合并：MySQL可能使用index_merge，但效率低
  • UNION优势：每个子查询可以使用自己的索引
  • 最佳实践：确保OR的每个条件都有索引，或用UNION重写

📊 性能对比总结

场景 错误写法 正确写法 性能提升
LIKE查询 '%keyword%' 'keyword%' 100x ⬆️
NOT IN NOT IN (子查询) NOT EXISTS 10x ⬆️
索引列计算 YEAR(column) column >= 'value' 50x ⬆️
OR条件 WHERE a=x OR b=y UNION 5x ⬆️