CN110580247A

CN110580247A - 针对数据共享平台实现数据库操作效率优化功能的系统及其方法

Info

Publication number: CN110580247A
Application number: CN201910858452.6A
Authority: CN
Inventors: 徐明明; 臧一超; 刘相
Original assignee: PRIMETON INFORMATION TECHNOLOGY Co Ltd
Current assignee: PRIMETON INFORMATION TECHNOLOGY Co Ltd
Priority date: 2019-09-11
Filing date: 2019-09-11
Publication date: 2019-12-17

Abstract

本发明涉及一种针对数据共享平台实现数据库操作效率优化功能的系统，包括初始化模块，用于创建数据库中的表以及插入数据；常规优化模块，用于实现优化查询、优化数据库结构以及优化数据库服务器；查找异常查询模块，用于分析数据库报告的项目信息，并根据所述的项目信息定位异常脚本；优化模块，用于分析耗时严重的SQL，并进行调整和优化。本发明还涉及一种实现针对数据共享平台的数据库操作效率优化处理的方法。采用了本发明的针对数据共享平台实现数据库操作效率优化功能的系统及其方法，在常规数据库优化手段的基础上，将耗时、耗资源的SQL从业务、语法的角度上进行调整和优化，降低耗时，减少资源的占用，提高系统的反应速度。

Description

针对数据共享平台实现数据库操作效率优化功能的系统及其方法

技术领域

本发明涉及数据资产管理领域，尤其涉及数据资产优化管理领域，具体是指一种针对数据共享平台实现数据库操作效率优化功能的系统及其方法。

背景技术

数据服务共享平台定位于企业数据资源“纵向贯通”、“横向互联”的共享通道，使其成为企业、组织、部门的数据工厂，主要从安全管控、服务治理、服务消费、服务开发效率等系列问题入手，提供统一平台对多源、多类型数据进行统一的服务化管控，帮助企业更加有效、可靠的使用数据。

数据共享平台是处理数据交换的一种重要方式，是将数据规划整理后提供出去的一种管理方式，包括整个数据的流程和走向，通过有效的管理实现数据价值。其中，数据库的效率和性能是数据共享平台的一个重要组成部分，在系统的使用过程中，数据库的瓶颈和资源占用直接影响数据共享平台的数据提取效率、可否满足业务场景、用户体验度等方面，一定程度影响着数据资产管理的效率。

目前，针对数据共享平台中的数据库性能优化是根据项目选择数据库类型并且将数据源配置到项目当中，在项目的开发过程中在查询、数据库结构、数据库服务器方面进行优化，具体的实现步骤如下：

1、根据项目要求选择数据可类型，并且在项目中配置数据源；

2、使用数据库关键字语法优化查询；

3、使用分解表，增加中间表，增加冗余字段，优化插入记录的速度等手段优化数据库结构；

4、在服务器硬件、数据库参数优化服务器；

5、根据数据库消耗情况重复步骤2)，3)，4)，直到数据库SQL消耗恢复到正常。

根据如上步骤在数据共享平台中的选择数据库类型，配置数据源，通过常规手段对数据库进行性能优化是有一定的效果，但是实际项目使用中，数据出现偏差的场景是经常出现，导致一部分SQL执行的次数异常，耗时并且资源占用过多，数据库的性能上会有一定的影响，进而会影响系统的使用效率，使得数据共享平台的用户体验度下降。

现有的数据共享平台中可以选择数据库类型，配置数据源，在使用的过程中通过常规手段对数据库进行性能优化是有一定的效果，但是实际项目使用中，数据出现偏差的场景是经常出现，客户在不熟悉系统使用的情况下配置出的数据也是会很容易出现问题，出现某些SQL查询消耗资源异常高，进而会影响系统使用。比如在系统的使用过程中，某个SQL在2小时内达到百万次的执行，严重消耗了数据库资源，使得其余查询响应速度慢，影响数据共享平台无法正常使用。

发明内容

本发明的目的是克服了上述现有技术的缺点，提供了一种满足耗时低、资源占用少、反应速度快的针对数据共享平台实现数据库操作效率优化功能的系统及其方法。

为了实现上述目的，本发明的针对数据共享平台实现数据库操作效率优化功能的系统及其方法如下：

该针对数据共享平台实现数据库操作效率优化功能的系统，其主要特点是，所述的系统包括：

初始化模块，用于创建数据库中的表以及插入数据；

常规优化模块，与所述的初始化模块相连接，用于实现优化查询、优化数据库结构以及优化数据库服务器；

