Database performance optimization method based on partition management

Abstract

The invention discloses a database performance optimization method based on partition management. The database performance optimization method comprises database storage optimization and key query table efficiency optimization. The database storage optimization comprises the steps of confirming the partition data volume and creating a corresponding partition large file table space; verifying whether the table can be partitioned; creating a temporary table which is the same as the original table, and creating partitions and partition names according to company column values; copying the data to a temporary table; copying the dependent object; partitioning the original table according to a list rule, and emptying data in the original table; synchronizing the data, the trigger and the index information in the temporary table to the partitioned original table; checking whether the partitioned data is consistent or not, verifying whether front-end query is influenced or not, checking index parameters, adopting the database optimization method, dividing the data into a plurality of partitions, mapping key large table data into different partition tables, reducing the data size of a single table, improving the efficiency and availability of the system and ensuring the stability and reliability of the system.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a database performance optimization method based on partition management. Background Art

[0002] As time goes by, the amount of data in the database is also increasing rapidly, and the efficiency of the data management system faces huge challenges. Theoretically, when the amount of data in a single table of a relational database reaches 100 million or the capacity reaches 2G, the performance will be significantly reduced, and the impact will be multifaceted.

[0003] First, for complex queries, especially those involving multi-table joins, subqueries, and large-range scans, the increase in data volume may lead to a decrease in query performance. Second, as the amount of data increases, the concurrent processing capability of the database may become a bottleneck. Storage IO pressure will also increase significantly. In high-concurrency scenarios, a large number of requests may not be responded to in a timely manner, or even timeout errors may occur. In addition, massive data may also have an impact on system scalability. When the database reaches a certain scale, scalability will be limited, and adding more hardware resources may not improve performance, resulting in the system being unable to cope with growing business needs. Finally, database monitoring and maintenance will also become more difficult. As the amount of data grows, the maintenance cost of the index increases, and too many indexes can lead to a decrease in the performance of write operations. Large tables also increase the complexity of database monitoring and maintenance because more resources are required to monitor and optimize performance.

[0004] Therefore, it is necessary to design a database performance optimization method to distribute data into multiple database partitions or tables, reduce the amount of data in a single table, improve system efficiency and availability, and ensure the stability and reliability of the system. Summary of the invention

[0005] Purpose of the invention: The purpose of the present invention is to provide a database performance optimization method based on partition management. This database optimization method uses the business operation unit served by the system as the smallest partition unit, divides the data into multiple partitions, maps the key large table data to different partition tables, reduces the data volume of a single table, improves system efficiency and availability, and ensures the stability and reliability of the system.

[0006] Technical solution:

[0007] A database performance optimization method based on partition management includes database storage optimization and key query table efficiency optimization; the database storage optimization adopts a list partitioning method, divides data into multiple partitions based on the business operation unit served by the system as the minimum partition unit, and maps key large table data to different partition tables.

[0008] The database storage optimization includes:

[0009] 1) Confirm the partition data volume and create the corresponding partition large file table space to store the partitioned table data and related objects;

[0010] 2) Verify whether the table can be partitioned;

[0011] 3) Create a temporary table that is the same as the original table, and create partitions and partition names according to the company column value;

[0012] 4) Copy data to temporary tables, mainly copying data, table relationships and temporary tables;

[0013] 5) Copy dependent objects;

[0014] 6) Partition the original table according to the list rules and clear the data in the original table;

[0015] 7) After the partitioning is completed, the data, triggers, and index information in the temporary table are synchronized back to the original table after the partitioning;

[0016] 8) After synchronization is complete, check whether the data after partitioning is consistent, verify whether the front-end query is affected, and check the index parameters.

[0017] Preferably, between step 2) and step 3), the process also includes: performing historical data processing: performing field expansion and value assignment on the historical data of the table to be partitioned, so as to ensure data integrity and prepare for subsequent partitioning.

[0018] Preferably, the historical data processing comprises the following steps:

[0019] S1. Field expansion: Add a new field to the target table to define the partition range of the data;

