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

最新推荐文章于 2024-04-30 06:21:15 发布

阿，葱来了-C is coming

最新推荐文章于 2024-04-30 06:21:15 发布

阅读量2.2k

点赞数 1

分类专栏：大后端开发学习文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/chirp_CQ/article/details/128640996

版权

大后端开发学习专栏收录该内容

25 篇文章 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多秒
在这里插入图片描述
另一位开发尝试优化出来的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的字符集不同，如下图所示：

当我把这两个字段修改为一样的后，就成功解决了查询效率慢的问题，

解决办法总结

该问题的常规排查方法：

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

2、通过navicat的解释已选择的，对所选择的sql内容进行详细分析
在这里插入图片描述
解释结果如下图所示：
解释已选择的结果
对于sql的解释的结果列每列的意义可以去看一下这篇文章：
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 = '你的数据库名';

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

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

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

参考内容：

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

结束语

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

阿，葱来了-C is coming

关注

1
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
【mysql查询效率问题记录】不同电脑运行同一个sql的时间不同，线上使用了left join的查询效率低下的解决办法，尝试优化两个关联表的查询 sql使用join的关联字段字的字符集不同影响效率

对查询效率低下的一种解决办法，多个表的字符集不同时，涉及这些表的联合查询所需执行的办法，批量修改数据库内所有字段的字符集的办法
复制链接

扫一扫