查找异常查询模块，与所述的常规优化模块相连接，用于分析数据库报告的项目信息，并根据所述的项目信息定位异常脚本；

优化模块，与所述的查找异常查询模块相连接，用于分析耗时严重的SQL，并进行调整和优化。

较佳地，所述的初始化模块插入的数据包括初始化的用户登录名和密码、目录信息、功能信息和页面数据。

较佳地，所述的初始化模块中的数据库表信息包括分布式环境下的微服务应用平台、数据服务共享平台、数据集成平台、企业间服务整合单元、元数据资产整合单元、补充信息单元。

较佳地，所述的常规优化模块包括：

优化查询单元，与所述的初始化模块相连接，用于提高数据库的查询速度；

优化数据库结构单元，与所述的初始化模块相连接，用于分解字段多的表、增加中间表、增加冗余字段和优化插入记录的速度；

优化数据库服务器单元，与所述的初始化模块相连接，用于对硬件进行优化以及对数据库服务的参数进行优化。

较佳地，所述的查找异常查询模块使用的数据库报告为AWR报告。

该基于上述系统实现针对数据共享平台的数据库操作效率优化处理的方法，其主要特点是，所述的方法包括以下步骤：

(1)初始化模块创建数据库中涉及的表以及在数据库中插入数据；

(2)常规优化模块优化查询过程、优化数据库结构以及优化数据库服务器；

(3)查找异常查询模块分析数据库报告的项目信息，并根据所述的项目信息定位异常脚本；

(4)优化模块分析耗时严重的SQL，并进行调整和优化。

较佳地，所述的步骤(3)具体包括以下步骤：

(3.1)运行脚本产生报告；

(3.2)输入报告的类型以及收集AWR报告信息的持续时间；

(3.3)根据的快照号和快照时间，选择需要分析的快照信息；

(3.4)输入生成报告的文件名称；

(3.5)在相应的目录下查看和分析报告。

较佳地，所述的步骤(3.5)中通过数据库的AWR报告来分析报告。

采用了本发明的针对数据共享平台实现数据库操作效率优化功能的系统及其方法，通过分析执行次数很多、占用资源较大的SQL，在常规数据库优化手段的基础上，分析耗时比较严重的SQL，进而进行调优，将耗时、耗资源的SQL从业务、语法的角度上进行调整和优化，降低耗时，减少资源的占用，提高系统的反应速度。

附图说明

图1为本发明的针对数据共享平台实现数据库操作效率优化功能的系统的模块示意图。

图2为本发明的实现针对数据共享平台的数据库操作效率优化处理的方法的流程图。

具体实施方式

为了能够更清楚地描述本发明的技术内容，下面结合具体实施例来进行进一步的描述。

如图1所示，本发明的该针对数据共享平台实现数据库操作效率优化功能的系统，其中包括：

初始化模块，用于创建数据库中的表以及插入数据；

作为本发明的优选实施方式，所述的初始化模块插入的数据包括初始化的用户登录名和密码、目录信息、功能信息和页面数据。

作为本发明的优选实施方式，所述的初始化模块中的数据库表信息包括分布式环境下的微服务应用平台、数据服务共享平台、数据集成平台、企业间服务整合单元、元数据资产整合单元、补充信息单元。

作为本发明的优选实施方式，所述的常规优化模块包括：

作为本发明的优选实施方式，所述的查找异常查询模块使用的数据库报告为AWR报告。

如图2所示，本发明的该基于上述系统实现针对数据共享平台的数据库操作效率优化处理的方法，其中包括以下步骤：

(3.1)运行脚本产生报告；

(3.2)输入报告的类型以及收集AWR报告信息的持续时间；

(3.3)根据的快照号和快照时间，选择需要分析的快照信息；

(3.4)输入生成报告的文件名称；

(3.5)在相应的目录下查看和分析报告；

(4)优化模块分析耗时严重的SQL，并进行调整和优化。

作为本发明的优选实施方式，所述的步骤(3.5)中通过数据库的AWR报告来分析报告。

本发明的具体实施方式中，本发明涉及一种基于数据共享平台数据库操作的效率优化方法。目前在数据共享平台的使用中，支持多种数据库类型交互，在与数据库的交互过程中，在有限的的资源中，尽可能减少系统的瓶颈，减少资源的占用，增加系统的反应速度，是开发人员所追求的目标，当前理论上的优化方法包括优化查询、优化数据库结构、优化数据库服务器，但是在实际的项目开发中，即便使用了上述的优化手段，仍旧会出现因某些原因导致的数据库消耗特别大，占用资源高而导致数据库异常，针对该问题，本发明在使用上述优化手段的基础上通过分析耗时比较严重的SQL，进而进行调优，将耗时、耗资源的SQL从业务、语法的角度上进行调整、优化，降低耗时，减少资源的占用，提高系统的反应速度。

