本文分析了后端研发和运维在日常工作中所面临的线上SQL定位排查痛点,基于姓名贴的灵感,设计和开发了一款SQL染色标记的MyBatis插件。该插件轻量高效,对业务代码无侵入,接入简单,支持SELECT、INSERT、UPDATE、DELETE等语句,同时也支持无WHERE条件SQL的标记增强。该SQL染色插件并不改变SQL指纹,染色信息内置了statementId、PFinderId,方便分布式跟踪和定位。此外,还提供了附加信息的传递入口,方便用户进行自定义信息染色,例如客户端的执行线程id等。期望在大家面临类似痛点时提供一些实践经验和参考,也欢迎大家合适的场景下接入使用。
以下面的报表查询SQL为例:
SELECT
COUNT( *)
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'
AND m.owner_no IN(?)
思路
// 其他代码
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 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: xxx, traceId: 59f48d4d-5346-4ffe-9837-693a090090fc */
通过这个染色标记后的SQL我们可以一眼看出来,这个SQL是来自StockShelfLifeReportDao中的selectStockShelfLifeReport方法,其中StockShelfLifeReportDao对应于mapper文件中的namespace,selectStockShelfLifeReport 对应于 SQL id。
除了statementId和pFinderId外,还允许用户在线程上下文中自定义传输一些附加信息到SQL中,并体现在SQL注释信息中。
SELECT SQL效果:
SELECT
COUNT(DISTINCT ito.transfer_order_no) AS qty
FROM
inv_transfer_order AS ito
LEFT JOIN inv_transfer_order_detail itd
ON
ito.warehouse_no = itd.warehouse_no
AND ito.transfer_order_no = itd.transfer_no
AND itd.deleted = 0
WHERE
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
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
INTO
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 */
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等。
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>
答:缺少依赖,添加以下依赖:
<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的信息。
推荐阅读