in导致索引失效

结论:IN查询导致索引失效

user_info(用户表)

 

role_info(角色表)

 

user_role_relation(用户与角色的关系)

 

使用in查询的时候

EXPLAIN

SELECT *

FROM role_info

WHERE role_code in (

SELECT role_code

FROM user_role_relation

WHERE user_code in(

SELECT user_code

FROM user_info

WHERE user_name = '李四'))

 

 

 

使用=查询的时候

 

EXPLAIN

SELECT *

FROM role_info

WHERE role_code = (

SELECT role_code

FROM user_role_relation

WHERE user_code =(

SELECT user_code

FROM user_info

WHERE user_name = '李四'))