CN112231321A - 一种Oracle二级索引及索引实时同步方法 - Google Patents

Abstract

本发明提供了一种Oracle二级索引及索引实时同步方法，包括关系型数据库存储方式下海量数据的SQL查询解析优化和二级索引技术，以及二级索引实时同步方法，构建基于Oracle关系型数据库的海量数据的高效索引查询引擎，提供类似Oracle的关系型数据库的二级索引实时同步和高效检索服务。本发明减少随机查询带来的大量网络开销，提高二级索引的可扩展性和实用性；针对Oracle数据库含大字段类型的海量数据多表联合查询能力若得问题，优化SQL查询语句，设计高效的二级索引机制，构建分布式二级索引引擎。本方法同样适用于其他关系型数据库(如Mysql)高效二级索引与索引实时同步引擎构建。

Description

一种Oracle二级索引及索引实时同步方法

技术领域

本发明涉及大数据技术领域，尤其涉及一种Oracle二级索引及索引实时同步方法。

背景技术

大数据时代，数据量的爆炸式增长，对传统关系型数据库Oracle产生可巨大的影响。尤其是借助自然语言处理技术，巨量结构化信息的产生对Oracle数据存储访问压力空前大。在数据量不是特别的大(亿级)的情况下，Oracle数据库处理速度非常快，而且其安全级别高，支持快闪以及完善的恢复，即使硬件坏了，也可以恢复到故障前1s。因此Oracle数据库仍是当下许多行业，如金融、银行、军工，优先选择数据库之一。

但是Oracle数据库存在如下缺陷：数据量过大，数据中什么情况都可能存在；软硬件要求高，系统资源占用率高；要求数据库操控人员有很高的处理方法和技巧。当表中的数据量不断增大，越来越多的大字段数据产生，数据查询速度，尤其是多表联合查询速度必然会越来越慢，进而影响上层应用程序性能。如何借助先下流行的分布式技术、全文检索技术，让Oracle存的更多跑的更快是当下要解决的问题。

发明内容

发明目的：发明目的：针对上述Oracle数据库技术的不足，本发明旨在提设计一种基于Oracle数据库的二级索引，利用全文索引技术为关系型数据库列值建立索引系统，并通过Logstash技术和Oracle触发器技术实现索引表的高效构建、更新、删除，实现关系型数据库以及二级索引系统之间的索引实时同步，打破关系型数据库自身对海量多维数据，尤其是含有大字段类型列值数据的管理与访问开销大等缺陷；支持二级索引的动态列扩展和列删除，减少随机查询带来的大量网络开销，提高二级索引的可扩展性和实用性；针对关系型数据库海量大字段类型SQL操作能力弱和多表联合查询效率低的问题，优化数据库SQL查询，设计二级索引查询机制，构建分布式二级索引引擎。本发明方法同样适用Mysql关系型数据库。

技术方案：本发明提供的一种基于Elastcisearch的Oracle二级索引及索引实时同步方法，包括如下步骤：

步骤1，为Oracle数据库查询数据列项建立全文检索；

步骤2，设计基于全文检索引擎的标准SQL多表联合查询方案；

步骤3，为Oracle数据库构建二级索引，设计二级索引自动增删改的方法，实现Oracle数据库与二级索引数据的实时同步。

进一步的，步骤1包括：利用Lucene(Lucene是apache软件基金会4jakarta项目组的一个子项目，是一个开放源代码的全文检索引擎工具包)为Oracle数据库的查询条件列族建立全文检索。

进一步的，步骤2中，基于Elastcisearch的Oracle关系数据库二级索引查询方法，通过二级索引嵌套结构设计实现上层Oracle多表联合查询；通过规则机制，设计标准SQL解析器。通过唯一标示字段实现Oracle原始数据表和索引表关联，通过高效索引检索出符合条件表项，再通过唯一标示字段反查Oracle数据库，实现精准查询结果快速定位反馈。本方法打破Oracle自身大字段和海量数据查询效率低的缺陷。步骤2具体包括如下步骤：

步骤2-1，根据上层应用多表联合查询要求，采用嵌套结构设计二级索引表，设计一种基于标准SQL的多表联合查询等复杂SQL查询的二级索引检索方法(SQL On Second-Indexing)；

步骤2-2，优化SQL查询语句，避免使用如使用NOT、“*”、Where条件过滤时在查询列上使用计算、使用通配符％等情况，降低SQL语句本身复杂度；

步骤2-3，通过规则构造方式，将标准SQL解析成Elasticsearch查询语句；

