如何一眼定位SQL的代碼來(lái)源：一款SQL染色標(biāo)記的簡(jiǎn)易MyBatis插件

作者：京東物流 郭忠強(qiáng)

導(dǎo)語(yǔ)

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

痛點(diǎn)

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

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

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

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

以下面的報(bào)表查詢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)常會(huì)面臨這種根據(jù)SQL定位分析業(yè)務(wù)來(lái)源的問(wèn)題，尤其是在慢SQL分析治理時(shí)，往往會(huì)存在類(lèi)似的痛點(diǎn)。

?

??

?

思路

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

在此啟發(fā)下，我認(rèn)為對(duì)SQL也可以進(jìn)行一些染色標(biāo)記增強(qiáng)，通過(guò)這些標(biāo)記可以一眼看出來(lái)這個(gè)SQL是哪些業(yè)務(wù)產(chǎn)生的。

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

用什么來(lái)區(qū)分SQL的唯一性呢？這個(gè)區(qū)分的標(biāo)識(shí)區(qū)分度越高，越容易達(dá)到“一眼就看出來(lái)SQL來(lái)源”的效果。

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

?

方案

?

??

?

SQL染色增強(qiáng)，這里是通過(guò)將附加信息作為SQL注釋?zhuān)瑢?duì)SQL拼接改寫(xiě)。

因?yàn)樵黾拥牟糠质荢QL注釋?zhuān)挥绊慡QL的執(zhí)行正確性，也不會(huì)改寫(xiě)SQL指紋，對(duì)于慢SQL排查定位、死鎖日志SQL排查都有幫助。

開(kāi)整

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

??

?

SQL的修改核心代碼片段：

?

??

?

??

?

插件除了會(huì)自動(dòng)拼接statementId和pFinderId外，還預(yù)留了一個(gè)ThreadLocal變量，允許使用者執(zhí)行線程的上線文中向SQL傳遞附加信息，比如SQL的執(zhí)行用戶ERP、執(zhí)行線程的id等。

?

??

?

用法示例：

// 其他代碼
SQLMarkingThreadLocal.put("operator", UserInfoUtil.getUserCode());
// 其他代碼
SQLMarkingThreadLocal.remove();
// 其他代碼

用戶也可以通過(guò)自定義切面方式自動(dòng)賦值這些附加信息。

效果

?

??

?

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

?

通過(guò)這個(gè)染色標(biāo)記后的SQL我們可以一眼看出來(lái)，這個(gè)SQL是來(lái)自StockShelfLifeReportDao中的selectStockShelfLifeReport方法，其中StockShelfLifeReportDao對(duì)應(yīng)于mapper文件中的namespace，selectStockShelfLifeReport 對(duì)應(yīng)于 SQL id。

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

?

??

?

??

?

?

??

?

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

?

??

?

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

?

??

?

?

性能影響

??

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

?

支持情況

已支持的場(chǎng)景：

?使用MyBatis的SQL，包含select、insert、update、delete，同時(shí)也支持無(wú)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 */

?

無(wú)WHERE條件的SQL效果：

SELECT NOW()
/* [SQLMarking] statementId: com.jdwl.wms.stock.xxx.jdbc.main.dao.StockQueryDao.dbTime, pFinderId: 2033056.56579.17392526509236705 */

?

該插件暫不支持的場(chǎng)景如下：

?ORM非MyBatis的SQL，例如通過(guò) connection statement execute 操作的SQL，通過(guò)JdbcTemplate 操作的SQL等。

?

線上SQL的排查定位使用案例

?

慢SQL分析

??

?

會(huì)話管理

??

?

??

?

PFinder SQL分析

??

?

如何接入？

如果小伙伴也有類(lèi)似痛點(diǎn)和使用訴求，可以接入這個(gè)簡(jiǎn)易的SQL染色標(biāo)記插件。

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

接入方法也非常簡(jiǎn)單，如下。

1、引入Maven坐標(biāo)：

    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
        
    

對(duì)于其中的間接依賴(lài)，例如lombok等，大家可以使用自己工程中的已有依賴(lài)，在這里可以通過(guò)exclusion排掉，如果自己工程中沒(méi)有這些依賴(lài)，可以不exclusion。

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

FAQ

1、支持 Mybatis-Plus 嗎？

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

?

2、SQLMarking Plugin 在 plugins中的位置有嚴(yán)格要求嗎，比如必須第一個(gè)位置？

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

?

3、報(bào)錯(cuò)信息：There is no getter for property named 'delegate' in 'class com.sun.proxy.$Proxy211'

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

?

4、報(bào)錯(cuò)信息關(guān)鍵詞：NoClassDefFoundError RoutingStatementHandlerUtils

答：缺少依賴(lài)，添加以下依賴(lài)：

	mybatis-plugins
	mybatis-plugins
	2.2.3

?

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

答：可以用下這個(gè)

SQLMarkingThreadLocal.put(key, value)

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

審核編輯 黃宇