Spring boot+MyBatis+SQL Server使用变量声明和CTE（公用表表达式）的区别

使用变量声明

    <!-- 通过交接单id，查询样品项目，样品检不同项目，样品来源 jjs_YCDD 放到 remark -->
    <select id="selectApplyItemXByJJDId" resultType="com.weiyu.pojo.ApplyItem">
        <!-- 使用变量声明 -->
        declare @ApplyId varchar(30)

        select
        @ApplyId = jjd_ApplyID
        from JJD with(NOLOCK)
        where jjd_ID = #{jjdId}

        select
        null as id, avx.Avx_OuterApplyID as outerApplyId, avx.Avx_ApplyID as applyId,
        avx.Avx_SampleNo as sampleNo, avx.Avx_SampleName as sampleName,
        avx.avx_ItemID as itemId, avx.avx_ItemName as itemName,
        null as unit, null as standardScript, jjs.jjs_YCDD as remark,
        null as ffbzId, null as ffbzName, null as verifyMethod,
        null as pjbz, null as stdItemId
        from ApplyVerifyItemX avx with(NOLOCK)
        inner join VerifyItem vi with(NOLOCK) on vi.vi_ID = avx.avx_ItemID
        inner join JJDSample jjs with(NOLOCK) on jjs.jjs_JJDID = #{jjdId} and jjs.jjs_ApplyID = avx.Avx_ApplyID and jjs.jjs_SampleNo = avx.Avx_SampleNo
        inner join JJDVerifyItem jji with(NOLOCK) on jji.jji_JJDID = #{jjdId} and jji.jji_StdItemID = avx.avx_StdItemID
        where avx.avx_ApplyID = @ApplyId and avx.avx_Valid = 1
        order by len(avx.avx_SampleNo), avx.avx_SampleNo, len(vi.vi_SortNO), vi.vi_SortNO, avx.avx_ItemName
    </select>

使用 CTE（公用表表达式），更符合 MyBatis 风格

    <!-- 通过交接单id和样品编号，查询样品项目，样品检不同项目，样品来源 jjs_YCDD 放到 remark -->
    <select id="selectApplyItemXByJJDIdAndSampleNo" resultType="com.weiyu.pojo.ApplyItem">
        <!-- 使用 CTE（公用表表达式）替代变量声明，更符合 MyBatis 风格 -->
        with ApplyIdCTE as (
        select jjd_ApplyID as ApplyID
        from JJD with(NOLOCK)
        where jjd_ID = #{jjdId}
        )
        select
        null as id, avx.Avx_OuterApplyID as outerApplyId, avx.Avx_ApplyID as applyId,
        avx.Avx_SampleNo as sampleNo, avx.Avx_SampleName as sampleName,
        avx.avx_ItemID as itemId, avx.avx_ItemName as itemName,
        null as unit, null as standardScript, jjs.jjs_YCDD as remark,
        null as ffbzId, null as ffbzName, null as verifyMethod,
        null as pjbz, null as stdItemId
        from ApplyVerifyItemX avx with(NOLOCK)
        inner join VerifyItem vi with(NOLOCK) on vi.vi_ID = avx.avx_ItemID
        inner join JJDSample jjs with(NOLOCK) on jjs.jjs_JJDID = #{jjdId} and jjs.jjs_ApplyID = avx.Avx_ApplyID and jjs.jjs_SampleNo = #{sampleNo}
        inner join JJDVerifyItem jji with(NOLOCK) on jji.jji_JJDID = #{jjdId} and jji.jji_StdItemID = avx.avx_StdItemID
        where avx.avx_ApplyID = (select ApplyID from ApplyIdCTE) and avx.Avx_SampleNo = #{sampleNo} and avx.avx_Valid = 1
        order by len(avx.avx_SampleNo), avx.avx_SampleNo, len(vi.vi_SortNO), vi.vi_SortNO, avx.avx_ItemName
    </select>

WITH(NOLOCK) 详解

1. 基本概念

WITH(NOLOCK) 是 SQL Server 中的表提示（Table Hint），它的作用是：

主要功能

• 不申请共享锁：读取数据时不加锁

• 允许脏读：可以读取其他事务未提交的数据

• 避免阻塞：不会被其他事务阻塞，也不会阻塞其他事务

2. 语法和用法

sql

-- 基本用法
SELECT * FROM TableName WITH(NOLOCK)

-- 在JOIN中的用法
SELECT * 
FROM Table1 t1 WITH(NOLOCK)
INNER JOIN Table2 t2 WITH(NOLOCK) ON t1.ID = t2.ID

-- 您的查询中的用法
SELECT avix.Avx_ApplyID
FROM ApplyVerifyItemX avix WITH(NOLOCK)
WHERE avix.avx_Valid = 1

3. 实际效果演示

没有 NOLOCK 的情况

sql

-- 事务1（更新数据，未提交）
BEGIN TRANSACTION
UPDATE Customers SET Balance = Balance + 100 WHERE CustomerID = 1
-- 不提交，保持事务开放

-- 事务2（查询会被阻塞）
SELECT Balance FROM Customers WHERE CustomerID = 1
-- 这个查询会一直等待直到事务1提交或回滚

使用 NOLOCK 的情况

sql

