SQL语句调优

在数据量非常大的情况下查询语句的优化就显得非常重要了，有时候代码上一点细微的差别就可能导致查询时间成倍的增加。一般的调优从建表和查询本身的优化两个方面来进行。

一、建表

从数据库的设计开始就应该考虑到查询效率的问题，所以从建表方面，应该从考虑到以下几个方面：

1、主键列的长度应该尽量的小，能用smallInt就不用Int。

2、对于身份证号码等长度固定的字符型字段，不应使用varchar或者nvarchar，而应该使用定长的char或者nchar类型，因为定长字段的查询速度要快于可变字段。此外，英文字符多的时候尽量用char或者varchar，因为Unicode编码一个字符占用两个字节，会浪费存储空间。

3、尽量不要在表中出现null值，因为数据库在存放和查询包含null的数据表时会花费额外的运算时间。此外，应当尽量避免在where语句中对null值进行判断，null值得判断将会导致数据库放弃索引而采用全表扫描，降低效率。对于存在null值得列可以设置其默认值以从而提高其效率。

如：

select * from products where unitprice is null;

可将unitprice的默认值设为0.并使用下列查询来提高效率：

select * from products where unitprice=0;

4、如果一个表中有很多列，可将其垂直拆分成几个不同表并通过同名的主键将其连接起来，从而避免存取数据时加载过多的数据而导致查询效率下降。

5、关于索引的建立

    首先看一下关于索引的基本知识：

       （可参考http://www.programmerinterview.com/index.php/database-sql/what-is-an-index/）

创建索引的主要方法有两种,一种是在创建表时定义主键约束（PRIMARY_KEY）或者唯一约束（unique），这时候系统会自动建立起一个唯一索引；另一种是直接使用CREATE INDEX语句，其基本语法为

CREATE [UNIQUED][CLUSTERED|NONCLUSTERED]

INDEX  index_name ON table( column_name [ASC|DESC] , ……)

其中，UNIQUED代表唯一索引，保证了索引键中不包含重复的值，从而使表中的每一行具有唯一性；CLUSTERED聚集索引，表中每一行的物理顺序与索引顺序相同，一个表中只能有一个聚集索引，一个聚集索引可以包含多个列，聚集索引的检索效率比较高但是对增删改的效率影响比较大；NONCLUSTERED非聚集索引是为了改善经常使用的，没有建立聚集索引的查询的性能，一个表中可以有多个非聚集索引，一个聚集索引中包含索引键值和指向表数据存储位置的行定位器。

    索引的作用在于，通过索引可以显著的提高数据库查询的性能，而且还可以减少返回值查询结果集时读取的数据量。除此之外，索引还可以强制表中的行具有唯一性，从而确保表中数据的完整性。

    接下来看建表时关于索引的调优

1. 1. 对外键列尽量使用索引。因为外键是为了保证数据的一致性和完整性而添加的，但是从理论上来讲，外键的添加反而会使查询的效率降低，所以即使很少使用的外键也应该被添加索引从而使查询效率提高。
   2. 对于经常被查询或排序的列应当建立索引，这是因为建立索引之后本身就已经将索引进行过排序了，所以利用索引的查询效率会很高。
   3. 对于不经常使用的列不应该创建索引，因为索引本身是要占用硬盘空间的；对于内容重复性高的列（如性别）不应该创建索引。
   4. 一张表中不应该建立过多的索引列，因为索引会对增删改产生影响，所以过多的索引会影响整个系统的性能，得不偿失。
   5. 对于数据很少的数据表不必刻意去创建索引。
   6. 若在查询的时候符合条件的数据很多，那么非聚集索引搜索的性能可能还不如全表扫描。

二、查询

1、使用LIKE和%模糊查询时，只有以常量字符开头才会使用到索引，通配符(%)开头不会使用索引。

    如：

        select * from orders where customerid like 'D%'
        select * from orders where customerid like '%D'

    两句中第一句能使用索引，第二句就不能使用索引。

2、查询时多表连接时会非常的耗费资源，为了提高查询速度，可以适当的浪费存储空间，在多个表中重复存储相同的数据，以避免在数据检索时进行多表连接等操作。

3、避免在where子句中使用函数，因为如果使用了函数，那么在查询的时候，就无法使用索引，有多少条记录就需要调用多少次函数，这是非常耗费性能的。

    如下面的两个例子中，第一句使用函数的的需要进行全表扫描而第二句的则可以使用索引：

        select * from orders where year(orderdate)=2008 and month(orderdate)=7;  /*函数，全表扫描*/
        select * from orders where orderdate between '2008-7-1' and '2008-7-31';

        select * from orders where left(customerid,1)='d';  /*函数，全表扫描*/
        select *from orders where customerid like 'd%';

4、应该尽量避免在where子句中使用参数或者变量，也应该避免在where子句中对列进行表达式操作。这是因为sql语句在运行之前就必须要确定查询优化策略，所以上述行为都将导致全表扫描。（尽量不要使用的运算符包括NOT、NOT IN、NOT LIKE、NOT EXISTS、!=、<>、!>、!<）

如：

        select * from products where categoryid=@categoryid;  /*全表扫描*/
        select * from products with(index(products_categoryid)) where categoryid=@categoryid;  /*强制使用索引查询*/  /*假定PC是P表中C的索引*/

        select * from products where unitprice/2=5;  /*全表扫描*/
        select * from products where unitprice=5*2;  /*列值尽量不要进行运算*/

5、在AND运算中，只要有一个条件能够使用索引就可以大幅提升查询速度；但是在OR运算中，需要所有条件都能使用索引才能提升查询速度。

select * from order where customerid='bergs' or freight=60;  /*无法可以使用customerid上的索引，仍然需要全表查询*/

      select * from order where customerid='bergs'union all select * from order where freight=60；   /*此时第一个查询能使用索引，第二个查询仍然需要扫描全表，但是这样也仍能明显提高查询效率*/

6、尽量不要使用子查询特别是相关子查询，能使用join的尽量使用join。

例如，将orders表中的全部数据检索出来并且自动添加一个编号，直接使用排名函数row_number的效率就会很高，而如果使用相关子查询那么时间复杂度为O(n2)，随着数据的增加效率会越来越低。

        select orderrid,row_number()over (order by orderid) as 'rowno' from orders;
        select orderid,(select count(*) from orders as a where a.orderid<=b.orderid) as 'rowno' from orders as b order by rowno;

参考资料：

    祝锡永．数据库：原理技术与应用[M]．北京：机械工业出版社，2011.6，142-147,269-271．