1、SQL传参必须使用预编译方式,避免将参数值直接拼装到SQL中;
2、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:a is not null 改为 a>0 或a>’’,如:
select id from t where num is null
可以这样查询:
select id from t where num >0
或者
select id from t where num >’ ’
3、应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。
4、应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,用UNION ALL替换OR (适用于索引列)如:
select id from t where num=10 or Name = 'admin'
可以这样查询:
select id from t where num = 10
union all
select id from t where Name = 'admin'
5、in 和 not in 也要慎用,否则会导致全表扫描,用EXISTS替代IN、用NOT EXISTS替代NOT IN:如:
select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)
6、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2 = 100
应改为:
select id from t where num = 100*2
7、应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ --生成的id
应改为:
select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'
8、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引;
9、对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差;
10、select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的;
11、任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段;
12、LIKE操作符尽量避免使用like ’%***%’,能用like’***%’不要使用like ’%***%’,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引;
13、能用union all操作符进行表连接的,不要使用union操作符进行表连接的;
14、ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应绝对避免在order by子句中使用表达式;
15、sql语句用大写的;因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行;
总是使用索引的第一个列:
如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引. 这也是一条简单而重要的规则,当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引;
- 避免改变索引列的类型,及条件字段类型和传参类型。
SQL什么条件不会使用索引?
查询条件 | 不能使用索引原因 |
INDEX_COLUMN <> ? INDEX_COLUMN not in (?,?,...,?) | 不等于操作不能使用索引 |
function(INDEX_COLUMN) = ? INDEX_COLUMN + 1 = ? INDEX_COLUMN || 'a' = ? | 经过普通运算或函数运算后的索引字段不能使用索引 |
INDEX_COLUMN like '%'||? INDEX_COLUMN like '%'||?||'%' | 含前导模糊查询的Like语法不能使用索引 |
INDEX_COLUMN is null | B-TREE索引里不保存字段为NULL值记录,因此IS NULL不能使用索引 |
NUMBER_INDEX_COLUMN='12345' CHAR_INDEX_COLUMN=12345 | Oracle在做数值比较时需要将两边的数据转换成同一种数据类型,如果两边数据类型不同时会对字段值隐式转换,相当于加了一层函数处理,所以不能使用索引。 |
a.INDEX_COLUMN=a.COLUMN_1 | 给索引查询的值应是已知数据,不能是未知字段值。 |
注: 经过函数运算字段的字段要使用可以使用函数索引,这种需求建议与DBA沟通。 有时候我们会使用多个字段的组合索引,如果查询条件中第一个字段不能使用索引,那整个查询也不能使用索引 如:我们company表建了一个id+name的组合索引,以下SQL是不能使用索引的 Select * from company where name=? |