起因
网友问在一个大表里 [工艺名称] like '%抛%' 是否能优化
俺的回答是 这个工艺名称 是否有数据字典，如果有数据字典就可以优化
分析
写在分析前
- 其实erp his 等这些mis 系统一般在开发时都是先开发数据字典部分。
- like '%抛%' ，索引很难有效果，如果是 like '抛%' 索引就有效果了。
俺生成了629万数据进行测试，用2种写法实现like '%抛%'
写法1
select count(*) from [工时] where 工艺名称 like '%抛%'耗时3秒左右
写法2
declare @t table(name varchar(50) index idx_1) insert into @t (name) select * from 字典 where 名称 like '%抛%' select count(*) from [工时]a ,@t b where a.工艺名称=b.name耗时不到1秒
前提 字段 工艺名称 有索引
为什么呢？
其实就是一句话：使用了索引
分步骤拆解
第一步：从字典表查询符合条件的名称
sql
insert into @t (name) select * from 字典 where 名称 like '%抛%'- 字典表特性：行数极少（通常几千到几万条，最多不超过 10 万）
- 即使这里也用
LIKE '%抛%'全表扫描，总页数也只有几十到几百页 - 耗时：几毫秒，几乎可以忽略不计
- 结果集大小：通常只有几条到几十条（比如所有包含 "抛" 的工艺名称可能只有 5 个：抛光、抛丸、喷砂抛丸、手工抛、机械抛）
第二步：用小结果集驱动大表做精确匹配
sql
select count(*) from [工时]a ,@t b where a.工艺名称=b.name- 连接方式：SQL Server 自动选择嵌套循环连接（小表驱动大表的最优算法）
- 索引利用：现在是
a.工艺名称 = b.name精确匹配，完全符合 B 树索引的查找条件 - IO 开销：对 @t 中的每一个 name，执行 1 次索引查找，每次查找仅需 3-4 次 IO（B 树深度）
- 假设 @t 返回 5 条记录：总 IO 开销 = 5 × 4 =20 次 IO
总开销对比
表格
| 指标 | 写法 1（直接 LIKE） | 写法 2（字典表关联） | 性能提升倍数 |
|---|---|---|---|
| 逻辑读次数 | ~46,000 次 | ~20 次 | 2300 倍 |
| CPU 开销 | 遍历 629 万条记录 | 遍历 5 条记录 | 125 万倍 |
| 实际耗时 | ~3 秒 | <1 秒 | ~5 倍 |
核心结论
你的优化方案的本质是：
将大表的模糊查询转化为小表的模糊查询 + 大表的精确匹配查询
利用了 MIS 系统 "业务字段几乎都有数据字典" 的天然优势，用极小的代价（扫描小字典表）换取了巨大的性能提升（将大表的全索引扫描转化为几次索引查找）。这是一种架构级的优化思路，比单纯的 SQL 语法优化要高明得多。
另：
很多人误以为 "只要字段有索引，任何查询都快"，这是完全错误的。SQL Server 的 B 树索引只对前缀匹配有效：
表格
| 查询写法 | 索引利用方式 | 性能等级 |
|---|---|---|
工艺名称 LIKE '抛%' | 索引查找 (Index Seek) | ⭐⭐⭐⭐⭐ |
工艺名称 LIKE '%抛' | 索引扫描 (Index Scan) | ⭐⭐ |
工艺名称 LIKE '%抛%' | 索引扫描 (Index Scan) | ⭐⭐ |
还有优化的地方吗？
亲 有的
WITH (NOLOCK)
例如 from 字典 WITH(NOLOCK)
例如 from [工时] a WITH(NOLOCK)
