我想通过解释非常相似的查询来帮助理解数据输出，以及对性能的巨大影响。我有两张表：annonce和geologisation。第一个包含租赁广告，第二个包含相应的位置。所以，我们在一个特定的地方搜索租房。如果我使用默认计划
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）您通过匹配表找到具有这些位置地理定位id的租用。请开导我。
1条答案
按热度按时间cngwdvgl1#
你知道过滤地理位置比过滤后更聪明，因为你知道一些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您的数据分布呢？
可以将信息编码为空间数据（一个点）和索引。然后你可以在二维矩形中寻找点，mysql现在可以理解这实际上是一个包含有限数据量的非常小的矩形。它并不要求你的数据实际上是几何数据，只是你可以编码它在2维。
假设我的假设是正确的，你也可以使用
(g.gauche = 151579 or g.gauche = 151580)，mysql也应该能够理解这只是有限的数据量。当然，您可以强制索引（或使用
FROM geolocalisation g STRAIGHT_JOIN annonce a). 你知道一些mysql不知道的事情，通常，你不能告诉mysql。缺点是，这不能适应其他情况，例如，如果您（偶尔）在查询中使用较大的窗口，或者gauche <= droite不再是真的了。