mysql没有按预定的索引顺序执行

mysql没有按预定的索引顺序执行，导致查询性能差

 

mysql没有按预定的顺序执行，即走的索引不是最优，没有走a表`ENTITY_ID`索引，走了b表`COMPANY_ID`

SELECT a.`ENTITY_ID`, COUNT(DISTINCT b.`ID`) AS ct
FROM Wl_Early_Warning_Record a
INNER JOIN `wl_early_warning` b ON a.`EARLY_WARNING_ID` = b.`ID`
WHERE  b.`COMPANY_ID` = 5493
    AND a.`ENTITY_ID` IN (71690718, 71690053)
GROUP BY a.`ENTITY_ID`;

 

解决方案有3

（1）加强制索引
SELECT a.`ENTITY_ID`, COUNT(DISTINCT b.`ID`) AS ct
FROM Wl_Early_Warning_Record a  force index(ENTITY_ID)
INNER JOIN `wl_early_warning` b ON a.`EARLY_WARNING_ID` = b.`ID`
WHERE  b.`COMPANY_ID` = 5493
    AND a.`ENTITY_ID` IN (71690718, 71690053)
GROUP BY a.`ENTITY_ID`;

（2）使用严格按sql编写顺序 STRAIGHT_JOIN

SELECT a.`ENTITY_ID`, COUNT(DISTINCT b.`ID`) AS ct

FROM Wl_Early_Warning_Record a force index(ENTITY_ID)

        STRAIGHT_JOIN `wl_early_warning` b ON a.`EARLY_WARNING_ID` = b.`ID`

WHERE b.`COMPANY_ID` = 5493

        AND a.`ENTITY_ID` IN (71690718, 71690053)

GROUP BY a.`ENTITY_ID`;

（3）写成子查询
select temp.ENTITY_ID, count(temp.`EARLY_WARNING_ID`)

from `wl_early_warning` b

inner join

(select DISTINCT a.`ENTITY_ID`, a.`EARLY_WARNING_ID`

FROM Wl_Early_Warning_Record a

WHERE  a.`ENTITY_ID` IN (71690718, 71690053)) temp

on temp.`EARLY_WARNING_ID` = b.`ID`

where  b.`COMPANY_ID` = 5493

group by temp.ENTITY_ID