Database optimization method and device, computer program product and electronic equipment

Abstract

The invention discloses a database optimization method and device, a computer program product and electronic equipment. The method comprises the steps that M data tables in a target database are determined, for each data table, a primary key of the data table is determined based on the association degree of fields in the data table and target business, and M is a positive integer; the data table is divided into a main table and a plurality of sub-tables based on the primary key, partitioning is carried out according to data updating dates in the data table, an updated data table is obtained, and the data size of the main table is smaller than a preset data size threshold value; and determining target fields of which the query times are greater than or equal to a query time threshold in the M updated data tables, and creating indexes for the target fields to obtain an optimized target database. Through the method and the device, the problem of low access efficiency of the database due to high coupling of basic data of the database of multiple instances in related technologies is solved.

Description

Technical Field

[0001] The present application relates to the field of big data, and more specifically, to a database optimization method, device, computer program product, and electronic device. Background Art

[0002] With the rapid development of the global aviation industry, the fare publishing business has also expanded, with its user base growing to include airlines, travel agencies, and various aviation-related third-party service providers. This trend has led to a greater diversification of business scenarios, placing higher demands on database systems. Related technologies currently use a single-user, single-instance database architecture. Due to its relatively simple data management and access methods, it is no longer adaptable to today's complex business environment with multiple roles and instances.

[0003] Since business data needs to be distributed and stored in different user instances according to different application scenarios and belongs to different modules, the public basic data required by business needs requires multiple users to operate and access the public basic data at the same time. This will result in high coupling of the basic data, making it difficult for users to maintain the database and reducing the readability of the data.

[0004] Currently, no effective solution has been proposed to the problem of low database access efficiency due to the high coupling of basic database data of multiple instances in related technologies. Summary of the Invention

[0005] The main purpose of this application is to provide a database optimization method, device, computer program product and electronic device to solve the problem of low database access efficiency caused by the high coupling of database basic data of multiple instances in the related art.

[0006] To achieve the above-mentioned objectives, according to one aspect of the present application, a database optimization method is provided. The method comprises: determining M data tables in a target database; for each data table, determining a primary key for the data table based on the degree of association between fields in the data table and a target business, where M is a positive integer; splitting the data table into a main table and multiple sub-tables based on the primary key, and partitioning the sub-tables according to the data update date in the data table to obtain an updated data table, wherein the data volume of the main table is less than a preset data volume threshold; determining target fields in the M updated data tables whose query counts are greater than or equal to the query count threshold, creating indexes for the target fields, and obtaining an optimized target database.

[0007] Optionally, determining the primary key of a data table based on the degree of association between the fields in the data table and the target business includes: determining an evaluation value of the degree of association between each field in the data table and the target business, and determining a field whose evaluation value is greater than or equal to a degree of association threshold as a pending primary key; determining a pending primary key whose update frequency is less than a first update frequency threshold as the natural primary key of the data table; creating a proxy primary key for the data table when the data update frequency in the data table is greater than or equal to a second update frequency threshold, wherein the second update frequency threshold is greater than the first update frequency threshold; and determining the natural primary key and the proxy primary key as the primary key of the data table.

[0008] Optionally, after obtaining the optimized target database, the method further includes: when a query request for the target database is detected, parsing the query request to obtain the data to be queried; determining whether a target index for the data to be queried exists in the target database; when the target index exists in the target database, retrieving the data to be queried from the target database based on the target index; when the target index does not exist in the target database, determining an update date of the data to be queried, determining a target partition of the data table to which the data to be queried belongs based on the update date, and retrieving the data to be queried from the target partition.

[0009] Optionally, after obtaining the data to be queried, the method further includes: when the amount of the data to be queried is greater than or equal to a preset amount threshold, determining multiple data tables to which the data to be queried belongs; generating a query statement based on the multiple data tables, and executing the query statement to generate a view.

[0010] Optionally, after obtaining the optimized target database, the method further includes: setting target access rights for the optimized database, wherein the target access rights include access rights for multiple roles, and the access rights for each role are used to limit the access areas and operation types of users of the corresponding role to the optimized database.

[0011] Optionally, after obtaining the optimized target database, the method further includes: monitoring the execution time of the target query statement when a target query statement executed on the optimized target database is detected; determining the query requirements corresponding to the target query statement and the accessed area of ​​the target database when the execution time is greater than or equal to an execution time threshold; determining an optimization strategy for the target database based on the query requirements and the accessed area, and executing the optimization strategy on the target database.

[0012] Optionally, the method further includes: determining N objects in the target database, renaming each object according to a preset naming rule, and obtaining a target database with an updated name, wherein N is a positive integer, and the objects include at least one of the following: a data table, a field, a view, and an index.

