Плохой план выполнения MySQL
Мне нужна помощь в понимании вывода данных, объясняя очень похожие запросы и огромное влияние на производительность. У меня есть 2 таблицы: анонс и геолокализация. Первый содержит объявления об аренде, а второй - соответствующее местоположение. Итак, ищем аренду в заданном месте. Если я использую план по умолчанию
EXPLAIN
SELECT a.*, g.label AS geo_label, g.geo_url
FROM annonce a
INNER JOIN geolocalisation g ON a.geolocalisation_id = g.geolocalisation_id
WHERE a.categorie_id = 1 AND g.gauche >= 151579 AND g.droite <= 151580
AND couchage >= 2
ORDER BY FIELD(provenance_id, 2, 1), prix DESC, date_modification DESC, annonce_id ASC
У меня время выполнения более 10 секунд.
+----+-------------+-------+------------+--------+---------------------------------+--------------+---------+------------------------------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------------------------+--------------+---------+------------------------------+--------+----------+----------------------------------------------------+
| 1 | SIMPLE | a | NULL | ref | geolocalisation_id,categorie_id | categorie_id | 4 | const | 502897 | 33.33 | Using index condition; Using where; Using filesort |
| 1 | SIMPLE | g | NULL | eq_ref | PRIMARY,droite,gauche | PRIMARY | 4 | vacamax.a.geolocalisation_id | 1 | 25.00 | Using where |
+----+-------------+-------+------------+--------+---------------------------------+--------------+---------+------------------------------+--------+----------+----------------------------------------------------+
Если я установлю индекс геолокации на "бестактный"
EXPLAIN
SELECT a.*, g.label AS geo_label, g.geo_url
FROM annonce a
INNER JOIN geolocalisation g ON a.geolocalisation_id = g.geolocalisation_id
WHERE a.categorie_id = 1 AND g.gauche >= 151579 AND g.droite <= 151580
AND couchage >= 2
ORDER BY FIELD(provenance_id, 2, 1), prix DESC, date_modification DESC, annonce_id ASC
У меня время выполнения .1 с.
+----+-------------+-------+------------+-------+---------------------------------+--------------------+---------+------------------------------+-------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------------------------+--------------------+---------+------------------------------+-------+----------+---------------------------------------------------------------------+
| 1 | SIMPLE | g | NULL | range | gauche | gauche | 4 | NULL | 52785 | 33.33 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | SIMPLE | a | NULL | ref | geolocalisation_id,categorie_id | geolocalisation_id | 5 | vacamax.g.geolocalisation_id | 13 | 16.66 | Using where |
+----+-------------+-------+------------+-------+---------------------------------+--------------------+---------+------------------------------+-------+----------+---------------------------------------------------------------------+
Результат - 188 строк. Кажется, что в первом случае проверяется слишком много строк, но во втором случае фильтрация эффективна: геолокализация - это фильтр, который следует применять ДО соединения: 1) вы получаете места, удовлетворяющие условиям 2) вы находите арендуемые места с такими размещает geolocalisation_id, сопоставляя таблицы. Просвети меня, пожалуйста.
couchage находится в таблице annonce
А как насчет INDEX(categorid_id, couchage)?
Ответы 1
Ты знает, что фильтровать геолокацию разумнее до, чем после, потому что вы знаете кое-что о своих данных и своем запросе, чего не знает MySQL.
В частности, MySQL предполагает, что он должен просмотреть строки 502897*1 в первом запросе и строки 52785*13=686205 во втором запросе, и решает использовать первый. Есть и другие факторы, которые влияют на решение, какой план выполнения использовать, но он дает вам приблизительное представление о том, как MySQL думает, как выглядят ваши данные. Это далеко от реальности (188 строк), и неудивительно, что решение, основанное на таких неверных предположениях, привело к неудачной стратегии.
Фактически, даже я знаю это только потому, что вы сказали мне и теперь можете предположить, основываясь на именах столбцов, что gauche всегда меньше, чем droite, поэтому ваше условие на g, вероятно, описывает очень узкое окно. Но MySQL этого не знает, поскольку вы не сказали этого MySQL, поэтому он не может принять это во внимание. И, конечно же, он не может принимать решения на основании значений имен столбцов.
Поскольку у вас есть индекс на gauge, для высокого значения (например, g.gauge >= your_max_value_in_that_column) MySQL фактически должен быть в состоянии обнаружить, что существует только несколько строк, и должен использовать лучший план выполнения. В противном случае MySQL в основном невежественен. Попробуйте изменять размер окна в очень широком диапазоне (например, g.gauche >= 100000 AND g.droite <= 200000); MySQL не будет показывать существенно другое число в rows, если вы не приблизитесь к пределам своих столбцов (и не укажете на них индекс). Для некоторых диапазонов первый запрос действительно должен выполняться быстрее, поскольку он приближается к распределению данных, которое предполагает MySQL.
Итак, как вы можете сообщить MySQL о вашем распределении данных?
Возможно, можно будет закодировать вашу информацию как пространственные данные (точка) и указатель на ней. Затем вы можете искать точки, которые лежат в 2-м прямоугольнике, и теперь MySQL может понять, что на самом деле это очень маленький прямоугольник, содержащий ограниченный объем данных. Не обязательно, чтобы ваши данные были на самом деле геометрическими данными, просто вы можете закодировать их в двух измерениях.
Предполагая, что мое предположение верно, вы также можете использовать (g.gauche = 151579 or g.gauche = 151580), и MySQL также должен понимать, что это только ограниченный объем данных.
И вы, конечно, можете просто принудительно создать индекс (или использовать FROM geolocalisation g STRAIGHT_JOIN annonce a). Вы знаете что-то, чего не знает MySQL, и часто вы не можете сказать MySQL иначе. Недостатком является то, что он не может адаптироваться к другим ситуациям, например. если вы (иногда) используете в запросе окна большего размера, или gauche <= droite больше не соответствует действительности.
OK. Во-первых, гош всегда меньше droite (что по-французски означает «левый и правый»): географическая система - это дерево, корень которого является «миром», поэтому трюк «влево / вправо» позволяет выбрать любой географический регион и все его потомки. В примере это всего лишь город, поэтому только один узел с разницей влево и вправо, равной 1. Во-вторых, "gauche" и "droite" являются индексированными значениями. В-третьих, mysql оценивает сложность одного плана по сравнению с другим и выбирает самый дешевый. Но как это так ошибочно? geolocalisation_id - это первичный ключ таблицы g, так почему он начинается с таблицы a?
Я понял, что это слева и справа, моя точка зрения заключалась в том, что MySQL этого не делает. Предположим, что идентификаторы идут от 1 до 1.000.000. Если вы скажете: gauge >= 100.000, MySQL может сказать: ну, наверное, 9/10 всех строк выше этого (на самом деле это не так точно, и гораздо лучше, если это уникальный индекс, поэтому вы должны использовать его, если возможно, это может улучшить вашу статистику), но вы можете получить yist. Если вы скажете droite <= 100.001, он может сказать: это может означать, что там находится около 1/10 всех строк. не мочь понимает, что это всего лишь 1 запись, потому что он не знает, что left < right.
В какой таблице содержится
couchage?