TWI766757B - A system and a method for improving access performance of frequently used data and computer readable medium thereof - Google Patents

Abstract

The present invention provides a system and a method for improving access performance of frequently used data. The system includes a table data classifying module, an update frequency setting module, a sync-scheduling processing module, a table data converting module, a data key value producing module, a Structured Query Language (SQL) importing module, a language analyzing and converting module, an inquirement key-value producing module, a relational database, a Non-Structured Query Language (NoSQL) database, and an update record database to provide classification of the importance of table data in the relational database and set the update frequency. In this way, the system schedules regularly to convert the table data in the relational database to the NoSQL database, so as to achieve the integration of the relational database and the NoSQL database, and to improve the overall response performance of the application service. The present invention further provides a computer-readable medium for performing method of improving access performance of frequently used data.

Description

A system, method and computer readable medium for improving common data access performance

The present invention relates to a data access system, and more particularly, to a system, a method and a computer-readable medium for improving the performance of common data access.

Today's large-scale information systems often use high-end commercial relational databases to access key hot data (that is, commonly used data) in order to meet the needs of rapid and stable business growth. However, in order to increase profits, enterprises have gradually shifted the way of data storage from high-cost relational databases to NoSQL (Non-Structured Query Language) databases. By using NoSQL databases, it can not only save costs, but also reduce costs. Can greatly increase the data access speed.

The technical solution adopted in the prior art is to first determine whether the structured query language requirements can be converted into unstructured query language requirements, and if the structured query language requirements can be converted into unstructured query language requirements, the The query language requirements are converted into unstructured query language requirements, and the unstructured query language requirements are executed in the unstructured query language database (ie NoSQL database); if the structured query language requirements cannot be converted into unstructured query language requirements request, the structured query language request is executed in the structured query language database (relational database).

However, the conventional technology requires real-time synchronization of all table data between the relational database and the NoSQL database, so that the function of querying data can be provided for different query language requirements, and in order to maintain data consistency, the same data needs to be maintained at the same time. In the two databases, the operating cost and maintenance burden are greatly increased.

Therefore, how to overcome the difficulties faced by the conventional technology in the database has become an important issue for those skilled in the art.

In order to solve the above problem, the present invention provides a system for improving the access performance of common data, which includes: a table data classification module, which obtains plural table data to classify the importance of the plural table data, so as to generate the plural table data Corresponding plural classification data with importance types; an update frequency setting module for setting the update frequency according to the different importance of the plural table data, so as to generate an update frequency data; a synchronous scheduling processing module , which is to communicate and connect the table data classification module and the update frequency setting module to receive the complex classification data with important types from the table data classification module and the update frequency data of the update frequency setting module, and then from the table data classification module. The update frequency data matches the update frequency corresponding to the importance type of the plurality of classification data, so as to schedule and sequentially issue an instruction to perform the conversion operation of the plurality of table data; and a table data conversion module, which is a communication connection The synchronous scheduling processing module receives instructions of the conversion operation of the synchronous scheduling processing module in sequence, and then according to the instruction means to perform a conversion operation on the plural form data so as to store the converted plural form data in a database.

The present invention further provides a method for improving the access performance of common data, which includes: obtaining plural table data from a table data classification module, so as to classify the importance of the plural table data, so as to generate the corresponding data of the plural table data. Plural classification data with importance types; provided by an update frequency setting module to set the update frequency according to the different importance of the plurality of table data to generate an update frequency data; received from the table by a synchronous scheduling processing module The complex classification data with the importance type of the data classification module and the update frequency data of the update frequency setting module are used to match the update frequency corresponding to the importance type of the complex classification data from the update frequency data, so as to perform sorting. the program and sequentially issue instructions to perform the conversion operation of the plurality of table data; and a table data conversion module sequentially receives the instructions of the conversion operation of the synchronous scheduling processing module, and then according to the instruction, the plurality of table data A conversion operation is performed to store the converted plural form data in a database.

In one embodiment, the plural table data is stored in a relational database, and the converted plural table data is stored in a NoSQL (Non-Structured Query Language) database.

In one embodiment, it further includes an update record database, which stores the update records generated each time the conversion operation of the plurality of table data is performed, wherein, when the table data conversion module receives the synchronization schedule processing module After the instruction of the conversion operation issued by the team, the form data conversion module obtains the update record of the previous conversion of the plural form data from the update record database, so as to use the plural form data recorded in the update record. The content update range acquires the content of the plural table data for conversion operation.

In one embodiment, the form data conversion module is further connected to the form data classification module in communication to obtain the plural classified data, and then converts the corresponding plural form data according to the data format in the plural classified data to generate the plural form data. Converted plural form data.

In one embodiment, it further includes a data key-value production module, which is communicatively connected to the table data conversion module, and is established according to the converted plural table data for querying the converted data in the database. The data key value used by the plurality of table data, and the data key value is written into the database for storage.

In one embodiment, an SQL (Structured Query Language) syntax import module is further included, which receives an SQL syntax for querying the content of one of the plurality of table data.

In one embodiment, it further includes a syntax parsing conversion module, which is communicatively connected to the SQL syntax importing module to receive the SQL syntax and generate parsing data after parsing the SQL syntax.

In one embodiment, it further includes a query key-value generating module, which is connected to the syntax parsing conversion module in communication to receive the parsing data, and then generate query keys that meet the query conditions for the database according to the parsing data. , which is provided for querying one of the converted plural form data in the database.

In one embodiment, the query key generation module uses the query key to query the corresponding data key from the database, so as to retrieve the corresponding converted data from the database through the data key One of the plural form data of .

The present invention further provides a computer-readable medium, which is applied to a computing device or a computer and stores instructions for executing the above-mentioned method for improving the access performance of common data.

