A method and device for improving database query efficiency based on the Nuo Xue programming language

Abstract

The present application relates to the field of improving database query efficiency based on the Ganchu programming language, and particularly to a method and device for improving database query efficiency based on the Ganchu programming language, which comprises the following steps: acquiring the starting calling time point and calling completion time consumption of all database similar select statements in a time period; calculating the weighted standard deviation and weighted average based on the starting calling point and calling completion time consumption of each select statement; S3, selecting a database instance as the next select statement query call based on the weighted standard deviation and weighted average. The present application increases the calling weight of the select statement closer to the current time point, thereby calculating the weighted standard deviation and weighted average of different database instances according to different calling weights, and selecting an optimal database instance from multiple database instances as the next select statement query call based on the weighted standard deviation and weighted average, so as to improve the efficiency of the next database query.

Description

Technical Field

[0001] This invention relates to the field of improving database query efficiency based on the Cangjie programming language, and in particular to a method and apparatus for improving database query efficiency based on the Cangjie programming language. Background Technology

[0002] With the development of technologies such as the Internet and the Internet of Things, the amount of data generated daily is growing exponentially. These massive datasets require effective storage and management, and databases are systems designed specifically for this purpose. Database technology enables rapid querying and extraction of necessary information, facilitating and supporting various effective decisions. Therefore, the accuracy and timeliness of data are crucial for formulating effective strategies. However, computing resources are always limited. When there are many concurrent queries, databases inevitably become sluggish, struggling to quickly query and return the required data. Therefore, database clustering technology is needed to support this, distributing query requests across multiple instances in the database cluster, thereby achieving the effect of timely return of queried data even under high concurrency.

[0003] There are currently many technologies for connecting to database clusters, such as round-robin and least-connection methods. Round-robin accesses instances in the database cluster in turn, while least-connection accesses the instance with the fewest connections in the database cluster.

[0004] With the development of the times, and recognizing Chinese as an efficient and secure language, the Cangjie programming language has emerged. As a modern programming language designed for application development across all scenarios, Cangjie integrates modern language features, provides comprehensive compilation optimization and runtime implementation, and offers out-of-the-box IDE toolchain support, creating a user-friendly development experience and superior program performance for developers. The Cangjie programming language's database driver provides a plugin mechanism, particularly the ServerAddr plugin and the Stmt statement plugin, laying the foundation for writing code that efficiently accesses database cluster instances.

[0005] While each instance in a database cluster can provide query services, not every instance can respond and return data quickly. This is affected by many factors, such as the varying hardware configurations of the machines hosting the database instances, the different CPU and disk I / O resource usage of other applications on those machines, and the varying network latency between the client and each database instance. These factors result in differences in the concurrency each database instance can handle. Therefore, simple methods are insufficient to maximize the utilization of database cluster instance resources and have various drawbacks. For example, the drawback of round-robin is that if a slow-responding instance is selected, the query will be slow; the drawback of the instance with the fewest connections is that the instance with the fewest connections is not necessarily the optimal instance, as it may be located on a machine with high network latency, among other factors, leading to a slow query. Therefore, the current query method is not very efficient and still has significant room for improvement. Summary of the Invention

[0006] To improve the efficiency of subsequent database queries, this invention provides a method and apparatus for improving database query efficiency based on the Cangjie programming language. This method increases the call weight of select statements closer to the current time point, thereby calculating the weighted standard deviation and weighted average of different database instances based on these different call weights. Then, based on the weighted standard deviation and weighted average, the optimal database instance is selected from multiple database instances as the next select statement query call, thus improving the efficiency of the next database query.

[0007] Firstly, the present invention provides a method for improving database query efficiency based on the Cangjie programming language, which adopts the following technical solution:

[0008] A method for improving database query efficiency based on the Cangjie programming language includes the following steps:

[0009] S1, Obtain time period T l The start time T of all similar SELECT statements in the database. s and the time T taken to complete the call c The call completion time T c It is the difference between the end time of the call and the start time of the call;

[0010] S2, based on the starting point T of each select statement. s and the time T taken to complete the call c Calculate the weighted standard deviation σ and the weighted mean μ;

