数据查询优化方案

在数据库设计以后期的开发阶段，我们没有涉及到查询优化，然而在实际的查询过程中，感觉数据的提取速度并不怎么理想，带着这个问题，我整理了一些相关资料，现汇报如下：

对于数据查询的优化，我们可以从以下几个方面考虑：

第一，表结构的设计.
这个产品一开始并没有定型，是边做边修改，因此注定表的设计就没有前瞻性!
同时，重新设计数据库，设计表结构，在时间上是不允许的，这个方面可以放弃了。


第二，服务器的部署.
数据库服务器架设在美国，我在中国去访问肯定会很慢，这不用试的，如果架设在中国速度就会不一样的！
希望以后会有更多的服务器来解决这个问题。


第三，标准sql查询语句的优化.
针对目前所拥有的问题与原因，还有诸多因素，结合项目的实际，我觉得应该从以下几点中进行sql的优化:

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20

5.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2

9.应尽量避免在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'

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)

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

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

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

30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

具体的SQL语句在很多情况下需要结合实际的应用情况来写，这我将在具体的优化中实现。


第四，hibernate性能优化之一
由于hibernate是对jdbc的一个封装，而且在session的缓存中存在着相互关联的对象。对于一个类的访问将导致对另外一个关联类的访问，这样将导致大量的sql语句产生，所以为了提升hibernate性能减少频繁访问数据库，就需要对hibernate进行优化。
我们知道在hibernate的默认情况下，比如：在一对多的情况下，当我们访问Customer类后，又同时访问它相关联的Order类，但是如果我们不需要order的信息呢？这将导致hibernate的性能下降。
怎么解决呢？下面我们看一下hibernate加载一个对象的方式有哪些？
1.立即加载
  所谓立即加载，就是指当加载完实体对象后，立即加载其关联的对象数据，在Customer.hbm.xml里的内容为：
<set name="order" table="ord" cascade="all" lazy="false" inverse="true">
  <key column="c_id"/>
  <one-to-many class="com.lovo.Order"></one-to-many>
</set>
-------------------

从运行的结果中我们可以看出hibernate连续调用了两次sql,分别完成了对Customer和order对象的加载，这就是立即加载的基本原理，上面配置里面cascade="all"表示当我增删改这个实体时是不是要对相关联的实体进行增删改操作，在一般情况下用delete-update,lazy="false"表示立即加载这个对象，inverse="true"表示在一对多的情况下，把对象的加载交给多的一方进行维护。就好比老板和我们，老板不可能可照顾所有的人吧，得让我们自己照顾自己。呵呵，这个举例只是好理解而已。
2.延迟加载
当hibernate加载Customer对象时同时加载了关联的order对象，如果只需要读取Customer对象的信息而不需要读取order类的信息的话将会导致性能损耗，hibernate引入了延迟加载的方式避免了这一问题，
<set name="order" table="ord" cascade="all" lazy="true" inverse="true"><!--hibernate默认情况也是true-->
  <key column="c_id"/>
  <one-to-many class="com.lovo.Order"></one-to-many>
</set>
与立即加载不同之处在于，hibernate没有在加载Customer对象时就加载order对象并读取对象的信息，而是在调用customer.order.size()才会读取order的信息，这就是延迟加载的机制，当真正需要访问关联对象才执行sql语句进行数据读取。

3.预加载 
  所谓预加载就是指通过外连接(out-join)来获得对象的关联实例;
  比如：Query query = session.createQuery("from Customer c left join c.order");
  运行上面代码，hibernate会生成一条sql语句,相对于立即加载情况下的读取多条sql语句，预加载方式在性能上带来了更多的提升，减少了对数据库的访问次数，但是，我们发现预加载的情况下，关联对象的实例也会被创建。所以，这也是一种不推荐的加载方式，应当尽量使用延迟加载方式。