As can be seen from the above, the present invention provides a system, method and computer-readable medium for improving the access performance of common data, and proposes to classify the importance of table data according to the content of the relational database through the incremental update technology of data transfer, In this way, the update frequency of the table data is set, and the table data in the relational database is sequentially converted to the NoSQL database according to the active schedule. In addition, for the SQL grammar of the relational database, the present invention also proposes a SQL grammar parsing and conversion technology, so that the application program originally using the relational database can access the content in the NoSQL database without changing the original SQL grammar. Reduce the time required for program platform transfer and improve the overall flexibility of the system.

1: A system to improve the performance of common data access

11: Form data classification module

12: Update frequency setting module

13: Synchronous scheduling processing module

14: Form data conversion module

15: Data key value production module

16: SQL syntax import module

17: Syntax parsing conversion module

18: Query key-value production module

21: Associative Repository

22: NoSQL Repository

23: Update record database

S21 to S211: Steps

FIG. 1 is a schematic diagram of a system architecture for improving common data access performance according to the present invention; and

FIG. 2 is a schematic flowchart of a method for improving the access performance of common data according to the present invention.

The following specific embodiments are used to illustrate the implementation of the present invention, and those skilled in the art can easily understand other advantages and effects of the present invention from the contents disclosed in this specification.

It should be noted that the structures, proportions, sizes, etc. shown in the drawings in this specification are only used to cooperate with the contents disclosed in the specification for the understanding and reading of those who are familiar with the art, and are not intended to limit the implementation of the present invention. Therefore, it has no technical significance, and any modification of the structure, change of the proportional relationship or adjustment of the size will not affect the performance of the present invention. The effect and the achievable purpose should still fall within the scope that the technical content disclosed in the present invention can cover. At the same time, terms such as "a", "first", "second", "upper" and "lower" quoted in this specification are only for the convenience of description and are not used to limit the scope of the present invention. The scope of implementation and the change or adjustment of its relative relationship shall be regarded as the scope of implementation of the present invention without substantially changing the technical content.

FIG. 1 is a schematic diagram of a system architecture for improving the access performance of common data according to the present invention. As shown in FIG. 1 , the system 1 for improving common data access performance includes: a table data classification module 11 , an update frequency setting module 12 , a synchronization scheduling processing module 13 , and a table data conversion module 14. A data key-value production module 15, a SQL (Structured Query Language) grammar import module 16, a grammar parsing conversion module 17, a query key-value production module 18, an associative database 21, A NoSQL (Non-Structured Query Language) database 22 and an update record database 23 .

Specifically, the improvement of common data access performance is established on a server (such as a general-purpose server, a file-based server, a storage unit-based server, etc.) or other electronic devices with appropriate computing mechanisms, and these Modules and databases can be software, hardware or firmware; in the case of hardware, they can be processing units, processors, computers or servers with data processing and computing capabilities; in the case of software or firmware, they can be It may include instructions executable by a processing unit, processor, computer or server, and may be installed on the same hardware device or distributed across multiple hardware devices.

The table data classification module 11 is connected to the relational database 21 by communication, and obtains plural table data in the relational database 21, so as to classify the importance of different types of the table data, so as to generate corresponding important ones Plural classification data of types, wherein the importance type represents the importance of the table data.

In one embodiment, the table data includes the name of the relational database 21 stored in it, the name and detailed data content of the data table, and the types of the table data include authority data, business data, code table and Relevant record files, etc., but not limited to the above, their importance is divided into authority data, business data, code table and related record files, and these table data are updated according to their corresponding transaction conversions according to different degrees of importance The frequency is also different. In another embodiment, the table data may be stored in the same relational database 21 , may also be stored in different sub-databases in the same relational database 21 , or be stored in a distributed manner in different sub-databases The relational database 21 is not limited to the above.

In one embodiment, the categorization data includes the name of the relational database 21 in which the table data is stored, the name of the data table, the importance type, and the method used to convert the table data from the relational database 21 to the NoSQL database 22. The data format used.

The update frequency setting module 12 provides a user or an application program to set the update frequency of the table data according to the importance of the different table data, thereby generating an update frequency data. In one embodiment, the importance type of the classified data is high importance, the corresponding table data is updated in seconds or minutes, and the importance type of the classified data is medium importance , the corresponding table data is updated in minutes or hours, and if the importance type of the classified data is low importance, the corresponding table data is updated in hours or days.

For example, the update frequency setting module 12 has different timeliness for the update frequency of the table data according to various characteristics and requirements of the table data, wherein the high or medium importance of the table data They need a higher update frequency to be able to instantly reflect the changes in the changed items in the table data and avoid processing outdated data content. The data of low importance needs a lower update frequency to be able to use the system resources effectively and avoid the situation that the performance affects each other.

In one embodiment, the update frequency setting module 12 adopts the method of on-demand update for the table data that is fixed and not frequently changed, and the data update is performed when the content of the new table data needs to be used, which can also effectively utilize the system resources.

The synchronous scheduling processing module 13 communicates with the table data classification module 11 and the update frequency setting module 12 , and receives the plural classification data with important types from the table data classification module 11 and the data from the update frequency setting module 12 . The update frequency data is used to match the update frequency corresponding to the importance type of the classification data from the update frequency data, so as to perform scheduling, and sequentially issue an instruction to perform the conversion operation of the table data.

In one embodiment, the synchronization scheduling processing module 13 divides the plurality of table data in the relational database 21 into a first schedule and a second schedule according to the update frequency from high to low according to the importance type and the update frequency data , the third schedule and the fourth schedule, of which:

1. The first schedule is the one with the highest frequency of changes in the table data. For example, the authority data is of high importance. The content of the data will be changed frequently, and the chances of using and reading are relatively large, so the update frequency is If it is too slow, it will cause the consistency of authority data to occur. Therefore, the schedule is set in seconds for transaction update, which can avoid the occurrence of content inconsistency in data processing or the occurrence of data that is too old.

