当我在笔记本电脑上运行查询时,执行需要一秒钟,但在生产环境中,查询持续57秒(并且它会崩溃应用程序 - 用Ruby On Rails编写).
我用EXPLAIN运行了这个查询,发现我的笔记本电脑在查询执行的一个表上
使用连接缓冲区(块嵌套循环)
但这在生产中缺失(对于这个表,EXTRA列中没有任何内容).
这是为什么？如何添加使用连接缓冲区(Block Nested Loop)也用于生产？
谢谢
编辑:这是查询:
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 | | | +---------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
cFreed.. 10
这篇文章真的问了两个问题.
隐含的是"如何在我的生产系统上提高此查询的性能？".
正如@Fabricator已经注意到的那样,答案是" shipper_id在shipper_freight_flow_industries表上添加一个索引",一旦连接由于涉及的表的大小而创建了大量的组合,这是推荐的方法.
关于明确的一个:
这是为什么？如何添加使用连接缓冲区(Block Nested Loop)也用于生产？
时间差显然来自shipper_freight_flow_industries表的大小:笔记本电脑DB中的58,生产DB中的241,609.
而且只有笔记本电脑使用连接缓冲区的事实可能是由于join_buffer_size变量(看看这个MySql页面):它可能在两个系统上都设置为默认值(256K).
因此它在笔记本电脑数据库上使用,但在生产系统上它太小,不允许使用它来处理所涉及的行数.
如上所述,添加索引是提高此查询性能的首选方法,但尝试使用非常大join_buffer_size而没有索引可能会很有趣,并比较结果.
在某些特殊情况下,此解决方案可能是一个很好的替代方案,例如,如果此索引没有其他用途而不是此查询,并且此外它还会像其表更新那样执行其他任务.
这篇文章真的问了两个问题.
隐含的是"如何在我的生产系统上提高此查询的性能？".
正如@Fabricator已经注意到的那样,答案是" shipper_id在shipper_freight_flow_industries表上添加一个索引",一旦连接由于涉及的表的大小而创建了大量的组合,这是推荐的方法.
关于明确的一个:
这是为什么？如何添加使用连接缓冲区(Block Nested Loop)也用于生产？
时间差显然来自shipper_freight_flow_industries表的大小:笔记本电脑DB中的58,生产DB中的241,609.
而且只有笔记本电脑使用连接缓冲区的事实可能是由于join_buffer_size变量(看看这个MySql页面):它可能在两个系统上都设置为默认值(256K).
因此它在笔记本电脑数据库上使用,但在生产系统上它太小,不允许使用它来处理所涉及的行数.
如上所述,添加索引是提高此查询性能的首选方法,但尝试使用非常大join_buffer_size而没有索引可能会很有趣,并比较结果.
在某些特殊情况下,此解决方案可能是一个很好的替代方案,例如,如果此索引没有其他用途而不是此查询,并且此外它还会像其表更新那样执行其他任务.
这是Mysql https://bugs.mysql.com/bug.php?id=69721的错误
您可以使用以下命令运行查询
set optimizer_switch='block_nested_loop=off'
京公网安备 11010802040832号 | 京ICP备19059560号-6 