文章首发于个人博客
问题
业务中遇到一个非常慢的查询，大致可以简化为以下SQL：
select * from t1
where c1 not in (...)
and c2 not like 'ABC%'
and c3 in (...) and c4=-1
and c5>'2020-01-01,00:00:00'
其中：
-
有索引且命中的条件已简化掉了
-
c5为自定义格式的时间字符串 -
c2和c5有索引（普通索引），c1、c3和c4无索引 -
c3和c4为绑定条件 -
c1、c3和c4均为小值域列
分析
-
not in和not like会导致普通索引失效 -
为
小值域列添加普通索引无法提供预期的效果 -
c3和c4为绑定条件，且条件固定不变，视为一体进行处理的话，可一定程度上扩充列的值域 -
c5虽然已有索引，但字符串比较操作还是太低效
解决方案
基于以上特殊的情形，搜索并尝试后发现，函数索引恰好可以达到预期，而使用的数据库又刚好支持（Oracel和PostgreSQL完全支持）。
添加索引
-
将
c1的not in手动改为in查询，即可利用已有索引 -
c2的not like为后模糊查询，为其创建函数索引：
CREATE INDEX idx_t1_func_c2 ON t1(CASE WHEN c2 NOT LIKE 'ABC%' THEN 1 ELSE 0 END);
- 为
c3和c4的绑定条件创建函数索引：
CREATE INDEX idx_t1_func_c3c4 ON t1(CASE WHEN c3 in (...) and c4=-1 THEN 1 ELSE 0 END);
c5不能改变表数据类型，但可以转换为时间类型，为转换后的时间类型创建索引，即为c5创建函数索引：
CREATE INDEX idx_t1_func_c5 ON t1(to_date(c5, 'YYYY-MM-DD,HH24:MI:SS'));
修改查询
添加索引后，还需要按照索引使用相同的函数来改造条件语句，最终优化后的SQL：
select * from t1
where c1 in (...)
and (CASE WHEN c2 NOT LIKE 'ABC%' THEN 1 ELSE 0 END)=1
and (CASE WHEN c3 in (...) and c4=-1 THEN 1 ELSE 0 END)=1
and to_date(c5, 'YYYY-MM-DD,HH24:MI:SS')>to_date('2020-01-01,00:00:00','YYYY-MM-DD,HH24:MI:SS')
效果
清除缓存测试，原查询大约需要57s，优化后大约13s。