[0011] S21. Assume the weight of the time taken to complete the nth select statement is W.n The current time point is T t And define the distance from the current time point T. t The more recent the SELECT statement, the higher its call weight, which is T. l -(T t -T s ) = T l +T s -T t Then the weight W n =max(T) l +T s -T t ,1);

[0012] S22, Assuming time period T l A database instance executes n similar SELECT statements. Let T be the time taken to complete the nth SELECT statement. cn The average time taken to complete n calls is in,

[0013] The formulas for calculating the weighted standard deviation σ and weighted average μ of the time taken to complete a SELECT statement call of the same type in this database instance are as follows:

[0014]

[0015] μ=∑W n T cn / ∑W n ;

[0016] S3. Based on the weighted standard deviation σ and the weighted average μ, select one database instance from multiple database instances as the next select statement query call.

[0017] Preferably, the completion time T for the SELECT statement in S1 that has not yet finished is... c Select the current time point and the start time point T. s The difference between the two values ​​and the maximum value of the previous call completion time is used as the call completion time T. c ;

[0018] For the completion time of the select statement that caused the exception in step S1, the current time point and the start time point T are selected. s The difference between the two values, plus twice the maximum value of the previous call completion time, is taken as the call completion time T. c .

[0019] Preferably, S3 includes:

[0020] The value of σ+2μ is calculated based on the weighted standard deviation σ and the weighted average μ, and the database instance with the smallest value of σ+2μ is selected from multiple database instances as the next SELECT statement query call.

[0021] Secondly, the present invention provides a device for improving database query efficiency based on the Cangjie programming language, which adopts the following technical solution:

[0022] A device for improving database query efficiency based on the Cangjie programming language, comprising:

[0023] Call the module to obtain the time period T l The start time T of all similar SELECT statements in the database. s and the time T taken to complete the call c The call completion time T c It is the difference between the end time of the call and the start time of the call;

[0024] The calculation module is used to calculate based on the starting call point T of each select statement. s and the time T taken to complete the call c Calculate the weighted standard deviation σ and the weighted mean μ;

[0025] The weight calculation unit is used to calculate the weight based on the current time point T. t , distance from the current time point T t The more recent the SELECT statement, the higher its call weight. The call weight is calculated as T. l -(T t -T s ) = T l +T s -T t ;

[0026] The weight determination unit is used to determine the weight T to be called. l +T s -T t If the value is less than 1, proceed to the first weight assignment unit; otherwise, proceed to the second weight assignment unit.

[0027] The first weight assignment unit, with weight W. n The value is assigned to 1;

[0028] The second weight assignment unit, weight W n The value is T l +T s -T t The value;

[0029] The average time calculation unit is used to calculate the time T for completing the nth call. cnCalculate the average time taken to complete n calls. in,

[0030] The weighted standard deviation calculation unit is used to calculate the standard deviation based on the weight W. n The time T taken to complete the call cn and average call completion time Calculate the weighted standard deviation σ, where,

[0031] The weighted average calculation unit is used to calculate the average based on weight W. n The time T taken to complete the call cn Calculate the weighted average μ, where μ = ∑W n T cn / ∑W n ;

[0032] The filtering module is used to select one database instance from multiple database instances based on the weighted standard deviation σ and the weighted average μ for the next select statement query.

[0033] Preferably, the calling module includes:

[0034] The calling unit is used to obtain the time period T. l The start time T of all similar SELECT statements in the database. s and the time T taken to complete the call c The call completion time T c It is the difference between the end time of the call and the start time of the call;

[0035] The judgment unit is used to determine whether the SELECT statement call has ended. If it has ended, the time T for the SELECT statement call to complete is calculated. c If the process is not completed, check if the call is abnormal. If there is no abnormality, proceed to the first call assignment unit; if there is an abnormality, proceed to the second call assignment unit.

[0036] The first call assignment unit is used to select the current time point and the start call time point T. s The difference between the two values ​​and the maximum value of the previous call completion time is used as the call completion time T. c ;

[0037] The second call assignment unit is used to select the current time point and the start call time point T. s The difference between the two values, plus twice the maximum value of the previous call completion time, is taken as the call completion time T. c .

