Method and device for improving database query efficiency based on Cangjie programming language

Abstract

The invention relates to the field of improving database query efficiency based on Cangjie programming language, in particular to a method and device for improving database query efficiency based on Cangjie programming language, and the method comprises the steps: obtaining calling starting time points and calling completion time consumption of all similar select statements of a database in a time period; calculating a weighted standard deviation and a weighted average based on the calling starting point and the calling completion time consumption of each select statement; and S3, based on the weighted standard deviation and the weighted average, selecting a database instance as next select statement query call. According to the method, the calling weight of the select statement closer to the current time point is higher, so that the weighted standard deviation and the weighted average of different database instances are calculated according to different calling weights; and on the basis of the weighted standard deviation and the weighted average, selecting an optimal database instance from the plurality of database instances as next select statement query calling so as to improve the efficiency of next database query.

Description

Technical Field

[0001] The present invention relates to the field of improving database query efficiency based on Cangjie programming language, and in particular to a method and device for improving database query efficiency based on Cangjie programming language. Background Art

[0002] With the development of technologies such as the Internet and the Internet of Things, the amount of data generated every day is growing exponentially. These huge data sets need to be effectively stored and managed, and databases are systems designed for this purpose. With the help of database technology, it is possible to quickly query and extract the required information, providing convenience and support for making various effective decisions. Therefore, the accuracy and timeliness of data are crucial to formulating effective strategies. However, computing resources are always limited. When there are many concurrent queries, the database will inevitably be slow to respond, making it difficult to quickly query and return the required data. Therefore, database cluster technology is needed to support it. When querying, requests are distributed to multiple instances of the database cluster, so that the query data can be returned in a timely manner even when there are many concurrent queries.

[0003] There are many technologies for connecting to a database cluster, such as the round-robin method and the least number of connections method. The round-robin method is to access instances in the database cluster in turn, and the least number of connections method is to access the instance with the least number of connections in the database cluster.

[0004] With the development of the times, Chinese has become an efficient and safe language, and Cangjie programming language has emerged. As a modern programming language for full-scenario application development, Cangjie programming language creates a friendly development experience and excellent program performance for developers through the integration of modern language features, comprehensive compilation optimization and runtime implementation, and out-of-the-box IDE tool chain support. The database driver of Cangjie programming language provides a plug-in mechanism, especially the service address ServerAddr plug-in and the statement Stmt plug-in, which provide a basis for writing efficient access to database cluster instances.

[0005] Although each instance in the database cluster can provide query services, not every database instance can respond quickly and return data. This is affected by many factors, such as the hardware configuration of the machine where the database instance is located may not be the same, the CPU and disk IO resources occupied by other applications on the machine where the database instance is located may not be the same, the network delay between the client and each database instance may not be the same, and various other factors, resulting in different concurrency numbers that each database instance can bear. Therefore, simple processing is difficult to achieve the purpose of maximizing the use of database cluster instance resources, and there are various defects. For example, the defect of polling is that once a slow-responding instance is polled, the query will be slower; the defect of the instance with the least number of connections is that the instance with the least number of connections is not necessarily the best instance. It is possible that the network delay of the machine where this instance is located is high, and other factors lead to slow queries. Therefore, based on the current query method, its effect is not very high, and there is still a lot of room for improvement. Summary of the invention

[0006] In order to improve the efficiency of the next database query, the present invention provides a method and device for improving the efficiency of database query based on Cangjie programming language. The method makes the call weight of the select statement closer to the current time point higher, thereby calculating the weighted standard deviation and weighted average of different database instances according to different call weights, and selecting an optimal database instance from multiple database instances based on the weighted standard deviation and weighted average as the next select statement query call, thereby improving the efficiency of the next database query.

[0007] In a first aspect, 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 Cangjie programming language comprises the following steps:

[0009] S1. Get time period T l The time point T at which select statements of the same type are called for all databases in the same database s And the call takes time T c The call takes T c The difference between the end call time point and the start call time point;