[0020] S2. Field assignment: Determine the value of the new field based on the existing fields, and create a stored procedure to automatically perform the assignment;

[0021] S3, data check: After the assignment, check whether all historical data has been initialized to ensure that there is no omission to avoid affecting the subsequent redefinition operation;

[0022] S4, Error handling: Error analysis and reassignment of unassigned data to correct potential problems;

[0023] S5. Redefine: After ensuring that all data are correctly assigned, redefine the table structure;

[0024] S6. Data processing: Process historical data in batches according to time periods and handle special cases of missing data.

[0025] Preferably, after step 8), the process also includes optimizing the application structure: ensuring that newly inserted data can automatically adapt to partition management through triggers, and optimizing query statements to improve efficiency.

[0026] Preferably, the key query table efficiency optimization includes:

[0027] A1. Data archiving logic sorting and verification: Analyze the structured query language of the query table, sort out the archiving logic and verify its accuracy and consistency in the test environment;

[0028] A2. Application optimization: add identification fields to relevant business documents according to the requirements of archiving logic, and mark the data that meets the requirements of archiving logic, so as to optimize query performance;

[0029] A3. Optimize the structured query language of the query table, re-establish appropriate query statements, optimize subqueries and create indexes, and reduce the amount of active data by filtering the marked archived business data, avoiding the use of full table scans, thereby improving query efficiency;

[0030] A4. Stock data processing: Initialize archiving and tagging of historical stock data to ensure data accuracy and consistency, and manually process abnormal data;

[0031] A5. Track and improve query efficiency optimization, record query response time and archived data volume before and after optimization, so as to evaluate optimization effects and make continuous improvements;

[0032] A6. Optimize the query table scope and clarify the query table scope that needs to be optimized to ensure that the optimization measures are highly targeted and improve query efficiency.

[0033] Preferably, step A2 includes: according to the requirements of the archiving logic, adding identification fields to the relevant data entries, and adding a background timer task to mark the data that meets the archiving logic requirements at a scheduled increment every day to facilitate the design optimization of the query table filtering conditions; at the same time, adding verification to the front end of the relevant data entries to prevent the accuracy of the query table data from being affected by the data modification after archiving.

[0034] Preferably, it also includes maintaining the consistency of the system global memory configuration of the primary and standby databases.

[0035] Beneficial effects: This database optimization method uses the business operation unit served by the system as the smallest partition unit, divides the data into multiple partitions, maps the key large table data to different partition tables, reduces the amount of data in a single table, improves system efficiency and availability, and ensures the stability and reliability of the system. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] Figure 1 It is a workflow diagram of database storage optimization of the present invention;

[0037] Figure 2 It is a workflow diagram of historical data processing of the present invention;

[0038] Figure 3 It is a workflow diagram for optimizing the efficiency of the linkage key query table of the present invention. DETAILED DESCRIPTION

[0039] In order to make the technical solution of the present invention clearer, the present invention is further described in detail below with reference to the accompanying drawings and specific embodiments.

[0040] Example 1

[0041] In this embodiment, in Oracle, there are many types of table partitions, and different types are suitable for different scenarios. Taking the trade ERP system as an example, according to the existing business characteristics of the trade ERP system and the requirements for the data table structure, based on data partitioning rules, data distribution after partitioning, query performance improvement, and management and maintenance costs, a list partitioning method is adopted. The business operation unit served by the system is the smallest unit of the partition, and the data is divided into six partitions. The key large table data is mapped to different partition tables to improve retrieval efficiency and improve the response performance of the entire system.

[0042] A database performance optimization method based on partition management includes database storage optimization and key query table efficiency optimization; the database storage optimization adopts a list partitioning method, divides data into multiple partitions based on the business operation unit served by the system as the minimum partition unit, and maps key large table data to different partition tables.

