作者：京东物流 郭忠强
导语
本文分析了后端研发和运维在日常工作中所面临的线上 SQL 定位排查痛点，基于姓名贴的灵感，设计和开发了一款 SQL 染色标记的 MyBatis 插件。该插件轻量高效，对业务代码无侵入，接入简单，支持 SELECT、INSERT、UPDATE、DELETE 等语句，同时也支持无 WHERE 条件 SQL 的标记增强。该 SQL 染色插件并不改变 SQL 指纹，染色信息内置了 statementId、PFinderId，方便分布式跟踪和定位。此外，还提供了附加信息的传递入口，方便用户进行自定义信息染色，例如客户端的执行线程 id 等。期望在大家面临类似痛点时提供一些实践经验和参考，也欢迎大家合适的场景下接入使用。
痛点
作为后端开发，不可避免地与 SQL 打交道，一个大型复杂系统中往往会有大量的 SQL 语句支撑业务，而且单表所涉及的不同 SQL 可能也多达几十个甚至上百个。
当看到一个 SQL 时，如何快速识别这个 SQL 是哪块业务的？具体是哪个方法走到了这个 SQL？
这些 SQL 是凭个人大脑无法全部记住的，而且业务在不断发展，SQL 语句本身也在不断地变化，可能明天增多一个表的 join，后天增多了几个 where 条件限制，大后天减少了几个字段……
SQL 本身也是支持动态拼接形成，当看到一个 SQL 时，如何快速定位是来自哪块具体业务？这是个问题，也是个难题。
以下面的报表查询 SQL 为例：
SELECT COUNT( *)FROM st_stock mINNER JOIN st_lot_shelf_life slslON m.tenant_code = slsl.tenant_code AND m.sku = slsl.sku AND m.lot_no = slsl.lot_no AND slsl.deleted = 0WHERE m.deleted = 0 AND m.stock_qty > 0 AND m.warehouse_no = ? AND m.lot_no != '-1' AND m.owner_no IN(?)
复制代码
我经常会面临这种根据 SQL 定位分析业务来源的问题，尤其是在慢 SQL 分析治理时，往往会存在类似的痛点。
思路
我们日常看到一些工作人员的制服上会配备姓名贴，这样很有辨识度，通过姓名贴我们可以一看就可以看出来当前的工作人员是哪位同事。
在此启发下，我认为对 SQL 也可以进行一些染色标记增强，通过这些标记可以一眼看出来这个 SQL 是哪些业务产生的。
我这里考虑采用 MyBatis Plugini 机制进行 SQL 染色增强，可以达到业务零侵入的效果：不改业务代码、不改业务 SQL，做到 SQL 无感增强，自动染色。
用什么来区分 SQL 的唯一性呢？这个区分的标识区分度越高，越容易达到“一眼就看出来 SQL 来源”的效果。
对此，我采用 SQL statement 的 id 来作为唯一标识。SQL statement 是有两部分组成：mapper namespace + SQL id，通过 SQL statement 的 id 基本上可以唯一确定程序中的 SQL 在 mapper 文件中的位置，顺便可以找到对应的 DAO 方法，及其追溯到上层调用来源和业务场景。
方案
SQL 染色增强，这里是通过将附加信息作为 SQL 注释，对 SQL 拼接改写。
因为增加的部分是 SQL 注释，不影响 SQL 的执行正确性，也不会改写 SQL 指纹，对于慢 SQL 排查定位、死锁日志 SQL 排查都有帮助。
开整
这里是对 SQL 执行前进行染色增强，所以拦截 StatementHandler 的 StatementHandler 方法即可。
SQL 的修改核心代码片段：
插件除了会自动拼接 statementId 和 pFinderId 外，还预留了一个 ThreadLocal 变量，允许使用者执行线程的上线文中向 SQL 传递附加信息，比如 SQL 的执行用户 ERP、执行线程的 id 等。
用法示例：
// 其他代码SQLMarkingThreadLocal.put("operator", UserInfoUtil.getUserCode());// 其他代码SQLMarkingThreadLocal.remove();// 其他代码
复制代码
用户也可以通过自定义切面方式自动赋值这些附加信息。
效果
2025-02-11 00:27:19.982 [http-nio-8082-exec-7] DEBUG [pfinderId：4630283.56667.17392048399060130] org.apache.ibatis.logging.jdbc.BaseJdbcLogger-debug:137 - c.j.w.s.i.j.r.d.S.selectStockShelfLifeReport==> Preparing: SELECT m.id, m.sku, m.location_no locationNo, m.container_level_1 containerLevel1, m.container_level_2 containerLevel2, m.lot_no lotNo, m.sku_level skuLevel, m.owner_no ownerNo, m.pack_code packCode, m.stock_qty stockQty, m.prepicked_qty prePickedQty, m.premoved_qty preMovedQty, m.frozen_qty frozenQty, m.diff_qty diffQty, m.broken_qty brokenQty, m.status, m.create_time as createTime, m.update_time as updateTime, m.update_user as updateUser, m.create_user as createUser, stock_qty - (prepicked_qty + premoved_qty + frozen_qty + diff_qty + broken_qty) availableQty, (prepicked_qty + premoved_qty + frozen_qty + diff_qty + broken_qty) noAvailableQty, m.zone_no zoneNo, m.zone_type zoneType, slsl.shelf_life_status shelfLifeStatus, slsl.left_days leftDays, slsl.production_date productionDate, slsl.expiration_date expirationDate, slsl.shelf_life_days shelfLifeDays, slsl.warning_days warningDays, slsl.regular_advent_days regularAdventDays, slsl.urgent_advent_days urgentAdventDays, slsl.advent_days adventDays, slsl.extend_content extendContent FROM st_stock m INNER JOIN st_lot_shelf_life slsl ON m.tenant_code = slsl.tenant_code AND m.sku = slsl.sku AND m.lot_no = slsl.lot_no AND slsl.deleted = 0 WHERE m.deleted = 0 AND m.stock_qty > 0 AND m.warehouse_no = ? AND m.lot_no != '-1' LIMIT ? /* [SQLMarking] statementId: com.jdwl.wms.stock.infrastructure.jdbc.report.dao.StockShelfLifeReportDao.selectStockShelfLifeReport, pFinderId: 4630283.56667.17392048399060130, operator: guozhongqiang5, traceId: 59f48d4d-5346-4ffe-9837-693a090090fc */2025-02-11 00:27:19.982 [http-nio-8082-exec-7] DEBUG [pfinderId：4630283.56667.17392048399060130] org.apache.ibatis.logging.jdbc.BaseJdbcLogger-debug:137 - c.j.w.s.i.j.r.d.S.selectStockShelfLifeReport==> Parameters: 6_975(String), 10(Integer)2025-02-11 00:27:19.988 [http-nio-8082-exec-7] DEBUG [pfinderId：4630283.56667.17392048399060130] org.apache.ibatis.logging.jdbc.BaseJdbcLogger-debug:137 - c.j.w.s.i.j.r.d.S.selectStockShelfLifeReport<== Total: 10
复制代码
SELECT m.id, m.sku, m.location_no locationNo, m.container_level_1 containerLevel1, m.container_level_2 containerLevel2, m.lot_no lotNo, m.sku_level skuLevel, m.owner_no ownerNo, m.pack_code packCode, m.stock_qty stockQty, m.prepicked_qty prePickedQty, m.premoved_qty preMovedQty, m.frozen_qty frozenQty, m.diff_qty diffQty, m.broken_qty brokenQty, m.status, m.create_time AS createTime, m.update_time AS updateTime, m.update_user AS updateUser, m.create_user AS createUser, stock_qty -(prepicked_qty + premoved_qty + frozen_qty + diff_qty + broken_qty) availableQty, (prepicked_qty + premoved_qty + frozen_qty + diff_qty + broken_qty) noAvailableQty, m.zone_no zoneNo, m.zone_type zoneType, slsl.shelf_life_status shelfLifeStatus, slsl.left_days leftDays, slsl.production_date productionDate, slsl.expiration_date expirationDate, slsl.shelf_life_days shelfLifeDays, slsl.warning_days warningDays, slsl.regular_advent_days regularAdventDays, slsl.urgent_advent_days urgentAdventDays, slsl.advent_days adventDays, slsl.extend_content extendContentFROM st_stock mINNER JOIN st_lot_shelf_life slslON m.tenant_code = slsl.tenant_code AND m.sku = slsl.sku AND m.lot_no = slsl.lot_no AND slsl.deleted = 0WHERE m.deleted = 0 AND m.stock_qty > 0 AND m.warehouse_no = ? AND m.lot_no != '-1' LIMIT ? /* [SQLMarking] statementId: com.jdwl.wms.stock.infrastructure.jdbc.report.dao.StockShelfLifeReportDao.selectStockShelfLifeReport, pFinderId: 4630283.56667.17392048399060130, operator: xxx, traceId: 59f48d4d-5346-4ffe-9837-693a090090fc */
复制代码
通过这个染色标记后的 SQL 我们可以一眼看出来，这个 SQL 是来自 StockShelfLifeReportDao 中的 selectStockShelfLifeReport 方法，其中 StockShelfLifeReportDao 对应于 mapper 文件中的 namespace，selectStockShelfLifeReport 对应于 SQL id。
除了 statementId 和 pFinderId 外，还允许用户在线程上下文中自定义传输一些附加信息到 SQL 中，并体现在 SQL 注释信息中。
借助 IDE 的 reference 功能，我们可以很快找到调用入口：
继续向上追溯，流量源头是来自一张报表查询：
性能影响
既然是代理增强，多少会有一些性能开销，目前根据我这边使用的情况来看，单个 SQL 基本上是 0-1ms 左右，个别在 3-4ms，正常情况下，不会影响业务响应时长。
支持情况
已支持的场景：
•使用 MyBatis 的 SQL，包含 select、insert、update、delete，同时也支持无 where 条件的 SQL。支持 MyBatis-Plus。
SELECT SQL 效果：
SELECT COUNT(DISTINCT ito.transfer_order_no) AS qtyFROM inv_transfer_order AS itoLEFT JOIN inv_transfer_order_detail itd ON ito.warehouse_no = itd.warehouse_no AND ito.transfer_order_no = itd.transfer_no AND itd.deleted = 0WHERE ito.deleted = 0 AND ito.warehouse_no = ? AND ito.transfer_status IN(?, ?, ?, ?, ?, ?, ?, ?) /* [SQLMarking] statementId: com.jdwl.wms.inventory.xxx.infrastructure.jdbc.dao.TransferOrderDao.selectOverstockOrderQty, pFinderId: 4900300.56689.17397685906403801, traceId: abc53cd3-e814-451e-a771-5d8caae861a7, operator: xxx */
复制代码
UPDATE SQL 效果：
UPDATE inv_transfer_task_detail SET task_status = ?, task_user = ?, update_user = ?, update_time = now(), receive_time = now()WHERE warehouse_no = ? AND deleted = 0 AND order_detail_id IN(?) AND task_status IN(?, ?, ?) /* [SQLMarking] statementId: com.jdwl.wms.inventory.xxx.infrastructure.jdbc.dao.TransferTaskDetailDao.updateStatusAndTaskUserByOrderDetailAndStatus, pFinderId: 4900300.56689.17397685881342999, traceId: 41366c16-2e10-4c45-a10c-c84326e201b4, operator: xxx */
复制代码
INSERT SQL 效果：
INSERTINTO inv_transfer_task_result ( id, result_no, transfer_type, task_type, location_no, container_level_1, container_level_2, container_full, extend_content, warehouse_no, create_user, create_time, update_user, update_time, task_no, tenant_code ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), ?, now(), ?, 'TC26473419' ) /* [SQLMarking] statementId: com.jdwl.wms.inventory.xxx.infrastructure.jdbc.dao.TransferTaskResultDao.insert, pFinderId: 4900300.56689.17397685845562352, traceId: 7cc0eebf-c4c5-4fc1-b5de-ae1f14ba29ba, operator: xxx */
复制代码
无 WHERE 条件的 SQL 效果：
SELECT NOW()/* [SQLMarking] statementId: com.jdwl.wms.stock.xxx.jdbc.main.dao.StockQueryDao.dbTime, pFinderId: 2033056.56579.17392526509236705 */
复制代码
该插件暂不支持的场景如下：
•ORM 非 MyBatis 的 SQL，例如通过 connection statement execute 操作的 SQL，通过 JdbcTemplate 操作的 SQL 等。
线上 SQL 的排查定位使用案例
慢 SQL 分析
会话管理
PFinder SQL 分析
如何接入？
如果小伙伴也有类似痛点和使用诉求，可以接入这个简易的 SQL 染色标记插件。
目前该组件已在多个大型复杂系统的生产环境中接入使用，大家可以先在测试、UAT 环境接入试用，然后再逐步推广线上生产环境。
接入方法也非常简单，如下。
1、引入 Maven 坐标：
<dependency> <groupId>com.jd.sword</groupId> <artifactId>sword-mybatis-plugins</artifactId> <version>1.0.2-SNAPSHOT</version> <exclusions> <exclusion> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> </exclusion> <exclusion> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclusion> <exclusion> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> </exclusion> <exclusion> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> </exclusion> </exclusions></dependency>
复制代码
对于其中的间接依赖，例如 lombok 等，大家可以使用自己工程中的已有依赖，在这里可以通过 exclusion 排掉，如果自己工程中没有这些依赖，可以不 exclusion。
2、在 mybatis config xml 中引入 SQLMarking 插件：
<!-- SQLMarking Plugin --><plugin interceptor="com.jd.sword.mybatis.plugin.sql.SQLMarkingInterceptor"> <!-- 是否开启SQL染色标记插件 --> <property name="enabled" value="true"/></plugin>
复制代码
FAQ
1、支持 Mybatis-Plus 吗？
答：支持，Mybatis-Plus 是在 MyBatis 基础上的增强，MyBatis 插件可以得到执行。
2、SQLMarking Plugin 在 plugins 中的位置有严格要求吗，比如必须第一个位置？
答：没有严格要求，理论上放上放下都可以。有的小伙伴工程里依赖了多种 MyBatis Plugin，多种 Plugin 之间可能会有冲突，比如有些 Plugin 会对 SQL 的开头 INSERT/SELECT/UPDATE/DELETE 关键词进行前缀判断，大家如果遇到报错可以灵活调整 SQLMarking Plugin 的位置，向上或向下调整，不一定非得放在第一个位置。
3、报错信息：There is no getter for property named 'delegate' in 'class com.sun.proxy.$Proxy211'
答：这种是多个插件之间有先后顺序依赖，别的插件先行执行，影响了 delegate 的获取，调整 SQLMarking Plugin 的位置，向上或向下调整，可解决冲突。
4、报错信息关键词：NoClassDefFoundError RoutingStatementHandlerUtils
答：缺少依赖，添加以下依赖：
<dependency> <groupId>mybatis-plugins</groupId> <artifactId>mybatis-plugins</artifactId> <version>2.2.3</version></dependency>
复制代码
5、染色信息中如何添加一些个性化的附加信息？
答：可以用下这个
SQLMarkingThreadLocal.put(key, value)
复制代码
SQL 执行完 remove 掉。一个方法同时执行多个 SQL 时，如果 SQLMarkingThreadLocal 可共享，也可以在方法维度上 put 和 remove，就不用每个 SQL put remove 一下。主要是看线程上下文是否应该传递 SQLMarkingThreadLocal 的信息。
评论