[0010] S2, based on the start call point T of each select statement s And the call takes time T c , calculate the weighted standard deviation σ and weighted mean μ;

[0011] S21. Assume that the weight of the time taken to complete the nth select statement is Wn , the current time point is T t , and define the distance from the current time point T t The closer the select statement is, the greater the call weight is. The call weight 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 There are n select statements of the same type in a database instance, and the time taken to complete the nth call is defined as T cn , the average time taken to complete n calls is in,

[0013] The calculation formulas for the weighted standard deviation σ and weighted average μ of the time consumed by the same type of select statement calls in this database instance are:

[0014]

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

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

[0017] Preferably, the time taken to complete the call of the select statement that has not been called in S1 is T c , select the current time point and the start calling time point T s The maximum value of the difference between the last call completion time and the call completion time is taken as the call completion time T c ;

[0018] The time taken to complete the call of the select statement with abnormal call in step S1 is selected from the current time point and the start time point T s The difference between the time taken to complete the previous call and twice the maximum value of the time taken to complete the previous call is taken as the time taken to complete the call 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] In a second aspect, 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 Cangjie programming language, comprising:

[0023] Call module to obtain time period T l The time point T at which select statements of the same type are called for all databases in the same database s And the call takes time T c The call takes T c The difference between the end call time point and the start call time point;

[0024] Calculation module, used to start calling point T based on each select statement s And the call takes time T c , calculate the weighted standard deviation σ and weighted mean μ;

[0025] The weight calculation unit is used to calculate the weight of the t , from the current time point T t The closer the select statement is, the greater the call weight is. The call weight is calculated as T l -(T t -T s )=T l +T s -T t ;

[0026] Weight judgment unit, used to judge the call weight T l +T s -T t Is the value of less than 1? If so, enter the first weight assignment unit; if not, enter the second weight assignment unit;

[0027] The first weight assignment unit, weight W n Assign a value of 1;

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

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

[0030] Weighted standard deviation calculation unit, used to calculate the standard deviation based on the weight W n 、Time taken to complete the call T cn and the average time it takes to complete the call Calculate the weighted standard deviation σ, where

[0031] Weighted average calculation unit, used to calculate the average value based on the weight W n 、Time taken to complete the call T cn Calculate the weighted average μ, where μ = ∑W n T cn / ∑W n ;

[0032] The screening module is used to screen out a database instance from multiple database instances as the next select statement query call based on the weighted standard deviation σ and the weighted average μ.

[0033] Preferably, the calling module includes:

[0034] Calling unit, used to obtain time period T l The time point T at which select statements of the same type are called for all databases in the same database s And the call takes time T c The call takes T c The difference between the end call time point and the start call time point;

[0035] The judgment unit is used to judge whether the select statement has been called. If it has been called, the time taken to complete the call of the select statement at the calling location is T c If it is not ended, determine whether the call is abnormal. If there is no abnormality, enter the first call assignment unit. If it is abnormal, enter 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 maximum value of the difference between the last call completion time and the call completion time is taken 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 time taken to complete the previous call and twice the maximum value of the time taken to complete the previous call is taken as the time taken to complete the call T c .

[0038] Preferably, the screening module comprises:

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

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

[0041] In a third aspect, the present invention provides an electronic device, which 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, and the one or more programs are configured to: execute a method for improving database query efficiency based on the Cangjie programming language according to any of the above items.

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

[0047] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements any of the above-mentioned methods for improving database query efficiency based on the Cangjie programming language.

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

[0049] 1. The present invention makes the call weight of the select statement closer to the current time point higher, thereby calculating the weighted standard deviation and weighted average of different database instances according to different call weights, and selecting an optimal database instance from multiple database instances based on the weighted standard deviation and weighted average as the next select statement query call, so as to improve the efficiency of the next database query.

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