4.批量加载
  hibernate中的批量加载就是批量提交多个限定条件,一次完成多个数据的读取,例如：
  from Customer where c_id=1;
  from Customer where c_id=2;
  我们可以将其整合为一条语句：
   from Customer where c_id in(1,2);
  在hibernate的关联关系中，我们可以在立即加载和延迟加载里设定批量加载的数目，从而减少查询语句的数目，提高延迟加载和立即加载的性能。在映射文件中<set>元素的batch-size属性用于设置批量加载的数量，Customer类的映射文件有如下:
<set name="order" table="ord" cascade="all" lazy="true" inverse="true" batch-size="10"><!--一般小于10-->
  <key column="c_id"/>
  <one-to-many class="com.lovo.Order"></one-to-many>
</set>
可以发现在使用批量加载的情况下，select语句使用了in关键字来限定范围，从而减少了select语句的使用，而in所限定的范围大小与"batch-size"属性的值有关，值越小范围就越小，反之越大，一般来说batch-size属性设置为一个小于10的数值，由此可见，批量加载的方式可以成为延迟加载或立即加载的一种优化解决方案，采用这种方式抓取对象又被称为批量抓取(Batch fetching);

hibernate查询优化：
我们已经理解了hibernate中对象加载方式后，可以看出在hibernate中主要有如下几个方面来优化查询性能：
1.降低数据库的访问的次数和频率，减少select语句的数量，对延迟加载和立即加载设置批量抓取策略。
2.使用延迟加载，避免不需要的多余数据，
3.使用投影查询，避免查询数据占用缓存。
4.减少访问字段,降低访问数据库的数据量，利用query()对象的iterate()方法
  query的list()和iterator方法都可以执行HQL查询语句，但是list()方法会直接操作sql访问数库，而iterator方法会根据主键字段到一、二级缓存里查找，如果有就加入到查询结果中，没有就执行额外的查询语句。
Query q1=session.createQuery("from Customer");
List it1=q1.list();
Query q2=session.createQuery("from Customer");
List it2=q2.iterate();

list()方法查询所有字段信息，而iterate()方法查询的却不是所有字段而是c_id,也就是说减少了访问字段，同时iterator()方法根据c_id再向缓存里进行查找对象，如果缓存里没有数据最后再进行数据库的访问。

hibernate缓存:
hibernate为什么要有缓存呢？？和其它关系型数据库一样，
我们不可能每次相同的查询都去与数据库进行交互吧，也就是说hibernate的缓存就是一块内在空间，充当数据库在内存中的一个临时容器，当我们每次执行session.load(),save().update()时，或者query.list(),iterate()方法时，如果在Sesssion中不存在相应对象，hibernate就把该对象加入到缓存中。比如：我们查询ID为了的用户，由于第一次在缓存中没有，就与数据库进行交互，并且加入到一级缓存中，第二次呢？由于一级缓存中已经存在了，所以直接从一级缓存中获取数据，提高了查询性能。
缓存分为以下几种：
  1。一级缓存：必须的，缓存中每个持久对象都对应一个OID，用clear()清除对象.
2.二级缓存：是一个处理分布式的缓存。不是必须的。在查询时首行是从一级缓存中查找，如果没有就去二级缓存中查找，二级缓存最大的好处在于提高了查询性能，但是，hibernate并没有提供二级缓存的实现，而是为第三方的缓存组件提供了接口，所以还需要配置。
3.查询缓存：使用查询缓存，如果查询结果集中包含实体，二级缓存中只会存放实体OID，如果查询部分属性，则二级缓存会存放所有属性值。hibernate默认是不进行查询缓存的。如果要进行缓存，需要调用Query.setCacheable(true)方法。
Session管理:
首先，什么是线程安全，什么是线程不安全??
  线程安全：就是说可以有多个线程同时访问，
  线程不安全：不能有多个线程访问。否则，会造成混乱。
