CN112506964B - Data query method, system and computer readable storage medium - Google Patents
Data query method, system and computer readable storage medium Download PDFInfo
- Publication number
- CN112506964B CN112506964B CN202011377046.7A CN202011377046A CN112506964B CN 112506964 B CN112506964 B CN 112506964B CN 202011377046 A CN202011377046 A CN 202011377046A CN 112506964 B CN112506964 B CN 112506964B
- Authority
- CN
- China
- Prior art keywords
- data
- index
- updated
- tables
- fields
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 42
- 230000001360 synchronised effect Effects 0.000 claims description 13
- 230000004048 modification Effects 0.000 claims description 9
- 238000012986 modification Methods 0.000 claims description 9
- 230000000875 corresponding effect Effects 0.000 description 68
- 230000008569 process Effects 0.000 description 6
- 230000008859 change Effects 0.000 description 5
- 238000004891 communication Methods 0.000 description 5
- 238000003780 insertion Methods 0.000 description 3
- 230000037431 insertion Effects 0.000 description 3
- 230000002596 correlated effect Effects 0.000 description 2
- 238000010586 diagram Methods 0.000 description 2
- 230000006870 function Effects 0.000 description 2
- 238000012217 deletion Methods 0.000 description 1
- 230000037430 deletion Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
- G06F16/24553—Query execution of query operations
- G06F16/24558—Binary matching operations
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2228—Indexing structures
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/27—Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Computing Systems (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a data query method, which comprises the following steps: acquiring data updating information of each data table in a database; if a plurality of updated data tables exist according to the data updating information, and associated unique table fields exist among the updated data tables, merging the table fields of the updated data tables into an index field of the same index, wherein the updated data tables are data tables with data updating in a database; and querying the data in the updated data tables from the search engine based on the combined index fields. The invention also discloses a data query system and a computer readable storage medium. By combining the table fields of the data tables into the same index, the data in the data tables can be queried only by one query without multiple queries, and the data query efficiency is improved.
Description
Technical Field
The present invention relates to the field of data query technologies, and in particular, to a data query method, a data query system, and a computer readable storage medium.
Background
At present, the method for inquiring data based on indexes mainly comprises the following two steps: the first is to synchronize the data table to the index as it is, and it is necessary to combine multiple table data to obtain from the index and then to the database or another index, that is, it is necessary to query at least twice, which is not efficient. The second method is to synchronize the log data to the queue through analysis, and consume the queue arrangement data to write into the index through other programs, at this time, not only the queue module and the additional consuming program module are required to be additionally introduced, but also the data is not real-time enough and not efficient enough when synchronizing to the index. Thus, the existing synchronous indexing scheme has the problem of low data query efficiency.
Disclosure of Invention
The invention mainly aims to provide a data query method, a data query system and a computer readable storage medium, and aims to solve the problem of low data query efficiency when data are synchronized to an index in the prior art.
In order to achieve the above object, the present invention provides a data query method, which includes the steps of:
Acquiring data updating information of each data table in a database;
If a plurality of updated data tables exist according to the data updating information, and associated unique table fields exist among the updated data tables, merging the table fields of the updated data tables into an index field of the same index, wherein the updated data tables are data tables with data updating in a database;
and querying the data in the updated data tables from the search engine based on the combined index fields.
Optionally, the step of merging the table fields of the plurality of updated data tables into an index field of the same index includes:
determining the data table type and the data update type corresponding to each update data table according to the data update information;
determining an index synchronization mode according to the data table type corresponding to each updated data table and the data updating type;
And merging the table fields of each updated data table into the index fields of the same index according to the determined index synchronization mode.
Optionally, the step of determining the index synchronization mode according to the data table type corresponding to each updated data table and the data update type includes:
judging whether a preset main table exists in the plurality of updated data tables or not and whether the data update type corresponding to the preset main table is newly added data or not according to the data table type and the data update type corresponding to each updated data table;
if a preset main table exists and the data update type corresponding to the preset main table is newly added data, determining that the index synchronization mode is a first synchronization mode;
if a preset main table exists and the data update type corresponding to the preset main table is modified data, determining that the index synchronization mode is a second synchronization mode;
and if the preset main table does not exist, determining that the index synchronization mode is a third synchronization mode.
Optionally, the step of merging the table fields of each updated data table into the index field of the same index in the determined index synchronization manner includes:
When a preset main table exists in the plurality of updated data tables and the data update type corresponding to the preset main table is newly added data, the table field of the preset main table is used as an index field of a newly added index to be inserted into an index table of a search engine according to the first synchronous mode;
and merging the table field of an additional table into the index field of the newly added index, wherein the additional table is other data tables except a preset main table in the plurality of updated data tables.
Optionally, the step of merging the table fields of each updated data table into the index field of the same index according to the determined index synchronization mode includes:
Determining a target index from indexes of the search engine according to unique table fields with association among the plurality of updated data tables;
When a preset main table exists in the plurality of updated data tables and the data update type corresponding to the preset main table is data modification, updating the table field of the preset main table to the target index according to the second synchronous mode;
The table fields of the additional table are merged into the index field of the updated target index.
Optionally, the step of merging the table fields of each updated data table into the index field of the same index according to the determined index synchronization mode includes:
And merging the table fields of each additional table into the index field of the target index according to the third synchronous mode when the preset main table does not exist in the plurality of updated data tables.
Optionally, the step of determining the target index from the index of the search engine according to the unique table field where there is an association between the plurality of updated data tables includes:
Determining an index taking the unique table field as a main key from indexes of a search engine according to the unique table field;
And determining an index taking the unique table field as a main key in the search engine as a target index.
Optionally, the step of merging the table fields of the plurality of updated data tables into an index field of the same index includes:
determining a data structure corresponding to the index in the search engine;
Converting the data structures of the plurality of updated data tables into data structures corresponding to indexes;
And merging the table fields of the plurality of updated data tables into index fields of the same index according to the converted data structure.
In addition, in order to achieve the above object, the present invention also provides a data query system, which includes a memory, a processor, and a data query program stored on the processor and executable on the processor, wherein the processor implements the steps of the data query method as described above when executing the data query program.
In addition, in order to achieve the above object, the present invention also provides a computer-readable storage medium having stored thereon a data query program which, when executed by a processor, implements the steps of the data query method as described above.
According to the embodiment of the invention, through obtaining the data updating information of each data table in the database, if a plurality of updating data tables exist according to the data updating information, and associated unique table fields exist among the plurality of updating data tables, table fields of the plurality of updating data tables are combined into index fields of the same index, the updating data tables exist in the data tables, then the data in the plurality of updating data tables can be queried from a search engine based on the combined index fields, a consumption queue is not required to be introduced, and the data is not required to be acquired from the database or other indexes after the index is queried. The table fields of the plurality of updated data tables are combined to the same index of the search engine, so that corresponding data in the plurality of data tables can be queried and obtained only through one query without multiple queries, and the data query efficiency is improved.
Drawings
FIG. 1 is a schematic diagram of a data query system in a hardware operating environment according to an embodiment of the present invention;
FIG. 2 is a flow chart of a first embodiment of the data query method of the present invention;
fig. 3 is a flowchart of a second embodiment of the data query method of the present invention.
The achievement of the objects, functional features and advantages of the present invention will be further described with reference to the accompanying drawings, in conjunction with the embodiments.
Detailed Description
It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the scope of the invention.
The main solution of the invention is as follows: acquiring data updating information of each data table in a database; if a plurality of updated data tables exist according to the data updating information, and associated unique table fields exist among the updated data tables, merging the table fields of the updated data tables into an index field of the same index, wherein the updated data tables are data tables with data updating in a database; and querying the data in the updated data tables from the search engine based on the combined index fields.
There are two main types of data query schemes based on indexes at present: one is to synchronize the data table to the index as it is, which needs to be queried many times, and the query efficiency is not high; another is to synchronize log data to a queue, which also has the problem of inefficient queries. Therefore, the present invention provides a data query method, system and computer readable storage medium, wherein whether an updated data table exists in a data table in a database is determined by acquiring data update information of each data table in the database, the updated data table exists in the data table, if it is determined that a plurality of updated data tables exist according to the data update information and associated unique table fields exist among the plurality of updated data tables, table fields of the plurality of updated data tables are merged into index fields of the same index, and then data in the plurality of updated data tables is queried from a search engine based on the merged index fields, so that the problem that query is not efficient still exists due to the fact that the plurality of data tables are still needed to be combined for query when the data tables are directly synchronized to the index, or a consumption queue is introduced to synchronize log data to a queue is avoided.
Referring to fig. 1, fig. 1 is a schematic diagram of a data query system of a hardware running environment according to an embodiment of the present invention.
As shown in fig. 1, the data query system may include: a communication bus 1002, a processor 1001, such as a CPU, a user interface 1003, a network interface 1004, a memory 1005. Wherein the communication bus 1002 is used to enable connected communication between these components. The user interface 1003 may include a Display, an input unit such as a Keyboard (Keyboard), and the optional user interface 1003 may further include a standard wired interface, a wireless interface. The network interface 1004 may optionally include a standard wired interface, a wireless interface (e.g., WI-FI interface). The memory 1005 may be a high-speed RAM memory or a stable memory (non-volatile memory), such as a disk memory. The memory 1005 may also optionally be a storage device separate from the processor 1001 described above.
Those skilled in the art will appreciate that the data query system architecture shown in FIG. 1 does not constitute a limitation on the data query system, and may include more or fewer components than shown, or may combine certain components, or a different arrangement of components.
In the data query system shown in fig. 1, the network interface 1004 is mainly used for connecting to a background server and performing data communication with the background server; the user interface 1003 is mainly used for connecting a client (user side) and performing data communication with the client; and the processor 1001 may be configured to call a data query program stored in the memory 1005 and perform the following operations:
Acquiring data updating information of each data table in a database;
If a plurality of updated data tables exist according to the data updating information, and associated unique table fields exist among the updated data tables, merging the table fields of the updated data tables into an index field of the same index, wherein the updated data tables are data tables with data updating in a database;
and querying the data in the updated data tables from the search engine based on the combined index fields.
Optionally, the processor 1001 may call a data query program stored in the memory 1005, and further perform the following operations:
determining the data table type and the data update type corresponding to each update data table according to the data update information;
determining an index synchronization mode according to the data table type corresponding to each updated data table and the data updating type;
And merging the table fields of each updated data table into the index fields of the same index according to the determined index synchronization mode.
Optionally, the processor 1001 may call a data query program stored in the memory 1005, and further perform the following operations:
judging whether a preset main table exists in the plurality of updated data tables or not and whether the data update type corresponding to the preset main table is newly added data or not according to the data table type and the data update type corresponding to each updated data table;
if a preset main table exists and the data update type corresponding to the preset main table is newly added data, determining that the index synchronization mode is a first synchronization mode;
if a preset main table exists and the data update type corresponding to the preset main table is modified data, determining that the index synchronization mode is a second synchronization mode;
and if the preset main table does not exist, determining that the index synchronization mode is a third synchronization mode.
Optionally, the processor 1001 invokes a data query program stored in the memory 1005 and performs the following operations:
When a preset main table exists in the plurality of updated data tables and the data update type corresponding to the preset main table is newly added data, the table field of the preset main table is used as an index field of a newly added index to be inserted into an index table of a search engine according to the first synchronous mode;
and merging the table field of an additional table into the index field of the newly added index, wherein the additional table is other data tables except a preset main table in the plurality of updated data tables.
Optionally, the processor 1001 may call a data query program stored in the memory 1005, and further perform the following operations:
Determining a target index from indexes of the search engine according to unique table fields with association among the plurality of updated data tables;
When a preset main table exists in the plurality of updated data tables and the data update type corresponding to the preset main table is data modification, updating the table field of the preset main table to the target index according to the second synchronous mode;
The table fields of the additional table are merged into the index field of the updated target index.
Optionally, the processor 1001 may call a data query program stored in the memory 1005, and further perform the following operations:
And merging the table fields of each additional table into the index field of the target index according to the third synchronous mode when the preset main table does not exist in the plurality of updated data tables.
Optionally, the processor 1001 may call a data query program stored in the memory 1005, and further perform the following operations:
Determining an index taking the unique table field as a main key from indexes of a search engine according to the unique table field;
And determining an index taking the unique table field as a main key in the search engine as a target index.
Optionally, before the step of acquiring the binary log data of the database, the processor 1001 may call a data query program stored in the memory 1005, and further perform the following operations:
determining a data structure corresponding to the index in the search engine;
Converting the data structures of the plurality of updated data tables into data structures corresponding to indexes;
And merging the table fields of the plurality of updated data tables into index fields of the same index according to the converted data structure.
Referring to fig. 2, fig. 2 is a flowchart of a first embodiment of a data query method according to the present invention, where the data query method includes the following steps:
Step S10: acquiring data updating information of each data table in a database;
In this embodiment, in order to improve the data query efficiency, the fields of multiple data tables in the database may be combined into the index field of the same index in the search engine, that is, the user may quickly query the data in the multiple data tables only by inputting any one of the index fields, without multiple queries through the index and the database, that is, without associating multiple data tables to perform a continuous table query, or without combining multiple data tables into the same data table, so as to avoid increasing the load of the database. However, when the data table in the database is updated, the table field of the data table is updated, and the corresponding index in the search engine is updated, so that the purpose of directly inquiring the data in the multiple data tables through the index of the search engine can be achieved. Therefore, before merging the table fields of multiple data tables, it is necessary to monitor the data change state in the database to obtain the data update information of each data table in the database.
Wherein the database may be mysql database, which is an open source relational database management system (RDBMS) that uses the most commonly used database management language-Structured Query Language (SQL). Moreover, mysql databases typically use binary logs (binlog) to record SQL statements that users update the database, e.g., both the SQL statements that change the database table and the contents of the database table are recorded in binglog, except for data query statements. Therefore, in order to obtain the data update information of each data table in the database, the system background can disguise as a secondary database of the mysql database and monitor the change of the binary log data of the main database, when the change of the binary log data of the main database is monitored, the data update exists in the database, and as the main database pushes the binary log to the secondary database (namely, the system background), the system background can obtain the binary log file pushed by the main database, analyze the data in the binary file and obtain the data update information of each data table in the database, wherein the data update information can comprise newly added data, data modification, deleted data and the like.
In addition, the binary log (binlog) of mysql database typically has three formats, STATEMENT, ROW and Mixedlevel. The state format records the sql Statement of each piece of modified data in the binlog, and the change of each line is not required to be recorded, so that the binlog log quantity can be effectively reduced and IO (input/output) is saved; the Row format can not record the information related to the context of the executed sql statement, only needs to record what is modified by that record, the log content can clearly record the details of each Row of data modification, and the problem that the storage process or function under certain specific conditions, and the calling and triggering of the trigger cannot be copied correctly can not occur; in Mixedlevel format, general statement modification uses statment format to save binlog, such as some functions, and when a state cannot complete the master-slave copy operation, the binlog is saved in row format. Therefore, in order to acquire detailed data modification data and ensure the validity of data record, before acquiring the binary log data of the database, the binary log of the database is preferably configured into a row format to acquire the binary log data in the row format, so that the problems that the binary log data cannot be copied correctly and the like are avoided.
In addition, in order to avoid that the load of the search engine is excessive when the data volume is excessive, and the data query efficiency is affected, the search engine in the embodiment may be a distributed search engine. Therefore, when the data volume is too large, the data can be distributed to a plurality of nodes of the distributed search engine for storage, and the influence on the search performance of the distributed search engine is avoided. Compared with the traditional database, the distributed search engine, such as ELASTICSEARCH search engine, adopts distributed search instead of traversing search, adopts inverted index instead of B+ tree index, has higher search speed, supports slicing and copying, and has high expansibility and high real-time performance. That is, the distributed search engine has the characteristics of real-time, stability, reliability, rapidness and the like when searching data, and fields of a plurality of data tables in the database can be preferably combined into the same index of the distributed search engine, and the distributed search engine can be preferably ELASTICSEARCH search engine.
Step S20: if a plurality of updated data tables exist according to the data updating information, and associated unique table fields exist among the updated data tables, merging the table fields of the updated data tables into an index field of the same index, wherein the updated data tables are data tables with data updating in a database;
After binary log data of data update information of data tables in a record database is acquired by a system background, the binary log data is analyzed, so that the data update information of each data table in the database can be obtained, and according to the data update information, on one hand, the data modification of the data tables, the data addition of the data tables, or the deletion of the data tables, namely the data update of the data tables, can be determined; on the other hand, table fields of different data tables can be obtained through analysis, and then the unique table field associated with the associated data tables is determined according to the table fields corresponding to the different data tables.
Then, after the data update information of each data table in the database is obtained, whether an updated data table with data update exists in the database can be determined according to the data update information, if so, when a plurality of updated data tables exist, whether the correlated unique table fields (unique keys) exist among the plurality of updated data tables is further determined, for example: if the fields of the table a are A0, A1, A2, and the fields of the table B are A0, B1, B2, then the table a and the table B have an associated unique key A0, and at this time, it is determined that there are associated unique table fields between the plurality of updated data tables. If the unique table fields related to each other exist among the plurality of updating data tables, the plurality of updating data tables are related data tables corresponding to the same service, and at the moment, the table fields of the plurality of updating data tables can be combined into index fields of the same index. That is, when there are multiple update data tables in the database and there are associated unique table fields between the multiple update data tables, the table fields of the multiple update data tables may be merged into an index field of the same index.
If only one updated data table exists in the database according to the data updating information, when an index corresponding to the updated data table exists in the index of the search engine, the table field of the updated data table can be directly combined to the index field of the index corresponding to the updated data table; when the index corresponding to the updated data table does not exist in the index of the search engine, the table field of the updated data table can be directly inserted into the index table of the search engine as a new index field.
If it is determined that there are a plurality of update data tables in the database according to the data update information, but there is no inter-related unique key between the plurality of update data tables, there is a possibility that there is a portion of inter-related unique key between the plurality of update data tables, and there is no inter-related unique key between the portion of update data tables, at this time, table fields of the update data tables having the inter-related unique key may be merged into an index field of the same index in the search engine, and for the update data tables having no inter-related unique key, it may be referred to a case where there is only one update data table, and when there is an index corresponding to the update data table in the index of the search engine, the table fields of the update data tables may be merged directly into the index field of the index corresponding to the update data table, or when there is no index corresponding to the update data table in the index of the search engine, the table fields of the update data tables may be directly inserted into the index table of the search engine as new index fields. Of course, it is also possible that there is no unique key associated with each other between all of the plurality of updated data tables. At this time, it is also possible to refer to the case where only one updated data table exists, and the description thereof will not be repeated here.
The specific merging process may be: the method comprises the steps of reserving unique keys of a plurality of updated data tables, merging table fields of the updated data tables, merging the merged table fields into index fields of corresponding indexes in a search engine according to the unique keys, or merging the table fields of the updated data tables, taking the merged table fields as the index fields, taking the unique keys as index main keys, and directly inserting the table fields into the index tables of the search engine. Of course, priority may be set for each update data table in advance, so that multiple update data tables are sequentially combined into index fields of the same index in the search engine according to preset priority, for example, priority corresponding to different data tables may be set according to data insertion order, priority corresponding to different data tables may be set according to data update type, or priority corresponding to different data tables may be set according to data insertion order and update type, for example, a data table in which data is inserted first may be designated as a main table, then preset priority may be determined according to the main table and data update type corresponding to the main table, and multiple update data tables may be sequentially combined into index fields of the same index in the search engine according to preset priority. Of course, the master table may be the data table into which the data is inserted first, or may be the data table with the largest table field, etc., and may be set according to specific application requirements, which is not limited herein.
Step S30: and querying the data in the updated data tables from the search engine based on the combined index fields.
After merging the table fields of the multiple update data tables with the correlated unique table fields into the index fields of the same index, a user can search and acquire the data in the corresponding update data table in the multiple update data tables from the search engine only by inputting any field in the index when inquiring or acquiring the data.
In this embodiment, whether an update data table exists in a database is determined by acquiring data update information of each data table in the database, where the update data table exists in the data table, and if it is determined that a plurality of update data tables exist according to the data update information and associated unique table fields exist among the plurality of update data tables, table fields of the plurality of update data tables are combined into an index field of the same index; and querying the data in the updated data tables from the search engine based on the combined index fields. That is, by merging the table fields of the plurality of updated data tables with associated unique table fields into the same index of the search engine, a user only needs to query any field in the index for one time to query corresponding data, and does not need to merge the data of the plurality of data tables into the same data table or perform continuous table query, so that the problem that the query efficiency is low due to the fact that the continuous table query needs to be performed for multiple times or the data of the plurality of data tables are merged into the same data table, the data table is overlarge, the data reading and writing performance of the database is influenced, the data is inconvenient to query quickly, and the query efficiency of the data query is improved.
Referring to fig. 3, fig. 3 is a flowchart of a second embodiment of the data query method according to the present invention, in this embodiment, the data query method includes the following steps:
step S11: acquiring data updating information of each data table in a database;
Step S12: if a plurality of updated data tables exist according to the data updating information and associated unique table fields exist among the updated data tables, determining the data table type and the data updating type corresponding to each updated data table according to the data updating information;
Step S13: determining an index synchronization mode according to the data table type corresponding to each updated data table and the data updating type;
step S14: merging the table fields of each updated data table into index fields of the same index according to the determined index synchronization mode;
Step S15: and querying the data in the updated data tables from the search engine based on the combined index fields.
In this embodiment, after obtaining the data update information of each data table in the database, the data table type and the data update type corresponding to each updated data table may be determined according to the data update information, where the data update information may include attribute information (such as a table name and a table field) of the data table in which data update exists and update information of the data table (such as a data update type of the data table in which data update exists, where the data update type may be new, modified or deleted). Then, the data table type corresponding to each updated data table can be determined according to the attribute information of the data table, and the data table type can be divided according to the data insertion sequence or the data update amount; and determining the data update type corresponding to each updated data table according to the update information of the data table. Then, according to the data table type and the data update type corresponding to each update data table, a corresponding index synchronization mode can be determined, specifically, index synchronization rules corresponding to different data table types and data update types can be preset, and based on the data table type and the data update type corresponding to each update data table, the index synchronization rules corresponding to each update data table are determined from the preset index synchronization rules. The table fields of each updated data table are then merged into index fields of the same index in the distributed search engine according to the determined index synchronization rules. The index synchronization rule may be: the new index is directly inserted into the search engine, or the index corresponding to the unique key in the search engine is updated based on the unique key associated between each updated data table.
Specifically, in an embodiment, before merging table fields of a plurality of update data tables, a data table in which data is inserted first is configured as a main table in advance, other data tables except a preset main table in the plurality of update data tables are used as additional tables, and a unique key associated among the plurality of update data tables is used as a main key of a corresponding index of a search engine. Then, according to the table type (main table or additional table) and the data update type (new and modified) corresponding to the different data tables, the scheme for determining the index synchronization mode corresponding to the different updated data tables may be: firstly, judging whether a preset main table exists in the plurality of updated data tables or not and whether the data update type corresponding to the preset main table is newly added data or not according to the data table type and the data update type corresponding to each updated data table. If a preset main table exists in the plurality of updated data tables and the data update type corresponding to the preset main table is newly added data, determining that the index synchronization mode is a first synchronization mode; if a preset main table exists in the plurality of updated data tables and the data update type corresponding to the preset main table is modified data (including deleted data), determining that the index synchronization mode is a second synchronization mode; if the preset main table is not existed in the plurality of updated data tables and only the additional table is existed, determining that the index synchronization mode is a third synchronization mode. The first synchronization method may be that a table field of a preset main table is first used as an index field of a new added index to be inserted into an index table of a search engine, and then the table fields of each additional table are combined into the inserted index field of the new added index; the second synchronization method may be that the table fields of the preset main table are first merged into the index fields of the corresponding indexes in the search engine, and then the table fields of the additional table are merged into the index fields of the indexes; the third synchronization means may be to directly merge the table fields of the respective additional tables into the index field of the target index.
In addition, since the data structures of the data tables stored in the database are inconsistent with the data structures of the indexes in the index table, if the table fields of the plurality of updated data tables are to be combined into the index fields of the same index, the data structures corresponding to the indexes in the search engine are determined first, then the row records of each updated data table are analyzed, the fields in each updated data table are organized, so that the data structures in the updated data tables are changed into the data structures in the index table, and then the table fields of the plurality of updated data tables are combined into the index fields of the same index according to the converted data structures. In the data structure corresponding to the index, the fields of different updated data tables are different, but the primary keys are the same. The data structure corresponding to the index can be a Hash structure, a binary search tree, a red black tree, a B tree, a B+ tree and the like, and the data structure in the database can be a linear data structure, a tree data structure, an aggregate data structure, a graph data structure and the like.
When a preset main table exists in the plurality of updated data tables and the data update type corresponding to the preset main table is newly added data, the data structure of each updated data table can be converted into a data structure corresponding to an index, then the table field of the preset main table is firstly used as an index field of the newly added index to be inserted into an index table of a search engine according to the first synchronous mode, and then the table fields of the additional tables are combined into the index field of the newly added index; when a preset main table exists in the plurality of updated data tables and the data update type corresponding to the preset main table is data modification, a target index can be determined from indexes of a search engine according to a unique table field with association among the plurality of updated data tables, then the data structure of each updated data table is converted into a data structure corresponding to the index, then the table field of the preset main table is updated to the target index according to the second synchronization mode, and the table field of an additional table is combined to the index field of the updated target index; when the preset main table does not exist in the plurality of updated data tables, a target index is determined from indexes of the search engine according to unique table fields with association among the plurality of updated data tables, then the data structure of each updated data table is converted into a data structure corresponding to the index, and then the table fields of each additional table are combined into the index field of the target index according to the third synchronization mode. Wherein, because the unique key of the correlation between the plurality of updating data tables is used as the main key of the index after merging in advance, the step of determining the target index from the index of the search engine according to the unique table field with the correlation between the plurality of updating data tables can be: determining unique keys among a plurality of data tables, determining an index taking the unique table field as a main key from indexes of a search engine according to the unique table field, and determining the index taking the unique table field as the main key in the search engine as a target index.
The sequence of the step of converting the data structure of the plurality of updated data tables into the data structure corresponding to the index and the step of determining the target index from the index of the search engine according to the unique table field with the association between the plurality of updated data tables is not limited, and the step may be performed sequentially or simultaneously, may be set according to specific application requirements, and is not limited herein.
According to the embodiment, data updating information of each data table in a database is obtained, when a plurality of updated data tables are determined to exist according to the data updating information and associated unique table fields exist among the plurality of updated data tables, a data table type and a data updating type corresponding to each updated data table are determined according to the data updating information, an index synchronization mode is determined according to the data table type corresponding to each updated data table and the data updating type, table fields of each updated data table are combined into index fields of the same index according to the determined index synchronization mode, and then data in the plurality of updated data tables are queried from a search engine based on the combined index fields. That is, by merging the table fields of each updated data table into the index field of the same index in the sub-search engine according to the determined index synchronization method, not only can the problem of low data query efficiency caused by multiple queries required when querying data in multiple data tables by means of continuous table query or merging data in multiple data tables into the same data table, etc. be avoided, the data query efficiency is improved, but also the merging and synchronization are performed according to the determined index synchronization method, and the data query efficiency can be further improved.
In addition, the embodiment of the invention also provides a data query system, which comprises a memory, a processor and a data query program stored on the processor and capable of running on the processor, wherein the data query program is executed by the processor to realize the steps of the data query method.
In addition, the embodiment of the invention also provides a computer readable storage medium, wherein the computer readable storage medium stores a data query program, and the data query program realizes the steps of the data query method when being executed by a processor.
It should be noted that, in this document, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or system that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or system. Without further limitation, an element defined by the phrase "comprising one … …" does not exclude the presence of other like elements in a process, method, article, or system that comprises the element.
The foregoing embodiment numbers of the present invention are merely for the purpose of description, and do not represent the advantages or disadvantages of the embodiments.
From the above description of the embodiments, it will be clear to those skilled in the art that the above-described embodiment method may be implemented by means of software plus a necessary general hardware platform, but of course may also be implemented by means of hardware, but in many cases the former is a preferred embodiment. Based on this understanding, the technical solution of the present invention may be embodied essentially or in a part contributing to the prior art in the form of a software product stored in a storage medium (e.g. ROM/RAM, magnetic disk, optical disk) as described above, comprising instructions for causing a terminal device (which may be a mobile phone, a computer, a server, a television, or a network device, etc.) to perform the method according to the embodiments of the present invention.
The foregoing description is only of the preferred embodiments of the present invention, and is not intended to limit the scope of the invention, but rather is intended to cover any equivalents of the structures or equivalent processes disclosed herein or in the alternative, which may be employed directly or indirectly in other related arts.
Claims (5)
1. A data query method, characterized in that the data query method comprises the steps of:
Acquiring data updating information of each data table in a database;
if a plurality of updated data tables exist according to the data updating information, and associated unique table fields exist among the updated data tables, determining the data table type and the data updating type corresponding to each updated data table according to the data updating information, wherein the updated data tables exist data updated data tables in a database;
Judging whether a preset main table exists in the plurality of updated data tables or not and whether the data update type corresponding to the preset main table is newly added data or not according to the data table type and the data update type corresponding to each updated data table;
When the preset main table exists in the plurality of updated data tables and the data update type corresponding to the preset main table is newly added data, the table field of the preset main table is used as an index field of a newly added index to be inserted into an index table of a search engine according to a first synchronous mode;
Merging the table field of an additional table into the index field of the newly added index, wherein the additional table is other data tables except the preset main table in the plurality of updated data tables;
Determining a target index from indexes of the search engine according to unique table fields with association among the plurality of updated data tables;
when the preset main table exists in the plurality of updated data tables and the data update type corresponding to the preset main table is data modification, updating the table field of the preset main table to a target index according to a second synchronous mode;
Merging the table field of the additional table into the index field of the updated target index;
When the preset main table does not exist in the plurality of updated data tables, merging the table fields of each additional table into the index field of the target index according to a third synchronous mode;
and querying the data in the updated data tables from the search engine based on the combined index fields.
2. The data query method of claim 1, wherein the step of determining a target index from the index of the search engine based on the unique table field for which there is an association between the plurality of updated data tables comprises:
Determining an index taking the unique table field as a main key from indexes of a search engine according to the unique table field;
And determining an index taking the unique table field as a main key in the search engine as a target index.
3. The data query method according to any one of claims 1 to 2, wherein before the step of determining a data table type and a data update type corresponding to each updated data table according to the data update information, the method comprises:
determining a data structure corresponding to the index in the search engine;
And converting the data structures of the plurality of updated data tables into data structures corresponding to indexes.
4. A data query system comprising a memory, a processor and a data query program stored on the memory and executable on the processor, the processor implementing the steps of the data query method of any of claims 1-3 when the data query program is executed.
5. A computer-readable storage medium, characterized in that the computer-readable storage medium has stored thereon a data query program, which, when executed by a processor, implements the steps of the data query method according to any of claims 1-3.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011377046.7A CN112506964B (en) | 2020-11-30 | 2020-11-30 | Data query method, system and computer readable storage medium |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202011377046.7A CN112506964B (en) | 2020-11-30 | 2020-11-30 | Data query method, system and computer readable storage medium |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN112506964A CN112506964A (en) | 2021-03-16 |
| CN112506964B true CN112506964B (en) | 2024-05-28 |
Family
ID=74969609
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202011377046.7A Active CN112506964B (en) | 2020-11-30 | 2020-11-30 | Data query method, system and computer readable storage medium |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112506964B (en) |
Families Citing this family (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN113094370B (en) * | 2021-04-13 | 2024-06-04 | 北京精鸿软件科技有限公司 | Data index construction method and device, storage medium and electronic equipment |
| CN113254446B (en) * | 2021-05-26 | 2023-05-12 | 深圳壹账通智能科技有限公司 | Data fusion method, device, electronic equipment and medium |
| CN113553488A (en) * | 2021-07-15 | 2021-10-26 | 挂号网（杭州）科技有限公司 | Method and device for updating index data in search engine, electronic equipment and medium |
| CN113792079B (en) * | 2021-11-17 | 2022-02-08 | 腾讯科技（深圳）有限公司 | Data query method and device, computer equipment and storage medium |
| CN117194410B (en) * | 2023-07-13 | 2024-05-14 | 广州白码科技有限公司 | Method and system for generating business report by artificial intelligence language model |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US9092472B1 (en) * | 2013-06-25 | 2015-07-28 | Amazon Technologies, Inc. | Data merge based on logical segregation |
| CN105488050A (en) * | 2014-09-17 | 2016-04-13 | 阿里巴巴集团控股有限公司 | Database multi-index method, apparatus and system |
| CN107038162A (en) * | 2016-02-03 | 2017-08-11 | 滴滴（中国）科技有限公司 | Real time data querying method and system based on database journal |
| CN110019211A (en) * | 2017-11-27 | 2019-07-16 | 北京京东尚科信息技术有限公司 | The methods, devices and systems of association index |
| CN111782659A (en) * | 2020-07-10 | 2020-10-16 | 东北大学 | Database index creation method and device, computer equipment and storage medium |
Family Cites Families (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US8244700B2 (en) * | 2010-02-12 | 2012-08-14 | Microsoft Corporation | Rapid update of index metadata |
| US20140324875A1 (en) * | 2013-04-29 | 2014-10-30 | Keyw Corporation | Index for fast batch updates of large data tables |
| US10296650B2 (en) * | 2015-09-03 | 2019-05-21 | Oracle International Corporation | Methods and systems for updating a search index |
-
2020
- 2020-11-30 CN CN202011377046.7A patent/CN112506964B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US9092472B1 (en) * | 2013-06-25 | 2015-07-28 | Amazon Technologies, Inc. | Data merge based on logical segregation |
| CN105488050A (en) * | 2014-09-17 | 2016-04-13 | 阿里巴巴集团控股有限公司 | Database multi-index method, apparatus and system |
| CN107038162A (en) * | 2016-02-03 | 2017-08-11 | 滴滴（中国）科技有限公司 | Real time data querying method and system based on database journal |
| CN110019211A (en) * | 2017-11-27 | 2019-07-16 | 北京京东尚科信息技术有限公司 | The methods, devices and systems of association index |
| CN111782659A (en) * | 2020-07-10 | 2020-10-16 | 东北大学 | Database index creation method and device, computer equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN112506964A (en) | 2021-03-16 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN112506964B (en) | Data query method, system and computer readable storage medium | |
| CN109800222B (en) | HBase secondary index self-adaptive optimization method and system | |
| CN109299102B (en) | HBase secondary index system and method based on Elastcissearch | |
| US11567978B2 (en) | Hybrid structured/unstructured search and query system | |
| CN105630864B (en) | Forced ordering of a dictionary storing row identifier values | |
| US8924373B2 (en) | Query plans with parameter markers in place of object identifiers | |
| US8527556B2 (en) | Systems and methods to update a content store associated with a search index | |
| CN109766345B (en) | Metadata processing method and device, equipment and readable storage medium | |
| US20150169741A1 (en) | Methods And Systems For Eliminating Duplicate Events | |
| US20100281005A1 (en) | Asynchronous Database Index Maintenance | |
| CN106407360B (en) | Data processing method and device | |
| US20220083618A1 (en) | Method And System For Scalable Search Using MicroService And Cloud Based Search With Records Indexes | |
| US11030242B1 (en) | Indexing and querying semi-structured documents using a key-value store | |
| US8027969B2 (en) | Efficient calculation of sets of distinct results in an information retrieval service | |
| US9418154B2 (en) | Push-model based index updating | |
| US20110289112A1 (en) | Database system, database management method, database structure, and storage medium | |
| KR101544560B1 (en) | An online analytical processing system for big data by caching the results and generating 2-level queries by SQL parsing | |
| US9594784B2 (en) | Push-model based index deletion | |
| CN115168338A (en) | Data processing method, electronic device and storage medium | |
| US20110113052A1 (en) | Query result iteration for multiple queries | |
| CN116049193A (en) | Data storage method and device | |
| US8498987B1 (en) | Snippet search | |
| US8818990B2 (en) | Method, apparatus and computer program for retrieving data | |
| CN114153857A (en) | Data synchronization method, data synchronization apparatus, and computer-readable storage medium | |
| CN111753045A (en) | Hive secondary full-text index technical method and system based on elastic search |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |