chabapok (chabapok) wrote in ru_mysql,
chabapok
chabapok
ru_mysql

Category:

Проблема с представлением

Я столкнулся со следующей проблемой: представление ведет себя не так (запрос выполняется гораздо дольше в >10^6 раз), как сам select-запрос.

Есть две таблицы:

CREATE TABLE IF NOT EXISTS `A` (
  `id` int(11) NOT NULL,
  `v1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE IF NOT EXISTS `B` (
  `id` int(11) NOT NULL,
  `v2` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


заполняем их отфонарными данными(A ~ 140000записей, B~7300записей), выполняем запрос:

SELECT A.id, A.v1, (SELECT v2 FROM B WHERE B.id<=A.id ORDER BY B.id DESC LIMIT 1) as v2m FROM A LIMIT 0,30

выполняется 0.0001сек
Делаем из этого запроса представление:

create view C (id, v1, v2m) as
SELECT A.id,
    A.v1,
    (SELECT v2 FROM B WHERE B.id<=A.id ORDER BY B.id DESC LIMIT 1) as v2m
    FROM A

и выполняем запрос к представлению:
SELECT * FROM C LIMIT 0, 30

из другой консоли: show processlist;
| 170 | root  | localhost       | asdasd | Query   |  171 | Sending data | SELECT * FROM `C` LIMIT 0, 30 |

дальше просто устал ждать и прибил его. На меньшем количестве данных дождаться окончания выполнения получалось, но все равно есть капитальная просадка времени выполнения запроса.
mysql 5.5.28-0ubuntu0.12.04.2

Для тех, кто захочет попробовать у себя, можно скачать дамп базы отсюда: zalil точка ru/33956621

вопроса 2:
1. Что я делаю не так?
2. Если это такой баг, то как бы его обойти, переделав запрос? если б там было строго равенство, можно было бы через join, но мне там надо брать именно ближайшее меньшее.
Subscribe

  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 23 comments
а почему Вы "LIMIT 0, 30" не внесли во вью?
мне надо, чтобы просто я смотрел это вью через phpmyadmin, для удобства. С сохранением функции постраничного листания. Поэтому, этот лимит у меня в селекте.

разве этот лимит который в селекте не должен "распространиться" на вью?

Если вью создавать без подзапроса (SELECT v2 FROM B WHERE B.id<=A.id ORDER BY B.id DESC LIMIT 1), то катастрофических тормозов нет, хотя и лимита в запросе вью нет.
хорошо будет показать эксплейн запроса, чтоб наверняка.
очень вероятно что получается так -
из-за того что есть сабквери в селект листе то вью выполняется через материализацию и лимит сверху не прикладывается ко вью,
т.к во вью нету лимита то сортируется вся табличка A - 140К записей,
т.к сабселект - зависимый, он выполняется для каждой строчки таблицы А - в кратце это очень дорого, самый плохой сценарий который только можно придумать, что вы и заметили по времени выполнения.

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


ясно спс.

Anonymous

November 14 2012, 16:57:15 UTC 8 years ago

че-то я туплю. оптимизация сортировки с лимитом тут ни при чем. просто сабселект будет выполняться только 30 раз, а не 140К.
думалось, что сабселект должен быть быстрый - по индексированной таблице, по праймари кею. Видимо, не такой и быстрый.

Вобщем, оказывается селект далеко не всегда юзает индекс, даже если очевидно, что индекс может быть заюзан. И в сабзапросе, он, по всей видимости, не использовался, хотя мог.

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

так что тут наверное, действительно дело в limit 0,30

eexo

8 years ago

Anonymous

November 16 2012, 08:14:13 UTC 8 years ago

из-за того что есть сабквери в селект листе то вью выполняется через материализацию

А есть ли объективные причины, чтобы такой view выполнялся через материализацию?

Посмотрел - там проблема в LEX::can_be_merged(), пишут:

1627.1.11 bell@sa | bool selects_allow_merge= select_lex.next_select() == 0;
| if (selects_allow_merge)
| {
1810.2323.19 monty@m | for (SELECT_LEX_UNIT *tmp_unit= select_lex.first_inner_unit();
| tmp_unit;
| tmp_unit= tmp_unit->next_unit())
1627.1.11 bell@sa | {
1810.2323.19 monty@m | if (tmp_unit->first_select()->parent_lex == this &&
| (tmp_unit->item == 0 ||
| (tmp_unit->item->place() != IN_WHERE &&
| tmp_unit->item->place() != IN_ON)))
1627.1.11 bell@sa | {
| selects_allow_merge= 0;
| break;
| }
| }
| }

1810.2323.19 - огромный патч со всяки переименованиями, по делу изменений нет;

1627.1.11 - sp1r-bell@sanja.is.com.ua-20041007195431-33913, "allow merging views with subqueries in WHERE clause (BUG#5504)". А есть ли *объективные* причины, почему нельзя делать algorithm=merge для подзапросов в select_list? (кстати, в MySQL 5.6 это не пофикшено? в MariaDB проверил - не пофикшено)
LEX::can_be_merged - не проблема:) там можно написать все что угодно. вопрос в том будет ли таким образом смерженный вью работать и как.
по существу - надо смотреть куда конкретно попадет этот сабселект и действовать соответственно. AFAIK это достаточно нетривиально. судя по тому что это не сделано ни в мускле и в марии это не является для них первоочередной задачей.
а до 5.6 вообще материализация view в mysql существует?
если и да, в зачаточном состоянии.
Я смотрел мекстами код mysql - довольно по-военному написано, без плюшек. А материализация - это именно что плюшки.
С view EXPLAIN какой интересный:

mysql [localhost] {msandbox} (test) > explain extended SELECT * FROM C LIMIT 0, 30;
+----+--------------------+------------+-------+---------------+---------+---------+------+--------+-----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+-------+---------------+---------+---------+------+--------+-----------+-------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 140140 | 100.00 | |
| 2 | DERIVED | A | ALL | NULL | NULL | NULL | NULL | 140140 | 100.00 | |
| 3 | DEPENDENT SUBQUERY | t1 | index | PRIMARY | PRIMARY | 4 | NULL | 1 | 739000.00 | Using where |
+----+--------------------+------------+-------+---------------+---------+---------+------+--------+-----------+-------------+
3 rows in set, 2 warnings (39 min 32.48 sec)

Note (Code 1276): Field or reference 'test.A.id' of SELECT #3 was resolved in SELECT #2
Note (Code 1003): select `C`.`id` AS `id`,`C`.`v1` AS `v1`,`C`.`v2m` AS `v2m` from `test`.`C` limit 0,30

И mysql> show status like 'Handler%';
+----------------------------+-----------+
| Variable_name | Value |
+----------------------------+-----------+
...
| Handler_read_prev | 586832829 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 183832 |
...
| Handler_write | 183831 |
+----------------------------+-----------+
15 rows in set (0.00 sec)

Для запроса без view:

| Handler_read_prev | 221630 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 30 |

Пошлите-ка багу на bugs.mysql.com.
чтобы багу послать, самое главное - это сначала надо понять ее суть. :)
Пока единственное что я понял - это то, что оно работает странновато, но почему так происходит мне обьяснили.

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

Поле filtered я не знаю, у вас оно рано 100, 100, 739000, у меня три раза 100 (это уже я пробую под 5.5.28-MariaDB, при этом поведение у нее такое же). Только что нагуглил что оно означает, согласен 739000 - странноватое значение, но у меня под марией оно не воспроизводится. На днях попробую под mysql, когда буду возле той машины.

Почему explain extended показывает 3 строки - для меня теперь очевидно -- две приходится на селект, который внутри вью, и одна на селект из результата который был получен вью-шным селеком. Поэтому в чем странность explain extended --не считая 739000, я не вижу

Насчет сравнения show status. Дело в том что если сделать set profiling=1; выполнить select * from C LIMIT 0,30 (все же дождался -- выполнялось 255сек ) и посмотреть show profile for query 2, то можно видеть такую картину:


| starting | 0.000063 |
...тут всякая предподготовка...
| executing | 0.000015 |
| Sorting result | 0.000008 |
| Sending data | 0.003277 |
...^ эти 3 строчки повторяются ~100000 раз. Как я понимаю - на каждую отдаваемую строку
| end | 0.000005 |
| query end | 0.000004 |
| closing tables | 0.000003 |
| removing tmp table | 0.000008 |
| closing tables | 0.000010 |
| freeing items | 0.000008 |
| updating status | 0.000004 |
| Waiting for query cache lock | 0.000003 |
| updating status | 0.000025 |
| Waiting for query cache lock | 0.000007 |
| updating status | 0.000002 |
| storing result in query cache | 0.000004 |
| logging slow query | 0.000012 |
| cleaning up | 0.000004 |
+--------------------------------+----------+
420458 rows in set (3.44 sec)

Что из нее ясно? каждый подзапрос внутри вью сортируется, а судя по тому что в эксплейне нету filesort - сортировка идет с применением индекса. Само по себе наличие сортировок меня не удивляет - ведь там есть подзапрос с ORDER BY.

почему мы имеем Handler_read_prev отличающееся на 3 порядка по сравнению с простым селектом? Чтобы это обьяснить,перезапустим mysql и выполним запрос который внутри вью (без limit). Тоже выполняется 253 сек. после него делаем

> show status like 'Handler%';

| Handler_read_last | 140140 |
| Handler_read_next | 0 |
| Handler_read_prev | 586832829 |
| Handler_read_rnd_next | 140165 |
| Handler_tmp_write | 221 |

Не особо сильно отличается от статистики подзапроса сделанного через во вью. Так что не вижу где там баг, не считая filtered =739000. Если это и есть баг - оставляю отправку багрепорта на ваше усмотрение, т.к. я немогу даже сформулировать его.


Я тут вижу две проблемы: запрос с VIEW выполняется дольше аналогичного без него. Это уже само по себе неправильно.

Вторая: EXPLAIN выполняется 39 min 32.48 sec даже в 5.6. Это, и переменные handler_%, обозначает, что во время EXPLAIN подзапрос фактически выполнялся. Это "фича" до 5.6, но в 5.6 такого быть уже не должно.

Вобщем-то не принципиально: если не отправите, я в понедельник сама отправлю.

chabapok

8 years ago

svetasmirnova

8 years ago

chabapok

8 years ago

svetasmirnova

8 years ago

chabapok

8 years ago

svetasmirnova

8 years ago

svetasmirnova

8 years ago

Anonymous

December 18 2012, 11:36:03 UTC 8 years ago

Баг в MariaDB: https://mariadb.atlassian.net/browse/MDEV-3862 (https://mariadb.atlassian.net/browse/MDEV-3862) . Уже пофиксили и пушнули в MariaDB 10.0.
аналогичный вопрос, только view вида select from t1 union select from t2