SQL 21日自学通(V3.0)(PDF格式)-第63部分
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
…………………………………………………………Page 318……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
删除索引以优化数据的载入
一种可以加快批量更新速度的方法是删除索引 设想一下如果历史表的记录有上千条
而且它还可能有一个甚至更多的索引 你认为索引会怎样 你通常会认为索引可以加快表
的访问速度 但是在批量载入的时候 将索引删除的好处可能会更大
当你通过索引向表中装放数据的时候 你通常会希望尽可能地使用索引 尤其是当你
要更新的记录在表中所占的比率很高的时候 那么让我们来看一下这种方法 如果我们学
习一本以后续指引的书的话 你也许会发现从头至尾地看这本书要比用索引来定位你的关
注点更快 如果你所要关注的东西所占的比重占全书中的比重比较小的时候索引是更有效
的
为了让数据占总表比重相当的批量载入达到最大效率 你可以使用下边的三步来使索
引失去作用
1 删除适当的索引
2 装入或更新表中的数据
3 重新生成表的索引
经常使用 MIT 来让 DBA 走开
当你在执行一个批量事务时 你必须知道要多长时间执行一回 MIT 命令 就像
你在第 11 天 事务控制 所学到的那样 MIT 可以结束一个事务 MIT 可以将
事务中所作的任何改变写要实际的表中 但是在后台 它做的工作不只如此 在数据库中
有一个区域是用以存储全部的写到实际表中之前的事务数据的 ORACLE 将这一区域叫做
ROLLBACK 段 当你执行一个 MIT 命令以后 与你的 SQL 相关联的事务会将
ROLLBACK 段中的内容写到实际的表中 然后更新这一区域 ROLLBAKC 段中的原有内
容就被删除了 ROLLBACK 命令是另一种清除 ROLLBACK 段的命令方法 只是它不将所
做的改动写到目标表中
如你所料 如果你一直不执行 MIT 或 ROLLBACK 命令 那么事务就会一直保存
在 ROLLBACK 段中 随之而来的是 如果你要装入的数据大小比 ROLLBACK 段的可用
空间还要大 数据库将会终止并挂起所有的活动事务 不运行 MIT 命令是通用程序
的一个缺陷 有规律地使用 MIT 命令将会例数据库系统输入的性能稳定
318
…………………………………………………………Page 319……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
对 ROLLBACK 的管理是数据库管理员 DBA 的一项复杂而重要的责任 因为事务
对 ROLLBACK 段的影响是动态的 随后是像个别的 SQL 语句一样影响数据库的整体性能
所以当你批量载入大量数据的时候 要确保按一定的规律执行 MIT 命令 由你的数
据库管理进行检查并告诉你应该很久执行一次 MIT 命令 见下图
你在上图中也看到了 当用户执行一个事务的时候 所做的改动是保存在 ROLLBACK
段中的
在动态环境中重新生成表和索引
在大型数据库系统中动态数据库环境一词的意思就是状态在不断地改变 我们在批处
理进程和日常事务处理过程中会经常使用这种改变 动态数据库通常很定于 OLTP 系统
但是也可以在 DSS 或数据仓库中引用它 这要视需载入的数据的量和频度而定
结果是数据库中持续不断的大量数据的改变 从而造成大量的碎片 如果管理不当这
些碎片就很容易失去控制 ORACLE 在表最初生成时为它分配了一个长度 当数据载入并
填充完初始长度以后 初创建的表会得到下一个分配的长度
表和索引的大小的数据库管理员的工作 而且它对 SQL 语句性能的影响是很大的 首
先要进行正确的管理 所分配的空间应该足够表在一天中所增加的尺寸 同时也应该制定
一个计划以按一定的规律对数据库进行碎片的清理工作 如果可能最好这成为每周的例行
工作 清除关系型数据库中的表和索引的碎片在基本概念上的非常麻烦的
1 对表和索引进行完善的备份
2 删除表和索引
3 用新的分配空间来重新生成表和索引
319
…………………………………………………………Page 320……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
4 将数据恢复到新建的表中
5 如有必须 重新生成索引
6 对该表重新分派用户的规则和权限
7 直到你已经确认了新生成的表是完全正常的 否则请保留备份 如果你选择了放弃
对原始表的备份 你要在新表的数据完全恢复后马上做一个备份
警告 当你还没有确认新表已经完全正常之前千万不要丢弃原有的备份
下边给出了一个 ORACLE 数据库中邮件清单表的实际的真实例子
INPUT
CREATE TABLE MAILING_TBL_BKUP AS
SELECT * FROM MAILING_TBL；
OUTPUT
Table Created。
INPUT
drop table mailing_tbl；
OUTPUT
Table Dropped。
INPUT
CREATE TABLE MAILING_TBL
（ INDIVIDUAL_ID VARCHAR2（12） NOT NULL
INDIVIDUAL_NAME VARCHAR2（30） NOT NULL
ADDRESS VARCHAR（40） NOT NULL
CITY VARCHAR（25） NOT NULL
STATE VARCHAR（2） NOT NULL
ZIP_CODE VARCHAR（9） NOT NULL
） TABLESPACE TABLESPACE_NAME
STORAGE （ INITIAL NEW_SIZE
NEXT NEW_SIZE）
OUTPUT
Table created。
320
…………………………………………………………Page 321……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
INPUT
INSERT INTO MAILING_TBL
select * from mailing_tbl_bkup；
93；451 rows inserted。
CREATE INDEX MAILING_IDX ON MAILING TABLE
（ INDIVIDUAL_ID ）
TABLESPACE TABLESPACE_NAME
STORAGE （ INITIAL NEW_SIZE
NEXT NEW_SIZE ）
OUTPUT
Index Created。
INPUT
grant select on mailing_tbl to public
OUTPUT
Grant Succeeded。
INPUT
drop table mailing_tbl_bkup；
OUTPUT
Table Dropped。
分析
重新生成表和索引可以让你的存储空间达到最优化 这将会提高整体的性能 切记
只有当你确认新生成的表正确无误以后才可以删除原有表的备份 而且你也应该知道你用
另一种方法达到了相同的结果 请检查数据库文档看一下它的可以选项
数据库的调整
调整数据库就是将数据库的服务优化的过程 如果你刚开始使用 SQL 那么除非你是
数据库管理员或是刚刚接触关系型数据库的数据库管理员 否则你不公直接面对数据库的
调整 不管你是不是数据库管理员 也不管理你会不会使用 SQL 来在应用程序中编程 如
果想让所有的部分协同工作 通常的调整技巧有以下几点
321
…………………………………………………………Page 322……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
将数据库所需的全部大小最小化
在设计时预留出增长的空间很好 但是不要对此过于迷恋 不要将你以后数据库的增
长空间与资源强行捆绑
对用户进程进行可用时间片试验
这样你可以为不同的用户分配不同的可控时间
优化你所使用的应用的网络包的尺寸
要通过网络传送大量的数据 应该设定网络的最大包尺寸 你参考你的数据库和网络
文档来查看更多的内容
将大表分配于不同的磁盘上
如果并发用户访问位于多个磁盘上的大表 等待系统资源的机会就会减少
将数据库的排序区 系统目录区 撤消区分布于不同的磁盘上 见下图
这些区域是用户访问最多的区域 把它放在多个硬盘上你可以最有效地利用资源
增加 CPU
这样系统管理及数据库的性能将会得到极大的提高 增加 CPU 可以提高数据处理的速
度是显而易见的 如果你在机器上将有多个 CPU 那么你将有机会行使并行处理策略 关
于并行处理请参考你的数据库相关文档中的更详细的信息— — 如果它在你的环境中是可用
的话
增加内存
一般说来 这样会更好
将表和索引存贮在不同的硬盘上
只要有可能 你就应该将索引和与它相关的表存储在不同的硬盘上 这种安排可以在
读表的同时访问与该表相关的索引 存储在多个硬盘上的对象的实际能力取决于有多少块
硬盘连接于控制器上
下图给出的如何隔离数据库各个主要区域的简单例子
在上图中使用了四个设备 从 DISK01 到 DISK04 这样做的目的是保证每个盘都有较
322
…………………………………………………………Page 323……………………………………………………………
SQL 21 日自学通（V1。0） 翻译人 笨猪
高的访问率
DISK01…系统目录中存储着关于表 索引 用户 统计信息 数据文件的名字 大小
增加长度以及其它的相关数据 它在事务中被访问的次数相当多
DISK02…每天的每一次对表的内容的改变 插入 更新 删除 都会更新事务的历史
记录信息 事物记录是在线事务的一项重要功能 但它在只读型的数据库如 DSS 或数据仓
库中的意义不大
DISK03…ROLLBACK 段对于事务处理环境的意义也很大 可是 如果很少进行事务活
动的话 ROLLBACK 段就不会被经常使用
DISK04…这里是数据库排序区 也就是说它是 SQL 语句过程和排序数据 如 GROUP BY
或 ORDER BY 子句 的临时存放区域 DSS 和数据仓库是排序区域的典型代表 但是
在事务处理环境中也考虑使用排序区域
技巧 你也应该注意到数据库的表和索引是如何入到每一个磁盘上的 应该尽可能地将表
和索引分开
注意在上图中表和索引是被安排在不同的设备上的 你也可以看在 大表 和索引在
不同设备上的交互访问 这种技术将大表分为可以访问的小段 交互访问表和索引是一种
控制碎片的办法 在这种情况下可以在参考相关索引的同时进行读表 这就会提高数据的
整体访问速度
这个例子的确是非常简单的 根据你所使用的数据库的函数 大小和相互关系 你可
以找到类似的方法对系统进行优化以提高工作性能 如果钱不是问题那么完美的方法是将
每一个数据库实体都放在一个硬盘上 包括大表和索引
注 DBA 与系统管理员应该协同工作以平衡数据库的使用空间和优化服务器的可用存储空
间
对数据库的调整要依据你所使用的数据库系统而定 显而易见 优化查询要比调整数
据库的作用更明显 也就是说当你还没有将 SQL 应用本身调整好时你不要期望在对数
