一种数据库多表联合查询的方法

摘要

一种数据库多表联合查询的方法包括：根据查询需求，确定关系型数据库中与业务逻辑相关联的多个表；连接数据库，获取所述多个表的信息；根据外键信息将多个表关联成一张物化视图表；处理物化视图表中的字段，生成组合键，并将其设置为新建列式数据表的行键；建立关系型数据库到列式数据表的映射关系，将映射关系及物化视图表中的数据记录写入XML文件；将物化视图表中的数据映射成列式数据表中的行键和列标识符，数据记录写入到列式数据表中；在所述列式数据表中进行多表查询，获取查询结果。该方法能够在海量数据的情况下快速、高效地完成多表联合查询。

权利要求

1.一种数据库多表联合查询的方法，其特征在于，包括以下步骤：

步骤一，根据查询需求，确定关系型数据库中与业务逻辑相关联的多个表；

步骤二，连接数据库，并获取所述多个表的信息；

步骤三，根据所述多个表的外键信息，将所述多个表关联成一张物化视图表；

步骤四，按照查询需求的要求对所述物化视图表中的字段进行处理，生成组合键；

步骤五，新建一列式数据表，将所述组合键设置为所述列式数据表中的行键；

步骤六，建立从所述关系型数据库的字段到所述列式数据表的列标识符的映射关系，并将所述映射关系以及所述物化视图表中的数据记录写入XML文件中；

步骤七，根据存储于XML文件中的所述映射关系，将物化视图表中的组合键和字段映射成列式数据表中的行键和列标识符，并将物化视图表中的数据记录写入到列式数据表中；

步骤八，接收多表查询请求，在所述列式数据表中进行查询，获取查询结果。

2.根据权利要求1所述的数据库多表联合查询的方法，其特征在于，还包括在服务端设置监听器，用于对所述关系型数据库的更新操作进行监听，一旦监听到数据库中产生数据更新，便在所述物化视图表中进行数据同步。

3.根据权利要求2所述的数据库多表联合查询的方法，其特征在于，所述监听器还对物化视图表的更新操作进行监听，一旦监听到所述物化视图表中产生数据更新，便扫描所述物化视图表中并查找出新增数据记录，对所述新增数据记录重复步骤六和步骤七。

4.根据权利要求1或3所述的数据库多表联合查询的方法，其特征在于，步骤四还包括在所述物化视图表中添加一标记位，该标记位用于标记数据记录是否写入至列式数据表中，初始默认值为0。

5.根据权利要求4所述的数据库多表联合查询的方法，其特征在于，根据步骤七的返回结果修改物化视图表中的标记位，如果返回结果中显示物化视图表中的数据记录成功写入列式数据表，则将所述标记位由0改为1，如果返回结果中显示物化视图表中的数据记录未能写入列式数据表，则所述标记位保持默认值0。

6.根据权利要求5所述的数据库多表联合查询的方法，其特征在于，在步骤七中将物化视图表中标记位为0的数据记录写入到列式数据表中。

7.根据权利要求3所述的数据库多表联合查询的方法，其特征在于，按照给定周期对所述新增数据记录重复步骤六和步骤七。

8.根据权利要求7所述的数据库多表联合查询的方法，其特征在于，所述周期可按实际需要进行设置。

说明书

一种数据库多表联合查询的方法

技术领域

[0001] 本发明涉及数据库技术领域，尤其涉及一种数据库多表联合查询的方法。

背景技术

[0002] 数据库是长期储存在计算机内、有组织的、可共享的数据集合，数据库中的数据以一定的数据模型组织、描述和储存在一起，用户可以在一定范围内对数据库中的数据进行查询、检索和处理等操作。根据查询要求从一个计算机文件或数据库中提取所需要的数据的技术，这是数据处理的基本技术之一，称之为数据查询技术。对于关系型数据库而言，数据查询的方法有很多种，如简单结构化查询语言查询、模糊查询、对象化数据模型查询、多表联合查询等。其中，多表联合查询是非常常用且重要的查询方法，在数据库管理中可通过连接运算符来实现。

[0003] 在关系数据库管理系统中，表建立时各数据之间的关系可以是不确定的，通常把一个实体的所有信息存放在一张表中。在检索数据时，通过连表操作查询出存放于多个表中的不同实体的信息。这种多表联合查询的方法给数据查询操作带来很大的灵活性，用户可以在任何时候增加新的数据类型，只需为不同实体创建新的表，而不必对数据库进行额外的操作。

