1. 从生产案例看LIKE模糊查询的索引失效去年我遇到一个典型的性能问题某财务系统每月1号凌晨生成的报表要跑6个多小时而业务要求必须在2小时内完成。排查后发现罪魁祸首是这条SQLSELECT * FROM TRANS_LOG WHERE TRANS_DESC LIKE %手续费% AND CREATE_TIME BETWEEN 2023-01-01 AND 2023-01-31这个查询有两个致命问题首先是前后百分号的LIKE模糊匹配其次是时间范围跨度太大。当时达梦数据库的执行计划显示走了全表扫描CSCN2处理1600万条数据耗时218分钟。为什么B树索引会失效这要从索引结构说起。达梦的普通B树索引就像电话簿只能按字母顺序查找。当我们搜索%手续费%时相当于要找出所有包含手续费三个字的条目而电话簿只能查找以特定字开头的条目。这就好比让你在一本没有目录的书中找出所有提到人工智能的页码只能逐页翻找。2. LIKE_OPT_FLAG参数的黑科技2.1 参数原理解析达梦的LIKE_OPT_FLAG参数默认值为0是专门为LIKE查询设计的优化开关-- 查看当前参数值 SELECT * FROM V$PARAMETER WHERE NAMELIKE_OPT_FLAG; -- 修改参数需要DBA权限 SP_SET_PARA_VALUE(1,LIKE_OPT_FLAG,5); -- 推荐组合值这个参数有4种优化模式可以通过位运算组合使用值优化场景转换方式适用条件1首尾通配符LIKE %AB% → POSITION(AB)0需要函数索引2右模糊查询LIKE AB% → POSITION(AB)1普通索引可用4连接字符串优化LIKE A8REVERSE索引优化LIKE %AB → REVERSE() LIKE BA%需要REVERSE索引2.2 实战参数调优在我的项目中通过以下组合拳实现了性能飞跃先设置参数为组合模式SP_SET_PARA_VALUE(1,LIKE_OPT_FLAG,5); -- 14组合创建POSITION函数索引CREATE INDEX IDX_TRANS_DESC_POS ON TRANS_LOG(POSITION(手续费,TRANS_DESC));改写SQL可选-- 原始SQL SELECT * FROM TRANS_LOG WHERE TRANS_DESC LIKE %手续费%; -- 优化后等效写法 SELECT * FROM TRANS_LOG WHERE POSITION(手续费,TRANS_DESC)0;实测效果1600万数据查询从218分钟降到47秒执行计划从CSCN2变为SSEK2二级索引定位。3. 函数索引的进阶玩法3.1 POSITION函数索引的局限性虽然POSITION函数索引效果显著但有两个坑需要注意固定值问题索引只对特定关键词有效-- 这个索引只能优化手续费查询 CREATE INDEX IDX_POS_1 ON TABLE(POSITION(手续费,COL)); -- 对以下查询无效 SELECT * FROM TABLE WHERE COL LIKE %服务费%;多条件问题每个关键词需要单独索引-- 需要创建两个索引 CREATE INDEX IDX_POS_1 ON TABLE(POSITION(手续费,COL)); CREATE INDEX IDX_POS_2 ON TABLE(POSITION(服务费,COL));3.2 REVERSE索引方案对于后缀模糊查询如LIKE %公司可以结合REVERSE索引-- 步骤1创建REVERSE函数索引 CREATE INDEX IDX_REVERSE ON EMPLOYEE(REVERSE(NAME)); -- 步骤2改写SQL SELECT * FROM EMPLOYEE WHERE REVERSE(NAME) LIKE REVERSE(%公司);这个方案的原理是把后缀匹配转为前缀匹配。在我的测试中300万数据查询从112秒降到1.3秒。4. 全文索引的降维打击当需要同时查询多个关键词时全文索引才是终极解决方案。去年我们为某知识库系统实施了这个方案-- 创建全文索引 CREATE CONTEXT INDEX IDX_FULLTEXT ON DOCUMENTS(CONTENT) LEXER CHINESE_LEXER; -- 定期重建索引重要 ALTER CONTEXT INDEX IDX_FULLTEXT ON DOCUMENTS REBUILD; -- 使用CONTAINS查询 SELECT * FROM DOCUMENTS WHERE CONTAINS(CONTENT,手续费 AND 服务费);性能对比200万文档方案索引大小查询耗时适用场景LIKE %X%-78秒简单查询POSITION函数索引1.2GB1.5秒固定关键词全文索引2.8GB0.3秒复杂搜索需要注意的是全文索引虽然强大但维护成本较高。建议满足以下条件时使用数据变更频率低每天重建索引不超过2次查询条件复杂多关键词、布尔运算对搜索结果有相关性排序需求5. 避坑指南与最佳实践在多个项目实施中我总结了这些血泪经验参数设置陷阱修改LIKE_OPT_FLAG后需要重建执行计划缓存CALL SP_CLEAR_PLAN_CACHE();索引选择策略数据量10万普通B树索引右模糊LIKE AB%10万~100万POSITION函数索引100万考虑全文索引监控建议-- 查找低效LIKE查询 SELECT SQL_TEXT,EXEC_TIME FROM V$SQL_HISTORY WHERE SQL_TEXT LIKE %LIKE% ORDER BY EXEC_TIME DESC;开发规范禁止在循环中使用LIKE模糊查询避免在JOIN条件中使用LIKE超过3个百分号的LIKE必须走评审流程最近在处理一个政府项目时我们发现某接口偶尔会超时。通过ET工具分析原来是开发在循环里调用了LIKE查询。改成先批量查数据再程序过滤后接口耗时从8秒降到200毫秒。
达梦数据库-实战-03-SQL优化之LIKE模糊查询的索引失效与参数调优
1. 从生产案例看LIKE模糊查询的索引失效去年我遇到一个典型的性能问题某财务系统每月1号凌晨生成的报表要跑6个多小时而业务要求必须在2小时内完成。排查后发现罪魁祸首是这条SQLSELECT * FROM TRANS_LOG WHERE TRANS_DESC LIKE %手续费% AND CREATE_TIME BETWEEN 2023-01-01 AND 2023-01-31这个查询有两个致命问题首先是前后百分号的LIKE模糊匹配其次是时间范围跨度太大。当时达梦数据库的执行计划显示走了全表扫描CSCN2处理1600万条数据耗时218分钟。为什么B树索引会失效这要从索引结构说起。达梦的普通B树索引就像电话簿只能按字母顺序查找。当我们搜索%手续费%时相当于要找出所有包含手续费三个字的条目而电话簿只能查找以特定字开头的条目。这就好比让你在一本没有目录的书中找出所有提到人工智能的页码只能逐页翻找。2. LIKE_OPT_FLAG参数的黑科技2.1 参数原理解析达梦的LIKE_OPT_FLAG参数默认值为0是专门为LIKE查询设计的优化开关-- 查看当前参数值 SELECT * FROM V$PARAMETER WHERE NAMELIKE_OPT_FLAG; -- 修改参数需要DBA权限 SP_SET_PARA_VALUE(1,LIKE_OPT_FLAG,5); -- 推荐组合值这个参数有4种优化模式可以通过位运算组合使用值优化场景转换方式适用条件1首尾通配符LIKE %AB% → POSITION(AB)0需要函数索引2右模糊查询LIKE AB% → POSITION(AB)1普通索引可用4连接字符串优化LIKE A8REVERSE索引优化LIKE %AB → REVERSE() LIKE BA%需要REVERSE索引2.2 实战参数调优在我的项目中通过以下组合拳实现了性能飞跃先设置参数为组合模式SP_SET_PARA_VALUE(1,LIKE_OPT_FLAG,5); -- 14组合创建POSITION函数索引CREATE INDEX IDX_TRANS_DESC_POS ON TRANS_LOG(POSITION(手续费,TRANS_DESC));改写SQL可选-- 原始SQL SELECT * FROM TRANS_LOG WHERE TRANS_DESC LIKE %手续费%; -- 优化后等效写法 SELECT * FROM TRANS_LOG WHERE POSITION(手续费,TRANS_DESC)0;实测效果1600万数据查询从218分钟降到47秒执行计划从CSCN2变为SSEK2二级索引定位。3. 函数索引的进阶玩法3.1 POSITION函数索引的局限性虽然POSITION函数索引效果显著但有两个坑需要注意固定值问题索引只对特定关键词有效-- 这个索引只能优化手续费查询 CREATE INDEX IDX_POS_1 ON TABLE(POSITION(手续费,COL)); -- 对以下查询无效 SELECT * FROM TABLE WHERE COL LIKE %服务费%;多条件问题每个关键词需要单独索引-- 需要创建两个索引 CREATE INDEX IDX_POS_1 ON TABLE(POSITION(手续费,COL)); CREATE INDEX IDX_POS_2 ON TABLE(POSITION(服务费,COL));3.2 REVERSE索引方案对于后缀模糊查询如LIKE %公司可以结合REVERSE索引-- 步骤1创建REVERSE函数索引 CREATE INDEX IDX_REVERSE ON EMPLOYEE(REVERSE(NAME)); -- 步骤2改写SQL SELECT * FROM EMPLOYEE WHERE REVERSE(NAME) LIKE REVERSE(%公司);这个方案的原理是把后缀匹配转为前缀匹配。在我的测试中300万数据查询从112秒降到1.3秒。4. 全文索引的降维打击当需要同时查询多个关键词时全文索引才是终极解决方案。去年我们为某知识库系统实施了这个方案-- 创建全文索引 CREATE CONTEXT INDEX IDX_FULLTEXT ON DOCUMENTS(CONTENT) LEXER CHINESE_LEXER; -- 定期重建索引重要 ALTER CONTEXT INDEX IDX_FULLTEXT ON DOCUMENTS REBUILD; -- 使用CONTAINS查询 SELECT * FROM DOCUMENTS WHERE CONTAINS(CONTENT,手续费 AND 服务费);性能对比200万文档方案索引大小查询耗时适用场景LIKE %X%-78秒简单查询POSITION函数索引1.2GB1.5秒固定关键词全文索引2.8GB0.3秒复杂搜索需要注意的是全文索引虽然强大但维护成本较高。建议满足以下条件时使用数据变更频率低每天重建索引不超过2次查询条件复杂多关键词、布尔运算对搜索结果有相关性排序需求5. 避坑指南与最佳实践在多个项目实施中我总结了这些血泪经验参数设置陷阱修改LIKE_OPT_FLAG后需要重建执行计划缓存CALL SP_CLEAR_PLAN_CACHE();索引选择策略数据量10万普通B树索引右模糊LIKE AB%10万~100万POSITION函数索引100万考虑全文索引监控建议-- 查找低效LIKE查询 SELECT SQL_TEXT,EXEC_TIME FROM V$SQL_HISTORY WHERE SQL_TEXT LIKE %LIKE% ORDER BY EXEC_TIME DESC;开发规范禁止在循环中使用LIKE模糊查询避免在JOIN条件中使用LIKE超过3个百分号的LIKE必须走评审流程最近在处理一个政府项目时我们发现某接口偶尔会超时。通过ET工具分析原来是开发在循环里调用了LIKE查询。改成先批量查数据再程序过滤后接口耗时从8秒降到200毫秒。
