Каскадное предложение WHERE внутри VIEW с UNION

Это не решено, но я выяснил, почему: представление MySQL, содержащее UNION, плохо оптимизируется... Другими словами, МЕДЛЕННО!

Исходный пост:

Я работаю с базой данных для игры. Есть две одинаковые таблицы equipment и safety_dep_box. Чтобы проверить, есть ли у игрока предмет снаряжения, я хотел бы проверить обе таблицы.

Вместо выполнения двух запросов я хочу воспользоваться функциональностью UNION в MySQL. Недавно я узнал, что могу создать VIEW. Вот мой взгляд:

CREATE VIEW vAllEquip AS SELECT * FROM equipment UNION SELECT * FROM safety_dep_box;

Вид создан просто отлично. Однако, когда я бегу

SELECT * FROM vAllEquip WHERE owner=<id>

Запрос занимает вечность, в то время как независимые запросы на выборку выполняются быстро. Кажется, я знаю, почему, но я не знаю, как это исправить.

Спасибо!

P.S. с дополнительной информацией:

Две таблицы идентичны по структуре, но разделены, поскольку представляют собой таблицы с несколькими 100 миллионами строк. Структура включает в себя первичный ключ по идентификатору int, множественный индекс по владельцу int. Чего я не понимаю, так это разницы в скорости между следующим:

SELECT COUNT(*) FROM (SELECT * FROM equipment WHERE owner=1 UNION ALL SELECT * FROM safety_dep_box WHERE owner=1) AS uES;

0,42 сек.

SELECT COUNT(*) FROM (SELECT * FROM equipment WHERE owner=1 UNION  SELECT * FROM safety_dep_box WHERE owner=1) AS uES;

0,37 сек.

SELECT COUNT(*) FROM vAllEquip WHERE owner=1;

прервано через 60 секунд

Версия: 5.1.51

mysql> explain SELECT * FROM equipment UNION SELECT * FROM safety_dep_box;
+----+--------------+----------------+------+---------------+------+---------+------+---------+-------+
| id | select_type  | table          | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+--------------+----------------+------+---------------+------+---------+------+---------+-------+
|  1 | PRIMARY      | equipment      | ALL  | NULL          | NULL | NULL    | NULL | 1499148 |       |
|  2 | UNION        | safety_dep_box | ALL  | NULL          | NULL | NULL    | NULL |  867321 |       |
| NULL | UNION RESULT | <union1,2>     | ALL  | NULL          | NULL | NULL    | NULL |    NULL |       |
+----+--------------+----------------+------+---------------+------+---------+------+---------+-------+

с предложением WHERE

mysql> explain SELECT * FROM equipment WHERE owner=1 UNION ALL SELECT * FROM safety_dep_box WHERE owner=1
    -> ;
+----+--------------+----------------+------+-----------------------+-------+---------+-------+------+-------+
| id | select_type  | table          | type | possible_keys         | key   | key_len | ref   | rows | Extra |
+----+--------------+----------------+------+-----------------------+-------+---------+-------+------+-------+
|  1 | PRIMARY      | equipment      | ref  | owner,owner_2,owner_3 | owner | 4       | const |    1 |       |
|  2 | UNION        | safety_dep_box | ref  | owner,owner_3         | owner | 4       | const |    1 |       |
| NULL | UNION RESULT | <union1,2>     | ALL  | NULL                  | NULL  | NULL    | NULL  | NULL |       |
+----+--------------+----------------+------+-----------------------+-------+---------+-------+------+-------+

mysql view union
person Mikhail    schedule 08.11.2010    source источник
comment
Какую версию MySQL вы используете? Не могли бы вы показать объяснение SELECT * FROM Equipment UNION SELECT * FROM safety_dep_box; ?   -  person Alexis Dufrenoy    schedule 16.11.2010
comment
Я ответил на ваши 2 вопроса в своем исходном посте.   -  person Mikhail    schedule 16.11.2010
comment
Вам нужно EXPLAIN с предложением WHERE. В противном случае план выполнения довольно очевиден (получить все строки из одной таблицы, затем из другой). С предложением WHERE EXPLAIN (вероятно) скажет вам, что он получает небольшое количество строк из первой таблицы, а затем выполняет какое-то сканирование таблицы во второй (возможно, из-за отсутствия UNION ALL, как описано ниже).   -  person Larry Lustig    schedule 16.11.2010
comment
Я добавил EXPLAIN с WHERE. В этом суть проблемы — когда я добавляю предложение WHERE к выбору из представления — оно не распространяется на внутренние операторы SELECT. Из того, что мне удалось найти, нет другого решения, кроме как не использовать представление.   -  person Mikhail    schedule 16.11.2010