本发明通过分析执行次数很多，占用资源较大的SQL，在常规数据库优化手段的基础上，分析耗时比较严重的SQL，进而进行调优，将耗时、耗资源的SQL从业务、语法的角度上进行调整、优化，降低耗时，减少资源的占用，提高系统的反应速度。

本发明分为数据库初始化模块、常规优化模块、查找异常查询模块、优化模块。

数据库初始化模块是数据共享平台中的初始化数据，包括初始化的用户登录名和密码、最基本的目录信息、功能信息和页面数据，主要是数据库脚本和产品的安装包；常规优化模块是指优化查询、优化数据库结构、优化数据库服务器；查找异常查询模块是在系统的运行过程中，在和数据库的交互过程中会记录下一些查询信息，根据这些信息去定位异常脚本；优化模块是指在常规数据库优化手段的基础上，分析耗时比较严重的SQL，进而进行调优，将耗时、耗资源的SQL从业务、语法的角度上进行调整、优化，降低耗时，减少资源的占用，提高系统的反应速度。

如图1所示，下面将详细描述数据库初始化模块、常规优化模块、查找异常查询模块、优化模块。

一、数据库初始化模块

数据库初始化模块是数据共享平台中的初始化数据脚本，指数据库中涉及到的表的创建和数据的插入，包括初始化的用户登录名和密码、最基本的目录信息、功能信息和页面数据，主要是数据库脚本和产品的安装包，该模块只包括最简单的表创建和数据的增加，在系统的使用过程中还会增加新的数据。

针对数据库初始化的操作涉及到的具体信息如下：

数据库表信息包括EOS、DSP、DI、ESB、METACUBE、OTHER六部分，初始完成后数据库会有499张表的数据。

EOS：分布式环境下的微服务应用平台，支持传统SOA应用和微服务应用(前端应用、后端服务)的建设。

DSP：数据服务共享平台DSP(Data Service Platform)基于大数据架构提供统一的数据服务能力，是企业数据资源对外开放的共享通道，是数据资源价值变现的关键环节。DSP提供实时接口服务、批量作业服务、文件传输服务，从数据定义、服务开发、服务消费、运行管控四个方面着手，实现数据资源的闭环管理。

DI：是一个数据集成平台，能够按照统一的规则集成并提高数据的质量，通过图形化工具快速灵活地设计与部署实现数据的抽取、转换、加载等功能。DI由运行容器、业务模型、业务调度、元数据仓库、管理控制台和图形化设计界面六部分构成，为企业数据集成提供一套完整的解决方案。

ESB：主要用于企业内及企业间的服务整合，通过总线形式有效改善现有系统之间调用的网状关系，使得系统之间的关系更加可视化和提高管控能力，它的高性能、高可靠、高扩展和业务化给客户带来高管控能力、高投资回报、高运营等能力，从而为提高企业IT的服务质量提供可能，更直接的为企业的业务扩展、业务创新、客户维护和卓越运营提供了有力的保障。

METACUBE：能够整合游离于企业各环节的元数据资产，便于用户浏览及分析元数据；帮助用户了解和管理信息和加工处理过程的来源，也有助于用户理解信息与加工过程之间的关系以及它们如何被使用，支持采集多种元数据来源。

OTHER：包括一些补充的表和数据。

表数量比较多，主要包括表的增加、修改、删除，数据的增加具体的脚本此处不做说明。

二、常规优化模块

常规优化模块是指优化查询、优化数据库结构、优化数据库服务器。

优化查询：查询是数据库中最频繁的操作，提高查询速度可以有效地提高数据库的性能。

常用分析查询语句的关键字：describe，explain，将产生查询语句的附加信息。

语法如下：

Explain select*from tablename；

Describe select select_options；

使用索引，索引是数据库中高效访问数据的方法，并且加快查询的速度，使用索引可以快速定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。

语法如下：

Create index index_name on tablename(colume_name)；

使用索引需要注意：

1)在使用like关键字进行查询的语句中，如果匹配字符串的第一个字符为“％”，索引不会起作用，只有“％”不在第一个位置，索引才会起作用。

2)在mySQL中可以为多个字段创建索引，一个索引可以包含16个字段，对于多列索引，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

3)查询语句的查询条件中只有or关键字，且or前后的两个条件中的列都是索引时，查询中才使用索引，否则，查询将不适用索引。

