专业课程-备考NCRE三级-数据库技术

内容纲要

数据库技术

第五章:UML与数据库应用系统

DBAS建模

UML:统一建模语言(Unified Modeling Language)

UML定义有语义(自然语言)和表示法(可视化标准表示符号)两部分组成

UML根据语义定义了四层建模概念框架

  • 元元模型层
  • 元模型层
  • 模型层
  • 用户模型层

视图是对系统模型在某方面的投影

  • 结构视图
  • 实现视图
  • 行为视图
  • 环境视图
  • 用例视图

UML图分两类

  • 结构图
    • 类图
    • 对象图
    • 复合结构图
    • 包图
    • 组件图
    • 部署图
  • 行为图
    • 用例图
    • 交互图(顺序图、通信图、交互概述图、时间图)
    • 状态图
    • 活动图

DBAS业务流程和需求表达

用例图:捕捉需求的工具
活动图:陈述活动与活动之间的流程控制的转移

用例模型

  • 系统:长方框表示
  • 角色:实体
  • 用例:所由动作的集合

用例之间的三种关系;扩展使用组合

DBAS系统内部结构的表达

UML中用类图来描述系统的静态结构,而用顺序图和通信图来表示系统的动态结构

系统结构与类图

类图:展现了一组类、接口和协作以及它们间的关系

一个系统可以有多个类图

类图

  1. 属性
    • +‘表示公有
    • -‘表示私有
    • #‘表示受保护
  2. 操作
  3. 关系
    • 关联关系
      • 多重性:参与对象的数目的上下界限制
      • 关联类:通过虚线连接
      • 聚集:整体和部分的关系(特殊形式的关联)
        • 共享聚集(空心菱形)
        • 组成(实心菱形)
    • 继承关系(空心三角形)
    • 依赖关系
    • 精化关系

类之间的关系:动物 —实现–> 猫 —泛化–> 加菲猫

系统结构和顺序图

顺序图表达了系统的功能需求,主要目的是表达对象与对象之间如何沟通与合作,强调时间

系统结构和通信图

通信图(协作图)显示了交互过程中各对象之间的组织交互关系,强调空间

DBAS系统微观设计的表达

微观设计的焦点是单一对象或事件,通常描述某一特定时间点、某一对象在系统中的现状
UML中对细节方面的内容可用对象图、状态机图及时间图来表达、分析、描述某个特定状况下的系统运作情况

  1. 对象图是根据类图诞生的实例,展现了一组对象及其对象间的关系
  2. 状态机图主要陈述系统中的有关事件或对象的状态转移(商品从采购入库到出售的过程),由事件驱动
  3. 时间图由时间驱动,一般对应至少一张状态图,作为状态图的辅助说明工具

DBAS系统宏观设计

宏观设计的焦点在研究比较大范围的元素之间的联系,如包,命名空间,子系统等
UML包图表达系统中不同包命名空间、不同项目间彼此的关系。
在这里,包及命名空间指的是逻辑层次上的关联性,项目指的是实体层次的关联性。

交互概述图:把不同的交互图结合到一张图表达
复合结构图:适用于进行系统整合,利用于绘制开发系统和外部系统间的关系

DBAS系统实现与部署的表达

组件图:展现了一组组件之间的组织和依赖,用于对源码的可执行发布、物理数据库等的系统建模
部署图:描述系统中硬件和软件的物理配置情况和系统体系结构

第六章:高级数据查询

一般数据查询功能扩展

TOP限制结果集

TOP应与ORDER BY一起使用,不然没有意义(不一定)

TOP n [percent] [WITH TIES]

-- 取前3行
SELECT TOP 3
-- 取前30%行
SELECT TOP 30 percent
-- 包括最后一行取值并列的结果
SELECT TOP 3 WITH TIES

CASE函数

SELECT Sno,Sname,TYPE =
    CASE
        WHEN THEN '好学生'
        WHEN THEN '中等生'
        WHEN THEN '差等生'
        ELSE '留学生'
    END
