数据库访问性能优化.ppt

数据库管理系统,授课教师 吴涛,第10章 数据库访问性能优化,10.1 存储过程10.2 索引,2/83,10.1 存储过程,10.1.1 存储过程概念10.1.2 创建和执行存储过程10.1.3 从存储过程中返回数据10.1.4 查看和维护存储过程10.1.5 一些系统存储过程,3/83,10.1.1 存储过程概念,SQL语言是应用程序和数据库之间的主要编程接口。使用SQL语言编写访问数据库的代码时，可用两种方法存储和执行这些代码。在客户端存储代码，并创建向数据库服务器发送的SQL命令将SQL语句存储在数据库服务器端，然后由应用程序调用执行这些SQL语句。存储在数据库服务器端供客户端调用执行的SQL语句就是存储过程。,4/83,存储过程功能,接受输入参数并以输出参数的形式将多个值返回给调用者。包含执行数据库操作的语句。将查询语句执行结果返回到客户端内存中。,5/83,存储过程优点,允许模块化程序设计只需创建一次并存储在数据库中，就可以在应用程序中反复调用该存储过程改善性能在创建存储过程时对代码进行分析和优化，并在第一次执行时进行语法检查和编译，将编译好的可执行代码存储在内存的一个专门缓冲区中，以后再执行此存储过程时，只需直接执行内存中的可执行代码即可。,6/83,存储过程优点（续）,减少网络流量只需要一条执行存储过程的代码即可实现，因此，不再需要在网络中传送大量的代码。可作为安全机制使用对于即使没有直接执行存储过程中的语句权限的用户，也可以授予他们执行该存储过程的权限。,7/83,10.1.2 创建和执行存储过程,创建存储过程CREATE PROCEDURE 存储过程名 参数名 数据类型=default OUTPUT,n AS SQL语句 n 执行存储过程 EXEC UTE 存储过程名 实参,OUTPUT,n,8/83,示例,例1不带参数的存储过程。查询计算机系学生的考试情况，列出学生的姓名、课程名和考试成绩。CREATE PROCEDURE p_StudentGrade1AS SELECT Sname,Cname,Grade FROM Student s INNER JOIN SC ON s.Sno=SC.Sno INNER JOIN Course cON c.Cno=sc.Cno WHERE Dept=计算机系执行此存储过程：EXEC p_StudentGrade1,9/83,示例,例2带输入参数的存储过程。查询某个指定系学生的考试情况，列出学生的姓名、所在系、课程名和考试成绩。CREATE PROCEDURE p_StudentGrade2 dept char(20)AS SELECT Sname,Dept,Cname,Grade FROM Student s INNER JOIN SC ON s.Sno=SC.Sno INNER JOIN Course c ON c.Cno=SC.Cno WHERE Dept=dept执行存储过程，查询信息管理系学生的修课情况 EXEC p_StudentGrade2 信息管理系,10/83,示例,例3带多个输入参数并有默认值的存储过程：查询某个学生某门课程的考试成绩，课程的默认值为“java”。CREATE PROCEDURE p_StudentGrade3 sname char(10),cname char(20)=JavaAS SELECT Sname,Cname,Grade FROM Student s INNER JOIN SC ON s.Sno=SC.sno INNER JOIN Course c ON c.Cno=SC.Cno WHERE sname=sname AND cname=cname,11/83,参数传递方式,按参数位置传值实参的排列顺序与创建存储过程时参数定义的顺序一致。EXEC p_StudentGrade3 吴宾,高等数学按参数名传值在EXEC语句中，指明定义存储过程时指定的参数的名字以及参数的值。EXEC p_StudentGrade3 sname=吴宾,cname=高等数学,12/83,如果在定义存储过程时为参数指定了默认值，则在执行存储过程时可以不为有默认值的参数提供值。例如，执行例3的存储过程：EXEC p_StudentGrade3 吴宾相当于执行：EXEC p_StudentGrade3 吴宾,Java,13/83,示例,例4带输出参数的存储过程。统计全体学生人数，并将统计结果用输出参数返回。CREATE PROCEDURE p_Count total int OUTPUTAs SELECT total=COUNT(*)FROM Student 执行此存储过程：DECLARE res intEXEC p_Count res OUTPUTPRINT res,14/83,示例,例5带输入参数和输出参数的存储过程。统计指定课程（课程名）的平均成绩，并将统计结果用输出参数返回。CREATE PROC p_AvgGrade cn char(20),avg_grade int OUTPUTAS SELECT avg_grade=AVG(Grade)FROM SC JOIN Course C ON C.Cno=SC.Cno WHERE Cname=cn,15/83,执行例5存储过程示例,查询Java课程的平均成绩。DECLARE Avg_Grade intEXEC p_AvgGrade java,Avg_Grade OUTPUTPRINT Avg_Grade,16/83,示例,例6统计指定系选修指定课程（课程名）的学生人数和考试平均成绩，并用输出参数返回选课人数和平均成绩。CREATE PROC p_CountAvg dept varchar(20),cn varchar(20),cnt int OUTPUT,avg_grade int OUTPUTAS SELECT cnt=COUNT(*),avg_grade=AVG(Grade)FROM SC JOIN Course C ON C.Cno=SC.Cno JOIN Student S ON S.Sno=SC.Sno WHERE Dept=dept AND Cname=cn,17/83,执行例6存储过程示例,查询计算机系修“高等数学”的学生人数和考试平均成绩。DECLARE Count int,AvgGrade intEXEC p_CountAvg 计算机系,高等数学,Count OUTPUT,AvgGrade OUTPUTSELECT Count AS 人数,AvgGrade AS 平均成绩,18/83,修改数据示例,例7将指定课程（课程号）的学分增加指定的分数。CREATE PROC p_UpdateCredit cno varchar(10),inc intAS UPDATE Course SET Credit=Credit+inc WHERE Cno=cno,19/83,删除数据示例,例8删除指定课程（课程名）中考试成绩不及格学生的此门课程的修课记录。CREATE PROC p_DeleteSC cn varchar(20)AS DELETE FROM SC WHERE Grade 60 AND Cno IN(SELECT Cno FROM Course WHERE Cname=cn),20/83,插入数据示例,例7.在课程表中插入一行数据，其各列数据均通过输入参数获得。CREATE PROC p_InsertCourse cno char(6),cname nvarchar(20),x tinyint,y tinyintAS INSERT INTO Course VALUES(cno,cname,x,y),21/83,SQL Server提供4种存储过程返回数据的形式 输出参数，既可以返回数据（整型值或字符值等），也可以返回游标变量（游标是可以逐行检索的结果集，具体内容请参见本书第9章）。返回代码，只能是整型值。SELECT 语句的结果集，这些语句包含在该存储过程内或该存储过程所调用的任何其它存储过程内。可从存储过程外引用的全局游标。,10.1.3 从存储过程中返回数据,22/83,例10建立查询指定系的学生姓名和性别的存储过程，如果用户未指定系名，则返回代码1；如果用户指定的系名不存在，则返回代码2。CREATE PROC p_Student dept varchar(20)=NULLAS IF dept IS NULL RETURN 1-未指定系名 IF NOT EXISTS(SELECT*FROM Student WHERE Dept=dept)RETURN 2-指定的系名无效 SELECT Sname,Sex FROM Student WHERE Dept=dept调用该存储过程。DECLARE ret intEXEC ret=p_Student IF ret=1 PRINT 必须指定一个系名IF ret=2 PRINT 指定的系名不存在,示例,23/83,例11根据各种错误设置不同的返回代码值。查询指定课程（课程名）的考试平均成绩，根据每种可能的错误赋予的返回代码。CREATE PROCEDURE p_GetAvgGrade cname varchar(30)=NULL,avg_grade int OUTPUT AS IF cname IS NULL-验证cname参数的有效性 RETURN 1 IF(SELECT COUNT(*)FROM Course WHERE Cname=cname)=0 RETURN 2 SELECT avg_grade=AVG(Grade)FROM SC JOIN Course c ON SC.Cno=c.Cno WHERE Cname=cname IF avg_grade IS NULL-检查该门课程是否有考试 RETURN 3 ELSERETURN 0-成功!,示例,24/83,DECLARE ret int,avg int,tip varchar(40)EXEC ret=p_GetAvgGrade avg_grade=avg outputSET tip=CASE ret WHEN 1 THEN 提示1:必须指定一个课程名！WHEN 2 THEN 提示2:指定的课程名不存在！WHEN 3 THEN 提示3:指定的课程还没有考试！ENDPRINT tip,调用例11,25/83,10.1.4 查看和维护存储过程,查看存储过程修改存储过程删除存储过程,26/83,1.查看存储过程,在SSMS的“对象资源管理器”中，展开要查看存储过程的数据库，然后依次展开该数据库下的“可编程性”“存储过程”，即可看到该数据库下用户定义的全部存储过程。,27/83,2.修改存储过程,ALTER PROC EDURE 存储过程名 参数名 数据类型=default OUTPUT,.n AS SQL语句.n 与定义存储过程的语句基本一样。,28/83,示例,例12修改student_grade2存储过程，使其能查询指定系考试成绩大于等于80分的学生的修课情况。ALTER PROCEDURE student_grade2 dept char(20)AS SELECT Sname,Dept,Cname,Grade FROM Student s INNER JOIN SC ON s.Sno=SC.Sno INNER JOIN Course c ON c.Cno=SC.Cno WHERE Dept=dept AND Grade=80,29/83,3.删除存储过程,删除存储过程语法DROP PROC|PROCEDURE 存储过程名,n 例13删除p_StudentGrade1存储过程。DROP PROC p_StudentGrade1,30/83,1.sp_columns作用：返回当前环境中指定表或视图的列信息。语法格式：sp_columns table_name=object,table_owner=owner,table_qualifier=qualifier,column_name=column,10.1.5 一些系统存储过程,31/83,例查询Student表中包含的列信息。EXEC sp_columns table_name=Student,示例,32/83,作用：返回当前环境中单个表的主键信息。语法格式：sp_pkeys table_name=name,table_owner=owner,table_qualifier=qualifier,2.sp_pkeys,33/83,例2.查询SC表包含的主键。EXEC sp_pkeys table_name=SC,示例,34/83,作用：返回当前环境的逻辑外键信息。sp_fkeys pktable_name=pktable_name,pktable_owner=pktable_owner,pktable_qualifier=pktable_qualifier,fktable_name=fktable_name,fktable_owner=fktable_owner,fktable_qualifier=fktable_qualifier,3.sp_fkeys,35/83,例3查看引用Student表的外键表和外键列。EXEC sp_fkeys pktable_name=Student,示例,36/83,作用：返回可在当前环境中查询的对象列表，也就是返回任何能够在 FROM 子句中出现的对象。sp_tables table_name=name,table_owner=owner,table_qualifier=qualifier,table_type=type,fUsePattern=fUsePattern,4.sp_tables,37/83,例4在Students数据库中执行下列语句，查看该数据库中的全部可查询对象。EXEC sp_tables,示例,38/83,例5在Students数据库中执行下列语句，查看该数据库中的全部用户表。EXEC sp_tables table_type=TABLE,示例,39/83,例6.在MySimpleDB数据库中执行下述代码，查看Person架构中的可查询对象。EXEC sp_tables table_name=%,table_owner=Production,table_qualifier=MySimpleDB,示例,40/83,微软提供的一个不公开的系统存储过程，用于对数据库中全部表进行某种操作。EXEC RETURN_VALUE=sp_MSforeachtable command1,replacechar,command2,command3,whereand,precommand,postcommand,5.sp_MSforeachtable,41/83,例7.在MySimpleDB数据库执行下列代码，可列出该数据库中全部表的数据行数。create table#rowcount(tablename varchar(128),rowcnt int)EXEC sp_MSforeachtable insert into#rowcount select?,count(*)from?,N?select*from#rowcount order by rowcnt descdrop table#rowcount,示例,42/83,例8.在MySimpleDB数据库执行下列代码，列出数据行数超过1万的表。create table#rowcount(tablename varchar(128),rowcnt int)exec sp_MSforeachtable insert into#rowcount select?,count(*)from?having Count(*)10000,N?select*from#rowcount order by rowcnt descdrop table#rowcount,示例,43/83,10.1.5 使用ADO.NET访问数据库,ADO.NET是.NET框架中用于进行数据库访问的类库。在ADO.NET中包含了多个用于进行数据库处理的类，如Connection类、Command类等，这些类帮助完成连接数据库、获取数据、更新数据、删除数据等操作。确切的说，ADO.NET向程序员公开了数据访问服务的类，程序员通过这些类，可以方便的操作数据库。下面以C#语言为例，介绍如何使用ADO.NET访问数据库。,ADO.NET框架与.NET框架,ADO.NET在.NET中所处的位置，如下图所示。,用Connection类连接数据库,当应用程序需要与数据库连接，并从数据库中获取数据，那就需要通过ADO.NET提供的连接方式，创建应用程序与数据库之间的对话，创建对话后，才可以正常读取数据。这个对话就是通过Connection类创建的连接。Connection类是一个连接类的统称，针对不同的数据库，ADO.NET又提供了不同的子类连接类，如支持SQL Server数据库的连接类是SqlConnection，其继承自Connection类。本节就演示如何通过这个类，创建与数据库的对话。要知道SqlConnection类能干什么，先通过表4.1来学习这个类的一些重要方法和属性。,用Command类执行命令,Command类也是一个命令的基类，其中用于执行SQL Server命令的类是SqlCommand，派生自Command类。SqlCommand对象主要用来执行数据库执行命令，所以其包括很多执行的方法，还有一些与数据库相关的属性。表4.2列出了SqlCommand常用的方法和属性。,用DataReader类读取数据,前述ExecuteReader方法返回的是一个只读数据集。ADO.NET提供了DataReader类用于描述只读数据集，与SQL Server数据库操作相关联的是SqlDataReader类，派生自DataReader类。SqlDataReader类包含的是数据集，可能是多条记录，当然也可能一条记录。要读取每条记录的信息，或者读取每条记录中某个字段的信息，就需要用到SqlDataReader类提供的一些属性和方法，表4.3列出了这些常用的属性和方法。,如何给Command对象添加参数对象（Parameter）,参数就是变量在数据库操作中的一种替代品。如姓名变量在程序中名字为name，那么我们就需要给Command对象添加一个参数对象，用来承载name变量的内容。这样才可以在数据库中操作。针对SQL Server数据库操作的参数是SqlParameter，其派生自Parameter类。一个SqlParameter表示一个参数，默认情况下是input参数，表示是输入的参数，就是从程序中读取的参数，输出参数表示从数据库中读取出来的参数。具体是输入还是输出参数，需要通过SqlParameter的属性Direction属性来表示。参数类型主要有3种：Input、InputOutput和ReturnValue。以下应用程序代码演示了如何创建这3种类型的参数。,使用事务（Transaction）,事务是一个整体操作，其封装了多个操作，一个执行不成功，则其他的所有操作都不成功。在VS2008用于事务处理的类，存在于命名空间中，类名称为SqlTransaction，其继承自Transaction类，表示要在SQL Server数据库中处理的Transact-SQL事务。SqlTransaction的主要属性是Connection，获取与改事务关联的数据库连接。主要方法有：Commit：用来提交数据库事务。RollBack：从挂起状态回滚事务。程序通过在SqlConnection对象上调用BeginTransaction方法，创建一个事务对象。然后通过事务的那两个方法执行或中断事务。为了捕获在提交过程中发生的错误，在事务处理中一般使用try/catch语句。现在再来看一个简单的示例：用户取款操作。,主要内容 索引的概念 聚簇索引与非聚簇索引 索引的创建与管理 索引的维护教学目标 了解索引的概念及优点 掌握索引的分类 掌握创建、修改和删除索引的方法,10.2 索引,10.2.1 索引简介,1.索引的概念数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。,2.创建索引的优、缺点,创建索引的优点通过创建唯一索引，可以保证数据记录的唯一性。可以大大加快数据检索速度。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。创建索引的缺点创建索引要花费时间和占用存储空间。建立索引加快了数据检索速度，却减慢了数据修改速度。,10.2.1 索引简介,考虑建索引的列：主键：通常，检索、存取表是通过主键来进行的。因此，应该考虑在主键上建立索引连接中频繁使用的列：用于连接的列若按顺序存放，系统可以很快地执行连接。如外键，除用于实现参照完整性外，还经常用于进行表的连接在某一范围内频繁搜索的列和按排序顺序频繁检索的列不考虑建索引的列：很少或从来不在查询中引用的列，因为系统很少或从来不根据这个列的值去查找数据行只有两个或很少几个值的列（如性别，只有两个值“男”或“女”），为这样的列创建索引并不能得到建立索引的好处以bit、text、image数据类型定义的列数据行数很少的小表（几千行记录以内）一般也没有必要创建索引,如果在一个列上创建索引，该列就称为索引列。索引列中的值称为关键字值。,3.考虑建索引的列和不考虑建索引的列,在SQL Server中，索引可以分为聚集索引、非聚集索引、惟一性索引、包含列索引、索引视图、全文索引、空间索引、筛选索引、XML索引等多种类型。这里仅介绍常用的聚集索引、非聚集索引以及惟一性索引。1聚集索引聚集索引是对聚集索引列进行排序，进而实现了对记录进行排序。也就是说，在聚集索引中，不但索引是有序的，而且索引中的数据也是有序的。通常设置于经常在Group By、Order By 子句中出现的列/列组上。2非聚集索引在非聚集索引中，每个索引并不是包含行记录的数据，而是数据行的一个指针。也就是说，在非聚集索引中，索引是顺序排序和存储，但是索引指向的数据行并不一定是按顺序存储的，这取决于创建非聚集索引时所用的方法。创建非聚集索引的方法大致分为基于聚集索引和基于堆。其中，基于聚集索引创建非聚集索引时，数据行是有序的；而基于堆创建非聚集索引时，数据行是无序的。非聚集索引通常设置于经常在Where子句中出现的列/列组上。,55,10.2.2 索引的类型,3惟一性索引一个索引的唯一性体现在该索引能够唯一地确定表的一个记录。因为唯一性索引不允许索引键中包含重复的值，因此在具有唯一性索引的表或视图中，每一个记录在某种程度上都是唯一的。在创建PRIMARY KEY约束时，SQL Server自动创建了一个聚集类型的唯一性索引。,56,10.2.2 索引的类型,SQL Server 的索引架构,每个数据表只能有1个聚集索引每个数据表最多有249个非聚集索引每个索引最多包含16个列，不能超过900字节,聚集索引和非聚集索引,Root,Non-Leaf,Leaf,BalancedTree,8K Bytes,8K Bytes,讨论：以下数据库应怎样设置索引,59/131,CREATE TABLE Student(Sno CHAR(7)PRIMARY KEY,Sname NCHAR(5)NOT NULL,SID CHAR(18)UNIQUE,Ssex NCHAR(1)DEFAULT 男,Sage TINYINT,Sdept NVARCHAR(20),CREATE TABLE Course(Cno CHAR(6)PRIMARY KEY,Cname NVARCHAR(20)NOT NULL,Credit NUMERIC(3,1)CHECK(Credit0),Semester TINYINT),CREATE TABLE SC(Sno CHAR(7)NOT NULL,Cno CHAR(6)NOT NULL,Grade TINYINT,PRIMARY KEY(Sno,Cno),FOREIGN KEY(Sno)REFERENCES Student(Sno),FOREIGN KEY(Cno)REFERENCES Course(Cno),10.2.3 索引的创建与管理,1.使用T-SQL语句创建索引,CREATE UNIQUE CLUSTERED|NONCLUSTEREDINDEX 索引名 ON 表名|视图名(列名ASC|DESC,.n)WITH PAD_INDEX,FILLFACTOR=填充因子,DROP_EXISTING,其中：UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行 CLUSTERED：用于指定创建的索引为聚集索引NONCLUSTERED：用于指定创建的索引为非聚集索引PAD_INDEX：用于指定索引中间级中每个页（节点）上空间填充百分比FILLFACTOR=填充因子：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，填充因子的值为1到100DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引,SQL Server中关于创建索引的事实和指导原则：当在某列创建 PRIMARY KEY 约束或 UNIQUE 约束的时候，SQL Server 自动为此列创建索引必须是表的拥有者才能执行 CREATE INDEX 语句可以在视图上创建索引 SQL Server 在 sysindexes 系统表内存储索引信息在列上创建索引之前查看列上是否已经创建索引了在比较小的列上定义索引以使索引也比较小。小的索引更有效率选择惟一性好的列创建索引，使每个键值标识较少的行创建聚集索引的时候，所有现存的非聚集索引都会被重建,【例2】在demo数据库的student_course表上创建一个名student_course_index的非聚簇复合索引，索引关键字为student_id,course_id，升序，填充因子50%USE demoCREATE NONCLUSTERED INDEX student_course_indexON student_course(student_id ASC,course_id ASC)WITHFILLFACTOR=50,【例1】在demo数据库的BOOK表上创建一个名为book_id_index的唯一性聚簇索引，索引关键字为book_id，升序，填充因子50%USE demoCREATE UNIQUE CLUSTERED INDEX book_id_indexON book(book_id ASC)WITHFILLFACTOR=50,索引创建示例,2.使用T-SQL语句管理索引,使用T-SQL语句查看索引在创建索引之前或在创建索引之后，可以用sp_helpindex或sp_help系统存储过程查看表的索引。【例3】用系统存储过程sp_helpindex查看表book的索引信息。EXEC sp_helpindex book 使用T-SQL语句对索引更名在创建索引之后，可以用sp_ rename系统存储过程重新命名表的索引。【例4】用系统存储过程sp_rename将表book的索引book_id_index 重新命名为book_id_index1。sp_rename book.book_id_index,book.book_id_index1注意：要重命名的索引要以“表名.索引名”的形式给出。,3.使用T-SQL语句删除索引,创建索引之后，如果该索引不再需要，可以用DROP语句将其删除。DROP语句的语法如下：DROP INDEX table.index,.n【例5】用DROP语句将表book的索引“book_id_index1”删除。DROP INDEX book.book_id_index1注意：被删除的索引要以“表名.索引名”的形式给出。删除索引时要注意，如果索引是在CREATE TABLE语句中创建的，只能用ALTER TABLE语句删除索引。如果索引是CREATE INDEX创建的，可用DROP INDEX删除。,一般地，对查询语句，查询处理器创建了可以提高性能的执行规划。然而，如果对某一个特定的查询语句例如检索、插入、删除、修改，查询处理器没有创建最好的执行规划，那么用户可以在查询语句中增加优化隐藏来影响查询处理器创建出最优的执行规划。优化隐藏就是指在执行查询语句、使用多表连接检索或者指定查询语句操作的对象表时，明确地指出应该使用的查询方法、连接算法或者对表的操作方式。,65/131,