优化数据库结构：一个好的数据库设计方案会对于数据库的性能常常会起到事半功倍的效果，合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够是查询速度更快，数据库的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等方面内容。

将字段很多的表分解成多个表，对于字段很多的表，如果某些字段使用的频率很低，当数据量很大时，会由于使用频率低的字段的存在而变慢，可以将这些字段分离出来形成新表。

增加中间表，对于需要经常联合查询的表，可以建立中间表以提高查询效率，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，一次来提高查询效率。

增加冗余字段，设计数据库表时，应尽量遵循范式理论的规约，尽可能减少冗余字段，但是，合理的加入冗余字段可以提高查询速度。

优化插入记录的速度，插入记录的时候，影响插入速度的主要是索引、唯一性校验、一次性插入记录条数等，根据这些情况，可以分别进行优化。

优化数据库服务器：主要包括硬件进行优化和对数据库服务的参数进行优化。

三、查找异常查询模块

查找异常查询模块，根据数据库查询报告查找占用资源异常高的SQL，是在系统的使用过程中和数据库的交互过程中会记录下一些查询信息，根据这些信息去定位异常脚本。

经常用到的是借助数据库使用的AWR报告，它是一种性能收集和分析工具，它能够提供一个时间段内整个系统资源使用情况的报告。

步骤如下：

1)用系统管理员的权限登录；

2)运行脚本产生报告；

3)输入报告的类型(html、text)；

4)输入要收集几天的AWR报告信息；

5)根据列出的快照号和快照时间，选择需要分析的快照信息，包括开始和结束；

6)输入生成报告的文件名称；

7)退出，在相应的目录下查看、分析报告。

分析报告中的项目，例如在mainreport中查看SQL Statistics，该项下有SQL执行次数和时间的排序，根据SQL id和SQL text定位对应的语句，分析问题。

四、优化模块

优化模块是指在常规数据库优化手段的基础上，分析耗时比较严重的SQL，进而进行调优，将耗时、耗资源的SQL从业务、语法的角度上进行调整、优化，降低耗时，减少资源的占用，提高系统的反应速度。

在本发明的具体实施例中，本部分以所在地产行业通过优化优化耗时、耗资源的SQL来说明本发明的实现例子。

在某地产数据实施案例中，通过业务调研，识别出客户数据信息，部分数据如下表所示：

在得到客户数据表后，根据本发明的方法，主要实施步骤如图2所示。

一、数据库初始化模块

这六部分的初始化文件都是.sql脚本，包括删除旧表、增加新表、增加索引、插入数据等操作，直接按照顺序导入执行即可。具体脚本很多，此处不做具体说明，只引入部分脚本说明，如下：

删除可能存在的表：

DROP TABLE TSC_MONITOR_RES；

创建新表：

CREATE TABLE TSC_MONITOR_RES(

C_ID VARCHAR2(255BYTE)NOT NULL，

C_IP VARCHAR2(255BYTE)NULL，

C_DATE VARCHAR2(255BYTE)NULL，

C_TOTALMEMORY VARCHAR2(255BYTE)NULL，

C_FREEMEMORY VARCHAR2(255BYTE)NULL，

C_MAXMEMORY VARCHAR2(255BYTE)NULL，

C_OSNAME VARCHAR2(255BYTE)NULL，

C_TOTALMEMORYSIZE VARCHAR2(255BYTE)NULL，

C_FREEMEMORYSIZE VARCHAR2(255BYTE)NULL，

C_USEDMEMORY VARCHAR2(255BYTE)NULL，

C_TOTALTHREAD VARCHAR2(255BYTE)NULL，

C_CPUUSAGE VARCHAR2(255BYTE)NULL，

C_DISKUSAGE VARCHAR2(4000BYTE)NULL，

C_SERVERTYPE VARCHAR2(255BYTE)NULL，

C_SERVERSTATUS VARCHAR2(255BYTE)NULL，

C_DESCRIPTION VARCHAR2(255BYTE)NULL

)

LOGGING

NOCOMPRESS

NOCACHE；

创建唯一索引：

CREATE UNIQUE INDEX SYS_C0010056

ON TSC_MONITOR_RES(C_ID ASC)

LOGGING

VISIBLE；

增加值约束：

ALTER TABLE TSC_MONITOR_RES ADD CHECK(C_ID IS NOT NULL)；

创建主键：

ALTER TABLE TSC_MONITOR_RES ADD PRIMARY KEY(C_ID)；

插入数据：

insert into TSC_MONITOR_RES values(“，”，…)；

