MySQL 索引失效案例：为什么你的 GROUP BY 没有走索引？

晟盾科技

于 2025-02-14 15:54:37 发布

阅读量802

点赞数 19

分类专栏：后端开发文章标签： mysql 数据库 sql database 数据库开发

本文链接：https://blog.youkuaiyun.com/SDTechnology/article/details/145634797

版权

后端开发专栏收录该内容

47 篇文章

订阅专栏

3.1 最优索引调整方案-调整索引顺序（见效最快）

一、问题现象描述

在日常开发中，我们经常遇到这样的情况：明明在相关字段上建立了索引，但执行包含 GROUP BY 的查询时，通过 EXPLAIN 查看执行计划却发现没有走索引。

1.1 创建测试表

CREATE TABLE order_record (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  amount DECIMAL(10,2),
  create_time DATETIME
) ENGINE=InnoDB;

1.2 插入测试数据

-- 快速生成测试数据的存储过程
DELIMITER $$
CREATE PROCEDURE generate_test_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 10000 DO
    INSERT INTO order_record(user_id, product_id, amount, create_time)
    VALUES (
      FLOOR(RAND()*1000),
      FLOOR(RAND()*100),
      RAND()*1000,
      NOW() - INTERVAL FLOOR(RAND()*365*3) DAY
    );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL generate_test_data();

1.3 创建索引

ALTER TABLE order_record ADD INDEX idx_user_product (user_id, product_id);

1.4 测试查询

EXPLAIN 
SELECT user_id, COUNT(*) 
FROM order_record 
GROUP BY user_id;

执行结果展示：显示 Using index 说明走了索引

| id | select_type | table        | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra       |
|  1 | SIMPLE      | order_record | NULL       | index | idx_user_product | idx_user_product | 8       | NULL | 9852|   100.00 | Using index |

但当增加条件时：

EXPLAIN 
SELECT user_id, COUNT(*)
FROM order_record
WHERE product_id = 5
GROUP BY user_id;

执行计划：我们会发现“Using where; Using temporary”Using where 可能走索引，但 Using temporary 一般意味着未高效使用索引。二者同时出现时往往需要优化（具体要看type和key字段的真实表现），当type=ALL且key=NULL时，说明索引完全失效。通过调整索引字段顺序配合查询优化

| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
|  1 | SIMPLE      | order_record | NULL       | ALL  | idx_user_product     | NULL | NULL    | NULL | 9852|    10.00 | Using where; Using temporary   |

二、关键原因分析

2.1 组合索引的最左匹配原则

如果 GROUP BY 的字段不是复合索引的第一个字段，执行引擎将无法有效使用索引。例如当索引是(user_id, product_id)时：

EXPLAIN 
SELECT product_id, COUNT(*)
FROM order_record
GROUP BY product_id;

执行计划必然显示全表扫描，因为 product_id 不在索引的首位

2.2 数据分布的临界值

当需要分组的数据量超过总数据量的 30%时，优化器可能认为全表扫描效率更高

2.3 临时表的使用

当存在非索引字段的聚合操作时（特别是内存临时表放不下时会转磁盘临时表），会影响执行引擎的选择

三、解决方案实践

3.1 最优索引调整方案-调整索引顺序（见效最快）

原理：把 WHERE 条件中的字段（product_id）放到索引的最前面

操作步骤：

-- 1. 先删掉原来的索引
ALTER TABLE order_record DROP INDEX idx_user_product;
-- 2. 新建"产品-用户"组合索引（顺序很重要！）
ALTER TABLE order_record ADD INDEX idx_product_user (product_id, user_id);

效果：

就像一个整理过的药箱，药品按【药品种类→用户】分类
当你需要 "统计买过维生素 C的用户" 时，直接打开"维生素 C"抽屉就能完成统计

查询速度：⚡ 提速 10 倍+

3.2不修改索引

适用场景：不能动数据库结构时

优化方法：

-- 方法 1：强制走现有索引 + 禁止隐式排序
SELECT user_id, COUNT(*) 
FROM order_record FORCE INDEX(idx_user_product) -- 强行指定索引
WHERE product_id = 5
GROUP BY user_id
ORDER BY NULL;  -- 加上这句取消自动排序
-- 方法 2：拆分查询两步走（先生成临时数据集）
SELECT user_id, COUNT(*) 
FROM (
  SELECT user_id      -- 子查询优先过滤数据
  FROM order_record 
  WHERE product_id = 5 
) AS temp
GROUP BY user_id;

效果类似：

就像在一本按【用户→药品】登记的记录本里
先列所有用户清单，再逐个翻找谁买过维生素 C
速度提升有限 🐢（约提速 2-3 倍）

四、最佳实践建议

1. 索引字段顺序原则

口诀：查谁放前面

原理：把WHERE后的条件字段作为索引第一个字段

-- 🚫 错误示范：索引顺序是(user_id, product_id)
SELECT ... WHERE product_id=5 → 索引失效!
-- ✅ 正确操作：调整为(product_id 在前)
ALTER TABLE 订单表 ADD INDEX (product_id, user_id);

场景类比：

就像整理快递柜，应该先按【小区楼栋】分大类，再按【房间号】细分。如果反过来，找某栋楼所有快递就得翻遍所有柜子。

2.全覆盖索引设计

口诀：查啥就存啥

操作：直接把查询需要的字段都放进索引

-- 创建"包打听"索引（包含查询所有需要的字段）
ALTER TABLE 订单表 ADD INDEX 超级索引 
(product_id, user_id, amount, create_time);

五、总结建议

通过合理的设计索引和使用方法，90%的 GROUP BY 性能问题都能得到有效解决。

使用 EXPLAIN 分析执行计划
检查索引是否符合最左匹配原则
使用覆盖索引减少回表
必要时使用 FORCE INDEX 提示
定期进行慢查询分析