SQL语句优化分享——索引讲解.pptVIP

SQL语句优化分享 ——索引;主要内容;问题1;问题2;问题3;问题4;问题5;问题;主要内容;表的物理存储结构;每页的开始部分是 96 字节的页首，用作SQL中的管理字节，管理字节中记录着本页的编号、上一页和下一页的编号等（还有其他信息）。 页首下面逐笔存放记录的数据区，如下图：;看看刚刚例子中的数据表DepartmentA内部存储结构信息：;看看页面1897的内部存储结构信息：;计算方法： 表大小=页的数量*8K 页的数量=总行数/每页可存放的行数 取整 每页可存放的行数=8096/(行长度+2) 取整 行长度=各列长度之和+2 + (总列数/ 8 进位取整 ) +4 如果存在VarChar型的可变长度列，行长度需再加上：2+2*可变长度列数 如果存在可变长度列，每行的长度可能不一样。;索引的存储结构和表类似，也是存储在页上。只是表中存储的字段多，而索引中存的字段非常少而已。所以索引一般比表小很多。 计算方法（叶级索引）： 叶级索引的大小=页的数量*8K 页的数量=总行数/每页可存放的行数 取整 每页可存放的行数=8096/(行长度+2) 取整 行长度=各列长度之和+2 + (总列数/ 8 进位取整 ) +1+8 如果存在VarChar型的可变长度列，行长度需再加上：2+2*可变长度列数 如果存在可变长度列，每行的长度可能不一样。 ;1、sp_spaceused 表名 功能：查看一个表共有多少行、共有多少页、共占用多少K字节，及数据和索引的分别占用。 用此存储过程检查一下，你人工计算的表大小和实际的表大小一样吗？;2、sp_tablespace 功能：功能： sp_spaceused的强化版，返回一个数据库所有表的空间占用情况。 目前存放在\\172.16.100.51\培训视频资料\数据库相关资料\SQL SERVER性能优化，可以移植到任何服务器以方便使用。;主要内容;聚集索引——示意图;前面有提到：DBCC IND命令用于查询一个存储对象的内部存储结构信息;前面例子中的DepartmentA没有加索引，里面的数据会不会首尾相连？;聚集索引——堆的存储结构;同一级页之间是通过上一页、下一页相连的，那不同级别间是如何定义关系的？;聚集索引——存储结构;聚集索引——排序;聚集索引查找记录的流程：先到索引页里找到键值为XXX的那条记录，然后 再到数据页里把实际数??读出来。;聚集索引——聚集索引查找;这里可以通过查看申请了什么锁来推测内部处理方式：;聚集索引——聚集索引查找;聚集索引——聚集索引扫描;聚集索引——聚集索引扫描;聚集索引——复合聚集索引;为什么是根据建立索引的第一个字段来查找？;聚集索引——复合聚集索引;主要内容;非聚集索引示意图：;非聚集索引——存储结构;非聚集索引是怎麽找记录的？;由于Department8表只有500笔记录,数据量少的话SQLSERVER会直接走全表扫描,看不出效果，所以这里50万笔记录：;非聚集索引——索引查找、索引扫描;为什么第2、3、5查询开销较大？;非聚集索引——索引查找、索引扫描;因为DepartmentID不在非聚集索引里，所以只能全表扫描。表扫描是直接扫描堆中的数据页（IndexID都是0，代表堆）。;索引查找跟聚集索引查找的原理基本上是一样的。（只不过聚集索引查找找到记录所在范围的时候还需要到数据页里找实际的记录，因为聚集索引页的每一行记录只记录了聚集索引字段在数据页的范围）。;非聚集索引——索引查找、索引扫描;非聚集索引——索引查找、索引扫描;为什麽非聚集索引扫描没有到堆中扫描而在索引页里扫描？;既然在索引页里扫描和在数据页里扫描大家都是漫无目的地去扫描，那么到堆中的数据页里去扫描不是更好？？因为堆中的数据页包含了记录的所有字段，而索引页只包含了创建非聚集索引时所包含的字段。;覆盖索引只能建立在非聚集索引上，创建覆盖索引如下：;既然覆盖索引只是在索引页加上一个字段，那么倒不如创建非聚集索引的时候,不如ModifiedDate列也纳入到非聚集索引中？;可以将非键列包含在非聚集索引中，以避免超过当前索引大小的限制（最大键列数为 16，最大索引键大小为 900 字节）。数据库引擎计算索引键列数或索引键大小时，不考虑非键列。 ;避免添加不必要的列。添加过多的索引列（键列或非键列）会对性能产生下列影响： ? 一页上能容纳的索引行将更少。这样会使 I/O 增加并降低缓存效率。 ? 需要更多的磁盘空间来存储索引。特别是，将 varchar(max)、nvarchar(max)、varbinary(max) 或 xml 数据类型添加为非键索引列会显著增加磁盘空间要求。这是因为列值被复制到了索引叶级别。因此，它们既驻留在索引中，也驻留在基表中。 ? 索引维护可能会增加对基础表或索引视图执