SQL查询优化--索引的使用与管理

1．执行计划

执行计划的作用：执行计划可以是根据SQL Server查询优化器估算的计划,不需要查询语句真正执行,减少数据库负荷

使用执行计划的方式：不实际执行,只估算出执行计划、实际执行SQL语句后,根据实际情况总结出执行计划

执行计划的常用图标；

案例需求: 通过两种执行计划查看在数据库adventureWorks上的查询语句: Select * From PRODUCTION.WORKORDER Where PRODUCTID =732 的执行情况。 实施方法一:事先查看执行计划

实施方法二:执行查询后显示执行计划

2．客户端统计

客户端统计信息功能获取查询后的实际花销信息,与查询计划一起配合使用分析查询效率更加方便有效

实施方法:

选择客户统计信息按钮

输入SQL语句

选择执行计划的全局效果图

查看显示的执行计划

查看执行计划图

查看节点内容

3.使用索引的查询语句的写法

查询语句基本写法:

Select [字段列表] From table1 Where 条件表达式1 {and|or} 条件表达式2 {and|or} …条件表达式n;

查询语句使用索引的最基本要求就是要以索引字段为查询条件

条件表达式的样式为 {[索引字段] 条件运算符 [值]}

使用查询语句的注意事项:

查找条件中不要将字段进行运算

LIKE模糊查询字段的通配符是第一个字符，查询不会使用索引

查询条件使用not或者!=操作符时，查询不会使用索引

使用组合索引，查询条件必须包含组合索引里第一个索引字段

如果查询结果字段与查询条件的字段总是成对出现，建议使用索引表查询

查找条件中不要将字段进行运算

案例需求:对比下列语句 ,分析索引使用情况:

1、Select * From PRODUCTION.WORKORDER Where ProductID + 10=742

2、Select * From PRODUCTION.WORKORDER Where ProductID=732

查询没有使用索引的情况

查询使用索引的情况

查询条件使用not或者!=操作符时

案例需求:查看

Select * From PRODUCTION.WORKORDER Where ProductID!=732 的索引使用情况

使用组合索引的效果

案例需求:数据表PRODUCTION.WORKORDER建立两个组合索引：

索引1:

IX_WorkOrder_ProductIDStartDateEndDate

包含三个字段(ProductID，StartDate，EndDate)

索引2:

IX_Workorder_StartdateEnddate包含字段

两个字段(StartDate，EndDate)

查询结果字段与查询条件字段成对出现

案例需求:分析查询语句

Select ProductID From PRODUCTION.WORKORDER

Where startdate >cast(‘2001-07-12’ As datetime) And startdate <

cast(‘2001-07-14’ As datetime) 使用以下哪组索引?

索引1:

IX_WorkOrder_ProductIDStartDateEndDate包含字段

(ProductID,StartDate,EndDate)

索引2 :

IX_Workorder_StartdateEnddate包含字段(StartDate,EndDate)

关于管理索引

1.禁用与重建索引

索引建立后一般不需要维护，数据库能够自动维护信息，但是在某些情况下还是需要手动维护。例如：每月一次的入库操作，其他时间都是查询操作。在批量更新的时候就不合适使用索引，平时查询适合使用索引，这个时候就需要人工维护索引，在批量更新时禁用索引，当批量更新后，重新建立索引。 禁用索引还有以下规则：

1.如果索引是创建主键产生或者具备唯一性约束，那么禁用索引也会禁用这些约束。

2.如果索引是聚集索引，禁用索引相当于将表禁用，用户不能够再操作数据表。

3.查询与维护语句执行时不会使用禁用索引，但是如果用查询提示使用己禁用的索引，查询语句会操作失败。

4.不能创建与已禁用索引同名的索引，这个是对禁用索引与删除索引的区别。

案例需求:禁用PRODUCTION.WORKORDER表上的聚集索引，访问表时会提示不允许访问。

实施步骤:

选择聚集索引

禁用索引

尝试打开WorkOrder表,查看效果

重建索引

重新打开WokrOrder表,查看效果

2.整理索引数据碎片

再数据使用了一段时间后,必然会产生碎片.那么产生碎片后就到知道使用索引的效率降低.进而导致查询的效率降低.为了应对索引碎片,一般比较好的办法就是重新建立或重新整理碎片来组织索引.重新填满数据块.并除去数据碎片.

注意事项:如果碎片很小，则不需要重新生成或重新组织碎片，如果碎片小于30%，则可以选择重新组织索引，如果碎片大于30%，则可以重新生成索引。

查看索引碎片

重新组织索引

重新生成索引

重新组织索引

3.维护索引统计信息

索引统计信息的作用：随着列中数据的更改，索引和列的统计信息会自动更新保持一致，如果统计信息停用，可能导致查询优化器参考错误的统计信息，从而使用错误的执行计划。 可以举一个例子：在一个包含1000行数据的表上创建索引，索引列中包含的数据都是唯一值，查询优化器把该索引列视为搜集查询数据的最好方法。如果更新活动频繁发生，使得索引列中的数据有很多重复值，则该列对于查询不再是理想的候选列。

手动更新索引的统计信息

查看统计信息

动更新统计信息

选择更新统计信息复选框完成更新

设置数据库关闭自动更新统计信息操作