卧槽，为啥我加了索引还是慢？—— 索引失效的10种死法

重生之我在二本学院拿offer当牌打
57 阅读29分钟

摘要：从一次线上慢查询事故出发，通过三位开发者的踩坑经历，深度剖析MySQL索引失效的10种常见场景。涵盖函数计算、类型转换、前缀模糊匹配等经典问题，配合B+树原理解析、EXPLAIN执行计划分析、源码级探究，并手写索引选择模拟器。真实案例+完整代码+流程图，让你彻底搞懂"为什么加了索引还是全表扫描"。

💥 翻车现场

凌晨2点，哈吉米被电话吵醒。

"用户反馈商城首页加载超时！RDS的CPU已经飙到95%了！"

哈吉米一个激灵爬起来，打开监控平台，慢查询日志里密密麻麻全是这条SQL：

SELECT * FROM product 
WHERE DATE(create_time) = '2024-10-01' 
AND status = 1

"卧槽，这条SQL不是已经加了索引吗？我明明在 create_time 和 status 上都建了索引啊！"

紧急回滚代码后，哈吉米在钉钉群里@了南北绿豆和阿西噶阿西：

哈吉米：兄弟们救命！为啥我加了索引还是慢成狗？
南北绿豆：发下EXPLAIN看看
阿西噶阿西：我猜又是你用了DATE()函数吧？
哈吉米：？？？有问题吗？
南北绿豆：有大问题！索引直接废了

第二天早上，三人约了个会议室，阿西噶阿西在白板上写下：

索引失效的10种死法，每一种都能让你的数据库原地爆炸

🤔 先搞清楚：索引到底是怎么工作的？

南北绿豆："在讲索引失效之前，咱们先搞明白索引是怎么加速查询的。"

B+树的快速定位原理

想象你在图书馆找一本书：

  • 没索引：从第一本开始翻，找到天荒地老（全表扫描）
  • 有索引：先看目录，定位到具体书架，再找具体的书（树形查找）

MySQL的InnoDB引擎用的是B+树索引，长这样：

                [50]
               /    \
         [20,35]    [65,80]
        /  |  \      /  |  \
    [10] [25] [40] [55] [70] [90]
     ↓    ↓    ↓    ↓    ↓    ↓
   叶子节点（存完整数据行）

关键特性：

  1. 有序存储：索引列的值从左到右递增
  2. 快速定位：O(log n) 时间复杂度
  3. 范围查询：叶子节点是双向链表，扫描连续区间很快

阿西噶阿西："重点来了！索引能加速查询的前提是：查询条件能直接利用索引的有序性。"

哈吉米："什么叫'直接利用有序性'？"

南北绿豆："比如你查 WHERE age = 25，MySQL直接在B+树里二分查找25这个值。但如果你查 WHERE age + 1 = 26，MySQL就懵了：'你让我找age+1等于26的，那我得把每个age值都加1再比较，索引白建了！'"

哈吉米："卧槽，恍然大悟！"

🔥 索引失效的10种死法

死法1️⃣：在索引列上使用函数

场景重现：

-- 哈吉米的翻车SQL
SELECT * FROM product 
WHERE DATE(create_time) = '2024-10-01';

-- 索引定义
CREATE INDEX idx_create_time ON product(create_time);

EXPLAIN分析：

EXPLAIN SELECT * FROM product WHERE DATE(create_time) = '2024-10-01'\G

*************************** 1. row ***************************
           type: ALL          -- 全表扫描！
      possible_keys: NULL      -- 没用上索引
            key: NULL
           rows: 580000        -- 扫描了58万行
          Extra: Using where

为什么失效？

索引存的是 create_time 的原始值（如 2024-10-01 14:23:56），是有序的：

索引树： 2024-10-01 10:00:00
         2024-10-01 14:23:56
         2024-10-01 18:45:12
         2024-10-02 09:12:33

但你查 DATE(create_time)，MySQL必须对每一行都执行 DATE() 函数后再比较：

需要计算： DATE(2024-10-01 10:00:00) = 2024-10-01 ✅
          DATE(2024-10-01 14:23:56) = 2024-10-01 ✅
          DATE(2024-10-02 09:12:33) = 2024-10-01 ❌

索引的有序性被破坏了，只能全表扫描！

正确写法：

-- 改成范围查询，让索引发挥作用
SELECT * FROM product 
WHERE create_time >= '2024-10-01 00:00:00' 
  AND create_time < '2024-10-02 00:00:00';

EXPLAIN对比：

EXPLAIN SELECT * FROM product 
WHERE create_time >= '2024-10-01 00:00:00' 
  AND create_time < '2024-10-02 00:00:00'\G

*************************** 1. row ***************************
           type: range        -- 范围扫描，走索引了！
      possible_keys: idx_create_time
            key: idx_create_time
           rows: 1523         -- 只扫描1523行
          Extra: Using index condition

常见函数陷阱：

错误写法正确写法
WHERE YEAR(create_time) = 2024WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'
WHERE SUBSTRING(phone, 1, 3) = '138'WHERE phone LIKE '138%'
WHERE UPPER(name) = 'AJOCER'WHERE name = 'AJOCER'（或建函数索引）
WHERE amount + 100 > 500WHERE amount > 400

死法2️⃣：隐式类型转换

阿西噶阿西："上次我就栽在这个坑里！"

