为什么这句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 uf表的类型是eq_ref,select u表的时候是all
其中user_id和friend_id都是外键关联到u表的id上,u表的id是主键

把or换成union,先select再union,似乎结果集的笛卡尔积变小了

疑问是为什么,第一种方法select u表的类型是all

SELECT u.id FROM user AS u, user_friend AS uf WHERE  
(uf.user_id = 1 AND uf.friend_id = u.id)


不带or的看看走得什么explain

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SIMPLE | uf | ref | PRIMARY,user_friend_friend_ref | PRIMARY | 4 | const | 9 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | uf.friend_id | 1 | |

带or的
| 1 | SIMPLE | uf | index_merge | PRIMARY,user_friend_friend_ref | PRIMARY,user_friend_friend_ref | 4,4 | NULL | 12 | Using union(PRIMARY,user_friend_friend_ref); Using where |
| 1 | SIMPLE | u | ALL | PRIMARY | NULL | NULL | NULL | 309 | Range checked for each record (index map: 0x1) |