[0043] For tables where partition migration is planned, a column of attributes needs to be added at the table design level to clearly identify the partitioning rules. First, the attributes of the newly added columns need to be precisely defined, including data type, length, and whether nulls are allowed, to ensure that the new columns can accurately reflect the partitioning logic. Modify the existing table structure to seamlessly integrate the new columns, maintain data integrity, and not interfere with existing businesses. According to the established partitioning rules, update the data values ​​of the new columns to ensure that each record can be accurately mapped to the corresponding partition. In addition, the relevant indexes and constraints need to be adjusted or rebuilt to adapt to changes in the table structure and ensure the performance and consistency of the database. On this basis, define a detailed partitioning strategy to clarify the number and range of partitions and the storage location of data, and realize logical segmentation and physical storage optimization of data. Finally, modify the logic of data insertion and update to ensure that the value of the partition identification column can be correctly set during data operations, and automatically route data to the target partition to achieve automatic partition management of data.

[0044] The database storage optimization includes:

[0045] 1) Confirm the partition data volume and create the corresponding partition large file table space to store the partitioned table data and related objects;

[0046] 2) Verify whether the table can be partitioned (it can be redefined online only if it is a candidate);

[0047] 3) Create a temporary table that is the same as the original table, and create partitions and partition names according to the company column value;

[0048] 4) Copy data to temporary tables, mainly copying data, table relationships and temporary tables (backup data);

[0049] 5) Copy dependent objects (Oracle will automatically create all existing triggers, indexes, materialized view logs, authorizations and constraints, etc.) (backup index, trigger and other information);

[0050] 6) Partition the original table according to the list rules and clear the data in the original table;

[0051] 7) After the partitioning is completed, the data, triggers, and index information in the temporary table are synchronized back to the original table after the partitioning;

[0052] 8) After synchronization is complete, check whether the data after partitioning is consistent, verify whether the front-end query is affected, and check the index parameters.

[0053] Furthermore, between step 2) and step 3), historical data processing is also included: field expansion and assignment of historical data to the table to be partitioned are performed to ensure data integrity and prepare for subsequent partitioning.

[0054] The historical data processing includes the following steps:

[0055] S1. Field expansion: Add a new field to the target table to define the partition range of the data;

[0056] S2. Field assignment: Determine the value of the new field based on the existing fields, and create a stored procedure to automatically perform the assignment;

[0057] S3, data check: After the assignment, check whether all historical data has been initialized to ensure that there is no omission to avoid affecting the subsequent redefinition operation;

[0058] S4, Error handling: Error analysis and reassignment of unassigned data to correct potential problems;

[0059] S5. Redefine: After ensuring that all data are correctly assigned, redefine the table structure;

[0060] S6. Data processing: Process historical data in batches according to time periods and handle special cases of missing data.

[0061] In the commerce ERP system of this embodiment, the historical data in the tables busicost, lrpclient and creditpool that need to be partitioned need to be added with a new deptno field value to define the range of the partition. The three tables can define the deptno field value according to corbcode, upsheetcode and upicode, create a corresponding stored procedure in the database, execute the corresponding field assignment, and after the assignment is completed, check whether the historical data has been initialized and assigned. If there is unassigned data, it may cause redefinition to fail. For the unassigned data, reassign it according to the error message and then perform the redefine option.

[0062] The following data are mainly processed: historical data is processed in batches of 5 days (document date); data with empty document date is processed; if the company date is empty, it is processed through the superior document.

[0063] Furthermore, after step 8), the application structure optimization is also included: triggers are used to ensure that newly inserted data can automatically adapt to partition management, and query statements are optimized to improve efficiency.

[0064] For newly inserted data, an Oracle trigger needs to be defined. Before writing data on the front end, the data value of the partition column is written through the trigger. When inserting data, the newly added data will be automatically inserted into the corresponding partition according to the partition key value. For data with undefined partition values, the data will be rolled back and the insertion will fail. When querying a large partitioned table, you need to specify the partition. Previously, you only needed to specify a table name, but now you need to specify different partitions. It is suitable for statement splicing, which can improve data query efficiency and reduce the number of index entries. Queries on partitioned tables are performed by optimizing SQL statements, which include the following three aspects:

