点击上方"java全栈技术"关注我们，每天学习一个java知识点

1 正确使用索引

数据库性能很关键的一点在于sql是否能正确使用索引，我们应该尽量避免无法使用索引的sql写法。

示例：表t的id和name数据类型分别为number和varchar2，且id为从1开始递增的主键，表记录数为10万

• 索引查询范围尽可能小，否则可能用不到索引

无法使用索引（查询范围太大）：

select * from t where id>10;

正确使用索引（查询范围很小）：

select * from t where id<10;

• 尽量避免对索引列进行函数计算，请尽可能将函数运算移到条件右边或者创建函数索引。

无法使用索引（左边条件使用了函数）：

select * from t where id+5=10;

正确使用索引（将函数运算移到条件右边）：

select * from t1 where id=10-5

或者创建函数索引

create index idx_t1 on t1(id+5)

• 两个字段直接进行比较无法使用索引（本例中id1为主键）

无法使用索引（条件两边含不同字段）：

select * from t where id1=id2+10

正确使用索引（将两个字段移到条件左边并创建函数索引）：

select * from t1 where id1-id2=10;
create index idx_t1 on t1(id1-id2)

• 不同类型的数值比较可能无法使用索引

无法使用索引（条件两边含不同类型字段，db会做隐式函数转换，使得索引无法正常使用）：

select * from t where name=123

正确使用索引（两边数据类型一致）：

select * from t where name='123'

• 对于复合索引，sql语句一般情况下必须使用第一个索引字段，索引才会生效(本例中id,name为联合索引)

无法使用索引（id为索引列的第一个字段，单独查询name一般不会使用索引）：

select * from t where name='123'

正确使用索引（条件中使用了索引列的第一个字段,where条件中的字段先后顺序没有关系）：

select * from t where id=1select * from t where id=1 and name='abc'select * from t where name='abc' and id=1

• 查询条件中，尽量避免使用null作为条件，如果有查询需求，可以在null字段上创建默认值

无法使用索引（oracle不行，mysql可以）：

select * from t where id is null;select * from t where id is not null;

正确使用索引：

alter table t modify id int default 0;select * from t where id is null

转换为

select * from t where id=0

• 对于索引的比较，尽量避免使用 <>（!=）

无法使用索引(无论任何情况都只能全表扫描)：

select * from t where id !=0;

正确使用索引（如果非0值占少数，则两个条件都可能用到索引）：

select * from t where id>0 or id<0

• like查询时，%不能出现在条件最左边

无法使用索引(无论任何情况都只能全表扫描)：

select * from t where name like '%abc;select * from t where name like '%abc%;

• 如果要高效实现如上查询，建议使用全文本索引或者第三方搜索引擎。

正确使用索引：

select * from t where name like 'abc%'

• 如果可以，尽可能只查询索引列

需求：只需要查询表t的id和name值,id和name上有联合索引

低效查询（先扫描索引，再回表）：

select * from t1 where id=1 and name=>'abc'

高效查询(只需要扫描索引)：

select id,name from t1 where id=1 and name>'abc'

2 尽量减少数据库的访问

• 使用乐观锁，避免大多数无效数据库访问

低效sql：

select count(1) from t1 where conditionif count(1)>0 then update t1 set ...

高效sql：

update t1 set ...if result=0 thenexception

• 高效存在性判断

低效sql：

select count(*) into t_count from t where condition;if t_count> 0 then select cols into t_cols from t where condition;elseotherstatement;end;

高效sql：

select 1 into t_count from t where condition and rownum<2;if exist(1) then select cols into t_cols from t where condition;elseotherstatement;end;

• 使用casewhen函数减少数据库访问次数

低效sql（需要访问3次数据库）

select sum(sum1) from( select sum(data_object_id)*0.001 sum1 from t1 where data_object_id<=10000union select sum(data_object_id)*0.002 sum1 from t1 where data_object_id>10000 and data_object_id<=50000union select sum(data_object_id)*0.003 sum1 from t1 where data_object_id>50000);

高效sql（访问一次数据库）

select sum(case when data_object_id<=10000 then data_object_id*0.001 when data_object_id>10000 and data_object_id<=50000 then data_object_id*0.002 when data_object_id>50000 then data_object_id*0.003 else 0end) from t1;

3 尽量减少排序

• 用unionall替代union

二者的区别是后者会合并后排序去重，前者不需要这个工作

• 减少排序结果集

示例1：

低效sql（全部记录进行排序后过滤）：

select name,count(1) from t1 group by name having name>'abc'

高效sql（过滤数据后再排序）：

select name,count(1) from t1 where name>'abc' group by name;

示例2：

低效分页查询（对所有满足条件的记录进行排序后分页）：

select object_id,object_name,rn from (select a.*, rownum rn from (select * from t1 where object_id>0 order by object_id) a )where rn <= &last and rn >= &first;

高效分页查询（只对满足条件的记录排序前rn条）：

select object_id,object_name,rn from (select a.*, rownum rn from (select * from t1 where object_id>0 order by object_id) a where rownum <= &last)where rn >= &first;

• 尽可能利用索引排序

低效sql（排序操作在表上）：

select * from (select rownum no,t.* from (select * from t where lmodify> (sysdate - interval '60' day) order by lmodify desc) t where rownum < 24100 ) where no > 24000

高效sql（排序操作在索引上）

select * from t e,(select * from (select c.*,rownum rcount from (select rowid rn,lmodify from t where lmodify> (sysdate - interval '30' day)order by lmodify desc ) cwhere rownum<24100) where rcount>24000) dwhere e.rowid=d.rn

4 in/exists使用规范

示例：当有a、b 两个结果集，当结果集b 很大时，a 较小时，适用exists，如：

select * from a where exists(select 1 from b where a.column = b.column);

当结果集 a 很大时，b 很小时，适用in,如：

select *from awhere a.column in(select b.columnfrom b)

5 避免锁问题

• 事务尽可能小，尽量避免大事务，频繁提交的大事务会明显带来并发的降低。

• 批量处理的任务最好能较少执行频率，且最好放在访问量低的时候做。

• 避免死锁

死锁产生原因：session间由于对锁的获取产生了资源竞争，导致发生session的互相等待的僵持状态，即死锁。一般非事务锁（比如表锁）由于锁是一次性完全占有和释放，不会在同一资源上产生互相等待，因此不会产生死锁，比如mysql中的MyISAM存储引擎；而事务锁（比如页锁和行锁）由于在同一事务中锁是逐渐获取的，如果两个session获取锁的顺序相反，则可能产生死锁。Oracle和mysql中的InnoDB存储引擎都属于这一类。

死锁示例：

死锁的处理：oracle和mysql对于死锁会自动探测并处理，一般是选择其中一个事务进行回滚。

死锁的危害：降低系统并发性和用户体验。

如何避免死锁：在不同的事务中对表的操作顺序保持一致。比如在两个方法中都对order和order_detail进行更新，那么要保证两个方法中两个表的更新顺序是一致的。