CN111177148B

CN111177148B - Method for automatically building and dividing tables of hydropower database

Info

Publication number: CN111177148B
Application number: CN201911239181.2A
Authority: CN
Inventors: 陈意; 郑键兵; 花胜强; 蔡杰; 丁琳; 吴宁; 李宁宁; 向南; 周锡琅
Original assignee: Nanjing Nari Water Conservancy And Hydropower Technology Co ltd; Nari Technology Co Ltd
Current assignee: Nanjing Nari Water Conservancy And Hydropower Technology Co ltd; Nari Technology Co Ltd
Priority date: 2019-12-06
Filing date: 2019-12-06
Publication date: 2023-03-24
Anticipated expiration: 2039-12-06
Also published as: CN111177148A

Abstract

The invention discloses a method for automatically building and dividing tables of a hydropower database by utilizing a virtual database technology. The method constructs a virtual logic database based on a standard database bottom layer communication protocol, filters, classifies, distinguishes and analyzes sql sentences by intercepting access data requests of database sessions, achieves automatic creation and horizontal data segmentation of database sub-tables, and ensures that single-table data of the hydropower database can be uniformly distributed according to point numbers, time and other attributes. The method of the invention greatly improves the reading and writing speed of the big data list table, simplifies the data access logic of the service system and has low cost. The method can customize the sub-table algorithm of various strategies according to the field engineering environment and the appointed table field, dynamically and automatically convert the single-table request of the service system into the multi-table request, and avoid the performance reduction caused by overlarge single-table data of the database.

Description

Method for automatically building and dividing tables of hydropower database

Technical Field

The invention relates to large-scale measurement data storage and access in scientific research such as water conservancy and power generation professional big data storage, in particular to a method for automatically building tables and sub-tables in a hydropower database.

Background

With the development of intelligent hydroelectric power plants, the magnitude of data is exponentially increased from GB to TB to PB. Various operations on data are increasingly difficult, and the traditional relational database cannot meet the requirements of fast query and data insertion. The use scene of the hydropower business absolutely needs to have affairs and safety indexes. NoSQL is certainly not satisfied at this time, so a relational database is still needed.

At present, commercial databases or open source databases used by hydropower systems generally do not support large-scale automatic table building expansion, and a performance bottleneck exists on a single table. When the general database list exceeds 1000W-2000W records, the performance is obviously reduced. In order to improve the performance and the amount of data that can be stored, the sub-tables need to be performed according to engineering characteristics, various professional characteristics of water and electricity, and the like.

For tables with large data size (more than ten million levels), the performance of the relational database is greatly reduced, so that the size of each table is controlled to be in the million level. For a table with a large data size, it can be considered that the records are put into different data tables through fragmentation according to a certain rule. Thus, the data amount of each data table is not too large, and the performance is not greatly lost.

Database sharding refers to: the data stored in one database table is dispersedly stored in different data tables under a certain specific condition, so that the volume of a single table is dispersed, and after the database table is fragmented, the data is dispersed into a plurality of data tables from one data table. At this time, when the system needs to query, different data tables need to be switched to query, so how the system knows in which data table the data to be queried is, and into which data table the data needs to be inserted when a record is added.

Disclosure of Invention

The invention aims to show a method for automatically building tables and sub-tables in the hydropower industry, which is low in cost, high in efficiency and capable of being infinitely expanded by utilizing a virtual database technology. The virtual database technology is to construct a logical database, which provides undifferentiated database access service by using a standard database communication protocol. The data access layer of all the hydropower service components and modules uniformly accesses the logic library, and the virtual logic library is used for uniformly managing the access and routing of the data sub-table.

The method can analyze various database requests submitted by the hydropower business, accurately routes all the requests to different sub-tables for real database access, combines and sorts the access results according to the request types and feeds back the access results to the hydropower business end, and the hydropower business end does not need to know the details of the bottom database at all.

The technical scheme of the invention is as follows:

a method for automatically building and sub-listing a hydropower database comprises the following steps:

1) The front end of the virtual database establishes database service interception according to a mysql database standard bottom layer communication protocol, the rear end establishes a physical database connection thread pool according to real database connection information, a user name and a password, and the hydropower service system is connected to the virtual database according to real mysql link information;

