1.索引必须合理创建,过多的创建索引会增加增删改的负担。
2.有空值的字段尽量不要建索引。
3.尽可能建立唯一的索引。
4.常用的字段组合可以建立复合索引。
5.不要在索引列使用以下操作,否则索引会失效。
a.not 、<>
b. is null ,isnot null
c.like,like比较特殊,当使用 like '%xx%'时,索引会失效;左模糊和右模糊索引都可以用,当然,右模糊时需要建立对应的倒索引。
比如:ZTE_FBP_EMPLOYEES_N3是表ztenet.zte_fbp_employees 的在employee_number列上的索引
———————————不走N3索引————————————————————
Not:
SELECTt.employee_id
FROM ztenet.zte_fbp_employees t
WHERE t.employee_number not in('000');
Is not null:
SELECTt.employee_id
FROM ztenet.zte_fbp_employees t
WHERE t.employee_number is not null;
Is null:
SELECTt.employee_id
FROM ztenet.zte_fbp_employees t
WHERE t.employee_number is null;
Like:
SELECTt.employee_id
FROM ztenet.zte_fbp_employees t
WHERE t.employee_number like '%001%';
SELECTt.employee_id
FROM ztenet.zte_fbp_employees t
WHERE t.employee_number like '%001';
——————————走N3索引————————————————————
Is not null:
SELECTt.employee_id
FROM ztenet.zte_fbp_employees t
WHERE t.employee_number>'';
Like:
SELECTt.employee_id
FROM ztenet.zte_fbp_employees t
WHERE t.employee_number like '001%';
//创建一个反的索引,然后左like
CREATE INDEX USER_INFO_IDX ON USER_INFO(UPPER(REVERSE(FULL_ID)));
SELECTt.employee_id
FROM ztenet.zte_fbp_employees t
WHERE t.employee_number like '%001';
6.不要在索引列执行计算、类型转换,否则索引会失效,反之,有时我们会故意让索引失效,这时我们就可以通过在索引列计算、类型转换等等方式来实现。
比如:bill_code是表zte_ets_air_bills的索引
——————————不走N1索引————————————————————
//通过计算,取消不需要的索引
SELECT*
FROM zte_ets_air_bills t
WHERE t.bill_code||'' = '000'
AND t.bill_id = 1
//转换让其不走索引
SELECT*
FROM zte_ets_air_bills t
WHERE NVL(t.bill_code,'N') = '000'
AND t.bill_id = 1
注意:列和对应值的类型一定要写对,否则可能导致不走索引
此时走的是列名determiner 的索引N2,因为N2是varchar
SELECT*
FROM ztefbp.zte_fbp_pos_stru_elements el
WHERE 1 = 1
AND el.determiner = '0'
AND el.pos_structure_id = 10001
若写成如下,则不会走索引N2了,因为demerminer=0这句默认转换成了to_number(demerminer)=0,索引失效了。
SELECT*
FROM ztefbp.zte_fbp_pos_stru_elements el
WHERE 1 = 1
AND el.determiner = 0
AND el.pos_structure_id = 10001
7.如果是组合索引,条件只用到一个列,这时可以把另外一个列用于一个客观条件,
比如:ZTE_FBP_EMPLOYEES_N1是由dept_id和employee_number组成的索引
——————————不走N1索引————————————————————
SELECTt.employee_id
FROM ztenet.zte_fbp_employees t
WHERE t.employee_number = '000'
——————————走N1索引—————————————————————————
SELECTt.employee_id
FROM ztenet.zte_fbp_employees t
WHERE t.employee_number = '000'
AND t.dept_id >= 0
8.不是走索引就一定快,优化时根据实际情况来进行选择。