首页 > 科技 > 计算机 > 数据库

达梦数据库性能优化

作译者：曾昭文等

出版时间：2022-04 千字数：332 版次：01-01 页数：232

开本：16开装帧： I S B N ：9787121431135

换版：

纸质书定价：￥98.0

库存:无

分享到：

共有图书评论0条【查看评论摘要】

看了又看

{{data.bookName}}

￥{{data.price}}

内容简介

前言

上架建议

作者简介

编辑推荐

音视频专区

第1 章DM8 性能优化概述··············································································1
1.1 优化的基本概念·················································································1
1.1.1 为什么优化···············································································1
1.1.2 谁来优化··················································································2
1.1.3 优化什么··················································································2
1.1.4 何时优化··················································································4
1.1.5 优化到什么程度·········································································6
1.2 优化的基本思路·················································································7
1.2.1 数据库全面健康检查···································································7
1.2.2 优化阶段设计············································································7
1.2.3 数据库配置···············································································8
1.2.4 添加新的应用程序······································································8
1.2.5 运行过程的优化·········································································8
1.2.6 生产环境中优化的特殊问题··························································9
1.3 优化的基本步骤··············································································.10
1.3.1 问题分析···············································································.10
1.3.2 监控系统性能·········································································.11
1.3.3 数据库重演（REPLAY） ··························································.11
1.3.4 检查数据物理一致性································································.12
1.3.5 优化数据库布局······································································.12
1.4 数据库优化误区··············································································.13
第2 章 DM8 体系架构·················································································.14
2.1 总体构成·······················································································.14
2.1.1 数据库··················································································.15
2.1.2 实例·····················································································.15
2.2 物理存储结构·················································································.15
2.2.1 配置文件···············································································.16
2.2.2 控制文件···············································································.16
2.2.3 数据文件···············································································.17
2.2.4 日志文件················································································18
2.2.5 备份文件················································································21
2.2.6 数据重演文件··········································································21
2.3 逻辑存储结构···················································································21
2.3.1 表空间···················································································21
2.3.2 段·························································································24
2.3.3 簇·························································································25
2.3.4 页·························································································26
2.3.5 记录······················································································27
2.4 DM8 内存结构··················································································27
2.4.1 内存池···················································································27
2.4.2 缓冲区···················································································28
2.4.3 排序区···················································································30
2.4.4 哈希区···················································································31
2.5 DM8 线程结构··················································································31
2.5.1 监听线程················································································31
2.5.2 工作线程················································································31
2.5.3 I/O 线程··················································································32
2.5.4 调度线程················································································32
2.5.5 日志FLUSH 线程·····································································33
2.5.6 日志归档线程··········································································33
2.5.7 日志APPLY 线程······································································33
2.5.8 定时器线程·············································································33
2.5.9 逻辑日志归档线程····································································34
2.5.10 MAL 系统相关线程·································································34
2.5.11 其他线程···············································································34
2.5.12 线程信息的查看······································································34
2.6 工作过程·························································································35
2.6.1 DM8 的启动和关闭过程·····························································35
2.6.2 数据管理工作过程····································································37
第3 章DM8 调优诊断工具············································································40
3.1 DEM 工具·······················································································40
3.1.1 DEM 工具部署·········································································40
3.1.2 DEM DMAgent 部署··································································42
3.1.3 功能操作················································································44
3.1.4 DEM 工具的应用举例································································46
3.2 SQL 跟踪工具··················································································48
3.2.1 SQL 跟踪日志··········································································48
3.2.2 SQL 性能分析工具····································································49
3.3 数据库检查工具（dmdbchk 工具） ······················································.50
3.3.1 功能简介···············································································.50
3.3.2 使用dmdbchk 工具··································································.51
3.3.3 查看dmdbchk 工具的参数·························································.51
3.3.4 dmdbchk 报告解读···································································.52
3.3.5 应用实例···············································································.52
3.4 性能统计信息·················································································.54
3.4.1 系统信息相关视图···································································.55
3.4.2 存储信息相关视图···································································.55
3.4.3 内存管理信息相关视图·····························································.56
3.4.4 事务信息相关视图···································································.56
3.4.5 线程信息相关视图···································································.57
3.4.6 历史模块相关视图···································································.57
3.4.7 缓存信息相关视图···································································.57
3.4.8 会话信息相关视图···································································.58
3.4.9 捕获信息相关视图···································································.58
3.5 AWR 报告······················································································.59
3.5.1 AWR 简介·············································································.59
3.5.2 DBMS_WORKLOAD_REPOSITORY 包········································.60
3.5.3 DBMS_WORKLOAD_REPOSITORY 的创建、检测、删除语句··········.63
3.5.4 AWR 快照应用举例·································································.64
第4 章DM8 实例优化·················································································.65
4.1 实例优化的相关概念········································································.65
4.1.1 达梦优化器············································································.65
4.1.2 统计信息···············································································.67
4.2 度量实例性能·················································································.69
4.2.1 数据库命中率·········································································.70
4.2.2 数据库等待统计数据································································.71
4.2.3 系统监视···············································································.74
4.2.4 了解应用程序·········································································.76
4.3 内存调优·························································································.77
4.3.1 共享内存池············································································.77
4.3.2 BUFFER 缓冲区调优································································.77
4.3.3 SQL 缓冲区调优······································································.78
4.3.4 字典缓冲区调优······································································.81
4.3.5 其他缓冲区调优······································································.81
第5 章DM8 I/O 优化··················································································.83
5.1 DM8 I/O 性能优化概述·····································································.83
5.1.1 I/O 性能相关概念······································································83
5.1.2 影响I/O 性能的主要因素····························································85
5.1.3 I/O 优化的主要措施···································································86
5.2 大表分区技术···················································································87
5.2.1 分区的概念·············································································87
5.2.2 分区的方法·············································································89
5.2.3 维护水平分区表·······································································98
5.2.4 水平分区表的限制·································································.101
5.2.5 生产环境下表分区实施方法·····················································.103
5.3 索引优化技术················································································.104
5.3.1 索引的概念和分类·································································.105
5.3.2 应用索引进行优化·································································.106
5.4 数据库空间碎片整理技术·································································.111
5.4.1 碎片整理的相关概念······························································.112
5.4.2 碎片评估方法·······································································.112
5.4.3 碎片整理方法·······································································.114
第6 章DM8 SQL 语句优化········································································.116
6.1 DM8 SQL 语句优化的相关概念·························································.116
6.1.1 DM8 SQL 语句的执行过程·······················································.116
6.1.2 查询优化器··········································································.120
6.2 SQL 语句执行计划·········································································.122
6.2.1 执行计划的简介····································································.122
6.2.2 执行计划查看·······································································.123
6.2.3 常见的操作符介绍·································································.124
6.3 SQL 统计信息···············································································.124
6.3.1 统计信息简介·······································································.124
6.3.2 统计信息对执行计划的影响·····················································.126
6.3.3 统计信息的更新及查看···························································.129
6.4 DM8 的索引设计············································································.132
6.4.1 DM8 索引的存储结构·····························································.133
6.4.2 索引的更新及查看·································································.134
6.4.3 执行计划不使用索引的情况·····················································.135
6.5 DM8 SQL 优化的基本步骤·······························································.138
6.5.1 确定优化目标·······································································.138
6.5.2 确定高负载的SQL 语句··························································.139
6.5.3 配置索引信息·······································································.139
6.6 DM8 SQL 优化的基本方法·······························································.139
6.6.1 利用绑定变量提升性能···························································.139
6.6.2 开发有效的SQL 语句·····························································.141
6.6.3 使用优化器提示（HINT）·························································143
附录A DM8 参数配置文件相关参数·······························································152
A.1 DM8 服务配置···············································································152
A.1.1 dm.ini···················································································152
A.1.2 dmmal.ini ··············································································182
A.1.3 dmarch.ini ·············································································184
A.1.4 dm_svc.conf ···········································································185
A.1.5 sqllog.ini ···············································································187
A.1.6 dmrep.ini···············································································189
A.1.7 dmllog.ini ··············································································190
A.1.8 dmtimer.ini ············································································191
附录B DM8 系统数据字典···········································································192
B.1 与Oracle 兼容数据字典····································································192
B.2 DM8 常用数据字典··········································································196
附录C DM8 常用动态性能视图·····································································198
C.1 系统信息相关性能视图·····································································198
C.2 进程和线程相关性能视图··································································198
C.3 数据库信息性能视图········································································199
C.4 数据库配置参数相关性能视图····························································200
C.5 会话信息相关性能视图·····································································200
C.6 资源管理信息相关性能视图·······························································202
C.7 段簇页信息相关性能视图··································································203
C.8 日志管理信息相关性能视图·······························································203
C.9 事务和检查点信息相关性能视图·························································204
C.10 事件信息相关性能视图···································································205
C.11 DSC 相关性能视图·········································································205
C.12 数据守护相关性能视图···································································206
C.13 其他性能视图···············································································207
附录D DM8 执行计划常用操作符··································································209

