导语

本文分析了后端研发和运维在日常工作中所面临的线上SQL定位排查痛点，基于姓名贴的灵感，设计和开发了一款SQL染色标记的MyBatis插件。该插件轻量高效，对业务代码无侵入，接入简单，支持SELECT、INSERT、UPDATE、DELETE等语句，同时也支持无WHERE条件SQL的标记增强。该SQL染色插件并不改变SQL指纹，染色信息内置了statementId、PFinderId，方便分布式跟踪和定位。此外，还提供了附加信息的传递入口，方便用户进行自定义信息染色，例如客户端的执行线程id等。期望在大家面临类似痛点时提供一些实践经验和参考，也欢迎大家合适的场景下接入使用。

痛点

 

  

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

  
  
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是哪些业务产生的。

我这里考虑采用MyBatis Plugini机制进行SQL染色增强，可以达到业务零侵入的效果：不改业务代码、不改业务SQL，做到SQL无感增强，自动染色。

用什么来区分SQL的唯一性呢？这个区分的标识区分度越高，越容易达到“一眼就看出来SQL来源”的效果。

对此，我采用SQL statement的id来作为唯一标识。SQL statement是有两部分组成：mapper namespace + SQL id，通过SQL statement的id基本上可以唯一确定程序中的SQL在mapper文件中的位置，顺便可以找到对应的DAO方法，及其追溯到上层调用来源和业务场景。

开整

 

  

   

   

   

   

   

  
  
// 其他代码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 */
 

除了statementId和pFinderId外，还允许用户在线程上下文中自定义传输一些附加信息到SQL中，并体现在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 */
 
 

  该插件暂不支持的场景如下：
 

PFinder SQL分析

 

  

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

   

  
  
<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>
 

 

  

   

   

   

   

   

  
  
<!-- SQLMarking Plugin --><plugin interceptor="com.jd.sword.mybatis.plugin.sql.SQLMarkingInterceptor">    <!-- 是否开启SQL染色标记插件 -->    <property name="enabled" value="true"/></plugin>
 
 

  
 

FAQ

 

  

   

   

   

   

   

  
  
<dependency>  <groupId>mybatis-plugins</groupId>  <artifactId>mybatis-plugins</artifactId>  <version>2.2.3</version></dependency>
 
 

  

 

 

  

   

  
  
SQLMarkingThreadLocal.put(key, value)