Ответы (2)


arrow_upward
2
arrow_downward

Во-первых, вам, вероятно, следует использовать UNION ALL вместо обычного UNION. С простым UNION движок попытается дедуплицировать ваш результирующий набор. Скорее всего, это источник вашей проблемы.

Во-вторых, вам понадобятся индексы владельца в обеих таблицах, а не только в одной. В идеале это должны быть целочисленные столбцы.

В-третьих, Рэндольф прав в том, что вы не должны использовать «*» в своем операторе SELECT. Перечислите все столбцы, которые вы хотите включить. Это особенно важно в UNION, потому что столбцы должны точно совпадать, и, если есть разногласия в порядке столбцов в ваших двух таблицах, вы можете принудительно выполнить некоторое преобразование типов, которое будет стоить вам некоторого времени.

Наконец, фраза «Есть две одинаковые таблицы» почти всегда указывает на то, что ваша база данных спроектирована неоптимально. Вероятно, это должна быть одна таблица. Чтобы указать право собственности на предмет, ваша таблица safety_dep_box должна содержать только идентификатор владельца и идентификатор предмета (чтобы связать оборудование и игроков) и, возможно, дополнительный столбец целочисленного ключа с автоматической нумерацией.

person Larry Lustig    schedule 15.11.2010
comment
Я добавил дополнительную информацию к исходному сообщению, чтобы ответить на ваши вопросы. Я также создал представление, которое выбирает только идентификатор, владельца, имя, и запрос этого представления также был прерван через 60 секунд. - person Mikhail; 15.11.2010
comment
Сколько времени для SELECT COUNT(*) FROM (SELECT * FROM Equipment WHERE owner=1 UNION SELECT * FROM safety_dep_box WHERE owner=26990) AS uES;? Если вы не опечатались, вы не тестируете SELECT на настольном оборудовании? идентификатор владельца = 1. - person Larry Lustig; 15.11.2010
comment
Да, опечатка :(. Я заменил идентификатор владельца, чтобы устранить путаницу, но вместо этого я создал несколько - person Mikhail; 15.11.2010
comment
Комментарий union all в этом ответе является важной частью вашей проблемы со скоростью. Как он сказал, union включает неявную фазу устранения дубликатов, которая требует сортировки и сравнения. Со 100+ миллионами строк это будет очень и очень медленно. - person Donnie; 16.11.2010
comment
Но я попробовал UNION ALL, и это не помогло ›_‹. Проблема в том, что мое предложение WHERE не распространяется на таблицы в представлении, и представление пытается создать временную таблицу, содержащую обе таблицы целиком. Я не знаю, как ускорить это, кроме как не использовать представление - person Mikhail; 16.11.2010

arrow_upward
0
arrow_downward

Во-первых, никогда не используйте SELECT * в представлениях. Это ленивый код. Во-вторых, не зная, как выглядят базовые таблицы, мы вряд ли сможем вам помочь.

Причина, по которой это занимает вечность, заключается в том, что он должен построить полный результат, а затем отфильтровать его. Вам понадобятся индексы для ваших owner полей, какими бы они ни были.

person Community    schedule 08.11.2010
comment
SELECT *, потому что я хочу, чтобы мое представление имело ту же функциональность, что и индивидуальный выбор. У меня уже есть индексы в столбце владельца, хотя это не помогает. В базовых таблицах слишком много столбцов, чтобы перечислять их здесь, хотя я не понимаю, какое это имеет значение. - person Mikhail; 09.11.2010
comment
Извините, но нет оправдания использованию SELECT * в представлении. Возможно, вам нужно изучить индексированное представление? - person ; 09.11.2010
comment
Есть ли способ проиндексировать представление в MySQL, о котором я не знаю? - person Mikhail; 16.11.2010