在Session管理中SessionFacory是线程安全，它可以被多个线程同时访问，而Session是不安全的，以前我们是在每个方法里都会使用一个session,这样造成的结果是Session的频繁创建和销毁，内在消耗也相应增大了。在Session管理中，可以使用ThreadLocal模式，它为多线程的情况下并发访问问题提供了一种隔离机制，例如:
public static Session getSession(){
  Session session=(Session)threadLocal.get();//获得session对象
  if(session==null){
   session = SessionFactory.openSession();
   threadLocal.set(session);
  }
  return session;
}
ThreadLocal的使用将使得我们在线程级进行Session重用，从而充分利用一缓存中的已有的数据，避免数据库访问开销的浪费和大量临时对象的反复创建。
hibernate的批量处理：加入10000条数据
PrepareStatement pst = conn.preparedStatement(...);
for(int i=0;i<10000;i++){
  pst.addBatch();
}
int[] count=pre.executeBatch();
由于会产生大量的对象存放在内存中，所以性能不好


第五，hibernate性能优化之二
一、批量修改和删除

　　在Hibernate 2中，如果需要对任何数据进行修改和删除操作，都需要先执行查询操作，在得到要修改或者删除的数据后，再对该数据进行相应的操作处理。在数据量少的情况下采用这种处理方式没有问题，但需要处理大量数据的时候就可能存在以下的问题：

　　 占用大量的内存。

　　 需要多次执行update/delete语句，而每次执行只能处理一条数据。

　　以上两个问题的出现会严重影响系统的性能。因此，在Hibernate 3中引入了用于批量更新或者删除数据的HQL语句。这样，开发人员就可以一次更新或者删除多条记录，而不用每次都一个一个地修改或者删除记录了。

　　如果要删除所有的User对象(也就是User对象所对应表中的记录)，则可以直接使用下面的HQL语句：

　　delete User

　　而在执行这个HQL语句时，需要调用Query对象的executeUpdate()方法，具体的实例如下所示：

　　String HQL="delete User";

　　Query query=session.createQuery(HQL);

　　int size=query.executeUpdate();

　　采用这种方式进行数据的修改和删除时与直接使用JDBC的方式在性能上相差无几，是推荐使用的正确方法。

　　如果不能采用HQL语句进行大量数据的修改，也就是说只能使用取出再修改的方式时，也会遇到批量插入时的内存溢出问题，所以也要采用上面所提供的处理方法来进行类似的处理。

二、 使用SQL执行批量操作

　　在进行批量插入、修改和删除操作时，直接使用JDBC来执行原生态的SQL语句无疑会获得最佳的性能，这是因为在处理的过程中省略或者简化了以下处理内容：

　　● HQL语句到SQL语句的转换。

　　● Java对象的初始化。

　　● Java对象的缓存处理。

　　但是在直接使用JDBC执行SQL语句时，有一个最重要的问题就是要处理缓存中的Java对象。因为通过这种底层方式对数据的修改将不能通知缓存去进行相应的更新操作，以保证缓存中的对象与数据库中的数据是一致的。

三、 提升数据库查询的性能

　　数据库查询性能的提升也是涉及到开发中的各个阶段，在开发中选用正确的查询方法无疑是最基础也最简单的。

　　1 、SQL语句的优化

　　使用正确的SQL语句可以在很大程度上提高系统的查询性能。获得同样数据而采用不同方式的SQL语句在性能上的差距可能是十分巨大的。

　　由于Hibernate是对JDBC的封装，SQL语句的产生都是动态由Hibernate自动完成的。Hibernate产生SQL语句的方式有两种：一种是通过开发人员编写的HQL语句来生成，另一种是依据开发人员对关联对象的访问来自动生成相应的SQL语句。

　　至于使用什么样的SQL语句可以获得更好的性能要依据数据库的结构以及所要获取数据的具体情况来进行处理。在确定了所要执行的SQL语句后，可以通过以下三个方面来影响Hibernate所生成的SQL语句：

