性能文章>掉坑了!GROUP_CONCAT函数引发的线上问题>

掉坑了!GROUP_CONCAT函数引发的线上问题原创

1年前
567742

image.png
本文分享一篇在工作遇到的一个问题,关于MySQL GROUP_CONCAT函数导致的问题。希望能帮忙到你。

业务场景

在说遇到的坑之前,先描述一下大致的业务场景。系统有一个排班的功能,一个医生一天可以排多种业务类型的排班,并且每一种业务类型的排班都支持排个多时段(时段的最小单位是15分钟)。

举例:
D医生在 2020-12-31 的 8:00~10:00、10:30~12:00  排班了【在线A】 和 【在线B】业务。

【在线A】 2020-12-31 8:00~10:00
【在线A】 2020-12-31 10:30~12:00
【在线B】 2020-12-31 8:00~10:00
【在线B】 2020-12-31 10:30~12:00

在查询这个一天排班的数据,伪SQL方便理解:

SELECT
 dt,
 biz_type,
 GROUP_CONCAT(
  CONCAT_WS('|', id, start_time, end_time)
 ) AS multiPeriodDetail
FROM
 doctor_schedule_detail
WHERE
 doctor_id = '456231'
AND dt = '2020-12-30'
GROUP BY
 doctor_id,
 biz_type,
 dt

CONCAT_WS(’|’, id, start_time, end_time)中的字段说明:

  • id:时段的id
  • start_time:时段开始时间
  • end_time:时段结束时间

对数据进行分组后,使用 CONCAT_WS函数 指定分隔符进行参数拼接。然后使用 GROUP_CONCAT 函数 返回分组中字符串结果组合连接的值,默认缺省为一个逗号 (",")进行分隔,这个缺省值可以去掉,本例子中使用默认缺省值。

查询后结果展示如下:
image.png
查询结果
至此场景描述结束。

遇到的坑

这个代码在线上已经运行很长了,有一天用户反馈说给D医生排班了,但是界面查不到这个时段的排班数据。

经过"紧张激烈"的排查,终于找到了这个问题的罪魁祸首。

如上场景描述,在查询SQL中使用了 GROUP_CONCAT 函数,在业务中因为排班的最小时段为:单位是15分钟。正常医生不会拍很多很细的班,但在特殊的场景中,有一些医生就是15分钟排一次班,而且系统中真实的 CONCAT_WS函数拼接返回的字段比较多,那么导致查询出来的数据非常多, GROUP_CONCAT 函数就就大于默认值得数据丢弃了。

注:GROUP_CONCAT 对应的配置参数 group_concat_max_len 默认设置最大的长度 1024字节。

查询 group_concat_max_len 配置:

show variables like "group_concat_max_len";

tips:字段内容字节长度

MySQL utf-8 编码集, 一个中文占3个字节。

-- 字符长度 输出结果:6
SELECT CHAR_LENGTH("你好2021")
-- 字节长度 输出结果:10
SELECT LENGTH("你好2021")

解决方案

在知道问题原因后,就有了对应的解决方案。
第一种方案:修改Mysql的配置,调整 group_concat_max_len 的值。

1 方法一:在MySQL的配置文件中加入如下配置(推荐):
2 
3 group_concat_max_len = 102400
4 方法二:更简单的操作方法,执行SQL语句:
5 
6 SET GLOBAL group_concat_max_len = 102400;
7  
8 SET SESSION group_concat_max_len = 102400;
9

生产环境,肯定要用方法一:在MySQL的配置文件。但考虑生产环境修改配置需要走一些流程和审批,于是有了第二种方案的考虑。

第二种方案:进行业务功能的调整,需求的调整或者GROUP_CONCAT 拼接少返回一些数据

功能已经上线,此时在修改,对业务有一定的影响
综合考虑后,决定更改group_concat_max_len的值,经过对业务的分析计算出具体要更改的值。

结语

在真实场景中,大多数问题的解决方案不止一种,在权衡后找到一个符合当时的问题的最优解决方案。

请先登录,再评论

你也是医疗行业,有机会交流交流😁

11年前

之前同事也遇到 group_concat的问题,也是因为数据太长,导致数据丢失,也是通过第一种方案修改配置来解决的

11年前

这种业务用GROUP_CONCAT感觉就是个隐患

1年前

因基本不用GROUP_CONCAT,所以又回头找了下其用法解释:在 MySQL 中,你可以得到表达式结合体的连结值。通过使用 DISTINCT 可以排除重 复值。如果希望对结果中的值进行排序,可以使用 ORDER BY 子句。
SEPARATOR 是一个字符串值,它被用于插入到结果值中。缺省为一个逗号 (","),可以通过指定 SEPARATOR "" 完全地移除这个分隔符。
可以通过变量 group_concat_max_len 设置一个最大的长度。
这种时间排班业务,其实是可以整合在一个字段去做查询,做好这个字段的维护,会比使用函数去查询要方便,也更容易找到问题。

21年前
回复 Loubobooo:

这。。可以,学到了👍

1年前回复

为你推荐

MySQL之KEY分区引发的血案
需求背景业务表tb_image部分数据如下所示,其中id唯一,image_no不唯一。image_no表示每个文件的编号,每个文件在业务系统中会生成若干个文件,每个文件的唯一ID就是字段id:业务表t
MySQL 死锁套路:一次诡异的批量插入死锁问题分析
线上最近出现了批量insert的死锁,百思不得解。死锁记录如下:```2018-10-26T11:04:41.759589Z 8530809 [Note] InnoDB: (1) TRANSACTI
MySQL 死锁套路:唯一索引下批量插入顺序不一致
死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。先来做几个小实验,简化的表结构如下:```CREATE TABL
MySQL 死锁套路:再来看一例走不同索引更新的例子
前面有文章介绍了利用调试MySQL源码的方式来调试锁相关的信息,这里利用这个工具来解决一个比较简单的问题,线上的表字段较多,这里简单成为了一个表:```CREATE TABLE `t3` ( `id
如何在 Mac 下用 Clion 调试 MySQL 源码
前面写了几篇文章来通过调试 MySQL 源码来分析死锁问题,有读者问如何用 IDE 调试源码,这篇文章简单介绍一下如何在 Mac 下调试。之所以使用调试的方式来分析死锁问题是因为在解决 MySQL 死
掉坑了!GROUP_CONCAT函数引发的线上问题
本文分享一篇在工作遇到的一个问题,关于MySQL GROUP_CONCAT函数导致的问题。希望能帮忙到你。 业务场景在说遇到的坑之前,先描述一下大致的业务场景。系统有一个排班的功能,一个医生一天可以排
空中楼阁之纸上谈兵 mysql的dbcp的配置
maxWait=-1 最大等待时间:当没有可用连接时,连接池等待连接被归还的最大时间(以毫秒计数),超过时间则抛出异常,如果设置为-1表示无限等待testOnBorrow=true指明是否在从池中取出
一个诡异的MySQL查询超时问题,居然隐藏着存在了两年的BUG
这一周线上碰到一个诡异的BUG。线上有个定时任务,这个任务需要查询一个表几天范围内的一些数据做一些处理,每隔十分钟执行一次,直至成功。通过日志发现,从凌晨5:26分开始到5:56任务执行了三次,三次都