步骤2-4，设计基于Elastcisearch的Oracle关系数据库二级索引数据查询流程；

进一步的，步骤3中，设计Oracle数据表数据增、删、改操作监控机制，设计触发器，为数据索引实时更新操作提供触发机制；并设计基于日志方式的保证Oracle数据库原始数据和二级索引一致性方法，解决由于多客户端并发操作Oracle数据库造成的索引与原始数据不一致性问题。步骤3包括：设计索引监控表IndexLog表，表结构为(表名、SYS_GUID、请求类别)，具体步骤如下：

(1)设置Oracle触发器：当Oracle数据库有数据增加、修改、删除操作的时候，Oracle触发器将变化的表和字段信息写入IndexLog；

(2)Logstash设置定时机制，时间设置按需求进行配置，Logstash监测IndexLog表，当发现表不为空时将IndexLog表内容写入IndexLogBak表，同时删除IndexLog表中的相关条目；

(3)根据IndexLogBak表内容，如果是新增或修改需求，根据表名和SYS_GUID从Oracle中读取原始字段信息，为带_idx前缀的字段建索引；如果是删除需求，直接根据表名和SYS_GUID信息删除索引表中相关的索引数据。

(4)当IndexLogBak表中每一条记录都操作完毕，清空IndexLogBak表。

步骤3中，设计二级索引类型List的secondIndexs变量，该变量以JSON的形式保存表中创建的二级索引列，如存在多表联合查询情况，索引构建的时候通过Elasticsearch套接字方法，即nested和parent-child，使Oracle二级检索支持多表关联查询；在索引阶段，ElasticSearch只需向Oracle返回满足条件的SYS_GUID集合。

步骤3中，在数据查询时通过JDBC提供的API在ElasticSearch中进行多条件的复杂查询，并得到查询结果；ElasticSearch中的列能够选择是否存储，如果查询条件中需要的结果已经存储在ElasticSearch中，则直接向用户返回其需要的结果；如果没有存储，则会向用户返回该列值对应的SYS_GUID，用户获取到满足条件的SYS_GUID集合后，再根据SYS_GUID到Oracle数据库中将完整的数据返回。

步骤3还包括，将标准SQL转成Elasticsearch可执行语句，通过映射规则和SQL元数据字段描述，解析SQL语句，转成Elasticsearch能直接检索的语句；

所述映射规则包括：过滤规则、排序规则和聚合规则，其中，排序规则为一个或多个字段的升序或降序；

聚合规则为实现SQL聚合函数至Elasticsearch聚合API的映射；

过滤规则具体为：将SQL语法树中第一符号翻译为Elasticsearch中的rangeQuery；在SQL的模式匹配中，如果模式串不以第二符号开头，则翻译为Elasticsearch中的prefixQuery，否则翻译为Elasticsearch中的wildcardQuery；将where子句中的和以及或逻辑，翻译为Elasticsearch中的boolQuery；所述第一符号为>、>＝、<或<＝；第二符号为_或％。

步骤3还包括，建立接口模块，接口模块用于提供Jave、Python、Restful外部接口。

有益效果：由于历史原因，有的系统数据依旧存在Oracle中，信息时代传感器增多数据量激增，因此增加了前端和其他功能模块的数据访问时间，严重影响了用户体验。本发明提出一种方法，为关系型数据库Oracle构建二级索引，千万级数据访问由不建立二级索引秒级响应，提升之毫秒级响应；并且设计了一种二级索引自动增删改的方法，实现了Oracle数据库与二级索引数据的实时同步，大大提升了数据访问效率的同时也保证了数据访问的一致性。

附图说明

下面结合附图和具体实施方式对本发明做更进一步的具体说明，本发明的上述和/或其他方面的优点将会变得更加清楚。

图1为本发明基于Elasticsearch的Oracle数据库二级索引(ESIOnOracle)方法的总体框图。

图2为本发明基于Elasticsearch的Oracle数据库二级索引设计图。

图3为本发明标准SQL解析器设计示意图。

图4为本发明基于Elasticsearch的Oracle数据库二级索引SQL查询流程设计示意图。

图5为本发明基于Elasticsearch的Oracle数据库二级索引实时同步方法设计示意图。

具体实施方式