2. The second schedule is the middle of the frequency of change in these table data, for example, business data, its importance is medium, and the data that is not read frequently can be classified as the category of frequency of change, This category can be set to use minutes as a unit to set the schedule for transaction update.

3. The third schedule is the one with the smallest change frequency in the table data, for example, the code table, its importance is low, and the timeliness requirements belong to the lowest level, and the number of changes will be different. It is often rare, and changes are not often performed, so the data synchronization schedule processing can be set to the lowest frequency, and the frequency can be set in hours or days to set the schedule for transaction update.

4. The fourth schedule is for those that do not change the data in these tables, for example, the relevant record file, which is used as the data table of the record, and only provides the operation process track record of the changed item, which is used to query the relevant operation records or obstacles. Excluded from use, so the related record files hardly need to be updated, and the synchronization process can be set to not need to be processed.

The table data conversion module 14 is the communication connection table data classification module 11, the synchronization scheduling processing module 13, the relational database 21, the NoSQL database 22 and the update record database 23, wherein, when the table data conversion module 14 After receiving the instruction of executing the conversion operation of the table data issued by the synchronization scheduling processing module 13, firstly obtain the update record of the table data in the previous conversion from the update record database 23, so as to obtain the update record of the table data in the previous conversion according to the update record. The form database 21 obtains the form data that needs to be converted this time.

Furthermore, the table data conversion module 14 performs a conversion operation of converting the table data from the relational database 21 to the NoSQL database 22 according to the data format in the classified data from the table data classification module 11, so as to generate the These converted table data are stored in the NoSQL database 22 .

In one embodiment, the update record database 23 stores the update records (including the name of the database, the name of the data table, the update range and the update time, etc., but not limited to the above) generated each time the conversion operation of the table data is performed. , to provide a reference for the next conversion operation. For example, the table data conversion module 14 confirms the plural table data to be converted in this conversion operation according to the names of the database and data table in the update record, and according to the data in the update record The update range determines the range of complex table data that needs to be converted for this conversion operation, and the conversion is completed after the conversion is completed. After the operation, the update record database 23 writes back the update records of the table data after this conversion to the update record database 23 for subsequent reference.

In one embodiment, if the table data is converted for the first time, the table data conversion module 14 provides the user to set the range of the table data to be converted, that is, to set which table data in the table data Conversion is required.

In one embodiment, the table data conversion module 14 further provides the user to set the execution mode of the conversion operation of the table data, and then set the keyword according to the table data, and set the conversion range of the table data. The data conversion operation is divided into the following execution methods:

1.One Key-One Data (one keyword corresponds to one form data):

When the user sets the content of the table data to be stored in the relational database 21 in the form of a unique key (One Key), the unique key can correspond to a unique piece of table data, based on the table data The unique keyword converts these table data into the NoSQL database 22, and the table data conversion module 14 converts these table data according to the data format (such as Map type) in the corresponding classified data, and saves them together. into the NoSQL database 22. Therefore, when the user queries the table data in the NoSQL database 22, the corresponding table data can be retrieved in the Map type through the unique keyword, and then the field data in the required table data can be retrieved. deal with.

2.Multiple Key-One Data (multiple keywords correspond to one table data):

When the user sets the content of the table data to be stored in the relational database 21 in the form of multiple keys, the combination of the multiple keys can correspond to A unique piece of table data is used to convert the table data into the NoSQL database 22 according to a plurality of keywords of the table data, and the table data conversion module 14 converts the table data according to the corresponding classification data. The data format (such as Map type) is converted, and multiple keywords are separated by any special symbols (such as &, *, %, etc., but not limited to the above). Therefore, when the user queries the table data in the NoSQL database 22, the corresponding table data can be retrieved as a whole in the form of a Map through the combination of multiple unique keywords, and then the required table data can be retrieved. field data is processed.

3.One Key-Multiple Data (one keyword corresponds to multiple table data):

When the user sets the content of the table data as a type of unique key (One Key) stored in the relational database 21, the unique key can correspond to multiple table data, so that the unique key of the table data can be stored in the relational database 21. The keyword converts the table data into the NoSQL database 22, and the table data conversion module 14 converts the table data according to the data format (eg, Set type) in the corresponding classified data. Therefore, when the user queries multiple table data in the NoSQL database 22, the corresponding plurality of table data can be retrieved in the form of Set through the unique keyword, and the retrieved The table data content will not be repeated.

The data key value production module 15 is a communication connection with the table data conversion module 14, and according to the converted table data, a keyword (such as a unique keyword (One Key), multiple keys, etc.) set in the table data conversion module 14 A keyword (Multiple Key) and the conversion range of the table data (such as converting the complete table data, or converting part of the table data) are established to provide the information for querying the converted table data in the NoSQL database 22. data key to use, and will The data key value is written into the NoSQL database 22 for storage, wherein the design method of the data key value has the following design methods according to the usage requirements:

1. The first production method: when the conversion scope of table data is to convert complete table data, the full names of the table data are used, and they are arranged in order according to the size of the hierarchy, namely the name of the database, the name of the data table. Name, query key value and query value field, use a colon ":" as a separator in the middle of each layer to design data key value, among which, the query key value and query value field are the set keywords (such as the unique keyword (One Key ), Multiple Keys).

In one embodiment, when the query key value and the keyword set in the query value field are multiple keys, "&" is used as a concatenation symbol to combine at least two of the table data. The complete name and its content are used to generate query keys and query value fields (described in the following embodiments). It should be understood that this design method can completely correspond to the original table data in the relational database 21, so as to obtain the complete converted table data through the query key value and the query value field.