序一
数据库已成为现代软件生态的基石之一。遗憾的是，国产数据库的技术水平与国外一流水平相比还有一定的差距。同时，国产数据库在关键领域的应用普及度相对较低，应用研发人员规模还较小，大力推动和普及国产数据库的应用是当务之急。
由电子工业出版社策划，国防科技大学信息通信学院和武汉达梦数据库股份有限公司等单位多名专家联合编写的“国产数据库达梦丛书”，聚焦数据库管理系统这一重要基础软件，以达梦数据库系列产品及其关键技术为研究对象，翔实地介绍了达梦数据库的体系架构、应用开发技术、运维管理方法，以及面向大数据处理的集群、同步、交换等一系列内容，涵盖了数据库管理系统及大数据处理的多个关键技术和运用方法，既有技术深度，又有覆盖广度，是推动国产数据库技术深入广泛应用、打破国外数据库产品垄断局面的重要工作。
“国产数据库达梦丛书”的出版，预期可以缓解国产数据库系列教材和相关关键技术研究专著匮乏的问题，能够发挥出普及国产数据库技术、提高国产数据库专业化人才培养效益的作用。此外，丛书对国产数据库相关技术的应用方法和实现原理进行了深入探讨，也将会吸引更多的软件开发人员了解、掌握并运用国产数据库，同时可促进研究人员理解实施原理、加快相关关键技术的自主研发水平。


