Производительность SQL: использование объединения и подзапросов

Привет, 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

Как видите, приведенный выше запрос дважды выполняет подзапрос «ВЫБЕРИТЕ ИД_друга ИЗ друзей, ГДЕ ИД_пользователя = 1».

Итак, вот вопрос. Если вас больше всего беспокоит производительность, как бы вы изменили приведенный выше запрос или схему?

Заранее спасибо.


sql join select subquery union
person kaiTaku    schedule 22.01.2011    source источник
comment
@kaiTaku, я отредактировал вопрос, чтобы показать образцы кода. Честно говоря, это, вероятно, не будет проблемой, если у вас нет огромного количества записей, поскольку индексы должны сделать это довольно быстро. Возможно, вы захотите рассмотреть другие индексы, один только для user_id. Как и в случае с всеми оптимизациями, тестируйте на репрезентативных данных.   -  person paxdiablo    schedule 22.01.2011
comment
@paxdiablo, Вау! Такой быстрый ответ! Извините, возможно, я перезаписал ваше редактирование, но все равно спасибо. И, возможно, вы правы в том, что вам нужно проводить тесты, чтобы найти лучшую оптимизацию.   -  person kaiTaku    schedule 22.01.2011
comment
Один вопрос, если 1 дружит со 2, то у вас есть строка (1, 2). Нужна ли вашей таблице соответствующая строка (2, 1)? И возможен ли сценарий, в котором 1 дружит с 2, но 2 не дружит с 1. Например, ваш список контактов MSN, где вы можете иметь [email protected] в своем списке, но он не обязательно имеет вас в своем списке.   -  person Salman A    schedule 22.01.2011
comment
Просто примечание: нет необходимости в DISTINCT в вашем примерном запросе, поскольку UNION все равно удалит дубликаты.   -  person a_horse_with_no_name    schedule 22.01.2011
comment
@ Салман А, чтобы ответить на твой вопрос, не обязательно. Если вы можете выполнить работу быстрее с другим стилем, пожалуйста, дайте мне знать!   -  person kaiTaku    schedule 22.01.2011
comment
@a_horse_with_no_name, я хотел удалить все дубликаты в подзапросе, чтобы повысить производительность.   -  person kaiTaku    schedule 22.01.2011
comment
@kaiTaku: UNION в любом случае сделает это, поэтому база данных дважды попытается удалить дубликаты. Так что либо используйте UNION ALL, либо не используйте DISTINCT. Наличие двух шагов для удаления дубликатов не ускорит процесс.   -  person a_horse_with_no_name    schedule 22.01.2011
comment
@a_horse_with_no_name, ты прав. Я провел тесты с 4,5 миллионами записей с отдельными и без отдельных записей, и производительность была одинаковой. Спасибо за чаевые!   -  person kaiTaku    schedule 22.01.2011
comment
Хммм, в настоящее время предложенный outis запрос является самым быстрым, но с примерно 4,5 миллионами записей запрос занимает 2,87 секунды, чтобы вернуться. В журнале медленных запросов указано, что Rows_examined равен 10, поэтому индекс должен работать, но я не могу понять, почему такая задержка. Аппаратные характеристики не так уж плохи: 2,27 ГГц Xeon, 4 ГБ памяти   -  person kaiTaku    schedule 22.01.2011

Ответы (2)


arrow_upward
1
arrow_downward

В этом конкретном случае вы можете использовать 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 будет примерно так же эффективен, как UNION в большом смысле, хотя, возможно, быстрее на постоянный коэффициент. Запрос Джейси выглядит так, как будто он может быть быстрее.

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                               |
+----+--------------------+---------+--------+---------------+---------+---------+------------+------+-------------------------------------------+
person outis    schedule 22.01.2011
comment
Это было действительно быстрее! Я тестировал около миллиона записей, и производительность вашего запроса была примерно в 8 раз выше. Спасибо! - person kaiTaku; 22.01.2011

arrow_upward
0
arrow_downward

Нет необходимости в UNION. Просто добавьте OR к user_id начинающего пользователя:

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 |
+-----------+
person atp    schedule 22.01.2011
comment
Спасибо! Должно быть, это было слишком легко для тебя. - person kaiTaku; 22.01.2011