跨库查询中TB_MBR_INFO表索引未生效的原因排查请求

核心现象 #

跨库查询时，依赖子查询无法正常使用TB_MBR_INFO_MBR_ID_IDX索引（执行计划中possible_keys为空，走index类型扫描），但同库查询时索引能以ref类型正常生效，且表结构、索引定义完全一致。

可能原因及对应解决方案 #

1. 字符集/排序规则不匹配（最常见） #

跨库时，TB_CHAT_ROOM.seller/buyer与TB_MBR_INFO.MBR_ID的字符集或排序规则可能存在差异，触发隐式类型转换，导致索引无法被精准匹配，只能退化为索引全扫描。

验证方法：

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA IN ('database1', 'database2') 
  AND TABLE_NAME IN ('TB_CHAT_ROOM', 'TB_MBR_INFO') 
  AND COLUMN_NAME IN ('MBR_ID', 'seller', 'buyer');

解决方法：
将两个表的对应字段字符集和排序规则统一，例如：

-- 修改database1.TB_CHAT_ROOM的seller/buyer字段
ALTER TABLE database1.TB_CHAT_ROOM 
MODIFY COLUMN seller VARCHAR(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE database1.TB_CHAT_ROOM 
MODIFY COLUMN buyer VARCHAR(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 跨库依赖子查询的优化器限制 #

MySQL优化器在处理跨库的DEPENDENT SUBQUERY时，可能无法正确解析跨库表之间的关联关系，导致无法生成ref类型的索引查找计划，只能选择索引扫描。

解决方法：改用LEFT JOIN替代依赖子查询，优化器对JOIN的跨库关联支持更友好：

SELECT 
    A.regdate AS REG_DT,
    S.MBR_NO AS SELL_MBR_NO,
    B.MBR_NO AS PRCH_MBR_NO
FROM database1.TB_CHAT_ROOM A
LEFT JOIN database2.TB_MBR_INFO S USE INDEX (TB_MBR_INFO_MBR_ID_IDX) 
    ON S.MBR_ID = A.seller
LEFT JOIN database2.TB_MBR_INFO B USE INDEX (TB_MBR_INFO_MBR_ID_IDX) 
    ON B.MBR_ID = A.buyer;

3. 表统计信息不准确 #

跨库场景下，MySQL可能无法自动同步或获取到另一库表的最新统计信息，优化器基于过时的统计数据，错误判断索引查找的成本高于索引扫描。

解决方法：手动更新两个库的表统计信息：

ANALYZE TABLE database1.TB_CHAT_ROOM;
ANALYZE TABLE database2.TB_MBR_INFO;

4. MySQL版本限制 #

旧版本MySQL（如5.7早期版本）对跨库查询的优化逻辑存在缺陷，升级到8.0及以上稳定版本，可获得更好的跨库查询优化支持。

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