索引与自定义函数.ppt

1,引入索引机制,是为了提高对数据库的查询效率。,索引、创建与管理方法,6.1 索引,2,INSERT INTO ASD VALUES (7,F,79,22) INSERT INTO ASD VALUES (2,B,89,21) INSERT INTO ASD VALUES (5,E,94,20) INSERT INTO ASD VALUES (10,D,76,22) INSERT INTO ASD VALUES (6,A,64,20) INSERT INTO ASD VALUES (4,C,88,21) INSERT INTO ASD VALUES (1,H,91,22) INSERT INTO ASD VALUES (8,G,68,19),Create table ASD( NO int not null, NAME nchar(4) not null, SCORE numeric(4,1) null, AGE int not null) go,先建立一个名为“ASD”的表，并插入记录：,在未建立主键约束,并未建立索引时,记录显示的顺序是物理顺序,3,1索引的概念 (1) 创建索引，可以避免全表扫描，从而提高查询速度. (2) 索引是数据库对象,分别用CREATE命令建立,用DROP命令删除,用ALTER命令修改. (3) 索引与与表（或视图）关联，并按表中指定的列值排列顺序的映象表。建立索引后表的存储由两部分组成:一是用来存放表的数据页面; 二是用来存放索引的索引页面。索引就存放在索引页面上。 (4) SQL Server读取数据的过程 首先确定是否存在索引，然后查询优化器（负责生成查询的优化执行计划的组件）从表扫描和使用索引这两种方法中，确定对于数据访问哪种方式更为有效。 (5) 数据检索方式:先搜索索引页面，从中找到所需数据的指针，再直接通过指针从数据页面中读取数据。 （6）应注意的问题: 不应该在每一个列上都创建索引,以免降低系统速度。 插入、删除或更新索引列比非索引列要花更长的时间。,4,索引顺序 1 A 2 B 3 C 4 D 5 E 6 F 7 G 8 H,CREATE INDEX ASD_NAME_IND ON ASD(NAME,ASD),索引页面,数据页面,行定位器,已按表中NAME字段建立索引的表的存储。,5,索引键值顺序,被引用的数据行存放顺序,行定位器,索引页面,数据页面,6,2索引的存储结构 (1) 簇索引(Clusteredlndex) 对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上。 因此一个表只能有一个簇索引。簇索引查找数据很快。 （2) 适于使用簇索引的情况 经常用于排序从表中检索的数据的列。 经常顺序访问的列。 每个索引行包含一个键值和 一个指针，分别指向数据行。 (3）表中按顺序排列的序列（如拼音顺序、日期和数字顺序）在查询的时候不需要逐行查找，因此查询速度快。 (4) 以下数据类型的列不可用作聚集索引的键： ntext、text、varchar(max)、nvarchar(max)、 varbinary(max)、 xml或image,7,CREATE CLUSTERED INDEX ASD_CLU_NO ON ASD (NO ASC),建立簇索引后记录重新按指定列值排列了顺序,且则行 定位器中存储的是簇索引的索引键：,索引页,行定位器,数据页,8,2) 非簇索引(Nonclusteredlndex)。 非簇索引将行定位器按关键字的值，用一定的方式排序。则行定位器存储的是指向数据行的指针。 非簇索引检索效率较 低。 一个表最多可以建248个非簇索引。 索引列中的数据频繁更改时应建立非簇索引。,9,索引顺序 1 A 2 B 3 C 4 D 5 E 6 F 7 G 8 H,CREATE INDEX ASD_NAME_IND ON ASD(NAME,ASD),索引页面,数据页面,行定位器,在非簇索引中，行定位器存储的是指向数据行的指针。数据没有按索引键重新排列顺序。,10,1 2 3 4 5 6 7,4 7 1 3 2 5 6,索引键值顺序,被引用的数据行存放顺序,行定位器,11,CREATE CLUSTERED INDEX Asd_clu_nocf2 ON ASD (NO ASC),建立了聚集索引 后,记录按索引顺 序重新物理地排 列了记录的顺序. 一个表只能有一 种物理排列方式, 所以一个表只能 建一个聚集索引.,12,3索引键的组成 根据索引键的组成，可以将索引分为三种类型。 1) 唯一索引 创建唯一索引，可确保表内的索引列中不包含重复的数据内容。 2) 复合索引 在一个表中，通过连接或附接两个或多个列值而创建的索引。 3) 覆盖索引 当索引中包含了需要的所有信息时，这个索引称为覆盖索引。它的键值包含了满足查询条件的所有数据。,13,612 创建索引 1. 创建索引时，要注意几点: (1) 当在表中创建主关键字约束或唯一性约束时，SQL Server自动创建一个唯一性索引。 (2) 如果表中已有数据，那么在创建索引时，SQL Server会检查数据的合法性。当有不合法数据时，创建索引将失败。 (3) 当有多个列作为关键字时，应创建复合索引，即索引包含有两个或多个列。 (4) 基于相同列但列次序不同的复合索引也是不同的。,14,简单的建立索引命令,CREATE INDEX 索引类型 ON (列名 排序方式，n) 索引类型有: CLUSTERED -簇索引 NONCLUSTERED -非簇索引 UNIQUE -唯一索引,15,2. 创建索引的完整格式： CREATE 索引类型 INDEX ON (列名1ASC I DESC ,n ) WITH ON 其中: 索引类型有: UNIQUE、CLUSTERED 、 NONCLUSGTERED 索引选项为以下属性的组合： PAD INDEX FILLFACTOR=填充因子 IGNORE_DUP_KEY DROP_EXISTING STATISTICS_NORECOMPUTE SORT_IN_TEMPDB 其中，,16,1) UNIQUE 创建唯一索引。创建唯一索引后，如果执行INSERT或UPDATE操作后会导致有重复的索引值出现时，该INSERT或UPDATE操作会失败。 2) CLUSTERED 指明创建的索引为簇索引。默认为创建的索引为非簇索引。 3) NONCLUSTERED 创建的索引为非簇索引。,17,4) PAD_INDEX 指定填充索引的内部节点的行数，至少应大于等于两行。 PAD_INDEX选项只有在 FILLFACTOR选项指定后才起作用，因为PAD_INDEX使用与FILLFACTOR相同的百分比。 默认时SQLServer确保每个索引页至少有能容纳一条最大索引行数据的空闲空间。如果FILLFACTOR指定的百分比不够容纳一行数据，SQLServer会自动内部更改百分比。,18,5) FILLFACTOR；填充因子 它指定创建索引时每个索引页的数据占索引页大小的百分比。fillfactor的值为1到100， 它其实同时指出了索引页保留的自由空间占索引页大小 的百分比，即100-fillfactor。,19,6) IGNORE DUP KEY 此选项控制了包含一个唯一约束的列中插入重复数据时SQLServer所作的反应。 当选择此选项时，SOL Server返回一个错误信息，跳过此行数据的插入继续执行下面的插入数据的操作。 当没选择此选项时，SQLServer不仅会返回一个错误信息，还会回滚整个INSERT语句。 7) DROP EXISTING 指定要删除同名索引并重新创建。,20,例 在图书表中为出版社创建索引。 CREATE INDEX idx_press ON book (press) 例 创建出版社和作者的复合索引。使用DROP EXISTING是因为前面例子已经创建了索引idx_press，所以先删除同名的索引，然后再创建新索引。 CREATE INDEX idx_press ON book (press，author) WITH DROP_EXISTING,21,例6-3创建唯一非簇索引。 CREATE UNIQUE INDEX idx_press ON book ( book_id) WITH DROP_EXISTING 例6-4使用填充因子创建唯一非簇索引。 CREATE UNIQUE INDEX idx_press ON book ( book_id) WITH PAD_INDEX, FILLFACTOR=50, DROP EXISTING,22,613 删除索引 1. 索引删除的语法： DROP lNDEX 表名.索引名 , n 2.几点说明： (1)DROP lNDEX命令不能删除由CREATE TABLE或ALTER TABLE命令创建的PRIMARY KEY或UNIQUE约束索引。 (2) 不能删除系统表中的索引。 (3)在删除簇索引时，表中的所有非簇索引都将被重建。,23,614 索引优化 关于创建索引的建议如下。 (1)将更新尽可能多的行的查询写入单个语句内，而不要使用多个查询更新相同的行 (2)使用索引优化分析查询并获得索引建议。 (3)对聚集索引使用整型键，另外，在唯一列、非空列或标识列上创建聚集索引可以获得较佳的性能。 (4)在查询经常用到的所有列上创建非聚集索引。 (5)检查列的唯一性。,24,72 自定义函数,25,7.2.1 自定义函数的基本概念 1.自定义函数:是用户为实现代码封装和重用,将一组T-SQL语句按一定格式定义得到,并有输入参数,运行后有返回值。 2.自定义函数的优点： (1) 允许模块化程序设计。一次创建可多次调用。 (2) 执行速度更快(首次优化编译，此后直接运行)。 (3) 减少网络流量。 3.自定义函数与存储过程的比较,26,722 自定义函数的三种类型 1标量函数 (1) 函数返回单个数值(返回值的类型在RETURNS子句中指定的)。 (2)函数返回单个数值，返回由BEGINEND块指定的多个值。 返回的值不可为text、ntext、image、cursor和timestamp数据类型。,27,2表值函数 (1)内嵌表值函数 返回由选择的结果构成的记录集表。它可以替代视图,且比视图的逻辑功能更加强大。 (2) 多语句表值函数 返回由选择的结果构成的记录集表,函数需要由BEGINEND限定函数体。,28,多语句表值函数的主体中只允许使用的语句： 赋值语句。 除错误捕获语句 TRYCATCH外的流程控制语句。 定义局部数据变量和局部游标的DECLARE语句。 SELECT语句，其中的选择列表包含为局部变量分配值的表达式。 游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以INTO子句向局部变量赋值的FETCH语句；不允许使用将数据返回到客户端的FETCH语句。 修改table局部变量的INSERT、UPDATE和DELETE语句。 调用扩展存储过程的EXECUTE语句。,29,723 创建与使用用户自定义函数 CREATE FUNCTION ( 参数名 数据类型 =默认值 , ) RETURNS 返回值数据类型 WITH 选项 AS BEGIN SQL语句 RETURN 返回表达式 END 其中：选项有二： （1）ENCRYPTION可以实现用户自定义函数的加密； （2）SCHEMA BINDING将自定义函数绑定到它所引用的数据库对象。,30,例7-17自定义标量函数，实现对图书价格的高与低的评价。 USE 图书馆 If EXISTS(SELECT name FROM sysobjects WHERE name=fc_price AND type=FN) DROP FUNCTION fc_price GO CREATE FUNCTION fc_price(priceinput money) RETURNS nvarchar(10) BEGIN DECLARE returnstr nvarchar(10) If priceinput40 SET returnstr=较贵图书 ELSE SET returnstr=便宜图书 RETURN returnstr END 使用该函数。 SELECT top 20 book_name,price,dbo.fc_price(price)FROM book,31,例7-18自定义内嵌表值函数fc_press，根据指定的出版社参数查询该出版社出版的图书，返回结果记录集。 USE library GO CREATE FUNCTION fc_press(press varchar(30) RETURNS table AS RETURN (SELECT bookname，author，press，price FROM book WHERE press = press ) GO 下面的语句说明了如何使用新建立的内嵌表值函数来获取清华大学出版社出版的图书信息。 select * From fc_press(清华大学出版社),32,Create function XY_fc(专业 nvarchar(8) Returns table AS Return select * from 班级 WHERE 专业=专业 GO select * FROM XY_fc(软件工程),33,34,例7-19自定义多语句表值函数 fc_press_1，根据指定的出版社参数查询该出版社出版的图书，返回结果记录集。请注意与定义内嵌表值函数fc_press 对比。 USE library GO CREATE FUNCTION fc_press_1 (press nvarchar(30) RETURNS tb_press table (book_name nvarChar(30) NOT NULL， author nvarchar(20) NULL， press nvarChar(30) NULL， plice money NULL ) AS BEGIN INSERT tb_Press SELECT book_name,author,press,price FROM book WHERE press=press RETURN END GO 下面这两个语句分别调用多语句表值函数fcjoress_l宋查询清华大学出版社和科学出版社出版的图书。 SELECT*from dbofc press l(清华大学出版社) GO SELECT*from dbofc_press_l(科学出版社) GO,35,724 自定义函数的管理 1查看自定义函数 EXEC sp_help 如要查看fcrice的信息的具体语句为： EXEC sp_help fc price 利用这个语句，可以看到函数的名称及相关参数。但要看到函数的具体定义，需要用系统存储过程sphelptext。其语法如下： E