-- 事务1（更新数据，未提交）
BEGIN TRANSACTION
UPDATE Customers SET Balance = Balance + 100 WHERE CustomerID = 1
-- 不提交，保持事务开放

-- 事务2（使用NOLOCK，不会被阻塞）
SELECT Balance FROM Customers WITH(NOLOCK) WHERE CustomerID = 1
-- 立即返回结果，可能读取到未提交的Balance+100的值

4. NOLOCK 的风险和问题

脏读（Dirty Read）

sql

-- 事务1：更新后回滚
BEGIN TRANSACTION
UPDATE Products SET Price = 100 WHERE ProductID = 1
-- 此时Price变为100，但未提交

-- 事务2：使用NOLOCK读取
SELECT Price FROM Products WITH(NOLOCK) WHERE ProductID = 1
-- 读取到Price=100（脏数据）

-- 事务1：回滚
ROLLBACK TRANSACTION
-- Price恢复原值，但事务2已经读取了错误的数据

其他数据一致性问题

sql

-- 可能遇到缺失行（因为页面拆分）
SELECT COUNT(*) FROM LargeTable WITH(NOLOCK)
-- 结果可能不准确

-- 可能遇到重复行
SELECT DISTINCT CustomerID FROM Orders WITH(NOLOCK)
-- 可能返回重复的CustomerID

5. 适用场景

适合使用 NOLOCK 的情况

sql

-- 1. 报表查询、数据分析（对实时性要求不高）
SELECT 
    COUNT(*) as TotalOrders,
    AVG(OrderAmount) as AvgAmount
FROM Orders WITH(NOLOCK)
WHERE OrderDate >= '2024-01-01'

-- 2. 监控系统、日志查询
SELECT * FROM SystemLog WITH(NOLOCK)
ORDER BY LogTime DESC

-- 3. 大数据量统计（可接受轻微误差）
SELECT Department, COUNT(*) as EmployeeCount
FROM Employees WITH(NOLOCK)
GROUP BY Department

不适合使用 NOLOCK 的情况

sql

-- 1. 财务交易数据
SELECT Balance FROM Accounts WHERE AccountID = 123
-- 绝对不能使用NOLOCK！

-- 2. 库存管理
SELECT Quantity FROM Inventory WHERE ProductID = 456
-- 需要准确数据

-- 3. 关键业务逻辑判断
IF EXISTS(SELECT 1 FROM Users WITH(NOLOCK) WHERE Username = 'admin')
-- 可能误判用户存在性

6. 在您查询中的具体分析

原查询中的使用

sql

FROM ApplyVerifyItemX avix WITH(NOLOCK)
INNER JOIN VerifyItem vi WITH(NOLOCK) ON vi.vi_ID = avix.avx_ItemID
INNER JOIN ApplySampleInfoC asic WITH(NOLOCK) ON asic.as_ApplyID = avix.Avx_ApplyID 
INNER JOIN JJDVerifyItem jvi WITH(NOLOCK) ON jvi.jji_StdItemID = avix.avx_StdItemID 

风险评估

可能适合使用 NOLOCK 的原因：

• 查询涉及多个表连接，使用锁可能导致阻塞

• 可能是报表类查询，对数据实时性要求不高

• 数据量可能较大，性能是关键考虑因素

需要注意的风险：

• 如果这是关键业务数据，脏读可能导致问题

• 排序操作可能因为数据不一致而出现异常

7. 替代方案

使用 READ UNCOMMITTED 隔离级别

sql

-- 会话级别设置
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM ApplyVerifyItemX avix
-- 相当于所有表都使用了WITH(NOLOCK)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 恢复默认

使用快照隔离（推荐）

sql

-- 需要数据库启用快照隔离
ALTER DATABASE YourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

-- 使用快照隔离
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT * FROM ApplyVerifyItemX avix
-- 读取事务开始时的数据快照，避免脏读和阻塞

8. 最佳实践建议

对于您的查询，建议：

sql

-- 方案1：保持NOLOCK，但添加注释说明
SELECT 
    NULL AS id, 
    avix.Avx_OuterApplyID AS outerApplyId
    -- ... 其他字段
FROM ApplyVerifyItemX avix WITH(NOLOCK) -- 注意：允许脏读，用于性能优化
WHERE avix.avx_ApplyID = 'G2025060005-1' 
    AND avix.avx_Valid = 1

-- 方案2：使用变量控制（更安全）
DECLARE @UseNoLock BIT = 0; -- 0=安全模式，1=性能模式

SELECT 
    NULL AS id, 
    avix.Avx_OuterApplyID AS outerApplyId
    -- ... 其他字段
FROM ApplyVerifyItemX avix 
    + CASE WHEN @UseNoLock = 1 THEN ' WITH(NOLOCK)' ELSE '' END
WHERE avix.avx_ApplyID = 'G2025060005-1' 
    AND avix.avx_Valid = 1

9. 总结

WITH(NOLOCK) 的使用决策矩阵：

对于您的查询建议：

• 如果这是报表或分析查询 → 可以保留 WITH(NOLOCK)

• 如果涉及关键业务决策 → 应该移除 WITH(NOLOCK)

• 折中方案 → 使用快照隔离或只在非关键表上使用 NOLOCK