MySQL left join 查询巨慢优化

左手的ㄟ右手 2022-10-22 04:19 286阅读 0赞

### MySQL left join 查询巨慢 优化 ###

最近工作中遇到一个非常奇怪的问题，mysql中有两张表，test\_info和test\_do\_info需要进行LEFT JOIN关联查询，每张表又都1W+的数据，关联查询需要12s之久。  
按照常理来说1W+的数据关联查询应该很快，即使进行全表扫描也不至于12s。至于索引在这么小的数据量下作用应该不大（这里指的是查询走索引与不走索引的速度）。

sql与执行计划如下：

select * from (
    select a.* from test_info a LEFT JOIN test_do_info b on a.id = b.teleg_id
    ) aa ORDER BY create_time desc;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0eWxlNjY2Ng_size_16_color_FFFFFF_t_70_pic_center]

网上很多答案：1，由于数据库两张表的字段编码不一致导致的。2，由于Using filesort排序导致的。3，由于没有走索引导致的。

我挨个排查了下：

1，由于数据库中两张表的编码字段不一致：

show full columns from test_info;
    show full columns from test_do_info;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0eWxlNjY2Ng_size_16_color_FFFFFF_t_70_pic_center 1]  
查询后比对结果，两张表的字段编码完全一致。所有第一种可能性排除。

2，由于Using filesort排序导致的。

在我的这条SQL种主要是由于使用了ORDER BY语句导致的，去掉ORDER BY后Using filesort自然会消失。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0eWxlNjY2Ng_size_16_color_FFFFFF_t_70_pic_center 2]  
filesort消失了，但是查询速度还是没有跟上来，所以主要的原因还是没有找到。

3，由于没有走索引导致的。  
给两张表创建索引：

create index test_info_index on test_info(id);
    create index test_do_info_index on test_do_info(id,teleg_id);

创建完索引后在查看执行计划：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0eWxlNjY2Ng_size_16_color_FFFFFF_t_70]  
可以看到已经使用了关联表索引，但是速度还是没有上来。

这就奇怪了，为啥速度还是没有上来？后来突然发现了执行计划中的异常：**Using join buffer (Block Nested Loop)**

这个是在干嘛？可能是这个引起的。经过百度的一番搜索，发现真是由他引起的。  
为什么会走join buffer，主要是因为 1，索引创建不正确。2，没有走索引。

解决方案：  
1，索引我们在第三步已经创建了，但是我们创建的顺序不对。将test\_do\_info表的关联字段和主键的索引顺序调换即可。

alter table test_do_info drop index test_do_info_index;
    create index test_do_info_index on test_do_info(teleg_id,id);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0eWxlNjY2Ng_size_16_color_FFFFFF_t_70_pic_center 3]  
执行计划中已经没有 **Using join buffer (Block Nested Loop)** 了。在执行下原SQL快到飞起，那么我们在不创建索引下还是会很慢，那是由于我们在创建表主键时，会自动在主键列创建索引。所以还是会引起**Using join buffer (Block Nested Loop)** 问题。

2，不创建索引，直接使用派生表 (select \* from test\_info)。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0eWxlNjY2Ng_size_16_color_FFFFFF_t_70_pic_center 4]  
执行计划中什么都没有，不建议这种修改。可以比对一下上一种的修改方式，这种方式的查询速度明显要慢与创建索引的方式。

如果您在上述文章中发现有错误的地方欢迎您指出来我会积极去改正。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0eWxlNjY2Ng_size_16_color_FFFFFF_t_70_pic_center]: /images/20221022/07832c60ef7f43ef9eaa32bc6f599b57.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0eWxlNjY2Ng_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221022/10c2ee3b84f1436d99e6724c864c08ca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0eWxlNjY2Ng_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221022/673fdfcfa0814d10a2ac471d5e783540.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0eWxlNjY2Ng_size_16_color_FFFFFF_t_70]: /images/20221022/85ffe391c24047668ea8446413fc70aa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0eWxlNjY2Ng_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221022/f86d6b255289446a9a612f796edb6ac8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N0eWxlNjY2Ng_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221022/4743243d5f4940c0be79c5a3086292e9.png