本发明基于大数据开源软件，针对结构化数据，基于列数据的高效索引技术，设计基于关系型数据库的二级索引方法，利用二级索引技术为关系型数据库Oracle的条件查询列值建立索引系统，并通过Oracle触发器和Elasticsearch的Lagstash技术实现索引表的高效自动安全创建、修改、删除，打破Oracle自身海量数据存储以及复杂查询的缺陷。本发明方法支持二级索引的动态实时增加或减少和删除，减少随机查询带来的大量网络开销，提高二级索引的可扩展性和实用性。另外，本发明针对多表联合查询、大字段等复杂SQL查询效率低问题设计SQL解析器和执行器，优化SQL查询，构建二级检索SQL查询引擎，实现高效的并行SQL查询。

一种基于Elasticsearch的Oracle二级索引方法，包括二级索引构建方法模块、复杂SQL查询解析模块、索引实时同步更新模块以及接口模块。

Oracle二级索引方法模块在用户设计二级索引的时候需要考虑为Oracle表中的哪些列建立二级索引，通常情况下，应结合上层数据查询需求为条件查询列构建全文索引。为了方便识别为哪些列构建了二级索引，Oracle数据表中该列名以“idx_”开始。同时为了减少数据查询获取二级索引结果之后原始数据查询的时间开销和空间开销，本发明考虑构建索引时应包含Oracle数据表唯一标示ID(SYS_GUID)，用于唯一标识Oracle数据表中的一行记录，此时Oracle一行记录即为ElasticSearch中的一行。本方案设计扩展的Oracle数据库创建数据表功能，创建关系型数据表的同时创建索引表，设计二级索引类型List<SYS_GUID，index1，index2，index3…>的secondIndexs变量，该变量以JSON的形式保存表中创建的二级索引列(Key)，并提供了对应的putIndexs()方法、deleteIndexs()、updateIndexs()、clearIndexs()和getIndexs()等方法。在索引表构建的时候，充分考虑查询需求，如存在多表联合查询情况，索引构建的时候通过Elasticsearch套接字方法，即nested和parent-child，使Oracle二级检索支持多表关联查询。在索引阶段，ElasticSearch只需向Oracle返回满足条件的SYS_GUID集合即可。

如图5所示，复杂SQL查询解析模块支持标准SQL查询模块。针对复杂SQL操作能力弱的问题，设计SQL查询解析优化器，并将SQL语句解析成二级索引引擎支持的检索结构。

索引实时同步更新模块通过Oracle触发器和Logstash技术实现二级索引实时增加、删除、更新。考虑到多客户端并发访问同一张Oracle数据表的情况，设计Oracle数据表IndexLog用于记录数据库中哪些表哪些记录变更，IndexLog表字段包含变更的数据库表名(table_name)、数据库记录唯一标示ID(SYS_GUID)、操作类型(增加、修改、删除)。当Oracle数据表变更时通过触发器写IndexLog表，记录变更的数据表和数据项。同时IndexLog数据表设置备份表IndexLogBak，表结构与IndexLog表同。基于Logstash技术的索引实时更新模块，通过读IndexLog表将本次更新的数据表及其数据项写入备份表IndexLogBak，同时将本次处理的数据从IndexLog中删除，更新完二级索引表之后将IndexLogBak表清空。本方案能避免多客户端同时操作同一张Oracle表数据时间差引起的数据不同步问题。

接口模块用于与其他模块交互数据；提供Jave、Python、Restful外部接口，提供数据存储、上层应用以及其他模块可调用接口模块的接口。

图1中，本发明一种基于Elastcisearch的Oracle二级索引及索引实时同步方法总体框图。本方法包括三个模块，分别是二级索引模块、SQL查询解析优化模块、实时索引更新模块。

图2中，本发明基于Elasticsearch的Oracle数据库二级索引设计图。本发明中索引创建功能通过方法CreateInde()实现，针对Oracle数据表中的SYS_GUID而言的，其原理就是针对数据表主键(SYS_GUID)外的某个或者某些字段查询条件列建立的类似于“键-值”对的数据结构，以列的值作为“键”，以该列值对应的SYS_GUID作为“值”，并按“键”的值进行排序，利用B+树或者其他查找效率高的数据结构进行存储，以建立“列值”到SYS_GUID的映射关系。在索引表构建的时候，充分考虑查询需求，如存在多表联合查询情况，索引构建的时候通过Elasticsearch套接字方法，即nested和parent-child，使Oracle二级检索支持多表关联查询。

数据查询时通过JDBC提供的API可以在ElasticSearch中进行多条件的复杂查询，并快速得到查询结果。由于ElasticSearch中的列可以选择是否存储，如果查询条件中需要的结果已经存储在ElasticSearch中，则可以直接向用户返回其需要的结果；如果没有存储，则会向用户返回该列值对应的SYS_GUID，用户获取到满足条件的SYS_GUID集合后，再根据SYS_GUID到Oracle中将完整的数据返回。