-- 表结构
CREATE TABLE user (
  id INT PRIMARY KEY,
  phone VARCHAR(11),
  age INT,
  INDEX idx_phone(phone)
);

-- 阿西噶阿西的SQL
SELECT * FROM user WHERE phone = 13800138000;  -- 注意：phone是字符串，但传了数字

EXPLAIN分析：

EXPLAIN SELECT * FROM user WHERE phone = 13800138000\G

*************************** 1. row ***************************
           type: ALL          -- 全表扫描
      possible_keys: idx_phone
            key: NULL          -- 索引失效
           rows: 120000
          Extra: Using where

为什么失效？

南北绿豆："MySQL的类型转换规则是这样的："

-- 字符串 vs 数字：字符串转成数字
WHERE phone = 13800138000 
-- 等价于
WHERE CAST(phone AS UNSIGNED) = 13800138000

相当于在索引列上加了 CAST() 函数，索引直接废掉！

源码层面分析（MySQL 5.7 源码 item_cmpfunc.cc）：

// MySQL在比较不同类型时的转换逻辑
Arg_comparator::set_cmp_func() {
  if (left_type == STRING_RESULT && right_type == INT_RESULT) {
    // 字符串字段 vs 整数常量 → 把字符串转成整数
    func = &Arg_comparator::compare_string_as_int;
    // 这会导致索引失效！
  }
}

正确写法：

-- 加上引号，类型匹配
SELECT * FROM user WHERE phone = '13800138000';

对比测试：

-- 错误写法（全表扫描）
SELECT * FROM user WHERE phone = 13800138000;
-- 耗时：2.3秒，扫描12万行

-- 正确写法（走索引）
SELECT * FROM user WHERE phone = '13800138000';
-- 耗时：0.02秒，扫描1行

反向案例：

-- 如果是数字字段，传字符串反而没问题
SELECT * FROM user WHERE age = '25';  -- age是INT
-- 等价于
SELECT * FROM user WHERE age = 25;    -- 常量被转换，索引仍然有效

哈吉米："为什么这个方向就没问题？"

南北绿豆："因为转换的是常量，不是索引列！MySQL把 '25' 转成 25 后，仍然可以在索引树里二分查找。"

类型转换规则总结：

场景MySQL行为索引是否失效
VARCHAR字段 = 数字字段转成数字❌ 失效
INT字段 = '字符串'字符串转成数字✅ 有效
DATE字段 = '2024-10-01'字符串转成DATE✅ 有效
VARCHAR字段 LIKE 数字字段转成数字❌ 失效

死法3️⃣：前缀模糊匹配（LIKE '%xxx'）

哈吉米："这个我知道！LIKE '%abc' 不走索引，LIKE 'abc%' 走索引！"

阿西噶阿西："知道为什么吗？"

哈吉米："呃……因为……B+树是有序的？"

南北绿豆："对了一半！关键是索引的有序性只对前缀有效。"

原理图解：

索引树（按name排序）：
  Alice
  Bob
  Bobby
  Brandon
  Charlie

情况1：LIKE 'Bo%'（后缀模糊）

查找逻辑：
1. 在索引树里定位到 'Bo' 开头的位置（Bob）
2. 顺着叶子节点往后扫描，直到不是 'Bo' 开头为止
3. 只扫描了 Bob、Bobby 两行 ✅

情况2：LIKE '%ob'（前缀模糊）

查找逻辑：
1. 索引树按首字母排序，但结尾是啥不知道
2. 必须遍历所有行，逐个检查是否以 'ob' 结尾
3. 扫描了全部行 ❌

实战案例：

-- 商品搜索功能
CREATE TABLE product (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  INDEX idx_name(name)
);

-- 场景1：搜索"苹果手机"
SELECT * FROM product WHERE name LIKE '苹果%';  -- ✅ 走索引

-- 场景2：搜索"手机"结尾的商品
SELECT * FROM product WHERE name LIKE '%手机';  -- ❌ 全表扫描

EXPLAIN对比：

EXPLAIN SELECT * FROM product WHERE name LIKE '苹果%'\G
type: range          -- 走索引
key: idx_name
rows: 234

EXPLAIN SELECT * FROM product WHERE name LIKE '%手机'\G
type: ALL            -- 全表扫描
key: NULL
rows: 58000

解决方案：

方案1：全文索引（适合中文搜索）

-- 创建全文索引
ALTER TABLE product ADD FULLTEXT INDEX ft_name(name) WITH PARSER ngram;

-- 使用全文搜索
SELECT * FROM product WHERE MATCH(name) AGAINST('手机' IN BOOLEAN MODE);

方案2：Elasticsearch（推荐）

// 同步数据到ES
@Service
public class ProductSearchService {
    
    @Autowired
    private ElasticsearchClient esClient;
    
    public List<Product> search(String keyword) {
        // ES的倒排索引天然支持模糊搜索
        SearchResponse<Product> response = esClient.search(s -> s
            .index("product")
            .query(q -> q
                .match(m -> m
                    .field("name")
                    .query(keyword)
                )
            ), Product.class
        );
        return response.hits().hits().stream()
            .map(hit -> hit.source())
            .collect(Collectors.toList());
    }
}

方案3：反向索引（针对后缀匹配）

-- 如果经常搜索后缀，可以存储反向字符串
ALTER TABLE product ADD COLUMN name_reverse VARCHAR(100);
UPDATE product SET name_reverse = REVERSE(name);
CREATE INDEX idx_name_reverse ON product(name_reverse);