2. The second production method: when the conversion range of the table data is the table data of the conversion part, the conditional data fields are used, and they are arranged in order according to the size of the hierarchy, namely database name, query field, and query key. In the value and query value fields, colons are used as separators in the middle of each level, and "@" (or other special symbols) are used as concatenation symbols in the query field to design data key values (described in the following embodiments). Since the NoSQL database 22 is a data processing method of a data key value, it is impossible to perform conditional filtering according to different SQL syntaxes. This design method can filter and filter the table data to be used in advance, that is, set the conversion range of table data, and stored in the NoSQL database 22 for subsequent processing and use.

The SQL syntax importing module 16 receives a user or other system applications inputting SQL syntax for querying table data.

The syntax parsing conversion module 17 is communicatively connected to the SQL syntax importing module 16 to receive the SQL syntax for querying table data sent by the SQL syntax importing module 16, and then perform keyword parsing for the SQL syntax for querying table data , in order to obtain the analytical data related to the name of the database, the name of the data table, the query field, the query key value and the query value range, etc., but not limited to the above.

The query key value production module 18 is connected to the syntax parsing conversion module 17 in communication to receive the parsing data (such as the name of the database, the name of the data table, the query field, the query key value) sent by the syntax parsing and conversion module 17 and query value field), and then generate query keys that meet the query conditions for the NoSQL database 22 according to the analysis data, so as to provide the converted table data for querying the NoSQL database 22 .

In one embodiment, the query key generation module 18 uses the generated query key to query the corresponding data key from the NoSQL database 22, so as to retrieve the corresponding data key from the NoSQL database 22 through the data key The converted table data are used to obtain query results.

The following embodiment is the first embodiment of the system for improving the access performance of common data of the present invention to perform the conversion operation. Repeat the narration.

In this embodiment, the relational database 21 stores an employee data table (such as table data), and the content of the employee data table is stored in the relational form in the form of an employee number (ie empno) as a unique key (One Key). Database 21, as shown in Table 1, for example, the employee data table includes the name of the database: security, the name of the data table: employee, employee number (ie empno), employee Job name (ie empname), office (ie office), gender (ie gender) and contact number (ie mobile), etc., but not limited to the above.

Table 1: Employee Data Sheet

The table data classification module 11 obtains the employee data table from the relational database 21, and classifies the importance of the employee data table to generate corresponding classification data with important types, as shown in Table 2 below, wherein the employee data The level of the importance type in the table is B, and the data format to be converted into the employee data table is MAP.

Table 2: Categorized data of employee data sheet

The update frequency setting module 12 provides a user to set the update frequency of the table data according to different importance types, so as to generate an update frequency data, as shown in Table 3. In detail, the higher the level of the importance type, the higher the update frequency, wherein the level of the importance type is divided into A, B, C, D, etc. from high to low.

Table 3: Update Frequency Information

The synchronous scheduling processing module 13 matches the importance type (B) in the classification data of the employee data table with the update frequency data, so as to obtain the corresponding update frequency (10 minutes), and executes the process according to the update frequency (10 minutes). Schedule, wherein, the update frequency (10 minutes) of the employee data table is the middle of the change frequency, so the synchronous scheduling processing module 13 sets the employee data table as the second schedule to send the table data conversion module 14 for execution Instructions for conversion of employee data sheets.

After receiving the instruction of the conversion operation, the table data conversion module 14 obtains the update record of the employee data table in the previous conversion from the update record database 23, as shown in Table 4, according to the update range (A001) in the update record from The relational database 21 acquires the employee data of employee A in the employee data table that needs to be converted this time.

Table 4: Update records

Specifically, the form data conversion module 14 adopts the execution method of one employee number corresponding to one employee data, and uses the employee number (A001) as the update basis, and confirms the update range (A001) in the update record to obtain the need for conversion this time. The employee data of employee A in the employee data sheet of the job.

Furthermore, the table data conversion module 14 further provides the user to set the execution method of the conversion operation of table data (such as the execution method of One Key-One Data), and then set the keyword according to the table data, for example: the table data conversion module Group 14 provides users to set the employee number (ie empno) and its content (ie A001) in the employee data table (as shown in Table 1) as the unique key (One Key), and set the conversion range of the table data (such as full form information).

Next, the form data conversion module 14 converts the employee data of employee A in the employee data table according to the conversion range of the set form data (such as complete form data) and the data format (Map type) in the corresponding classified data , to generate the converted employee data of employee A, as shown in Table 5, and then store it in the NoSQL database 22.

Table 5: Employee Information of Converted Employee A

The data key value production module 15 uses the employee number (ie empno) and its content (ie A001) set by the table data conversion module 14 as the unique key (One Key), and the conversion range of the table data (such as complete Form data), to design a data key by adopting the "first production method", so as to use the converted employee data of employee A to create a data key, wherein the data key of employee A includes the name of the database in sequence (security), the name of the data table (employee), the query key value (empno) and the query value field (A001), and the colon ":" is used as a separator in the middle of each hierarchy to form the data key value, for example, the data of employee A The key value is security: employee: empno: A001, and finally the data key value of employee A is written into the NoSQL database 22 for storage. It should be understood that since the employee number (ie, empno) and its content (A001) are set as the unique key (One Key), the query key value is empno, and the query value field is A001.

Therefore, the NoSQL database 22 stores the converted employee data (Value) and data key value (Key) of employee A, as shown in Table 6, thereby completing the update operation.

Table 6: Contents stored in the NoSQL database of the first embodiment

The following embodiment is the second embodiment of the system for improving the access performance of common data of the present invention to perform the conversion operation, and the main contents of this embodiment for performing the conversion operation are as follows, and the other contents are the same as the first embodiment, and together Referring to FIG. 1 for description, the description will not be repeated here.

In this embodiment, the table data conversion module 14 further provides the user to set the execution method of the conversion operation of table data (such as the execution method of Multiple Key-One Data), and then set the keyword according to the table data, such as: table The data conversion module 14 provides the user to set the employee name (ie, empname) and its content (ie, employee A) and the office (ie, office) and its content (ie, O008) in the employee data table (as shown in Table 1) as Multiple keywords (Multiple Key), and set the conversion range of table data (such as complete table data).