[0013] To achieve the above-mentioned objectives, according to another aspect of the present application, a database optimization device is provided. The device comprises: a first determining unit, configured to determine M data tables in a target database, and for each data table, determining a primary key of the data table based on the degree of association between a field in the data table and a target business, where M is a positive integer; a splitting unit, configured to split the data table into a main table and multiple sub-tables based on the primary key, and partition the data table according to the data update date, thereby obtaining an updated data table, wherein the data volume of the main table is less than a preset data volume threshold; and a second determining unit, configured to determine target fields in the M updated data tables whose query counts are greater than or equal to the query count threshold, and to create indexes for the target fields, thereby obtaining an optimized target database.

[0014] In order to achieve the above-mentioned purpose, according to another aspect of the present application, a computer program product is provided, including a computer program, which, when executed by a processor, implements the steps of the database optimization method described in each embodiment of the present application.

[0015] The present application adopts the following steps: determining M data tables in a target database, and for each data table, determining the primary key of the data table based on the degree of association between the fields in the data table and the target business, where M is a positive integer; splitting the data table into a main table and multiple sub-tables based on the primary key, and partitioning according to the data update date in the data table to obtain an updated data table, wherein the data volume of the main table is less than a preset data volume threshold; determining target fields in the M updated data tables whose query times are greater than or equal to the query times threshold, creating indexes for the target fields, and obtaining an optimized target database, thereby solving the problem of low database access efficiency caused by the high coupling of the basic data of multiple instances in the related art. By selecting the primary key based on the degree of association between the fields in the data table and the target business, splitting the data table, and creating indexes for the fields with high query frequency, the performance of the target database can be significantly optimized, query time can be reduced, and the scalability and data management efficiency of the system can be improved. Thus, the access efficiency of the database is improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] The accompanying drawings, which constitute part of this application, are intended to provide a further understanding of this application. The exemplary embodiments and descriptions of this application are intended to explain this application and do not constitute an improper limitation on this application. In the accompanying drawings:

[0017] Figure 1 is a flowchart of a database optimization method provided according to an embodiment of the present application;

[0018] Figure 2 is a schematic diagram of role-based access rights control provided according to an embodiment of the present application;

[0019] Figure 3is a schematic diagram of a database optimization device provided according to an embodiment of the present application;

[0020] Figure 4 is a schematic diagram of an electronic device provided according to an embodiment of the present application. DETAILED DESCRIPTION

[0021] It should be noted that, in the absence of conflict, the embodiments and features of the embodiments in this application can be combined with each other. The present application will be described in detail below with reference to the accompanying drawings and in combination with the embodiments.

[0022] In order to enable those skilled in the art to better understand the present invention, the following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments in the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts should fall within the scope of protection of this application.

[0023] It should be noted that the terms "first", "second", etc. in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or sequential order. It should be understood that the data used in this way can be interchanged where appropriate, so that the embodiments of the present application described here. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0024] It should be noted that the information collected is information and data authorized by the user or fully authorized by all parties, and the collection, storage, use, processing, transmission, provision, disclosure and application of relevant data comply with the relevant laws, regulations and standards of the relevant regions, take necessary confidentiality measures, do not violate public order and good customs, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0025] The present invention will be described below in conjunction with preferred implementation steps. Figure 1 is a flowchart of a database optimization method provided in accordance with an embodiment of the present application, such as Figure 1 As shown, the method includes the following steps:

[0026] Step S101 : determining M data tables in a target database, and determining a primary key of each data table based on the degree of association between fields in the data table and the target business, where M is a positive integer.

[0027] In step S101, the target database can be a relational database used by the fare publishing system. This database structure is divided into airline users, administrator users, agent users, and public data, based on demand dimensions. These include: NFS_AA (net price airlines: AA is the two-character airline code), NFS_GBL (public data), NFS_TA (agents), and FCIMS (published price airlines). The target database's data structure is primarily driven by business needs. By identifying business data requirements and access patterns, gaining a deep understanding of business logic and transaction requirements, and employing standardized design to reduce data redundancy, the target database is optimized to improve data integrity and query performance, while also reducing update complexity.

[0028] Optimizing data based on the target database requires ensuring the performance, efficiency, data security, and maintainability of existing data operations. To ensure the efficiency, security, and maintainability of data queries, it is necessary to follow the principles of database security and optimization. For example: (1) High availability design: Master-slave replication, which implements data redundancy by setting up a master database and one or more slave databases. When the master database fails, it can quickly switch to the slave database. Cluster deployment, which uses database clusters to provide higher availability and fault tolerance. Load balancing, which distributes requests to different database nodes through a load balancer to disperse pressure and improve the availability of the overall system. (2) Backup and recovery strategy: Regular backup, develop a detailed backup plan, including full backup and incremental backup. Ensure the integrity and consistency of backup data. Multi-point backup, store backup data in multiple geographical locations to prevent data loss due to disasters in a single location. Automated recovery, establish an automated recovery process so that services can be quickly restored in the event of a failure.