FROM Student

将查询结果保存到新表

  1. 永久表
  2. 临时表(在tempdb中创建)
    • 全局临时表(##T1 ‘##‘表示临时表)
      • 老师创建后,连接期间内,学生也能访问
    • 局部临时表(#T1 ‘#‘表示临时表)
      • 老师创建后,连接期间内,只能自己访问
SELECT Sno INTO New_Table
FROM Student

查询结果的并、交、差运算

UNION:添加更多行
JION:添加更多列

UNION ALL/DISTINCT
ALL:去除重复值
DISTINCT:去除重复值

-- 查询姓罗和姓王的学生姓名和电话,并按电话结果升序排列
SELECT name,tel
FROM Table_customer
WHERE name LIKE '罗%'
UNION
SELECT name,tel
FROM Table_customer
WHERE name LIKE '王%'
ORDER BY tel ASC

INTERSECT:交集

EXCEPT:差运算

相关子查询

在相关子查询中,内层查询的执行次数由外层查询的结果数决定

基于集合测试(IN)的子查询语句,先执行内层再执行外层
带谓词EXISTS的相关子查询语句,先执行外层再执行内层

WHERE后不能使用聚合函数(SUM、AVG、MAX…)

-- 查询与王居居同一地址的顾客姓名和所在地址
SELECT Cname,Address
FROM Table_customer
WHERE Address IN(
    SELECT Address FROM Table_customer WHERE Cname = '王居居'
)
AND Cname != '王居居'
-- 查询单价大于平均值的商品名称和单价
SELECT name,price
FROM Table_Goods a
WHERE price > (
    SELECT AVG(price) FROM TAble_Goods
)
-- 查询购买了单价大于2000商品顾客的会员卡卡号
SELECt DISTICT id
FROM Table_sale
WHERE EXISTS(
    SELECT *
    FROM Table_saleDetail
    WHERE Table_sale.id = Table_saleDetail.id
    AND price > 2000
)
-- 派生表
SELECT * FROM (SELECT * FROM table) AS temp

其他查询函数

开窗函数:一组行称为一个窗口,开窗函数指用于分区分组计算的函数
结合OVER子句对组内数据进行编号(ROW_NUMBER),并进行求和计算平均值等统计

PARTITION BY:将结果集划分为多个分区
OVER:对组内数据进行编号、统计
排名函数

  • RANK()
  • DENSE_RANK():连续,中间没有间断
  • NTILE
  • ROW_NUMBER

WITH创建公用表达式(临时的结果集)

-- 聚合函数结合OVER子句
-- 查找每门课信息及其该学期开设全部课程的平均学分
SELECT Cno,Cname,Semester,Credit,
    AVG(Credit) OVER(PARTITION BY Semester) AS 'AVG'
FROM Course

-- OVER子句与排名函数
-- 查询每个产品在每个订单的订购数量排名
SELECT OrderID,ProductID,OrderQty,
    RANK() OVER(
        PARTITION BY OrderID
        ORDER BY OrderQty DESC
    ) AS '排名'
FROM OrderDetail
ORDER BY OrderID
-- 创建公用表达式(临时的结果集合)
WITH BuyGoods(CradId,Counts)
AS(
    SELECT CardId,COUNT(*)
    FROM Table_sale
    GROUP BY CardId
)
-- 使用公用表达式
SELECT CardId,Counts
FROM BuyGoods
ORDER BY Counts

第七章:数据库及数据库对象

创建及维护数据库

SQL Server数据库概述

SQL Server将数据库分为两大类:

  • 系统数据库
  • 用户数据库

五个默认系统数据库

  • master
    • 记录SQL Server实例系统级信息,包括元数据(例登陆账户)、系统设置及数据库配置等
  • msdb
    • 保存关于调度报警、作业、操作员等信息
  • model
    • SQL Server创建的所有数据库的模板
  • tempdb
    • 临时数据库,用于保存临时对象或者中间结果集
  • resourse
    • 只读数据库,包括所有SQL Server 系统对象(物理上保存数据库,逻辑上在数据库的sys架构中)

SQL Server数据库的组成

SQL Server将数据库映射成一组操作系统文件,分为:数据文件和日志文件

数据的存储分配单位是数据页(8KB)
一行5KB,20000行需要20000页,存储空间为20000*8KB = 160MB

  1. 数据文件
    • 主要数据文件(.mdf)
      • 一个数据库只能有一个
      • 大小不能小于3MB,必须大于model数据库的大小(model < .mdf < 3MB)
    • 次要数据文件(.ndf)
      • 一个数据库包含0到多
  2. 日志文件
    • 事务日志文件(.ldf)
      • 存放恢复数据库的所有日志信息
      • 至少有一个
  3. 数据库存储空间的分配
    • 创建数据库时,model数据库自动被复制到新建用户数据库主要数据文件中
    • 一行数据不能跨页存储

数据库文件组

分类:主文件组和用户定义文件组

主文件组

  • 系统定义,包含主要数据文件和未分配的数据文件
  • 系统表的所有页分配在此

用户定义文件组

  • 创建文件组以将数据文件组织起来,便于管理和数据分配,提高数据查询效率
  • 日志文件不包括在文件组内,日志空间和数据空间是分开管理的
  • 一个文件不可以是多个文件组的成员
  • 文件组填满后会循环增长
  • 一旦将文件添加到数据库,就不能再将这些文件移到其他文件组

数据库文件的属性

  1. 文件名及其位置
  2. 初始大小
  3. 增长方式
  4. 最大容量

用T-SQL语句创建数据库

  1. 创建数据库

    ON:指定数据文件
    LOG ON:指定日志文件
    PRIMARY:主文件组

  2. 扩大数据库

    TO FILEGROUP:添加到文件组
    ADD FILE:添加新的数据文件
    ADD LOG FILE:添加新的日志文件
    MODIFY FILE:修改指定文件

  3. 收缩数据库

    EMPTYFILE:设定文件为空,系统会清理
    NOTRUNCATE:将释放的文件空间保留在文件中
    TRUNCATEONLY:将未使用的空间都释放给操作系统

CREATE DATABASE RShDB
ON (
    NAME = RShDB_Data,
    FILENAME = 'D:\RShDB_Data\RShDB_Data.mdf',
    SIZE = 10,
    MAXSIZE = 30,
    FILEGROWTH = 5)
LOG ON(
    NAME = RShDB_log,
    FILENAME = 'D:\RShDB_Data\RShDB_Data.ldf',
    SIZE = 3,
    MAXSIZE = 12,
    FILEGROWTH = 2)

分离和附加数据库

分离(sp_detach_db)和附加(FOR ATTACH)可使数据库从一台服务器移动到另一台服务器

分离数据库不删除数据文件和日志文件

架构

也称为模式,使数据库下的一个逻辑命名空间,使数据库对象的一个容器

CREATE SCHEMA:创建模式
DROP SCHEMA:删除模式

分区表

分区表是将表中的数据按水平方式划分成不同的子集,从物理上将一个大表分成多个小表,但逻辑上还是一个大表

  1. 创建分区函数,告诉DBMS以什么方式对表分区

    -- 在int列上创建左侧分区函数,分为四个分区
    CREATE PARTITION FUNCTION myRangePF3(int)
    AS RANGE [LEFT/RIGHT]
    FOR VALUES(1,100,1000)
  2. 创建分区方案,将分区函数生成的分区映射到文件组中

    -- 创建一个将所有分区 映射到同一文件组的分区方案
    CREATE PARTITION FUNCTION myRangePF3(int)
    AS RANGE [LEFT/RIGHT]
    FOR VALUES(1,100,1000)
    GO
    CREATE PARTITION SCHEME myRangePS3
    AS PARTITION myRangePF3
    ALL TO (test1fg);
  3. 使用分区方案创建表

    -- 创建一个将所有分区 映射到同一文件组的分区方案
    CREATE PARTITION FUNCTION myRangePF3(int)
    AS RANGE [LEFT/RIGHT]
    FOR VALUES(1,100,1000)
    GO
    CREATE PARTITION SCHEME myRangePS3
    AS PARTITION myRangePF3
    ALL TO (test1fg,test2fg,test3fg,test4fg)
    GO
    CREATE TABLE PartitionTable(
        col1 int,
        col2 char(10))
    ON myRangePS3(col1)
    GO

索引

创建索引是为了加快查询速度

聚集索引:查找码和数据表的记录的排列顺序一致
非聚集索引:上述否之
稠密索引:每个查找码在索引文件都有相应的记录
稀疏索引:上述否之
主索引:在主码上建立的索引
辅索引:非主属性上建立的索引
唯一索引:索引列不包含重复值
单层索引:索引列的键值按线性排列
多层索引:如B+和B树

UNIQUE:创建唯一索引

CREATE [UNIQUE] [Clustered/`NON`CLUSTERED] INDEX index_name
ON Student(Sno)
-- 在Cname,Cid上创建一个非聚集索引(Cname升序,Cid降序)
CREATE NONCLUSTERD INDEX index_name_id
ON Ctudent(Cname ASC,Cid DESC)
GO

索引视图

建有唯一聚集索引的视图称为索引视图/物化视图

适合建立

  • 很少更新
  • 数据批处理更新,可考虑重建

可以提升下列查询类型

  • 处理大量行的连接和聚合
  • 连接和聚合操作

不适合建立

  • 经常更新
  • 具有大量写操作的OLTP系统
  • 不涉及聚合或连接的查询

第八章:数据库后台编程技术

存储过程

T-SQL两种存储和执行代码的方法

  • 客户端存储代码
  • 子程序形式的程序模块存储

存储过程和子程序类似

  • 有输入、输出参数,并能调用
  • 包含执行数据库操作(调用其他存储过程)
  • 有返回值

存储过程的好处

  • 允许程序模块化设计
  • 改善性能
  • 减少网络流量
  • 增强应用程序的安全性

创建存储过程

OUTPUT:指示参数为输出参数
RECOMPILE:指示重新编译

执行存储过程

EXECUTE | EXEC :执行
PROCEDURE | PROC :存储过程

-- 执行存储过程
EXEC myproc @price = 1000
GO
-- 带输出参数的存储过程:计算两数乘积作为输出参数
CREATE PROCEDURE multi
    @ val1 int,@ val2 int,@val3 int OUTPUT
AS
SET @val3 = @val1 * @val2
GO
DECLARE @ret int
EXEC multi 2,3,@ret OUTPUT
PRINT @ret
GO

用户定义函数

SQL Server 2008支持两类

  • 标量函数:返回单个数据值

    -- 计算立方体体积的标量函数
    CREATE FUNCTION func (@ a int,@ b int,@ c int)
    RETURNS int
    AS
    BEGIN
        RETURN (@ a * @ b * @c)
    END
    -- 调用标量函数
    SELECT func(1,2,3)
  • 表值函数:返回一个表值

    • 内联表值函数:返回查询语句的结果表
    -- 查询指定类别商品的名称和单价的内联表值函数
    CREATE FUNCTION func
        (@ class char(10))
    RETURNS table
    AS
    RETURN(
        SELECT Gname,Gprice
        FROM Goods a
        JOIN GoodsClass b
        ON a.Gid = b.Gid
        WHERE Gclass = @ class
    )
    GO
    -- 调用内联表值函数
    SELECT * FORM func('食物')
    • 多语句表值函数:
    -- 查询指定类别商品的名称、单价、生产日期和种类数的多语句表值函数
    CREATE FUNCTION func
        (@ class varchar(20))
    RETURNS @ func table(
        商品名 varchar(50),
        单价 money,
        生产日期 datetime,
        种类数 int
    )
    AS
    BEGIN
        INSERT INTO @ func
        SELECT(
            ...
        )
        RETURN
    END
    -- 调用多语句表值函数
    SELECT * FORM func('食物')
    -- 删除
    DROP FUNCTION fGoods

触发器

SQL Server支持三类

  • DML触发器(最常用)
    • 针对表或视图的INSERT、UPDATE、DELETE操作
  • DDL触发器
    • 主要对应T-SQL的CREATE、ALTER、DROP
  • 登录触发器
    • 遇见LOGIN ON事件触发

DELETED表:存储DELETE和UPDATE语句影响行的副本
INSERTED表:存储INSERTE和UPDATE语句影响行的副本
UPDATE类似与删除后执行插入,即先执行DELETE,再执行INSERT,产生DELETED和INSERTED表

创建触发器

后触发器

  • FOR
  • AFTER
  • ROOLBACK

前触发器,一张表的每个操作只能定义一个前触发器

  • INSTEAD OF
-- 保证商品表中单价列的值与商品价格变动表中单价列的值一致
-- 维护不同列之间的取值完整性触发器(后触发型))
CREATE TRIGGER tg
    ON Table_PriceHistory FOR INSERT,UPDATE
AS
    DECLARE @ newPrice money
    SELECT @ newPrice = salePrice FROM inserted
    -- 更新老表的价格为新的变动价格
    UPDATE Table_Goods SET salePrice = @newPrice
    WHERE Gid IN(
        SELECT Gid FROM inserted
    )
-- 创建保证销售单据表中使用的会员卡是有效日期内的会员卡的触发器
CREATE TRIGGER tg
    ON Table_saleBill INSTEAD OF INSERT,UPDATE
    -- 在销售单据表修改前,将saleBill表的受影响行替换为有效的会员卡信息
AS
    IF NOT EXISTS(
        SELECT *
        FROM inserted a
        JOIN table_card b
        ON a.id = b.id
        WHERE saleDate NOT BETWEEN StartDate AND EndDate
    )
    INSERT INTO Table_saleBill SELECT * FROM inserted
-- 删除触发器
DROP TRIGGER tg

游标

游标可以不依照集合的方式操作数据,能够逐行处理结果集

游标组成:

  • 游标结果集
  • 游标当前行指针

游标特点:

  • 允许定位结果集的特定行
  • 允许从结果集中当前位置检索一行或多行
  • 支持对结果集中当前行的数据进行修改
  • 不同用户对游标可见性不一样

使用游标

提取数据

  1. 声明游标
    • SCOLL:指定所有提取选项均可用
    • INSENSITIVE:在原表的临时表(tempdb)上操作
    • DECLARE cursor_name [INSENSItIVE] [SCOLL] CURSOR FOR
  2. 打开游标
    • 声明和打开后,默认指向第一行
  3. 提取数据
    • NEXT:返回紧跟着游标的下一行,第一次提取则为第一行,此后都是下一行
    • FIRST:第一行
    • LAST:最后一行
    • ABSOLUTE:绝对的—>1-N行 或 倒数1-N
    • RELATIVE:相对的—>当前行的N行 或 当前行的N行
  4. 关闭游标
  5. 释放游标
-- 定义一个查询'北京市海淀区'姓'王'的顾客姓名和邮箱的游标,并输出游标结果
DECLARE @ name varchar(10),@ email varchar(50)
DECLARE cursor_name CURSOR FOR
    SELECT Cname,Cemail
    FROM Table_customer
    WHERE Cname LIKE '王%'
    AND Caddress LIKE '北京市海淀区'
OPEN cursor_name
--- NEXT第一次提取时候提取当前行,此后提取紧跟的下一行
FETCH NEXT FROM cursor_name INTO @ name ,@ email
--- 0代表FETCH语句成功,-1失败,-2提取行不存在
WHILE @ @ FETCH_STATUS = 0
BEGIN
    --- WHILE 循环体,当提取操作返回0时继续提取
    PRINT'顾客姓名:' + @ name + ',邮箱:' + @ email
    FETCH NEXT FROM cursor_name INTO @ name,@ email
END
CLOSE cursor_name
--- 释放游标
DEALLOCATE cursor_name

第九章:数据库安全性

安全性:保护数据库以防止不合法用户故意造成的数据泄漏、更改或破坏
完整性:保护数据库以防止合法用户无意造成破坏

不安全因素

  1. 非授权用户对数据库的恶意存取和破坏
  2. 数据库中重要或敏感的数据被泄漏
  3. 安全环境的脆弱性

安全标志CC

TCSEC—>C1级别

数据库安全控制

  1. 用户身份鉴别(安全策略方面)
  2. 多层存取控制(安全策略方面)
  3. 审计
  4. 视图
  5. 数据加密

用户身份鉴别

  1. 静态口令
  2. 动态口令
  3. 生物特征
  4. 智能卡识别

存取控制

定义用户权限

合法权限检查机制

SQL语义检查:

  • DAC检查
  • MAC检查
  • 继续语义检查
自主存取控制DAC

授予和回收权限

-- 查询权限
GRANT SELECT
ON TABLE Student
TO User1;
-- 全部操作权限
GRANT ALL PRIVILEGES
ON TABLE Student
TO User1;

-- 将查询权限授予所有用户
GRANT SELECT
ON TABLE Student
TO PUBLIC;
-- 权限转授
GRANT SELECT
ON TABLE Student
TO User1;
WITH GRANT OPTION
--- 回收权限
REVOKE SELECT
ON TABLE Student
FROM PUBLIC;
--- 创建用户
CREATE USER WANG WITH DBA
CREATE USER WANG WITH RESOURCE
CREATE USER WANG WITH CONNECT
强制存取权限MAC

密级:
TS >= S >= C >= P

  1. Top Secret
  2. Secret
  3. Confidential
  4. Public

视图机制

把保密的数据对无权存取的用户隐藏起来,从而一定程度上保护数据

审计功能

把用户对数据库的所有操作自动记录下来放入审计日志中。审计员可以利用审计日志监控数据库的各种行为,重现导致数据库现有状况的一系列事件,找出非法存取数据的人、时间、内容等

AUDIT ON 审计
NOAUDIT ON 不审计

数据加密

明文加密和密文加密

  1. 存储加密
  2. 传输加密

SQL Server的安全控制

身份验证模式

  • Windows身份验证模式
    • 只需登陆Windows即可登陆SQL Server,信息保存在Windows本地
    • SQL Server的安全性和Windows安全性进行了绑定
  • 混合身份验证模式
    1. Windows身份验证,登陆信息保存在Windows本地和SQL Serve服务器
    2. 非Windows的SQL Server授权用户,登陆信息保存在SQL Server实例

登陆账户

  • 创建账户
    CREATE LOGIN user1 WITH PASSWORD = '123456'
  • 修改账户
    ALTER LOGIN user1 WITH NAME = newUser
  • 删除账户
    DROP LOGIN newUser

数据库用户

  • 用户通过账户登陆后只能连接数据库,若没有成为合法用户不能访问数据
  • 映射:登陆账户—>数据库用户 的操作
  • 一个账户啊可以映射为多个数据库用户
  • 管理数据库用户的过程实际上就是建立登陆账户和数据库用户之间的映射关系的过程
  • CREATE USER user1:创建数据库用户user1
  • DROP USER user1:删除数据库用户user1

新建的数据库默认设置

  • 只有一个数据库用户dbo(Database owner)
  • 默认设置为关闭guest访客账户
    • GRANT CONNECT TO guest:开启guest
    • REVOKE CONNECT TO guest:关闭guest
  • 添加为合法数据库用户后默认只有查询权限

权限管理

UPDATE、DELETE、INSERT、SELECT、REFERENCES(获取外表的相关权限)

角色

指一组相同权限的用户(学生老师角色)

  • 系统预设角色
    • 服务器级角色
    • bulkadmin:能执行BLUK INSERT操作
    • dbcreator:能创建、修改、删除和还原数据库
    • diskadmin:能管理磁盘文件
    • processadmin:能管理SQL Server运行的进程
    • securityadmin:处理大多数的日常事务(能管理登陆账户、创建或删除数据库等),但没有系统管理员的超级权限
    • serveradmin:具有服务器级别的配置权限(如开、关服务器)
    • setupadmin:仅限于添加和删除链接服务器
    • sysadmin:系统管理员,Windows的Administrator组自动映射为sysadmin
    • 数据库级角色
    • db_accessadmin:添加和删除数据库用户
    • db_backupoperator:备份数据库、日志
    • db_datareader:查询数据库中所有用户数据的权限
    • db_writer:插入、删除和更改所有数据库用户数据
    • db_ddladmin:具有执行DDL的权限
    • db_denydatareader:拒绝
    • db_owner:数据库拥有者
    • db_securityadmin:管理数据库角色、语句和各类对象的权限
    • public:所有数据库用户都是public的成员
  • 用户定义角色(数据库级角色)

    --创建学生角色,拥有者为老师角色
    CREATE ROLE Student AUTHORIZATION Teacher`

Oracle的安全管理

Oracle不仅支持集中式应用,还支持跨平台、分布式应用

用户权限:

  • DBA用户
  • 普通用户

角色

  • Connect角色:连接数据库,有查询权限
  • Resource角色:能创建各种对象
  • DBA:全部权限

第十章:数据库运行和维护

主要任务:保证数据库系统安全、可靠且高效率运行

基本工作

  1. 数据库的转存与恢复
  2. 数据库的安全性、完整性控制
  3. 检测并改善数据库的性能
  4. 数据库的重组和重构
    • 重组:重新调整存储方法、整理回收碎片,提高数据库性能(不改变原逻辑和物理结构)
    • 重构:部分修改数据的模式和内模式(如教务系统,有关制度修改,实体间的联系发生变化导致重构)

运行状态监控和分析

  1. 自动监控机制
  2. 手动监控机制
  3. 对数据库架构体系的监控
    • 使用/剩余空间等基本信息
    • 空间是否具有自动拓展能力
    • 哪些表的拓展将引起空间拓展
    • 段的占用空间和区间数等
  4. 对数据库性能的监控
    • 缓冲的命中率
    • 用户锁
    • 回滚段、临时段的使用情况等

数据库存储空间管理

  1. DBA应结合数据库的运行情况,预测未来每天会增长多少记录,从而预测目前剩余空间能使用多少时间
  2. 如果发现空间不足,要考虑扩大存储容量
  3. 若不能进行硬件拓展,应考虑合理的数据备份和转存策略

数据库性能优化

一般从下列几个方面考虑

数据库运行环境与参数调整方面

  1. 外部调整
    • CPU
      • 适当增加CPU的数量
      • 提高运算效率
      • 提高数据库的操作效率
      • 终止需要许多资源的进程
    • 网络:(大量SQL数据在网络上传输导致网速变慢)
      • 网络设备对网络的影响很大,可以考虑调整网卡、交换机、集线器等网络设备
  2. 调整内存分配
    • 通过调整相关参数,控制数据库的内存分配,可以很大程度上改善数据库系统的性能
  3. 调整磁盘I/O
    • 响应时间是评判数据库性能优劣的一个重要度量,减少磁盘I/O时间可以改善数据库性能
  4. 调整竞争
    • 控制连接数据库的最大进程数
    • 减少调度进程的竞争
    • 较少多线程服务进程的竞争
    • 减少重做日志缓冲区竞争
    • 减少回滚段竞争

数据库模式调整与优化

数据库设计阶段,强调高效率利用存储空间、减少数据冗余和不一致,即规范化

规范程度高的数据库通常产生很多表,在频繁查询时可能要求查询多个表的数据,对多表的连接会消耗更多的计算机资源

因此,可以反规范化

  • 增加派生冗余列:表中增加的列由表中的数据项经过计算生成
    • 在查询时减少连接操作,避免使用聚合函数
    • 可以提高查询统计的处理速度,达到以空间换时间的目的
  • 增加冗余列:在多个表中增加具有相同语义的列(主码和外码重复出现不属于冗余列)
    • 能够尽可能避免连接操作
    • 前提条件是保证冗余列及其对应列数据的一致性
    • 可以提高查询统计的处理速度,达到以空间换时间的目的
  • 重新组表:把经常连接的表组合到一起
    • 提高查询效率,减少连接
  • 分割表:(数据太庞大或数据具有使用独立性),可以分割表的数据
    • 水平分割
    • 会给应用增加很大程度上的复杂度,大部分情况下复杂度超过带来的优点
    • 大部分情况局部使用,很少情况才全局使用
    • 垂直分割:把常用、不常用的列分别放入不同表中
    • 数据行变小,数据页能存放更多数据
    • 减少查询时的I/O次数,提高查询效率
    • 缺点是查询所有列需要JOIN操作
  • 新增汇总表:将频繁使用的统计操作的中间、最终结果存储在汇总表中
    • 降低了数据访问量CPU计算量
    • 提高了数据统计速率

反规范化是为了减少表间的连接,提高查询性能
根据实际应用,合理采用反规范化技术

数据库的存储优化

  1. 物化视图:包括一个查询结果的数据对象

    • 预先计算并保存经常使用的操作结果
    • 提高查询效率
    • 适用于多个数据量较大的表进行连接操作(分布式多表连接)
  2. 聚集:将经常一起使用的具有公共列的多个表存储在一起

    • 减少I/O次数
    • 改善系统性能

查询优化

效率低下的SQL语句常常是系统效率不佳的主要原因

  1. 合理使用索引(还可删除和重建索引)
    • 索引能提高查询效率,但是会增加系统开销,要在加快查询与增加开销直接进行权衡
    • 使用索引情况
      • 查询中作为条件被使用的列,可建索引
      • 频繁进行排序、分组的列,可建索引
      • 一个列的值域很大时,可建索引
      • 待排序列有多个,可建复合索引
      • 数据库大量更新后,删除并重建索引
    • 索引调整与修改的原因
      • 缺少索引,查询语句执行时间过长
      • 某些索引使用频率低但占用空间
      • 索引被建立在频繁改变的属性上,系统开销大
    • 基于调优分析,可删除或增加索引
      • 目的:动态评估需求
  2. 避免或简化排序
    • GROUP BY 和 ORDER BY的次序和索引不同
    • 如果排序无法避免,应简化排序操作、缩小排序范围
  3. 消除对大型表数据的顺序存取
  4. 避免复杂的正则表达式
  5. 使用临时表加速查询
  6. 用排序取代非顺序磁盘存取
  7. 不充分的连接条件
  8. 存储过程
  9. 不随意使用游标
  10. 事务处理

SQL Server常用性能工具

  1. SQL Server Profiler
  2. 数据库引擎优化顾问

待整理

DFD方法的基本元素

  1. 数据源
    数据源(终点):代表系统之外的实体,可以是人、物或是其他的软件系统。
  2. 数据流
    • 数据流是数据在系统内传播的的路径,由一组成分固定的数据组成
    • 由于数据是在流动中,所以必须有流向,除了与数据存储之间的数据流不用命名外,数据流应该用名词或名词短语。
  3. 数据存储
    表示的是信息的静态存储(永久保存)
  4. 数据处理
    对数据的加工(处理)加工是对数据进行处理的单元,它接收一定的数据输入,对其进行处理,并产生输出

论述

提高数据查询效率

  1. 创建文件组,优化文件结构,提高查询效率
  2. 适当创建索引,索引可以提高查询效率,但要占用额外的数据空间,要合理使用索引
  3. 如果频繁查询大数据量的视图,可以对视图创建唯一聚集索引,可以提高查询性能

发表评论