[0038] Preferably, the filtering module includes:

[0039] A filtering calculation unit is used to calculate the value of σ+2μ based on the weighted standard deviation σ and the weighted mean μ;

[0040] The filtering unit is used to select the database instance with the smallest value of σ+2μ from multiple database instances as the next database instance to be invoked in the select statement query.

[0041] Thirdly, the electronic device provided by the present invention adopts the following technical solution:

[0042] An electronic device comprising:

[0043] One or more processors;

[0044] Memory;

[0045] One or more applications, wherein the one or more applications are stored in the memory and configured to be executed by the one or more processors, the one or more applications being configured to: execute a method for improving database query efficiency based on the Cangjie programming language according to any one of the preceding claims.

[0046] Fourthly, the present invention provides a computer-readable storage medium, which adopts the following technical solution:

[0047] A computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method for improving database query efficiency based on the Cangjie programming language as described above.

[0048] In summary, the present invention has the following beneficial technical effects:

[0049] 1. This invention improves the efficiency of the next database query by giving higher weight to SELECT statements that are closer to the current time point, thereby calculating the weighted standard deviation and weighted average of different database instances based on different call weights, and selecting the optimal database instance from multiple database instances based on the weighted standard deviation and weighted average as the next SELECT statement query call.

[0050] 2. This invention calculates the value of σ+2μ and selects the one with the smallest σ+2μ value as the query call for the next select statement. The value of σ+2μ not only takes into account the weighted standard deviation σ and weighted average μ of the call completion time, but also takes into account the stability of the query call, which greatly improves the efficiency of the next select statement query call.

[0051] 3. When obtaining the call completion time data, this invention also considers database instances where the SELECT statement has not finished calling and database instances where the SELECT statement call encountered an exception. This makes the call completion time data more comprehensive, accurate, complete, and realistic, so as to facilitate the subsequent selection of the optimal SELECT statement query from the completed SELECT statement query, the incomplete SELECT statement query, and the SELECT statement query that encountered an exception as the next SELECT statement query. It not only takes into account the call completion time but also the stability of the SELECT statement call, thereby improving the efficiency of the next SELECT statement query call. Attached Figure Description

[0052] Figure 1 This is a flowchart of a method for improving database query efficiency based on the Cangjie programming language in an embodiment of the present invention. Detailed Implementation

[0053] The present invention will be further described in detail below with reference to the accompanying drawings.

[0054] This invention discloses a method for improving database query efficiency based on the Cangjie programming language.

[0055] Reference Figure 1 The method for improving database query efficiency based on the Cangjie programming language includes the following steps:

[0056] S1, Obtain time period T l The start time T of all similar SELECT statements in the database. s and the time T taken to complete the call c The call completion time T c It is the difference between the end time of the call and the start time of the call;

[0057] Among them, the completion time T for a SELECT statement that has not yet finished calling is... c Select the current time point and the start time point T. s The difference between the two values ​​and the maximum value of the previous call completion time is used as the call completion time T. c ;

[0058] For the completion time of a select statement that throws a step call exception, the current time point and the start time point T are selected. s The difference between the two values, plus twice the maximum value of the previous call completion time, is taken as the call completion time T. c .

[0059] S2, based on the starting point T of each select statement. sand the time T taken to complete the call c Calculate the weighted standard deviation σ and the weighted mean μ;

[0060] Specifically, step S2 includes the following:

[0061] S21. Assume the weight of the time taken to complete the nth select statement is W. n The current time point is T t And define the distance from the current time point T. t The more recent the SELECT statement, the higher its call weight, which is T. l -(T t -T s ) = T l +T s -T t Then the weight W n =max(T) l +T s -T t ,1);

[0062] S22, Assuming time period T l A database instance executes n similar SELECT statements. Let T be the time taken to complete the nth SELECT statement. cn The average time taken to complete n calls is in,

[0063] The formulas for calculating the weighted standard deviation σ and weighted average μ of the time taken to complete a SELECT statement call of the same type in this database instance are as follows:

[0064]

[0065] μ=∑W n T cn / ∑W n ;

