在亿万级的数据库的查询中如何提高效率

一）数据库设计方面  

1）首先避免全局扫瞄，在where和order by等涉及的列上建立索引。

2）尽量避免的where子句中对null进行判断，因为对null值进行判断将导致引擎放弃使用索引，而进行全表的扫描。例如：select id form table where num=null;将num的默认值设置成，这样数据库就会按照索引机制进行查询。                                     

3）并不是所有的索引对查询都有效果，当表中的索引重复值比较多的时候数据库引擎会放弃使用索引进行查询。如一个表中gender 字段的 man woman各占一半，那么数据库会放弃用索引进行查询。

4）索引并不是越多越好，建立索引固然会提高数据库的查询效率，但是会降低插入和删除的效率，因为在对数据库表进行更新操作时候会进行索引重建，如果索引过多会大大增加重建的负担，一个表的最大索引数最好不要超过6个，对于不常使用的列不需要建立索引。

5）尽可能避免对索引的更新改变。因为索引的数据列的顺序就是表记录的物理存储顺序，一旦改变列值将会导致整个表顺序的改变，会耗费相当大的资源。所以如果一个列需要经常进行改变就需要考虑是否要在这个列上建立索引了。

6）尽量使用数字类型的字段，因为数据库执行引擎在对比字符串的时候会逐一比较，这样会增加系统资源的开销，而如果使用了数字类型，只需要比较一次就可以。

7）尽可能使用varchar/nvarchar类型来代替char/nchar类型，因为变长字段的存储的空间小，因此查询的效率高一些。

8）尽可能使用表变量来代替临时表，如果表中包含大量的数据请注意索引有限（只有主键索引）

二）SQL语句方面

  1） 尽量避免在where子句中使用 != < > 因为这样数据库引擎会放弃索引进行全局扫描。   

   2）尽量避免使用or连接条件，否则将导致引擎放弃索引进行全表扫描。

   例如：select id from table where num=10 or num=20 可以改写成这样 ： select id from table where num=10 union all select id from table where num=20;

 3)  in和not in 也要谨慎使用，因为使用这个也将会导致数据库引擎放弃索引换成全表扫描。例如：

select id from table num in (1,2,3)  对于条件中连续的数据可以使用如下语句 select id from table where num between 1 and 3

4)如下的语句也会进行全表扫描  select id from table where name like %hhuu%

5) 如果在where子句中使用参数也将会导致全局扫描。

6）尽量避免在where子句中对字段进行操作，这样数据库引擎也会进行全表扫描。例如：

select id from table where num/2=10;  可以改造成如下：select id from table where num = 10*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).不要写一些没有意义的查询，如需要生成一个空表结构： 

select col1,col2 into #t from t where 1=0 

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： 

create table #t(…)

10) 很多时候用 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)

11)尽量避免使用select * ，用具体的字段进行代替。

12）尽量避免使用游标，游标的执行效率非常低，如果数据超过一万行就考虑使用其他进行替代。

13）尽量避免向客户端返回大量数据。

14）尽量避免使用大的事务，提高系统的并发能力。

四）Java方面的

1）尽量少造对象。

2）合理摆正系统的设计位置。，大量数据操作一定要和少量数据操作分开，大量的数据肯定不是ORM框架搞定的。

3）使用JDBC连接数据库进行数据的操作。

4）控制好内存，让数据流动起来，不是把所有的数据都读进内存才进行处理，而是边读数据边进行处理。

5）合理利用数据，有的数据要进行缓存。