多表关联SQL查询执行超1秒，如何优化索引提升性能？

首先，咱们先明确你的核心需求：根据特定driver_licence_id，获取该用户名下所有车辆对应的部件厂商ID的拼接字符串。原查询功能没问题，但在百万级数据量下耗时超1秒，需要通过精准的索引优化来提速。

原查询SQL #

SELECT GROUP_CONCAT(p.manufacturers_id ORDER BY p.manufacturers_id) as mids 
FROM car c 
INNER JOIN parts_in_car pic ON c.car_id = pic.car_id 
JOIN parts p ON pic.part_id = p.part_id 
JOIN customers cus ON c.cus_id = cus.cus_id 
WHERE cus.driver_licence_id = 5555555 
GROUP BY c.car_id, c.date_created 
ORDER BY c.date_created

你已尝试创建的索引 #

-- Customer表
CREATE INDEX customer_driver_licence_id_idx ON customer (driver_licence_id);
-- cars表
CREATE INDEX cars_cus_id_idx ON cars (cus_id);
-- parts表
CREATE INDEX parts_manufacturers_id_idx ON parts (manufacturers_id);
-- parts_in_car表
CREATE INDEX parts_in_car_part_id_idx ON parts_in_car (part_id);
CREATE INDEX parts_in_car_car_id_idx ON parts_in_car (car_id);

当前瓶颈与EXPLAIN结果 #

你已经定位到问题出在GROUP BY操作，并且尝试创建了(car_id, date_added)索引，当前EXPLAIN结果如下：

+-------+-------------------------------------+
| table | key                                 |
+-------+-------------------------------------+
| a     | cus_id                              |
| o     | cars_cus_id_car_id_date_created_idx |
| pip   | parts_in_car_car_id_idx             |
| p     | PRIMARY                             |
+-------+-------------------------------------+

针对性索引优化建议 #

结合你的查询逻辑和现有索引情况，我推荐以下几个关键的索引调整：

1. 优化customers表的覆盖索引
   原索引只包含driver_licence_id，但查询需要通过它关联到cus_id，创建以下覆盖索引可以彻底避免回表：

   CREATE INDEX customer_driver_licence_cus_id_idx ON customers (driver_licence_id, cus_id);
   

   这个索引能让数据库直接从索引中拿到driver_licence_id对应的cus_id，不需要再去查询表的其他数据，大幅减少IO开销。

2. 调整cars表的联合索引顺序
   你已有的cars_cus_id_car_id_date_created_idx要确保字段顺序是**cus_id在前，然后是car_id，最后是date_created**，如果之前的索引顺序不对，重新创建：

   CREATE INDEX cars_cus_id_car_date_idx ON cars (cus_id, car_id, date_created);
   

   这个顺序完美匹配查询逻辑：先通过cus_id过滤用户的车辆，再直接拿到car_id和date_created，既满足关联parts_in_car的需求，也能直接支持GROUP BY和ORDER BY的排序，避免额外的临时表排序操作。

3. 优化parts_in_car表的覆盖索引
   原单独的parts_in_car_car_id_idx无法覆盖关联part_id的需求，创建以下联合索引：

   CREATE INDEX parts_in_car_car_part_idx ON parts_in_car (car_id, part_id);
   

   这样数据库通过car_id就能快速找到对应的part_id，不需要回表查询其他字段。

4. 调整parts表的覆盖索引
   原索引parts_manufacturers_id_idx的字段顺序不符合查询逻辑（查询是通过part_id找manufacturers_id），创建以下覆盖索引：

   CREATE INDEX parts_part_manufacturer_idx ON parts (part_id, manufacturers_id);
   

   这个索引能让数据库通过part_id直接获取manufacturers_id，同时GROUP_CONCAT里的排序也能利用索引的顺序，进一步提升效率。

额外建议 #

记得删除之前创建的冗余索引（比如单独的cars_cus_id_idx、parts_in_car_car_id_idx等），避免索引维护带来的额外性能开销。

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