Index construction method and device, storage medium and electronic equipment
By detecting the acceleration conditions of the target SQL statement and generating index construction tasks in the model dimension, the problem of the surge in the number of tasks in the traditional index construction method is solved, and more efficient index construction and database performance improvement is achieved.
Patent Information
- Application Number
- CN202411382193.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-09-30
- Publication Date
- 2025-01-03
AI Technical Summary
When traditional index construction methods process large amounts of data segments, the number of construction tasks surges, causing tasks to wait in line, affecting system performance and response speed.
By obtaining the target SQL statement to be processed, detect whether the acceleration conditions are met, generate and execute the index construction task, and use the Kylin model to conduct index construction in the model dimension to reduce the number of construction tasks.
It effectively reduces the number of construction tasks, improves index construction efficiency, avoids task accumulation and delays, and improves the overall performance and response speed of the database.
Smart Images
Figure CN119248786A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of data processing technology, and in particular to an index construction method, device, storage medium and electronic device. Background Art
[0002] In the existing field of database optimization technology, especially in dealing with large-scale data sets and complex query optimization, a common challenge is how to efficiently manage and optimize the access efficiency of data models. In order to improve query performance, database management systems (DBMS) often adopt the strategy of indexing specific data segments (Segments) to speed up the data retrieval process. However, when facing complex models containing a large number of segments, traditional index construction methods often adopt a full refresh method, that is, generating independent construction tasks for each segment one by one. This strategy will lead to a surge in the number of construction tasks when dealing with a large number of segments, which will in turn cause tasks to queue up, which not only increases system resource consumption, but also seriously affects the data loading and refresh efficiency of other model segments, and may even cause overall system performance bottlenecks.
[0003] Specifically, under the full refresh strategy, the number of build tasks is directly equal to the number of segments of the optimized model, which means that for each additional segment, an additional build task is required. In a big data environment, this linear growth in the number of build tasks will soon exceed the system's processing capacity, causing task accumulation and delays, which will have an adverse impact on the overall performance and response speed of the database.
[0004] Application Contents
[0005] The technical problem to be solved by this application is to provide an index construction method, device, storage medium and electronic device, which can reduce the number of construction tasks. The specific solution is as follows:
[0006] An index building method, comprising:
[0007] Get each target SQL statement to be processed;
[0008] For each of the target SQL statements, when it is detected that the target SQL statement meets the preset acceleration condition, the optimization prompt information of the target SQL statement is obtained; when the optimization prompt information includes the index information and model information of the target model, the acceleration index corresponding to the target SQL statement is generated according to the index information, and the model information is stored in a preset record table; the target model is a Kylin model used to accelerate the target SQL statement;
[0009] Generate an index building task corresponding to each of the target models, and execute each of the index building tasks to complete the index building of each of the target models, so that the acceleration index corresponding to each of the target SQL statements is configured to accelerate each of the target SQL statements.
[0010] In the above method, optionally, the generating of the index building task corresponding to each target model includes:
[0011] Obtaining a target model identifier for each target model;
[0012] The preset model building interface is called according to the target model identifier of each target model to obtain the index building task corresponding to each target model.
[0013] The above method may optionally include: obtaining the target SQL statement to be processed;
[0014] Get a list of SQL statements;
[0015] Perform non-empty verification, number limit verification, and non-Kylin data source verification on the SQL statement list to obtain the verification result of the SQL statement list;
[0016] When the verification result indicates that the verification has passed, concatenating the SQL statements in the SQL statement list;
[0017] The concatenated SQL statements are formatted to obtain a target SQL statement to be processed.
[0018] In the above method, optionally, generating the acceleration index corresponding to the target SQL statement according to the index information includes:
[0019] A preset acceleration index interface is called according to the index information to generate the acceleration index.
[0020] In the above method, optionally, the process of detecting that the target SQL statement meets a preset acceleration condition includes:
[0021] Calling a preset model detection interface to detect whether there is a target model capable of accelerating the target SQL statement;
[0022] If there is a target model capable of accelerating the target SQL statement, it is determined that the target SQL statement meets a preset acceleration condition.
[0023] The above method may optionally further include:
[0024] When the optimization prompt information is empty, a prompt message is output, where the prompt message is used to prompt that the target SQL statement already has an acceleration index.
[0025] An index building device, comprising:
[0026] An acquisition unit, used for acquiring each target SQL statement to be processed;
[0027] An execution unit is used for obtaining, for each target SQL statement, optimization hint information of the target SQL statement when it is detected that the target SQL statement meets a preset acceleration condition; when the optimization hint information includes index information and model information of a target model, generating an acceleration index corresponding to the target SQL statement according to the index information, and storing the model information in a preset record table; the target model is a Kylin model used to accelerate the target SQL statement;
[0028] An index building unit is used to generate an index building task corresponding to each of the target models and execute each of the index building tasks to complete the index building of each of the target models, so that the acceleration index corresponding to each of the target SQL statements is configured to accelerate each of the target SQL statements.
[0029] In the above device, optionally, the index building unit includes:
[0030] An acquisition subunit, used to acquire a target model identifier of each target model;
[0031] The index building subunit is used to call a preset model building interface according to the target model identifier of each target model to obtain the index building task corresponding to each target model.
[0032] A storage medium includes stored instructions, wherein when the instructions are executed, the device where the storage medium is located is controlled to execute the index building method as described above.
[0033] An electronic device includes a memory and one or more instructions, wherein the one or more instructions are stored in the memory and are configured to be executed by one or more processors to execute the index building method as described above.
[0034] Based on the above-mentioned implementation of this application, an index construction method, device, storage medium and electronic device are provided, the method includes: obtaining each target SQL statement to be processed; for each target SQL statement, when it is detected that the target SQL statement meets the preset acceleration condition, obtaining the optimization prompt information of the target SQL statement; when the optimization prompt information includes the index information and model information of the target model, generating the acceleration index corresponding to the target SQL statement according to the index information, and storing the model information in a preset record table; the target model is a Kylin model for accelerating the target SQL statement; generating an index construction task corresponding to each target model, and executing each index construction task to complete the index construction of each target model, so that the acceleration index corresponding to each target SQL statement is configured to accelerate each target SQL statement. Applying the method provided in the embodiment of this application, index construction can be performed in the model dimension, and the number of tasks can be effectively constructed. BRIEF DESCRIPTION OF THE DRAWINGS
[0035] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying any creative work.
[0036] Figure 1 A method flow chart of an index construction method provided in this application;
[0037] Figure 2 A flowchart of a process for obtaining a target SQL statement to be processed provided by the present application;
[0038] Figure 3 A flowchart of a process for detecting that a target SQL statement meets a preset acceleration condition provided by the present application;
[0039] Figure 4 A schematic diagram of the structure of an index building device provided in this application;
[0040] Figure 5 A schematic diagram of the structure of an electronic device provided in this application. DETAILED DESCRIPTION
[0041] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.
[0042] In this application, the terms "comprises", "comprising" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "comprising a ..." does not exclude the presence of other identical elements in the process, method, article or device comprising the element.
[0043] The present application embodiment provides an index construction method, which can be applied to electronic devices, such as computers, tablets, smart phones, and smart wearable devices. The method flow chart of the method is as follows: Figure 1 As shown, specifically including:
[0044] S101: Obtain each target SQL statement to be processed.
[0045] In this embodiment, the initial SQL statements are first obtained. These initial SQL statements are displayed in the form of a dashboard component access record ID list, and their status is displayed after each statement, specifically, not accelerated, accelerated successfully, accelerated failed, and optimal index already exists. Then, the SQL statements to be processed are selected from the initial SQL statements, wherein the selection can be made manually according to the requirements, or automatically selected by setting rules, such as setting a time threshold, and when the query time of a certain initial SQL statement exceeds the time threshold, it is selected as the SQL statement to be processed.
[0046] Optionally, each SQL statement may be verified, and the SQL statements that pass the verification may be concatenated to obtain a target SQL statement.
[0047] In an embodiment provided in the present application, based on the above solution, optionally, the process of obtaining the target SQL statement to be processed is as follows: Figure 2 As shown, including:
[0048] S201: Obtain a list of SQL statements.
[0049] Optionally, the SQL statement list includes multiple SQL statements to be processed.
[0050] S202: Perform non-empty verification, number limit verification, and non-Kylin data source verification on the SQL statement list to obtain the verification result of the SQL statement list.
[0051] In this embodiment, when the SQL statement in the SQL statement list is not empty, it is determined that the non-empty check of the SQL statement in the SQL statement list passes; otherwise, the non-empty check fails.
[0052] Optionally, when the number of SQL statements in the SQL statement list is less than a preset number, it is determined that the number limit check of the SQL statements in the SQL statement list passes; otherwise, the number limit check fails.
[0053] Optionally, when the data source of the SQL statement is Kylin, determine the non-Kylin data source verification of the SQL statement in the SQL statement list, otherwise, the non-Kylin data source verification fails.
[0054] S203: When the verification result indicates that the verification is passed, concatenate the SQL statements in the SQL statement list.
[0055] Optionally, when the verification result indicates that the non-empty check, the number limit check, and the non-Kylin data source check of the SQL statement list have all passed, the SQL statements in the SQL statement list that have passed the verification are concatenated.
[0056] In this embodiment, SQL statements can be spliced according to Kylin's acceleration requirements.
[0057] S204: Formatting the spliced SQL statements to obtain a target SQL statement to be processed.
[0058] In this embodiment, the SQL formatting interface of Kylin is called to format the concatenated SQL statements to obtain the target SQL statement.
[0059] In this embodiment, the Kylin models to which different target SQL statements belong may be the same or different.
[0060] S102: For each of the target SQL statements, when it is detected that the target SQL statement meets the preset acceleration conditions, obtain optimization hint information of the target SQL statement; when the optimization hint information includes index information and model information of the target model, generate an acceleration index corresponding to the target SQL statement according to the index information, and store the model information in a preset record table; the target model is a Kylin model used to accelerate the target SQL statement.
[0061] In this embodiment, the model information may include information such as the model name and ID; the index information may include content such as the accelerated index field.
[0062] In an embodiment provided by the present application, based on the above solution, optionally, a process of detecting that the target SQL statement meets a preset acceleration condition is performed, such as Figure 3 As shown, including:
[0063] S301: calling a preset model detection interface to detect whether there is a target model capable of accelerating the target SQL statement.
[0064] S302: If there is a target model capable of accelerating the target SQL statement, determine whether the target SQL statement meets a preset acceleration condition.
[0065] S303: If there is no target model capable of accelerating the target SQL statement, it is determined that the target SQL statement does not meet the acceleration condition.
[0066] In an embodiment provided in the present application, based on the above solution, optionally, it further includes:
[0067] When the optimization prompt information is empty, a prompt message is output, where the prompt message is used to prompt that the target SQL statement already has an acceleration index.
[0068] In this embodiment, it is determined whether the optimization prompt information is empty. If the optimization prompt information is empty, it means that the model matching the target SQL statement can complete the acceleration of the target SQL statement, and no further optimization is required. Therefore, the state of the target SQL statement in the dashboard component access record ID list is changed to the optimal index already exists, and the acceleration of the target SQL statement is stopped.
[0069] S103: Generate an index building task corresponding to each of the target models, and execute each of the index building tasks to complete the index building of each of the target models, so that the acceleration index corresponding to each of the target SQL statements is configured to accelerate each of the target SQL statements.
[0070] In this embodiment, for each target model, a preset index building interface may be called according to the ID of the target model to build the index to be built of the target model.
[0071] By applying the method provided in the embodiment of the present application, index construction can be performed in the model dimension, and multiple construction tasks that may have been generated for multiple segments can be integrated into a single construction task, thereby effectively increasing the number of construction tasks.
[0072] Optionally, the index to be constructed may be an acceleration index of the target SQL statement.
[0073] In an embodiment provided in the present application, based on the above solution, optionally, generating an index building task corresponding to each target model includes:
[0074] Obtaining a target model identifier for each target model;
[0075] The preset model building interface is called according to the target model identifier of each target model to obtain the index building task corresponding to each target model.
[0076] In this embodiment, by calling a unified index building interface, index building can be performed in the model dimension, and multiple building tasks that may have been generated for multiple segments can be integrated into a single building task, which can effectively increase the number of building tasks.
[0077] In an embodiment provided by the present application, based on the above solution, optionally, generating an acceleration index corresponding to the target SQL statement according to the index information includes:
[0078] A preset acceleration index interface is called according to the index information to generate the acceleration index.
[0079] In one embodiment provided in this application, the steps for implementing SQL one-key acceleration are as follows:
[0080] Step 1: Input parameters. In this embodiment, the input parameters may include the SQL to be accelerated and a dashboard component access record ID list.
[0081] Step 2: Check the SQL list to be accelerated for non-empty, SQL number size limit, and non-Kylin data source. If the check passes, proceed to step 3. Otherwise, return to step 1.
[0082] Step 3: Splice the SQL statements according to the Kylin application-side acceleration requirements. In this embodiment, the SQL statements can be spliced using a splicing symbol corresponding to the acceleration requirements, and the splicing symbol can be any symbol, for example, ";".
[0083] Step 4: Call Kylin’s SQL formatting interface to accelerate SQL formatting.
[0084] Step 5: Based on the formatted SQL, call Kylin's model matching interface to determine whether there is a model that can achieve SQL acceleration. If the return result is yes, proceed to step 6; otherwise, an exception is thrown and it is prompted that there is a data table that has not built a Kylin model. Please go to the data management module to publish it.
[0085] Step 6: Call Kylin's optimization suggestion interface for the formatted SQL to obtain optimization suggestions (that is, the optimization prompt information mentioned above).
[0086] Step 7: Determine whether the optimization suggestion returned in step 6 is empty. If it is empty, the Kylin one-key acceleration function ends, and the front-end prompts that the optimal index already exists for the current accelerated SQL. If it is not empty, execute step 8.
[0087] Step 8: Call Kylin's model recommendation interface according to the optimization suggestions, generate the optimal index of the Kylin model, and save the optimized model information to Oracle's SQL acceleration success record table.
[0088] Step 9: Get the optimized Kylin model ID list and perform the following operations: For each Kylin model modelId, call Kylin's index building interface to build the index to be built for the model.
[0089] It should be noted that in the related technology, the Segment list of the optimized model is fully refreshed. At this time, the number of build tasks is equal to the number of segments of the optimized model. When the number of segments is too large, it is easy for build tasks to wait in line, affecting the loading and refresh of segment data of other models; therefore, in the embodiment of the present application, for the new index generated by the one-click acceleration function of slow SQL, the number of build tasks generated by calling the index build interface is 1, which greatly reduces the number of build tasks and improves the build efficiency.
[0090] In this embodiment, after the Kylin model obtains and generates the optimal index, the full refresh Segment is optimized by loading data, and the optimization model is directly built without index. The number of build tasks generated at this time is equal to the number of optimized models, that is, one SQL statement is accelerated to optimize one model to generate one optimal index, which corresponds to one build task. At this time, compared with the previous full refresh of the Segment list, the number of build tasks generated is greatly reduced, and the build time is also reduced, which improves the overall build efficiency.
[0091] and Figure 1 Corresponding to the method described above, the embodiment of the present application also provides an index construction device for Figure 1 The specific implementation of the method in the embodiment of the present invention is shown in the structural diagram of the device. Figure 4 As shown, including:
[0092] An acquisition unit 401 is used to acquire each target SQL statement to be processed;
[0093] The execution unit 402 is used for obtaining, for each target SQL statement, optimization hint information of the target SQL statement when it is detected that the target SQL statement meets a preset acceleration condition; when the optimization hint information includes index information and model information of a target model, generating an acceleration index corresponding to the target SQL statement according to the index information, and storing the model information in a preset record table; the target model is a Kylin model used to accelerate the target SQL statement;
[0094] The index building unit 403 is used to generate an index building task corresponding to each of the target models, and execute each of the index building tasks to complete the index building of each of the target models, so that the acceleration index corresponding to each of the target SQL statements is configured to accelerate each of the target SQL statements.
[0095] In an embodiment provided by the application, based on the above solution, optionally, the index building unit 403 includes:
[0096] A first acquisition subunit is used to acquire a target model identifier of each target model;
[0097] The index building subunit is used to call a preset model building interface according to the target model identifier of each target model to obtain the index building task corresponding to each target model.
[0098] In an embodiment provided by the application, based on the above solution, optionally, the acquisition unit includes:
[0099] The second acquisition subunit is used to acquire a SQL statement list;
[0100] A verification subunit, used to perform non-empty verification, number limit verification and non-Kylin data source verification on the SQL statement list to obtain the verification result of the SQL statement list;
[0101] A splicing subunit, configured to splice the SQL statements in the SQL statement list if the verification result indicates that the verification has passed;
[0102] The processing subunit is used to format the spliced SQL statements to obtain the target SQL statement to be processed.
[0103] In an embodiment provided by the application, based on the above solution, optionally, the execution unit includes:
[0104] The generating subunit is used to call a preset acceleration index interface according to the index information to generate the acceleration index.
[0105] In an embodiment provided by the application, based on the above solution, optionally, the execution unit includes:
[0106] A calling subunit, used for calling a preset model detection interface to detect whether there is a target model capable of accelerating the target SQL statement;
[0107] The determination subunit is configured to determine whether the target SQL statement satisfies a preset acceleration condition if there is a target model capable of accelerating the target SQL statement.
[0108] In an embodiment provided by the application, based on the above solution, optionally, it also includes:
[0109] The output unit is used to output a prompt message when the optimization prompt information is empty, wherein the prompt message is used to prompt that the target SQL statement already has an acceleration index.
[0110] The specific principles and execution processes of each unit and module in the index construction device disclosed in the above-mentioned embodiment of the present application are the same as the index construction method disclosed in the above-mentioned embodiment of the present application. Please refer to the corresponding parts of the index construction method provided in the above-mentioned embodiment of the present application, and will not be repeated here.
[0111] An embodiment of the present application further provides a storage medium, which includes stored instructions, wherein when the instructions are executed, the device where the storage medium is located is controlled to execute the above-mentioned index construction method.
[0112] The present application also provides an electronic device, whose structural diagram is as follows: Figure 5 As shown, it specifically includes a memory 501 and one or more instructions 502, wherein the one or more instructions 502 are stored in the memory 501 and are configured to be executed by one or more processors 503 to perform the following operations:
[0113] Get each target SQL statement to be processed;
[0114] For each of the target SQL statements, when it is detected that the target SQL statement meets the preset acceleration condition, the optimization prompt information of the target SQL statement is obtained; when the optimization prompt information includes the index information and model information of the target model, the acceleration index corresponding to the target SQL statement is generated according to the index information, and the model information is stored in a preset record table; the target model is a Kylin model used to accelerate the target SQL statement;
[0115] Generate an index building task corresponding to each of the target models, and execute each of the index building tasks to complete the index building of each of the target models, so that the acceleration index corresponding to each of the target SQL statements is configured to accelerate each of the target SQL statements.
[0116] It should be noted that each embodiment in this specification is described in a progressive manner, and each embodiment focuses on the differences from other embodiments, and the same or similar parts between the embodiments can be referred to each other. For the device embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.
[0117] Finally, it should be noted that, in this article, relational terms such as first and second, etc. are merely used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations.
[0118] For the convenience of description, the above device is described in various units according to their functions. Of course, when implementing the present application, the functions of each unit can be implemented in the same or multiple software and / or hardware.
[0119] It can be known from the description of the above implementation methods that those skilled in the art can clearly understand that the present application can be implemented by means of software plus a necessary general hardware platform. Based on such an understanding, the technical solution of the present application can be essentially or partly contributed to the prior art in the form of a software product, which can be stored in a storage medium such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes several instructions for enabling a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in the various embodiments of the present application or certain parts of the embodiments.
[0120] The above is a detailed introduction to an index construction method provided by the present application. Specific examples are used in this article to illustrate the principles and implementation methods of the present application. The description of the above embodiments is only used to help understand the method of the present application and its core idea; at the same time, for general technical personnel in this field, according to the idea of the present application, there will be changes in the specific implementation method and application scope. In summary, the content of this specification should not be understood as a limitation on the present application.
Claims
1. An index construction method, characterized in that: include: Get each target SQL statement to be processed; For each of the target SQL statements, when it is detected that the target SQL statement meets a preset acceleration condition, obtaining optimization prompt information of the target SQL statement; In the case where the optimization prompt information includes index information and model information of the target model, generating an acceleration index corresponding to the target SQL statement according to the index information, and storing the model information in a preset record table; The target model is a Kylin model used to accelerate the target SQL statement; Generate an index building task corresponding to each of the target models, and execute each of the index building tasks to complete the index building of each of the target models, so that the acceleration index corresponding to each of the target SQL statements is configured to accelerate each of the target SQL statements.
2. The method according to claim 1, characterized in that The generating of the index building task corresponding to each target model includes: Obtaining a target model identifier for each target model; The preset model building interface is called according to the target model identifier of each target model to obtain the index building task corresponding to each target model.
3. The method according to claim 1, characterized in that The process of obtaining the target SQL statement to be processed includes: Get a list of SQL statements; Perform non-empty verification, number limit verification, and non-Kylin data source verification on the SQL statement list to obtain the verification result of the SQL statement list; When the verification result indicates that the verification has passed, concatenating the SQL statements in the SQL statement list; The concatenated SQL statements are formatted to obtain a target SQL statement to be processed.
4. The method according to claim 1, characterized in that Generating the acceleration index corresponding to the target SQL statement according to the index information includes: A preset acceleration index interface is called according to the index information to generate the acceleration index.
5. The method according to claim 1, characterized in that The process of detecting that the target SQL statement meets the preset acceleration condition includes: Calling a preset model detection interface to detect whether there is a target model capable of accelerating the target SQL statement; If there is a target model capable of accelerating the target SQL statement, it is determined that the target SQL statement meets a preset acceleration condition.
6. The method according to claim 1, characterized in that Also includes: When the optimization prompt information is empty, a prompt message is output, where the prompt message is used to prompt that the target SQL statement already has an acceleration index.
7. An index building device, characterized in that: include: An acquisition unit, used for acquiring each target SQL statement to be processed; An execution unit, for each target SQL statement, when detecting that the target SQL statement meets a preset acceleration condition, obtains optimization prompt information of the target SQL statement; In the case where the optimization prompt information includes index information and model information of the target model, generating an acceleration index corresponding to the target SQL statement according to the index information, and storing the model information in a preset record table; The target model is a Kylin model used to accelerate the target SQL statement; An index building unit is used to generate an index building task corresponding to each of the target models and execute each of the index building tasks to complete the index building of each of the target models, so that the acceleration index corresponding to each of the target SQL statements is configured to accelerate each of the target SQL statements.
8. The device according to claim 7, characterized in that The index building unit includes: An acquisition subunit, used to acquire a target model identifier of each target model; The index building subunit is used to call a preset model building interface according to the target model identifier of each target model to obtain the index building task corresponding to each target model.
9. A storage medium, characterized in that: The storage medium includes stored instructions, wherein when the instructions are executed, the device where the storage medium is located is controlled to execute the index building method according to any one of claims 1 to 6.
10. An electronic device, characterized in that: The system comprises a memory and one or more instructions, wherein the one or more instructions are stored in the memory and configured to be executed by one or more processors to execute the index building method according to any one of claims 1 to 6.