-- 查询时反转搜索词
SELECT * FROM product WHERE name_reverse LIKE REVERSE('%手机');

死法4️⃣：联合索引不遵循最左前缀原则

阿西噶阿西："这个是面试高频题！"

-- 表结构
CREATE TABLE order_info (
  id INT PRIMARY KEY,
  user_id INT,
  order_status TINYINT,
  create_time DATETIME,
  INDEX idx_uid_status_time(user_id, order_status, create_time)
);

南北绿豆："联合索引 (user_id, order_status, create_time) 的数据结构是这样的："

索引树（先按user_id排序，相同再按order_status，再按create_time）：
(100, 1, 2024-10-01 10:00)
(100, 1, 2024-10-01 14:00)
(100, 2, 2024-10-02 09:00)
(101, 1, 2024-10-01 11:00)
(101, 3, 2024-10-03 15:00)
(102, 1, 2024-10-01 12:00)

查询场景对比：

SQL是否走索引原因
WHERE user_id = 100✅用到了最左列
WHERE user_id = 100 AND order_status = 1✅用到了前两列
WHERE user_id = 100 AND create_time > '2024-10-01'✅用到了user_id，create_time部分有效
WHERE order_status = 1❌跳过了user_id
WHERE order_status = 1 AND create_time > '2024-10-01'❌跳过了user_id
WHERE user_id = 100 OR order_status = 1❌OR条件无法用索引

为什么必须最左开始？

哈吉米："用个比喻来理解："

联合索引就像电话簿：

电话簿结构：
  先按姓氏排序（user_id）
    ↓
  姓氏相同的按名字排序（order_status）
    ↓
  名字相同的按年龄排序（create_time）
  • 找"姓张的" → 快，因为按姓氏排序 ✅
  • 找"姓张，名伟的" → 更快 ✅
  • 找"名伟的" → 没法快速定位，因为不知道姓氏 ❌

实战案例：

-- 场景：查询某用户的待支付订单
-- ✅ 正确写法（走索引）
SELECT * FROM order_info 
WHERE user_id = 10086 
  AND order_status = 0;

-- ❌ 错误写法（不走索引）
SELECT * FROM order_info 
WHERE order_status = 0 
  AND create_time > '2024-10-01';

EXPLAIN验证：

-- 正确写法
EXPLAIN SELECT * FROM order_info WHERE user_id = 10086 AND order_status = 0\G
type: ref
key: idx_uid_status_time
key_len: 5           -- 用了user_id(4字节) + order_status(1字节)
rows: 12

-- 错误写法
EXPLAIN SELECT * FROM order_info WHERE order_status = 0\G
type: ALL
key: NULL
rows: 89000

索引优化技巧：

技巧1：根据查询频率调整列顺序

-- 如果经常单独查order_status，可以加单列索引
CREATE INDEX idx_status ON order_info(order_status);

-- 或者调整联合索引顺序（但要考虑其他查询）
CREATE INDEX idx_status_uid_time ON order_info(order_status, user_id, create_time);

技巧2：范围查询放最后

-- ✅ 好的顺序
CREATE INDEX idx_uid_status_time ON order_info(user_id, order_status, create_time);
SELECT * FROM order_info 
WHERE user_id = 100 
  AND order_status = 1 
  AND create_time > '2024-10-01';  -- 范围查询放最后

-- ❌ 不好的顺序
CREATE INDEX idx_uid_time_status ON order_info(user_id, create_time, order_status);
SELECT * FROM order_info 
WHERE user_id = 100 
  AND create_time > '2024-10-01'  -- 范围查询后，order_status索引失效
  AND order_status = 1;

死法5️⃣：OR条件中有未建索引的列

南北绿豆："这个坑很隐蔽！"

-- 表结构
CREATE TABLE user (
  id INT PRIMARY KEY,
  username VARCHAR(50),
  phone VARCHAR(11),
  email VARCHAR(100),
  INDEX idx_phone(phone)
  -- 注意：email没有索引
);

-- 阿西噶阿西的SQL
SELECT * FROM user 
WHERE phone = '13800138000' 
   OR email = 'test@example.com';

EXPLAIN分析：

EXPLAIN SELECT * FROM user 
WHERE phone = '13800138000' OR email = 'test@example.com'\G

type: ALL            -- 全表扫描
possible_keys: idx_phone
key: NULL            -- 索引失效
rows: 120000

为什么失效？

阿西噶阿西："因为OR的逻辑是'两个条件只要满足一个就返回'，MySQL的策略是："

查询逻辑：
1. 先用phone索引找到满足条件的行
2. 再全表扫描找email满足条件的行
3. 合并结果

既然email要全表扫描，那还不如直接全表扫描一次！

源码分析（sql/opt_range.cc）：

// MySQL的优化器逻辑
bool is_or_usable(SEL_ARG *key1, SEL_ARG *key2) {
  // 如果OR的任意一边没有索引，整个OR就不用索引
  if (!key1 || !key2) 
    return false;
  // 只有两边都有索引，才能合并使用
  return true;
}

解决方案：

方案1：给所有列加索引

CREATE INDEX idx_email ON user(email);

-- 再次EXPLAIN
EXPLAIN SELECT * FROM user 
WHERE phone = '13800138000' OR email = 'test@example.com'\G

type: index_merge   -- 索引合并
key: idx_phone,idx_email
Extra: Using union(idx_phone,idx_email); Using where
rows: 2

