Форум PHP программистов > Увеличение времени запроса

[ Поиск ] - [ Пользователи ] - [ Календарь ]

Полная Версия: Увеличение времени запроса

paul85

18.04.2014 - 21:05

Здравствуйте, уважаемые форумчане!

Существует относительно простенькая база данных, в которой присутствуют следующие таблицы:
brand, model, test
Первые 2 "объединены" PR-FK. Запрос строится таким образом, что берется таблица test и к ней INNER JOIN-ами добавляются поля из model и brand. Соответственно в результате мы получаем model_name, brand_name и test.*

Но почему-то когда я объединяю таблицы model и test тоже связкой PK FK, то время запроса, казалось бы, должно уменьшиться. А оно значительно увеличивается.

Вот EXPLAIN запроса с ключом и без:


+----+-------------+------------+------+------------------------+-----------------+---------+-------- -------------------+------+---------------------------------+
| id | select_type | table      | type | possible_keys          | key             | key_len | ref                       | rows | Extra                           |
+----+-------------+------------+------+------------------------+-----------------+---------+------- --------------------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL                   | NULL            | NULL    | NULL                      |  130 | Using temporary; Using filesort |
|  2 | DERIVED     | temp3      | ALL  | PRIMARY                | NULL            | NULL    | NULL                      |   17 | Using temporary; Using filesort |
|  2 | DERIVED     | temp2      | ref  | PRIMARY,fk_model_brand | fk_model_brand  | 5       | wisereport.temp3.brand_id |   86 | Using where                     |
|  2 | DERIVED     | temp1      | ref  | fk_test_model1         | fk_test_model1  | 5       | wisereport.temp2.model_id |    3 | Using where                     |
+----+-------------+------------+------+------------------------+-----------------+---------+------- --------------------+------+---------------------------------+


+----+-------------+------------+--------+------------------------+---------+---------+-------------- -------------+------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys          | key     | key_len | ref                       | rows | Extra                                        |
+----+-------------+------------+--------+------------------------+---------+---------+------------- --------------+------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                   | NULL    | NULL    | NULL                      |  130 | Using temporary; Using filesort              |
|  2 | DERIVED     | temp1      | ALL    | NULL                   | NULL    | NULL    | NULL                      | 8363 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | temp2      | eq_ref | PRIMARY,fk_model_brand | PRIMARY | 4       | wisereport.temp1.model_id |    1 |                                              |
|  2 | DERIVED     | temp3      | eq_ref | PRIMARY                | PRIMARY | 4       | wisereport.temp2.brand_id |    1 |                                              |
+----+-------------+------------+--------+------------------------+---------+---------+------------- --------------+------+----------------------------------------------+

Почему без ключа работает в разы быстрее? Я этого никак понять не могу...

glock18

18.04.2014 - 21:13

Первый с FK?

paul85

18.04.2014 - 21:16

Да, первый запрос, когда все три таблицы объединены последовательно друг с другом PK-FK.

А второй лишь brand и model PK-FK, а test сама по себе. Вообще без каких-либо ключей.

glock18

18.04.2014 - 21:22

Ну, исходя из всего вышеописанного, вполне вероятно, что индексы создаваемые для FK ничего не дают при выборке. Вполне вероятно, что таблица, в которой 8k+ рядов просматривается без ключей, просто имеет намного более удобочитаемые ряды, чем другие две. У тех получается 1400+ строк, и если есть текстовые поля (особенно если несколько), то это почти 100% и приводит к проигрышу против предположительно таблицы с чисто числовыми колонками.

Как результат Mysql, вполне возможно, ошибочно оценивает влияние индекса, и решает его использовать. В этом случае можно попросить его/их игнорировать.

T1grOK

18.04.2014 - 21:28

FK дает дополнительную нагрузку, так как внутри подсистема хранения контролирует еще целостность данных.
Высоконагруженные проекты часто для надежности используют InnoDB(который восстанавливается почти всегда в случае сбоев, в отличие от MyISAM, который в половине случаев умрет смертью храбрых), но отказываются от внешних ключей из-за чрезмерной медлительности.
+ роль(порой и огромную) играет план выполнения запросов, в данном случае как видим он разный.

_____________
Mysql, Postgresql, Redis, Memcached, Unit Testing, CI, Kohana, Yii, Phalcon, Zend Framework, Joomla, Open Cart, Ymaps, VK Api

paul85

18.04.2014 - 21:40

glock18, я что-то неочень понял идею. В выборке участвуют только числовые поля (в WHERE и ON). Из всех 3-х таблиц там всего-то и есть по сути 2 строковых поля, как раз model_name и brand_name. И пересечений по ним не строится. Остальные поля INT и DATE.

T1grOK, то есть если я разомкну brand и model, то по идее еще быстрее будет? Я всегда думал, что FK и целостность данных влияет только на запись.

paul85

18.04.2014 - 21:48

T1grOK, а как можно повлиять на план выполнения запроса? То есть я прекрасно знаю, как его оптимизировать, и в какой последовательности выполнять. Но как об этом сообщить СуБД?

T1grOK

18.04.2014 - 21:52

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

_____________
Mysql, Postgresql, Redis, Memcached, Unit Testing, CI, Kohana, Yii, Phalcon, Zend Framework, Joomla, Open Cart, Ymaps, VK Api

glock18

18.04.2014 - 22:10

Цитата (T1grOK @ 18.04.2014 - 17:28)

FK дает дополнительную нагрузку, так как внутри подсистема хранения контролирует еще целостность данных.

ммм... А с чего это выборку затрагивало?

glock18

18.04.2014 - 22:17

Цитата (paul85 @ 18.04.2014 - 17:40)

glock18, я что-то неочень понял идею. В выборке участвуют только числовые поля (в WHERE и ON). Из всех 3-х таблиц там всего-то и есть по сути 2 строковых поля, как раз model_name и brand_name. И пересечений по ним не строится. Остальные поля INT и DATE.

Так как проход по таблицам делается, то все поля имеют значение. И наличие текстовых полей (varchar, text и тд) означает, что размер одной строки есть значение переменное. Чем больше таких полей в таблице, тем больше времени мускуль тратит на проход.

T1grOK

19.04.2014 - 09:45

Цитата (glock18 @ 18.04.2014 - 18:10)

ммм... А с чего это выборку затрагивало?

Я тоже так думал, пока не столкнулся на проекте с проблемой выборки из трех таблиц(100кк, 10к, 10к записей) при идентичных результатах EXPLAIN запрос с FK выполнялся 0.7 секунды, а без 0.58. (Хотя по сути такого не должно быть ведь FK в принципе - это обычный индекс + триггер).

P.S. Орать во все горло, что везде так не буду, может версия Mysql виновата или ее конфигурация в столь странном поведении.
На счет аналогичных странностей, PHP 5.3.17 в определенной конфигурации, невероятно тормозит при операциях деления на 0, и выполняет ОДНО деление за 0,1 секунды.

_____________
Mysql, Postgresql, Redis, Memcached, Unit Testing, CI, Kohana, Yii, Phalcon, Zend Framework, Joomla, Open Cart, Ymaps, VK Api

glock18

19.04.2014 - 10:04

Цитата (T1grOK @ 19.04.2014 - 05:45)

Аналогичные результаты предполагают использование одних и тех индексов? Мб все таки индексы fk-шный использовался? Лучший способ проверить влияет ли FK на выборку - создать вручную индекс соответствующий, и сравнить перфоманс запроса с FK против просто индекса.

PS: насколько я помню, explain иногда некорректно показывает какой индекс используется по факту

Быстрый ответ:

Здесь расположена полная версия этой страницы.