升级MySQL 8.0.28后user_products表索引异常，查询性能波动

问题核心 #

升级到MySQL 8.0.28后，user_products表的查询在product_to='49'时耗时骤增（5秒vs其他值的0.007秒），EXPLAIN显示优化器错误选择了productId索引，而非更匹配WHERE条件的索引，强制指定索引后性能恢复。

可能原因 #

1. 统计信息过时/不准确：升级后表的统计信息未更新，8.0优化器依赖的新统计逻辑（比如直方图）没有正确生成，导致对product_to='49'的数据分布判断偏差，选错索引。
2. 索引选择性判断差异：5.7与8.0的优化器对索引选择性的计算逻辑有变化，针对product_to='49'这个特定值，优化器误判productId索引的查询成本更低。
3. 数据分布倾斜：product_to='49'对应的数据行数远多于其他值，优化器的成本模型计算失误，选择了不适合处理大结果集的索引。

解决方案 #

1. 强制更新表统计信息

先执行统计信息更新，让优化器获取最新的数据分布：

ANALYZE TABLE user_products;

这是最常见的解决方法，升级后未更新统计信息是这类问题的高发原因。

2. 创建针对性复合索引

当前查询的过滤条件是status=0、product_to='xxx'、delivery_date<=CURDATE()，还要按id降序排序，创建覆盖这些条件的复合索引可以彻底解决索引选择问题：

CREATE INDEX idx_up_status_productto_delivery_id ON user_products(status, product_to, delivery_date, id);

这个索引可以让查询直接通过索引完成过滤和排序，无需回表或额外排序操作，性能最优。

3. 临时应急：FORCE INDEX指定正确索引

如果更新统计信息后问题仍存在，可以临时在查询中强制使用匹配WHERE条件的索引（比如product_to或product_multicol索引）：

SELECT user_products.id,user_products.status,user_products.delivery_date,user_products.product_to,user_products.category_id,users.username,user_groups.name 
FROM user_products FORCE INDEX (product_to)
LEFT JOIN category ON category.id = user_products.category_id
LEFT JOIN users ON user_products.user_id = users.id
LEFT JOIN user_groups ON user_groups.id = users.user_group_id
WHERE user_products.status = 0 AND user_products.product_to = "49" AND user_products.delivery_date <= CURDATE() 
ORDER BY user_products.id DESC;

注意这是临时方案，优先通过更新统计信息或创建合适索引让优化器自动选择。

4. 检查并生成直方图

MySQL 8.0引入了直方图统计来优化字符串等字段的查询计划，若product_to字段的直方图缺失，执行以下命令生成：

ANALYZE TABLE user_products UPDATE HISTOGRAM ON product_to;

可以通过以下语句查看直方图是否存在：

SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS 
WHERE TABLE_NAME='user_products' AND COLUMN_NAME='product_to';

额外排查点 #

• 查看product_to='49'的行数：SELECT COUNT(*) FROM user_products WHERE product_to='49';，如果行数远多于其他值，说明数据分布倾斜，复合索引的作用会更明显。
• 检查优化器参数：查看optimizer_switch的设置，确认是否开启了可能影响索引选择的新选项（比如condition_fanout_filter等），必要时调整参数验证。

内容的提问来源于stack exchange，提问作者tafsir