[0066] S3. Based on the weighted standard deviation σ and the weighted average μ, select one database instance from multiple database instances as the next select statement query call.

[0067] When selecting one of the database instances as the next select statement query call, although the weighted standard deviation σ and weighted average μ of the call completion time are important indicators, the stability of the call query also needs to be considered when calling the query. Therefore, the value of σ+2μ is calculated by selecting the screening weighted standard deviation σ and weighted average μ.

[0068] Specifically, the value of σ+2μ is calculated based on the weighted standard deviation σ and the weighted average μ, and the database instance with the smallest value of σ+2μ is selected from multiple database instances as the next SELECT statement query call.

[0069] The following is an example of a method for improving database query efficiency based on the Cangjie programming language in the above embodiments;

[0070] Cangjie programming language is a modern programming language that supports efficient, secure, and all-scenario application development. It features strong security, high performance, high efficiency, and all-scenario capabilities. Therefore, Cangjie language was chosen to implement the solution of this invention embodiment.

[0071] Write code in the Cangjie language to connect to a Panwei database cluster with one master and two backup instances. The master database instance is located at 192.168.2.30:5432, and the two backup instances are located at 192.168.2.33:5432 and 192.168.2.35:5432. A total of 12 threads are started, each using one database connection to execute SQL queries. The database contains approximately 10 million records, and the queries use time-consuming SQL statements, meaning the query conditions will be non-primary key SQL statements.

[0072] The code collects time-consuming information through a plugin. The data from the calculation process is shown in Appendix 1, "Data from a Single Calculation Process," below, showing the start time T of the three database instances. s and time T c Information such as these are all reflected in the data table, and the weighted standard deviation and weighted average of each database instance calculated according to the above steps are also reflected in the table.

[0073]

[0074]

[0075]

[0076] Based on the above results, the database instance address to which the next select request should be sent is 192.168.2.30:5432.

[0077] Appendix 1: Data from one calculation process;

[0078] Selected time period: 1 minute;

[0079]

[0080]

[0081]

[0082]

[0083]

[0084]

[0085]

[0086]

[0087]

[0088]

[0089]

[0090]

[0091]

[0092]

[0093]

[0094]

[0095]

[0096]

[0097]

[0098] This application also discloses an apparatus for improving database query efficiency based on the Cangjie programming language. This apparatus is applied to the method for improving database query efficiency based on the Cangjie programming language disclosed in the above embodiments, and is used to implement the functions of the method for improving database query efficiency based on the Cangjie programming language in the above embodiments. Specifically, an apparatus for improving database query efficiency based on the Cangjie programming language includes the following modules or units:

[0099] Call the module to obtain the time period T l The start time T of all similar SELECT statements in the database. s and the time T taken to complete the call c The call completion time T c It is the difference between the end time of the call and the start time of the call;

[0100] The calling modules include:

[0101] The calling unit is used to obtain the time period T. l The start time T of all similar SELECT statements in the database. s and the time T taken to complete the call c The call completion time T c It is the difference between the end time of the call and the start time of the call;

[0102] The judgment unit is used to determine whether the SELECT statement call has ended. If it has ended, the time T for the SELECT statement call to complete is calculated. c If the process is not completed, check if the call is abnormal. If there is no abnormality, proceed to the first call assignment unit; if there is an abnormality, proceed to the second call assignment unit.

[0103] The first call assignment unit is used to select the current time point and the start call time point T. s The difference between the two values ​​and the maximum value of the previous call completion time is used as the call completion time T. c ;

[0104] The second call assignment unit is used to select the current time point and the start call time point T. s The difference between the two values, plus twice the maximum value of the previous call completion time, is taken as the call completion time T. c .

[0105] The calculation module is used to calculate based on the starting call point T of each select statement. s and the time T taken to complete the call c Calculate the weighted standard deviation σ and the weighted mean μ;

[0106] The calculation module includes:

[0107] The weight calculation unit is used to calculate the weight based on the current time point T. t , distance from the current time point T t The more recent the SELECT statement, the higher its call weight. The call weight is calculated as T. l -(T t -T s ) = T l +T s -T t ;