[0029] (3) Normalization: Reduce data redundancy. Eliminate data redundancy through normalization and ensure that each data item is stored in only one place. Normalization forms include first normal form, second normal form, third normal form, etc. Avoid over-normalization. Although normalization can reduce redundancy, over-normalization may lead to excessive table join operations, which will affect query performance. In some cases, moderate denormalization may be necessary. (4) Security: Permission control. Under any circumstances, permission policies must be strictly controlled and users of sensitive data must be isolated. Encrypted transmission. Use SSL (Secure Sockets Layer) / TLS (Transport Layer Security) protocols to encrypt database connections to prevent data from being stolen during transmission. Conduct regular security audits to check for unauthorized access or other security vulnerabilities.

[0030] Based on the current status of the relational database used in the freight rate publishing system, the data and business types in the database were analyzed. The database can be optimized through the following optimization methods, but not limited to these. The optimization methods may include: naming standards, effective application of primary keys and foreign keys, index optimization, view and data type selection, security control and regular backup and recovery, performance optimization, partitioning and sharding, etc. Through these effective optimization methods, the database instance can run more efficiently.

[0031] A primary key, or primary keyword, is one or more fields in a table whose values ​​uniquely identify a record within the table. In a relationship between two tables, a primary key is used to reference a specific record in one table from another. A primary key is a unique keyword that is part of the table definition. A table's primary key can be composed of multiple keywords, and the primary key column cannot contain null values. Primary keys are optional.

[0032] For each data table, analyze the degree of relevance of its fields to the target business. Understand the role of each field in the business logic, determine which fields can uniquely identify records in the table, and align with business requirements. Delve into the business processes supported by each table, understand which fields are critical to the business process, and reflect the unique attributes of the business entity. Examine the fields in the data table, looking for fields or combinations of fields that are unique in the entire table. Based on the analysis of business relevance and field uniqueness, the most suitable field can be selected from each data table as the primary key. If there is a field or combination of fields whose data characteristics are closely related to the business logic and can guarantee uniqueness in the entire data set, then these fields can serve as natural primary keys. When there are no obvious natural primary key candidates in the data table, consider introducing an independent, artificially generated surrogate primary key, such as using a sequence, GUID (globally unique identifier), or auto-increment ID.

[0033] Step S102 : splitting the data table into a main table and multiple sub-tables based on the primary key, and partitioning the data table according to the data update date to obtain an updated data table, wherein the data volume of the main table is less than a preset data volume threshold.

[0034] In step S102, each data table can be partitioned by data update date. This allows the database to access only the relevant partitions rather than the entire table when querying data within a specific time period. Furthermore, complex business tables can be split into a master table and multiple sub-tables, reducing the amount of data in the master table. The primary key is used as the basis for the splitting. By splitting the data table using the primary key, the large table can be divided into multiple smaller parts, improving query performance and management efficiency.

[0035] Partitioning methods include horizontal and vertical partitioning. Horizontal partitioning divides a large table into multiple smaller parts, each stored in a different physical location. Vertical partitioning splits the columns in a table into multiple tables, each containing a set of related columns. This reduces the size of a single table and improves query performance. First, determine the partitioning strategy (select horizontal or vertical partitioning based on business needs); then determine the sharding strategy (determine the sharding key); implement partitioning or sharding; and test and optimize the partitioned and sharded data tables, while also monitoring and maintaining them.

[0036] Step S103 : determining target fields in the M updated data tables whose query times are greater than or equal to a query times threshold, creating indexes for the target fields, and obtaining an optimized target database.

[0037] In step S103, an index is a separate, physical storage structure that sorts the values ​​of one or more columns in a database table. It is a collection of values ​​of one or more columns in a table and a corresponding list of logical pointers to the data pages that physically identify these values ​​in the table. When defining a primary key, the database will automatically create a unique index for it. Although this reduces the trouble of manually creating an index for the primary key, it is more important to customize the index in combination with the business requirement fields to meet the performance requirements of specific businesses. When creating an index, you can choose to create a column with high selectivity (that is, a large amount of data with different values ​​in the column). When building a composite index, you should also put the column with the highest selectivity at the front. For example, if a column has only two different values ​​(such as gender), then the effect of this column as an index may not be good. You can give priority to columns that are frequently used in WHERE clauses, JOIN conditions, or ORDER BY clauses.

[0038] For example, you can create indexes based on target fields in a data table whose query counts are greater than or equal to a threshold to improve query performance. Avoid creating indexes on unnecessary columns to reduce index maintenance overhead and the potential performance impact on adding and updating data. By optimizing query statements, designing efficient index structures, and using partitioning strategies, you can significantly improve query speed. Using indexes allows you to quickly locate data, reducing full table scans, and optimized queries return results faster.

