第6章创建与优化索引.ppt

问题引入 已知一个大的商店在美国一共有上百家的分店，并有 1000 万个顾客。该商店创建了一张 Customers表，其中的列名分别为 name, address, city , age , hobby等。需要通过下面的 Select 语句给 Boston 地区的喜爱运动的顾客投递运动设备标签： select name,address , city, state from customers where city = ‘ Boston ’ and age between 18 and 50 and hobby in ( racket sports, jogging, hiking ， bodybuilding ，outdoor sports ) 问题引入 相关条件如下： （1）每个数据库页面大小为2kb，每条顾客记录需要100字节； （2）Boston地区的顾客占了整个美国的1000万顾客的1/100; （3）每次I/O操作需要1/100秒。 现有如下问题需要计算： 问题引入 问题1.在堆结构下需要访问多少页面的数据？ 假设一次读取一个页面，则需要访问多少页面，需要多少访问时间？ 500 000个页面 ， 5000秒 ，500000 / 100=5000（秒）=1小时23分钟20秒 问题2 假设在custid和city上建立主键，即聚焦索引，则需要访问多少个页面？需要多少分钟？ 5000页 一次IO操作需要1/100秒，5000/100=50秒 问题引入 问题3.假设内存缓冲区每次有5个页面可以替换，city为聚焦索引，需要多少时间？ Boston的数据页数/100（占总数据的百分之一）/20（每页20行）=5000页 需要的时间：5000/5*0.01=10秒 问题5.是否应该在在custid和city上建立主键，即聚焦索引，在hobby和age上建立非聚焦索引？这样做的好处和坏处各是什么？如何平衡？ 适合使用堆的情况 表中包含频繁添加、删除和更新行的易变数据的表 包含少量数据的表。 主要包含重复数据行的表。 包含写入但极少读取的数据的表。 使用聚集索引的场合 当查询使用between,,=和=之类的运算符返回一系列值时。 返回使用order by 或group by 子句排序的数据。 返回使用join子句合并的数据。 返回大型结果集 使用聚集索引的注意事项 创建聚集索引的列不能是数据频繁更改的列 索引键不要使用宽键。 使用非聚集索引的场合 当用户要以多种方式搜索数据时，非聚集索引很有用。 表的更新频率较低。 明显知道查询不会返回大型结果集。 查询中需要对多个不同值的列进行索引 使用非聚集索引的注意事项 在非聚集索引之前创建聚集索引 SQL Server 在以下情况下自动重新构建现有的非聚集索引： 1.删除了现有的聚集索引 2.创建了聚集索引 3.更改了定义聚集索引的列。 如果表中的大部分数据频繁更新，则应避免大量在此表上使用非聚集索引 创建索引的基本语法 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...,n ] )INCLUDE ( column [ ,...,n ] )[WITH option [ ,...,n ] ][ON {partition_scheme (column) | filegroup | default } ] 创建唯一索引的场合 应该只在强制实体完整性的列上创建唯一索引。 主键和唯一索引之间的区别： 1.主键一定是唯一索引，但唯一索引不一定就是主键 2.一个表中可以有多个唯一索引，但只能有一个主键 3.主键列不允许有空值，但唯一索引列允许空值。 问题引入： 学校内部办公网站提供教师信息查询功能，通过两种查询方式: 1.快速查询：通过教师姓名这一单一信息进行查询。 2.高级查询：可以通过专业，职称，研究方向等组合信息进行高级查询。 提问：如何创建索引，使得两种方式查询都能有较高的效率。 创建复合索引的场合 两列或更多列查询的频率较高 Select查询子句中仅引用了索引中的列。 创建复合索引的注意事项 首先定义唯一性最高的列 使用复合索引提高查询性能并能减少在表上创建的索引数。减少了索引维护。 具有包含性列的索引 包含性列 带包含性列的索引 提供查询范围，提高查询性能 例： Document(title nvarchar(50) ,REVISION NCHAR(5) ,Filena