倪光南     
中国工程院院士    
2020年7月   

序二

作为现代软件开发和运行的重要基础支撑之一，数据库技术在信息产业中得到了广泛应用。如今，即使进入人人联网、万物互联的网络计算新时代，持续成长、演化和发展的各类信息系统，仍离不开底层数据管理技术特别是数据库技术的支撑。数据库技术从关系型数据库到非关系型数据库、分布式数据库、数据交换等不断迭代更新，很好地促进了各类信息系统的稳定运行和广泛应用。但是，长期以来，我国信息产业中的数据库大量依赖国外产品和技术，特别是一些关系国计民生的重要行业信息系统也未摆脱国外数据库产品。大力发展国产数据库技术，夯实研发基础、吸引开发人员、丰富应用生态，已经成为我国信息产业发展和技术研究中一项重要且急迫的工作。
武汉达梦数据库股份有限公司研发团队和国防科技大学信息通信学院，长期从事国产数据库技术的研制、开发、应用和教学工作。为了助推国产数据库生态的发展，扩大国产数据库技术的人才培养规模与影响力，电子工业出版社在前期与团队合作的基础上，策划出版“国产数据库达梦丛书”。该套丛书以达梦数据库DM8为蓝本，全面覆盖了达梦数据库的开发基础、性能优化、集群、数据同步与交换等一系列关键问题，体系设计科学合理。
“国产数据库达梦丛书”不仅对数据库对象管理、安全管理、作业管理、开发操作、运维优化等基础内容进行了详尽说明，同时也深入剖析了大规模并行处理集群、数据共享集群、数据中心实时同步等高级内容的实现原理与方法。特别是针对DM8融合分布式架构、弹性计算与云计算的特点，介绍了其支持超大规模并发事务处理和事务分析混合型业务处理的方法，实现动态分配计算资源，提高资源利用精细化程度，体现了国产数据库的技术特色。相关内容既有理论和技术深度，又可操作实践，其出版工作是国产数据库领域产学研紧密协同的有益尝试。

 王怀民    
中国科学院院士    
2020年7月 


序三