　　 HQL语句的书写方法。

　　 查询时所使用的查询方法。

　　 对象关联时所使用的抓取策略。

　　2 、使用正确的查询方法

　　在前面已经介绍过，执行数据查询功能的基本方法有两种：一种是得到单个持久化对象的get()方法和load()方法，另一种是Query对象的list()方法和iterator()方法。在开发中应该依据不同的情况选用正确的方法。

　　get()方法和load()方法的区别在于对二级缓存的使用上。load()方法会使用二级缓存，而get()方法在一级缓存没有找到的情况下会直接查询数据库，不会去二级缓存中查找。在使用中，对使用了二级缓存的对象进行查询时最好使用load()方法，以充分利用二级缓存来提高检索的效率。

　　list()方法和iterator()方法之间的区别可以从以下几个方面来进行比较。

　　 执行的查询不同

　　list()方法在执行时，是直接运行查询结果所需要的查询语句，而iterator()方法则是先执行得到对象ID的查询，然后再根据每个ID值去取得所要查询的对象。因此，对于list()方式的查询通常只会执行一个SQL语句，而对于iterator()方法的查询则可能需要执行N+1条SQL语句(N为结果集中的记录数)。

　　iterator()方法只是可能执行N+1条数据，具体执行SQL语句的数量取决于缓存的情况以及对结果集的访问情况。

　　 缓存的使用

　　list()方法只能使用二级缓存中的查询缓存，而无法使用二级缓存对单个对象的缓存(但是会把查询出的对象放入二级缓存中)。所以，除非重复执行相同的查询操作，否则无法利用缓存的机制来提高查询的效率。

　　iterator()方法则可以充分利用二级缓存，在根据ID检索对象的时候会首先到缓存中查找，只有在找不到的情况下才会执行相应的查询语句。所以，缓存中对象的存在与否会影响到SQL语句的执行数量。

　　 对于结果集的处理方法不同

　　list()方法会一次获得所有的结果集对象，而且它会依据查询的结果初始化所有的结果集对象。这在结果集非常大的时候必然会占据非常多的内存，甚至会造成内存溢出情况的发生。

　　iterator()方法在执行时不会一次初始化所有的对象，而是根据对结果集的访问情况来初始化对象。因此在访问中可以控制缓存中对象的数量，以避免占用过多缓存，导致内存溢出情况的发生。使用iterator()方法的另外一个好处是，如果只需要结果集中的部分记录，那么没有被用到的结果对象根本不会被初始化。所以，对结果集的访问情况也是调用iterator()方法时执行数据库SQL语句多少的一个因素。

　　所以，在使用Query对象执行数据查询时应该从以上几个方面去考虑使用何种方法来执行数据库的查询操作。

四、 使用正确的抓取策略

　　所谓抓取策略(fetching strategy)是指当应用程序需要利用关联关系进行对象获取的时候，Hibernate获取关联对象的策略。抓取策略可以在O/R映射的元数据中声明，也可以在特定的HQL或条件查询中声明。

　　Hibernate 3定义了以下几种抓取策略。

　　 连接抓取(Join fetching)

　　连接抓取是指Hibernate在获得关联对象时会在SELECT语句中使用外连接的方式来获得关联对象。

　　 查询抓取(Select fetching)

　　查询抓取是指Hibernate通过另外一条SELECT语句来抓取当前对象的关联对象的方式。这也是通过外键的方式来执行数据库的查询。与连接抓取的区别在于，通常情况下这个SELECT语句不是立即执行的，而是在访问到关联对象的时候才会执行。

　　 子查询抓取(Subselect fetching)

　　子查询抓取也是指Hibernate通过另外一条SELECT语句来抓取当前对象的关联对象的方式。与查询抓取的区别在于它所采用的SELECT语句的方式为子查询，而不是通过外连接。

　　 批量抓取(Batch fetching)