[0039] The database optimization method provided by the embodiment of the present application is to determine M data tables in the target database, and for each data table, determine the primary key of the data table based on the degree of association between the fields in the data table and the target business, wherein M is a positive integer; split the data table into a main table and multiple sub-tables based on the primary key, and partition according to the data update date in the data table to obtain an updated data table, wherein the data volume of the main table is less than a preset data volume threshold; determine the target fields in the M updated data tables whose query times are greater than or equal to the query times threshold, create indexes for the target fields, and obtain an optimized target database, thereby solving the problem of low database access efficiency caused by the high coupling of the basic data of the database of multiple instances in the related art. By selecting the primary key based on the degree of association between the fields in the data table and the target business, splitting the data table, and creating indexes for the fields with high query frequency, the performance of the target database can be significantly optimized, the query time is reduced, and the scalability and data management efficiency of the system are improved. Thus, the effect of improving the access efficiency of the database is achieved.

[0040] The primary key can be determined based on the degree of association with the target business. Optionally, in the database optimization method provided in the embodiment of the present application, determining the primary key of the data table based on the degree of association between the field in the data table and the target business includes: determining the association degree evaluation value of each field in the data table and the target business, and determining the field with the association degree evaluation value greater than or equal to the association degree threshold as the pending primary key; determining the pending primary key with an update frequency less than a first update frequency threshold as the natural primary key of the data table; when the data update frequency in the data table is greater than or equal to a second update frequency threshold, creating a proxy primary key for the data table, wherein the second update frequency threshold is greater than the first update frequency threshold; and determining the natural primary key and the proxy primary key as the primary key of the data table.

[0041] In some embodiments, the values ​​of one data table are placed in a second data table to represent an association, and the values ​​used are the primary key values ​​of the first table (composite primary key values ​​may be included if necessary). In this case, the attributes in the second table that store these values ​​are foreign keys. Clarify the metadata of business requirements and define a primary key for each metadata-level data table to ensure the uniqueness and integrity of the metadata. At the same time, foreign keys are used to maintain the association relationship between metadata tables. Natural primary keys and surrogate primary keys are used in combination according to different business scenarios. Natural primary keys are used for data columns that are strongly relevant to the business, that is, the correlation degree assessment value is greater than or equal to the correlation degree threshold and is not easy to change. In tables containing large amounts of data, additional fields with no business meaning are established as surrogate primary keys to improve query efficiency and stability. Use a single field as the primary key and avoid complex composite primary keys unless necessary. Once the primary key is determined, try to avoid modifying it, otherwise it will affect all other tables that rely on the primary key and affect the stability of the data. If a natural primary key is used, ensure that it is readable and meaningful.

[0042] Among them, creating a primary key mainly consists of two steps: selecting appropriate fields (one or more) in the table as the primary key; creating it when building the table or adding it using alter table; creating a foreign key mainly consists of four steps: determining the two data tables to establish a foreign key relationship; defining the foreign key columns in the child table; adding a FOREIGN KEY constraint; and specifying cascading operations.

[0043] For example, gain an in-depth understanding of business processes and data usage, and determine which fields are critical to business logic and can reflect the essential characteristics of business entities. Assign a relevance assessment value to each field in the data table, which reflects the degree of closeness of the field to the target business. The assessment value can be determined based on expert review, data analysis, or business rules. Set a relevance threshold based on business needs and data characteristics. If the relevance assessment value of a field is greater than or equal to this threshold, it is considered a pending primary key. Monitor the update frequency of pending primary key fields to determine whether they are suitable as natural primary keys. The first update frequency threshold is used to screen out fields with lower update frequencies. These fields are more suitable as natural primary keys because their values ​​are relatively stable and can effectively prevent primary key conflicts. Pending primary key fields with an update frequency less than or equal to the first update frequency threshold are determined as natural primary keys.

[0044] For tables with active data updates (i.e., with an update frequency greater than or equal to the second update frequency threshold), create a separate field as a surrogate primary key. A higher second update frequency threshold than the first indicates frequent data updates, making the primary key insufficient for efficient data management. A surrogate primary key can be an auto-incrementing ID, a GUID (globally unique identifier), or another generation mechanism to ensure each record has a unique identifier.

[0045] By quantifying business relevance and analyzing field update frequency, this embodiment can scientifically and rationally determine the primary key of a data table, meeting the uniqueness requirements of business logic while also balancing the efficiency and stability of data management. By properly designing foreign key constraints and triggers, data integrity can be ensured. Optimized backup and recovery strategies can also enhance data reliability, ensuring rapid data recovery in the event of a failure and ensuring business continuity.