[0051] 3. When obtaining the call completion time-consuming data, the present invention also takes into account the database instances where the select statement has not been called and the database instances where the select statement call has an exception, so that the call completion time-consuming data is more sufficient, accurate, complete and true, so as to facilitate the subsequent screening of the optimal select statement query from the select statement queries that have been called, the select statement queries that have not been called, and the select statements that have been called abnormally as the next select statement query. It not only takes into account the call completion time-consuming time, but also takes into account the stability of the select statement call, thereby improving the efficiency of the query call of the next select statement. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] Figure 1 It is a flow chart of a method for improving database query efficiency based on Cangjie programming language in an embodiment of the present invention. DETAILED DESCRIPTION

[0053] The present invention is further described in detail below in conjunction with the accompanying drawings.

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

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

[0056] S1. Get time period T l The time point T at which select statements of the same type are called for all databases in the same database s And the call takes time T c The call takes T c The difference between the end call time point and the start call time point;

[0057] Among them, the time taken to complete the call of the select statement that has not been called is T c , select the current time point and the start calling time point T s The maximum value of the difference between the last call completion time and the call completion time is taken as the call completion time T c ;

[0058] The time taken to complete the call of the select statement with abnormal step call is selected from the current time point and the start time point T s The difference between the time taken to complete the previous call and twice the maximum value of the time taken to complete the previous call is taken as the time taken to complete the call T c .

[0059] S2, based on the start call point T of each select statement sAnd the call takes time T c , calculate the weighted standard deviation σ and weighted mean μ;

[0060] Specifically, step S2 includes the following contents:

[0061] S21. Assume that 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 closer the select statement is, the greater the call weight is. The call weight 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 There are n select statements of the same type in a database instance, and the time taken to complete the nth call is defined as T cn , the average time taken to complete n calls is in,

[0063] The calculation formulas for the weighted standard deviation σ and weighted average μ of the time consumed by the same type of select statement calls in this database instance are:

[0064]

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

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

[0067] In the process of 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 a 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 embodiment;

[0070] Cangjie programming language is a modern programming language that supports efficient, secure, and full-scenario application development. It has the characteristics of strong security, high performance, high efficiency, and full scenarios. Therefore, Cangjie language is selected to complete the solution of the embodiment of the present invention.

[0071] Write Cangjie language code and use Cangjie language chinamobiledb-driver to connect to the one-master and two-slave cluster of Panwei database. The address of the master database instance is 192.168.2.30:5432, and the addresses of the two backup instances are 192.168.2.33:5432 and 192.168.2.35:5432. A total of 12 threads are started and each thread uses 1 database connection to execute query SQL statements. There are about 10 million data in the database, and the query SQL statement uses a time-consuming SQL statement, that is, the query condition will be a non-primary key SQL statement.

[0072] The code will collect time-consuming information through the plug-in. The data of the calculation process is shown in Appendix 1 "Data of a calculation process" below. The start call time of the three database instances is T s and time T c The above information is reflected in the data table, and the weighted standard deviation, weighted average, etc. of each database instance calculated according to the above steps are also reflected in the table.

[0073]

[0074]

[0075]

[0076] Finally, 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 of 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] The embodiment of the present application also discloses a device for improving database query efficiency based on Cangjie programming language, which is applied to the method for improving database query efficiency based on Cangjie programming language disclosed in the above embodiment, and is used to implement the functions of the method for improving database query efficiency based on Cangjie programming language in the above embodiment. Specifically, a device for improving database query efficiency based on Cangjie programming language includes the following modules or units:

[0099] Call module to obtain time period T l The time point T at which select statements of the same type are called for all databases in the same database s And the call takes time T c The call takes T c The difference between the end call time point and the start call time point;

[0100] The calling modules include:

[0101] Calling unit, used to obtain time period T l The time point T at which select statements of the same type are called for all databases in the same database s And the call takes time T c The call takes T c The difference between the end call time point and the start call time point;