Next, the form data conversion module 14 converts the employee data of employee A in the employee data table according to the conversion range of the set form data (such as complete form data) and the data format (Map type) in the corresponding classified data , to generate the converted employee data of employee A (as shown in Table 5), and then store it in the NoSQL database 22 .

The data key value production module 15 sets the employee name (ie empname) and its content (ie employee A) and the office (ie office) and its content (ie O008) set by the table data conversion module 14 as a plurality of keywords (Multiple Key), and the conversion range of table data (such as complete table data), so as to use the "first production method" to design a data key, so as to use the The converted employee data of employee A creates a data key, wherein the data key of employee A includes the name of the database (security), the name of the data table (employee), the query key (empname&office) and the query field in sequence (employee A&O008), and the colon ":" is used as a separator in each hierarchy to form the data key value. For example, the data key value of employee A is security:employee:empname&office:employee A&O008, and finally the data of employee A The key value is written into the NoSQL database 22 for storage.

In one embodiment, since the employee name (ie empname) and the office (ie office) are set as multiple keys, "&" is used as a concatenation symbol in the query key value and the query value field respectively Therefore, the query key value is empname&office, and the query value field is employee A&O008.

Therefore, the NoSQL database 22 stores the converted employee data (Value) and data key value (Key) of employee A, as shown in Table 7, thereby completing the update operation.

Table 7: Contents stored in the NoSQL database of the second embodiment

The following embodiment is the third embodiment of the system for improving the access performance of common data of the present invention to perform the conversion operation, and the main content of this embodiment for performing the conversion operation is as follows, and the rest of the content is the same as the first embodiment, and together Referring to FIG. 1 for description, the description will not be repeated here.

In this embodiment, the table data conversion module 14 further provides the user to set the execution method of the conversion operation of table data (such as the execution method of One Key-One Data), and then set the keyword according to the table data, for example: table The data conversion module 14 provides the user to set the employee number (ie empno) and its content (ie A001) in the employee data table (as shown in Table 1) as the unique key (One Key), and to set the conversion of table data Scope (eg part of table information)

Next, the form data conversion module 14 converts the employee data of employee A in the employee data table according to the conversion range of the set form data (such as part of the form data) and the data format (Map type) in the corresponding classified data For example, the conversion range of the table data is gender (ie gender) and contact number (ie mobile) to generate the converted employee data of employee A, as shown in Table 8, and then store it in the NoSQL database 22.

Table 8: Employee Information of Converted Employee A

The data key value production module 15 uses the employee number (ie empno) and its content (ie A001) set by the table data conversion module 14 as the unique key (One Key), and the conversion range of the table data (eg part of the Form data), to use the "second production method" to design a data key, so as to use the converted employee data of employee A to create a data key, wherein the data key of employee A includes the name of the database in sequence (security), query field (gender@mobile), query key (empno), and query value field (A001), and use a colon ":" as a separator in each hierarchy to form a data key, for example, employee A's capital The material key is security: gender@mobile:empno:A001, and finally the data key of employee A is written into the NoSQL database 22 for storage.

In one embodiment, gender (ie gender) and contact phone number (ie mobile) are used as the conversion range of table data, that is, as values in the query field, while gender (ie gender) and contact phone number (ie mobile) The "@" is used as a concatenation symbol between them, so the value in the query field is gender@mobile.

Therefore, the NoSQL database 22 stores the converted employee data (Value) and data key value (Key) of employee A, as shown in Table 9, thereby completing the update operation.

Table 9: Contents stored in the NoSQL database of the third embodiment

On the other hand, the NoSQL database 22 stores the converted employee data (Value) and the data key (Key) of employee A, wherein the data key (Key) of employee A stored in the NoSQL database 22 can be stored at the same time It includes the data keys produced by the first embodiment, the second embodiment and the third embodiment.

The following embodiment is a fourth embodiment of using SQL syntax to query table data in a NoSQL database for the system for improving common data access performance of the present invention, and the main content of this embodiment is as follows, and the rest of the content is the same as the first The embodiment, the second embodiment and the third embodiment are also described with reference to FIG. 1 , and will not be repeated here.

In this embodiment, when the NoSQL database stores the data key (Key) of the converted employee A of the first embodiment (such as Key=securi- in Table 6) ty:employee:empno:A001), the SQL grammar import module 16 receives a first SQL grammar input by the user to query employee data in the employee data table, wherein the first SQL grammar corresponds to the first implementation Example data key (Key). For example, the first SQL syntax is select*from security.employee where empno=A001, wherein the first SQL syntax is queried in the manner of One Key-One Data (one keyword corresponds to one table data).

The syntax parsing conversion module 17 performs keyword parsing on the first SQL syntax for querying employee data in the employee data table to obtain parsed data, wherein the parsed data sequentially includes the name of the database (security), the name of the data table ( employee), query field (*, also refers to query complete data), query key value (empno) and query value field (A001).

Furthermore, the query key generation module 18 generates a first query key that meets the query conditions for the NoSQL database 22 according to the analysis data, for example, security:employee:empno:A001, and queries the key generation module 18 Query the corresponding data key value (such as the data key value security:employee:empno:A001 in Table 6 of the first embodiment) in the NoSQL database 22 by using the query key value produced by it, so as to obtain the corresponding data key value through the query The data key value retrieves the employee data in the corresponding employee data table from the NoSQL database 22 (as shown in Table 6 of the first embodiment).