[0046] After optimizing the target data, the data can be queried based on the index or partition to improve the data query efficiency. Optionally, in the database optimization method provided in the embodiment of the present application, after obtaining the optimized target database, the method further includes: when a query request for the target database is detected, parsing the query request to obtain the data to be queried; determining whether there is a target index for the data to be queried in the target database; when the target index exists in the target database, retrieving the data to be queried from the target database based on the target index; when the target index does not exist in the target database, determining the update date of the data to be queried, determining the target partition of the data table to which the data to be queried belongs based on the update date, and retrieving the data to be queried from the target partition.

[0047] In some embodiments, when a database query request is detected from a client or application, the request is first parsed to understand the query criteria and target fields, i.e., the data to be queried. The target database is then checked to see if indexes have been created for the key fields in the query criteria. The presence of an index can significantly improve query speed by allowing the database to quickly locate data pages. If the target index exists, the database management system will utilize it to optimize the query process. The index allows for quick locating of relevant records, avoiding a full table scan.

[0048] When the target index doesn't exist, or the index optimization effect isn't significant (for example, the query field isn't indexed), partitioning strategies can be used to further optimize query performance, especially when querying data with time-series characteristics. The most recent update date of the data being queried is obtained or inferred from the query request. This is key information for determining the partition in which the data resides. Based on the update date, the partition to which the data belongs is determined. Once the correct partition is found, data is retrieved only from that specific partition, rather than performing a full table scan. This significantly reduces the search scope and improves query speed.

[0049] This embodiment combines index query with a partitioning strategy based on update date to achieve intelligent and efficient query of the target database, thereby improving the query efficiency of the database.

[0050] If the data to be queried spans multiple tables, it can be queried through views. Optionally, in the database optimization method provided in the embodiment of the present application, after obtaining the data to be queried, the method also includes: when the number of data to be queried is greater than or equal to a preset number threshold, determining the multiple data tables to which the data to be queried belongs; generating a query statement based on the multiple data tables, and executing the query statement to generate a view.

[0051] In some embodiments, a view is a virtual table whose contents are defined by a query. Like a real table, a view contains a series of named columns and row data. However, a view does not exist in the database as a stored set of data values. The row and column data come from the tables referenced by the query that defines the view and are dynamically generated when the view is referenced. Views are used to simplify complex queries and business logic, and improve the efficiency and maintainability of data operations. The steps for selecting and establishing a view can be: clarifying the data source of the view, which can be one or more tables or other views; writing query statements based on the data source, selecting the required columns and data, and executing the query statements; creating a view and naming it. Use views to simplify complex queries, improve security, and provide logical isolation of data.

[0052] In addition, choosing the right data type can save storage space and improve query efficiency. For example, avoid using large data types such as CLOB and BLOB unless necessary. Regarding data types, use the smallest data type that meets your needs and ensure that the selected data type can cover all possible values. Use appropriate data types to maintain data integrity.

[0053] This embodiment determines a preset threshold. When the query data volume exceeds the threshold, it identifies the multiple data tables involved, generates and optimizes multi-table query statements, and ultimately creates a view. This effectively improves database query efficiency and data integration capabilities. Creating a view simplifies data access and enhances system responsiveness and maintainability.

[0054] In order to ensure the security of the database, access permissions are set for the database. Optionally, in the database optimization method provided in the embodiment of the present application, after obtaining the optimized target database, the method further includes: setting target access permissions for the optimized database, wherein the target access permissions include access permissions for multiple roles, and the access permissions for each role are used to limit the access areas and operation types of users of the corresponding role to the optimized database.

[0055] In some embodiments, access permissions and roles are set for the database to limit user operating permissions to protect the confidentiality and integrity of the data. The database is backed up regularly to ensure data security and reliability. At the same time, backup and recovery strategies are tested to deal with possible failure situations. In a multi-user environment, role-based permission management and audit logs are used to protect data from unauthorized access and leakage. By granting permissions to airline users, agent users, and administrator users, appropriate roles are given permissions to access their corresponding database objects (such as NFS_CA, NFS_TA, etc.) and the types of operations they can perform (such as read, write, execute, etc.).

[0056] For example, Figure 2is a schematic diagram of role-based access rights control provided according to an embodiment of the present application, such as Figure 2 As shown in the figure, the target access rights for the airline role are to query the NFS_XX tables in the fare publishing system database and update the FCIMS tables. The target access rights for the airline and agent roles are to query the FCIMS tables and update the NFS_XX tables. The NFS_XX tables include fare tables, rule tables, and group tables, while the FCIMS tables include special fare tables, basic data tables, and city and airport tables.

[0057] This embodiment uses a role-based permission control strategy to strictly limit each user's access rights to the scope of their role, ensuring data security while improving data access efficiency and system manageability. Implementing permission management for the database allows for better management and maintenance of the database, improving database performance and ensuring data reliability and consistency.

[0058] By monitoring the performance of the target database, an optimization strategy is promptly executed on the target database. Optionally, in the database optimization method provided in the embodiment of the present application, after obtaining the optimized target database, the method further includes: when a target query statement executed on the optimized target database is detected, monitoring the execution time of the target query statement; when the execution time is greater than or equal to an execution time threshold, determining the query demand corresponding to the target query statement and the accessed area of ​​the target database; determining the optimization strategy of the target database based on the query demand and the accessed area, and executing the optimization strategy on the target database.

[0059] In some embodiments, by monitoring the execution time of running SQL (Structured Query Language) statements, an email alert is sent if the execution time threshold is exceeded. The email may include the query requirements corresponding to the target query statement and the accessed area of ​​the target database, thereby determining which modules have reduced SQL execution efficiency. The SQL execution plan is analyzed and targeted improvements are made to points that affect performance. For example, when a business scenario requires querying a large data table, an index is built based on the query condition field combination, thereby improving query efficiency and improving the user experience, and ensuring that the index does not incur additional consumption for adding or updating data.

[0060] This embodiment uses a database optimization strategy based on query response time to improve query efficiency, enhance user experience, effectively reduce database server load, and extend system life. Through continuous monitoring and optimization, it can ensure that the database maintains good performance and stability under various business pressures.

[0061] The database maintenance efficiency is improved by standardizing the naming of database objects. Optionally, in the database optimization method provided in the embodiment of the present application, the method further includes: determining N objects in the target database, renaming each object according to a preset naming rule, and obtaining the target database with the updated name, wherein N is a positive integer and the object includes at least one of the following: a data table, a field, a view, and an index.

[0062] In some embodiments, the object can be the Scehma (schema) of the target database, that is, the organization and structure of the database, which can be tables, columns, data types, views, stored procedures, relations, primary keys, foreign keys, etc. In order to better manage and maintain the database, formulate and follow consistent preset naming rules, including table names, column names, index names, etc., use underscores to separate words or camel case naming, etc. For example, choose meaningful English words when naming; use lowercase letters and underscores; avoid using reserved words and keywords; keep names concise; follow a unified naming style; consider database characteristics; add descriptive information. Under the premise of avoiding the use of sql reserved words, define tables, field names, views, and indexes that meet business scenarios according to business needs. Do not use the database's automatic naming of indexes. Name the index with the prefix IDX according to the actual business, and create an index space with the IDX suffix accordingly.

[0063] This embodiment can effectively adjust the naming convention of the database and improve the readability and maintainability of the database by identifying objects in the target database and renaming them according to preset naming rules.

[0064] It should be noted that the steps shown in the flowcharts of the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions, and that, although a logical order is shown in the flowcharts, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0065] The present application also provides a database optimization device. It should be noted that the database optimization device of the present application can be used to execute the database optimization method provided in the present application. The following describes the database optimization device provided in the present application.