[0065] (1) Inquiry and submission of quota: All menus under General Business - Quota Configuration (New Version) - Quota Query, related to major trade quotas.

[0066] (2) Inquiries and submission checks involving transactions: statistical inquiry - data verification - transaction inquiry - business transaction inquiry and transaction offset functions.

[0067] (3) Inquiry and submission inspection related to costs: statistical inquiry - data verification - cost and income inquiry - business cost inquiry.

[0068] Furthermore, the key query table efficiency optimization includes:

[0069] A1. According to the specific SQL conditions of the inventory, transactions, quota and other query tables, perform segmented analysis and sort out the related business tables involved in the query. At the same time, combine the actual business situation and sort out the filing logic according to certain business rules and verify the correctness of the filing logic, data accuracy and data consistency in the test environment;

[0070] A2. According to the requirements of archiving logic, add identification fields to related business documents, and add background scheduled tasks to mark the data that meets the requirements of archiving logic every day to facilitate the design and optimization of query table filter conditions. At the same time, add verification to the front end of related documents to prevent the accuracy of query table data from being affected by data modification after archiving;

[0071] A3. Re-establish appropriate query statements, optimize subqueries, and create indexes. By filtering the marked and archived business data, reduce the amount of active data, and avoid full table scans, query efficiency can be improved.

[0072] A4. Initialize and archive historical stock data in batches, mark the stock data that meets the archiving logic requirements, and ensure the accuracy and consistency of the final data. Abnormal data that does not meet the archiving logic needs to be analyzed and processed manually. Take the abnormal data processing method of prepayment aging analysis as an example;

[0073] ① Simulation archiving logic: the contract is terminated and the fund balance under the contract is 0. Contracts that do not meet this condition will not be archived;

[0074] ② If the verification fund balance is not 0 at the time of contract termination, the contract termination operation is not allowed;

[0075] ③ The business department needs to balance the fund balance under the contract through the receipt re-identification / payment re-identification document before performing the contract termination operation.

[0076] A5. During the entire efficiency optimization process, key data should be recorded, such as query response time before and after optimization, archived data volume, etc. After the optimization is completed, the optimized data should be compared with the baseline data to determine whether the optimization has achieved the expected effect. At the same time, through continuous monitoring, analysis and improvement, the system query efficiency is guaranteed to be stable at a relatively good level;

[0077] A6. Optimize the query table range and clarify the query table range that needs to be optimized to ensure that the optimization measures are highly targeted and improve query efficiency. The specific range is shown in the following table:

[0078] Serial number Query Table 1 Inventory Balance Sheet 2 Prepaid Account Aging Analysis Table 3 Accounts receivable aging analysis table 4 Customer Transaction Inquiry Form

[0079] Furthermore, considering the impact of different system global memory configurations (SGA) on system performance in the database master-slave architecture, the SGA sizes of the master and slave databases should be kept consistent to ensure efficient operation of the database system.

[0080] In a database master-slave architecture, inconsistencies in the system global memory configuration (SGA) between the master and slave databases can have a significant impact on system performance. When the slave database's SGA configuration is small, especially when the database buffer cache capacity is insufficient, the slave database will not be able to efficiently cache data in memory. In this case, the slave database has to frequently read data from the disk, increasing disk I / O. At the same time, this additional disk access requirement causes the slave database to frequently perform SQL parsing, which also increases the CPU burden and prolongs query response time.

[0081] In the current case where the standby database memory is lower than the primary database configuration, the Redo Apply process cannot effectively use the available memory resources when applying the primary database Redo logs, which slows down the log application speed, affects data synchronization efficiency and system query efficiency, and further affects business scenarios with high requirements for commercial real-time performance.

[0082] In summary, the memory capacity of the standby database needs to be expanded to 1T to keep consistent with the configuration of the primary database, alleviate data synchronization delays, and improve SQL statement execution efficiency.

