为什么这句explain出来的type是all
explain SELECT u.id FROM user AS u, user_friend AS uf WHERE
(uf.user_id = 1 AND uf.friend_id = u.id) OR
(uf.friend_id = 1 AND uf.user_id = u.id) ;
改成select xx union select xx就快很多
------解决方案--------------------explain select xx union select xx 这个呢
或者你再执行遍
SELECT u.id FROM user AS u, user_friend AS uf WHERE
(uf.user_id = 1 AND uf.friend_id = u.id) OR
(uf.friend_id = 1 AND uf.user_id = u.id) ;
------解决方案--------------------SELECT u.id FROM user AS u, user_friend AS uf WHERE
(uf.user_id = 1 AND uf.friend_id = u.id)
不带or的看看走得什么explain
------解决方案--------------------根据“Using union(PRIMARY,user_friend_friend_ref);”
user_friend.user_id是主键??!!
SQL code
SELECT u.id
FROM user u
INNER JOIN user_friend uf
ON u.id = uf.user_id
WHERE uf.user_id = 1
OR uf.friend_id = 1;
------解决方案--------------------
因为OR的原故。
这样导致MYSQL无法使用索引,如果只是一张表,MYSQL还会使用索引合并技术来优化。但你现在是两张表,导致MYSQL无法常规优化。 当然也可以说是MYSQL做的不理想的地方。