面试题：MySQL JOIN优化之复杂场景下索引的构建与运用

Question

假设有三个表A、B、C，A表有字段id_a、name_a、category_a，B表有字段id_b、name_b、id_a_fk（关联A表的id_a），C表有字段id_c、name_c、id_b_fk（关联B表的id_b）。现在要查询category_a为'特定分类'且name_c包含'特定字符串'的数据，应该如何构建索引以及编写JOIN查询语句来实现高效查询，并解释这样做的原因。

Answer 1

构建索引

1. 在表A的category_a字段上创建索引：

   CREATE INDEX idx_a_category_a ON A(category_a);
   

   原因：此索引可以加速对表A中category_a为'特定分类'的行的查找，在执行JOIN操作前能快速定位到符合条件的A表记录。
2. 在表B的id_a_fk字段上创建索引：

   CREATE INDEX idx_b_id_a_fk ON B(id_a_fk);
   

   原因：表B通过id_a_fk与表A关联，此索引有助于快速连接表A和表B，在找到表A中符合category_a条件的记录后，能快速定位到表B中与之关联的记录。
3. 在表C的name_c字段上创建索引：

   CREATE INDEX idx_c_name_c ON C(name_c);
   

   原因：此索引可加速对表C中name_c包含'特定字符串'的行的查找，提升查询效率。
4. 在表C的id_b_fk字段上创建索引：

   CREATE INDEX idx_c_id_b_fk ON C(id_b_fk);
   

   原因：表C通过id_b_fk与表B关联，此索引有助于快速连接表B和表C，提高JOIN操作的效率。

JOIN查询语句

SELECT A.id_a, A.name_a, A.category_a, B.id_b, B.name_b, C.id_c, C.name_c
FROM A
JOIN B ON A.id_a = B.id_a_fk
JOIN C ON B.id_b = C.id_b_fk
WHERE A.category_a = '特定分类' AND C.name_c LIKE '%特定字符串%';

原因解释

1. JOIN操作：通过JOIN将表A、B、C连接起来，先基于id_a连接表A和表B，再基于id_b连接表B和表C，从而构建起三表之间的关系，获取所需的所有字段。
2. WHERE条件：A.category_a = '特定分类'筛选出表A中符合分类条件的记录，C.name_c LIKE '%特定字符串%'筛选出表C中name_c包含特定字符串的记录。通过合理构建索引和JOIN查询，能利用索引快速定位数据，减少全表扫描，从而实现高效查询。