In another embodiment, when the NoSQL database stores the converted data key of employee A of the second embodiment (eg, Key=securi-ty:employee:empname&office:employee A&O008 in Table 7) , the SQL grammar import module 16 receives the second SQL grammar input by the user to query the employee data in the employee data table, wherein the second SQL grammar corresponds to the data key (Key) of the second embodiment. For example, the second SQL syntax is select *from security.employee where empname=employee A and office=0008, wherein the second SQL syntax is queried in the manner of Multiple Key-One Data (multiple keywords correspond to one table data).

The syntax parsing conversion module 17 performs keyword parsing on the second SQL syntax for querying employee data in the employee data table to obtain parsed data, wherein the parsed data sequentially includes the name of the database (security), the name of the data table ( employee), query field (*, also refers to query complete data), query key value (empname&office) and query value field (employee A&O008).

Furthermore, the query key generation module 18 generates a second query key that meets the query conditions for the NoSQL database 22 according to the parsed data, for example, security:employ-ee:empname&office:employee A&O008, and the query key The production module 18 uses the produced query key to query the corresponding data key in the NoSQL database 22 (such as the data key security: employee:empname&office:employee A&O008 in Table 7 of the second embodiment), The employee data in the corresponding employee data table is retrieved from the NoSQL database 22 through the queried data key value (as shown in Table 7 of the second embodiment).

In another embodiment, when the NoSQL database stores the data key (Key) of the converted employee A of the third embodiment (eg, Key=securi-ty:gender@mobile:empno:A001 in Table 9) , the SQL grammar import module 16 receives the third SQL grammar input by the user to query the employee data in the employee data table, wherein the third SQL grammar corresponds to the data key (Key) of the third embodiment. For example, the third SQL syntax is select gender, mobile from security.employee where empno=A001, wherein the third SQL syntax is queried in the manner of One Key-One Data (one keyword corresponds to one table data).

The syntax parsing conversion module 17 performs keyword parsing on the third SQL syntax for querying employee data in the employee data table to obtain parsed data, wherein the parsed data sequentially includes the name of the database (security), the query field (gender) @mobile), query key (empno) and query range (A001).

Furthermore, the query key generation module 18 generates a third query key that meets the query conditions for the NoSQL database 22 according to the analysis data, for example, security: -gender@mobile:empno:A001, and the query key generates The manufacturing module 18 queries the NoSQL database 22 for the corresponding data key (such as the data key security:gender@mobile:empno:A001 in Table 9 of the third embodiment) by using the query key produced by it, so as to The employee data in the corresponding employee data table is retrieved from the NoSQL database 22 through the queried data key value (as shown in Table 9 of the third embodiment).

The following embodiment is the fifth embodiment of the system for improving the access performance of common data of the present invention to perform the conversion operation. Referring to FIG. 1 for description, the description will not be repeated here.

In this embodiment, the relational database 21 stores an employee permission table (such as table data), and the content of the employee permission table is stored in the relational database in the form of the employee number (ie empno) as the unique key (One Key). The database 21, as shown in Table 10, for example, the employee permission table includes the name of the database: security, the name of the data table: duty, employee number (ie empno), employee role (ie role), etc., but not limited to the above.

Table 10: Employee permission table

The table data classification module 11 obtains the employee permission table from the relational database 21, and classifies the importance of the employee permission table to generate corresponding classification data with important types, as shown in Table 11 below, where the employee permission The level of the importance type in the table is A, and the data format to be converted into the employee permission table is Set.

Table 11: Classification information of employee authority table

The synchronization scheduling processing module 13 matches the importance type (A) in the classification data of the employee authority table with the update frequency data (as shown in Table 3) provided by the update frequency setting module 12 to obtain the corresponding The update frequency (30 seconds) is scheduled according to the update frequency (30 seconds). Among them, the update frequency (30 seconds) of the employee permission table is the one with the largest change frequency. Therefore, the synchronous scheduling processing module 13 will The authorization table is set as the first schedule to issue an instruction to the table data conversion module 14 to execute the conversion operation of the employee authorization table.

After receiving the instruction of the conversion operation, the table data conversion module 14 obtains the update record of the employee authority table in the previous conversion from the update record database 23, as shown in Table 12, according to the update range (A001) in the update record from The relational database 21 obtains the employee number (ie, empno) in the employee authority table that needs to be converted this time.

Table 12: Update records

The form data conversion module 14 adopts the execution method that an employee number corresponds to a plurality of employee roles (ie roles), and uses the employee number (A001) as the update basis, and confirms the update range (A001) in the update record to obtain the required conversion. The employee role (ie role) in the employee permission table of the job.

Furthermore, the table data conversion module 14 further provides the user to set the execution method of the conversion operation of table data (such as the execution method of One Key-Multiple Data), and then set the keyword according to the table data, for example: the table data conversion module Group 14 provides users to set the employee number (ie empno) and its content (ie A001) in the employee permission table (as shown in Table 10) as the unique key (One Key), and set the conversion range of table data (such as full form information).

Next, the table data conversion module 14 converts the employee number (ie empno) in the employee authority table to the employee number (ie empno) and the The employee roles (ie roles) are converted to generate converted employee numbers and employee roles, as shown in Table 13, which are then stored in the NoSQL database 22.

Table 13: Converted employee numbers and employee roles

The data key value production module 15 uses the employee number (ie empno) and its content (ie A001) set by the table data conversion module 14 as the unique key (One Key), and the table The conversion range of grid data (such as complete table data), so as to use the "first production method" to design a data key value, so as to use the converted employee number and employee role to create a data key value, among which, about the converted employee The data keys of the serial number and employee role include the name of the database (security), the name of the data table (duty), the query key (empno) and the query field (A001) in sequence, and a colon ":" is used in the middle of each hierarchy. As a separator to form the data key, for example, the data key of the converted employee number and employee role is security:duty:empno:A001.

Therefore, the NoSQL database 22 stores the content (Value) of the converted employee number and employee role and its data key value (Key), as shown in Table 14, thereby completing the update operation.