[0108] The weight determination unit is used to determine the weight T to be called. l +T s -T t If the value is less than 1, proceed to the first weight assignment unit; otherwise, proceed to the second weight assignment unit.

[0109] The first weight assignment unit, with weight W. n The value is assigned to 1;

[0110] The second weight assignment unit, weight W n The value is T l +T s -T t The value;

[0111] The average time calculation unit is used to calculate the time T for completing the nth call. cn Calculate the average time taken to complete n calls. in,

[0112] The weighted standard deviation calculation unit is used to calculate the standard deviation based on the weight W. n The time T taken to complete the call cn and the average time to complete the call tt c Calculate the weighted standard deviation σ, where,

[0113] The weighted average calculation unit is used to calculate the average based on weight W. n The time T taken to complete the call cn Calculate the weighted average μ, where μ = ∑W n T cn / ∑W n .

[0114] The filtering module is used to select a database instance for the next select statement query based on the weighted standard deviation σ and the weighted mean μ.

[0115] The filtering module includes:

[0116] A filtering calculation unit is used to calculate the value of σ+2μ based on the weighted standard deviation σ and the weighted mean μ;

[0117] The filtering unit is used to select the database instance with the smallest value of σ+2μ from multiple database instances as the next database instance to be invoked in the select statement query.

[0118] This invention also discloses an electronic device, comprising a processor and a memory. The processor and memory are connected, for example, via a bus. Optionally, the electronic device may further include a transceiver. It should be noted that in practical applications, the transceiver is not limited to one unit, and the structure of this electronic device does not constitute a limitation on the embodiments of this invention.

[0119] The processor can be a CPU (Central Processing Unit), a general-purpose processor, a DSP (Digital Signal Processor), an ASIC (Application-Specific Integrated Circuit), an FPGA (Field-Programmable Gate Array), or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. It can implement or execute the various exemplary logic blocks, modules, and circuits described in conjunction with the disclosure of this invention. The processor can also be a combination that implements computational functions, such as a combination of one or more microprocessors, a combination of a DSP and a microprocessor, etc.

[0120] A bus can include a pathway for transmitting information between the aforementioned components. The bus can be a PCI peripheral component interconnect standard bus or an EISA extended industry standard architecture bus, etc. Buses can be categorized as address buses, data buses, control buses, etc.

[0121] The memory may be a ROM read-only memory or other type of static storage device capable of storing static information and instructions, RAM random access memory or other type of dynamic storage device capable of storing information and instructions, or an EEPROM electrically erasable programmable read-only memory, a CD-ROM read-only optical disc or other optical disc storage, optical disc storage (including compressed optical discs, laser discs, optical discs, digital universal optical discs, Blu-ray discs, etc.), magnetic disk storage media or other magnetic storage devices, or any other medium capable of carrying or storing desired program code in the form of instructions or data structures and accessible by a computer, but not limited thereto.

[0122] The memory stores application code that executes the present invention and is controlled by a processor. The processor executes the application code stored in the memory to implement the content shown in the above embodiments of a method for improving database query efficiency based on the Cangjie programming language.

[0123] Electronic devices include, but are not limited to: mobile terminals such as mobile phones, laptops, digital radio receivers, PDAs (personal digital assistants), PADs (tablet computers), PMPs (portable multimedia players), and in-vehicle terminals (such as in-vehicle navigation terminals), as well as fixed terminals such as digital TVs and desktop computers. Servers can also be included.

[0124] This invention discloses a computer-readable storage medium storing a computer program that, when run on a computer, enables the computer to execute the corresponding content of the method for improving database query efficiency based on the Cangjie programming language disclosed in the above embodiments.

[0125] The above are all preferred embodiments of the present invention and are not intended to limit the scope of protection of the present invention. Therefore, all equivalent changes made in accordance with the structure, shape and principle of the present invention should be covered within the scope of protection of the present invention.

Claims

