常见sql语句效率优化方式

首先介绍几条优化原则：

• 尽量避免where中包含子查询； 
• where条件中，过滤量最大的条件放在where子句最后； 
• 采用绑定变量有助于提高效率； 
• 在索引列上使用计算、改变索引列的类型、在索引列上使用!=将放弃索引； 
• 避免在索引列上使用is null和is not null； 
• 使用索引的第一个列； 
• 用union-all替代union； 
• like ‘text%’使用索引，但like ‘%text’不使用索引； 
• select a, b from tab 比 select * from tab 效率高。

 

一、“like '%%'”的优化，建议使用instr(?,?)效率高

单表有超大数量级的数

1、oracle和mysql中

如果有索引的情况下，使用like 'aa%'或者like '%aa'效率较高，因为如果是like "%aa%"那么会导致全表扫描！效率非常低！

如果没有索引，那么最好使用instr(stpm,字符串)函数解决问题

举例：

select  * from xslsb where spmc like '%毛线%';               //效率很低

select * from xslsb where (instr(spmc,'毛线'))>0;            //效率高

2、sql server中

2005以后，采用contains(spmc,'毛线')函数

 

二、批量插入

如果一条一条插入，那么效率很低

insert into t_user values(?,?,?);

所以采用批量

insert into t_user select * from t_user2;

其他延伸：t_user 表字段要和t_user2字段的结构和名称一致

三、exists和in的效率

exists相对较高，适用于外表小，内表大的情况

        select  * from  外表小  where exists( select * from  内表大);

       in相对较低，适用于外表大，内表小的情况

select  * from  外表大  where  字段  in (select  字段  from   内表小);

如果内表和外表差不多，那么exists和in效率差不多！

四、对查询的条件添加索引

1、mysql 

     create index t_user_index on t_user(username);

     drop index t_user_index on t_user;                         --删除索引

 

2、oracle

     create index t_user_index  on t_user(username);

     drop index t_user_index;                                         ---删除索引

五、使用union all 而少用union

union all执行的内部机制少

union 在表联合查询后还会进行筛选操作，费时！

六、连续值查询

1、使用between and

原因：不会全表查询 

2、不使用in()

原因：导致全表查询，效率低

七、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t_user where substring(name,1,3) = 'abc'       -–name以abc开头的id

改为：select id from t where  name like 'abc%'

八、尽量避免大事务操作，提高系统并发能力。

九、跨oracle用户访问

select count(*) from (select hykh from user.tablewhere  scsj>=to_date('2015-11-01','yyyy-MM-dd') group by hykh);

user代表用户

table代表user中的table表

十、创建临时表可以提高查询速度

如果在1000万的数据中查询结果，那么以下第二种查询效率会高很多倍！

(1)select * from t_user where name='suns';

(2)select * from t_user tu,user us where   tu.id=us.id and  us.name='suns';

十一、使用mysql 的扩展语句replace into 与insert into 的注意

1、insert into 效率低，但是通用性能很好

2、replace into 效率高  ，但是如果表中有自增长的主键，那么一定要谨慎选择使用，因为如果主键的增长id一旦达到最大后就无法继续增长了！