网友的一个SQL问题

起因网友问在一个大表里 [工艺名称] like %抛% 是否能优化俺的回答是 这个工艺名称 是否有数据字典如果有数据字典就可以优化分析写在分析前其实erp his 等这些mis 系统一般在开发时都是先开发数据字典部分。like %抛% 索引很难有效果如果是 like 抛% 索引就有效果了。俺生成了629万数据进行测试用2种写法实现like %抛%写法1select count(*) from [工时] where 工艺名称 like %抛%耗时3秒左右写法2declare 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秒前提 字段 工艺名称 有索引为什么呢其实就是一句话使用了索引分步骤拆解第一步从字典表查询符合条件的名称sqlinsert into t (name) select * from 字典 where 名称 like %抛%字典表特性行数极少通常几千到几万条最多不超过 10 万即使这里也用LIKE %抛%全表扫描总页数也只有几十到几百页耗时几毫秒几乎可以忽略不计结果集大小通常只有几条到几十条比如所有包含 抛 的工艺名称可能只有 5 个抛光、抛丸、喷砂抛丸、手工抛、机械抛第二步用小结果集驱动大表做精确匹配sqlselect count(*) from [工时]a ,t b where a.工艺名称b.name连接方式SQL Server 自动选择嵌套循环连接小表驱动大表的最优算法索引利用现在是a.工艺名称 b.name精确匹配完全符合 B 树索引的查找条件IO 开销对 t 中的每一个 name执行 1 次索引查找每次查找仅需 3-4 次 IOB 树深度假设 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)