2) A data access layer of the hydropower business system submits an sql request to a virtual database, the virtual database monitors an sql request statement, the sql request statement is analyzed, a preset sub-table name and a sub-table algorithm are compared, and whether the sql request statement needs to be subjected to sub-table processing or not is checked; if the statement does not need to be subjected to table division processing, directly submitting the request to a back-end physical database;

3) Analyzing a statement query condition for an sql request statement needing to be subjected to table division processing, calculating a sub-table name related to the statement according to a preset table division algorithm, and classifying according to statement types, sequences and groups;

4) Inquiring the sub-table cache according to the name of the sub-table, checking whether the sub-table exists in the physical database, if not, establishing a corresponding sub-table in the physical database, if so, indicating that the sub-table is established, entering the step 5), and submitting a request for the sub-table;

5) Amending the submitted sql request statement, splitting the sql request statement into a plurality of sub-table requests, asynchronously submitting all the requests to a physical database for execution, and placing and caching the returned result of each sub-table request in a result set; waiting for all the sub-table requests to be completed, and entering the step 6) to merge the result sets;

6) Merging all the sub-query result sets according to the categories sorted in the step 3), and returning the merged result sets to the hydropower service system according to a standard mysql protocol.

Further, in step 2), the method for processing the sub-tables comprises: and processing the search condition, the sub search condition and the sub table field value of the input sql request statement by an sql parser to perform cache analysis, circularly calculating the sub table range, and performing sub table statement execution on all the fragments in parallel after determining all the fragment nodes.

Further, the content of the table splitting processing comprises defining table splitting rules to realize splitting, each table binds one splitting rule, and each splitting rule specifies one splitting field and binds one function to realize a dynamic splitting algorithm.

Further, when the logic library for table splitting receives an SQL request statement, the SQL request statement is parsed, the related table is searched, then the definition of the table is checked, if a fragmentation rule exists, the value of a fragmentation field in the SQL request statement is obtained, a fragmentation function is matched, a fragmentation list corresponding to the SQL request statement is obtained, then the SQL request statement is sent to the fragments for execution, and finally, result data returned by all the fragments is collected and processed and output to the client.

Further, in step 4), the step of creating the sub-table is: and presetting a sub-table creation script template, and when the sub-table does not exist, directly imitating the main table to copy and create the sub-table.

Further, the constraints and triggers of the main table are copied to the sub-tables, and the related constraints and triggers are automatically created according to the template.

Further, the merging process in step 6) includes reordering and grouping.

Furthermore, a thread pool is defined in the virtual database, connection with a real physical database is established, and when a plurality of sql request statements request processing, a plurality of sessions and the physical database are concurrently established for communication.

Furthermore, a table division configuration center in the virtual database is constructed with a table division algorithm registration center, and directly participates in table names and field names of the table division and implementation type paths of the designated table division algorithm.

The invention has the advantages that:

compared with the prior database and table dividing method, the method is mainly improved in the following aspects:

1. the database service based on the standard database communication protocol is provided, the details of the database sub-tables are shielded for the client, the decoupling of the database sub-tables and the application service data access is realized, and the complexity of the application program data access is greatly reduced.

2. The script template is constructed based on the predefined base table, so that the automatic generation of the base table, the related constraint of the base table and the trigger is realized, and the automatic infinite expansion of the base table can be realized theoretically.

3. Asynchronous field sql submission is fully utilized, and the data query speed is greatly improved.

4. By proxy interception, the sub-table algorithm is independent of data access logic, and the sub-table strategy can be developed secondarily according to the current situation of the project, so that the personalized sub-table requirement is fully met, and the development cost is reduced.

Drawings

FIG. 1 is a schematic sub-table illustration of the present invention;

FIG. 2 is a schematic diagram of the slicing rule according to the present invention;

FIG. 3 is a view of the frame assembly of the present invention;

fig. 4 is a flow chart of the operation of the present invention.

Detailed Description

The method for automatically building tables and sub-tables of the hydropower database by utilizing the virtual database technology comprises the following steps as shown in figure 4:

1) The front end of a virtual database program establishes database service interception according to a mysql database standard bottom layer communication protocol, the rear end establishes a physical database connection thread pool according to real database connection information, a user name, a password and the like, and a hydropower service system is connected to a virtual database according to real mysql link information.

