mysql复合条件匹配的查询优化

对于查找所有 r = 'r1' 的关系，并且同时存在一个 r = 'r2' 的关系，如果写出如下语句，查询速度非常慢，即使对r, h, t, h_table, t_table 都加了索引：

SELECT id FROM `关系` r1
WHERE r1.r = 'r1'
  AND EXISTS (
    SELECT 1 FROM `关系` r2
    WHERE r1.h = r2.h
      AND r1.t = r2.t
      AND r1.h_table = r2.h_table
      AND r1.t_table = r2.t_table
      AND r2.r = 'r2'
  );

原因有以下两种：

1、mysql在执行多字段联合查询时，无法有效利用这些单列索引进行快速查找

2、EXISTS 是一种“半连接”操作，通常效率不如显式的 JOIN，尤其是在大表中做多次子查询时，性能会显著下降。

优化措施：

1、添加组合索引：

ALTER TABLE `关系` ADD INDEX idx_h_t_tables_r (h, t, h_table, t_table, r);

如果报specified key was too long; max length is 3072 bytes，可以使用前缀索引，具体如下

ALTER TABLE `关系` ADD INDEX idx_h_t_tables_r (h(100), t(100), 
h_table(100), t_table(100), r(100));

 2、改写为 JOIN 查询

SELECT DISTINCT r1.id
FROM `关系` r1
JOIN `关系` r2
  ON r1.h = r2.h
 AND r1.t = r2.t
 AND r1.h_table = r2.h_table
 AND r1.t_table = r2.t_table
WHERE r1.r = 'r1'
  AND r2.r = 'r2';