SQL 优化--索引

「爱情、让人受尽委屈。」 2022-06-18 00:16 194阅读 0赞

Sys.dm_exec_requests是检查SQL Server性能瓶颈的有力工具
    
    update statistics t_goods with fullscan---更新表的统计信息
    聚簇索引的索引叶子就是数据本身。
    非聚簇索引的索引叶子节点包含的是指向数据的书签（即数据行号或聚集索引的key）
     
    
    
    索引优化原则：
    主要是减少逻辑读的次数。逻辑读中包含从内存数据缓存中访问的页数和从物理磁盘中读取的页数。
    而物理读表示那些没有驻留在内存缓冲区中需要从磁盘读取的数据页数。
    优化主要是针对 逻辑读的次数。
    
    
    在执行查询时，SQL Server动态选择使用哪个索引。为此，SQL Server根据每个索引上分布在该关键字上的统计量来决定使用哪个索引。值得注意的是，经过日常的数据库活动（如插入、删除和更新表格），SQL Server用到的这些统计量可能已经“过期”了，需要更新。你可以通过执行DBCC SHOWCONTIG来查看统计量的状态。当你认为统计量已经“过期”时，你可以执行该表格的UPDATE STATISTICS命令，这样SQL Server就刷新了关于该索引的信息了。
    
    根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段
    把经常一起出现的字段组合在一起，组成复合索引，复合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。
    
    主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。
    主键的选择还要注意复合主键的字段次序，对于复合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低、单独或者组合查询可能性大的字段放在前面
    
    在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会 被使用，并且应尽可能的让字段顺序与索引顺序相一致。
    
    --//
    .如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。
    
    
    解决方式：
    可以改为强制查询使用索引：
    select id from usertable with(index(索引名)) where code=@code 或 select * from usertable with(index=index_name) wherecode=@code--强制按照索引查找
    
    --//
    
    应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
    
    聚簇索引(clustered indexes)的使用
    
    　　聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。聚簇索引的叶子页面和表的数据页面相同。聚簇索引列的顺序不仅是聚簇索引行的顺序还是数据行的物理顺序。因为表只能有一种存储物理顺序。所以一个表只能有一个聚簇索引。聚簇索引不需要像非聚簇索引那样从索引行跳到基本行。
    
    在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查(between、<、<=、>、> =)或使用group by或order by的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。
    
    在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列(如 IDENTITY 的自增列)上，否则会经常引起封锁冲突。
    在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。
     选择聚簇索引应基于where子句和连接操作的类型。聚簇索引的侯选列是：
    
    　　● 主键列,该列在where子句中使用并且插入是随机的。
    
    　　● 按范围存取的列，如p_order > 100 and p_order < 1000。
    
    　　● 在group by或order by中使用的列。
    
    　　● 不经常修改的列。
    
    　　● 在连接操作中使用的列。
    
    　注：由于聚簇索引上非聚簇索引的依赖性，用单独的 drop index 和 create index 重建聚簇索引将导致所有非聚簇索引被建立两次。可以使用  CREATE CLUSTERED INDEX IX_TB(CODE) WITH( DROP_EXISTING=ON) 通过一步方式来实现。
    　同理也可以在非聚簇索引上使用 CREATE NONCLUSTERED INDEX IX_TB(CODE) WITH(DROP_EXISTING=ON)
    
    CREATE NONCLUSTERED INDEX IX_TB ON TABLE(C1,code)
    with (data_compression=row) ----创建索引时指定 索引压缩(行压缩或页压缩)
    CREATE NONCLUSTERED INDEX IX_TB ON TABLE(C1,code)
    with (data_compression=page) ----创建索引时指定 索引压缩(行压缩或页压缩)
    
    非聚簇索引(nonclustered indexes)的使用
    
         非聚簇索引在其索引行上保存聚簇索引键(表包含聚簇索引)。非聚簇索引都将聚簇索引键值做为其的行定位器。不包含聚簇索引的表叫 堆表。非聚簇索引保存的是一个指针(行定位器)，指针从索引行定位到数据行。
    
    　　SQL Server缺省情况下建立的索引是非聚簇索引，由于非聚簇索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。一个表如果没有聚簇索引时,可有250个非聚簇索引。每个非聚簇索引提供访问数据的不同排序顺序。在建立非聚簇索引时，要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外，还要考虑这些问题：
    
    　　● 索引需要使用多少空间。
    
    　　● 合适的列是否稳定。
    
    　　● 索引键是如何选择的，扫描效果是否更佳。
    
    　　● 是否有许多重复值。
    
    　　对更新频繁的表来说，表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开销。对移到新页的每一行而言，指向该数据的每个非聚簇索引的页级行也必须更新，有时可能还需要索引页的分理。从一个页面删除数据的进程也会有类似的开销，另外，删除进程还必须把数据移到页面上部，以保证数据的连续性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情况:
    
    　　● 某列常用于集合函数(如Sum,....)。
    
    　　● 某列常用于join,order by,group by。
    
    　　● 查寻出的数据不超过表中数据量的20%。
    
    非聚集索引的页级页中包括的指针类型将取决于非聚集索引是构建在堆上还是聚集索引上。、
      建立在堆上的非聚集索引时，指针是由 文件ID 、页ID 和数据所在的页槽号组成的。即（1:85691:10）第一个文件，85691页第10条记录。
      建立在聚集索引上的非聚集索引时，指针值就是数据行的聚集索引键值。即使用聚集键导航聚集索引来检索索引需要的列。 
    
    
    覆盖索引( covering indexes)的使用
    
    　　覆盖索引是在所有满足SQL查询不用到达基本表所需的列上建立的非聚簇索引。即 如果查询遇到一个索引并且完全不需要引用底层基本表，那么该索引就是覆盖索引。
     也可以这样理解：SQL查询中不包含任何从非聚簇索引页面跳到基本表的数据页面的列，也就是说非聚簇索引页面包含了所有要查询的数据，不必再到数据页面中查找数据。 如果非聚簇索引中包含结果数据,那么它的查询速度将快于聚簇索引。
    
    　　但是由于覆盖索引的索引项比较多,要占用比较大的空间。而且update 操 作会引起索引值改变。所以如果潜在的覆盖查询并不常用或不太关键，则覆盖索引的增加反而会降低性能。
    
     也可以这样理解覆盖索引： 非聚集索引页中包含了聚集索引键值和其他列（比如说要查询列）的值。这一查询的速度就比在用外键索引（或索引）查询来的更快。因为它会直接从非聚集索引页中取出这些列值。
     实际上是减少了两个步骤(我个人的理解)：
     1、因为根据索引找到索引页，而该索引页中包含了聚集索引键。然后根据聚集索引键（每一个主键）查找真实的行所在的页面位置。
     2、然后从页面的对应位置中取出药查询的列。
     覆盖索引的案例如：
      create index fugai_index on mytable(ProductID) INCLUDE (COLUMN1,COLUMN2)
     覆盖索引列的值是存放在内存中的,如果覆盖索引中包括过多的列会消耗内存.这是其不足点.
    
     覆盖索引：覆盖索引能够使得语句不需要访问表仅仅访问索引就能够得到所有需要的数据。
      因为聚集索引叶子节点就是数据所以无所谓覆盖与否，所以覆盖索引主要是针对非聚集索引而言。
      覆盖索引要求包含SELECT 子句和where子句做所有的字段。Where语句中的字段在前面，select中的在后面。
    
     
    
    过滤(筛选)索引
    
     例如：  CRATE NONCLUSTERED INDEX IX_WHERE_QTY ON Production.product(qty) where qty is null; --where是必须的。创建过滤索引的标准格式。
    
     为了能够索引具有零散数据分布的字段或稀疏的列，在索引上引用过滤，这样就只搜索一些数据。即 使用过滤器的非聚簇索引，用来在可能没有很好选择性的一个或多个列上创建一个高选择性的关键字组。
     例如：某个列上有很多NULL值。 为了调高查询效率 则可以在该列上创建 索引，使查询形成 覆盖索引。虽然会 执行 扫描操作，但是会减少逻辑读、CPU时间、I/O竞争。这也是一种提高查询性能的方法。
     创建过滤索引的前提条件是 ：
      ANSI_NULLS=ON,ANSI_PADDING=ON,ANSI_WARNINGS=ON,ARITHABORT=ON,CONCAT_NULL_YIELDS_NULL=ON,QUOTED_IDENTIFIER=ON
      NUMERIC_ROUNDABORT=OFF
    
    
     
     由于表上有过度地插入、修改和删除操作，索引页被分成多块就形成了索引碎片，如果索引碎片严重，那扫描索引的时间就会变长，甚至导致索引不可用，因此数据检索操作就慢下来了。
    
    什么是索引碎片
    
    　　有两种类型的索引碎片：内部碎片和外部碎片。
    
    　　内部碎片：为了有效的利用内存，使内存产生更少的碎片，要对内存分页，内存以页为单位来使用，最后一页往往装不满，于是形成了内部碎片。
    
    　　外部碎片：为了共享要分段，在段的换入换出时形成外部碎片，比如5K的段换出后，有一个4k的段进来放到原来5k的地方，于是形成1k的外部碎片。
    
    如何知道是否发生了索引碎片?
     SELECT object_name(dt.object_id) Tablename,si.name as IndexName,
      dt.avg_fragmentation_in_percent AS ExternalFragmentation,
      dt.avg_page_space_used_in_percent AS InternalFragmentation
    　　FROM
    　　( SELECT object_id,index_id,avg_fragmentation_in_percent,
    　　   avg_page_space_used_in_percent
    　　  FROM sys.dm_db_index_physical_stats (db_id(N'Test'),NULL,NULL,NULL,NULL)
    　　   WHERE index_id <> 0) AS dt 
    　　 INNER JOIN sys.indexes si ON si.object_id=dt.object_id
    　　   AND si.index_id=dt.index_id AND dt.avg_fragmentation_in_percent>10
    　　   AND dt.avg_page_space_used_in_percent<75 
    　　   ORDER BY avg_fragmentation_in_percent DESC
    
    ExternalFragmentation的值>10表示对应的索引发生了外部碎片.
    InternalFragmentation的值<75表示对应的索引发生了内部碎片。
    
    如何整理索引碎片?
    
    　　有两种整理索引碎片的方法：
    
    　　1)重组有碎片的索引：执行下面的命令
    
    　　ALTER INDEX ALL ON TableName REORGANIZE
    
    　　2)重建索引：执行下面的命令
    
    　　ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=100,ONLINE=ON)
    
    　　也可以使用索引名代替这里的“ALL”关键字重组或重建单个索引，也可以使用SQL Server管理工作台进行索引碎片的整理。即 选择对应的索引 ,点鼠标右键选择 重新生成或重新组织。DBCC INDEXDEFRAG 和 DBCC DBREINDEX 是清除clustered和nonculstered索引碎片的两个命令。INDEXDEFRAG是一种在线操作（也就是说，它不会阻塞其它表格动作，如查询），而DBREINDEX则在物理上重建索引。在绝大多数情况下，重建索引可以更好的消除碎片，但是这个优点是以阻塞当前发生在该索引所在表格上其它动作为代价换取来得。当出现较大的碎片索引时，INDEXDEFRAG会花上一段比较长的时间，这是因为该命令的运行是基于小的交互块（transactional block）。
    
    什么时候用重组，什么时候用重建呢?
    
    　　当对应索引的外部碎片值介于10-15之间，内部碎片值介于60-75之间时使用重组，其它情况就应该使用重建。
    
    　　值得注意的是重建索引时，索引对应的表会被锁定，但重组不会锁表，因此在生产系统中，对大表重建索引要慎重，因为在大表上创建索引可能会花几个小时，幸运的是，从SQL Server 2005开始，微软提出了一个解决办法，在重建索引时，将ONLINE选项设置为ON，这样可以保证重建索引时表仍然可以正常使用。
    
    　　虽然索引可以提高查询速度，但如果你的数据库是一个事务型数据库，大多数时候都是更新操作，更新数据也就意味着要更新索引，这个时候就要兼顾查询和更新操作了，因为在OLTP数据库表上创建过多的索引会降低整体数据库性能。
    
    　　如果你的数据库是事务型的，平均每个表上不能超过5个索引，如果你的数据库是数据仓库型，平均每个表可以创建10个索引都没问题。
    　　
    
    索引的选择
    
     选择什么样的索引基于用户对数据的查询条件,这些条件体现于where从句和join表达式中。一般来说建立索引的思路是
    
    　　(1)、主键时常作为where子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用它作为连接的时候。
    　　(2)、有大量重复值且经常有范围查询和排序、分组发生的列，或者非常频繁地被访问的列，可考虑建立聚簇索引。
    　　(3)、经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。
    　　(4)、如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。
    　　(5)、在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则可以把fillfactor置为100。
    　　(6)、在选择索引键时，设法选择那些采用小数据类型的列作为键以使每个索
    
    　　引页能够容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为它能够提供比任何数据类型都快的访问速度。
    
     重建索引
    
    　　随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引可以降低分片，维护大块I/O的效率。重建索引实际上是重新组织B-树空间。在下面情况下需要重建索引：
    
    　　(1)、数据和使用模式大幅度变化。
    　　(2)、排序的顺序发生改变。
    　　(3)、要进行大量插入操作或已经完成。
    　　(4)、使用大块I/O的查询的磁盘读次数比预料的要多。
    　　(5)、由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。
    　　(6)、dbcc检查出索引有问题。
    
    　　当重建聚簇索引时,这张表的所有非聚簇索引将被重
    
    索引统计信息的更新
    
    　　当在一个包含数据的表上创建索引的时候，SQL Server会创建分布数据页来存放有关索引的两种统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不动态地重新计算。这意味着，当表的数据改变之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，在下面情况下应该运行update statistics命令：
    
    　　(1)、数据行的插入和删除修改了数据的分布。
    　　(2)、对用truncate table删除数据的表上增加数据行。
    　　(3)、修改索引列的值。
    
    
    使用系统生成的主键 作为聚集索引或需要作为常用查询条件的列作为聚集索引列
    
    小型表不要设置键值(索引键)
    
    使用聚合索引内的时间段，搜索时间会按数据占整个数据表的百分比成比例减少，而无论聚合索引使用了多少个
    
    用聚合索引比用一般的主键作order by时速度快，特别是在小数据量情况下
    
    用聚合索引比用不是聚合索引的主键速度快
    
    把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度
    
    将聚合索引建立在日期列上.
    
    每个表最好不要建超过5个索引。
    
    对 ORDER BY 或 GROUP BY 子句中指定的列进行索引
    
    频繁更改的列 不要建索引，因为这样会增加SQLSERVER的维护工作量和增加磁盘空间。
    
    经常被分组排序的列、外键列、主键列、联接操作所涉及到的列、返回某个范围内的数据列 都应该建立聚集索引或非聚集索引。
    
    当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引。
    
    避免在索引列上使用函数或计算，在where子句中，如果索引是函数的一部分，优化器将不再使用索引而使用全表扫描.例如:
    低效：select * from dept where sal*12 >2500;
    高效：select * from dept where sal>2500/12;
    
    避免在索引列上使用not和 “!=”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到not 和 “!=”时，就会停止使用索引而去执行全表扫描。
    
    索引列上>=代替>
     低效：select * from t_a  where deptno > 3
     高效：select * from t_a where deptno >=4
    两者的区别在于，前者dbms将直接跳到第一个deptno等于4的记录，而后者将首先定位到deptno等于3的记录并且向前扫描到第一个deptno大于3的。
    
    不使用索引的情况：NOT IN、NOT BETWEEN、LIKE（第一个字符非%号除外，如name like '赵%'）、<>、IS NULL/IS NOT、NULL、查询的字段加函数
    
    有大量重复值、且经常有范围查询（between, >,< ，>=,< =）和order by、group by发生的列，考虑建立聚集索引；
    
    经常同时存取多列，且每列都含有重复值可考虑建立组合索引；
    
    索引中不包括一个或几个待排序的列； 
    group by或order by子句中列的次序与索引的次序不一样；