2) And the data access layer of the hydropower service system submits an sql request of the database to the virtual library, the virtual library monitors the sql request, analyzes the sql statement, compares the preset table name and table algorithm and checks whether the sql statement needs to be routed and fragmented. And if the statement does not need to be subjected to table division processing, directly submitting the request to a back-end physical database.

3) Analyzing the query condition of the statement for the sql statement needing to be subjected to table division, calculating the name of a sub-table related to the statement according to the table division algorithm of the project, and classifying the statement types, the ranks, the groups and the like

4) And inquiring the sub-table cache, checking whether the sub-table exists in the physical database, if not, constructing a corresponding sub-table in the physical database, copying the constraint of the main table and the trigger of the main table to the sub-table and renaming the sub-table. If the sub-table exists, the sub-table is created, and step 5 is entered, and request submission is carried out on the sub-table.

5) And correcting the originally submitted sql request, splitting the sql request into a plurality of sub-table requests, asynchronously submitting all the requests to a physical library for processing, and placing a returned result of each sub-table request into a cache result set. And waiting for all the sub-table requests to be completed, and entering the step 6 result set and merging.

6) And all the sub-query result sets are merged according to the operation types sorted in the third step, the merging process comprises re-sorting, grouping and the like, and the merged result sets are returned to the calling party of the hydropower service system according to the standard mysql protocol.

The principle of the water regimen water regulation system database table division is that SQL sentences sent by client application are intercepted, and certain specific analysis is firstly carried out on the SQL sentences: such as fragment analysis, route analysis, read-write separation analysis, cache analysis, etc., then sending the SQL to a real database at the back end, and properly processing the returned result, and finally returning to the user.

The system is used for logic library of sub-list, it is a Server which realizes MySQL protocol, the front end user can regard it as a database agent, it uses MySQL client tool and command line to access, and its back end can also use JDBC protocol to communicate with most main stream database Server. The core function of the method is table splitting, namely, a large table is horizontally split into N small tables which are stored in a back-end physical database, as shown in fig. 1.

The core content of the water regime system database table is to realize fragmentation by defining the fragmentation rule of the table, each table can bind one fragmentation rule, and each fragmentation rule specifies one fragmentation field and binds one function to realize a dynamic fragmentation algorithm, as shown in fig. 2. When the logic base service receives an SQL, the SQL is firstly analyzed, a related table is searched, then the definition of the table is seen, if the fragmentation rule exists, the value of the fragmentation field in the SQL is obtained, the fragmentation function is matched, a fragmentation list corresponding to the SQL is obtained, then the SQL is sent to the fragments to be executed, and finally, the result data returned by all the fragments are collected and processed and output to the client.

Database sub-tabulation to application non-intrusive:

many open-source sub-table schemes are currently on the market that are referenced by clients by doing some lightweight framework packages. And the dynamic data source and the dynamic fragmentation rule are realized by combining with a dynamic configuration center of the framework. The sharding framework tells the application which database should be accessed and what the sharding rules are by analyzing sql requests and querying the configuration center. And finally, the application accesses the database by itself, and carries out fragmentation and combination results.

In this way, although the pressure of realizing fragmentation and result aggregation is distributed to each application, the difficulty of table-division routing and result merging is reduced, the method has invasiveness to the service application system codes, couples service development with the database-division table management of the background database, makes an application program very heavy, and is not beneficial to development and management because once problems occur, the application end is difficult to locate the problems.

And adding a database routing middleware (equivalent to an agent) in the application layer and the database layer, disguising the application layer and the database layer into a mysql database, realizing a mysql protocol stack, and managing all mysql instances in a background. The application is unaware, only when the background is just one mysql instance. The proxy mysql example realizes functions of library and table division, result combination and the like. The background database is managed in a unified mode, problems can be located in time, application is not invasive, and pressure is applied to the middleware.

Stack cache based sql conditional analysis:

data of the regimen water-conditioning system has timeliness, and the acquisition frequency of various types of measurement data is not uniform, so that the method of dividing the table according to the time type is the most common table dividing method. All sql requests for conditional query according to time are extremely complex, and how to perform table-splitting routing analysis for conditions becomes a difficult problem. Currently, the table-splitting syntax analysis of the open source, such as TDDL, cannot perform table splitting according to time, is limited to perform routing analysis lookup on a specific type of table-splitting field, and the mycat performs only between single-operation analysis on a time field.