方案2：改写成UNION（推荐）

-- UNION会去重
SELECT * FROM user WHERE phone = '13800138000'
UNION
SELECT * FROM user WHERE email = 'test@example.com';

-- 如果确定不会重复，用UNION ALL（更快）
SELECT * FROM user WHERE phone = '13800138000'
UNION ALL
SELECT * FROM user WHERE email = 'test@example.com';

性能对比：

-- OR写法（全表扫描）
SELECT * FROM user WHERE phone = '13800138000' OR email = 'test@example.com';
-- 耗时：1.8秒，扫描12万行

-- UNION写法（走索引）
SELECT * FROM user WHERE phone = '13800138000'
UNION
SELECT * FROM user WHERE email = 'test@example.com';
-- 耗时：0.03秒，扫描2行

死法6️⃣：!=、<>、NOT IN 可能导致索引失效

哈吉米："我经常用 !=，这也有问题？"

南北绿豆："不是一定失效，要看情况！"

测试场景：

CREATE TABLE product (
  id INT PRIMARY KEY,
  status TINYINT,  -- 0:下架 1:上架
  category_id INT,
  INDEX idx_status(status)
);

-- 假设100万条数据，其中：
-- status=0 有1000条
-- status=1 有999000条

情况1：过滤掉少量数据

-- 查询上架商品（99.9%的数据）
SELECT * FROM product WHERE status != 0;

EXPLAIN:
type: ALL            -- 全表扫描
key: NULL            -- 不走索引
rows: 1000000

为什么不走索引？

MySQL的优化器会评估成本：

走索引方案：
1. 在索引树里找所有 status != 0 的行（需要扫描整棵树）
2. 回表查询完整数据（999000次回表）
成本：999000 + 树高度 ≈ 999003

全表扫描方案：
1. 顺序读取表数据（磁盘顺序IO很快）
成本：1000000 ÷ 页大小 ≈ 62500次IO

结论：全表扫描反而更快！

情况2：过滤掉大量数据

-- 查询下架商品（0.1%的数据）
SELECT * FROM product WHERE status != 1;

EXPLAIN:
type: range          -- 走索引
key: idx_status
rows: 1000

阿西噶阿西："所以 != 是否走索引，取决于过滤后的数据量！"

NOT IN的陷阱：

-- 场景：排除某些分类的商品
SELECT * FROM product WHERE category_id NOT IN (1, 2, 3);

EXPLAIN:
type: ALL            -- 通常不走索引

改写建议：

-- 方案1：如果可以，改成IN
SELECT * FROM product WHERE category_id IN (4, 5, 6, ..., 100);

-- 方案2：用NOT EXISTS（有时更好）
SELECT * FROM product p
WHERE NOT EXISTS (
  SELECT 1 FROM excluded_category e 
  WHERE e.id = p.category_id
);

-- 方案3：左连接 + NULL判断
SELECT p.* 
FROM product p
LEFT JOIN excluded_category e ON p.category_id = e.id
WHERE e.id IS NULL;

实战建议：

操作符是否走索引优化建议
!=、<>看数据分布如果过滤少量数据，加 FORCE INDEX 或改写SQL
NOT IN通常不走改成 NOT EXISTS 或 LEFT JOIN
IS NOT NULL看数据分布NULL值少的话会走索引
<、>✅ 走索引范围查询，正常走索引

死法7️⃣：字符集或排序规则不一致

阿西噶阿西："这个坑我踩过，超级隐蔽！"

场景重现：

