SQL索引调优

一&＃xff1a;前言

Index对数据库性能有着举足轻重的作用。Index设计的优劣直接影响到DB执行的效率。所以在做DB Tuning时&＃xff0c;一部分会从Index着手处理&＃xff0c;SQL Server也提供了很好的工具Database Engine Tuning Advisor&＃xff0c;会给出一些建Index和优化方面的建议。

二&＃xff1a;Index概述

这方面在各个博客论坛上面已经讲的比较多了&＃xff0c;在此大致总结一下&＃xff1a;

1. 数据表的基本结构

当建立一个新表时&＃xff0c;系统将在磁盘中分配一段以8K为单位的连续空间&＃xff1b;当第一个8K用完的时候&＃xff0c;SQL Server指针会自动分配8K的空间。每个8K空间成为一个数据页&＃xff08;Page&＃xff09;&＃xff0c;又称页面或者数据页面&＃xff0c;并分配0-7的页号&＃xff0c;每个文件的第0页记录引导信息&＃xff0c;叫文件头&＃xff08;File Header&＃xff09;&＃xff1b;每8个数据页&＃xff08;64K&＃xff09;的组合形成扩展区&＃xff08;Extent&＃xff09;&＃xff0c;成为扩展。全部的数据页的组合形成堆&＃xff08;Heap&＃xff09;。

2. 索引的基本概念

建立索引的目的就是提高数据检索效率&＃xff0c;改善数据库工作性能&＃xff0c;提高数据访问速度。系统表sysindexes存储Index的重要信息。以B-Tree为存储结构。

3. 数据表扫描与索引的使用

没有索引时&＃xff0c;访问表的数据时按照Table Scan&＃xff0c;平均效率比较低。

建立索引时&＃xff0c;访问表的数据时按照Index Scan/Seek&＃xff0c;平均效率很高。

4. 聚集索引和非聚集索引(Clustered Index and Non Clustered Index)

相同点&＃xff1a;

• 以B-Tree为存储结构存放的一组数据页
• 不同阶的节点包含指向另一个阶的数据页
• 子节点包含所有的键值
• 在sysindexes中可以找到索引的大小和内容分布
• 都会提高数据查询的效率

不同点&＃xff1a;

• 叶子节点存放什么&＃xff1a;聚集索引存放实际的数据页&＃xff1b;非聚集索引存放指针

注意&＃xff1a;子叶层级存放的内容不一样。

5. 覆盖索引(Covering Index)

索引覆盖是指建索引的字段正好是覆盖查询条件中所涉及的字段,这里需要注意的是,必须是从第一个开始覆盖。

6. 死锁(DackLock)

请参照

http://www.cnblogs.com/changbluesky/archive/2010/06/10/1753021.html

三&＃xff1a;性能简述(Performance)

1. Index碎片

1.1 查询碎片

sys.dm_db_index_physical_stats可以用来检测特定索引、表或索引视图的所有索引、数据库中所有索引或所有数据库中所有索引中的碎片。

重要栏位:

1.2. 重建索引与重组索引(rebuild and reorganize)

无论何时对基础数据执行插入、更新或删除操作&＃xff0c;SQL Server 数据库引擎都会自动维护索引。随着时间的推移&＃xff0c;这些修改可能会导致索引中的信息分散在数据库中&＃xff08;含有碎片&＃xff09;。当索引包含的页中的逻辑排序&＃xff08;基于键值&＃xff09;与数据文件中的物理排序不匹配时&＃xff0c;就存在碎片。碎片非常多的索引可能会降低查询性能&＃xff0c;导致应用程序响应缓慢。通过重新组织索引或重新生成索引来修复索引碎片&＃xff0c;提高性能。

• ALTER INDEX IX_IndexName ON dbo.TableName REBUILD WITH (ONLINE&＃61;ON)
• ALTER INDEX IX_IndexName ON dbo.TableName REORGANIZE

两种方法的区别&＃xff1a;

• 重新组织索引是通过对叶页进行物理重新排序&＃xff0c;使其与叶节点的逻辑顺序&＃xff08;从左到右&＃xff09;相匹配&＃xff0c;从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。
• 重新生成索引将删除该索引并创建一个新索引。此过程中将删除碎片&＃xff0c;通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间&＃xff0c;并在连续页中对索引行重新排序&＃xff08;根据需要分配新页&＃xff09;。这样可以减少获取所请求数据所需的页读取数&＃xff0c;从而提高磁盘性能。

建议根据碎片程度&＃xff0c;使用修复碎片的最佳方法&＃xff1a;

2. 选择正确而的Index

2.1 主要的考量

以范围查询

常常需要排序的数据

2.2 次要考量

栏位长度要短

• 会影响所有的非聚集索引
• 非聚集索引的子也曾都包含所有聚集索引的键值

数据的类型

3.建立索引的方针

所有SQL语法的优先性

优先建立多个查询语法可以共通使用的索引

建立符合索引时&＃xff0c;最佳的栏位顺序

四&＃xff1a;总结

与书中的索引一样&＃xff0c;数据库中的索引使您可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键&＃xff0c;以及映射到指定数据的存储位置的指针。通过创建设计良好的索引以支持查询&＃xff0c;可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性&＃xff0c;从而确保表数据的数据完整性。

设计良好的索引可以减少磁盘 I/O 操作&＃xff0c;并且消耗的系统资源也较少&＃xff0c;查询优化器也能够很好的利用索引&＃xff0c;提高查询性能。