第07章 数据库设计(4)

数据库系统概论

数据库系统概论An Introduction to Database System

第七章 数据库设计 (4) (4)

An Introduction to Database System

数据库系统概论

实例1---电信通话信息系统 电信通话信息系统 实例问题描述 该系统投入运行后正常， 该系统投入运行后正常，四个月时开始出现 复杂条件的查询得不到结果的情况。原因何在？ 复杂条件的查询得不到结果的情况。原因何在？ 复杂条件的SQL语句查询造成超时； 复杂条件的SQL语句查询造成超时； SQL语句查询造成超时 发现是对单表查询，该表的记录数将近2000万 发现是对单表查询，该表的记录数将近2000万 2000 且每天以20万条的速度增加； 20万条的速度增加 条，且每天以20万条的速度增加； 由于查询数据表中的记录数过多引起的。 由于查询数据表中的记录数过多引起的。 采用何种方法才能提高大数据量表的查询效率？ 采用何种方法才能提高大数据量表的查询效率？2

An Introduction to Database System

数据库系统概论

实例1---电信通话信息系统 电信通话信息系统 实例sector record_date start_of_time end_of_time city_name city_id omc_id mm_id …… 分区编号 记录日期 开始时间 结束时间 城市名称 城市编号

……An Introduction to Database System 3

数据库系统概论

实例1---电信通话信息系统 电信通话信息系统 实例解决办法： 解决办法： 大数据量表的查询属于数据库优化的一个方面 优化原则：尽量减少I/O次数； 优化原则：尽量减少I/O次数； I/O次数 具体做法：采用水平分区的方法 具体做法： 将一个表分割成结构相同的几个表， 将一个表分割成结构相同的几个表，每个 表中只包含大表中的一部分数据行。查询时， 表中只包含大表中的一部分数据行。查询时， 就可以只查询包括查询结果在内的一、 就可以只查询包括查询结果在内的一、两个小 I/O数量大幅降低 数量大幅降低。 表，使I/O数量大幅降低。An Introduction to Database System 4

数据库系统概论

实例1---电信通话信息系统 电信通话信息系统 实例具体实现： 具体实现： (1)以月份为单位将数据表拆分为多张小表； (1)以月份为单位将数据表拆分为多张小表； 以月份为单位将数据表拆分为多张小表sector record_date start_of_time end_of_time city_name city_id omc_id mm_id …… 分区编号 记录日期 开始时间 结束时间 城市名称 城市编号

拆分后的小表的名称为： 拆分后的小表的名称为： Pm_hour_sector_200701 Pm_hour_sector_200702 Pm_hour_sector_200703 Pm_hour_sector_200704 ……An Introduction to Database System 5

……

数据库系统概论

实例1---电信通话信息系统 电信通话信息系统 实例具体实现： 具体实现： (2)查询数据合并； (2)查询数据合并； 查询数据合并 表分区减少了查询时I/O的数量， 表分区减少了查询时I/O的数量，但是

增加 I/O的数量 了查询时选择表的负担。 了查询时选择表的负担。 Server通过使用分区视图将数据合并 通过使用分区视图将数据合并， SQL Server通过使用分区视图将数据合并， 利用单一视图面对应用程序， 利用单一视图面对应用程序，这样简化了查询 数据操作。 数据操作。

An Introduction to Database System

数据库系统概论

实例1---电信通话信息系统 电信通话信息系统 实例具体实现： 具体实现： (2)查询数据合并； (2)查询数据合并； 查询数据合并 分区视图的定义为： 分区视图的定义为： CREATE VIEW Pm_hour_sector_history_view as select * from pm_hour_sector_200701 union all select * from pm_hour_sector_200702 union all ……An Introduction to Database System 7

数据库系统概论

实例1---电信通话信息系统 电信通话信息系统 实例具体实现： 具体实现： (3)查询优化的条件； (3)查询优化的条件； 查询优化的条件 分区视图的核心内容： 分区视图的核心内容：要求针对该视图执行的 任何查询都被优化成只执行查询结果所涉及的 基本表，其余表自动忽略。 基本表，其余表自动忽略。 对分区视图的限制：每张表必须建立check check约 对分区视图的限制：每张表必须建立check约 限制该表存放的数据彼此的范围不重叠。 束，限制该表存放的数据彼此的范围不重叠。 这样， Server才能做出相应的优化选择 才能做出相应的优化选择。 这样，SQL Server才能做出相应的优化选择。An Introduction to Database System 8

数据库系统概论

实例1---电信通话信息系统 电信通话信息系统 实例具体实现： 具体实现： 为小表增加time_id字段， numeric类型 time_id字段 类型， 为小表增加time_id字段，是numeric类型， 存放时间信息，格式yymmddhhmm yymmddhhmm, 存放时间信息，格式yymmddhhmm,如： pm_hour_sector_200701表建立的约束为 表建立的约束为： pm_hour_sector_200701表建立的约束为：time_id between 200701010000 and 200701312359。 。

