海量数据的优化

1 合理使用索引

      ●在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。 

●在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。

●在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。

●如果待排序的列有多个，可以在这些列上建立复合索引。

●另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。

2  避免困难的正规表达式

　　MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _”

    where charindex('df',colName) > 0 与 where colName LIKE "%df" 结果相同 

　　即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT ＊ FROM customer WHERE zipcode >“98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。

　　另外，还要避免非开始的子串。例如语句：SELECT ＊ FROM customer WHERE zipcode[2，3]>“80”，在where子句中采用了非开始子串，因而这个语句也不会使用索引。

3 使用临时表加速查询,把表的一个子集进行排序并创建临时表，有时能加速查询。

  在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

  注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。

  select colName1,colName2,……

  into temp_tablename

  from table1,table2……

  说明：SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
        语法：

         (1)  SELECT column_name(s) INTO newtable [IN externaldatabase]FROM source

        IN子句可用于向另一个数据库中拷贝表：SELECT Persons.* INTO Persons IN 'Backup.mdb'FROM Persons

        externaldatabase表示另一个数据库文件的全路径。此方法的前提是access数据库。

        (2) 对于sqlserver数据库

            select * into 目的库名..目的表 from 原始表

            eg:select * into temp_db..student_back from student

4 用排序来取代非顺序存取,顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。

5 字段提取要按照“需多少、提多少”的原则，order by按聚集索引列排序效率最高，一个sqlserver数据表只能建立一个聚集索引，一般默认为ID，也可以改为其它的字段。如果一列经常被分组排序，则应该在此列上建立聚集或非聚集索引；返回某范围内的数据，列中有小数目的不同值，主键，外键，可以建立聚集索引；有大数目的不同值，频繁更新修改，主码，外键列可建立非聚集索引。

6 如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。无论是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。

7 用聚合索引比用不是聚合索引的主键速度快；在小数据量情况下，用聚合索引比用一般的主键作order by时速度快，但在大数据量情况下，二者的速度差别并不明显；

8 SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。

    尽量少用NOT；

    通配符%在字符串的开通使得索引无法使用。

    or 会引起全表扫描，、IN 的作用相当与OR；

    非操作符、函数引起的不满足SARG形式的语句，不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等，另外还有函数。

    Count(*)与Count(主键)的速度相当，比其他字段的汇总要快。Count(字段)中，字段越长，汇总速度越慢。

9 Top关键字：select top col_Num colName1,colName2  from tableName

  eg:从publish 表中取出第 n 条到第 m 条的记录：
     SELECT TOP m-n+1 *
      FROM publish
     WHERE (id NOT IN
　　　　        (SELECT TOP n-1 id
　　　　          FROM publish))

     id 为publish 表的关键字 (思想不错，但是由于含有NOT IN使得速度比较慢)

     小数据量和海量数据的通用分页方案：  

     select top 页大小 *

     from tableName

     where col_selected >

            (select max(col_selected) from

                    (  select top ((页码-1)*页大小) col_selected from tableName

                       order by col_selected )

             as another_Name

            )    

     order by col_selected(最好是将排序和分页都在聚集索引上进行，会大大提高程序效率)

10 如果使用了IN或者OR等时发现查询没有走索引，使用显示申明指定索引：Select * FROM PersonMember(INDEX=IX_Title) Where processid IN ('男'，'女');

11 如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌Insert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串(尺寸是它的两倍)，服务器受到字符后又将他转换成二进制值

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

13 查看数据库中是否存在某个表：select * from dbname.dbo.sysobjects where name like 'temptable'

14 创建存储过程：

  

   CREATE PROCEDURE dbo.proc_Name 
          @parameter1 para1_type, //可为参数设定默认值：eg：@name varchar(200) = 'chen'
          @parameter2 para2_type,

          @output_para3 para3_type OUTPUT  //用作输出
   AS 
   SELECT colName1,colName2,colName3
   FROM tableName1 别名1 INNER JOIN tableName2 别名2
      ON 别名1.colName = 别名2.colName

   WHERE  col_Name1 = @parameter1
      AND col_Name2 = @parameter2
   GO

   注：OUTPUT 变量必须在创建表和使用该变量时都进行定义。

   执行存储过程：declare @output_para para_type//用于接受单个返回值

                 execute proc_Name 'para1','para2',@output_para3 = @output_para OUTPUT

   在查询分析器中可以利用print命令在屏幕上显示结果，convert（varchar(10),@output_para）用于类型转化，

15 游标使用例子:

declare student_cursor3 CURSOR FOR
select sno,sname,sdept
from student;

declare @sno int
declare @sname varchar(200)
declare @sdept varchar(200)

OPEN student_cursor3

fetch next from student_cursor3 into @sno,@sname,@sdept
WHILE @@fetch_status = 0
BEGIN
IF @@fetch_status = -2
CONTINUE
print 'result is:' + convert(varchar(10),@sno) + @sname + @sdept;
fetch next from student_cursor3 into @sno,@sname,@sdept
END
close student_cursor3;
DEALLOCATE student_cursor3;