[0004] 然而，随着信息系统的不断普及和深入应用，所产生的业务逻辑数据信息量呈现爆炸性增长，数据之间的耦合度也变得越来越高。在关系型数据库中表现为关系表的数量不断增多，数据表之间的关联关系变得更加复杂。此外，单张表中数据量的也不断增加，采用多表联合查询的方式对数据进行检索时，计算机处理速度明显下降，数据查询效率非常低。

[0005] 针对多表联合查询存在的查询效率低的问题，目前主要依靠增加索引的方式来提高查询效率，由于不同类型的索引具有不适合在重复率低的字段上建立或者占用大量空间等方面的缺陷，因此如果涉及的多个表的数据量都非常大且重复率高时，索引没建好的话执行效率也不敢恭维。

发明内容

[0006] 本发明要解决是数据库多表联合查询速度慢的技术问题，提供一种新的多表查询方法，以显著地提高多表联合查询的处理速度，提升查询效率。

[0007] 本发明提供一种数据库多表联合查询的方法，其特征在于，包括以下步骤：

[0008] 步骤一，根据查询需求，确定关系型数据库中与业务逻辑相关联的多个表；

[0009] 步骤二，连接数据库，并获取所述多个表的信息；

[0010] 步骤三，根据所述多个表的外键信息，将所述多个表关联成一张物化视图表；

[0011] 步骤四，按照查询需求的要求对所述物化视图表中的字段进行处理，生成组合键；

[0012] 步骤五，新建一列式数据表，将所述组合键设置为所述列式数据表中的行键；

[0013] 步骤六，建立从所述关系型数据库的字段到所述列式数据表的列标识符的映射关系，并将所述映射关系以及所述物化视图表中的数据记录写入XML文件中；

[0014] 步骤七，根据存储于XML文件中的所述映射关系，将物化视图表中的组合键和字段映射成列式数据表中的行键和列标识符，并将物化视图表中的数据记录写入到列式数据表中；

[0015] 步骤八，接收多表查询请求，在所述列式数据表中进行查询，获取查询结果。

[0016] 优选地，所述的数据库多表联合查询的方法，还包括在服务端设置监听器，用于对所述关系型数据库的更新操作进行监听，一旦监听到数据库中产生数据更新，便在所述物化视图表中进行数据同步。

[0017] 优选地，所述监听器还对物化视图表的更新操作进行监听，一旦监听到所述物化视图表中产生数据更新，便扫描所述物化视图表中并查找出新增数据记录，对所述新增数据记录重复步骤六和步骤七。

[0018] 优选地，步骤四还包括在所述物化视图表中添加一标记位，该标记位用于标记数据记录是否写入至列式数据表中，初始默认值为0。

[0019] 优选地，根据步骤七的返回结果修改物化视图表中的标记位，如果返回结果中显示物化视图表中的数据记录成功写入列式数据表，则将所述标记位由0改为1，如果返回结果中显示物化视图表中的数据记录未能写入列式数据表，则所述标记位保持默认值0。

[0020] 优选地，在步骤七中将物化视图表中标记位为0的数据记录写入到列式数据表中。

[0021] 优选地，可以按照给定周期对所述新增数据记录重复步骤六和步骤七。

[0022] 优选地，所述周期可按实际需要进行设置，比如周期可设置为600s，也就是说，每600s对物化视图表中的增量数据进行一次写入操作，将新增数据记录写入到列式数据表。

[0023] 相比现有技术方案，本发明的有益效果在于，在数据表数量极大增长，数据表之间的关联关系愈发复杂的情况下，本发明的方案通过建立列式数据库，将关系型数据库中的数据记录迁移至列式数据库中再进行多表联合查询，使得查询的速度和效率都得到很大的提升。

附图说明

[0024] 通过结合附图的以下详细描述，本发明的上述及其他目的、特征和优点将变得更为明显。在附图中：

[0025] 图1为本发明数据库多表联合查询方法一个实施例的流程示意图；

[0026] 图2为不同的多表查询方法的效率对比曲线图。

具体实施方式

[0027] 下面结合附图和实施例对本发明作进一步说明。

[0028] 图1是本发明数据库多表联合查询方法一个实施例的流程示意图，根据实施例，并以传感设备的元数据信息表和网关信息表在关系型数据库MySQL中的设计为例来说明本发明的技术方案，本发明提供的数据库多表联合查询方法包括如下步骤：

[0029] 步骤一，根据查询需求，将MySQL中具有关联关系的表进行分组，确定MySQL中具有关联关系的元数据信息表sensorInfo和网关信息表gatewayInfo。

[0030] 步骤二，连接MySQL数据库，获取表sensorInfo(见表1)和表gatewayInfo(见表2)的信息。根据数据库查询请求中的字段以及表的信息构造为多条针对表sensorInfo和表gatewayInfo的结构化查询语句SQL，将所述SQL语句存入内存中。

