MySQL-如何添加“使用连接缓冲区(块嵌套循环)"查询? [英] MySQL - how to add "Using join buffer (Block Nested Loop)" to a query?
问题描述
当我在笔记本电脑上运行查询时，执行该查询大约需要一秒钟，但是在生产环境中，查询持续57秒(并且崩溃了应用程序-用Ruby On Rails编写).
When I run a query on my laptop, it takes like a second to execute, but in the production environment, the query lasts 57 seconds (and it crashes the applications - that's written in Ruby On Rails).
我使用 EXPLAIN 运行了该查询，结果发现笔记本电脑上的笔记本电脑位于执行查询的一张桌子上
I ran this query with EXPLAIN and found out that on my laptop is on one table in the query executed
使用连接缓冲区(块嵌套循环)
Using join buffer (Block Nested Loop)
但这在生产中是缺少的(对于此表， EXTRA 列中没有任何内容).
but this is missing in production (for this table, there's nothing in the EXTRA column).
那是为什么?
谢谢
:这是查询:
SELECT `shippers`.`company_name`
FROM `shippers`
LEFT OUTER JOIN `shipper_services`
ON `shipper_services`.`shipper_id` = `shippers`.`id`
LEFT OUTER JOIN `services` ON `services`.`id` = `shipper_services`.`service_id`
LEFT OUTER JOIN `shipper_freight_flow_industries`
ON `shipper_freight_flow_industries`.`shipper_id` = `shippers`.`id`
LEFT OUTER JOIN `freight_flow_industries`
ON `freight_flow_industries`.`id` = `shipper_freight_flow_industries`.`freight_flow_industry_id`
WHERE `shippers`.`id` IN (189459, 337334, 149804, 36046, 158403, 165682, 153399, 51471, 211796, 164984, 61118, 56497, 340353, 66525, 225635, 250376, 33237, 69140, 151777, 169530, 245255, 142246, 259597, 57889, 262986, 167803, 33459, 40561, 65878, 44356, 169545, 210358, 25555, 4563, 82538, 157765, 232509, 213248, 37380, 258965, 340616, 340926, 143314, 195553, 60845, 161463, 255789, 38942, 192219, 811, 64672, 13530, 340809, 66030, 157223, 347671, 176886, 157812, 348255, 37357, 337003, 5588, 24257, 164799, 230717, 153801, 171835, 66595, 176780, 250184, 3262, 201519, 223904, 241992, 254167, 242449, 5368, 23903, 52571, 7198, 40135, 340494, 11851, 171285, 26810, 231003, 193961, 341161, 17122, 56660, 348103, 145539, 176912, 19716, 196617, 34803, 75444, 62418, 149606, 158879, 242439, 63291, 80548, 170778, 184871, 254549, 337109, 151159, 255077, 38939, 191926, 337720, 205999, 247471, 258106, 40225, 52599, 141637, 207246, 247541, 258876, 52629, 65936, 164884, 192238, 247588, 13669, 26875, 41763, 52700, 143009, 154515, 174092, 192869, 210753, 248501, 13835, 27222, 43017, 52995, 154640, 165368, 176390, 235034, 248829, 261492, 16610, 29589, 43109, 143661, 195373, 211866, 236874, 33148, 67629, 145474, 166592, 212358, 236937, 263276, 353, 18193, 44479, 68389, 196133, 241530, 251862, 331361, 770, 45861, 68982, 145652, 157945, 177022, 214534, 241659, 253705, 332487, 349455, 20303, 46852, 61001, 147373, 158198, 178036, 200139, 220189, 241765, 253987, 334046, 350465, 21532, 46970, 149478, 170761, 178187, 334167, 350466, 3285, 22934, 48334, 77067, 170770, 184809, 201905, 224892, 254293, 23063, 51366, 203181, 37607, 63370, 80720, 163426, 170798, 203424, 226486, 243809, 244904, 63388, 81420, 163553, 170800, 203819, 228767, 244936, 6057, 24664, 52584, 152229, 164080, 170812, 192096, 229759, 257935, 25300, 52592, 65635, 82575, 152798, 164171, 170817, 192179, 206647, 229772)
AND ((
(ACOS(least(1,COS(0.5194174327134307)*COS(-1.664517065837707)*COS(RADIANS(shippers.latitude))*COS(RADIANS(shippers.longitude))+
COS(0.5194174327134307)*SIN(-1.664517065837707)*COS(RADIANS(shippers.latitude))*SIN(RADIANS(shippers.longitude))+
SIN(0.5194174327134307)*SIN(RADIANS(shippers.latitude))))*3963.1899999999996)
<= 5.0)) AND (store_location = "0" AND corporate_hq = "0" AND queued_item="0")
GROUP BY company_name ORDER BY count_of_facilities DESC)
生产:
+----+-------------+---------------------------------+--------+-------------------+-----------+---------+-----------------------------------------------------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------------+--------+-------------------+-----------+---------+-----------------------------------------------------------------------+--------+----------------------------------------------+
| 1 | SIMPLE | shippers | range | PRIMARY,idx_store | PRIMARY | 4 | NULL | 245 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | shipper_services | ref | idx_combo | idx_combo | 5 | db_production.shippers.id | 2 | Using index |
| 1 | SIMPLE | services | eq_ref | PRIMARY | PRIMARY | 4 | db_production.shipper_services.service_id | 1 | Using index |
| 1 | SIMPLE | shipper_freight_flow_industries | ALL | NULL | NULL | NULL | NULL | 241609 | |
| 1 | SIMPLE | freight_flow_industries | eq_ref | PRIMARY | PRIMARY | 4 | db_production.shipper_freight_flow_industries.freight_flow_industry_id | 1 | Using index |
+----+-------------+---------------------------------+--------+-------------------+-----------+---------+-----------------------------------------------------------------------+--------+----------------------------------------------+
5 rows in set (0.00 sec)
索引:
+---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| shipper_freight_flow_industries | 0 | PRIMARY | 1 | id | A | 241609 | NULL | NULL | | BTREE | | |
+---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
本地主机:
+----+-------------+---------------------------------+--------+-------------------+-----------+---------+-----------------------------------------------------------------------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------------+--------+-------------------+-----------+---------+-----------------------------------------------------------------------------------+------+----------------------------------------------------+
| 1 | SIMPLE | shippers | range | PRIMARY,idx_store | PRIMARY | 4 | NULL | 245 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | shipper_services | ref | idx_combo | idx_combo | 5 | development.shippers.id | 2 | Using index |
| 1 | SIMPLE | services | eq_ref | PRIMARY | PRIMARY | 4 | development.shipper_services.service_id | 1 | Using index |
| 1 | SIMPLE | shipper_freight_flow_industries | ALL | NULL | NULL | NULL | NULL | 58 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | freight_flow_industries | eq_ref | PRIMARY | PRIMARY | 4 | development.shipper_freight_flow_industries.freight_flow_industry_id | 1 | Using index |
+----+-------------+---------------------------------+--------+-------------------+-----------+---------+-----------------------------------------------------------------------------------+------+----------------------------------------------------+
索引:
+---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| shipper_freight_flow_industries | 0 | PRIMARY | 1 | id | A | 58 | NULL | NULL | | BTREE | | |
+---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
推荐答案
这篇文章确实提出了2个问题.
This post really asks 2 questions.
隐式的内容类似于如何在生产系统上提高此查询的性能?".
正如@Fabricator已经注意到的那样，答案是在shipper_id上的索引上添加到shipper_freight_flow_industries表"，由于所涉及的表的大小，一旦连接创建了大量的组合，这是推荐的方法.
The implicit one is something like "How can I improve performance for this query on my production system?".
As already noticed by @Fabricator, the answer is "Add an index on shipper_id to the shipper_freight_flow_industries table", which is the recommended way as soon as joins create a huge number of combinations due to the size of the involved tables.
关于明确的一个:
那是为什么?
Why is that? How to add Using join buffer (Block Nested Loop) also to production?
时间差显然来自shipper_freight_flow_industries表的大小:笔记本电脑数据库中为58，生产数据库中为241,609.
The time difference obviously comes from the size of the shipper_freight_flow_industries table: 58 in the laptop DB, 241,609 in the production DB.
并且只有笔记本电脑使用连接缓冲区的事实可能是由于join_buffer_size变量引起的(请参见
And the fact that only the laptop uses a join buffer is likely due to the join_buffer_size variable (look at this MySql page): it's probably set to its default value (256K) on both systems.
So it's used on the laptop DB, but on the production system it's too small to allow using it for the number of rows involved.
如上所述，添加索引是提高此查询性能的首选方法，但是尝试使用非常大的没有索引的join_buffer_size并比较结果可能会很有趣.
在某些特殊情况下，此解决方案可能是一个不错的选择，例如，如果该索引除了用于此查询之外没有其他用途，并且还驻留其他任务(例如表更新)，则为该解决方案.
As stated above, adding an index is the preferred way to improve this query performance, but it might be interesting to try using a very large join_buffer_size without an index, and compare the results.
This solution might be a good alternative in some peculiar cases, such as if this index has no other use than for this query and if furthermore it's dwelling other tasks like its table updates.
这篇关于MySQL-如何添加“使用连接缓冲区(块嵌套循环)"查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
