作者：京東物流 郭忠強

導(dǎo)語

本文分析了后端研發(fā)和運維在日常工作中所面臨的線上SQL定位排查痛點，基于姓名貼的靈感，設(shè)計和開發(fā)了一款SQL染色標記的MyBatis插件。該插件輕量高效，對業(yè)務(wù)代碼無侵入，接入簡單，支持SELECT、INSERT、UPDATE、DELETE等語句，同時也支持無WHERE條件SQL的標記增強。該SQL染色插件并不改變SQL指紋，染色信息內(nèi)置了statementId、PFinderId，方便分布式跟蹤和定位。此外，還提供了附加信息的傳遞入口，方便用戶進行自定義信息染色，例如客戶端的執(zhí)行線程id等。期望在大家面臨類似痛點時提供一些實踐經(jīng)驗和參考，也歡迎大家合適的場景下接入使用。

痛點

作為后端開發(fā)，不可避免地與SQL打交道，一個大型復(fù)雜系統(tǒng)中往往會有大量的SQL語句支撐業(yè)務(wù)，而且單表所涉及的不同SQL可能也多達幾十個甚至上百個。

當看到一個SQL時，如何快速識別這個SQL是哪塊業(yè)務(wù)的？具體是哪個方法走到了這個SQL？

這些SQL是憑個人大腦無法全部記住的，而且業(yè)務(wù)在不斷發(fā)展，SQL語句本身也在不斷地變化，可能明天增多一個表的join，后天增多了幾個where條件限制，大后天減少了幾個字段……

SQL本身也是支持動態(tài)拼接形成，當看到一個SQL時，如何快速定位是來自哪塊具體業(yè)務(wù)？這是個問題，也是個難題。

以下面的報表查詢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(?)

我經(jīng)常會面臨這種根據(jù)SQL定位分析業(yè)務(wù)來源的問題，尤其是在慢SQL分析治理時，往往會存在類似的痛點。

?

??

?

思路

我們?nèi)粘？吹揭恍┕ぷ魅藛T的制服上會配備姓名貼，這樣很有辨識度，通過姓名貼我們可以一看就可以看出來當前的工作人員是哪位同事。

在此啟發(fā)下，我認為對SQL也可以進行一些染色標記增強，通過這些標記可以一眼看出來這個SQL是哪些業(yè)務(wù)產(chǎn)生的。

我這里考慮采用MyBatis Plugini機制進行SQL染色增強，可以達到業(yè)務(wù)零侵入的效果：不改業(yè)務(wù)代碼、不改業(yè)務(wù)SQL，做到SQL無感增強，自動染色。

用什么來區(qū)分SQL的唯一性呢？這個區(qū)分的標識區(qū)分度越高，越容易達到“一眼就看出來SQL來源”的效果。

對此，我采用SQL statement的id來作為唯一標識。SQL statement是有兩部分組成：mapper namespace + SQL id，通過SQL statement的id基本上可以唯一確定程序中的SQL在mapper文件中的位置，順便可以找到對應(yīng)的DAO方法，及其追溯到上層調(diào)用來源和業(yè)務(wù)場景。

?

方案

?

??

?

SQL染色增強，這里是通過將附加信息作為SQL注釋，對SQL拼接改寫。

因為增加的部分是SQL注釋，不影響SQL的執(zhí)行正確性，也不會改寫SQL指紋，對于慢SQL排查定位、死鎖日志SQL排查都有幫助。

開整

這里是對SQL執(zhí)行前進行染色增強，所以攔截StatementHandler的StatementHandler方法即可。

??

?

SQL的修改核心代碼片段：

?

??

?

??

?

插件除了會自動拼接statementId和pFinderId外，還預(yù)留了一個ThreadLocal變量，允許使用者執(zhí)行線程的上線文中向SQL傳遞附加信息，比如SQL的執(zhí)行用戶ERP、執(zhí)行線程的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 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對應(yīng)于mapper文件中的namespace，selectStockShelfLifeReport 對應(yīng)于 SQL id。

除了statementId和pFinderId外，還允許用戶在線程上下文中自定義傳輸一些附加信息到SQL中，并體現(xiàn)在SQL注釋信息中。

?

??

?

??

?

?

??

?

借助IDE的reference功能，我們可以很快找到調(diào)用入口：

?

??

?

繼續(xù)向上追溯，流量源頭是來自一張報表查詢：

?

??

?

?

性能影響

??

既然是代理增強，多少會有一些性能開銷，目前根據(jù)我這邊使用的情況來看，單個SQL基本上是0-1ms左右，個別在3-4ms，正常情況下，不會影響業(yè)務(wù)響應(yīng)時長。

?

支持情況

已支持的場景：

?使用MyBatis的SQL，包含select、insert、update、delete，同時也支持無where條件的SQL。支持MyBatis-Plus。

?

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 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效果：

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

?

無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染色標記插件。

目前該組件已在多個大型復(fù)雜系統(tǒng)的生產(chǎn)環(huán)境中接入使用，大家可以先在測試、UAT環(huán)境接入試用，然后再逐步推廣線上生產(chǎn)環(huán)境。

接入方法也非常簡單，如下。

1、引入Maven坐標：

    com.jd.sword
    sword-mybatis-plugins
    1.0.2-SNAPSHOT
    
        
            org.mybatis
            mybatis
        
        
            org.projectlombok
            lombok
        
        
            org.apache.commons
            commons-lang3
        
        
            org.slf4j
            slf4j-api
        
    

對于其中的間接依賴，例如lombok等，大家可以使用自己工程中的已有依賴，在這里可以通過exclusion排掉，如果自己工程中沒有這些依賴，可以不exclusion。

2、在mybatis config xml中引入SQLMarking插件：

FAQ

1、支持 Mybatis-Plus 嗎？

答：支持，Mybatis-Plus是在MyBatis基礎(chǔ)上的增強，MyBatis插件可以得到執(zhí)行。

?

2、SQLMarking Plugin 在 plugins中的位置有嚴格要求嗎，比如必須第一個位置？

答：沒有嚴格要求，理論上放上放下都可以。有的小伙伴工程里依賴了多種 MyBatis Plugin，多種Plugin之間可能會有沖突，比如有些 Plugin 會對SQL的開頭INSERT/SELECT/UPDATE/DELETE關(guān)鍵詞進行前綴判斷，大家如果遇到報錯可以靈活調(diào)整 SQLMarking Plugin 的位置，向上或向下調(diào)整，不一定非得放在第一個位置。

?

3、報錯信息：There is no getter for property named 'delegate' in 'class com.sun.proxy.$Proxy211'

答：這種是多個插件之間有先后順序依賴，別的插件先行執(zhí)行，影響了delegate的獲取，調(diào)整 SQLMarking Plugin 的位置，向上或向下調(diào)整，可解決沖突。

?

4、報錯信息關(guān)鍵詞：NoClassDefFoundError RoutingStatementHandlerUtils

答：缺少依賴，添加以下依賴：

	mybatis-plugins
	mybatis-plugins
	2.2.3

?

5、染色信息中如何添加一些個性化的附加信息？

答：可以用下這個

SQLMarkingThreadLocal.put(key, value)

SQL 執(zhí)行完 remove 掉。一個方法同時執(zhí)行多個SQL時，如果 SQLMarkingThreadLocal 可共享，也可以在方法維度上 put 和 remove，就不用每個SQL put remove一下。主要是看線程上下文是否應(yīng)該傳遞SQLMarkingThreadLocal的信息。

審核編輯 黃宇