1 sql逻辑要选有索引的字段做为查询条件
表数据量小,或者查询的字段重复性高则不需要索引。
索引不是越多越好,一个表在业务应用当中实际查询频率高的字段加索引。
2 子查询中要尽量缩小结果集
举例:
with t1 as (select * from a)
select * from b,t1 where t1.id=b.id;
给子查询t1多加字段查询条件来缩小t1返回的结果集,效率更高
3 sql逻辑中尽量避免给查询条件中任何的字段加函数,如果有实际需求,可在合理范围内添加函数类型的索引
举例:
不合适的写法:
select * from test
where to_char(test_time, 'yyyy-mm-dd')>'2024-01-01'
and to_char(test_time, 'yyyy-mm-dd')<'2024-02-01';
合适的写法:
select * from test
where test_time>'2024-01-01'::date
and test_time<'2024-02-01'::date;
4 sql逻辑中尽量避免使用or逻辑,如果or两边的字段都有索引一般会走索引,但是这种写法不稳定。可以用union代替or
举例:
不合适的写法:
select * from test where name='Tom' or phone='123456';
合适的写法:
select * from test where name='Tom'
union
select * from test where phone='123456';
5 sql逻辑中的查询字段要避免对jsonb类型的字段做遍历,效率低下且不可控。
举例:
select * from test
where add_info ->> 'code' in ('123' ) ;
6 sql逻辑中避免在in()语法的括号中存太多参数,有不走索引的风险
7 避免使用!=,<>作为判断条件,B树索引不会在这种情况下走索引
8 应对字段存默认值,避免做is null或者 is not null 的判断,如有需要可以建条件索引
9 sql逻辑中的查询字段能精确查询就避免模糊查询
若必须模糊查询,需注意 like ‘abc%’ 在大多数情况下走索引,而 like ‘%abc%’ 必然不走索引。
10 sql逻辑中要返回的字段都是需要的,不要不需要的字段
尽量避免使用select * from test,用具体字段名称代替*
11 如果逻辑过于复杂或关联的表过于多,应该使用子查询或者java代码把语句分成几个及逻辑模块
这样虽然不能获得效率最高,但是可以得到稳定的开销。
分割的原则是,业务逻辑或数学运算尽量放在java端,将表的连接、排序等数据逻辑放在sql端。
12 尽量避免使用过多的连接
如果连接的情况过多,需要考虑表本身的设计是否合理,字段设置是否合理。
13 有索引不能代表sql效率一定会高,建议提前做测试。
开发过程中有较复杂或跟业务较频繁的表相关的查询sql时,可以先在测试库及正式库做实际性能的简单测试,有性能问题进行优化。