为products表user_id创建索引后，select *查询不生效的问题排查

• 观察到的索引不触发并非索引失效的异常问题，是MySQL优化器基于查询成本评估后的正常决策。
• 你的products表共2395288条记录，其中user_id=4的记录有765277条，占全表比例超过31%，远高于MySQL优化器选择二级索引的常规阈值（通常为10%~30%区间，匹配行占比越高，优化器越倾向选择全表扫描）。
• 成本逻辑拆解：你创建的userid是普通二级索引，仅存储user_id字段值与对应聚簇索引的主键值。执行select *查询name、description这类不在二级索引范围内的字段时，每匹配到一条user_id=4的记录，都需要拿着主键值回到存储全量行数据的聚簇索引回查对应字段。当匹配行规模足够大时，「二级索引扫描+大规模回表」的总耗时会高于直接顺序扫描聚簇索引的全表扫描耗时，优化器自然会放弃使用二级索引。
• 你测试时发现count(*)、仅查询user_id字段时索引可以正常生效，原因是这类查询不需要回表：二级索引本身已经包含了查询所需的全部数据，且二级索引的体积远小于聚簇索引，扫描成本更低，因此优化器会优先选择。

• 方案1：创建覆盖索引（生产环境推荐）
  将按user_id过滤时业务需要返回的字段直接加入索引，构建无需回表的覆盖索引，参考创建语句：
  CREATE INDEX idx_userid_biz ON products(user_id, name, description)
  该索引结构已经包含了查询过滤条件、以及业务需要返回的所有字段，扫描时不需要额外回表，总成本远低于全表扫描，优化器会自动触发使用。
  注意：如果业务需要返回整行的所有字段，不建议将全部字段加入索引，否则会导致索引体积过大，拖慢数据写入性能。
• 方案2：强制指定索引（不建议长期业务使用）
  如果确认优化器的成本评估结果不符合实际性能预期，可以在查询语句中使用FORCE INDEX语法强制指定使用对应索引，参考写法：
  SELECT * FROM products FORCE INDEX(userid) WHERE user_id =4;
  该方式的弊端是，后续表数据分布发生变化后，强制走索引反而可能带来更差的性能，仅适合临时排查问题使用。
• 方案3：针对倾斜数据做业务层优化
  你的表存在明显的数据倾斜：单个用户的记录占全表近1/3，这类场景下哪怕索引可以正常生效，一次性查询70多万行数据的回表操作本身性能也不会理想。可以从业务侧调整：
  • 给按user_id的查询增加强制分页限制，避免一次性扫描几十万行数据
  • 定期执行ANALYZE TABLE products更新表的统计信息，避免优化器因为统计数据过期错判查询成本
  • 对数据量过大的用户做数据拆分，降低单用户的记录占比，让索引的成本评估更准确

内容的提问来源于stack exchange，提问作者Juan Pablo B