Table 14: Contents stored in the NoSQL database of the fifth embodiment

In one embodiment, when the NoSQL database stores the data keys (Key) of the converted employee numbers and employee roles of the fifth embodiment (such as Key=security:duty:empno:A001 in Table 14), The SQL grammar importing module 16 receives a fourth SQL grammar input by the user to query the employee number and employee role in the employee permission table, wherein the fourth SQL grammar corresponds to the data key value (Key) of the fifth embodiment . For example, the fourth SQL syntax is select *from security.duty where empno=A001, wherein the fourth SQL syntax is queried in the manner of One Key-Multiple Data (one keyword corresponds to multiple table data).

The syntax parsing conversion module 17 performs keyword parsing on the fourth SQL syntax for querying the employee number and employee role in the employee permission table to obtain parsing data, wherein the parsing The data sequentially includes the name of the database (security), the name of the data table (duty), the query field (*, also refers to querying all fields), the query key (empno) and the query field (A001).

Furthermore, the query key generation module 18 generates a fourth query key that meets the query conditions for the NoSQL database 22 according to the analysis data, for example, security:duty:empno:A001, and queries the key generation module 18 Query the corresponding data key value (such as the data key value security:duty:empno:A001 in Table 14) in the NoSQL database 22 by using the query key value produced by it, so as to obtain the corresponding data key value from the NoSQL database through the queried data key value The database 22 retrieves the corresponding employee numbers and employee roles (as shown in Table 14 of the fifth embodiment).

FIG. 2 is a schematic flowchart of the method for improving the access performance of common data according to the present invention. The main content of the method for improving the access performance of common data is as follows, and the rest of the content is the same as that described in FIG. Wherein, the method flow includes the following steps S21 to S211:

In step S21, the synchronization scheduling processing module 13 confirms whether the execution source is a scheduled operation, if so, it goes to step S22, otherwise, it goes to step S29.

In step S22 , if the scheduling operation is executed, the synchronous scheduling processing module 13 obtains the plural classification data having the importance type from the table data classification module 11 .

In step S23 , the synchronization scheduling processing module 13 obtains the update frequency data from the update frequency setting module 12 .

In step S24, when the table data conversion module 14 receives an instruction to perform the conversion operation of the plurality of table data in sequence according to the plurality of classification data with the importance type and the update frequency data from the synchronization scheduling processing module 13, the data is updated from the update record. The database 23 obtains the update records of the plural table data in the previous conversion.

In step S25 , the table data conversion module 14 obtains plural table data from the relational database 21 .

In step S26, the table data conversion module 14 determines the range of the plural table data to be converted in this conversion operation according to the update range of the update record, and performs the conversion operation according to the data format in the classified data corresponding to the plural table data to generate the converted form data.

In step S27, the data key value generation module 15 establishes data used for querying the converted table data in the NoSQL database 22 according to the converted table data from the table data conversion module 14 key value.

In step S28, the NoSQL database 22 stores the converted table data and the corresponding data keys for the user to query and obtain the converted table data through the data keys.

In step S29, if the scheduled operation is not executed, the SQL syntax import module 16 receives a user input of the SQL syntax for querying the table data.

In step S210, the syntax parsing conversion module 17 receives the SQL syntax for querying table data from the SQL syntax importing module 16, and performs keyword parsing to obtain parsing data.

In step S211, the query key generation module 18 receives the parsing data from the syntax parsing conversion module 17, and generates query keys that meet the query conditions according to the parsing data, so as to use the query keys to query the NoSQL database 22. A corresponding data key value is obtained, and then at least one of the converted table data corresponding to the data key value is obtained.

In addition, the present invention also discloses a computer-readable medium, which is applied to a computing device or computer having a processor (eg, CPU, GPU, etc.) and/or memory, and stores instructions, and can utilize the computing device or computer. The computer executes the computer-readable medium through a processor and/or a memory, so as to execute the above-mentioned methods and steps when executing the computer-readable medium.

In summary, the present invention provides a system and method for improving the access performance of common data and a computer-readable medium thereof. By classifying the importance of the table data on the content of the relational database, the update frequency of the table data is set. , and actively schedule to convert table data in relational database to NoSQL database. Therefore, it can not only provide the same service level as high-end commercial databases, but also improve the access performance of commonly used data, achieve the integration of relational databases and NoSQL databases, effectively offload the load of front-end entry, and improve the overall response of application services. performance.

In addition, the present invention also proposes a SQL syntax parsing and conversion technology, so that the application program originally using the relational database can access the content in the NoSQL database without changing the original SQL syntax, reducing the time and time required for the program platform transfer. Improve the overall flexibility of the system.

Furthermore, compared with the prior art, the system, method and computer-readable medium for improving common data access performance of the present invention have at least the following technical differences and effects:

1. The present invention proposes a scheduling classification setting technology to classify the importance and update frequency of the table data in the relational database, so as to actively schedule the table data according to the importance of the table data, so as to convert the table data to the NoSQL database, Therefore, compared with the prior art that needs to synchronize all the data between the relational database and the NoSQL database in real time, the present invention updates the data according to the importance, which can not only reduce the burden of the system, but also improve the overall work efficiency.

2. The present invention proposes a table data conversion technology, which converts the content of the relational database to the NoSQL database through incremental updating, and provides the same service level as the high-end commercial database.

3. The present invention proposes a key-value query production technology, which produces the corresponding data key value for the table data stored in the relational database, so as to provide user query through the data key value, which is stored in the NoSQL database after conversion form data.

Fourth, the present invention proposes a syntax parsing conversion technology, which automatically parses the original SQL syntax of the application program, and can obtain the converted table data in the NoSQL database without adjusting the program.

The above-mentioned embodiments merely illustrate the principles and effects of the present invention, but are not intended to limit the present invention. Any person skilled in the art can modify and change the above-mentioned embodiments without departing from the spirit and scope of the present invention. Therefore, the protection scope of the present invention should be listed in the scope of the patent application.

