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. 对外键列尽量使用索引。因为外键是为了保证数据的一致性和完整性而添加的,但是从理论上来讲,外键的添加反而会使查询的效率降低,所以即使很少使用的外键也应该被添加索引从而使查询效率提高。
    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.



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值