WO2015184762A1 - Database query method and device - Google Patents
Database query method and device Download PDFInfo
- Publication number
- WO2015184762A1 WO2015184762A1 PCT/CN2014/094259 CN2014094259W WO2015184762A1 WO 2015184762 A1 WO2015184762 A1 WO 2015184762A1 CN 2014094259 W CN2014094259 W CN 2014094259W WO 2015184762 A1 WO2015184762 A1 WO 2015184762A1
- Authority
- WO
- WIPO (PCT)
- Prior art keywords
- database
- query
- structured query
- connection
- structured
- Prior art date
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
Definitions
- the present invention relates to the field of computer technologies, and in particular, to a database query method and apparatus.
- the database typically includes one or more database tables for storing data values.
- a user, application, or remote system In order to retrieve and manipulate data in a database, a user, application, or remote system typically issues a database query to the database management system.
- a database query is a collection of statements, commands, or clauses used to retrieve data stored in a database. For example, a query can specify a specific location in the database to retrieve data (for example, a table, row, or column), or specify how to join columns from multiple tables.
- connection query can only use one database connection, for a powerful database server and storage device, only a small amount of processing power can be used; if the number of tables connected to the query is large, each If the amount of table data is large, the query efficiency is very low.
- the embodiment of the invention provides a database query method and device, so as to at least solve the problem of inefficient querying of database queries when querying multiple tables in the related art.
- the present invention provides a database query method, including the following steps:
- the data is obtained from the result set, and the acquired data is spliced at the same time to obtain the query result.
- the receiving the request for the database multi-table join query after constructing the plurality of structured query statements for the single table according to the request, further comprises:
- the receiving the request for the database multi-table join query after constructing the plurality of structured query statements for the single table according to the request, further comprises:
- the step of assigning the structured query statement to the thread corresponding to the database connection includes:
- the structured query statement corresponding to each segment is allocated to the thread corresponding to the database connection according to a preset allocation rule, and after the execution of the structured query statement belonging to the same segment is completed, Then execute the structured query of the next segment.
- the preset allocation rule comprises:
- the unstructured structured query statement is x and the remaining threads are y
- the x/y is rounded to obtain the integer z
- the z structured query statements are assigned to one of the remaining threads; the loop is executed. Until all thread allocation ends.
- the step of allocating a corresponding database connection according to the current database resource, and the connection query for the database comprises:
- the m/n is rounded to obtain the integer a.
- the structured query has b, and the a and b are taken.
- the minimum value c is used to allocate c database connections for the database multi-table join query request, for the database connection query; or
- an embodiment of the present invention further provides a database query device, where the database query device includes:
- Constructing a module configured to receive a request for a database multi-table join query, and construct a plurality of structured query statements for a single table according to the request;
- the database connection allocation module is configured to allocate a corresponding database connection according to the current database resource, and is used for connection query of the database;
- the structured query statement assigning module is configured to allocate the structured query statement to the thread corresponding to the database connection according to a preset allocation rule
- a receiving module configured to receive a result set returned by the thread after executing the structured query statement
- the splicing module is configured to acquire data from the result set, and simultaneously splicing the acquired data to obtain a query result.
- the database querying device further includes:
- the sorting module is arranged to sort the key fields of the structured query according to a preset sorting rule.
- the database querying device further includes:
- a segmentation module configured to segment the range of the key field of the structured query statement according to a preset segmentation rule, and process the structured query statement according to the result of the segmentation, to generate a corresponding segment Structured query statement;
- the structured query statement allocation module is further configured to allocate, according to a segmentation order, the structured query statement corresponding to each segment to a thread corresponding to the database connection according to a preset allocation rule, and belong to the same After the execution of the segmented structured query statement is completed, the structured query of the next segment is executed.
- the preset allocation rule in the structured query statement allocation module includes: when the unallocated structured query statement is x and the remaining threads are y, the x/y is rounded to obtain an integer z. Assign z structured query statements to one of the remaining threads; loop through the allocation rules until all thread allocation ends.
- the database connection allocation module is configured to: when the remaining database connections for the query function are m, and the remaining user query concurrency is n, the m/n is rounded to obtain an integer a, the structuring The query statement has b, and for a and b take the minimum value c, then allocate c database connections for the database multi-table join query request, for the database connection query; or,
- the invention allocates multiple database connections for one database query, so that the query multiple tables can be simultaneously performed, fully utilizes the database resources, and greatly improves the query efficiency while satisfying the business statistics.
- FIG. 1 is a schematic flowchart of a first embodiment of a database query method according to the present invention
- FIG. 2 is a schematic flowchart of a second embodiment of a database query method according to the present invention.
- FIG. 3 is a schematic flowchart of a third embodiment of a database query method according to the present invention.
- FIG. 4 is a schematic diagram of functional modules of a first embodiment of a database query apparatus according to the present invention.
- FIG. 5 is a schematic diagram of functional modules of a second embodiment of a database query apparatus according to the present invention.
- FIG. 6 is a schematic diagram of functional modules of a third embodiment of a database query apparatus according to the present invention.
- FIG. 1 is a schematic flowchart diagram of a first embodiment of a database query method according to the present invention.
- the database query method in this embodiment includes the following steps:
- Step S10 Receive a request for a database multi-table join query, and construct a plurality of structured query statements for a single table according to the request;
- Real-time monitoring of the request of the database multi-table connection query sent by the terminal After receiving the request sent by the terminal to the database multi-table connection query, the field and the table information in the request according to the database multi-table connection query are constructed as multiple single tables.
- the structured query statement stores the structured query statement in memory.
- the receiving a request for a database multi-table join query configured as a plurality of structured query statements for a single table, may be:
- Location constructed as two structured query statements for a single table: select begintime, location, a1, a2from A, and select begintime, location, b1, b2from B, querying Table A and Table B, respectively. Or,
- the request for the database multi-table connection query may include: A table, a1 field, a2 field, B table, b1 field, b2 field in the terminal interface, connection field begintime, location, corresponding to the request according to the database multi-table connection query Query statement rules, respectively construct structured query statements for A and B tables: select begintime, location, a1, a2from A and select begintime, location, b1, b2from B, query Table A and Table B, respectively.
- Step S11 Allocating a corresponding database connection according to the current database resource, and using the connection query of the database;
- the database resources may include a database server, performance of the storage device, user concurrency, and the like.
- the way the database connection is allocated can include:
- B according to the database server, the performance of the storage device, the current user query concurrency, and one or more resources in the maximum concurrency of the user query, allocate a corresponding database connection for the request of the database multi-table connection query, for Connection query for the database.
- the current user query concurrency is the number of current user query databases, and the maximum concurrency of the user query is that the database can simultaneously accept the maximum number of user query databases.
- Step S12 Assign the structured query statement to the thread corresponding to the database connection according to a preset allocation rule
- the preset allocation rule may be:
- B read the structured query statement in the memory, the structured query statement has u, the thread has v, round the u / v, obtain the integer w; first assign w to each thread A structured query statement, and then the remaining structured query statements are sequentially assigned to different threads.
- Step S13 Receive a result set returned by the thread after executing the structured query statement.
- the result set is a result set, and the result set is stored in the cache.
- Step S14 Obtain data from the result set, and splicing the acquired data to obtain a query result.
- the result set is essentially a table, a behavior of the table is a record, a column of the table is a field, and the field includes a key field and a common field except the key field;
- the query record table is a query result.
- the multi-table query of the database no longer depends on a single database connection, but flexiblely allocates multiple database connections according to the database resources, and uses these database connections in different threads to perform concurrent query, in meeting the business statistics. At the same time, the query efficiency is greatly improved.
- FIG. 2 is a schematic flowchart diagram of a second embodiment of a database query method according to the present invention.
- the request for receiving a query for a database multi-table join in the second embodiment further includes: after constructing a plurality of structured query statements for a single table according to the request:
- Step S21 Sort the key fields of the structured query according to a preset sorting rule.
- the preset collation may be sorted in the order of 0 to 9 when the key fields are Arabic numerals, or may be sorted in reverse order; when the key field is Chinese or English, the first letter of the Chinese pinyin or the first of the English may be taken.
- the letters are sorted in order of a to z, and can also be sorted in reverse order.
- the structured query statement After sorting the specified key fields in the structured query statement, the structured query statement is assigned to the thread corresponding to the database connection, and the thread stores the result set into the cache while the database is executing, and the database query system reads the result from the cache. The data is spliced while the data is collected. Since the result set after the execution of the structured query statement is sorted by the key field, this is beneficial to improve the stitching efficiency.
- the key segments are sorted and then queried, and the results are spliced while receiving the result returned by the thread, which greatly reduces memory consumption and improves processing efficiency.
- FIG. 3 is a schematic flowchart of a third embodiment of a database query method according to the present invention.
- the database query method in this embodiment may include the following steps:
- Step S30 Receive a request for a database multi-table join query, and construct a plurality of structured query statements for a single table according to the request;
- Real-time monitoring of the request of the database multi-table connection query sent by the terminal After receiving the request sent by the terminal to the database multi-table connection query, the field and the table information in the request according to the database multi-table connection query are constructed as multiple single tables. Structured query statement.
- Step S31 segmenting the range of the key segment of the structured query according to the preset segmentation rule
- the preset segmentation rule is set to be the maximum value and the minimum value of the numerical value when the key field is an Arabic numeral, and divide it into ten ranges, and the same segment belongs to the same segment; similarly, When the key field is Chinese or English, the first letter of Chinese pinyin and the first letter of English may be segmented; when the key field is time, it may be segmented by year, month, day, hour, minute, etc.
- Step S32 processing the structured query statement according to the result of the segmentation, and generating a structured query statement corresponding to each segment;
- the key segment is divided into ten ranges according to the preset segmentation rule, after the original structured query statement is split and reorganized, ten structured query statements are generated, and each structured query statement is responsible for a range.
- the structured query statement is stored in memory.
- Step S33 Allocating a corresponding database connection according to the current database resource, for connection query of the database;
- the database resources may include a database server, performance of the storage device, user concurrency, and the like.
- Step S34 According to the segmentation order, the structured query statement corresponding to each segment is allocated to the thread corresponding to the database connection according to a preset allocation rule, and the structured query statement belonging to the same segment is executed. After the end, execute the structured query of the next segment;
- the structured query statement of the same segment in the memory is read.
- the preset allocation rule has been described in detail in step S12 of the first embodiment, and details are not described herein again.
- Step S35 Receive a result set returned by the thread after executing the structured query statement.
- the result set is a result set, and the result set is stored in the cache.
- Step S36 Obtain data from the result set, and splicing the acquired data to obtain a query result.
- the multi-table query for the database no longer depends on a single database connection, but flexibly allocates multiple database connections according to the database resources, and uses these database connections to concurrently execute structured query statements in different threads to satisfy the service.
- the statistics greatly improve the efficiency of the query.
- the keyword segment is divided into multiple query plans according to the scope, which can achieve the final result of batch return, improve the rendering speed of the home page, and optimize the user experience.
- FIG. 4 is a schematic diagram of functional modules of a first embodiment of a database query apparatus according to the present invention.
- the database query device 10 in this embodiment includes:
- the constructing module 11 is configured to receive a request for a database multi-table join query, and construct a plurality of structured query statements for a single table according to the request;
- Real-time monitoring of the request of the database multi-table connection query sent by the terminal After receiving the request sent by the terminal to the database multi-table connection query, the field and the table information in the request according to the database multi-table connection query are constructed as multiple single tables.
- the structured query statement stores the structured query statement in memory.
- the receiving a request for a database multi-table join query configured as a plurality of structured query statements for a single table, may be:
- Location constructed as two structured query statements for a single table: select begintime, location, a1, a2from A, and select begintime, location, b1, b2from B, querying Table A and Table B, respectively. or,
- the request for the database multi-table connection query may include: A table, a1 field, a2 field, B table, b1 field, b2 field in the terminal interface, connection field begintime, location, corresponding to the request according to the database multi-table connection query Query statement rules, respectively construct structured query statements for A and B tables: select begintime, location, a1, a2from A and select begintime, location, b1, b2from B, query Table A and Table B, respectively.
- the database connection allocation module 12 is configured to allocate a corresponding database connection according to the current database resource, and is used for connection query of the database;
- the corresponding database connection number is allocated for the connection query of the database.
- the database resources may include a database server, performance of the storage device, user concurrency, and the like.
- the way the database connection is allocated can include:
- B according to the database server, the performance of the storage device, the current user query concurrency, and one or more resources in the maximum concurrency of the user query, allocate a corresponding database connection for the request of the database multi-table connection query, for Connection query for the database.
- the current user query concurrency is the number of current user query databases, and the maximum concurrency of the user query is that the database can simultaneously accept the maximum number of user query databases.
- the structured query statement assigning module 13 is configured to allocate the structured query statement to the thread corresponding to the database connection according to a preset allocation rule
- the preset allocation rule may be:
- B read the structured query statement in the memory, the structured query statement has u, the thread has v, round the u / v, obtain the integer w; first assign w to each thread A structured query statement, and then the remaining structured query statements are sequentially assigned to different threads.
- the receiving module 14 is configured to receive a result set returned by the thread after executing the structured query statement
- the result set is a result set, and the result set is stored in the cache.
- the splicing module 15 is configured to acquire data from the result set, and simultaneously splicing the acquired data to obtain a query result.
- the result set is essentially a table, a behavior of the table is a record, a column of the table is a field, and the field includes a key field and a common field except the key field;
- the query record table is a query result.
- the multi-table query of the database no longer depends on a single database connection, but flexiblely allocates multiple database connections according to the database resources, and greatly improves the query efficiency while satisfying the service statistics.
- FIG. 5 is a schematic diagram of functional modules of a second embodiment of a database query apparatus according to the present invention.
- the database querying device in the second embodiment further includes:
- the sorting module 22 is configured to sort the key fields of the structured query statement according to a preset sorting rule.
- the preset collation may be sorted in the order of 0 to 9 when the key fields are Arabic numerals, or may be sorted in reverse order; when the key field is Chinese or English, the first letter of the Chinese pinyin or the first of the English may be taken.
- the letters are sorted in order of a to z, and can also be sorted in reverse order.
- the structured query statement After sorting the specified key fields in the structured query statement, the structured query statement is assigned to the thread corresponding to the database connection, and the thread stores the result set into the cache while the database is executing, and the database query system reads the result from the cache. The data is spliced while the data is collected. Since the result set after the execution of the structured query statement is sorted by the key field, this is beneficial to improve the stitching efficiency.
- the key segments are sorted and then queried, and the results are spliced while receiving the result returned by the thread, which greatly reduces memory consumption and improves processing efficiency.
- FIG. 6 is a schematic diagram of functional modules of a third embodiment of a database query apparatus according to the present invention.
- the database query device in the third embodiment further includes:
- the segmentation module 32 is configured to segment the range of the key field of the structured query statement according to a preset segmentation rule and process the structured query statement according to the result of the segmentation, and generate each segmentation Corresponding structured query statement;
- the preset segmentation rule is set to be the maximum value and the minimum value of the numerical value when the key field is an Arabic numeral, and divide it into ten ranges, and the same segment belongs to the same segment; similarly, When the key field is Chinese or English, the first letter of Chinese pinyin and the first letter of English may be segmented; when the key field is time, it may be segmented by year, month, day, hour, minute, etc.
- the key segment is divided into ten ranges according to the preset segmentation rule, after the original structured query statement is split and reorganized, ten structured query statements are generated, and each structured query statement is responsible for a range.
- the structured query statement is stored in memory.
- the structured query statement assigning module 34 is further configured to allocate, according to a segmentation order, the structured query statement corresponding to each segment to a thread corresponding to the database connection according to a preset allocation rule, and belongs to After the execution of the structured query of the same segment is completed, the structured query of the next segment is executed.
- the structured query statement of the same segment in the memory is read.
- the preset allocation rule in the structured query statement allocation module has been described in detail in the structured query statement allocation module 13 of the first embodiment, and is no longer Narration.
- the multi-table query for the database no longer depends on a single database connection, but flexibly allocates multiple database connections according to the database resources, and uses these database connections to concurrently execute structured query statements in different threads to satisfy the service.
- the statistics greatly improve the efficiency of the query.
- the keyword segment is divided into multiple query plans according to the scope, which can achieve the final result of batch return, improve the rendering speed of the home page, and optimize the user experience.
- a database query method and apparatus provided by an embodiment of the present invention have the following beneficial effects: by allocating multiple database connections for one database query, the query multiple tables can be simultaneously performed, fully utilizing database resources, and satisfying the service. Statistics also greatly improve the efficiency of the query.
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
Disclosed is a database query method, comprising: receiving a database multi-table connection query request, and generating multiple structured query statements for single tables according to the request; allocating corresponding database connections for database connection queries according to current database resources; allocating according to pre-set allocation policies the structured query statements to the threads corresponding to the database connections for execution; receiving the result set returned after the threads have executed the structured query statements; obtaining data from the result set, and concatenating the obtained data so as to obtain the query result. In the present invention, database multi-table queries no longer rely on a single database connection. Instead, multiple database connections are flexibly allocated according to the database resources, thereby satisfying operation statistics and substantially enhancing query efficiency.
Description
本发明涉及计算机技术领域,尤其涉及数据库查询方法及装置。The present invention relates to the field of computer technologies, and in particular, to a database query method and apparatus.
数据库通常包括用于存储数据值的一个或多个数据库表格。为了在数据库中检索和操纵数据,用户、应用程序或远程***通常向数据库管理***发出数据库查询。数据库查询是指用于检索存储在数据库中的数据的语句、命令或子句的集合。例如,查询可以在数据库中指定检索数据的特定位置(例如,表格、行或列),或者指定如何联接来自多个表格的列。The database typically includes one or more database tables for storing data values. In order to retrieve and manipulate data in a database, a user, application, or remote system typically issues a database query to the database management system. A database query is a collection of statements, commands, or clauses used to retrieve data stored in a database. For example, a query can specify a specific location in the database to retrieve data (for example, a table, row, or column), or specify how to join columns from multiple tables.
对于多表查询,由于一条连接查询语句只能用到一个数据库连接,对于强大的数据库服务器和存储设备,只能使用到其中很小的处理能力;如果所连接查询的表个数多、每个表数据量大,则查询效率很低。For multi-table queries, since a connection query can only use one database connection, for a powerful database server and storage device, only a small amount of processing power can be used; if the number of tables connected to the query is large, each If the amount of table data is large, the query efficiency is very low.
发明内容Summary of the invention
本发明实施例提供了一种数据库查询方法及装置,以至少解决相关技术中查询多表时,数据库查询的查询低效率的问题。The embodiment of the invention provides a database query method and device, so as to at least solve the problem of inefficient querying of database queries when querying multiple tables in the related art.
为实现上述目的,本发明提供的一种数据库查询方法,包括以下步骤:To achieve the above objective, the present invention provides a database query method, including the following steps:
接收对数据库多表连接查询的请求,根据请求构造多条针对单表的结构化查询语句;Receiving a request for a database multi-table join query, constructing a plurality of structured query statements for a single table according to the request;
根据当前数据库资源,分配相应的数据库连接,用于数据库的连接查询;According to the current database resources, allocate corresponding database connections for connection query of the database;
按预设的分配规则,将所述结构化查询语句分配给所述数据库连接对应的线程执行;Assigning the structured query statement to the thread corresponding to the database connection according to a preset allocation rule;
接收所述线程执行结构化查询语句后返回的结果集;Receiving a result set returned by the thread after executing the structured query statement;
从结果集中获取数据,同时拼接所获取的数据,获得查询结果。
The data is obtained from the result set, and the acquired data is spliced at the same time to obtain the query result.
优选地,所述接收对数据库多表连接查询的请求,根据请求构造多条针对单表的结构化查询语句之后还包括:Preferably, the receiving the request for the database multi-table join query, after constructing the plurality of structured query statements for the single table according to the request, further comprises:
根据预设的排序规则对结构化查询语句的关键字段排序。Sorts the key fields of a structured query based on a preset collation.
优选地,所述接收对数据库多表连接查询的请求,根据请求构造多条针对单表的结构化查询语句之后还包括:Preferably, the receiving the request for the database multi-table join query, after constructing the plurality of structured query statements for the single table according to the request, further comprises:
根据预设的分段规则对结构化查询语句的关键字段的范围进行分段和按分段的结果对所述结构化查询语句进行处理,生成每个分段所对应的结构化查询语句;Segmenting the range of the key field of the structured query statement according to a preset segmentation rule and processing the structured query statement according to the result of the segmentation, and generating a structured query statement corresponding to each segment;
按预设的分配规则,将所述结构化查询语句分配给所述数据库连接对应的线程执行的步骤包括:According to the preset allocation rule, the step of assigning the structured query statement to the thread corresponding to the database connection includes:
按照分段顺序,将所述每个分段所对应的结构化查询语句按预设的分配规则分配给所述数据库连接对应的线程执行,且属于同一分段的结构化查询语句执行结束后,再执行下一分段的结构化查询语句。According to the segmentation order, the structured query statement corresponding to each segment is allocated to the thread corresponding to the database connection according to a preset allocation rule, and after the execution of the structured query statement belonging to the same segment is completed, Then execute the structured query of the next segment.
优选地,所述预设的分配规则包括:Preferably, the preset allocation rule comprises:
当未分配的结构化查询语句为x个,剩余线程为y时,则对x/y进行取整,获得整数z,将z条结构化查询语句分配到剩余线程中的一个;循环执行该步骤,直到所有的线程分配结束。When the unstructured structured query statement is x and the remaining threads are y, the x/y is rounded to obtain the integer z, and the z structured query statements are assigned to one of the remaining threads; the loop is executed. Until all thread allocation ends.
优选地,所述根据当前数据库资源,分配相应的数据库连接,用于数据库的连接查询的步骤包括:Preferably, the step of allocating a corresponding database connection according to the current database resource, and the connection query for the database comprises:
当剩余用于查询功能的数据库连接为m个,剩余用户查询并发度为n时,则对m/n进行取整,获得整数a,所述结构化查询语句有b条,对a和b取最小值c,则为所述数据库多表连接查询的请求分配c个数据库连接,用于数据库的连接查询;或者,When there are m database connections for the query function and the remaining users query the degree of n, the m/n is rounded to obtain the integer a. The structured query has b, and the a and b are taken. The minimum value c is used to allocate c database connections for the database multi-table join query request, for the database connection query; or
根据数据库服务器、存储设备的性能、当前用户查询并发度和用户查询最大并发度中的一个或多个资源,为所述数据库多表连接查询的请求分配相应的数据库连接,用于数据库的连接查询。Allocating a corresponding database connection for the request of the database multi-table connection query according to one or more resources in the database server, the performance of the storage device, the current user query concurrency, and the maximum concurrency of the user query, for the connection query of the database .
此外,为实现上述目的,本发明实施例还提供一种数据库查询装置,所述数据库查询装置包括:
In addition, in order to achieve the above object, an embodiment of the present invention further provides a database query device, where the database query device includes:
构造模块,设置为接收对数据库多表连接查询的请求,根据请求构造多条针对单表的结构化查询语句;Constructing a module, configured to receive a request for a database multi-table join query, and construct a plurality of structured query statements for a single table according to the request;
数据库连接分配模块,设置为根据当前数据库资源,分配相应的数据库连接,用于数据库的连接查询;The database connection allocation module is configured to allocate a corresponding database connection according to the current database resource, and is used for connection query of the database;
结构化查询语句分配模块,设置为按预设的分配规则,将所述结构化查询语句分配给所述数据库连接对应的线程执行;The structured query statement assigning module is configured to allocate the structured query statement to the thread corresponding to the database connection according to a preset allocation rule;
接收模块,设置为接收所述线程执行结构化查询语句后返回的结果集;a receiving module, configured to receive a result set returned by the thread after executing the structured query statement;
拼接模块,设置为从结果集中获取数据,同时拼接所获取的数据,获得查询结果。The splicing module is configured to acquire data from the result set, and simultaneously splicing the acquired data to obtain a query result.
优选地,所述数据库查询装置还包括:Preferably, the database querying device further includes:
排序模块,设置为根据预设的排序规则对结构化查询语句的关键字段排序。The sorting module is arranged to sort the key fields of the structured query according to a preset sorting rule.
优选地,所述数据库查询装置还包括:Preferably, the database querying device further includes:
分段模块,设置为根据预设的分段规则对结构化查询语句的关键字段的范围进行分段和按分段的结果对所述结构化查询语句进行处理,生成每个分段所对应的结构化查询语句;a segmentation module, configured to segment the range of the key field of the structured query statement according to a preset segmentation rule, and process the structured query statement according to the result of the segmentation, to generate a corresponding segment Structured query statement;
所述结构化查询语句分配模块还设置为按照分段顺序,将所述每个分段所对应的结构化查询语句按预设的分配规则分配给所述数据库连接对应的线程执行,且属于同一分段的结构化查询语句执行结束后,再执行下一分段的结构化查询语句。The structured query statement allocation module is further configured to allocate, according to a segmentation order, the structured query statement corresponding to each segment to a thread corresponding to the database connection according to a preset allocation rule, and belong to the same After the execution of the segmented structured query statement is completed, the structured query of the next segment is executed.
优选地,所述结构化查询语句分配模块中预设的分配规则包括:当未分配的结构化查询语句为x个,剩余线程为y时,则对x/y进行取整,获得整数z,将z条结构化查询语句分配到剩余线程中的一个;循环执行该分配规则,直到所有的线程分配结束。Preferably, the preset allocation rule in the structured query statement allocation module includes: when the unallocated structured query statement is x and the remaining threads are y, the x/y is rounded to obtain an integer z. Assign z structured query statements to one of the remaining threads; loop through the allocation rules until all thread allocation ends.
优选地,所述数据库连接分配模块设置为当剩余用于查询功能的数据库连接为m个,剩余用户查询并发度为n时,则对m/n进行取整,获得整数a,所述结构化查询语句有b条,对a和b取最小值c,则为所述数据库多表连接查询的请求分配c个数据库连接,用于数据库的连接查询;或者,Preferably, the database connection allocation module is configured to: when the remaining database connections for the query function are m, and the remaining user query concurrency is n, the m/n is rounded to obtain an integer a, the structuring The query statement has b, and for a and b take the minimum value c, then allocate c database connections for the database multi-table join query request, for the database connection query; or,
根据数据库服务器、存储设备的性能、当前用户查询并发度和用户查询最大并发度中的一个或多个资源,为所述数据库多表连接查询的请求分配相应的数据库连接,用于数据库的连接查询。
Allocating a corresponding database connection for the request of the database multi-table connection query according to one or more resources in the database server, the performance of the storage device, the current user query concurrency, and the maximum concurrency of the user query, for the connection query of the database .
本发明通过为一次数据库查询分配多条数据库连接,使得查询多表可同时进行,充分使用了数据库资源,在满足业务统计的同时大大提升了查询效率。The invention allocates multiple database connections for one database query, so that the query multiple tables can be simultaneously performed, fully utilizes the database resources, and greatly improves the query efficiency while satisfying the business statistics.
图1为本发明数据库查询方法第一实施例的流程示意图;1 is a schematic flowchart of a first embodiment of a database query method according to the present invention;
图2为本发明数据库查询方法第二实施例的流程示意图;2 is a schematic flowchart of a second embodiment of a database query method according to the present invention;
图3为本发明数据库查询方法第三实施例的流程示意图;3 is a schematic flowchart of a third embodiment of a database query method according to the present invention;
图4为本发明数据库查询装置第一实施例的功能模块示意图;4 is a schematic diagram of functional modules of a first embodiment of a database query apparatus according to the present invention;
图5为本发明数据库查询装置第二实施例的功能模块示意图;FIG. 5 is a schematic diagram of functional modules of a second embodiment of a database query apparatus according to the present invention; FIG.
图6为本发明数据库查询装置第三实施例的功能模块示意图。FIG. 6 is a schematic diagram of functional modules of a third embodiment of a database query apparatus according to the present invention.
本发明目的的实现、功能特点及优点将结合实施例,参照附图做进一步说明。The implementation, functional features, and advantages of the present invention will be further described in conjunction with the embodiments.
应当理解,此处所描述的具体实施例仅仅用以解释本发明,并不用于限定本发明。It is understood that the specific embodiments described herein are merely illustrative of the invention and are not intended to limit the invention.
本发明提供一种数据库查询方法。参照图1,图1为本发明数据库查询方法第一实施例的流程示意图。该实施例中的数据库查询方法包括以下步骤:The invention provides a database query method. Referring to FIG. 1, FIG. 1 is a schematic flowchart diagram of a first embodiment of a database query method according to the present invention. The database query method in this embodiment includes the following steps:
步骤S10、接收对数据库多表连接查询的请求,根据请求构造多条针对单表的结构化查询语句;Step S10: Receive a request for a database multi-table join query, and construct a plurality of structured query statements for a single table according to the request;
实时监测终端发来的数据库多表连接查询的请求,当接收到终端发来数据库多表连接查询的请求后,根据数据库多表连接查询的请求中的字段以及表格信息构造为多条针对单表的结构化查询语句,将所述结构化查询语句存入内存中。Real-time monitoring of the request of the database multi-table connection query sent by the terminal. After receiving the request sent by the terminal to the database multi-table connection query, the field and the table information in the request according to the database multi-table connection query are constructed as multiple single tables. The structured query statement stores the structured query statement in memory.
例如,所述接收对数据库多表连接查询的请求,构造为多条针对单表的结构化查询语句可为:For example, the receiving a request for a database multi-table join query, configured as a plurality of structured query statements for a single table, may be:
数据库多表连接查询的请求:select A.begintime,A.location,A.a1,A.a2,B.b1,B.b2from A,B where A.begintime=B.begintime and A.lcoation=B.location,构造为两条针对单表的结构化查询语句:select begintime,location,a1,a2from A以及select begintime,location,b1,b2from B,分别对表A和表B进行查询。或者,
Database multi-table join query request: select A.begintime, A.location, A.a1, A.a2, B.b1, B.b2from A, B where A.begintime=B.begintime and A.lcoation=B. Location, constructed as two structured query statements for a single table: select begintime, location, a1, a2from A, and select begintime, location, b1, b2from B, querying Table A and Table B, respectively. Or,
所述数据库多表连接查询的请求可包括:终端界面中的A表、a1字段、a2字段,B表、b1字段、b2字段,连接字段begintime、location,根据数据库多表连接查询的请求对应的查询语句规则,分别构造针对A表和B表的结构化查询语句:select begintime,location,a1,a2from A以及select begintime,location,b1,b2from B,分别对表A和表B进行查询。The request for the database multi-table connection query may include: A table, a1 field, a2 field, B table, b1 field, b2 field in the terminal interface, connection field begintime, location, corresponding to the request according to the database multi-table connection query Query statement rules, respectively construct structured query statements for A and B tables: select begintime, location, a1, a2from A and select begintime, location, b1, b2from B, query Table A and Table B, respectively.
步骤S11、根据当前数据库资源,分配相应的数据库连接,用于数据库的连接查询;Step S11: Allocating a corresponding database connection according to the current database resource, and using the connection query of the database;
接收数据库多表连接查询的请求后,根据当前数据库资源,分配相应的数据库连接数,用于数据库的连接查询。所述数据库资源可包括有数据库服务器、存储设备的性能和用户并发度等等。After receiving the request of the database multi-table join query, according to the current database resource, the corresponding database connection number is allocated for the connection query of the database. The database resources may include a database server, performance of the storage device, user concurrency, and the like.
数据库连接的分配方式可包括:The way the database connection is allocated can include:
A、当剩余用于查询功能的数据库连接为m个,剩余用户查询并发度为n时,则对m/n进行取整,获得整数a,所述结构化查询语句有b条,对a和b取最小值c,则为所述数据库多表连接查询的请求分配c个数据库连接,用于数据库的连接查询。A. When the remaining database connections for the query function are m, and the remaining users query the degree of concurrency n, the m/n is rounded to obtain the integer a, and the structured query has b, for a and b takes the minimum value c, then allocates c database connections for the database multi-table join query request, for the database connection query.
例如第一个查询只查2个表,对应的有2条结构化查询语句;剩余用于查询功能的数据库连接有50条,剩余用户查询并发度为10,则剩余每个查询请求平均能获取50/10=5个数据库连接,取2和5的最小值,因此分配2个数据库连接给此次数据库多表连接查询的请求;第一个查询未结束时发起第二个查询,第二个查询查12张表,对剩余每个查询请求平均能获取的数据库连接数48/9的计算结果取整为5,取12和5的最小值,那么第二个查询就分配5个数据库连接,以此类推。For example, the first query only checks 2 tables, and there are 2 structured query statements; the remaining database connections for the query function are 50, and the remaining users query the degree of concurrency is 10, then the remaining query requests can be obtained on average. 50/10=5 database connections, taking the minimum of 2 and 5, so assign 2 database connections to the database multi-table join query request; the first query does not end when the second query is initiated, the second Query 12 tables, and the calculation result of the average number of database connections 48/9 that can be obtained for each remaining query request is rounded to 5, taking the minimum value of 12 and 5, then the second query allocates 5 database connections. And so on.
或者,B、根据数据库服务器、存储设备的性能、当前用户查询并发度和用户查询最大并发度中的一个或多个资源,为所述数据库多表连接查询的请求分配相应的数据库连接,用于数据库的连接查询。Or, B, according to the database server, the performance of the storage device, the current user query concurrency, and one or more resources in the maximum concurrency of the user query, allocate a corresponding database connection for the request of the database multi-table connection query, for Connection query for the database.
数据库服务器性能越好或存储设备的性能越好,分配到的数据库连接就越多,当前用户查询并发度越接近用户查询最大并发度时,分配到的数据库连接就越少。所述当前用户查询并发度为当前用户查询数据库的数量,所述用户查询最大并发度为数据库可同时接纳用户查询数据库的最大数量。The better the database server performance or the better the performance of the storage device, the more database connections are allocated. The closer the current user query concurrency is to the maximum concurrency of the user query, the fewer database connections are allocated. The current user query concurrency is the number of current user query databases, and the maximum concurrency of the user query is that the database can simultaneously accept the maximum number of user query databases.
步骤S12、按预设的分配规则,将所述结构化查询语句分配给所述数据库连接对应的线程执行;
Step S12: Assign the structured query statement to the thread corresponding to the database connection according to a preset allocation rule;
由于数据库连接的资源远少于线程资源,而数据库连接数与线程数是一一对应的,所以只要分配了数据库连接数即可;当存在特殊情况使线程数少于数据库连接数时,实际分配的数据库连接数为线程数。所述预设的分配规则可以为:Since the database connection resources are far less than the thread resources, and the number of database connections is one-to-one corresponding to the number of threads, so long as the number of database connections is allocated; when there are special cases, the number of threads is less than the number of database connections, the actual allocation The number of database connections is the number of threads. The preset allocation rule may be:
A、读取内存中的结构化查询语句,当未分配的结构化查询语句为x个,剩余线程为y时,则对x/y进行取整,获得整数z,将z条结构化查询语句分配到剩余线程中的一个;循环执行该步骤,直到所有的线程分配结束。A. Read the structured query statement in memory. When the unstructured structured query statement is x and the remaining threads are y, then x/y is rounded to obtain the integer z, and the z structured query statement Assign to one of the remaining threads; loop through the steps until all thread allocation ends.
例如,有结构化查询语句12条,分配5个数据库连接,对应地有5个线程,该5个线程执行结构化查询语句数量对应为3、3、2、2、2。For example, there are 12 structured query statements, 5 database connections are allocated, and there are 5 threads correspondingly. The number of structured query statements executed by the 5 threads corresponds to 3, 3, 2, 2, 2.
或者,B、读取内存中的结构化查询语句,所述结构化查询语句有u条,所述线程有v个,对u/v进行取整,获得整数w;先对每个线程分配w条结构化查询语句,再将剩余的结构化查询语句依次分配给不同的线程。Or, B, read the structured query statement in the memory, the structured query statement has u, the thread has v, round the u / v, obtain the integer w; first assign w to each thread A structured query statement, and then the remaining structured query statements are sequentially assigned to different threads.
例如,有结构化查询语句12条,分配5个数据库连接,对应地有5个线程,先对每个线程分配2条结构化查询语句,即该5个线程分配到的结构化查询语句数量对应为2、2、2、2、2;,再将剩余的结构化查询语句依次分配给不同的线程,所以最终该5个线程执行结构化查询语句数量对应为3、3、2、2、2。For example, there are 12 structured query statements, 5 database connections are allocated, and there are 5 threads correspondingly. Two structured query statements are allocated to each thread first, that is, the number of structured query statements allocated by the five threads corresponds to 2, 2, 2, 2, 2;, then the remaining structured query statements are sequentially assigned to different threads, so the number of structured query statements executed by the 5 threads corresponds to 3, 3, 2, 2, 2 .
步骤S13、接收所述线程执行结构化查询语句后返回的结果集;Step S13: Receive a result set returned by the thread after executing the structured query statement.
线程每执行完一个结构化查询语句的结果为结果集,将所述结果集存入缓存中。Each time a thread executes a structured query statement, the result set is a result set, and the result set is stored in the cache.
步骤S14、从结果集中获取数据,同时拼接所获取到的数据,获得查询结果。Step S14: Obtain data from the result set, and splicing the acquired data to obtain a query result.
所述结果集实质是一张表格,表格的一行为一个记录,表格的一列为一个字段,所述的字段包括关键字段和除关键字段以外的普通字段;The result set is essentially a table, a behavior of the table is a record, a column of the table is a field, and the field includes a key field and a common field except the key field;
新建一查询记录表,读取缓存中结果集的一个记录,将所述记录录入查询记录表中,读取下一个记录,并将该记录的关键字段与查询记录表中的关键字段比较,当关键字段不同时,则增加这个记录到查询记录表中;当关键字段相同时,则增加该记录的普通字段到查询记录表中的相应记录中;当所有结果集录入表中后,所述查询记录表为查询结果。Create a new query record table, read a record of the result set in the cache, enter the record into the query record table, read the next record, and compare the key field of the record with the key field in the query record table When the key fields are different, the record is added to the query record table; when the key fields are the same, the common field of the record is added to the corresponding record in the query record table; when all the result sets are entered into the table, The query record table is a query result.
本实施例中,对数据库的多表查询不再依赖单个数据库连接,而是根据数据库资源,灵活分配多条数据库连接,并在不同的线程中使用这些数据库连接进行并发查询,在满足业务统计的同时大大提升了查询效率。
In this embodiment, the multi-table query of the database no longer depends on a single database connection, but flexiblely allocates multiple database connections according to the database resources, and uses these database connections in different threads to perform concurrent query, in meeting the business statistics. At the same time, the query efficiency is greatly improved.
参照图2,图2为本发明数据库查询方法第二实施例的流程示意图。基于第一实施例,该第二实施例中的接收对数据库多表连接查询的请求,根据请求构造多条针对单表的结构化查询语句之后还包括:Referring to FIG. 2, FIG. 2 is a schematic flowchart diagram of a second embodiment of a database query method according to the present invention. Based on the first embodiment, the request for receiving a query for a database multi-table join in the second embodiment further includes: after constructing a plurality of structured query statements for a single table according to the request:
步骤S21、根据预设的排序规则对结构化查询语句的关键字段排序。Step S21: Sort the key fields of the structured query according to a preset sorting rule.
所述预设的排序规则为当关键字段为***数字时,可按0至9顺序排序,也可逆序排序;当关键字段为中文或英文时,可取中文拼音的首字母或英文的首字母按a至z顺序排序,也可逆序排序。The preset collation may be sorted in the order of 0 to 9 when the key fields are Arabic numerals, or may be sorted in reverse order; when the key field is Chinese or English, the first letter of the Chinese pinyin or the first of the English may be taken. The letters are sorted in order of a to z, and can also be sorted in reverse order.
对结构化查询语句中指定关键字段排序后,将结构化查询语句分配给所述数据库连接对应的线程执行,线程一边执行一边将结果集存入缓存中,数据库查询***从缓存中读取结果集的数据的同时对数据进行拼接。由于结构化查询语句执行后的结果集是按照关键字段排序的,这样有利于提升拼接效率。After sorting the specified key fields in the structured query statement, the structured query statement is assigned to the thread corresponding to the database connection, and the thread stores the result set into the cache while the database is executing, and the database query system reads the result from the cache. The data is spliced while the data is collected. Since the result set after the execution of the structured query statement is sorted by the key field, this is beneficial to improve the stitching efficiency.
本实施例中,对关键字段进行排序后进行查询,可一边接收线程返回的结果一边对所述结果拼接,大大降低了内存耗用并提升处理效率。In this embodiment, the key segments are sorted and then queried, and the results are spliced while receiving the result returned by the thread, which greatly reduces memory consumption and improves processing efficiency.
参照图3,图3为本发明数据库查询方法第三实施例的流程示意图。该实施例中的数据库查询方法可包括以下步骤:Referring to FIG. 3, FIG. 3 is a schematic flowchart of a third embodiment of a database query method according to the present invention. The database query method in this embodiment may include the following steps:
步骤S30、接收对数据库多表连接查询的请求,根据请求构造多条针对单表的结构化查询语句;Step S30: Receive a request for a database multi-table join query, and construct a plurality of structured query statements for a single table according to the request;
实时监测终端发来的数据库多表连接查询的请求,当接收到终端发来数据库多表连接查询的请求后,根据数据库多表连接查询的请求中的字段以及表格信息构造为多条针对单表的结构化查询语句。Real-time monitoring of the request of the database multi-table connection query sent by the terminal. After receiving the request sent by the terminal to the database multi-table connection query, the field and the table information in the request according to the database multi-table connection query are constructed as multiple single tables. Structured query statement.
所述构造针对单表的结构化查询语已在第一实施例的步骤S10中详细说明,在此不再赘述。The structured query language for the single table has been described in detail in step S10 of the first embodiment, and details are not described herein again.
步骤S31、根据预设的分段规则对结构化查询语句的关键字段的范围进行分段;Step S31, segmenting the range of the key segment of the structured query according to the preset segmentation rule;
所述预设的分段规则设置为当关键字段为***数字时,可取数值的最大值和最小值,将其划分为十个范围,同属于一个范围的即为同一分段;类似的,关键字段为中文或英文时,可取中文拼音的首字母和英文的首字母进行分段;当关键字段为时间时,还可按年、月、日、时、分等的方式分段。
The preset segmentation rule is set to be the maximum value and the minimum value of the numerical value when the key field is an Arabic numeral, and divide it into ten ranges, and the same segment belongs to the same segment; similarly, When the key field is Chinese or English, the first letter of Chinese pinyin and the first letter of English may be segmented; when the key field is time, it may be segmented by year, month, day, hour, minute, etc.
步骤S32、按分段的结果对所述结构化查询语句进行处理,生成每个分段所对应的结构化查询语句;Step S32, processing the structured query statement according to the result of the segmentation, and generating a structured query statement corresponding to each segment;
若按照预设的分段规则将关键字段划分为十个范围,则将一条原结构化查询语句进行拆分、重组等处理后,生成十条结构化查询语句,且每一条结构化查询语句负责一个范围。将所述结构化查询语句存入内存中。If the key segment is divided into ten ranges according to the preset segmentation rule, after the original structured query statement is split and reorganized, ten structured query statements are generated, and each structured query statement is responsible for a range. The structured query statement is stored in memory.
步骤S33、根据当前数据库资源,分配相应的数据库连接,用于数据库的连接查询;Step S33: Allocating a corresponding database connection according to the current database resource, for connection query of the database;
接收数据库多表连接查询的请求后,根据当前数据库资源,分配相应的数据库连接数,用于数据库的连接查询。所述数据库资源可包括有数据库服务器、存储设备的性能和用户并发度等等。After receiving the request of the database multi-table join query, according to the current database resource, the corresponding database connection number is allocated for the connection query of the database. The database resources may include a database server, performance of the storage device, user concurrency, and the like.
所述数据库连接的分配方式已在第一实施例的步骤S11中详细说明,在此不再赘述。The manner of allocating the database connection has been described in detail in step S11 of the first embodiment, and details are not described herein again.
步骤S34、按照分段顺序,将所述每个分段所对应的结构化查询语句按预设的分配规则分配给所述数据库连接对应的线程执行,且属于同一分段的结构化查询语句执行结束后,再执行下一分段的结构化查询语句;Step S34: According to the segmentation order, the structured query statement corresponding to each segment is allocated to the thread corresponding to the database connection according to a preset allocation rule, and the structured query statement belonging to the same segment is executed. After the end, execute the structured query of the next segment;
读取内存中的同一分段的结构化查询语句,所述预设的分配规则已在第一实施例的步骤S12中详细说明,在此不再赘述。The structured query statement of the same segment in the memory is read. The preset allocation rule has been described in detail in step S12 of the first embodiment, and details are not described herein again.
步骤S35、接收所述线程执行结构化查询语句后返回的结果集;Step S35: Receive a result set returned by the thread after executing the structured query statement.
线程每执行完一个结构化查询语句的结果为结果集,将所述结果集存入缓存中。Each time a thread executes a structured query statement, the result set is a result set, and the result set is stored in the cache.
步骤S36、从结果集中获取数据,同时拼接所获取的数据,获得查询结果。Step S36: Obtain data from the result set, and splicing the acquired data to obtain a query result.
读取缓存中结果集的数据,将同一分段的结构化查询语句的结果集拼接成一个表,即为一个查询结果。完成一个查询结果后,即将该查询结果返回终端。Read the data of the result set in the cache, and concatenate the result set of the structured query of the same segment into a table, which is a query result. After completing a query result, the query result is returned to the terminal.
本实施例中,对数据库的多表查询不再依赖单个数据库连接,而是根据数据库资源,灵活分配多条数据库连接,使用这些数据库连接在不同的线程中并发执行结构化查询语句,在满足业务统计的同时大大提升了查询效率,同时还对关键字段按范围拆分为多次查询计划,可以达到最终结果分批返回的效果,提升首页呈现速度,优化用户体验。
In this embodiment, the multi-table query for the database no longer depends on a single database connection, but flexibly allocates multiple database connections according to the database resources, and uses these database connections to concurrently execute structured query statements in different threads to satisfy the service. At the same time, the statistics greatly improve the efficiency of the query. At the same time, the keyword segment is divided into multiple query plans according to the scope, which can achieve the final result of batch return, improve the rendering speed of the home page, and optimize the user experience.
对应地,本发明进一步提供一种数据库查询装置。参照图4,图4为本发明数据库查询装置第一实施例的功能模块示意图。该实施例中的数据库查询装置10包括:Correspondingly, the present invention further provides a database query device. Referring to FIG. 4, FIG. 4 is a schematic diagram of functional modules of a first embodiment of a database query apparatus according to the present invention. The database query device 10 in this embodiment includes:
构造模块11,设置为接收对数据库多表连接查询的请求,根据请求构造多条针对单表的结构化查询语句;The constructing module 11 is configured to receive a request for a database multi-table join query, and construct a plurality of structured query statements for a single table according to the request;
实时监测终端发来的数据库多表连接查询的请求,当接收到终端发来数据库多表连接查询的请求后,根据数据库多表连接查询的请求中的字段以及表格信息构造为多条针对单表的结构化查询语句,将所述结构化查询语句存入内存中。Real-time monitoring of the request of the database multi-table connection query sent by the terminal. After receiving the request sent by the terminal to the database multi-table connection query, the field and the table information in the request according to the database multi-table connection query are constructed as multiple single tables. The structured query statement stores the structured query statement in memory.
例如,所述接收对数据库多表连接查询的请求,构造为多条针对单表的结构化查询语句可为:For example, the receiving a request for a database multi-table join query, configured as a plurality of structured query statements for a single table, may be:
数据库多表连接查询的请求:select A.begintime,A.location,A.a1,A.a2,B.b1,B.b2from A,B where A.begintime=B.begintime and A.lcoation=B.location,构造为两条针对单表的结构化查询语句:select begintime,location,a1,a2from A以及select begintime,location,b1,b2from B,分别对表A和表B进行查询。或者,Database multi-table join query request: select A.begintime, A.location, A.a1, A.a2, B.b1, B.b2from A, B where A.begintime=B.begintime and A.lcoation=B. Location, constructed as two structured query statements for a single table: select begintime, location, a1, a2from A, and select begintime, location, b1, b2from B, querying Table A and Table B, respectively. or,
所述数据库多表连接查询的请求可包括:终端界面中的A表、a1字段、a2字段,B表、b1字段、b2字段,连接字段begintime、location,根据数据库多表连接查询的请求对应的查询语句规则,分别构造针对A表和B表的结构化查询语句:select begintime,location,a1,a2from A以及select begintime,location,b1,b2from B,分别对表A和表B进行查询。The request for the database multi-table connection query may include: A table, a1 field, a2 field, B table, b1 field, b2 field in the terminal interface, connection field begintime, location, corresponding to the request according to the database multi-table connection query Query statement rules, respectively construct structured query statements for A and B tables: select begintime, location, a1, a2from A and select begintime, location, b1, b2from B, query Table A and Table B, respectively.
数据库连接分配模块12,设置为根据当前数据库资源,分配相应的数据库连接,用于数据库的连接查询;The database connection allocation module 12 is configured to allocate a corresponding database connection according to the current database resource, and is used for connection query of the database;
接收数据库多表连接查询的请求后,根据当前数据库资源,分配相应的数据库连接数,用于数据库的连接查询。所述数据库资源可包括有数据库服务器、存储设备的性能和用户并发度等等。数据库连接的分配方式可包括:After receiving the request of the database multi-table join query, according to the current database resource, the corresponding database connection number is allocated for the connection query of the database. The database resources may include a database server, performance of the storage device, user concurrency, and the like. The way the database connection is allocated can include:
A、当剩余用于查询功能的数据库连接为m个,剩余用户查询并发度为n时,则对m/n进行取整,获得整数a,所述结构化查询语句有b条,对a和b取最小值c,则为所述数据库多表连接查询的请求分配c个数据库连接,用于数据库的连接查询。
A. When the remaining database connections for the query function are m, and the remaining users query the degree of concurrency n, the m/n is rounded to obtain the integer a, and the structured query has b, for a and b takes the minimum value c, then allocates c database connections for the database multi-table join query request, for the database connection query.
例如第一个查询只查2个表,对应的有2条结构化查询语句;剩余用于查询功能的数据库连接有50条,剩余用户查询并发度为10,则剩余每个查询请求平均能获取50/10=5个数据库连接,取2和5的最小值,因此分配2个数据库连接给此次数据库多表连接查询的请求;第一个查询未结束时发起第二个查询,第二个查询查12张表,对剩余每个查询请求平均能获取的数据库连接数48/9的计算结果取整为5,取12和5的最小值,那么第二个查询就分配5个数据库连接,以此类推。For example, the first query only checks 2 tables, and there are 2 structured query statements; the remaining database connections for the query function are 50, and the remaining users query the degree of concurrency is 10, then the remaining query requests can be obtained on average. 50/10=5 database connections, taking the minimum of 2 and 5, so assign 2 database connections to the database multi-table join query request; the first query does not end when the second query is initiated, the second Query 12 tables, and the calculation result of the average number of database connections 48/9 that can be obtained for each remaining query request is rounded to 5, taking the minimum value of 12 and 5, then the second query allocates 5 database connections. And so on.
或者,B、根据数据库服务器、存储设备的性能、当前用户查询并发度和用户查询最大并发度中的一个或多个资源,为所述数据库多表连接查询的请求分配相应的数据库连接,用于数据库的连接查询。Or, B, according to the database server, the performance of the storage device, the current user query concurrency, and one or more resources in the maximum concurrency of the user query, allocate a corresponding database connection for the request of the database multi-table connection query, for Connection query for the database.
数据库服务器性能越好或存储设备的性能越好,分配到的数据库连接就越多,当前用户查询并发度越接近用户查询最大并发度时,分配到的数据库连接就越少。所述当前用户查询并发度为当前用户查询数据库的数量,所述用户查询最大并发度为数据库可同时接纳用户查询数据库的最大数量。The better the database server performance or the better the performance of the storage device, the more database connections are allocated. The closer the current user query concurrency is to the maximum concurrency of the user query, the fewer database connections are allocated. The current user query concurrency is the number of current user query databases, and the maximum concurrency of the user query is that the database can simultaneously accept the maximum number of user query databases.
结构化查询语句分配模块13,设置为按预设的分配规则,将所述结构化查询语句分配给所述数据库连接对应的线程执行;The structured query statement assigning module 13 is configured to allocate the structured query statement to the thread corresponding to the database connection according to a preset allocation rule;
由于数据库连接的资源远少于线程资源,而数据库连接数与线程数是一一对应的,所以只要分配了数据库连接数即可;当存在特殊情况使线程数少于数据库连接数时,实际分配的数据库连接数为线程数。所述预设的分配规则可以为:Since the database connection resources are far less than the thread resources, and the number of database connections is one-to-one corresponding to the number of threads, so long as the number of database connections is allocated; when there are special cases, the number of threads is less than the number of database connections, the actual allocation The number of database connections is the number of threads. The preset allocation rule may be:
A、读取内存中的结构化查询语句,当未分配的结构化查询语句为x个,剩余线程为y时,则对x/y进行取整,获得整数z,将z条结构化查询语句分配到剩余线程中的一个;循环执行该步骤,直到所有的线程分配结束。A. Read the structured query statement in memory. When the unstructured structured query statement is x and the remaining threads are y, then x/y is rounded to obtain the integer z, and the z structured query statement Assign to one of the remaining threads; loop through the steps until all thread allocation ends.
例如,有结构化查询语句12条,分配5个数据库连接,对应地有5个线程,该5个线程执行结构化查询语句数量对应为3、3、2、2、2。For example, there are 12 structured query statements, 5 database connections are allocated, and there are 5 threads correspondingly. The number of structured query statements executed by the 5 threads corresponds to 3, 3, 2, 2, 2.
或者,B、读取内存中的结构化查询语句,所述结构化查询语句有u条,所述线程有v个,对u/v进行取整,获得整数w;先对每个线程分配w条结构化查询语句,再将剩余的结构化查询语句依次分配给不同的线程。Or, B, read the structured query statement in the memory, the structured query statement has u, the thread has v, round the u / v, obtain the integer w; first assign w to each thread A structured query statement, and then the remaining structured query statements are sequentially assigned to different threads.
例如,有结构化查询语句12条,分配5个数据库连接,对应地有5个线程,先对每个线程分配2条结构化查询语句,即该5个线程分配到的结构化查询语句数量对应
为2、2、2、2、2;,再将剩余的结构化查询语句依次分配给不同的线程,所以最终该5个线程执行结构化查询语句数量对应为3、3、2、2、2。For example, there are 12 structured query statements, 5 database connections are allocated, and there are 5 threads correspondingly. Two structured query statements are allocated to each thread first, that is, the number of structured query statements allocated by the five threads corresponds to
2, 2, 2, 2, 2;, then the remaining structured query statements are sequentially assigned to different threads, so the number of structured query statements executed by the 5 threads corresponds to 3, 3, 2, 2, 2 .
接收模块14,设置为接收所述线程执行结构化查询语句后返回的结果集;The receiving module 14 is configured to receive a result set returned by the thread after executing the structured query statement;
线程每执行完一个结构化查询语句的结果为结果集,将所述结果集存入缓存中。Each time a thread executes a structured query statement, the result set is a result set, and the result set is stored in the cache.
拼接模块15,设置为从结果集中获取数据,同时拼接所获取的数据,获得查询结果。The splicing module 15 is configured to acquire data from the result set, and simultaneously splicing the acquired data to obtain a query result.
所述结果集实质是一张表格,表格的一行为一个记录,表格的一列为一个字段,所述的字段包括关键字段和除关键字段以外的普通字段;The result set is essentially a table, a behavior of the table is a record, a column of the table is a field, and the field includes a key field and a common field except the key field;
新建一查询记录表,读取缓存中结果集的一个记录,将所述记录录入查询记录表中,读取下一个记录,并将该记录的关键字段与查询记录表中的关键字段比较,当关键字段不同时,则增加这个记录到查询记录表中;当关键字段相同时,则增加该记录的普通字段到查询记录表中的相应记录中;当所有结果集录入表中后,所述查询记录表为查询结果。Create a new query record table, read a record of the result set in the cache, enter the record into the query record table, read the next record, and compare the key field of the record with the key field in the query record table When the key fields are different, the record is added to the query record table; when the key fields are the same, the common field of the record is added to the corresponding record in the query record table; when all the result sets are entered into the table, The query record table is a query result.
本实施例中,对数据库的多表查询不再依赖单个数据库连接,而是根据数据库资源,灵活分配多条数据库连接,在满足业务统计的同时大大提升了查询效率。In this embodiment, the multi-table query of the database no longer depends on a single database connection, but flexiblely allocates multiple database connections according to the database resources, and greatly improves the query efficiency while satisfying the service statistics.
参照图5,图5为本发明数据库查询装置第二实施例的功能模块示意图。所述基于第一实施例,该第二实施例中的数据库查询装置还包括:Referring to FIG. 5, FIG. 5 is a schematic diagram of functional modules of a second embodiment of a database query apparatus according to the present invention. According to the first embodiment, the database querying device in the second embodiment further includes:
排序模块22,设置为根据预设的排序规则对结构化查询语句的关键字段排序。The sorting module 22 is configured to sort the key fields of the structured query statement according to a preset sorting rule.
所述预设的排序规则为当关键字段为***数字时,可按0至9顺序排序,也可逆序排序;当关键字段为中文或英文时,可取中文拼音的首字母或英文的首字母按a至z顺序排序,也可逆序排序。The preset collation may be sorted in the order of 0 to 9 when the key fields are Arabic numerals, or may be sorted in reverse order; when the key field is Chinese or English, the first letter of the Chinese pinyin or the first of the English may be taken. The letters are sorted in order of a to z, and can also be sorted in reverse order.
对结构化查询语句中指定关键字段排序后,将结构化查询语句分配给所述数据库连接对应的线程执行,线程一边执行一边将结果集存入缓存中,数据库查询***从缓存中读取结果集的数据的同时对数据进行拼接。由于结构化查询语句执行后的结果集是按照关键字段排序的,这样有利于提升拼接效率。After sorting the specified key fields in the structured query statement, the structured query statement is assigned to the thread corresponding to the database connection, and the thread stores the result set into the cache while the database is executing, and the database query system reads the result from the cache. The data is spliced while the data is collected. Since the result set after the execution of the structured query statement is sorted by the key field, this is beneficial to improve the stitching efficiency.
本实施例中,对关键字段进行排序后进行查询,可一边接收线程返回的结果一边对所述结果拼接,大大降低了内存耗用并提升处理效率。
In this embodiment, the key segments are sorted and then queried, and the results are spliced while receiving the result returned by the thread, which greatly reduces memory consumption and improves processing efficiency.
参照图6,图6为本发明数据库查询装置第三实施例的功能模块示意图。Referring to FIG. 6, FIG. 6 is a schematic diagram of functional modules of a third embodiment of a database query apparatus according to the present invention.
所述基于第一实施例,该第三实施例中的数据库查询装置还包括:According to the first embodiment, the database query device in the third embodiment further includes:
分段模块32,设置为根据预设的分段规则对结构化查询语句的关键字段的范围进行分段和按分段的结果对所述结构化查询语句进行处理,生成每个分段所对应的结构化查询语句;The segmentation module 32 is configured to segment the range of the key field of the structured query statement according to a preset segmentation rule and process the structured query statement according to the result of the segmentation, and generate each segmentation Corresponding structured query statement;
所述预设的分段规则设置为当关键字段为***数字时,可取数值的最大值和最小值,将其划分为十个范围,同属于一个范围的即为同一分段;类似的,关键字段为中文或英文时,可取中文拼音的首字母和英文的首字母进行分段;当关键字段为时间时,还可按年、月、日、时、分等的方式分段。The preset segmentation rule is set to be the maximum value and the minimum value of the numerical value when the key field is an Arabic numeral, and divide it into ten ranges, and the same segment belongs to the same segment; similarly, When the key field is Chinese or English, the first letter of Chinese pinyin and the first letter of English may be segmented; when the key field is time, it may be segmented by year, month, day, hour, minute, etc.
若按照预设的分段规则将关键字段划分为十个范围,则将一条原结构化查询语句进行拆分、重组等处理后,生成十条结构化查询语句,且每一条结构化查询语句负责一个范围。将所述结构化查询语句存入内存中。If the key segment is divided into ten ranges according to the preset segmentation rule, after the original structured query statement is split and reorganized, ten structured query statements are generated, and each structured query statement is responsible for a range. The structured query statement is stored in memory.
所述结构化查询语句分配模块34还设置为按照分段顺序,将所述每个分段所对应的结构化查询语句按预设的分配规则分配给所述数据库连接对应的线程执行,且属于同一分段的结构化查询语句执行结束后,再执行下一分段的结构化查询语句。The structured query statement assigning module 34 is further configured to allocate, according to a segmentation order, the structured query statement corresponding to each segment to a thread corresponding to the database connection according to a preset allocation rule, and belongs to After the execution of the structured query of the same segment is completed, the structured query of the next segment is executed.
读取内存中的同一分段的结构化查询语句,所述结构化查询语句分配模块中预设的分配规则已在第一实施例的结构化查询语句分配模块13中详细说明,在此不再赘述。The structured query statement of the same segment in the memory is read. The preset allocation rule in the structured query statement allocation module has been described in detail in the structured query statement allocation module 13 of the first embodiment, and is no longer Narration.
本实施例中,对数据库的多表查询不再依赖单个数据库连接,而是根据数据库资源,灵活分配多条数据库连接,使用这些数据库连接在不同的线程中并发执行结构化查询语句,在满足业务统计的同时大大提升了查询效率,同时还对关键字段按范围拆分为多次查询计划,可以达到最终结果分批返回的效果,提升首页呈现速度,优化用户体验。In this embodiment, the multi-table query for the database no longer depends on a single database connection, but flexibly allocates multiple database connections according to the database resources, and uses these database connections to concurrently execute structured query statements in different threads to satisfy the service. At the same time, the statistics greatly improve the efficiency of the query. At the same time, the keyword segment is divided into multiple query plans according to the scope, which can achieve the final result of batch return, improve the rendering speed of the home page, and optimize the user experience.
以上仅为本发明的优选实施例,并非因此限制本发明的专利范围,凡是利用本发明说明书及附图内容所作的等效结构或等效流程变换,或直接或间接运用在其他相关的技术领域,均同理包括在本发明的专利保护范围内。
The above are only the preferred embodiments of the present invention, and are not intended to limit the scope of the invention, and the equivalent structure or equivalent process transformations made by the description of the present invention and the drawings are directly or indirectly applied to other related technical fields. The same is included in the scope of patent protection of the present invention.
如上所述,本发明实施例提供的一种数据库查询方法及装置具有以下有益效果:通过为一次数据库查询分配多条数据库连接,使得查询多表可同时进行,充分使用了数据库资源,在满足业务统计的同时大大提升了查询效率。
As described above, a database query method and apparatus provided by an embodiment of the present invention have the following beneficial effects: by allocating multiple database connections for one database query, the query multiple tables can be simultaneously performed, fully utilizing database resources, and satisfying the service. Statistics also greatly improve the efficiency of the query.
Claims (10)
- 一种数据库查询方法,所述数据库查询方法包括以下步骤:A database query method, the database query method comprising the following steps:接收对数据库多表连接查询的请求,根据请求构造多条针对单表的结构化查询语句;Receiving a request for a database multi-table join query, constructing a plurality of structured query statements for a single table according to the request;根据当前数据库资源,分配相应的数据库连接,用于数据库的连接查询;According to the current database resources, allocate corresponding database connections for connection query of the database;按预设的分配规则,将所述结构化查询语句分配给所述数据库连接对应的线程执行;Assigning the structured query statement to the thread corresponding to the database connection according to a preset allocation rule;接收所述线程执行结构化查询语句后返回的结果集;Receiving a result set returned by the thread after executing the structured query statement;从结果集中获取数据,同时拼接所获取的数据,获得查询结果。The data is obtained from the result set, and the acquired data is spliced at the same time to obtain the query result.
- 如权利要求1所述的数据库查询方法,其中,所述接收对数据库多表连接查询的请求,根据请求构造多条针对单表的结构化查询语句之后还包括:The database query method according to claim 1, wherein the receiving a request for a database multi-table join query, and constructing a plurality of structured query statements for a single table according to the request further comprises:根据预设的排序规则对结构化查询语句的关键字段排序。Sorts the key fields of a structured query based on a preset collation.
- 如权利要求1所述的数据库查询方法,其中,所述接收对数据库多表连接查询的请求,根据请求构造多条针对单表的结构化查询语句之后还包括:The database query method according to claim 1, wherein the receiving a request for a database multi-table join query, and constructing a plurality of structured query statements for a single table according to the request further comprises:根据预设的分段规则对结构化查询语句的关键字段的范围进行分段;Segmenting the range of key fields of the structured query statement according to a preset segmentation rule;按分段的结果对所述结构化查询语句进行处理,生成每个分段所对应的结构化查询语句;Processing the structured query statement according to the result of the segmentation, and generating a structured query statement corresponding to each segment;按预设的分配规则,将所述结构化查询语句分配给所述数据库连接对应的线程执行的步骤包括:According to the preset allocation rule, the step of assigning the structured query statement to the thread corresponding to the database connection includes:按照分段顺序,将所述每个分段所对应的结构化查询语句按预设的分配规则分配给所述数据库连接对应的线程执行,且属于同一分段的结构化查询语句执行结束后,再执行下一分段的结构化查询语句。According to the segmentation order, the structured query statement corresponding to each segment is allocated to the thread corresponding to the database connection according to a preset allocation rule, and after the execution of the structured query statement belonging to the same segment is completed, Then execute the structured query of the next segment.
- 如权利要求1所述的数据库查询方法,其中,所述预设的分配规则包括:The database query method according to claim 1, wherein the preset allocation rule comprises:当未分配的结构化查询语句为x个,剩余线程为y时,则对x/y进行取整,获得整数z,将z条结构化查询语句分配到剩余线程中的一个;循环执行该步骤,直到所有的线程分配结束。 When the unstructured structured query statement is x and the remaining threads are y, the x/y is rounded to obtain the integer z, and the z structured query statements are assigned to one of the remaining threads; the loop is executed. Until all thread allocation ends.
- 如权利要求1所述的数据库查询方法,其中,所述根据当前数据库资源,分配相应的数据库连接,用于数据库的连接查询的步骤包括:The database query method according to claim 1, wherein the step of allocating a corresponding database connection according to the current database resource, and the connection query for the database comprises:当剩余用于查询功能的数据库连接为m个,剩余用户查询并发度为n时,则对m/n进行取整,获得整数a,所述结构化查询语句有b条,对a和b取最小值c,则为所述数据库多表连接查询的请求分配c个数据库连接,用于数据库的连接查询;或者,When there are m database connections for the query function and the remaining users query the degree of n, the m/n is rounded to obtain the integer a. The structured query has b, and the a and b are taken. The minimum value c is used to allocate c database connections for the database multi-table join query request, for the database connection query; or根据数据库服务器、存储设备的性能、当前用户查询并发度和用户查询最大并发度中的一个或多个资源,为所述数据库多表连接查询的请求分配相应的数据库连接,用于数据库的连接查询。Allocating a corresponding database connection for the request of the database multi-table connection query according to one or more resources in the database server, the performance of the storage device, the current user query concurrency, and the maximum concurrency of the user query, for the connection query of the database .
- 一种数据库查询装置,所述数据库查询装置包括:A database query device, the database query device comprising:构造模块,设置为接收对数据库多表连接查询的请求,根据请求构造多条针对单表的结构化查询语句;Constructing a module, configured to receive a request for a database multi-table join query, and construct a plurality of structured query statements for a single table according to the request;数据库连接分配模块,设置为根据当前数据库资源,分配相应的数据库连接,用于数据库的连接查询;The database connection allocation module is configured to allocate a corresponding database connection according to the current database resource, and is used for connection query of the database;结构化查询语句分配模块,设置为按预设的分配规则,将所述结构化查询语句分配给所述数据库连接对应的线程执行;The structured query statement assigning module is configured to allocate the structured query statement to the thread corresponding to the database connection according to a preset allocation rule;接收模块,设置为接收所述线程执行结构化查询语句后返回的结果集;a receiving module, configured to receive a result set returned by the thread after executing the structured query statement;拼接模块,设置为从结果集中获取数据,同时拼接所获取的数据,获得查询结果。The splicing module is configured to acquire data from the result set, and simultaneously splicing the acquired data to obtain a query result.
- 如权利要求6所述的数据库查询装置,其中,所述数据库查询装置还包括:The database query device of claim 6, wherein the database query device further comprises:排序模块,设置为根据预设的排序规则对结构化查询语句的关键字段排序。The sorting module is arranged to sort the key fields of the structured query according to a preset sorting rule.
- 如权利要求6所述的数据库查询装置,其中,所述数据库查询装置还包括:The database query device of claim 6, wherein the database query device further comprises:分段模块,设置为根据预设的分段规则对结构化查询语句的关键字段的范围进行分段和按分段的结果对所述结构化查询语句进行处理,生成每个分段所对应的结构化查询语句;a segmentation module, configured to segment the range of the key field of the structured query statement according to a preset segmentation rule, and process the structured query statement according to the result of the segmentation, to generate a corresponding segment Structured query statement;所述结构化查询语句分配模块还设置为按照分段顺序,将所述每个分段所对应的结构化查询语句按预设的分配规则分配给所述数据库连接对应的线程执行,且属于同一分段的结构化查询语句执行结束后,再执行下一分段的结构化查询语句。 The structured query statement allocation module is further configured to allocate, according to a segmentation order, the structured query statement corresponding to each segment to a thread corresponding to the database connection according to a preset allocation rule, and belong to the same After the execution of the segmented structured query statement is completed, the structured query of the next segment is executed.
- 如权利要求6所述的数据库查询装置,其中,所述结构化查询语句分配模块中预设的分配规则包括:当未分配的结构化查询语句为x个,剩余线程为y时,则对x/y进行取整,获得整数z,将z条结构化查询语句分配到剩余线程中的一个;循环执行该分配规则,直到所有的线程分配结束。The database query device according to claim 6, wherein the allocation rule preset in the structured query statement allocation module comprises: when the unallocated structured query statement is x and the remaining threads are y, then the pair is x. /y performs rounding to obtain an integer z, assigning z structured query statements to one of the remaining threads; looping through the allocation rules until all thread allocation ends.
- 如权利要求6所述的数据库查询装置,其中,所述数据库连接分配模块设置为当剩余用于查询功能的数据库连接为m个,剩余用户查询并发度为n时,则对m/n进行取整,获得整数a,所述结构化查询语句有b条,对a和b取最小值c,则为所述数据库多表连接查询的请求分配c个数据库连接,用于数据库的连接查询;或者,The database query device according to claim 6, wherein the database connection allocation module is configured to take m/n when the remaining database connections for the query function are m and the remaining users query the degree of concurrency n. Integral, the integer a is obtained, the structured query statement has b, and the minimum value c is obtained for a and b, and c database connections are allocated for the request of the database multi-table join query, for connection query of the database; or ,根据数据库服务器、存储设备的性能、当前用户查询并发度和用户查询最大并发度中的一个或多个资源,为所述数据库多表连接查询的请求分配相应的数据库连接,用于数据库的连接查询。 Allocating a corresponding database connection for the request of the database multi-table connection query according to one or more resources in the database server, the performance of the storage device, the current user query concurrency, and the maximum concurrency of the user query, for the connection query of the database .
Applications Claiming Priority (2)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN201410621118.6 | 2014-11-06 | ||
CN201410621118.6A CN105574052A (en) | 2014-11-06 | 2014-11-06 | Database query method and apparatus |
Publications (1)
Publication Number | Publication Date |
---|---|
WO2015184762A1 true WO2015184762A1 (en) | 2015-12-10 |
Family
ID=54766022
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
PCT/CN2014/094259 WO2015184762A1 (en) | 2014-11-06 | 2014-12-18 | Database query method and device |
Country Status (2)
Country | Link |
---|---|
CN (1) | CN105574052A (en) |
WO (1) | WO2015184762A1 (en) |
Cited By (3)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
WO2018058671A1 (en) * | 2016-09-30 | 2018-04-05 | 华为技术有限公司 | Control method for executing multi-table connection operation and corresponding device |
CN111459968A (en) * | 2020-03-27 | 2020-07-28 | 深圳市金蝶天燕云计算股份有限公司 | Data query method, data query system and computer storage medium |
CN116126865A (en) * | 2023-02-17 | 2023-05-16 | 安芯网盾(北京)科技有限公司 | Proxy method and device for mixed use of multiple databases |
Families Citing this family (12)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN106599310A (en) * | 2016-12-29 | 2017-04-26 | 中国建设银行股份有限公司 | Parallel query method and apparatus based on multi-data-source scene |
CN106649828B (en) * | 2016-12-29 | 2019-12-24 | ***股份有限公司 | Data query method and system |
CN108280082A (en) * | 2017-01-06 | 2018-07-13 | 北京京东尚科信息技术有限公司 | A kind of extemporaneous querying method and system of statistical data |
CN106951531B (en) * | 2017-03-21 | 2020-07-10 | 东软集团股份有限公司 | Data query method and device |
CN107273525A (en) * | 2017-06-23 | 2017-10-20 | 上海携程商务有限公司 | Functional expression querying method and system |
CN107918676B (en) * | 2017-12-15 | 2022-01-18 | 联想(北京)有限公司 | Resource optimization method for structured query and database query system |
CN109656946B (en) * | 2018-09-29 | 2022-12-16 | 创新先进技术有限公司 | Multi-table association query method, device and equipment |
CN109508344B (en) * | 2018-09-29 | 2024-03-12 | 中国平安人寿保险股份有限公司 | Service data query method and device, electronic equipment and storage medium |
CN109271409B (en) * | 2018-11-08 | 2021-11-02 | 成都索贝数码科技股份有限公司 | Database fragmentation execution method based on container resource allocation |
CN112131253A (en) * | 2019-06-25 | 2020-12-25 | 北京沃东天骏信息技术有限公司 | Data query method, device, equipment and storage medium |
CN110825953B (en) * | 2019-11-12 | 2024-03-22 | 上海德启信息科技有限公司 | Data query method, device and equipment |
CN111914151A (en) * | 2020-08-11 | 2020-11-10 | 上海毅博电子商务有限责任公司 | Association table object query optimization method |
Citations (5)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN1492361A (en) * | 2002-10-25 | 2004-04-28 | 联想(北京)有限公司 | Processing method for embedded data bank searching |
CN1556482A (en) * | 2003-12-31 | 2004-12-22 | 中兴通讯股份有限公司 | Data processing method for realizing data base multitable inguiry |
US20060136380A1 (en) * | 2004-12-17 | 2006-06-22 | Purcell Terence P | System and method for executing a multi-table query |
CN104112008A (en) * | 2014-07-16 | 2014-10-22 | 深圳市国泰安信息技术有限公司 | Multi-table data association inquiry optimizing method and device |
CN104123374A (en) * | 2014-07-28 | 2014-10-29 | 北京京东尚科信息技术有限公司 | Method and device for aggregate query in distributed databases |
Family Cites Families (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN102521406B (en) * | 2011-12-26 | 2014-06-25 | 中国科学院计算技术研究所 | Distributed query method and system for complex task of querying massive structured data |
-
2014
- 2014-11-06 CN CN201410621118.6A patent/CN105574052A/en active Pending
- 2014-12-18 WO PCT/CN2014/094259 patent/WO2015184762A1/en active Application Filing
Patent Citations (5)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
CN1492361A (en) * | 2002-10-25 | 2004-04-28 | 联想(北京)有限公司 | Processing method for embedded data bank searching |
CN1556482A (en) * | 2003-12-31 | 2004-12-22 | 中兴通讯股份有限公司 | Data processing method for realizing data base multitable inguiry |
US20060136380A1 (en) * | 2004-12-17 | 2006-06-22 | Purcell Terence P | System and method for executing a multi-table query |
CN104112008A (en) * | 2014-07-16 | 2014-10-22 | 深圳市国泰安信息技术有限公司 | Multi-table data association inquiry optimizing method and device |
CN104123374A (en) * | 2014-07-28 | 2014-10-29 | 北京京东尚科信息技术有限公司 | Method and device for aggregate query in distributed databases |
Cited By (7)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
WO2018058671A1 (en) * | 2016-09-30 | 2018-04-05 | 华为技术有限公司 | Control method for executing multi-table connection operation and corresponding device |
CN109791543A (en) * | 2016-09-30 | 2019-05-21 | 华为技术有限公司 | Execute the control method and corresponding intrument of multi-table join operation |
CN109791543B (en) * | 2016-09-30 | 2021-02-12 | 华为技术有限公司 | Control method for executing multi-table connection operation and corresponding device |
US11301470B2 (en) | 2016-09-30 | 2022-04-12 | Huawei Technologies Co., Ltd. | Control method for performing multi-table join operation and corresponding apparatus |
CN111459968A (en) * | 2020-03-27 | 2020-07-28 | 深圳市金蝶天燕云计算股份有限公司 | Data query method, data query system and computer storage medium |
CN116126865A (en) * | 2023-02-17 | 2023-05-16 | 安芯网盾(北京)科技有限公司 | Proxy method and device for mixed use of multiple databases |
CN116126865B (en) * | 2023-02-17 | 2023-09-08 | 安芯网盾(北京)科技有限公司 | Proxy method and device for mixed use of multiple databases |
Also Published As
Publication number | Publication date |
---|---|
CN105574052A (en) | 2016-05-11 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
WO2015184762A1 (en) | Database query method and device | |
DE102016105472B4 (en) | Storage tiering and block-level parallel allocation in file systems | |
US9298774B2 (en) | Changing the compression level of query plans | |
US8712972B2 (en) | Query optimization with awareness of limited resource usage | |
US8924373B2 (en) | Query plans with parameter markers in place of object identifiers | |
CN105279276A (en) | Database index optimization system | |
US9305076B1 (en) | Flattening a cluster hierarchy tree to filter documents | |
US10579616B2 (en) | Data search system, data search method, and program product | |
US9355147B2 (en) | Access plan for a database query | |
US9959312B2 (en) | High performance index creation on sorted data using parallel query plans | |
US9734176B2 (en) | Index merge ordering | |
CN106528898A (en) | Method and device for converting data of non-relational database into relational database | |
CN105989015B (en) | Database capacity expansion method and device and method and device for accessing database | |
Wang et al. | Distributed storage and index of vector spatial data based on HBase | |
CN105683941A (en) | Regulating enterprise database warehouse resource usage | |
KR102465391B1 (en) | Analytical methods of systems for setting data processing cycles based on growth rate of data in real time | |
CN111046059B (en) | Low-efficiency SQL statement analysis method and system based on distributed database cluster | |
KR20210060830A (en) | Big data intelligent collecting method and device | |
CN101639851A (en) | Method for storing and querying data and devices therefor | |
WO2021004266A1 (en) | Data insertion method and apparatus, device and storage medium | |
CN111522819A (en) | Method and system for summarizing tree-structured data | |
TWI785724B (en) | Method for creating data warehouse, electronic device, and storage medium | |
US9239867B2 (en) | System and method for fast identification of variable roles during initial data exploration | |
CN113032368A (en) | Data migration method and device, storage medium and platform | |
US11301448B2 (en) | Method and system of a secondary index in a distributed data base system |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
121 | Ep: the epo has been informed by wipo that ep was designated in this application |
Ref document number: 14893906 Country of ref document: EP Kind code of ref document: A1 |
|
NENP | Non-entry into the national phase |
Ref country code: DE |
|
122 | Ep: pct application non-entry in european phase |
Ref document number: 14893906 Country of ref document: EP Kind code of ref document: A1 |