图3为本发明基于Elasticsearch的Oracle数据库二级索引SQL查询解析优化设计图，SQL语句解析器分为两部分，首先进行SQL语句本身降复杂度，然后对SQL语句进行解析得到SQL语法树，利用附加规则将所述SQL语法树转换为Elasticsearch可执行的匹配条件和查询执行动作，返回查询结果集。本方案解析SQL语句，完成SQL至Elasticsearch JavaAPI的适配和优化。SQL解析优化模块相当于一个中间件，接收SQL语句作为输入，然后解析、优化、转换为Elasticsearch所能接受的API，查询得到Elasticsearch的数据后，作为结果输出。

具体实现细节如下。

(1)SQL语句降复杂度，其目的旨在将复杂SQL简单化，便于SQL解析。可通过SQL语句优化方法，优化SQL语句降低复杂度，优化方法具体包括以下几个方面：

一：注意WHERE子句中的连接顺序。Oracle本身采用自下而上的顺序解析条件查询，根据这个原理，多表连接必须写在其他Where条件之前，数据类型之类的条件过滤也应该写在其他Where之前。

二：选择(Select)子句返回列处理。分为两种情况，选择语句中带“_idx”前缀的列，和不带返回“_idx”前缀的列。带“_idx”列，直接通过索引表数据返回，不带“_idx”列，通过索引之后反查Oracle返回。当SQL语句中Select部分含有不带“_idx”的返回列值，标记该列，不传入复杂SQL查询解析模块，检索结果返回后通过唯一标示SYS_GUID反查Oracle获取。

三：避免在索引列上使用NOT。

四：避免在索引列上使用计算。

以上四点SQL语句优化方法为关系型数据库SQL语句常用的优化方法，具体使用何种方法或者方法组合可以根据需要完善补充，作为条件输入“SQL语句降复杂度”模块。

(2)标准SQL转成Elasticsearch可执行语句。通过映射规则和SQL元数据字段描述，解析SQL语句，转成Elasticsearch能直接检索的语句。

映射规则包括：过滤规则、排序规则和聚合规则。其中，排序规则为一个或多个字段的升序或降序，聚合规则为实现SQL聚合函数至Elasticsearch聚合API的映射。过滤规则具体为：将SQL语法树中第一符号翻译为Elasticsearch中的rangeQuery；在SQL的模式匹配中，若模式串不以第二符号开头，则翻译为Elasticsearch中的prefixQuery，否则翻译为Elasticsearch中的wildcardQuery；将where子句中的和以及或逻辑，翻译为Elasticsearch中的boolQuery。(所述第一符号为>、>＝、<或<＝；第二符号为_或％。)

图4为本发明基于Elasticsearch的Oracle数据库二级索引SQL查询流程设计。上层应用输入数据检索请求，经过标准SQL解析模块转成Elasticsearch能直接索引的语句，输入二级索引进行检索，将检索出SYS_GUID连同其它检索字段进行查询条件，查Oracle数据库，最后将最终查询结果返回给检索请求层。图4中右边方框中例举了SQL语句解析转换成Elasticsearch语句实例，标准SQL select语句经过复杂SQL查询解析模块解析成Elasticsearch直接检索的GET语句。

图5为本发明基于Elasticsearch的Oracle数据库二级索引实时同步方法设计。设计索引监控表IndexLog表，表结构为(表名、SYS_GUID、请求类别)。具体步骤如下：

(1)当Oracle有数据增加、修改、删除操作的时候，触发器将变化的表和字段信息写入IndexLog。

(2)Logstash设置定时机制，时间设置可按需求对Logstash配置文件进行配置，设置成想要的监测频率，例如：每秒定时监测。Logstash监测IndexLog表，当发现表不为空时将IndexLog表内容写入IndexLogBak表，同时删除IndexLog表中的相关条目。

(3)根据IndexLogBak表内容，如果是新增和修改需求，根据表名和SYS_GUID从Oracle中读取原始字段信息，为带_idx前缀的字段建索引；如果是删除需求，直接根据表名和SYS_GUID信息删除索引表中相关的索引数据。

(4)当IndexLogBak表中每一条记录都操作完毕，清空IndexLogBak表。

本实时同步方法避免了触发器操作与基于Logstash技术的实时监控数据更新操作带来的冗余与数据不一致问题。而且本二级索引表实时更新的方法可同样适用于其他关系型数据库。