1: A system to improve the performance of common data access

11: Form data classification module

12: Update frequency setting module

13: Synchronous scheduling processing module

14: Form data conversion module

15: Data key value production module

16: SQL syntax import module

17: Syntax parsing conversion module

18: Query key-value production module

21: Associative database

22: NoSQL Repository

23: Update record database

Claims (17)

A system for improving common data access performance, comprising: a table data classification module, which obtains the plurality of table data and classifies the importance of the plurality of table data, so as to generate the plurality of classification data of the importance type corresponding to the plurality of table data; an update frequency setting module for setting the update frequency according to different importance of the plurality of table data, so as to generate an update frequency data; a synchronous scheduling processing module, which communicates and connects the table data classification module and the update frequency setting module to receive the plurality of classification data with important types from the table data classification module and the update frequency setting module update frequency data, and then match the update frequency corresponding to the importance type of the plurality of classification data from the update frequency data, so as to schedule and sequentially issue instructions to perform the conversion operation of the plurality of table data; and A table data conversion module is connected to the synchronous scheduling processing module in communication, so as to sequentially receive instructions of the conversion operation of the synchronous scheduling processing module, and then perform the conversion operation of the plurality of table data according to the instructions, so as to achieve The converted plural form data is stored in a database. The system for improving common data access performance as claimed in claim 1, wherein the plural table data is stored in a relational database, and the converted plural table data is stored in NoSQL (Non-Structured Query Language) data library. The system for improving the access performance of common data as described in claim 1 further includes an update record database, which stores the update records generated each time the conversion operation of the plurality of table data is performed, wherein, when the table data is converted After the module receives the instruction of the conversion operation sent by the synchronous scheduling processing module, the table data conversion module obtains the duplicate data from the update record database. The update record of the previous conversion of the data of the plurality of tables is to obtain the content of the data of the plurality of tables according to the update range of the content of the data of the plurality of tables recorded in the update record, so as to perform the conversion operation. The system for improving the access performance of common data according to claim 1, wherein the table data conversion module is further connected to the table data classification module in communication to obtain the plural classified data, and then according to the data in the plural classified data Format conversion of the corresponding plural table data to generate the converted plural table data. The system for improving the access performance of common data as described in claim 1 further includes a data key-value production module, which is communicatively connected to the table data conversion module, so as to be established according to the converted plural table data for providing The data key value used by the converted plural form data is queried in the database, and the data key value is written into the database for storage. The system for improving common data access performance as claimed in claim 1 further includes an SQL (Structured Query Language) syntax import module for receiving an SQL syntax for querying the content of one of the plurality of table data. The system for improving common data access performance as described in claim 6 further includes a syntax parsing conversion module, which is communicatively connected to the SQL syntax importing module to receive the SQL syntax, and generate a syntax after parsing the SQL syntax Parse data. The system for improving the access performance of common data as described in claim 7 further includes a query key-value production module, which is connected to the syntax parsing conversion module in communication to receive the parsing data, and then target the data according to the parsing data. The database produces a query key value that meets the query conditions, so as to provide one of the converted plural table data in the database. A method for improving common data access performance, comprising: Obtaining plural table data from a table data classification module, so as to classify the importance of the plural table data, so as to generate plural classification data of the importance type corresponding to the plural table data; An update frequency setting module is provided to set the update frequency according to different importance of the plurality of table data, so as to generate an update frequency data; A synchronous scheduling processing module receives the complex classification data with the importance type from the table data classification module and the update frequency data of the update frequency setting module, so as to match the plurality of classifications corresponding to the update frequency data from the update frequency data the update frequency of the importance type of the data for scheduling and sequentially issuing instructions to perform the conversion of the plurality of tabular data; and A table data conversion module sequentially receives instructions of the conversion operation of the synchronous scheduling processing module, and then performs conversion operations on the plurality of table data according to the instructions, so as to store the converted plurality of table data into a data library. The method for improving common data access performance as claimed in claim 9, wherein the plural table data is stored in a relational database, and the converted plural table data is stored in NoSQL (Non-Structured Query Language) database. The method for improving the access performance of common data according to claim 9, further comprising storing in an update record database the update records generated each time the conversion operation of the plurality of table data is performed, wherein, when the table data conversion mode is After the group receives the instruction of the conversion operation sent by the synchronous scheduling processing module, the table data conversion module obtains the update record of the previous conversion of the plurality of table data from the update record database, so as to be based on the update The update range of the content of the plural form data recorded in the record obtains the content of the plural form data for the conversion operation. The method for improving common data access performance as claimed in claim 9, wherein the table data conversion module obtains the plural classified data from the table data classification module, and converts the data according to the data format in the plural classified data. corresponding to the plural form data to generate the converted plural form data. The method for improving commonly used data access performance as claimed in claim 9, further comprising creating, by a data key-value creation module according to the converted plural table data, for providing query of the converted plural number in the database The data key used by the table data, and the data key is written into the database for storage. The method for improving common data access performance as described in claim 9 further includes receiving an SQL grammar for querying the content of one of the plurality of table data from a SQL (Structured Query Language) grammar import module. The method for improving common data access performance as described in claim 14 further includes receiving the SQL grammar from the SQL grammar importing module by a grammar parsing conversion module, and generating parsed data after parsing the SQL grammar. The method for improving common data access performance as described in claim 15, further comprising: receiving the parsing data from the syntax parsing and converting module by a query key-value generating module, and then generating the parsing data for the database according to the parsing data. Formulate a query key value that meets the query conditions to provide one of the converted plural table data for querying the database. A computer-readable medium used in a computing device or computer, storing instructions for executing the method for improving the access performance of common data as described in any one of claims 9 to 16.

Images