二、常规方法优化模块

优化查询的语法如下：

explain plan for select*from TSC_MONITOR_RES；

select*from table(dbms_xplan.display)；

结果如下：

Plan hash value：2324000091

|0|SELECT STATEMENT||1|3937|2(0)|00：00：01|

|1|TABLE ACCESS FULL|TSC_MONITOR_RES|1|3937|2(0)|00：00：01|

查看表结构：Describe TSC_MONITOR_RES；

使用索引的语法如下：

CREATE UNIQUE INDEX SYS_C0010056ON TSC_MONITOR_RES(C_ID ASC)

在本项目实施中数据库结构已经固定，这里不做改动按照之前产品设计的优化方法使用。

在本项目实施中数据库服务器客户已经给定，使用按照客户要求的硬件和数据库服务的参数。

三、根据数据库查询报告查找占用资源异常高SQL模块

步骤如下：

1)用系统管理员的权限登录；

SQLplus/as sysdba

2)运行脚本产生报告；

@？/rdbms/admin/AWRrpt.sql

3)输入报告的类型(html、text)；

Enter value for report_type：html

4)输入要收集几天的AWR报告信息；

Enter value for num_days：1

Enter value for begin_snap：Snap Id1

Enter value for end_snap：Snap Id2

5)输入生成报告的文件名称；

2019073011.html

6)退出，在相应的目录下查看、分析报告。

分析报告中的项目，例如在mainreport中查看SQL Statistics，该项下有SQL执行次数和时间的排序，根据SQL id和SQLtext定位对应的语句，分析问题。

报告主要结果如下：

SQL ordered by Elapsed Time：

结果分析：在半小时里，执行次数最多的SQL多达百万次，根据SQL id＝’9t6f3ntrz6rf3’查找对应的SQL语句，分析对应的业务功能，得出是在监控硬件信息的模块功能中出现的问题。

四、优化模块

根据定位的业务功能，产品的功能代码做如下调整：

伪代码1：

修改后的伪代码2：

在不影响产品功能、用户使用的前提下，在满足产品安装环境本身的条件下，从业务上调整后，重复模块三中的步骤，并且设置的条件跟步骤三一致，得出的报告如下：

SQL ordered by Elapsed Time

结果分析：在半小时里，通过优化后的数据库查询信息，SQL id＝‘9t6f3ntrz6rf3’查询对应的SQL语句已经不在该排序中，达到了优化的目的，减少了资源消耗，提高系统的相应速度。

在此说明书中，本发明已参照其特定的实施例作了描述。但是，很显然仍可以作出各种修改和变换而不背离本发明的精神和范围。因此，说明书和附图应被认为是说明性的而非限制性的。

Claims

1.一种针对数据共享平台实现数据库操作效率优化功能的系统，其特征在于，所述的系统包括：

初始化模块，用于创建数据库中的表以及插入数据；

2.根据权利要求1所述的针对数据共享平台实现数据库操作效率优化功能的系统，其特征在于，所述的初始化模块插入的数据包括初始化的用户登录名和密码、目录信息、功能信息和页面数据。

3.根据权利要求1所述的针对数据共享平台实现数据库操作效率优化功能的系统，其特征在于，所述的初始化模块中的数据库表信息包括分布式环境下的微服务应用平台、数据服务共享平台、数据集成平台、企业间服务整合单元、元数据资产整合单元、补充信息单元。

4.根据权利要求1所述的针对数据共享平台实现数据库操作效率优化功能的系统，其特征在于，所述的常规优化模块包括：

5.根据权利要求1所述的针对数据共享平台实现数据库操作效率优化功能的系统，其特征在于，所述的查找异常查询模块使用的数据库报告为AWR报告。

6.一种基于权利要求1所述的系统实现针对数据共享平台的数据库操作效率优化处理的方法，其特征在于，所述的方法包括以下步骤：

(4)优化模块分析耗时严重的SQL，并进行调整和优化。

7.根据权利要求3所述的实现针对数据共享平台的数据库操作效率优化处理的方法，其特征在于，所述的步骤(3)具体包括以下步骤：

(3.1)运行脚本产生报告；

(3.2)输入报告的类型以及收集AWR报告信息的持续时间；

(3.3)根据的快照号和快照时间，选择需要分析的快照信息；

(3.4)输入生成报告的文件名称；

(3.5)在相应的目录下查看和分析报告。

8.根据权利要求1所述的实现针对数据共享平台的数据库操作效率优化处理的方法，其特征在于，所述的步骤(3.5)中通过数据库的AWR报告来分析报告。