建立常用字段的非聚簇索引 (4)解决后的执行情况 解决后的执行情况； (4)解决后的执行情况； 一条查询语句的执行情况： 一条查询语句的执行情况： 前者的执行结果是20s 后者是2s 20s, 2s。 前者的执行结果是20s,后者是2s。An Introduction to Database System 9

数据库系统概论

实例1---电信通话信息系统 电信通话信息系统 实例存在问题： 存在问题： 1.按时间存取数据的小表谁来创建？ 1.按时间存取数据的小表谁来创建？ 按时间存取数据的小表谁来创建 手工还是程序？ 手工还是程序？ 2.小数据表增加以后 分布视图谁来负责更新？ 小数据表增加以后， 2.小数据表增加以后，分布视图谁来负责更新？ 3.小表和视图变动后， 3.小表和视图变动后，应用程序中用户的访问权 小表和视图变

动后 是否受到影响？ 是否受到影响？ 4.数据以何种方式存储在小表中 数据以何种方式存储在小表中？ 4.数据以何种方式存储在小表中？ 可更新的分区视图还是单独编写程序进行数据 存储的？ 存储的？An Introduction to Database System 10

数据库系统概论

实例1---电信通话信息系统 电信通话信息系统 实例实践经验： 实践经验： 1.在数据表中，数据大于1000万时， 1000万时 1.在数据表中，数据大于1000万时，建议分区解 在数据表中 决 2.小于1000万时， 2.小于1000万时，采用对常用查询字段建立非聚 小于1000万时 簇索引的方式进行优化。 簇索引的方式进行优化。 3.实例验证，使用SQL Server可以管理800G以上 可以管理800G 3.实例验证，使用SQL Server可以管理800G以上 实例验证 的数据案例。 的数据案例。

An Introduction to Database System

数据库系统概论

实例2---全国人口查询系统 全国人口查询系统 实例问题描述 公安部准备将全国各省市的人口基本信息采集 入库， 入库，目前已经确定每个具有身份证的中华人民共 和国公民的基本信息包括两大部分： 和国公民的基本信息包括两大部分： 文本信息： 文本信息：150KB 图象信息： 图象信息：50KB 建成后系统供全国所有可能的用户使用， 建成后系统供全国所有可能的用户使用，包括 企业、学校、政府、旅店和其他个人用户等等。 企业、学校、政府、旅店和其他个人用户等等。An Introduction to Database System 12

数据库系统概论

实例2---全国人口查询系统 全国人口查询系统 实例系统要求： 系统要求： 按照某些条件查询某一个人是否存在， 按照某些条件查询某一个人是否存在，详细信息 系统中的数据不会频繁更新

An Introduction to Database System

数据库系统概论

实例2---全国人口查询系统 全国人口查询系统 实例1.海量信息存储的问题 1.海量信息存储的问题 假设13亿人口的信息将存入系统， 假设13亿人口的信息将存入系统，这个数据库 13亿人口的信息将存入系统 的容量达到多少？ 的容量达到多少？如果再加上一部分已死亡人的信 数据量将更大。 息，数据量将更大。 解决方案：SAN 存储区域网络 解决方案：SAN—存储区域网络 海量存储系统将数据集中起来存放， 海量存储系统将数据集中起来存放，并且将 存放介质与数据库服务器物理上隔离开来， 存放介质与数据库服务器物理上隔离开来，其扩展 性非常好。 性非常好。An Introduction to Database System 14

数据库系统概论

实例2---全国人口查询系统 全国人口查询系统 实例2.选用什么样的DBMS? 2.选用什么样的DBMS? 选用什么样的DBMS 数据库的容量过大，有没有一种DBMS可以管理 数据库的容量过大，有没有一种DBMS可以管理 DBMS 的数据？ 几百 TB 的数据？ 一

个数据库的最大容量可达多少？ 一个数据库的最大容量可达多少？ 数据库中一张表的最大容量是多少(存放的 数据库中一张表的最大容量是多少( 记录数)? 记录数)? 解决方案： 解决方案： (1)需要将这些数据放到几十个数据库中。 (1)需要将这些数据放到几十个数据库中。 需要将这些数据放到几十个数据库中 (2)选择能支持数据分布和良好数据划分功能的 (2)选择能支持数据分布和良好数据划分功能的 DBMS。 DBMS。An Introduction to Database System 15

数据库系统概论

实例2---全国人口查询系统 全国人口查询系统 实例3.采用什么样的体系结构？ 3.采用什么样的体系结构？ 采用什么样的体系结构 因为同时访问这个数据库的并发用户很多， 因为同时访问这个数据库的并发用户很多，为 了保证系统的查询时间，系统必须支持并行计算。 了保证系统的查询时间，系统必须支持并行计算。 解决方案： 解决方案： 采用支持并行计算的大型计算机 采用支持并行计算的大型计算机 利用高速网连接起来的多个PC服务器组成的机群 利用高速网连接起来的多个PC服务器组成的机群 PC服务器组成的 系统 考虑系统总线的吞吐量和磁盘阵列的吞吐量(I/O) 考虑系统总线的吞吐量和磁盘阵列的吞吐量(I/O)An Introduction to Database System 16