前言
最近工作中遇到一个比较好玩的事情，BUG 本身不难，但解决问题的过程比较曲折，细节较多，值得记录...
系统中有两张表 record 和 detail 存在一对多的关系，一条 record 表记录，对应着多条 detail 记录。detail 表中使用 record_id 列进行关联。record 目前 12w+ 数据量，detail 47w+。表结构大致如下所示：
-- record
+─────+──────────+──────────+
| id | cloumn1 | cloumn2 |
+─────+──────────+──────────+
-- detail
+─────+────────────+────────────+──────────────+
| id | record_id | input_key | input_value |
+─────+────────────+────────────+──────────────+
对于这两个表，现在前端页面上有两个功能，按照条件分页查询 record、导出符合条件的查询 record 记录，两个功能对应的是同一个接口，与前端（另一个后端系统）规定了接口形式如下：
{
"record_cloumn_1":"",
....
"record_cloumn_n":"",
"detail_column_1":"",
...
"detail_column_n":""
}
其中 detail_column 开头的 json key 数量不固定（动态变化），取决于该 record 对应了几条 detail。这些 key 对应着 detail 某一列的值，value 对应另一列的值。有点难理解，打个比方如下：
-- record id 为 1
+─────+──────────+──────────+
| id | cloumn1 | cloumn2 |
+─────+──────────+──────────+
| 1 | value1 | value2 |
+─────+──────────+──────────+
-- detail 关联着 id 为 1 的 record
+─────+────────────+────────────+──────────────+
| id | record_id | input_key | input_value |
+─────+────────────+────────────+──────────────+
| 1-1 | 1 | F1 | F1value |
+─────+────────────+────────────+──────────────+
| 1-2 | 1 | F2 | F2value |
+─────+────────────+────────────+──────────────+
转化成前端接口：
{
"id":"1",
"cloumn1":"value1",
"cloumn2":"value2",
"F1":"F1value",
"F2":"F2value"
}
性能问题
起因在于导出一个 2000+ excel 时出现超时，在后端系统交互的接口中，15 秒超时时间不算短，于是开始着手排查。一眼就看到了如下代码（伪代码）：
public ResultContext<?> list(QueryParam p) {
Page<Object> pageInfo = PageHelper.startPage(p.getPageNum(), p.getPageSize(), p.getOrderBy());
List<RecordPo> recordPos = recordMapper.query(p);
//
for (RecordPo po : recordPos) {
List<DetailPo> detailPos = detailMapper.queryByRecordId(po.getId());
// 省略拼装
}
return ResultContext.success(...);
}
很明显，典型的实习生代码，for 循环里面进行 select。当参数 QueryParam 中的 pageSize 值不大时，性能问题并不容易暴露，一旦这个值变大，如出现问题时 pageSize=10000，就很容易出现超时之类的性能问题。
重写
因为之前的代码，过于丑陋（前面贴出来的还是美化过的），用 Map 传参、for 循环里做大段逻辑等等，所以当时接到工单的我脑子只想全删了重写。重写后代码：
public ResultContext<?> list(QueryParam p) {
Page<Object> pageInfo = PageHelper.startPage(p.getPageNum(), p.getPageSize(), p.getOrderBy());
List<RecordDto> recordPos = recordMapper.queryBy(p);
// 省略 Dto -> Vo 过程
return ResultContext.success(...);
}
重点在于 SQL，因为 record 对 detail 是 1 对 n。所以连表查询后，记录数为 n。说到底还是一个 行转列 的过程，将不同的列合并成一个字段。经查询 MySQL 的 GROUP_CONCAT 方法能够满足要求，再加上子查询，顺利将 detail 表的多行转化为 record 表的一列，大致 SQL 如下：
<select id="queryBy">
SELECR r.*,
(
SELECT GROUP_CONCAT(CONCAT(d.input_key,':', d.input_value))
FROM detail d WHERE d.record_id=r.id
) AS input_detail
FROM record r WHERE 1=1
<!-- 省略条件 -->
</select>
结果集大概如下：
+─────+──────────+──────────+────────────────────────+
| id | cloumn1 | cloumn2 | input_detail |
+─────+──────────+──────────+────────────────────────+
| 1 | value1 | value2 | F1:F1value,F2:F2value |
+─────+──────────+──────────+────────────────────────+
input_detail 字段使用列与列之间使用 : 进行分割，不同的记录行之间使用 , 分割（MySQL GROUP_CONCAT 方法默认）。程序中只要将该拼装规则拆解开，重新组装为 web 接口所需要的 JSON 格式即可完成，此处省略。
！注！：MySQL 中
GROUP_CONCAT方法生成的字符串长度有限制默认是 1024 个字节，支持配置。具体见 官方文档对该方法的解释
缺陷产生
问题出在链接符上，在 SQL 中 detail 表中的 input_key 和 input_value 字段之间是使用 : 进行分割，程序在解析时，也是按照此规则进行解析，而恰好 input_value 字段值是用户界面上输入的，刚好又包含了 :，于是乎代码解析错乱了。
第 1 次修改
分配到修复的并不是我，我只负责进行代码评审。组员很痛快地改完了一版，放弃了我的 GROUP_CONCAT 方法，直接将 record 和 detail 进行左连接，然后在内存进行记录合并。大致代码如下：
<select id="queryBy">
SELECT r.*, d.input_key, d.input_value
FROM record r LEFT JOIN detail d on r.id=d.record_id
WHERE 1=1
<!-- 省略条件 -->
</select>
查到记录后，接着在内存中，对相同 id 的 record 记录进行合并，组装前端 JSON 报文，此处省略。
乍一看，确实是解决了特殊字符分割问题，其实这里引入了一个非常容易被忽略的问题：连表分页问题
前面说到这个接口有 分页查询 和 导出 两个功能，而分页这个功能针对的又是 record 表，所以当代码中 record 和 detail 表进行连接后，假设 record 为 1，detail 为 n，那查出来的记录数就是 1*n，分页对象就发生了变化。
举个例子，当你 pageSize 传 10 时，本意是想获取到 10 条 record 记录，但是由于在代码里进行了连表，其实你是获取到了 10 条的 detail，接着又在内存里进行了相同记录的合并，此时接口返回的实际数量就会少于 10。
小结：当一个接口有分页功能，千万注意 SQL 中的 连表 操作
跟组员解释后，进行了第二次修改...
第 2 次修改
由于分页这个功能，连表这个操作肯定得放弃了，于是选择了 MyBatis 一对多的功能，也就是 resultMap 标签的子标签 collection，XML 文件大致如下：
<resultMap id="queryByResult" type="xxx.xxx.RecordDTO">
<id column="id" property="id" />
<!-- 省略一些属性 -->
<!-- 将 id 属性传值到 queryDetails 方法中 -->
<collection property="details" ofType="xxx.xxx.DetailPo" select="queryDetails" column="id" />
</resultMap>
<!-- 外界调用，查询 record 方法 -->
<select id="queryBy" resultMap="queryByResult">
SELECT r.* FROM record r WHERE 1=1
</select>
<!-- 内部调用，查询 detail 方法 -->
<select id="queryDetails" resultType="xxx.xxx.DetailPo">
select d.input_key,d.input_value
from mh_payment_record_detail d
WHERE d.record_id = #{id}
</select>
当时提交后，看代码确实没看出什么问题，于是尝试着使用生产参数进行了测试，指定 pageSize 指定为 10，查看发现日志有些不对，截图如下：
说简单点就是 MyBatis 分别执行了两条 SQL，并没有做什么优化操作。当调用完 queryBy 方法之后，迭代调用 queryDetails 方法查询明细表。熟悉的套路，感觉就是对实习生那段代码，进行了一次封装？性能慢到爆。
...又回到最初的起点？这次又打回一版，组员继续吭哧吭哧改...
第 3 次修改
经过前两次修改，组员对业务已是很熟悉，这次为了解决 detail 表查询次数过多问题，先查 record 表分好页，再用 in 语句查询 detail，大致思路就是这样子。伪代码这里就不贴了，比较简单，需要注意的是，MySQL 对 in 语句里边值的数量似乎有限制，如果一页 record 数量过多时，不能一股脑全塞进去，还得分多次查询。
！注！：MySQL 对 in 语句值没有限制，但对整个 SQL 的长度有限制，默认为 4m。详见 官方文档对 in 方法的解释
这次修改相对于版本 2，效率上面有非常大的提升，但是和版本 1 比起来还是有差距，不过也可算是对这个问题划上了一个句号。目前看来版本 1 除了链接符问题似乎是最优解，这里也没什么好的方案解决，唯一能想到的是不是直接将需要转列的字段拼成 JSON 格式就好？但是这个 SQL 的复杂度着实令人着急，让后面的人怎么维护？
小结
回过去看看，这么多次的修改，无非就是在平衡分页查询和导出记录两个功能。说到底，是不是该怀疑接口设计上面存在缺陷？为了简单与客户端约定导出功能只是将 pageSize 这个值传成无限大，殊不知 BUG 修复与后期的返工时间远大于当初省下的。不过这里情况也属实特殊，表关系较复杂，没有经验确实很难做好设计。
正是验证了那句话，魔鬼在细节！！
补充（20201130）
偶然在 MySQL 官网看到对 JSON 数据格式的支持，貌似发现了最优解，如下：MySQL#JSON_OBJECTAGG
官方支持的行转列操作，而且聚合格式为 JSON，不用担心特殊字符问题，完美！
