SQL 性能：使用联合和子查询

Question

你好，stackoverflow（我的第一个问题！），

我们正在做一些类似 SNS 的事情，并且有一个关于优化查询的问题。

使用 mysql 5.1，创建当前表：

CREATE TABLE friends(
 user_id BIGINT NOT NULL,
 friend_id BIGINT NOT NULL,
 PRIMARY KEY (user_id, friend_id)
) ENGINE INNODB;

示例数据填充如下：

INSERT INTO friends VALUES
(1,2),
(1,3),
(1,4),
(1,5),
(2,1),
(2,3),
(2,4),
(3,1),
(3,2),
(4,1),
(4,2),
(5,1),
(5,6),
(6,5),
(7,8),
(8,7);

业务逻辑：我们需要找出给定用户的哪些用户是朋友或朋友的朋友。 对于 user_id=1 的用户，当前的查询是：

SELECT friend_id FROM friends WHERE user_id = 1
 UNION
 SELECT DISTINCT friend_id FROM friends WHERE user_id IN (
 SELECT friend_id FROM friends WHERE user_id = 1
);

预期结果是（顺序无关紧要）：

2
3
4
5
1
6

如您所见，上面的查询执行子查询“SELECTfriend_id FROM Friends WHERE user_id = 1”两次。

所以，问题来了。如果性能是您最关心的问题，您将如何更改上述查询或架构？

提前致谢。

Answer 1

在这种特殊情况下，您可以使用 JOIN：

SELECT DISTINCT f2.friend_id 
  FROM friends AS f1
    JOIN friends AS f2 ON f1.friend_id=f2.user_id OR f2.user_id=1
  WHERE f1.user_id=1;

检查每个查询表明 JOIN 在大 O 意义上与 UNION 性能差不多，尽管可能更快常数因子。 Jasie 的查询看起来可能更快。

EXPLAIN SELECT friend_id FROM friends WHERE user_id = 1
  UNION
    SELECT DISTINCT friend_id FROM friends WHERE user_id IN (
      SELECT friend_id FROM friends WHERE user_id = 1
    );
+----+--------------------+------------+--------+---------------+---------+---------+------------+------+-------------------------------------------+
| id | select_type        | table      | type   | possible_keys | key     | key_len | ref        | rows | Extra                                     |
+----+--------------------+------------+--------+---------------+---------+---------+------------+------+-------------------------------------------+
|  1 | PRIMARY            | friends    | ref    | PRIMARY       | PRIMARY | 8       | const      |    4 | Using index                               |
|  2 | UNION              | friends    | index  | NULL          | PRIMARY | 16      | NULL       |   16 | Using where; Using index; Using temporary |
|  3 | DEPENDENT SUBQUERY | friends    | eq_ref | PRIMARY       | PRIMARY | 16      | const,func |    1 | Using index                               |
| NULL | UNION RESULT       | <union1,2> | ALL    | NULL          | NULL    | NULL    | NULL       | NULL |                                           |
+----+--------------------+------------+--------+---------------+---------+---------+------------+------+-------------------------------------------+


EXPLAIN SELECT DISTINCT f2.friend_id 
  FROM friends AS f1
    JOIN friends AS f2 
      ON f1.friend_id=f2.user_id OR f2.user_id=1
  WHERE f1.user_id=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+---------------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                                       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+---------------------------------------------+
|  1 | SIMPLE      | f1    | ref   | PRIMARY       | PRIMARY | 8       | const |    4 | Using index; Using temporary                |
|  1 | SIMPLE      | f2    | index | PRIMARY       | PRIMARY | 16      | NULL  |   16 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+---------------------------------------------+


EXPLAIN SELECT DISTINCT friend_id FROM friends WHERE user_id IN (
    SELECT friend_id FROM friends WHERE user_id = 1
) OR user_id = 1;
+----+--------------------+---------+--------+---------------+---------+---------+------------+------+-------------------------------------------+
| id | select_type        | table   | type   | possible_keys | key     | key_len | ref        | rows | Extra                                     |
+----+--------------------+---------+--------+---------------+---------+---------+------------+------+-------------------------------------------+
|  1 | PRIMARY            | friends | index  | PRIMARY       | PRIMARY | 16      | NULL       |   16 | Using where; Using index; Using temporary |
|  2 | DEPENDENT SUBQUERY | friends | eq_ref | PRIMARY       | PRIMARY | 16      | const,func |    1 | Using index                               |
+----+--------------------+---------+--------+---------------+---------+---------+------------+------+-------------------------------------------+

Answer 2

不需要UNION。只需包含与初始用户的 user_id 的 OR 即可：

SELECT DISTINCT friend_id FROM friends WHERE user_id IN (
    SELECT friend_id FROM friends WHERE user_id = 1
) OR user_id = 1;

+-----------+
| friend_id |
+-----------+
|         2 |
|         3 |
|         4 |
|         5 |
|         1 |
|         6 |
+-----------+