[0083] The above-mentioned embodiments only express several implementation methods of the present invention, and the description thereof is relatively specific and detailed, but it cannot be understood as limiting the scope of the patent of the present invention. It should be pointed out that, for ordinary technicians in this field, several variations and improvements can be made without departing from the concept of the present invention, which all belong to the protection scope of the present invention. Therefore, the protection scope of the patent of the present invention shall be subject to the attached claims.

Claims

1. A database performance optimization method based on partition management, characterized in that: It includes database storage optimization and key query table efficiency optimization; the database storage optimization adopts a list partitioning method, divides the data into multiple partitions based on the business operation unit served by the system as the smallest partition unit, and maps the key large table data to different partition tables.

2. The database performance optimization method based on partition management according to claim 1 is characterized in that: The database storage optimization includes: 1) Confirm the partition data volume and create the corresponding partition large file table space to store the partitioned table data and related objects; 2) Verify whether the table can be partitioned; 3) Create a temporary table that is the same as the original table, and create partitions and partition names according to the company column value; 4) Copy data to temporary tables, mainly copying data, table relationships and temporary tables; 5) Copy dependent objects; 6) Partition the original table according to the list rules and clear the data in the original table; 7) After the partitioning is completed, the data, triggers, and index information in the temporary table are synchronized back to the original table after the partitioning; 8) After synchronization is complete, check whether the data after partitioning is consistent, verify whether the front-end query is affected, and check the index parameters.

3. The database performance optimization method based on partition management according to claim 2 is characterized in that: Between step 2) and step 3), historical data processing is also included: field expansion and assignment of historical data to the table to be partitioned are performed to ensure data integrity and prepare for subsequent partitioning.

4. The database performance optimization method based on partition management according to claim 3 is characterized in that: The historical data processing includes the following steps: S1. Field expansion: Add a new field to the target table to define the partition range of the data; S2. Field assignment: Determine the value of the new field based on the existing fields, and create a stored procedure to automatically perform the assignment; S3, data check: After the assignment, check whether all historical data has been initialized to ensure that there is no omission to avoid affecting the subsequent redefinition operation; S4, Error handling: Error analysis and reassignment of unassigned data to correct potential problems; S5. Redefine: After ensuring that all data are correctly assigned, redefine the table structure; S6. Data processing: Process historical data in batches according to time periods and handle special cases of missing data.

5. The database performance optimization method based on partition management according to claim 2 is characterized in that: After step 8), the application structure optimization is also included: triggers are used to ensure that newly inserted data can automatically adapt to partition management, and query statements are optimized to improve efficiency.

6. The database performance optimization method based on partition management according to claim 1 is characterized in that: The key query table efficiency optimization includes: A1. Data archiving logic sorting and verification: Analyze the structured query language of the query table, sort out the archiving logic and verify its accuracy and consistency in the test environment; A2. Application optimization: add identification fields to relevant business documents according to the requirements of archiving logic, and mark the data that meets the requirements of archiving logic, so as to optimize query performance; A3. Optimize the structured query language of the query table, re-establish appropriate query statements, optimize subqueries and create indexes, and reduce the amount of active data by filtering the marked archived business data, avoiding the use of full table scans, thereby improving query efficiency; A4. Stock data processing: Initialize archiving and tagging of historical stock data to ensure data accuracy and consistency, and manually process abnormal data; A5. Track and improve query efficiency optimization, record query response time and archived data volume before and after optimization, so as to evaluate optimization effects and make continuous improvements; A6. Optimize the query table scope and clarify the query table scope that needs to be optimized to ensure that the optimization measures are highly targeted and improve query efficiency.

7. The database performance optimization method based on partition management according to claim 6 is characterized in that: Step A2 includes: according to the requirements of the archiving logic, adding identification fields to the relevant data entries, and adding a background scheduled task to mark the data that meets the archiving logic requirements at a scheduled increment every day to facilitate the design and optimization of the query table filtering conditions; at the same time, adding verification to the front end of the relevant data entries to prevent the accuracy of the query table data from being affected by the modification of the data after archiving.

8. The database performance optimization method based on partition management according to claim 1, characterized in that: It also includes keeping the system global memory configuration of the primary and standby databases consistent.

Images