　　批量抓取是对查询抓取的优化，它会依据主键或者外键的列表来通过单条SELECT语句实现管理对象的批量抓取。

　　以上介绍的是Hibernate 3所提供的抓取策略，也就是抓取关联对象的手段。为了提升系统的性能，在抓取关联对象的时机上，还有以下一些选择。
立即抓取(Immediate fetching)

　　立即抓取是指宿主对象被加载时，它所关联的对象也会被立即加载。

　　 延迟集合抓取(Lazy collection fetching)

　　延迟集合抓取是指在加载宿主对象时，并不立即加载它所关联的对象，而是到应用程序访问关联对象的时候才抓取关联对象。这是集合关联对象的默认行为。

　　 延迟代理抓取(Lazy proxy fetching)

　　延迟代理抓取是指在返回单值关联对象的情况下，并不在对其进行get操作时抓取，而是直到调用其某个方法的时候才会抓取这个对象。

　　 延迟属性加载(Lazy attribute fetching)

　　延迟属性加载是指在关联对象被访问的时候才进行关联对象的抓取。

　　介绍了Hibernate所提供的关联对象的抓取方法和抓取时机，这两个方面的因素都会影响Hibernate的抓取行为，最重要的是要清楚这两方面的影响是不同的，不要将这两个因素混淆，在开发中要结合实际情况选用正确的抓取策略和合适的抓取时机。

　　抓取时机的选择

　　在Hibernate 3中，对于集合类型的关联在默认情况下会使用延迟集合加载的抓取时机，而对于返回单值类型的关联在默认情况下会使用延迟代理抓取的抓取时机。

　　对于立即抓取在开发中很少被用到，因为这很可能会造成不必要的数据库操作，从而影响系统的性能。当宿主对象和关联对象总是被同时访问的时候才有可能会用到这种抓取时机。另外，使用立即连接抓取可以通过外连接来减少查询SQL语句的数量，所以，也会在某些特殊的情况下使用。

　　然而，延迟加载又会面临另外一个问题，如果在Session关闭前关联对象没有被实例化，那么在访问关联对象的时候就会抛出异常。处理的方法就是在事务提交之前就完成对关联对象的访问。

　　所以，在通常情况下都会使用延迟的方式来抓取关联的对象。因为每个立即抓取都会导致关联对象的立即实例化，太多的立即抓取关联会导致大量的对象被实例化，从而占用过多的内存资源。

　　抓取策略的选取

　　对于抓取策略的选取将影响到抓取关联对象的方式，也就是抓取关联对象时所执行的SQL语句。这就要根据实际的业务需求、数据的数量以及数据库的结构来进行选择了。

　　在这里需要注意的是，通常情况下都会在执行查询的时候针对每个查询来指定对其合适的抓取策略。指定抓取策略的方法如下所示：

　　User user = (User) session.createCriteria(User.class)

　　.setFetchMode("permissions", FetchMode.JOIN)

　　.add( Restrictions.idEq(userId) )

　　.uniqueResult();

五、 查询性能提升小结

　　在文介绍了查询性能提升的方法，关键是如何通过优化SQL语句来提升系统的查询性能。查询方法和抓取策略的影响也是通过执行查询方式和SQL语句的多少来改变系统的性能的。这些都属于开发人员所应该掌握的基本技能，避免由于开发不当而导致系统性能的低下。

　　在性能调整中，除了前面介绍的执行SQL语句的因素外，对于缓存的使用也会影响系统的性能。通常来说，缓存的使用会增加系统查询的性能，而降低系统增加、修改和删除操作的性能(因为要进行缓存的同步处理)。所以，开发人员应该能够正确地使用有效的缓存来提高数据查询的性能，而要避免滥用缓存而导致的系统性能变低。在采用缓存的时候也应该注意调整自己的检索策略和查询方法，这三者配合起来才可以达到最优的性能。

　　另外，事务的使用策略也会影响到系统的性能。选取正确的事务隔离级别以及使用。