[0066] Figure 3 Schematic diagram of a database optimization device according to an embodiment of the present application. Figure 3 As shown, the device includes:

[0067] The first determining unit 301 is configured to determine M data tables in the target database, and for each data table, determine a primary key of the data table based on the degree of association between the fields in the data table and the target business, where M is a positive integer;

[0068] A splitting unit 302 is configured to split the data table into a main table and multiple sub-tables based on the primary key, and partition the data table according to the data update date to obtain an updated data table, wherein the data volume of the main table is less than a preset data volume threshold;

[0069] The second determining unit 303 is configured to determine target fields in the M updated data tables whose query times are greater than or equal to a query times threshold, create indexes for the target fields, and obtain an optimized target database.

[0070] The database optimization device provided in the embodiment of the present application includes a first determining unit 301, which determines M data tables in a target database. For each data table, a primary key of the data table is determined based on the degree of association between the fields in the data table and the target business, where M is a positive integer. A splitting unit 302 splits the data table into a main table and multiple sub-tables based on the primary key, and partitions the sub-tables according to the data update date in the data table to obtain an updated data table, wherein the data volume of the main table is less than a preset data volume threshold. A second determining unit 303 determines target fields in the M updated data tables whose query counts are greater than or equal to the query count threshold, creates indexes for the target fields, and obtains an optimized target database. This solves the problem of low database access efficiency caused by the high coupling of database basic data in multiple instances in the related art. By selecting primary keys based on the degree of association between the fields in the data table and the target business, splitting the data table, and creating indexes for fields with high query frequency, the performance of the target database can be significantly optimized, query time can be reduced, and the scalability and data management efficiency of the system can be improved. This improves the access efficiency of the database.