[0102] The judgment unit is used to judge whether the select statement has been called. If it has been called, the time taken to complete the call of the select statement at the calling location is T c If it is not ended, determine whether the call is abnormal. If there is no abnormality, enter the first call assignment unit. If it is abnormal, enter 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 maximum value of the difference between the last call completion time and the call completion time is taken 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 time taken to complete the previous call and twice the maximum value of the time taken to complete the previous call is taken as the time taken to complete the call T c .

[0105] Calculation module, used to start calling point T based on each select statement s And the call takes time T c , calculate the weighted standard deviation σ and weighted mean μ;

[0106] The computing module includes:

[0107] The weight calculation unit is used to calculate the weight of the t , from the current time point T t The closer the select statement is, the greater the call weight is. The call weight is calculated as T l -(T t -T s )=T l +T s -T t ;

[0108] Weight judgment unit, used to judge the call weight T l +T s -T t Is the value of less than 1? If so, enter the first weight assignment unit; if not, enter the second weight assignment unit;

[0109] The first weight assignment unit, weight W n Assign a value of 1;

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

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

[0112] Weighted standard deviation calculation unit, used to calculate the standard deviation based on the weight W n 、Time taken to complete the call T cn And the average time taken to complete the call tt c Calculate the weighted standard deviation σ, where

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

[0114] The screening module is used to screen out a database instance as the next select statement query call based on the weighted standard deviation σ and the weighted average μ;

[0115] The screening modules include:

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

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

[0118] An embodiment of the present invention further discloses an electronic device, which includes: a processor and a memory. The processor and the memory are connected, such as through a bus. Optionally, the electronic device may also include a transceiver. It should be noted that in actual applications, the transceiver is not limited to one, and the structure of the electronic device does not constitute a limitation on the embodiment of the present invention.

[0119] The processor may be a CPU central processing unit, a general processor, a DSP data 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 may implement or execute various exemplary logic blocks, modules and circuits described in conjunction with the disclosure of the present invention. The processor may also be a combination that implements computing functions, such as a combination of one or more microprocessors, a combination of a DSP and a microprocessor, etc.

[0120] The bus may include a path to transmit information between the above components. The bus may be a PCI peripheral component interconnect standard bus or an EISA extended industry standard architecture bus, etc. The bus may be divided into an address bus, a data bus, a control bus, etc.

[0121] The memory can be a ROM read-only memory or other types of static storage devices that can store static information and instructions, a RAM random access memory or other types of dynamic storage devices that can store information and instructions, or an EEPROM electrically erasable programmable read-only memory, a CD-ROM read-only disc or other optical disc storage, an optical disc storage (including compressed optical discs, laser discs, optical discs, digital versatile discs, Blu-ray discs, etc.), a magnetic disk storage medium or other magnetic storage device, or any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed by a computer, but is not limited to this.

[0122] The memory is used to store the application code for executing the solution of the present invention, and the execution is controlled by the processor. The processor is used to execute the application code stored in the memory to implement the content shown in the method for improving database query efficiency based on Cangjie programming language disclosed in the above embodiment.

[0123] The electronic devices include, but are not limited to, mobile terminals such as mobile phones, laptop computers, digital broadcast receivers, PDAs (personal digital assistants), PADs (tablet computers), PMPs (portable multimedia players), vehicle-mounted terminals (such as vehicle-mounted navigation terminals), and fixed terminals such as digital TVs, desktop computers, etc. It can also be a server, etc.

[0124] An embodiment of the present invention discloses a computer-readable storage medium, on which a computer program is stored. When the computer-readable storage medium is run on a computer, the computer can execute the corresponding content of a method for improving database query efficiency based on the Cangjie programming language disclosed in the above embodiment.

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

Claims