The method comprises the steps of analyzing an SQL request by a Druid SQL parser to generate a RANGE set, establishing two cache condition stacks of AND AND OR aiming at each sub-query, respectively placing sub-table conditions analyzed by each sub-query in the two stacks according to types, taking an intersection RANGE from the RANGE set placed in the AND stack, taking a union RANGE from the RANGE set placed in the OR stack, AND finally generating a result RANGE set by all the sub-queries according to a combination rule.

And taking the RANGE set as the input of a table-splitting algorithm, performing table-splitting routing calculation to finally form a routing node set, submitting an sql request to each node by multithreading of a background physical end, and finishing database query.

An automatic table building mechanism:

with the improvement of the management level of the water regime data, the requirement of the system on the data storage capacity is higher and higher. At present, the newly constructed minor wave bottom scheduling system requires data to be stored in a warehouse according to the second level, so that the expansion of the database cannot be defined according to the traditional predefined sub-table number. For example, the database sub-table is typically predefined as N. If N is set large enough, the range traversed by each routing computation loop may be large, seriously affecting computation efficiency. If the setting is too small, post-maintenance becomes very complicated when the sub-tables are used up. This problem can only be solved once and for all if the automatic sub-table expansion mode is set.

The virtual database analyzes and intercepts the acquired sql request, a target sub-table to be used is obtained through calculation according to a specified sub-table algorithm, if the number of the sub-table is larger than the number of the existing sub-tables in the database, a new sub-table is generated through copying according to the structure of the main table, and according to sub-table constraint and trigger templates customized on an engineering site, relationship constraint is established for the newly generated sub-table and a trigger is constructed, so that the sub-table is ensured to have the function of the main table completely.

Multi-threaded sql requests:

although the project-designed sub-table algorithm tries to concentrate most of the queries into one or two sub-tables, it is inevitable to generate a combined query across multiple sub-tables. In this case, a sql statement request is usually split into multiple statements, which becomes inefficient if it waits for all statements to execute in series.

The invention defines the thread pool in the virtual database layer to establish connection with the real physical database, and when a plurality of branch table statements request processing, a plurality of sessions and the physical database are constructed for communication. And finally merging the query results of each thread in the virtual library, and returning the processing result set to the application client.

Secondary development of a sub-table algorithm is supported:

due to the difference of various project management in the hydropower industry, the data storage mode and the data acquisition characteristics are different, so that the individual table distribution method can be quickly customized according to the situation of a project site at any time. For example, the frequency of data collected by a hydropower station A is fixed, but the types of stations are various, and the tables need to be sorted according to measuring points; the type of the acquisition device of the hydropower station B is fixed, but the acquisition frequency of various acquisition devices is different, and the sub-table is required to be performed according to time, so that a sub-table algorithm cannot be directly defined in the middle layer of the virtual pool obviously.

The invention constructs a table-division algorithm registration center in the virtual library table-division configuration center, and can directly participate in table names and field names of the table-division and the realization class path of the appointed table-division algorithm in the algorithm configuration. Usually, several common table division algorithms can be realized by default for selection, and when the personalized table division requirements appear, the algorithm can be registered in a table division configuration center only by developing the corresponding algorithm according to a specific interface, so that the personalized table division method is realized.

The whole method of the invention is carried out on the established virtual database platform. On the platform, an application end can develop own application service according to an access mode of a traditional relational database, submitted sql requests only need to know virtual main table names, then the platform can automatically perform table-splitting routing for the application end, and send reshaped sql requests to a real database in a background for processing.

The platform can fully analyze the sql sentences submitted from the application terminal, analyze the table names, the field names, the query conditions and the like in the sentences, automatically create sub-tables according to a table division algorithm specified by a configuration center, search routes, and finally merge processing results and return the processing results to the application terminal.

The whole virtual library platform is divided into three processing layers: a front-end protocol layer, a resolution routing layer and a back-end access layer. The whole structure is shown in fig. 3:

