问题

业务中遇到一个非常慢的查询，大致可以简化为以下SQL：

1
2
3
4
5

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为后模糊查询，为其创建函数索引：

1

CREATE INDEX idx_t1_func_c2 ON t1(CASE WHEN c2 NOT LIKE 'ABC%' THEN 1 ELSE 0 END);
  • 为c3和c4的绑定条件创建函数索引：
1

CREATE INDEX idx_t1_func_c3c4 ON t1(CASE WHEN c3 in (...) and c4=-1 THEN 1 ELSE 0 END);
  • c5不能改变表数据类型，但可以转换为时间类型，为转换后的时间类型创建索引，即为c5创建函数索引：
1

CREATE INDEX idx_t1_func_c5 ON t1(to_date(c5, 'YYYY-MM-DD,HH24:MI:SS'));

修改查询

添加索引后，还需要按照索引使用相同的函数来改造条件语句，最终优化后的SQL：

1
2
3
4
5

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。