[0031] 表1元数据信息表sensorInfo

[0032]列名 数据类型 默认值 备注
sensor_id Int(11) Not Null 主键、传感器编号(流水号)
gateway_id Int(11) Null 外键
type Int(11) 0 传感器类型
rate Int(11) 600 采样频率(单位：秒)
location Varchar(255) Null 位置

[0033] 表2网关信息表gatewayInfo

[0034]

[0035]

[0036] 步骤三，根据外键信息(此处为表sensorInfo中的字段gateway_id)，采用连接运算符LEFT JOIN来连接表sensorInfo和表gatewayInfo，构造出联表的SQL语句。执行SQL语句，生成一张单表，即物化视图表rel_sen_gate。

[0037] 步骤四，对所述物化视图表中的字段进行处理。具体地，通过将两个字段进行合并的方式生成组合键CombiKey，为后续构造列式数据表的行键做准备。同时，在物化视图表rel_sen_gate中添加新字段Flag，该字段是为了标记数据写入列式数据表成功与否。Flag初始默认值为0，表示该条记录未写入列式数据表，当数据成功写入列式数据表，则该字段内容将会更新为1。生成的物化视图表如表3所示。

[0038] 表3物化视图表rel_sen_gate

[0039] 列名 数据类型 默认值 备注sensor_id Int(11) Not Null 主键、传感器编号(流水号)
sen_gate Varchar(255) Null 组合键(gateway_id+sensor_id)
gateway_id Int(11) Null 网关编号(流水号)
type Int(11) 0 传感器类型
rate Int(11) 600 采样频率(单位：秒)
location Varchar(255) Null 位置
owner Varchar(64) Null 所属用户
gateway_ip Varchar(32) Null 网关IP
Flag Int(2) 0 标记位

[0040] 步骤五，在列式数据库HBase中新建一数据表Hrel_sen_gate，将步骤四中生成的组合键设置为列式数据表Hrel_sen_gate的行键RowKey。

[0041] 步骤六，建立从MySQL数据库的字段到列式数据表Hrel_sen_gate的列标识符的映射关系，并将所述映射关系以及物化视图表中的数据记录写入XML文件。

[0042] 这里采用HashMap来存储描述映射关系的XML文件，每个XML文件用于描述两个特定数据库之间字段的映射关系。HashMap具有属性值KEY和VALUE，KEY值中存储的是字符串，用于标识字段映射的两个数据库名、表名或者字段名，例如：KEY值为MySQLTOHBase，表示进行映射的两个数据库是MySQL和HBase，VALUE值存储的是XML文件的保存位置；KEY值为rel_sen_gateToHrel_sen_gate，表示进行映射的两个表是表rel_sen_gate和表Hrel_sen_gate，VALUE值存储的是将要进行同步的数据记录数；KEY值为CombiKeyToRowKey，表示进行映射的两个字段是字段CombiKey和字段RowKey，VALUE值存储的是该字段的数据记录；其他字段映射方式与上述方式类似。

[0043] 需要注意的是，步骤一所述的每个分组都会生成相应的物化视图表，并且在列式数据库HBase中都有一个数据表与其对应，物化视图表与列式数据表的映射关系也在XML文件中体现。

[0044] 步骤七，根据存储于XML文件中的所述映射关系，将物化视图表中的组合键和字段映射成列式数据表中的行键和列标识符，并将物化视图表中的数据记录写入到列式数据表中。

[0045] 具体地，连接列式数据库HBase，找到HBase数据库对应的XML文件，然后对XML文件进行解析。根据解析结果，将物化视图表rel_sen_gate中的组合键CombiKey和字段映射成列式数据表中的行键RowKey和列标识符，将表rel_sen_gate中各个字段的内容映射到列式数据表Hrel_sen_gate中的列标识符内容。完成映射操作之后的列式数据表如表4所示。

[0046] 表4列式数据表Hrel_sen_gate

[0047]

[0048] 根据本发明的技术方案，在服务端还设置有监听器，用于对MySQL数据库和物化视图表rel_sen_gate的更新操作进行监听，一旦监听到MySQL中产生数据更新，便在相应的物化视图表rel_sen_gate中进行数据同步(附图1中的数据同步a)。

[0049] 由于所述监听器还对物化视图表的更新操作进行监听，一旦监听到所述物化视图表rel_sen_gate中产生数据更新，便扫描所述物化视图表中并查找出新增数据记录，按照设定周期对所述新增数据记录重复步骤六和步骤七，即，将新增数据记录以及相对应的映射关系同步到XML文件中，并将所述新增数据根据映射关系同步至列式数据库HBase的数据表中，这个过程也称之为数据同步，也就是附图1中的数据同步b。

