MySQL迁移至ElasticSearch的索引设计与关联查询咨询

表结构说明 #

Table A   Table B   Table C 
id         id        id       
           FK_A                  
           FK_C              

表B为A与C的中间关联表，通过FK_A关联表A的id，FK_C关联表C的id。

疑问解答 #

1. 数据迁移时，关联查询结果存单个索引还是两个独立索引？ #

• 若业务核心需求仅为通过C的id快速查询对应的A的id，建议直接给表B建立(FK_C, FK_A)的复合索引，或创建专门的映射表存储C与A的关联关系，这样能避免多表关联的性能开销，查询效率最高。
• 若存在其他业务场景（比如通过A的id反向查C的id，或单独查询A/C表的其他字段），则保留A、C各自的主键索引，同时给表B建立(FK_C, FK_A)复合索引即可，这种方式兼顾多种需求，不会产生冗余数据。

2. 两个独立索引能否通过索引关联得到预期结果？ #

可以，但性能不如复合索引。给表B的FK_C和FK_A分别建立独立索引后，MySQL执行关联查询时会尝试使用这两个索引，但需要进行索引合并或回表操作，数据量较大时（比如你提到的1万+数据），回表会带来额外IO开销，查询效率会下降。

关联查询仅返回1条数据的问题解决 #

你大概率是查询语句存在错误，以下是正确的查询语句示例：

SELECT DISTINCT a.id AS ids_from_tableA
FROM Table_C c
JOIN Table_B b ON c.id = b.FK_C
JOIN Table_A a ON b.FK_A = a.id
WHERE c.id IN (/* 传入请求的ids_from_tableC列表 */);

同时排查以下几点：

• 确认WHERE条件是否正确传入了所有C的id，是否存在仅传入单个id的情况
• 检查表B中的关联数据是否完整，是否存在大部分A、C关联关系缺失的情况
• 避免误用GROUP BY或LIMIT 1，这类语句会截断结果集
• 加DISTINCT是为了避免同一个A的id因关联多个C而重复返回

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