在分析了数据多表联合查询、SQL优化解析和二级索引表实时同步更新的问题之后，本发明给出了一种基于Elastcisearch的Oracle二级索引及索引实时同步方法，该方法使用基于Lucene的Elasticsearch开源搜索引擎，为关系型数据库Oracle建立全文倒排表，支持索引数据列的扩展，动态创建索引，索引同步Oracle数据库新增、修改、删除，提供多表联合查询和标准SQL查询方法。本发明方法能弥补Oracle数据库不能根据应用需求动态创建索引提高查询效率的弊端，能极大的提高关系型数据库数据查询的效率。

本发明提供了一种Oracle二级索引及索引实时同步方法，具体实现该技术方案的方法和途径很多，以上所述仅是本发明的优选实施方式，应当指出，对于本技术领域的普通技术人员来说，在不脱离本发明原理的前提下，还可以做出若干改进和润饰，这些改进和润饰也应视为本发明的保护范围。本实施例中未明确的各组成部分均可用现有技术加以实现。

Claims (7)

1.一种Oracle二级索引及索引实时同步方法，其特征在于，包括如下步骤：

步骤1，为Oracle数据库查询数据列项建立全文检索；

步骤2，设计基于全文检索的标准SQL多表联合查询方案；

步骤3，为Oracle数据库构建二级索引，设计二级索引自动增删改的方法，实现Oracle数据库与二级索引数据的实时同步。

2.根据权利要求1所述的方法，其特征在于，步骤1包括：利用Lucene为Oracle数据库的查询条件列族建立全文检索。

3.根据权利要求2所述的方法，其特征在于，步骤3包括：设计索引监控表IndexLog表，表结构为：表名、SYS_GUID、请求类别，具体步骤如下：

(1)设置Oracle触发器：当Oracle数据库有数据增加、修改、删除操作的时候，Oracle触发器将变化的表和字段信息写入IndexLog；

(2)Logstash设置定时机制，时间设置按需求进行配置，Logstash监测IndexLog表，当发现表不为空时将IndexLog表内容写入IndexLogBak表，同时删除IndexLog表中的相关条目；

(3)根据IndexLogBak表内容，如果是新增或修改需求，根据表名和SYS_GUID从Oracle中读取原始字段信息，为带_idx前缀的字段建索引；如果是删除需求，直接根据表名和SYS_GUID信息删除索引表中相关的索引数据；

(4)当IndexLogBak表中每一条记录都操作完毕，清空IndexLogBak表。

4.根据权利要求3所述的方法，其特征在于，步骤3中，设计二级索引类型List的secondIndexs变量，该变量以JSON的形式保存表中创建的二级索引列，如存在多表联合查询情况，索引构建的时候通过Elasticsearch套接字方法，即nested和parent-child，使Oracle二级检索支持多表关联查询；在索引阶段，ElasticSearch只需向Oracle返回满足条件的SYS_GUID集合。

5.根据权利要求4所述的方法，其特征在于，步骤3中，在数据查询时通过JDBC提供的API在ElasticSearch中进行多条件的复杂查询，并得到查询结果；ElasticSearch中的列能够选择是否存储，如果查询条件中需要的结果已经存储在ElasticSearch中，则直接向用户返回其需要的结果；如果没有存储，则会向用户返回该列值对应的SYS_GUID，用户获取到满足条件的SYS_GUID集合后，再根据SYS_GUID到Oracle数据库中将完整的数据返回。

6.根据权利要求5所述的方法，其特征在于，步骤3还包括，将标准SQL转成Elasticsearch可执行语句，通过映射规则和SQL元数据字段描述，解析SQL语句，转成Elasticsearch能直接检索的语句；

所述映射规则包括：过滤规则、排序规则和聚合规则，其中，排序规则为一个或多个字段的升序或降序；

聚合规则为实现SQL聚合函数至Elasticsearch聚合API的映射；

过滤规则具体为：将SQL语法树中第一符号翻译为Elasticsearch中的rangeQuery；在SQL的模式匹配中，如果模式串不以第二符号开头，则翻译为Elasticsearch中的prefixQuery，否则翻译为Elasticsearch中的wildcardQuery；将where子句中的和以及或逻辑，翻译为Elasticsearch中的boolQuery；所述第一符号为>、>＝、<或<＝；第二符号为_或％。

7.根据权利要求6所述的方法，其特征在于，步骤3还包括，建立接口模块，接口模块用于提供Jave、Python、Restful外部接口。

Images