SQL优化问题

最近在找工作,面试的时候面试官经常会问到SQL优化的问题。面试的时候回答的不尽如人意,回来后总结了一些(参考网上的)。

SQL优化的具体操作

1.在表中建立索引,优先考虑where和group by使用的字段。

2.尽量避免使用select * ,使用select * 查询会返回无用的字段,降低查询的效率。例如:

select * from t;

优化方式: 用具体要返回的字段代替 *

3.尽量避免使用in和not in,会让数据库引擎放弃索引而进行全表的扫描。例如:

select * from t where id in(2,4);

select * from t1 where username in(select username from t2);

优化方式: 如果是连续值,使用between代替;如果是子查询可以使用exists代替。例如:

select * from t where id between 2 and 4;

select * from t1 where username exists (select * from t2 where t1.username = t2.username);

4.尽量避免使用or,这会让数据库引擎放弃索引而进行全表扫描。例如:

select * from t where id = 1 or id= 3;

优化方式: 可以使用union代替or。例如:

select * from t where id = 1 union select * from t where id = 3;

PS: 如果or两边的字段是同一个,如例子中的这样,貌似两种方式效率差不多,即使union扫描的是索引,or扫描的是全表。

5.尽量避免在字段开头使用模糊查询,这样会导致数据库引擎放弃索引而进行全表扫描。例如:

select * from t where username like '%li%';

优化方式: 尽量在字段后面使用模糊查询。例如;

select * form t where username like 'li%';

6.尽量避免null值得判断,这会导致数据库引擎放弃索引而进行全表扫描。例如:

select * from t where score is null;

优化方式: 可以给字段添加默认值0,对0值进行判断。例如:

select * from t where score = 0;

7.尽量避免where条件中等号左边进行表达式、函数操作,这会导致数据库引擎放弃索引而进行全表扫描。例如:

select * from t where score/10 = 9;

select * from t where substr(username,1,2) = 'li';

优化方式: 可以将表达式、函数操作移到等号的右侧。例如:

select * from t where score = 10*9;

select * from t where username = 'li%';

8.当数据量大的时候,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,这样会使数据库引擎放弃索引而进行全表扫描。例如:

select * from t where 1 = 1........

优化方式: 用代码拼装sql的时候进行判断,没有where时添加where,有where时添加and。