-- 主表（UTF8MB4）
CREATE TABLE user (
  id INT PRIMARY KEY,
  username VARCHAR(50) COLLATE utf8mb4_general_ci,
  INDEX idx_username(username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 关联表（UTF8）
CREATE TABLE user_ext (
  user_id INT,
  nickname VARCHAR(50) COLLATE utf8_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 关联查询
SELECT u.*, e.nickname
FROM user u
JOIN user_ext e ON u.username = e.nickname;

EXPLAIN分析：

EXPLAIN SELECT u.*, e.nickname FROM user u JOIN user_ext e ON u.username = e.nickname\G

id: 1  table: u
type: ALL            -- user表全表扫描

id: 1  table: e
type: ALL            -- user_ext表也全表扫描
Extra: Using where; Using join buffer

为什么失效？

南北绿豆："字符集不同时，MySQL需要转换字符集再比较，相当于："

-- 等价于
ON CONVERT(u.username USING utf8) = e.nickname

在索引列上加了 CONVERT() 函数，索引直接废了！

解决方案：

-- 方案1：统一字符集（推荐）
ALTER TABLE user_ext CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 方案2：显式转换非索引列
SELECT u.*, e.nickname
FROM user u
JOIN user_ext e ON u.username = CONVERT(e.nickname USING utf8mb4);

排序规则陷阱：

-- 场景：区分大小写查询
CREATE TABLE product (
  id INT PRIMARY KEY,
  code VARCHAR(50) COLLATE utf8mb4_bin,  -- 区分大小写
  INDEX idx_code(code)
);

-- 查询时用了不区分大小写的比较
SELECT * FROM product WHERE code = 'ABC123' COLLATE utf8mb4_general_ci;

EXPLAIN:
type: ALL            -- 索引失效

修复方法：

-- 查询时保持一致
SELECT * FROM product WHERE code = 'ABC123' COLLATE utf8mb4_bin;
-- 或者直接
SELECT * FROM product WHERE code = 'ABC123';

死法8️⃣：IS NULL 和 IS NOT NULL 的特殊情况

哈吉米："我听说 IS NULL 不走索引？"

南北绿豆："这是老黄历了！MySQL 5.7+ 已经优化了，要分情况看。"

测试场景：

CREATE TABLE user (
  id INT PRIMARY KEY,
  phone VARCHAR(11),
  INDEX idx_phone(phone)
);

-- 假设100万用户，其中：
-- phone IS NULL 有500条
-- phone IS NOT NULL 有999500条

情况1：IS NULL（少量NULL值）

SELECT * FROM user WHERE phone IS NULL;

EXPLAIN:
type: ref            -- 走索引
key: idx_phone
key_len: 14          -- 包含了NULL标志位
rows: 500
Extra: Using index condition

为什么能走索引？

阿西噶阿西："InnoDB的索引可以存储NULL值，在B+树里排最前面："

索引树：
  NULL
  NULL
  NULL
  '13800138000'
  '13800138001'
  ...

情况2：IS NOT NULL（大量非NULL值）

SELECT * FROM user WHERE phone IS NOT NULL;

EXPLAIN:
type: ALL            -- 全表扫描
key: NULL
rows: 1000000

原因：跟 != 一样，过滤掉的数据太少，全表扫描更快。

实战建议：

-- 如果经常查询IS NOT NULL，可以这样优化：
ALTER TABLE user ADD COLUMN phone_exists TINYINT AS (phone IS NOT NULL) STORED;
CREATE INDEX idx_phone_exists ON user(phone_exists);

-- 查询时
SELECT * FROM user WHERE phone_exists = 1;

NULL值的其他陷阱：

-- COUNT的区别
SELECT COUNT(*) FROM user;          -- 统计所有行
SELECT COUNT(phone) FROM user;      -- 统计phone非NULL的行

-- 联合索引中的NULL
CREATE INDEX idx_uid_phone ON user(user_id, phone);
-- 如果user_id为NULL，整个联合索引对这行数据无效

死法9️⃣：ORDER BY 和 GROUP BY 导致的索引失效

南北绿豆："排序和分组也能让索引失效，而且很容易被忽略！"

场景1：ORDER BY 不走索引

CREATE TABLE order_info (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10,2),
  create_time DATETIME,
  INDEX idx_uid_time(user_id, create_time)
);

-- ❌ 错误写法
SELECT * FROM order_info 
WHERE user_id = 10086 
ORDER BY amount DESC;

EXPLAIN:
key: idx_uid_time        -- WHERE走了索引
Extra: Using filesort    -- 但ORDER BY需要额外排序！

为什么filesort？

哈吉米："因为索引是按 (user_id, create_time) 排序的，但你按 amount 排序，索引帮不上忙！"

优化方案：

-- 方案1：调整索引顺序
CREATE INDEX idx_uid_amount ON order_info(user_id, amount);

-- 方案2：如果create_time也需要，建联合索引
CREATE INDEX idx_uid_amount_time ON order_info(user_id, amount, create_time);

-- ✅ 正确写法
SELECT * FROM order_info 
WHERE user_id = 10086 
ORDER BY amount DESC;

EXPLAIN:
key: idx_uid_amount
Extra: Using index condition  -- 没有filesort了

场景2：多列排序的陷阱

-- ❌ 索引无法同时优化这个排序
SELECT * FROM order_info 
WHERE user_id = 10086 
ORDER BY create_time ASC, amount DESC;  -- 一个升序，一个降序

EXPLAIN:
Extra: Using filesort

MySQL 8.0的解决方案：

-- MySQL 8.0支持降序索引
CREATE INDEX idx_uid_time_amount ON order_info(
  user_id, 
  create_time ASC, 
  amount DESC
);

-- 现在能完美匹配排序需求
SELECT * FROM order_info 
WHERE user_id = 10086 
ORDER BY create_time ASC, amount DESC;

场景3：GROUP BY 不走索引

-- ❌ 错误写法
SELECT user_id, COUNT(*) 
FROM order_info 
GROUP BY user_id, DATE(create_time);  -- 又用了函数！

EXPLAIN:
Extra: Using temporary; Using filesort  -- 创建临时表+排序

✅ 正确写法：

-- 方案1：避免函数
ALTER TABLE order_info ADD COLUMN create_date DATE AS (DATE(create_time)) STORED;
CREATE INDEX idx_uid_date ON order_info(user_id, create_date);

SELECT user_id, COUNT(*) 
FROM order_info 
GROUP BY user_id, create_date;

-- 方案2：如果可以，用范围查询代替函数
SELECT user_id, COUNT(*) 
FROM order_info 
WHERE create_time >= '2024-10-01' 
  AND create_time < '2024-10-02'
GROUP BY user_id;

死法🔟：索引选择性太低（重复值太多）

阿西噶阿西："最后一个坑：不是所有列都适合建索引！"

什么是索引选择性？

索引选择性 = 不重复的值数量 / 总行数

示例：
- 性别字段：2 / 1000000 = 0.0002%（选择性极低）
- 手机号：999000 / 1000000 = 99.9%（选择性极高）

测试场景：

CREATE TABLE user (
  id INT PRIMARY KEY,
  gender TINYINT,  -- 0:女 1:男
  INDEX idx_gender(gender)
);

-- 100万用户，男女各50万
SELECT * FROM user WHERE gender = 1;

EXPLAIN:
type: ALL            -- 全表扫描
possible_keys: idx_gender
key: NULL            -- 优化器选择不用索引
rows: 1000000

为什么不用索引？

南北绿豆："假设走索引："

1. 在索引树里找所有gender=1的行（50万行）
2. 回表查询完整数据（50万次随机IO）
成本：50万次随机IO

全表扫描：
1. 顺序读取表数据（顺序IO）
成本：约3万次顺序IO

结论：全表扫描快得多！

解决方案：

方案1：组合高选择性列

-- 单独gender索引无用，但组合其他列就有用了
CREATE INDEX idx_gender_age_city ON user(gender, age, city_id);

-- 这样查询能走索引
SELECT * FROM user 
WHERE gender = 1 
  AND age BETWEEN 25 AND 35 
  AND city_id = 110000;

方案2：使用覆盖索引

-- 如果只需要少量列，可以用覆盖索引
CREATE INDEX idx_gender_id_name ON user(gender, id, username);

-- 这个查询不需要回表
SELECT id, username FROM user WHERE gender = 1;

EXPLAIN:
type: ref
key: idx_gender_id_name
Extra: Using index  -- 覆盖索引，不回表

如何评估是否该建索引？

-- 计算选择性
SELECT 
  COUNT(DISTINCT gender) / COUNT(*) AS selectivity
FROM user;

-- 结果：0.0002（太低，不建议单独建索引）

-- 对比手机号
SELECT 
  COUNT(DISTINCT phone) / COUNT(*) AS selectivity
FROM user;

-- 结果：0.999（很高，适合建索引）

经验值：

  • 选择性 > 0.1（10%）：建议建索引
  • 选择性 < 0.01（1%）：不建议单独建索引，可以组合其他列
  • 选择性 ≈ 1：非常适合建索引（如主键、唯一键）

🎯 手写一个索引选择模拟器

哈吉米："听了这么多，我想自己实现一个简单的索引选择器，理解MySQL的决策逻辑！"

南北绿豆："好想法！来写个100行的迷你版。"

/**
 * MySQL索引选择模拟器
 * 模拟优化器评估是否使用索引的决策过程
 */
public class IndexSelectorSimulator {
    
    // 表统计信息
    static class TableStats {
        long totalRows;              // 总行数
        int avgRowSize;              // 平均行大小（字节）
        int pageSize = 16384;        // InnoDB页大小16KB
        
        public long estimateFullScanCost() {
            // 全表扫描成本 = 总页数
            return (totalRows * avgRowSize) / pageSize;
        }
    }
    
    // 索引统计信息
    static class IndexStats {
        String indexName;
        int treeHeight;              // B+树高度
        double selectivity;          // 选择性
        long distinctValues;         // 不重复值数量
        
        public long estimateIndexScanCost(long matchedRows, TableStats tableStats) {
            // 索引扫描成本 = 树高度 + 匹配行数 + 回表成本
            long treeReadCost = treeHeight;
            long indexScanCost = matchedRows;
            long tableAccessCost = matchedRows; // 回表需要随机IO
            
            return treeReadCost + indexScanCost + tableAccessCost;
        }
    }
    
    // 查询条件
    static class QueryCondition {
        String columnName;
        String operator;             // =, >, <, LIKE, IN等
        Object value;
        boolean hasFunction;         // 是否用了函数
        
        public boolean canUseIndex() {
            // 判断条件是否能用索引
            if (hasFunction) return false;
            if ("!=".equals(operator) || "NOT IN".equals(operator)) return false;
            return true;
        }
    }
    
    /**
     * 核心方法：决定是否使用索引
     */
    public static boolean shouldUseIndex(
        TableStats tableStats, 
        IndexStats indexStats,
        QueryCondition condition
    ) {
        System.out.println("===== 索引选择分析 =====");
        
        // 步骤1：检查条件是否能用索引
        if (!condition.canUseIndex()) {
            System.out.println("❌ 条件无法使用索引（函数/NOT IN等）");
            return false;
        }
        
        // 步骤2：估算匹配行数
        long matchedRows = estimateMatchedRows(tableStats, indexStats, condition);
        System.out.println("📊 预估匹配行数: " + matchedRows);
        
        // 步骤3：计算全表扫描成本
        long fullScanCost = tableStats.estimateFullScanCost();
        System.out.println("💰 全表扫描成本: " + fullScanCost);
        
        // 步骤4：计算索引扫描成本
        long indexScanCost = indexStats.estimateIndexScanCost(matchedRows, tableStats);
        System.out.println("💰 索引扫描成本: " + indexScanCost);
        
        // 步骤5：比较成本
        boolean useIndex = indexScanCost < fullScanCost;
        System.out.println(useIndex ? "✅ 选择使用索引" : "❌ 选择全表扫描");
        System.out.println("成本比: " + String.format("%.2f%%", 
            (double)indexScanCost / fullScanCost * 100));
        
        return useIndex;
    }
    
    /**
     * 估算匹配行数
     */
    private static long estimateMatchedRows(
        TableStats tableStats,
        IndexStats indexStats,
        QueryCondition condition
    ) {
        switch (condition.operator) {
            case "=":
                // 等值查询：总行数 / 不重复值数量
                return tableStats.totalRows / indexStats.distinctValues;
            
            case ">":
            case "<":
                // 范围查询：粗略估算30%
                return (long) (tableStats.totalRows * 0.3);
            
            case "LIKE":
                String pattern = (String) condition.value;
                if (pattern.startsWith("%")) {
                    // 前缀模糊，无法用索引
                    return tableStats.totalRows;
                } else {
                    // 后缀模糊，估算10%
                    return (long) (tableStats.totalRows * 0.1);
                }
            
            case "IN":
                // IN条件：假设IN里有N个值
                return tableStats.totalRows / indexStats.distinctValues * 5; // 假设5个值
            
            default:
                return tableStats.totalRows;
        }
    }
    
    /**
     * 测试用例
     */
    public static void main(String[] args) {
        // 场景1：高选择性索引 + 等值查询
        System.out.println("\n【场景1】查询手机号 = '13800138000'");
        TableStats table1 = new TableStats();
        table1.totalRows = 1000000;
        table1.avgRowSize = 200;
        
        IndexStats phoneIndex = new IndexStats();
        phoneIndex.indexName = "idx_phone";
        phoneIndex.treeHeight = 4;
        phoneIndex.selectivity = 0.999;
        phoneIndex.distinctValues = 999000;
        
        QueryCondition cond1 = new QueryCondition();
        cond1.columnName = "phone";
        cond1.operator = "=";
        cond1.value = "13800138000";
        cond1.hasFunction = false;
        
        shouldUseIndex(table1, phoneIndex, cond1);
        
        
        // 场景2：低选择性索引（性别）
        System.out.println("\n【场景2】查询性别 = 1");
        IndexStats genderIndex = new IndexStats();
        genderIndex.indexName = "idx_gender";
        genderIndex.treeHeight = 3;
        genderIndex.selectivity = 0.0002;
        genderIndex.distinctValues = 2;
        
        QueryCondition cond2 = new QueryCondition();
        cond2.columnName = "gender";
        cond2.operator = "=";
        cond2.value = 1;
        cond2.hasFunction = false;
        
        shouldUseIndex(table1, genderIndex, cond2);
        
        
        // 场景3：使用了函数
        System.out.println("\n【场景3】DATE(create_time) = '2024-10-01'");
        IndexStats timeIndex = new IndexStats();
        timeIndex.indexName = "idx_create_time";
        timeIndex.treeHeight = 4;
        timeIndex.selectivity = 0.8;
        timeIndex.distinctValues = 800000;
        
        QueryCondition cond3 = new QueryCondition();
        cond3.columnName = "create_time";
        cond3.operator = "=";
        cond3.value = "2024-10-01";
        cond3.hasFunction = true;  // 用了DATE()函数
        
        shouldUseIndex(table1, timeIndex, cond3);
    }
}

运行结果：

【场景1】查询手机号 = '13800138000'
===== 索引选择分析 =====
📊 预估匹配行数: 1
💰 全表扫描成本: 12195
💰 索引扫描成本: 6
✅ 选择使用索引
成本比: 0.05%

【场景2】查询性别 = 1
===== 索引选择分析 =====
📊 预估匹配行数: 500000
💰 全表扫描成本: 12195
💰 索引扫描成本: 500003
❌ 选择全表扫描
成本比: 4098.39%

【场景3】DATE(create_time) = '2024-10-01'
===== 索引选择分析 =====
❌ 条件无法使用索引（函数/NOT IN等）

阿西噶阿西："这个模拟器清晰地展示了MySQL的决策逻辑！"

📊 索引失效总结表

失效场景错误示例正确写法核心原因
1️⃣ 函数计算WHERE DATE(time) = '2024-10-01'WHERE time >= '2024-10-01' AND time < '2024-10-02'破坏索引有序性
2️⃣ 类型转换WHERE phone = 13800138000（phone是VARCHAR）WHERE phone = '13800138000'字段被隐式转换
3️⃣ 前缀模糊WHERE name LIKE '%手机'WHERE name LIKE '手机%' 或用ES无法利用前缀有序性
4️⃣ 跳过最左列联合索引(a,b,c)，查WHERE b=1WHERE a=1 AND b=1不符合最左前缀原则
5️⃣ OR未全索引WHERE phone='x' OR email='y'（email无索引）给email加索引 或 改UNIONOR任一边无索引则全废
6️⃣ !=过滤少WHERE status != 0（99%数据不是0）改业务逻辑 或 FORCE INDEX优化器认为全表扫描更快
7️⃣ 字符集不同关联列字符集utf8 vs utf8mb4统一字符集需要字符集转换
8️⃣ IS NOT NULLWHERE phone IS NOT NULL（大量非NULL）加计算列 或 接受全表扫描过滤数据太少
9️⃣ ORDER BY不匹配索引(a,b)，ORDER BY c调整索引顺序排序列不在索引中
🔟 选择性太低性别字段单独建索引组合其他列 或 用覆盖索引回表成本大于全表扫描

🔍 如何排查索引问题？

南北绿豆："记住这个排查流程："

步骤1：开启慢查询日志

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒的查询记录下来
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询

步骤2：分析EXPLAIN

EXPLAIN SELECT * FROM user WHERE phone = 13800138000\G

重点关注：
- type: ALL（全表扫描） < index（索引扫描） < range（范围） < ref（等值） < const（常量）
- possible_keys: 可能用到的索引
- key: 实际用到的索引
- rows: 扫描行数
- Extra: 
    - Using filesort（额外排序，慢）
    - Using temporary（创建临时表，慢）
    - Using index（覆盖索引，快）
    - Using where（在存储引擎层过滤，快）

步骤3：使用OPTIMIZER_TRACE

-- 开启优化器追踪
SET optimizer_trace="enabled=on";

-- 执行查询
SELECT * FROM user WHERE phone = 13800138000;

-- 查看优化器决策过程
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

-- 关键信息：
-- 1. considered_execution_plans: 考虑过的执行计划
-- 2. rows_estimation: 行数估算
-- 3. cost_for_plan: 每个计划的成本
-- 4. chosen: 最终选择的计划

步骤4：强制使用索引（临时方案）

-- 如果优化器选错了，可以强制使用
SELECT * FROM user FORCE INDEX(idx_phone) WHERE phone = '13800138000';

-- 或者禁止某个索引
SELECT * FROM user IGNORE INDEX(idx_name) WHERE phone = '13800138000';

💡 索引优化的最佳实践

哈吉米："总结一下，日常开发该怎么避坑？"

阿西噶阿西："记住这几条铁律："

✅ 建索引的原则

  1. WHERE、JOIN、ORDER BY的列优先考虑
  2. 选择性高的列（不重复值多）
  3. 频繁查询的列组合
  4. 字符串列尽量用前缀索引 
    CREATE INDEX idx_email ON user(email(10));  -- 只索引前10个字符
  5. 控制索引数量（单表建议不超过5个）

✅ 写SQL的原则

  1. 不在索引列上用函数，改写成范围查询
  2. 字符串加引号，避免类型转换
  3. 能用等值就别用范围，能用范围就别用模糊
  4. 联合索引按最左前缀写WHERE
  5. OR改UNION，NOT IN改NOT EXISTS

✅ 监控和优化

  1. 定期查看慢查询日志
  2. 用pt-query-digest分析慢查询
  3. 监控索引使用情况 
    -- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引统计
SHOW INDEX FROM user;
  4. 定期ANALYZE TABLE更新统计信息

🎓 思考题

南北绿豆："最后留几个思考题，检验下你的理解："

题目1：这个SQL会走索引吗？

CREATE TABLE order_info (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10,2),
  status TINYINT,
  create_time DATETIME,
  INDEX idx_uid_status_time(user_id, status, create_time)
);

-- 问题：这个查询会走索引的哪些列？
SELECT * FROM order_info
WHERE user_id = 10086
  AND create_time > '2024-10-01'
  AND status = 1;

答案：会走索引，但只用到了 user_id 和 status 两列。

原因：虽然SQL写的顺序是 user_id -> create_time -> status，但MySQL的优化器会自动调整成索引顺序 user_id -> status -> create_time。不过，status 是等值查询，create_time 是范围查询，所以三列都能用上！

更正：如果SQL是这样：

WHERE user_id = 10086
  AND create_time > '2024-10-01';  -- 跳过了status

那就只用到 user_id，create_time 用不上（违反最左前缀）。

题目2：为什么这个COUNT很慢？

-- 表有500万数据
SELECT COUNT(*) FROM user WHERE status = 1;
-- 耗时3秒

-- 但这个很快
SELECT COUNT(*) FROM user;
-- 耗时0.01秒

答案：

  1. COUNT(*)：InnoDB维护了表的总行数（近似值），直接返回，超快
  2. COUNT(*) WHERE ...：必须扫描索引或表，统计满足条件的行数

优化方案：

  • 如果status值固定（如0/1），可以用计数表维护每个状态的数量
  • 或者用Redis缓存计数
  • 或者接受近似值，用 EXPLAIN 的 rows 估算

题目3：手写一个SQL，同时避免"死法1"和"死法4"

-- 错误写法（同时犯了两个错误）
SELECT * FROM order_info
WHERE DATE(create_time) = '2024-10-01'  -- 死法1：函数
  AND status = 1;                        -- 假设索引是(create_time, status)，但DATE()破坏了

-- 正确写法
-- 索引：INDEX idx_time_status(create_time, status)
SELECT * FROM order_info
WHERE create_time >= '2024-10-01 00:00:00'   -- 改范围查询
  AND create_time < '2024-10-02 00:00:00'
  AND status = 1;                             -- 遵循最左前缀

🎉 结束语

凌晨3点，三人在会议室里击掌庆祝。

哈吉米："卧槽，索引的坑这么多！以后写SQL得小心了。"

阿西噶阿西："记住一句话：索引是给'有序查找'加速的，任何破坏有序性的操作都会让索引失效。"

南北绿豆："对了哈吉米，你那个翻车的SQL改了吗？"

哈吉米："改了改了！从 DATE(create_time) 改成范围查询，查询时间从8秒降到0.1秒，CPU也降到10%了！"

南北绿豆："这就是理解原理的力量！以后遇到慢查询，先EXPLAIN，再对照这10种死法排查。"

阿西噶阿西："哥几个，下次咱们聊聊'死锁'？我上周又踩坑了……"

哈吉米："别，我先缓缓……"

记住这个口诀：

函数类型别乱用，前缀模糊改ES搜
最左原则要遵守，OR条件两边都要有
不等过滤看数据，字符集要统一
NULL值能走但看量，排序分组要匹配
选择性低别单建，组合起来效果好

希望这篇文章能帮你避开索引失效的10种死法！如果觉得有帮助，记得收藏+点赞，下次慢查询时翻出来救命！

有问题欢迎在评论区讨论，咱们一起踩坑一起成长！💪

avatar
文章
阅读
粉丝