【mysql查询效率问题记录】不同电脑运行同一个sql的时间不同，线上使用了left join的查询效率低下的解决办法，尝试优化两个关联表的查询 sql使用join的关联字段字的字符集不同影响效率

小灰灰 2024-03-30 17:24 28阅读 0赞

#### 目录 ####

*   *  事件起因
     *  环境和工具
     *  过程
     *  解决办法总结
     *   *  该问题的常规排查方法：
     *  修改现有数据库的各个字段的字符集类型
     *  参考内容：
     *  结束语

### 事件起因 ###

不想看我的事件经过的，可以直接使用上面的目录跳转到 解决办法 的 该问题的常规排查方法 那儿去尝试那几种可能的办法！！！

事情是这样的：我们的一个系统已经上线很久了，但是在前段时间客户反馈系统用于发货的列表半天加载不出来（第一次报这个错的时候加载出来要30多秒，后面几次他们又提出这个问题的时候，直接几分钟都加载不出来了），为什么说有后面几次呢，因为他们刚开始提出这个问题的时候我们另一位开发说是当初这个数据结构就是这样设计的（而且还是客户那边的项目经理参与设计的），已经优化到极限了（反正就是找了理由推脱），后面这个列表越来越不行，时间越来越长，客户那边就反复提这个问题，推得过初一，推不过十五，我们这边也顶不住了，没办法了，那就优化吧！

对了，这个系统是一个比较久远的系统，而且已经 经手过很多次了，到我们手上的时候，已经有很多种代码风格揉合在一起了(简直就是一个大杂烩)，然后我们当初是在原有的基础上进行了部分新功能的开发，包括到现在为止也还在持续进行维护工作。

### 环境和工具 ###

数据库：mysql5.7

### 过程 ###

1、尝试去优化sql

我们尝试去优化sql，但其实这个优化并不理想，原本线上的数据库拉下来放到本地后，我们先试了一下差不多要70-80秒，这个时间已经非常离谱了，但我们还是想着去优化一下sql吧，然后优化出来，同等数据规模的情况下，原来的这个查询需要花费7-8秒

原来的那个查询效率慢的sql（这是控制台打印出来的，）：

SELECT
    	t.shanghai_order_id AS order_id,
    	t.task_number,
    	t.task_name,
    	t.doc_number AS orderNum,
    CASE
    		doc_type 
    		WHEN '采购入库' THEN
    		'入库单' 
    		WHEN '采购退货出库' THEN
    		'退货单' 
    		WHEN '领用出库' THEN
    		'出库单' 
    		WHEN '领用退库' THEN
    		'退库单' ELSE '异常单据' 
    	END AS order_type 
    FROM
    	sh_shanghai_order t
    	LEFT JOIN sh_ship_to_receive_task s ON s.shanghai_order_id = t.shanghai_order_id 
    WHERE
    	1 = 1 
    	AND t.doc_type = "领用出库" 
    	AND t.scan_item_flag = 'Y' 
    	AND s.id IS NULL 
    GROUP BY
    	t.shanghai_order_id 
    ORDER BY
    	t.make_date DESC 
    	LIMIT 1,20

笔者写到这儿时，上面那个sql的运行效率又提高了一些(因为之前对这个数据库的结构进行了一顿操作，写文章时是早已解决了这个问题，在复现这个问题时对该数据库的结构进行了复原到线上导下来的状态)，但还是要20多秒，如果在java中去运行的话，它还有个分页插件（它会先count计算一次总数来实现分页），又会去多查一次，然后总体的接口花费时间又翻倍了，直接40多秒  
![在这里插入图片描述][8fca503ecd3d489aa37b967c8e1da8aa.png]  
另一位开发尝试优化出来的sql：该优化主要是将原来的关联条件变为了现在的where的限制筛选的条件

SELECT
    	count( 0 ) 
    FROM
    	(
    	-- 0109下班前再次优化
    SELECT
      t.shanghai_order_id AS order_id,
      t.task_number,
      t.task_name,
      t.doc_number AS orderNum,
    CASE
        doc_type 
        WHEN '采购入库' THEN
        '入库单' 
        WHEN '采购退货出库' THEN
        '退货单' 
        WHEN '领用出库' THEN
        '出库单' 
        WHEN '领用退库' THEN
        '退库单' ELSE '异常单据' 
      END AS order_type 
    FROM
      sh_shanghai_order t
    
    WHERE
      1 = 1 
    
      AND t.doc_type = '领用出库'
      AND t.scan_item_flag = 'Y' 
      and t.shanghai_order_id not in (
        SELECT DISTINCT a.shanghai_order_id from sh_ship_to_receive_task a where a.shanghai_order_id like 'IM%'
      )
    GROUP BY
      t.shanghai_order_id 
    ORDER BY
      t.make_date DESC 
      LIMIT 1,20
    	) table_count

该优化后原本是要花70-80秒的查询，现在只需要花6-7秒，查询效率有了很大的提升，但其实还是不是很理想

在经历了好几次的查询优化和更新后，原本我们是打算就以这个6-7秒的查询的方式作为这次的优化方案（退一步想：这相比原来的70-80秒已经算是很大的优化了）

经过后面的查找资料和反复尝试，我们找到了这个查询缓慢的原因，数据库表的字符集

2、排查问题的过程中还发现了一个“神奇的情况”