[0071] Optionally, in the database optimization device provided in the embodiment of the present application, the first determination unit 301 includes: a first determination module, used to determine the association degree evaluation value of each field in the data table with the target business, and determine the field with the association degree evaluation value greater than or equal to the association degree threshold as the pending primary key; a second determination module, used to determine the pending primary key with an update frequency less than the first update frequency threshold as the natural primary key of the data table; a creation module, used to create a proxy primary key for the data table when the data update frequency in the data table is greater than or equal to the second update frequency threshold, wherein the second update frequency threshold is greater than the first update frequency threshold; a third determination module, used to determine the natural primary key and the proxy primary key as the primary key of the data table.

[0072] Optionally, in the database optimization device provided in the embodiment of the present application, the device also includes: a parsing unit, which is used to parse the query request to obtain the data to be queried when a query request to the target database is detected; a judgment unit, which is used to judge whether there is a target index for the data to be queried in the target database; a retrieval unit, which is used to retrieve the data to be queried from the target database based on the target index when the target index exists in the target database; a third determination unit, which is used to determine the update date of the data to be queried when the target index does not exist in the target database, determine the target partition of the data table to which the data to be queried belongs based on the update date, and retrieve the data to be queried from the target partition.

[0073] Optionally, in the database optimization device provided in the embodiment of the present application, the device also includes: a fourth determination unit, used to determine the multiple data tables to which the data to be queried belongs when the number of data to be queried is greater than or equal to a preset number threshold; an execution unit, used to generate a query statement based on the multiple data tables, and execute the query statement to generate a view.

[0074] Optionally, in the database optimization device provided in the embodiment of the present application, the device also includes: a setting unit for setting target access rights for the optimized database, wherein the target access rights include access rights for multiple roles, and the access rights for each role are used to limit the access area and operation type of the optimized database by users of the corresponding role.

[0075] Optionally, in the database optimization device provided in the embodiment of the present application, the device also includes: a monitoring unit, used to monitor the execution time of the target query statement when a target query statement is detected to be executed on the optimized target database; a fifth determination unit, used to determine the query requirements corresponding to the target query statement and the accessed area of ​​the target database when the execution time is greater than or equal to an execution time threshold; a sixth determination unit, used to determine the optimization strategy of the target database based on the query requirements and the accessed area, and execute the optimization strategy on the target database.

[0076] Optionally, in the database optimization device provided in the embodiment of the present application, the device also includes: an eighth determination unit, used to determine N objects in the target database, rename each object according to a preset naming rule, and obtain the target database with the updated name, wherein N is a positive integer, and the object includes at least one of the following: a data table, a field, a view, and an index.

[0077] The database optimization device includes a processor and a memory. The first determination unit 301, splitting unit 302 and second determination unit 303 are all stored in the memory as program units, and the processor executes the program units stored in the memory to implement corresponding functions.

[0078] The processor contains a kernel, which retrieves the corresponding program unit from the memory. One or more kernels can be set, and the efficiency of database access can be improved by adjusting the kernel parameters.

[0079] The memory may include non-permanent memory in a computer-readable medium, random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM, and the memory includes at least one memory chip.

[0080] An embodiment of the present invention provides a computer-readable storage medium having a program stored thereon. When the program is executed by a processor, a method for optimizing a database is implemented.

[0081] An embodiment of the present invention provides a processor, which is used to run a program, wherein the program executes a database optimization method when running.

[0082] Figure 4 Schematic diagram of an electronic device according to an embodiment of the present application. Figure 4 As shown, electronic device 401 includes a processor, a memory, and a program stored in the memory and executable on the processor. When the processor executes the program, the following steps are implemented: determining M data tables in a target database, determining the primary key of each data table based on the degree of association between the fields in the data table and the target business, where M is a positive integer; splitting the data table into a main table and multiple sub-tables based on the primary key, and partitioning the data table according to the data update date, to obtain an updated data table, wherein the data volume of the main table is less than a preset data volume threshold; determining target fields in the M updated data tables whose query counts are greater than or equal to the query count threshold, creating indexes for the target fields, and obtaining an optimized target database. The device herein may be a server, a PC, a PAD, a mobile phone, etc.

[0083] The present application also provides a computer program product, which, when executed on a data processing device, is suitable for executing an initialization program having the following method steps: determining M data tables in a target database, and for each data table, determining a primary key of the data table based on the degree of association between the fields in the data table and the target business, wherein M is a positive integer; splitting the data table into a main table and multiple sub-tables based on the primary key, and partitioning the data in the data table according to the data update date to obtain an updated data table, wherein the data volume of the main table is less than a preset data volume threshold; determining target fields in the M updated data tables whose query times are greater than or equal to the query time threshold, creating indexes for the target fields, and obtaining an optimized target database.

[0084] Those skilled in the art will appreciate that the embodiments of the present application can be provided as methods, systems, or computer program products. Therefore, the present application can adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application can adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) that contain computer-usable program code.