[0050] 此外，将物化视图表中产生的增量数据同步至列式数据库中的同时，数据写入结果也将反馈给服务器，服务器则依据该数据写入结果，对物化视图表rel_sen_gate的Flag字段内容进行更新。如果返回结果中显示数据写入成功，则将Flag字段内容由0改为1；如果返回结果中显示数据写入失败，则Flag字段内容保持默认值0不改变。在下一个数据写入周期，对Flag字段内容为1的数据记录重新进行同步，直至Flag变为1为止，这样能够保证数据同步的可靠性。

[0051] 步骤八，接收多表查询请求，在所述列式数据表中进行查询，获取查询结果。具体地，实时监测终端发来的数据库多表联合查询的请求，当接收到终端发来数据库多表联合查询的请求后，服务端对该请求进行解析，并访问XML文件，查询该多表联合请求在HBase数据库中所对应的数据表。然后连接HBase数据库，根据查询条件查询表，获取查询结果并返回给客户端。这种查询方式既保证了业务逻辑数据之间的关联关系，又避免了关系型数据库的联表查询，因此查询效率有了很大的提升。

[0052] 值得注意的是，在步骤四过程中，并没有直接将数据从MySQL单表导入到HBase数据表中，而是设计了物化视图表作为中间表进行过渡。这样设计的目的主要为了提高数据写入的效率。如果没有物化视图单表，那么在数据写入过程中，需要先对MySQL中的关联关系表sensorInfo和gatewayInfo都进行扫描，并进行连接操作，然后取出数据记录并对其进行格式转换，才能写入到列式数据表中。当数据量很大时，扫描多个表与联表操作都会变得很耗时，从而导致整个系统数据写入效率低下。

[0053] 此外，本发明还在服务器端设置有监听器，用于数据同步处理。即，当客户端对表sensorInfo或表gatewayInfo每进行一次操作，后台都将表sensorInfo与表gatewayInfo产生的增量数据同步到物化视图表rel_sen_gate中。在将物化视图单表rel_sen_gate中的数据写入到列式数据库中时，只需获取表rel_sen_gate中的增量数据即可，省去了多表查询和联表查询的时间。

[0054] 采用多线程并发访问的方式对本发明多表联合查询方法的效率进行了验证。客户端和服务端分别部署在HBase集群所在局域网内的两台普通PC机上。客户端采用多线程方式模拟用户执行并发查询操作。服务端处理用户查询请求并完成数据MySQL与HBase之间的同步。

[0055] 通过对比MySQL多表联合联表查询用时、物化视图单表查询用时和HBase列式数据表查询用时，来说明本发明的多表查询方法的高效性。

[0056] 在不同数据规模的情况下，不同查询方式返回特定记录所用时间的对比结果如表5所示。图2是不同的多表查询方法的效率对比曲线图，参考附图2，则能更直观地看到对比结果。

[0057] 表5不同查询方法检索用时对比结果(时间单位：s)

[0058]记录数(万条) MySQL多表联合 MySQL物化视图表 HBase列式数据表
10 5.217 2.076 9.998
30 8.338 4.596 10.119
60 15.669 1.0661 11.076
100 23.362 14.029 12.224
150 46.775 25.447 14.816
200 95.614 50.031 16.292
300 203.191 96.543 18.247

[0059] 由表5和图2可知，无论记录数的规模有多大，通过MySQL多表联合方式进行数据查询都是最耗时的。随着被检索表中记录数量的增加，当记录数小于60万条时，MySQL物化视图单表查询用时低于HBase列式数据表查询用时；但当记录数从100万递增到300万条时，MySQL物化视图单表查询用时迅速增大，查询效率明显低于HBase列式数据表，而且差异越来越明显。虽然表中记录数在不断增加，但是列式数据表查询时间增长趋势缓慢，检索性能高且稳定，由此可见，列式数据表能够很好的处理海量数据的查询请求。

[0060] 因此，将关系型数据库中的关联关系表进行处理并将数据同步至列式数据库中为客户端提供检索服务的方法，能够极大地提高系统的检索访问效率，也就是说，本发明的多表查询方法的效率高于常规的多表查询方法。

[0061] 可以理解的是，本公开不限于上述特定的实施方式，在不背离本公开精神及实质的情况下，本领域的技术人员可以根据本公开作出各种相应的修改和变形，并且对公开实施方式的修改、公开实施方式的特征的组合以及其它实施方式都意图被包含在所附权利要求限定的范围内。