Странная ситуация с SQL запросами MariaDB / PHP

1 234
D
На сайте с 18.12.2015
Offline
142
#31
Dimanych:
убрав или добавив limit 1 (казалось бы это ничего измениться не должно, но есть отличия или 2 сек или 0.1)

Хоть на результат это никак и не влияет, но план выполнения становится другой. Оптимизатор не отличается умом и сообразительностью.

С LIMIT 1 можно сделать так:

SELECT SQL_NO_CACHE SQL_BIG_RESULT COUNT(*) FROM tbl WHERE user_name="admin" GROUP BY user_id LIMIT 1;

или

SELECT SQL_NO_CACHE COUNT(*) FROM tbl IGNORE INDEX FOR GROUP BY (user_id) WHERE user_name="admin" GROUP BY user_id LIMIT 1;

Что приведет к одному и тому же плану выполнения запроса, как и без LIMIT 1:

  EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM voc_quiz_top WHERE user_name="admin" GROUP BY user_id;
+------+-------------+--------------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------+------+---------------+------+---------+------+--------+----------------------------------------------+
| 1 | SIMPLE | voc_quiz_top | ALL | NULL | NULL | NULL | NULL | 540416 | Using where; Using temporary; Using filesort |
+------+-------------+--------------+------+---------------+------+---------+------+--------+----------------------------------------------+

Все проблема в cardinality. 745 - это очень низко, именно поэтому при использовании индекса user_id (базовый запрос с LIMIT 1) и проседает в производительности, так как использование индекса тут не оправдано.

Самый быстрый вариант без filesort и временных таблиц, притом используя индекс, у меня получился с композитным ключом user_name_user_id:

EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM tbl WHERE user_name="admin" GROUP BY user_id;
+------+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | tbl | ref | user_name_user_id | user_name_user_id | 257 | const | 2 | Using where; Using index |
+------+-------------+-------+------+-------------------+-------------------+---------+-------+------+--------------------------+

Более того, что на PHP, что через CLI, все запросы выполняются одинаково, EXPLAIN у него тоже одинаковый. Но я проверял не через phpMyAdmin, возможно phpMyAdmin устанавливает какие-то свои переменные после коннекта.

Разработка и поддержка высоконагруженных проектов.
D
На сайте с 05.06.2007
Offline
155
#32

danforth, одно мне точно понятно, запрос ужасен и это проблема сайта)

Видимо логика обработки на deb8 mariadb 10.0 всё таки была немного другая или ещё что-то повлияло - ведь тормоза ранее не ощущались.

Всем спасибо за помощь, наверное дальше уже не стоит мусолить эту тему.

Написал не мало шедевров ;)
D
На сайте с 05.06.2007
Offline
155
#33

Возможно кому-то интересно.

При оптимизации другой таблицы, которая значительно жирнее, выяснилось что mariadb очень плохо работает с MyIsam, никакие методы оптимизации, индексы и прочее не помогали, помогла только конвертация в Innodb, которая сразу дала прирос по всем тяжёлым запросам примерно на 50-70%. Не смотря на то что разные бенчмарки (возможно старые) говорят что MyIsam на выборку значительно быстрее, однако я установил множество тормозов как только начинаем использовать сортировки, и никакие индексы тут к сожалению не помогают. Поэтому если у кого то ещё есть MyIsam, советую конвертировать в Innodb :)

Solmyr
На сайте с 10.09.2007
Offline
496
#34
Dimanych:
Поэтому если у кого то ещё есть MyIsam, советую конвертировать в Innodb

Фундаментальная разница, не решаемая оптимизацией запросов и таблиц, между MyIsam и Innodb состоит в том, что у Innodb будет гораздо более медленное добавление строк и несколько более медленные операции, затрагивающие за один раз много строк (селекты и апдейты), также для fixed row таблиц, у Innodb будут гораздо более медленные селекты, в случаях когда нельзя использовать индекс (но это обычно решается правильной оптимизацией).

В реальной жизни Innodb может быть быстрее только на нагруженных сайтах (когда в одну таблицу происходят одновременные чтения и записи), и то, только в тех случаях, когда записи вам нужны transaction safe. В иных случаях Innodb может быть быстрее только в тех случаях, когда администратор БД не понимает что делает, и случайно оптимизировал таблицы для MyIsam и Innodb по-разному.

Еще один минус Innodb - с ним гораздо труднее делать и развертывать бэкапы, особенно больших баз.

А вот в случае неправильной оптимизации - тогда да. Таблицы Innodb трудно запороть так, чтобы они случайно работали "еще медленнее". А MyIsam вполне можно.

iHead
На сайте с 25.04.2008
Offline
137
#35
Dimanych:
Возможно кому-то интересно.
При оптимизации другой таблицы, которая значительно жирнее, выяснилось что mariadb очень плохо работает с MyIsam, никакие методы оптимизации, индексы и прочее не помогали, помогла только конвертация в Innodb, которая сразу дала прирос по всем тяжёлым запросам примерно на 50-70%. Не смотря на то что разные бенчмарки (возможно старые) говорят что MyIsam на выборку значительно быстрее, однако я установил множество тормозов как только начинаем использовать сортировки, и никакие индексы тут к сожалению не помогают. Поэтому если у кого то ещё есть MyIsam, советую конвертировать в Innodb :)

В MySQL 5.7 точно такая же ситуация с MyISAM. Одна и та же база может летать на 5.6, но тормозить на 5.7.

Рекомендуемый хостинг партнер 1С-Битрикс (https://www.ihead.ru/bitrix/), PHP-хостинг (https://www.ihead.ru/php/), доверенный партнер RU-CENTER (https://www.ihead.ru/news/573.html), официальный представитель REG.RU в Кирове (https://www.ihead.ru/news/851.html)
1 234

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий