为什么这句explain出来的type是all

Question

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就快很多

Answer 1

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) ;

Answer 2

前者不知为何select uf表的类型是eq_ref,select u表的时候是all
其中user_id和friend_id都是外键关联到u表的id上,u表的id是主键把or换成union,先select再union,似乎结果集的笛卡尔积变小了疑问是为什么,第一种方法select u表的类型是all

Answer 3

SELECT u.id FROM user AS u, user_friend AS uf WHERE  
(uf.user_id = 1 AND uf.friend_id = u.id)
不带or的看看走得什么explain

Answer 4

| 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)           |

Answer 5

根据“Using union(PRIMARY,user_friend_friend_ref);”
user_friend.user_id是主键？？！！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，貌似这个快点。下面是测试用例：
DROP TABLE IF EXISTS user; 
CREATE TABLE user (
id INT NOT NULL auto_increment,
name VARCHAR(36) NOT NULL,
primary key(id)
) ENGINE=InnoDB;INSERT INTO user (name) VALUES (uuid());
INSERT INTO user (name) SELECT uuid() FROM user;
INSERT INTO user (name) SELECT uuid() FROM user;
INSERT INTO user (name) SELECT uuid() FROM user;
INSERT INTO user (name) SELECT uuid() FROM user;
INSERT INTO user (name) SELECT uuid() FROM user;
INSERT INTO user (name) SELECT uuid() FROM user;
INSERT INTO user (name) SELECT uuid() FROM user;
INSERT INTO user (name) SELECT uuid() FROM user;
INSERT INTO user (name) SELECT uuid() FROM user;
INSERT INTO user (name) SELECT uuid() FROM user;
INSERT INTO user (name) SELECT uuid() FROM user;
INSERT INTO user (name) SELECT uuid() FROM user;
INSERT INTO user (name) SELECT uuid() FROM user;DROP TABLE IF EXISTS user_friend;
CREATE TABLE user_friend (
id INT NOT NULL auto_increment,
user_id INT NOT NULL,
friend_id INT NOT NULL,
primary key(id),
index idx_user(user_id),
index idx_friend(friend_id)
) ENGINE=InnoDB;INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 1 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 2 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 3 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 4 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 5 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 11 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 12 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 13 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 14 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 15 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 21 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 22 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 23 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 24 FROM user;
INSERT INTO user_friend (user_id, friend_id) SELECT id, id + 25 FROM user;EXPLAIN
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;+----+-------------+-------+-------------+---------------------+---------------------+---------+-----------------+------+-----------------------------------------------+
| id | select_type | table | type        | possible_keys       | key                 | key_len | ref             | rows | Extra                                         |
+----+-------------+-------+-------------+---------------------+---------------------+---------+-----------------+------+-----------------------------------------------+
|  1 | SIMPLE      | uf    | index_merge | idx_user,idx_friend | idx_user,idx_friend | 4,4     | NULL            |   16 | Using union(idx_user,idx_friend); Using where |
|  1 | SIMPLE      | u     | eq_ref      | PRIMARY             | PRIMARY             | 4       | test.uf.user_id |    1 | Using index                                   |
+----+-------------+-------+-------------+---------------------+---------------------+---------+-----------------+------+-----------------------------------------------+

Answer 6

uf里的主键是 (uf.user_id,uf.friend)

Answer 7

因为OR的原故。这样导致MYSQL无法使用索引，如果只是一张表，MYSQL还会使用索引合并技术来优化。但你现在是两张表，导致MYSQL无法常规优化。 当然也可以说是MYSQL做的不理想的地方。