1. A method for improving database query efficiency based on Cangjie programming language, characterized in that: The following steps are involved: S1. Get time period T l The time point T at which select statements of the same type are called for all databases in the same database s And the call takes time T c The call takes T c The difference between the end call time point and the start call time point; S2, based on the start call point T of each select statement s And the call takes time T c , calculate the weighted standard deviation σ and weighted mean μ; S21. Assume that 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 closer the select statement is, the greater the call weight is. The call weight 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); S22, assuming time period T l There are n select statements of the same type in a database instance, and the time taken to complete the nth call is defined as T cn , the average time taken to complete n calls is in, The calculation formulas for the weighted standard deviation σ and weighted average μ of the time consumed by the same type of select statement calls in this database instance are: S3. Based on the weighted standard deviation σ and the weighted average μ, a database instance is selected from multiple database instances as the next select statement query call.

2. The method for improving database query efficiency based on Cangjie programming language according to claim 1, characterized in that: The time it takes to complete the call of the select statement that has not been called in S1 is T c , select the current time point and the start calling time point T s The maximum value of the difference between the last call completion time and the call completion time is taken as the call completion time T c ; The time taken to complete the call of the select statement with abnormal call in step S1 is selected from the current time point and the start time point T s The difference between the time taken to complete the previous call and twice the maximum value of the time taken to complete the previous call is taken as the time taken to complete the call T c .

3. The method for improving database query efficiency based on Cangjie programming language according to claim 1, characterized in that: The S3 includes: 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.

4. A device for improving database query efficiency based on Cangjie programming language, characterized in that: include: Call module to obtain time period T l The time point T at which select statements of the same type are called for all databases in the same database s And the call takes time T c The call takes T c The difference between the end call time point and the start call time point; Calculation module, used to start calling point T based on each select statement s And the call takes time T c , calculate the weighted standard deviation σ and weighted mean μ; The weight calculation unit is used to calculate the weight of the t , from the current time point T t The closer the select statement is, the greater the call weight is. The call weight is calculated as T l -(T t -T s )=T l +T s -T t ; Weight judgment unit, used to judge the call weight T l +T s -T t Is the value of less than 1? If so, enter the first weight assignment unit; if not, enter the second weight assignment unit; The first weight assignment unit, weight W n Assign a value of 1; The second weight assignment unit, weight W n The value of T l +T s -T t The value of The average time calculation unit is used to calculate the time taken to complete the nth call T cn Calculate the average time taken to complete n calls in, Weighted standard deviation calculation unit, used to calculate the standard deviation based on the weight W n 、Time taken to complete the call T cn and the average time it takes to complete the call Calculate the weighted standard deviation σ, where Weighted average calculation unit, used to calculate the average value based on the weight W n 、Time taken to complete the call T cn Calculate the weighted average μ, where μ = ∑W n T cn / ∑W n ; The screening module is used to screen out a database instance from multiple database instances as the next select statement query call based on the weighted standard deviation σ and the weighted average μ.

5. The device for improving database query efficiency based on Cangjie programming language according to claim 4, characterized in that: The calling module includes: Calling unit, used to obtain time period T l The time point T at which select statements of the same type are called for all databases in the same database s And the call takes time T c The call takes T c The difference between the end call time point and the start call time point; The judgment unit is used to judge whether the select statement has been called. If it has been called, the time taken to complete the call of the select statement at the calling location is T c If it is not ended, determine whether the call is abnormal. If there is no abnormality, enter the first call assignment unit. If it is abnormal, enter the second call assignment unit; The first call assignment unit is used to select the current time point and the start call time point T s The maximum value of the difference between the last call completion time and the call completion time is taken as the call completion time T c ; 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 time taken to complete the previous call and twice the maximum value of the time taken to complete the previous call is taken as the time taken to complete the call T c .

6. The device for improving database query efficiency based on Cangjie programming language according to claim 4, characterized in that: The screening module comprises: A screening calculation unit is used to calculate a value of σ+2μ based on a weighted standard deviation σ and a weighted mean μ; The screening unit is used to select the database instance with the smallest value of σ+2μ from multiple database instances as the next select statement query call.

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

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

Images