[0085] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the steps in the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0086] These computer program instructions may also be stored in a computer readable memory that can direct a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0087] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

[0088] In a typical configuration, a computing device includes one or more processors (CPUs), input / output interfaces, network interfaces, and memory.

[0089] The memory may include non-permanent memory in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. The memory is an example of a computer-readable medium.

[0090] Computer-readable media includes permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. The information can be computer-readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, magnetic disk storage or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include transitory computer-readable media (transitory media), such as modulated data signals and carrier waves.

[0091] It should also be noted that the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, commodity, or apparatus that includes a series of elements includes not only those elements but also other elements not explicitly listed, or includes elements inherent to such process, method, commodity, or apparatus. In the absence of further limitations, an element defined by the phrase "comprises a ..." does not exclude the presence of other identical elements in the process, method, commodity, or apparatus that includes the element.

[0092] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Furthermore, the present application may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0093] The above are merely embodiments of the present application and are not intended to limit the present application. For those skilled in the art, the present application may have various changes and variations. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present application should all be included within the scope of the claims of the present application.

Claims

1. A database optimization method, characterized in that: include: Determine M data tables in the target database, and for each data table, determine a primary key of the data table based on the degree of association between a field in the data table and the target business, where M is a positive integer; Splitting the data table into a main table and multiple sub-tables based on the primary key, and partitioning the data table according to the data update date, to obtain an updated data table, wherein the data volume of the main table is less than a preset data volume threshold; Determine a target field in the M updated data tables whose query times are greater than or equal to a query times threshold, create an index for the target field, and obtain an optimized target database.

2. The method according to claim 1, characterized in that Determining the primary key of the data table based on the degree of association between the fields in the data table and the target business includes: Determine an evaluation value of the degree of association between each field in the data table and the target business, and determine the field whose evaluation value of the degree of association is greater than or equal to a threshold value of the degree of association as a pending primary key; Determine a pending primary key whose update frequency is less than a first update frequency threshold as a natural primary key of the data table; creating a surrogate primary key for the data table when the data update frequency in the data table is greater than or equal to a second update frequency threshold, wherein the second update frequency threshold is greater than the first update frequency threshold; The natural primary key and the surrogate primary key are determined as primary keys of the data table.

3. The method according to claim 1, characterized in that After obtaining the optimized target database, the method further includes: When a query request to the target database is detected, parsing the query request to obtain data to be queried; Determine whether the target index of the data to be queried exists in the target database; If the target index exists in the target database, retrieving the data to be queried from the target database based on the target index; If the target index does not exist in the target database, an update date of the data to be queried is determined, a target partition of the data table to which the data to be queried belongs is determined based on the update date, and the data to be queried is retrieved from the target partition.

4. The method according to claim 3, characterized in that After obtaining the data to be queried, the method further includes: When the amount of the data to be queried is greater than or equal to a preset amount threshold, determining a plurality of data tables to which the data to be queried belongs; A query statement is generated based on the multiple data tables, and the query statement is executed to generate a view.

5. The method according to claim 1, wherein After obtaining the optimized target database, the method further includes: Target access permissions are set for the optimized database, wherein the target access permissions include access permissions for multiple roles, and the access permissions for each role are used to limit the access area and operation type of the optimized database by users of the corresponding role.

6. The method according to claim 1, characterized in that After obtaining the optimized target database, the method further includes: When detecting a target query statement executed on the optimized target database, monitoring the execution time of the target query statement; When the execution time is greater than or equal to the execution time threshold, determining the query requirement corresponding to the target query statement and the accessed area of ​​the target database; An optimization strategy for the target database is determined based on the query requirement and the accessed area, and the optimization strategy is executed on the target database.

7. The method according to claim 1, characterized in that The method further comprises: Determine N objects in a target database, rename each object according to a preset naming rule, and obtain a target database with an updated name, wherein N is a positive integer, and the objects include at least one of the following: a data table, a field, a view, and an index.

8. A database optimization device, characterized in that: include: A first determining unit is configured to determine M data tables in a target database, and for each data table, determine a primary key of the data table based on a degree of association between a field in the data table and a target business, where M is a positive integer; a splitting unit, configured to split the data table into a main table and multiple sub-tables based on the primary key, and partition the data table according to a data update date in the data table to obtain an updated data table, wherein the data volume of the main table is less than a preset data volume threshold; The second determining unit is configured to determine a target field in the M updated data tables whose query times are greater than or equal to a query times threshold, create an index for the target field, and obtain an optimized target database.

9. A computer program product comprising a computer program, characterized in that When the computer program is executed by a processor, the database optimization method according to any one of claims 1 to 7 is implemented.

10. An electronic device, characterized in that: The system comprises one or more processors and a memory, wherein the memory is used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors implement the database optimization method described in any one of claims 1 to 7.

Images