SQL 索引调优

转载 2012年03月26日 17:41:58

一:前言

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

 

二:Index概述

这方面在各个博客论坛上面已经讲的比较多了,在此大致总结一下:

1. 数据表的基本结构

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

 

2. 索引的基本概念

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

 

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

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

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

image

 

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

相同点:

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

不同点:

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

image 

注意:子叶层级存放的内容不一样。

 

5. 覆盖索引(Covering Index)

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

 

6. 死锁(DackLock)

请参照

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

 

三:性能简述(Performance)

1. Index碎片

1.1 查询碎片

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

捕获2

重要栏位:

avg_fragmentation_in_percent 逻辑碎片(索引中的无序页)的百分比
fragment_count 索引中的碎片(物理上连续的叶页)数量
avg_fragment_size_in_pages 索引中一个碎片的平均页数

 

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

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

  • ALTER INDEX IX_IndexName ON dbo.TableName REBUILD WITH (ONLINE=ON)
  • ALTER INDEX IX_IndexName ON dbo.TableName REORGANIZE

 

两种方法的区别:

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

 

建议根据碎片程度,使用修复碎片的最佳方法:

image

 

2. 选择正确而的Index

2.1 主要的考量

以范围查询

常常需要排序的数据

2.2 次要考量

栏位长度要短

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

数据的类型

 

3.建立索引的方针

所有SQL语法的优先性

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

建立符合索引时,最佳的栏位顺序

 

四:总结

 

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

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


SQL调优简介及调优方式

在日常工作或交流中,经常会讨论一些关于sql调优的问题,然后总结了下,下面我们主要是从软件方面进行分析,希望对你有帮助:         引导语:我曾有一种感觉,不管何种调优方式,索引是最根本的方法,...
  • u011463470
  • u011463470
  • 2016年03月30日 17:02
  • 3057

SQL 优化之索引

SQL索引一步到位(此文章为“数据库性能优化二:数据库表优化”附属文章之一)    SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将...
  • zyh_1988
  • zyh_1988
  • 2016年07月13日 16:04
  • 383

MySQL 资料分享

基础篇:   《MySQL索引与SQL调优》讲师:玄惭   (微博:玄惭)  本课程将从Mysql的底层存MySQL索引与SQL调优储开始,到上层应用实现来介绍SQL调优: (1...
  • hpuedu1909
  • hpuedu1909
  • 2015年12月25日 09:56
  • 205

sqlserver性能优化之索引的使用和优化

sqlserver性能优化之索引的使用和优化
  • huwei2003
  • huwei2003
  • 2017年03月29日 17:27
  • 1014

Sql Server查询性能优化之索引篇【推荐】

Sql Server查询性能优化之索引篇 索引基础知识 聚集索引和非聚集索引 索引碎片 索引设计指南...
  • huwei2003
  • huwei2003
  • 2017年03月31日 11:28
  • 1956

什么情况下应该建立索引 索引优化及分析

表的主关键字 自动建立唯一索引 如zl_yhjbqk(用户基本情况)中的hbs_bh(户标识编号) 表的字段唯一约束 ORACLE利用索引来保证数据的完整性 如lc_hj(流程环节)中的...
  • L12345678999
  • L12345678999
  • 2014年05月10日 18:15
  • 1785

mysql中对sql的调优方法

mysql sql 调优方法总结
  • honglicu123
  • honglicu123
  • 2017年03月20日 22:55
  • 234

Oracle SQL 自动调优

Oracle SQL 自动调优Oracle 11版本之后,Oracle支持自动调优,默认开启自动调优任务,每天执行一次,可以查看生成的调优建议。本文的SQL语句基于 11.2.0.1.0版本,不保证在...
  • wwlhz
  • wwlhz
  • 2017年04月14日 13:19
  • 539

关于oracle sql 调优学习的一点总结

大概算是从13年8月份的项目开始接触oracle 的sql优化,当时如小白一般,只是道听途说过类似使用like之类的语句效率很差。由于工作压力大,每天逼迫着自己去学习,去读一条条sql的执行计划,也不...
  • killvoon
  • killvoon
  • 2015年03月20日 13:54
  • 747

数据库调优教程(十二) 优化sql语句

上一章讲了如何设计一张好的表,一张好的表自然需要好的sql语句去操作它。本章就来聊聊如何优化sql语句。...
  • hzy38324
  • hzy38324
  • 2015年04月11日 09:35
  • 5740
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL 索引调优
举报原因:
原因补充:

(最多只允许输入30个字)