习近平总书记指出，“重大科技创新成果是国之重器、国之利器，必须牢牢掌握在自己手上，必须依靠自力更生、自主创新。”基于此，实现关键核心技术创新发展，构建安全可控的信息技术体系非常必要。
数据库作为科技产业和数字化经济中三大底座（数据库、操作系统、芯片）技术之一，是信息系统的中枢，其安全、可控程度事关我国国计民生、国之重器的重大战略问题。但是，数据库技术被国外数据库公司垄断达几十年，对我国信息安全造成了一定的安全隐患。
以武汉达梦数据库股份有限公司为代表的国产数据库企业，坚持40余年的自主原创技术路线，经过不断打磨和应用案例的验证，已在我国关系国计民生的银行、国企、政务等重大行业广泛应用，突破了国外数据库产品垄断国内市场的局面，保障了我国基本生存领域和重大行业的信息安全。
为了助推国产数据库的生态发展，推动国产数据库管理系统的教学和人才培养，国防科技大学信息通信学院与武汉达梦数据库股份有限公司，在总结数据库管理系统长期教学和科研实践经验的基础上，以达梦数据库DM8为蓝本，联合编写了“国产数据库达梦丛书”。该套丛书的出版一是推动国产数据库生态体系培育，促进国产数据库快速创新发展；二是拓展国产数据库在关系国计民生业务领域的应用，彰显国产数据库技术的自信；三是总结国产数据库发展的经验教训，激发国产数据库从业人员奋力前行，创新突破。

 李国徽      
华中科技大学软件学院院长、教授
2020年7月      

前言
发展具有自主知识产权的国产数据库管理系统，打破国外数据库产品的垄断，为我国信息化建设提供安全可控的基础软件，是维护国家信息安全的重要手段。
DM8 管理系统作为国内最早推出的具有自主知识产权的数据库管理系统之一，是唯一获得国家自主原创产品认证的数据库产品，现已在公安、电力、铁路、航空、审计、通信、金融、海关、国土资源、电子政务等多个领域得到广泛应用，对国家机关、各级政府和企业的信息化建设发挥了积极作用。
随着 DM8 技术的不断发展，数据库自动化管理能力不断提升，建立在DM8 之上的各类业务应用系统也日趋丰富。但由于各业务领域的特殊性，通用性的参数配置往往不能完全适用于所有业务领域，随着业务数据的不断积累、业务的不断拓展，对于执行大量磁盘I/O 和数据处理的数据库的性能要求也越来越高，有必要根据具体的业务环境定制最优的数据库运行参数，从而充分发挥数据库的管理效能。因此，数据库性能管理和数据库代码优化成为数据库专业人员的一项重要工作。
《达梦数据库性能优化》作为《国产数据库达梦丛书》之一，从DM8 体系架构入手，分析了DM8 的运行机制，从DM8 的实例优化、I/O 优化、SQL 语句优化3 个方面详细地介绍了影响DM8 性能的核心问题。全书共6 章，内容包括DM8 性能优化概述、DM8体系架构、DM8 调优诊断工具、DM8 实例优化、DM8 I/O 优化和DM8 SQL 语句优化等。同时，本书还将DM8 参数配置文件相关参数、DM8 系统数据字典和DM8 常用动态性能视图作为附录，便于广大读者查阅。
本书内容实用、示例丰富、语言通俗、格式规范。为了方便读者学习和体验操作，本书的例题源码可以在DM8 官网下载。
在本书的编写过程中，参考了武汉达梦数据库股份有限公司提供的技术资料，在此表示衷心的感谢。
由于编者水平有限，书中难免有些错误与不妥之处，敬请读者批评指正。欢迎读者通过电子邮件1251463841@qq.com 与我们交流，也欢迎访问达梦数据库官网、达梦数据库官方微信公众号“达梦大数据”，或者拨打服务热线400-991-6599 获取更多达梦数据库资料和服务。
编 者
2021 年10 月于武汉

查看更多 > 图书评论

暂无评论

发表图书评论

评论标题：
评论内容：
验证码：: 看不清楚
点击刷新
: 您还没有登录，请登录后再评论。
点击“登录”

购买过本书的顾客还买过

{{data.bookName}}

￥{{data.price}}