同样的数据库（都是线上导出来，放本地运行出来的数据库），另一位同事的电脑运行优化出来的sql却只需要1点几秒，但在我的电脑上就是需要7-8秒，虽然这个7-8秒相比原来的（70-80秒）已经优化了很多了，10倍的效率了，但还是很奇怪，明明是相同的数据库数据，但就是不同的电脑上运行的效率不一样（之前我们俩的电脑也是查一个比较复杂的数据时运行的效率也不一样）

3、最后发现这个关联表的关联字段的字符集不一样（估计是以前数据库和代码都经手过太多次后遗留的问题），导致查询效率非常低下。  
可以看到上面的sql中的sh\_shanghai\_order表的shanghai\_order\_id和sh\_ship\_to\_receive\_task的shanghai\_order\_id的字符集不同，如下图所示：  
![字符集不同][3f502a61cef745c6b13b0b52de0858b5.png]  
当我把这两个字段修改为一样的后，就成功解决了查询效率慢的问题，

### 解决办法总结 ###

#### 该问题的常规排查方法： ####

1、遇到这种数据量不大（10w以下？），仅仅只是几张表关联（3张及以下数量的表），但是查询效率非常慢的情况 首先考虑关联的表的关联字段的字符集类型是否一样，其次考虑关联字段是否有索引，关联字段类型是否一样。

2、通过navicat的解释已选择的，对所选择的sql内容进行详细分析  
![在这里插入图片描述][b5c66401591e4846b2dbdc7817d7eb44.png]  
解释结果如下图所示：  
![解释已选择的结果][4bc9189e01074d39a7558a5d337e01c0.png]  
对于sql的解释的结果列每列的意义可以去看一下这篇文章：  
[https://blog.csdn.net/chirp\_CQ/article/details/129145902][https_blog.csdn.net_chirp_CQ_article_details_129145902]

### 修改现有数据库的各个字段的字符集类型 ###

指定数据库运行下面的sql：

-- 查看数据库中所有字段用的排序规则
    SELECT
    	TABLE_SCHEMA '数据库',
    	TABLE_NAME '表',
    	COLUMN_NAME '字段',
    	CHARACTER_SET_NAME '原字符集',
    	COLLATION_NAME '原排序规则',
    	CONCAT(
    	'ALTER TABLE ',
    	TABLE_NAME,
    	' MODIFY COLUMN ',
    	COLUMN_NAME,
    	' ',
    	COLUMN_TYPE,
    	-- - 设置新的编码和排序规则
    	' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
    	( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),
    	( CASE WHEN COLUMN_COMMENT = '' THEN ' ' ELSE concat( ' COMMENT''', COLUMN_COMMENT, '''' ) END ),
    	';' 
    ) '修正SQL' 
    FROM
    	information_schema.`COLUMNS` 
    WHERE
    	-- -过滤正确排序规则
    	COLLATION_NAME != 'utf8mb4_general_ci'
    	-- -数据库名称  使用你自己的数据库名
    	AND TABLE_SCHEMA = '你的数据库名';

使用前要修改里面的数据库名，并将字符集修改为你自己所要使用的字符集

我用了一个本地的测试数据库运行结果如下图所示：  
![在这里插入图片描述][470dfe6286874e498b01a825cc1b8a8c.png]  
运行结果的最后一列 “修正SQL” 就是要复制出来进行执行的sql，运行完之后，该数据库的所有涉及字符集的字段都修改了你指定的字符集（上面的案例指定的是utf8mb4 这个就是全修改为了该类型的字符集）

这一列的sql语句运行完成后再次运行前面的生成这个修改sql的sql 时运行结果就为空了（因为不再有非你指定类型的字符集的字段了）

### 参考内容： ###

最后我在网络上找到了对应的解决办法的文章：  
字符集不同无法走索引问题：[https://blog.csdn.net/yabingshi\_tech/article/details/106518193][https_blog.csdn.net_yabingshi_tech_article_details_106518193]  
另一篇文章：使用left join后执行很慢的问题解决办法[https://blog.csdn.net/zyypjc/article/details/128029514][https_blog.csdn.net_zyypjc_article_details_128029514]  
修改数据库的字符集：[https://blog.csdn.net/ccboy2009/article/details/128176768][https_blog.csdn.net_ccboy2009_article_details_128176768]

### 结束语 ###

若是对你有所帮助的话，希望能获得你的 点赞、评论、收藏，这将是对我很大的鼓励！！！ 这对我真的很重要！！！  
蟹蟹٩(‘ω’)و

[8fca503ecd3d489aa37b967c8e1da8aa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/10ab6c2ee3ce4d679ca40f8ef3ed4746.png
[3f502a61cef745c6b13b0b52de0858b5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/96750817b3c444f3893738886ae8a31c.png
[b5c66401591e4846b2dbdc7817d7eb44.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/8b2f60aee8a149b5a707a3be61f40fdd.png
[4bc9189e01074d39a7558a5d337e01c0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d21e3db922994c7f8a38773d817e65d6.png
[https_blog.csdn.net_chirp_CQ_article_details_129145902]: https://blog.csdn.net/chirp_CQ/article/details/129145902
[470dfe6286874e498b01a825cc1b8a8c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/e273b27b95cb423b976516c18272df2a.png
[https_blog.csdn.net_yabingshi_tech_article_details_106518193]: https://blog.csdn.net/yabingshi_tech/article/details/106518193
[https_blog.csdn.net_zyypjc_article_details_128029514]: https://blog.csdn.net/zyypjc/article/details/128029514
[https_blog.csdn.net_ccboy2009_article_details_128176768]: https://blog.csdn.net/ccboy2009/article/details/128176768