the front-end protocol layer realizes a special communication layer and is responsible for providing database services of standard database protocols for the application end, and the front-end protocol layer is responsible for constructing a connection pool, and submitting the sql request submitted by the application end to an analysis routing layer for analysis and processing after preliminary processing and optimization.

The parsing routing layer analyzes syntax of sql statements submitted by the protocol layer, optimizes the statements according to query conditions, performs routing calculation on processing table objects, query conditions and the like according to a predefined routing table splitting algorithm, verifies sub-table nodes obtained through calculation, submits a sub-table creating request and then submits a plurality of sub-table query statements which are split respectively to the sub-table nodes if corresponding sub-tables do not exist, and the statements are sent to the back-end access layer to perform real database query. And the analysis routing layer is used for merging each sub-query result, and mainly relates to aggregation, sequencing and the like.

And the data access layer at the rear end establishes a database connection pool and executes real database query, and the query results are sent to the analysis routing layer for processing. The layer shields the underlying physical databases in standard jdbc connections.

The invention divides and routes the appointed database table according to the predefined table division strategy algorithm, and utilizes the real physical database link of the background to perform the actual database access and dynamic creation of the database table, thereby greatly improving the reading and writing speed of the big data list table, simplifying the data access logic of the business system and having low cost. Different from the database partitioning method of the prior database management system, the method can customize the partitioning algorithm of various strategies according to the field engineering environment and the appointed table field, dynamically and automatically convert the single-table request of the service system into the multi-table request, and avoid the performance reduction caused by the overlarge single-table data of the database. The range of useful data to be processed is further reduced by utilizing the point number rule or the timeliness characteristic of the hydropower data, and a platform foundation is provided for efficiently accessing the hydropower data. The service system can select to connect the virtual library or the physical library according to the actual situation of each project and the data size, and the service development of the service system is not interfered.

Claims

1. A method for automatically building and dividing tables in a hydropower database is characterized by comprising the following steps:

2) A data access layer of the hydropower service system submits an sql request to a virtual database, the virtual database monitors the sql request statement, the sql request statement is analyzed, a preset sub-table name and a sub-table algorithm are compared, and whether the sql request statement needs to be subjected to sub-table processing or not is checked; if the statement does not need to be subjected to table division processing, directly submitting the request to a back-end physical database;

6) Merging all the sub-query result sets according to the categories sorted in the step 3), and returning the merged result sets to the hydropower service system according to a standard mysql protocol;

the content of the table splitting processing comprises the steps of defining the table splitting rules to realize splitting, binding one splitting rule for each table, and assigning one splitting field and binding one function for each splitting rule to realize a dynamic splitting algorithm;

when a logic base for table division receives an SQL request statement, the SQL request statement is firstly analyzed, a related table is searched, then the definition of the table is checked, if a slicing rule exists, the value of a slicing field in the SQL request statement is obtained, a slicing function is matched, a slicing list corresponding to the SQL request statement is obtained, then the SQL request statement is sent to the slices to be executed, and finally, result data returned by all the slices are collected and processed and output to a client;

in step 4), the step of creating the sub-table is as follows: presetting a sub-table creation script template, and when the sub-table does not exist, directly simulating the main table to copy and create the sub-table structure;

and a sub-table configuration center in the virtual database is constructed with a sub-table algorithm registration center which directly participates in the table names and field names of the sub-tables and the realization class paths of the appointed sub-table algorithm.

2. The method for automatically building tables and sub-tables of the hydropower database according to claim 1, wherein in the step 2), the method for processing the tables is as follows: and processing the search condition, the sub search condition and the sub table field value of the input sql request statement by an sql parser to perform cache analysis, circularly calculating the sub table range, and performing sub table statement execution on all the fragments in parallel after determining all the fragment nodes.

3. The method of claim 1, wherein the constraints and triggers of the master table are copied to the sub-tables and the associated constraints and triggers are automatically created according to a template.

4. The method of automated tabulation of a hydro-electrical database as claimed in claim 1 wherein the merging process in step 6) includes reordering and grouping.

5. The method for automatically building tables and sub-tables for hydropower databases according to claim 1, wherein a thread pool is defined in the virtual database, a connection is established with a real physical database, and when a plurality of sql request statements are requested to be processed, a plurality of sessions are concurrently built for communication with the physical database.