1. A method for improving database query efficiency based on the Cangjie programming language, characterized in that, Includes the following steps: S1, Obtain Time Period The start time of all similar SELECT statements in the database. and call completion time Time taken to complete the call It is the difference between the end time of the call and the start time of the call; S2. Based on the start time of each select statement. and call completion time Calculate the weighted standard deviation and weighted average ; S21. Assume the weight of the time taken to complete the nth select statement is... The current time point is and define the distance from the current time point The more recent the SELECT statement, the higher its call weight. = Then the weight ; S22, Assuming a time period Given a database instance that executes the same SELECT statement n times, define the time taken to complete the nth call as... The average time taken to complete n calls is ,in, ; The weighted standard deviation of the time taken to complete similar SELECT statement calls in this database instance. and weighted average The calculation formulas are as follows: ； ； S3, Based on weighted standard deviation and weighted average Select one database instance from multiple database instances as the next SELECT statement query call; S3 includes: Based on weighted standard deviation and weighted average Calculate The value, and select from multiple database instances. The database instance with the smallest value is used as the next SELECT statement query call.

2. The method for improving database query efficiency based on the Cangjie programming language according to claim 1, characterized in that, For the completion time of the SELECT statement that has not yet finished in S1. Select the current time point and the start time point. The difference between the two values ​​and the maximum value of the previous call completion time are used as the call completion time. ; For the completion time of the select statement that caused the exception in step S1, the current time point and the start time point are selected. The difference and twice the maximum value of the previous call completion time are used as the call completion time. .

3. A device for improving database query efficiency based on the Cangjie programming language, characterized in that, include: Call the module to obtain the time period. The start time of all similar SELECT statements in the database. and call completion time Time taken to complete the call It is the difference between the end time of the call and the start time of the call; The calculation module is used to calculate based on the start time of each select statement. and call completion time Calculate the weighted standard deviation and weighted average ; The weight calculation unit is used to calculate the weight based on the current time point. , distance from the current time point The more recent the SELECT statement, the higher its call weight. The call weight is calculated as follows: = ; The weight determination unit is used to determine the weight to be called. If the value is less than 1, then proceed to the first weight assignment unit; otherwise, proceed to the second weight assignment unit. First weight assignment unit, weight The value is assigned to 1; The second weight assignment unit, weight The value is The value; The average time calculation unit is used to calculate the time taken to complete the nth call. Calculate the average time taken to complete n calls. ,in, ; Weighted standard deviation calculation unit, used for weight-based calculation Time taken to complete the call and average call completion time Calculate the weighted standard deviation ,in, ; Weighted average calculation unit, used for weight-based calculations Time taken to complete the call Calculate the weighted average ,in, ; The filtering module is used for filtering based on weighted standard deviation. and weighted average It selects one database instance from multiple database instances to be used in the next select statement query. The filtering module includes: Filtering calculation unit for use based on weighted standard deviation and weighted average Calculate The value; Filtering unit, used to select from multiple database instances. The database instance with the smallest value is used as the next SELECT statement query call.

4. The device for improving database query efficiency based on the Cangjie programming language according to claim 3, characterized in that, The calling module includes: Call unit, used to obtain time period The start time of all similar SELECT statements in the database. and call completion time Time taken to complete the call It is the difference between the end time of the call and the start time of the call; The judgment unit is used to determine whether the SELECT statement call has ended. If it has ended, it calculates the completion time of the SELECT statement call. If the process is not completed, check if the call is abnormal. If there is no abnormality, proceed to the first call assignment unit; if there is an abnormality, proceed to the second call assignment unit. The first call assignment unit is used to select the current time point and the start call time point. The difference between the two values ​​and the maximum value of the previous call completion time are used as the call completion time. ; The second call assignment unit is used to select the current time point and the start call time point. The difference and twice the maximum value of the previous call completion time are used as the call completion time. .

5. An electronic device, characterized in that, It includes: One or more processors; Memory; One or more applications, wherein the one or more applications are stored in the memory and configured to be executed by the one or more processors, the one or more applications being configured to: execute a method for improving database query efficiency based on the Cangjie programming language according to any one of claims 1 to 2.

6. A computer-readable storage medium having a computer program stored thereon, characterized in that, When executed by a processor, the program implements the method for improving database query efficiency based on the Cangjie programming language as described in any one of claims 1 to 2.

Images