如何创建索引

9/1/2015来源:SQL技巧人气:3922

如何创建索引

  通过前面二篇文章我们基本学会了如何结合执行计划分析出某Sql语句的性能问题,既然有性能问题的地方已经找到了,接下来就是如何优化查询来提高查询性能,而优化查询最重要的武器就是创建索引,这篇文章就来总结如何创建索引来提高查询速度,主要从以下几个方面来总结。

  1. 了解索引
  2. 创建索引的语法
  3. 结合实例演示如何创建索引
了解索引

  1,索引是什么?

    在Sql Server中,索引是一种增强式的存在,这表示即使没有索引,Sql Server的功能并不会受到影响。索引是对数据库表中一列或多列的值进行排序的一种结构(B树),使用索引可快速访问数据库表中的特定信息。

  2,为什么要使用索引?

    就是提高查询性能。

  3,有哪些索引?

    主要分为聚集索引(CLUSTERED INDEX)和非聚集索引(NONCLUSTERED INDEX)两种,聚集索引指的是一列或多列的物理顺序和逻辑顺序是一致的,一个数据库表只能有一个聚集索引,我们通常将主键(一般为自增int型)设为聚集索引。而非聚集索引则可以有多个,而且非聚集索引并不会改变数据库表的物理结构。

创建索引的语法

  1,创建索引,sql语法如下。  

-- 建立索引IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('Sales.Orders') AND name='idx_nc_orderdate')    CREATE NONCLUSTERED INDEX idx_nc_orderdate ON Sales.Orders(orderdate);GO

  2,删除索引,sql语法如下。

-- 删除索引IF EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('Sales.Orders') AND name='idx_nc_orderdate')    DROP INDEX idx_nc_orderdate ON Sales.Orders;GO
结合实例演示如何创建索引

  表结构如下:

  现在的需求是,查询2006年7月1日到2007年7月31日的所有订单,并按requireddate字段排序,查询sql代码如下:

USE TSQLFundamentals2008;GO-- 查询2006年7月1日到2007年7月31日的所有订单,并按requireddate字段排序SELECT orderid,orderdate,requireddate,shippeddate,shipname,shipaddress FROM Sales.OrdersWHERE orderdate>='20060701' AND orderdate<'20070801'ORDER BY requireddate;

  注意,这个表只有主键orderid的聚集索引外,没有建立任何索引。执行查询查看执行计划,如下图。

  从上面的执行计划可以看出,在排序和聚集索引查找方面比较耗时。首先看排序,因为是byrequireddate排序的,所以对requireddate字段要建立单个字段索引,代码如下。

IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('Sales.Orders') AND name='idx_nc_requireddate')    CREATE NONCLUSTERED INDEX idx_nc_requireddate ON Sales.Orders(requireddate);GO

  然后因为where条件中是按orderdate字段来限定范围的,所以对orderdate字段要建立单个字段索引,代码如下。

IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('Sales.Orders') AND name='idx_nc_orderdate')    CREATE NONCLUSTERED INDEX idx_nc_orderdate ON Sales.Orders(orderdate);GO

  因为要查询的字段为orderid,orderdate,requireddate,shippeddate,shipname,shipaddress,所以我们可以针对这几个字段建立一个组合字段索引,代码如下。

IF NOT EXISTS (SELECT * FROM sysindexes WHERE id=OBJECT_ID('Sales.Orders') AND name='idx_orders_orderid_orderdate_shipaddress')    CREATE NONCLUSTERED INDEX idx_orders_orderid_orderdate_shipaddress ON Sales.Orders(orderid,orderdate,requireddate,shippeddate,shipname,shipaddress);GO

  最后,我们再重新运行一次查询,查看执行计划,如下图。

  从执行计划中可以看到,后面那个已经变成了非聚集索引查询了,说明我们创建的索引起作用了。具体是创建单个字段索引,组合字段索引还是两个都要创建,这个要在实际的项目中反复试验。

参考资料

1,CareySon的T-SQL查询进阶--理解SQL Server中索引的概念,原理以及其他

2,田园里的蟋蟀的程序员眼中的 SQL Server-执行计划教会我如何创建索引?