Method, device and electronic equipment for processing sql statements in distributed database

Abstract

The application provides a processing method and device of an SQL statement in a distributed database and electronic equipment. The method is applied to an application server. Database shards in the server correspond to attribute labels. The method comprises the following steps: obtaining an SQL statement to be executed. The SQL statement carries a shard key and an operation type. The first target shard corresponding to the SQL statement is determined according to the shard key and the current number of shards. The SQL statement is executed according to the attribute label of the first target shard and the operation type of the SQL statement. When the SQL statement to be executed is received, different operations can be performed on the SQL statement according to different attribute labels of shards and different operation types of SQL statements. Whether the database is expanded by adding shards or the database is shrunk due to shard failure, data migration and re-sharding are not required, the consistency of data processing can be ensured, and the demand of financial services can be met.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of financial services, in particular to a processing method and device of SQL statements in a distributed database and electronic equipment. BACKGROUND

[0002] In the digital transformation of the financial industry, business data grows rapidly, and under the system pressure brought by high concurrency and large user volume, higher requirements are put forward for data storage and management. There is a stronger demand for how to realize the self-controlling of the database, guarantee the data strong consistency, high availability and horizontal expansion to protect the safety of customer funds and prevent financial risks. As a result, distributed databases, as a new database architecture, have gradually matured after nearly a decade of rapid development and have been applied to the financial industry.

[0003] In the use of distributed databases, as time goes by, the data volume and access volume on each shard become larger and larger, and the server capacity and performance reach a bottleneck. At this time, it is necessary to expand the database to ensure business stability. At the same time, a single shard or multiple shards may cause faults due to network or storage reasons during operation and use. At this time, it is necessary to isolate and degrade the fault shard, i.e. to reduce the capacity to ensure the high availability of financial services. In most current distributed database solutions, data migration and re-sharding are required. The entire process is time-consuming and may have risks such as data inconsistency, which cannot meet the requirements of financial services. SUMMARY

[0004] The purpose of the present application is to provide a processing method and device of SQL statements in a distributed database and electronic equipment. When a SQL statement to be executed is received, different operations can be performed on the SQL according to different attribute tags of the shards and different operation types of the SQL. Therefore, whether in the case of expanding the database by adding shards or in the case of reducing the capacity of the database due to shard faults, data migration and re-sharding are not required, the consistency of data processing can be guaranteed, and the requirements of financial services can be met.

[0005] In a first aspect, an embodiment of the present application provides a processing method of SQL statements in a distributed database. The method is applied to an application server, and a database shard in the server corresponds to an attribute tag. The attribute tag includes: available or unavailable. The method includes: obtaining a SQL statement to be executed; the SQL statement carries a shard key and an operation type; the operation type includes: an addition operation, an update operation or a query operation; determining a first target shard corresponding to the SQL statement according to the shard key and the current number of shards; and executing the SQL statement according to the attribute tag of the first target shard and the operation type of the SQL statement.

[0006] In the preferable embodiment of the present application, the operation type of the SQL statement is an adding operation; according to the attribute label of the first target shard and the operation type of the SQL statement, the step of executing the SQL statement comprises: if the attribute label of the first target shard is available, executing the SQL statement based on the first target shard; if the attribute label of the first target shard is unavailable, searching for a second target shard with an available attribute label in the order of shard identifiers, and executing the SQL statement based on the second target shard.

[0007] In the preferable embodiment of the present application, the operation type of the SQL statement is a query operation or an update operation; according to the attribute label of the first target shard and the operation type of the SQL statement, the step of executing the SQL statement comprises: if the attribute label of the first target shard is available, executing the SQL statement based on the first target shard to obtain a first execution result; if the first execution result is empty, determining an execution strategy corresponding to the SQL statement according to the current state of the database; the current state comprises: an expansion state or a shrinkage state caused by a fault shard; if the attribute label of the first target shard is unavailable, taking a shard with an available attribute label corresponding to the first target shard as a third target shard, and executing the SQL statement based on the third target shard.

[0008] In the preferable embodiment of the present application, the step of determining the execution strategy corresponding to the SQL statement according to the current state of the database comprises: if the current state of the database is the expansion state, obtaining the historical shard quantity before the expansion of the database, determining a fourth target shard according to the shard key of the SQL statement and the historical shard quantity, and executing the SQL statement based on the fourth target shard; if the current state of the database is the shrinkage state, taking a shard with an available attribute label corresponding to the first target shard as a fifth target shard, and executing the SQL statement based on the fifth target shard.

[0009] In the preferable embodiment of the present application, after the step of executing the SQL statement based on the third target shard, the method further comprises: obtaining a second execution result obtained by executing the SQL statement based on the third target shard; if the second execution result is empty, executing a corresponding strategy according to the operation type of the SQL statement.

[0010] In the preferable embodiment of the present application, the step of executing the corresponding strategy according to the operation type of the SQL statement comprises: if the operation type of the SQL statement is the query operation, returning a first specified error code; if the operation type of the SQL statement is the update operation, returning a second specified error code, and adding the SQL statement to a fault table.

[0011] In the preferred embodiment of the present application, the method further comprises: when the attribute label of the first target shard is detected to be recovered from unavailable to available, re-executing the SQL statement corresponding to the first specified error code, and re-executing the corresponding SQL statement in the fault table based on the second specified error code.

[0012] In a second aspect, the embodiments of the present application further provide a processing device for SQL statements in a distributed database. The device is applied to an application server, and a database shard in the server corresponds to an attribute label. The attribute label includes available or unavailable. The device includes: a statement acquisition module, configured to acquire a SQL statement to be executed. The SQL statement carries a shard key and an operation type. The operation type includes an adding operation, an updating operation or a querying operation. A shard determination module, configured to determine a first target shard corresponding to the SQL statement according to the shard key and a current shard quantity. A statement execution module, configured to execute the SQL statement according to the attribute label of the first target shard and the operation type of the SQL statement.

[0013] In a third aspect, the embodiments of the present application further provide an electronic device including a processor and a memory. The memory stores computer executable instructions capable of being executed by the processor. The processor executes the computer executable instructions to implement the method in the first aspect.

[0014] In a fourth aspect, the embodiments of the present application further provide a computer readable storage medium. The computer readable storage medium stores computer executable instructions. When the computer executable instructions are invoked and executed by a processor, the computer executable instructions cause the processor to implement the method in the first aspect.

[0015] The processing method, device and electronic device for SQL statements in a distributed database provided by the embodiments of the present application are applied to an application server. A database shard in the server corresponds to an attribute label. The attribute label includes available or unavailable. After acquiring a SQL statement to be executed, the embodiments of the present application can first determine a first target shard corresponding to the SQL statement according to a shard key carried by the SQL statement and a current shard quantity. Then, the SQL statement is executed according to the attribute label of the first target shard and an operation type carried in the SQL statement. That is, when the SQL statement to be executed is received, the embodiments of the present application can perform different operations on the SQL statement according to different attribute labels of shards and different operation types of SQL statements. Therefore, whether in the case of increasing shards to expand the database or in the case of database shrinkage caused by shard failure, data migration and re-sharding are not required, the consistency of data processing is ensured, and the demand of financial services is met. BRIEF DESCRIPTION OF DRAWINGS

[0016] In order to more clearly illustrate the technical solutions in the specific embodiments or the prior art of the present application, the drawings required to be used in the description of the specific embodiments or the prior art will be briefly introduced. Obviously, the drawings in the following description are some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor.

[0017] Figure 1 A flowchart of a processing method of an SQL statement in a distributed database provided by an embodiment of the present application is shown in FIG. 3.

[0018] Figure 2 A flowchart of another processing method of an SQL statement in a distributed database provided by an embodiment of the present application is shown in FIG. 4.

[0019] Figure 3 A schematic diagram of a processing method in a capacity expansion state provided by an embodiment of the present application is shown in FIG. 5.

[0020] Figure 4 A structural block diagram of a processing device of an SQL statement in a distributed database provided by an embodiment of the present application is shown in FIG. 6.

[0021] Figure 5 A structural schematic diagram of an electronic device provided by an embodiment of the present application is shown in FIG. 7. DETAILED DESCRIPTION

[0022] The technical solutions of the present application will be described in detail below with reference to the embodiments. Obviously, the described embodiments are only some of the embodiments of the present application, but not all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative labor fall within the scope of protection of the present application.

[0023] At present, in most distributed database solutions, data migration and re-sharding are required, the whole process is time-consuming, and there may be risks such as data inconsistency, which cannot meet the requirements of financial services.

[0024] Therefore, based on this, the embodiments of the present application provide a processing method, device and electronic device of an SQL (Structured Query Language, structured query language) statement in a distributed database. When a SQL statement to be executed is received, different operations can be performed on the SQL according to different attribute tags of sharding and different operation types of the SQL. Therefore, whether in the case of adding sharding for database capacity expansion or in the case of database capacity reduction caused by sharding failure, data migration and re-sharding are not required, the consistency of data processing can be ensured, and the demand of financial services can be met.

[0025] For the convenience of understanding the present embodiment, first of all, a kind of distributed database SQL statement processing method disclosed in the present application is introduced in detail.

[0026] Figure 1 A kind of distributed database SQL statement processing method provided in the present application, the method is applied to application server, the database shard in the server corresponds with attribute label;Attribute label includes: available or unavailable;For the shard that can normally work, it is marked as available, or not marked to indicate that it is available;When detecting that a certain shard fails, it is marked as unavailable;And when detecting that the shard is being recovered from failure, it is marked as available when recovery is completed;Therefore, the attribute label corresponding to each shard is either available or unavailable;The above-mentioned distributed database SQL statement processing method includes the following steps:

[0027] Step S102, obtain the SQL statement to be executed;SQL statement carries shard key and operation type;Operation type includes: new operation, update operation or query operation.

[0028] The above-mentioned SQL statement to be executed can be used to represent a business transaction request, and its corresponding new operation, update operation and query operation can correspond to a deposit request, a payment request and a balance query request respectively;The shard key carried in the SQL statement is a specified non-empty table field in the SQL statement, such as a hash value carried in a business transaction request.

[0029] Step S104, determine the first target shard corresponding to the SQL statement according to the shard key and the current number of shards.

[0030] The current number of shards is the number of shards in the current database, if the database has just been expanded, the current number of shards is the number of shards after expansion;If there is a fault shard in the database, resulting in shrinkage, in this case, the number of shards does not change.

[0031] By taking modulo operation on the shard key and the current number of shards, the first target shard corresponding to the SQL statement can be determined.

[0032] Step S106, execute the SQL statement according to the attribute label of the first target shard and the operation type of the SQL statement.

[0033] The attribute label of the first target shard has available and unavailable two cases;The operation type of the SQL statement has three cases of new operation, update operation or query operation, and is executed according to different processing modes according to different cases.

[0034] The embodiment of the application provides a processing method of an SQL statement in a distributed database, which is applied to an application server, and a database shard in the server corresponds to an attribute label; the attribute label comprises: available or unavailable; after obtaining an SQL statement to be executed, the embodiment of the application can first determine a first target shard corresponding to the SQL statement according to a shard key and a current shard quantity carried by the SQL statement; and then execute the SQL statement according to an attribute label of the first target shard and an operation type carried in the SQL statement. That is, when the SQL statement to be executed is received, different operations are performed on the SQL statement according to different attribute labels of shards and different operation types of the SQL statement, so that data migration and re-sharding are not required in the case of database expansion by adding shards or in the case of database capacity reduction caused by shard failure, consistency of data processing is ensured, and the demand of financial services is met.

[0035] The embodiment of the application also provides a processing method of an SQL statement in a distributed database, which is implemented on the basis of the above embodiment, and the embodiment mainly describes a processing process of executing an SQL statement according to an attribute label of a first target shard and an operation type of the SQL statement.

[0036] Referring to Figure 2 The embodiment of the application provides a processing method of an SQL statement in a distributed database, which is applied to an application server, and a database shard in the server corresponds to an attribute label; the attribute label comprises: available or unavailable; after obtaining an SQL statement to be executed, the embodiment of the application can first determine a first target shard corresponding to the SQL statement according to a shard key and a current shard quantity carried by the SQL statement; and then execute the SQL statement according to an attribute label of the first target shard and an operation type carried in the SQL statement. That is, when the SQL statement to be executed is received, different operations are performed on the SQL statement according to different attribute labels of shards and different operation types of the SQL statement, so that data migration and re-sharding are not required in the case of database expansion by adding shards or in the case of database capacity reduction caused by shard failure, consistency of data processing is ensured, and the demand of financial services is met.

[0037] Step S202, obtaining an SQL statement to be executed; the SQL statement carries a shard key and an operation type; the operation type comprises: an adding operation, an updating operation or a querying operation.

[0038] Step S204, determining a first target shard corresponding to the SQL statement according to the shard key and a current shard quantity.

[0039] For example, there are eight shards in the distributed database, namely, M1 shard, M2 shard, M3 shard, M4 shard, M5 shard, M6 shard, M7 shard and M8 shard; the first target shard is the M3 shard determined by the shard key in the SQL statement and the modulo operation of the current shard quantity 8; the following is described by taking this example.

[0040] The first case: in the case that the operation type of the SQL statement is the adding operation:

[0041] Only whether the first target shard calculated by the shard key in the SQL statement and the current shard quantity can work normally needs to be determined, if the first target shard can work normally, the SQL statement is directly executed, that is, the adding operation is performed; if the first target shard cannot work normally, a shard that can work normally is selected to execute the SQL statement.

[0042] (1) If the attribute label of the first target shard is available, step S206 is executed to execute the SQL statement based on the first target shard; for example, the SQL statement is executed in the M3 shard, that is, the addition operation is performed, such as adding data A in the M3 shard.

[0043] (2) If the attribute label of the first target shard is not available, step S208 is executed to find a second target shard with an available attribute label in the order of shard identification, and the SQL statement is executed based on the second target shard. For example, the next shard of the M3 shard is the M4 shard, and if the attribute label of the M4 shard is available, the SQL statement is executed in the M4 shard, that is, the addition operation is performed, such as adding data A in the M4 shard.

[0044] The second case: in the case where the operation type of the SQL statement is a query operation or an update operation:

[0045] (1) If the attribute label of the first target shard is available, step S210 is executed to execute the SQL statement based on the first target shard to obtain a first execution result; for example, when the M3 shard is available, the newly added data A is stored in the M3 shard, and when the newly added data A is queried, the M3 shard is still available, and the newly added data A is searched in the M3 shard. Normally, it can be searched, but in the following two cases, it cannot be searched:

[0046] If the first execution result is empty, the execution strategy corresponding to the SQL statement is determined according to the current state of the database; the current state includes the expansion state or the shrinkage state caused by the fault shard; specifically including the following two cases:

[0047] 1) If the current state of the database is the expansion state, step S212 is executed to obtain the historical number of shards before the expansion of the database, and the fourth target shard is determined according to the shard key of the SQL statement and the historical number of shards, and the SQL statement is executed based on the fourth target shard. For example, when the M3 shard is available, the result of searching or updating data A is empty, and the current state of the database is the expansion state, at this time, the number of shards before expansion can be used to replace the current number of shards to recalculate the target shard, such as M2 shard, and the query or update should be performed in the M2 shard to obtain the correct result.

[0048] It should be noted that when the database administrator and the developer jointly assess that the database needs to be expanded, the database expansion switch is opened by modifying the data source configuration, adding the expansion shard ID and the corresponding data source connection information, and expanding the database. At this time, the number of shards after expansion increases, that is, the shard rule changes, and the new business data is stored according to the new rule. However, the target shard calculated according to the new shard rule for the query and update of the business data before expansion may not be the shard where the original business data is actually landed. Therefore, the first execution result is empty, and the operation needs to be executed twice according to the original shard rule, that is, step S212.

[0049] That is, the target shard is determined by the historical number of shards before expansion and the shard key of the SQL statement. In this way, the data source corresponding to the SQL statement before expansion can be accurately determined, and then the second execution is performed, that is, the execution result can be obtained, such as querying the corresponding data or making corresponding changes to the data.

[0050] 2) If the current state of the database is a contraction state, for example, the detection process detects that a shard in the database has failed and marks the shard as unavailable, such as the M3 shard being initially unavailable, in this case, the newly added data A is automatically stored in the M4 shard. Therefore, when querying or updating the newly added data A, executing in the M3 shard will not get the corresponding result. At this time, step S214 needs to be executed to mark the next attribute tag of the first target shard as the available shard as the fifth target shard, and execute the SQL statement based on the fifth target shard. That is, in the case where the newly added data A is stored in the M4 shard, querying or updating in the M4 shard can get the corresponding result.

[0051] (2) If the attribute tag of the first target shard is unavailable, execute step S216 to mark the next attribute tag of the first target shard as the available shard as the third target shard, and execute the SQL statement based on the third target shard to obtain a second execution result. For example, the M3 shard is unavailable, so query or update in the M4 shard. If the M3 shard is initially unavailable, the newly added data A is stored in the M4 shard. At this time, querying or updating in the M4 shard can get the correct result. Of course, there are also cases where the correct result cannot be obtained, for example, if the M3 shard is initially available, the newly added data is stored in the M3 shard, and now the M3 shard is unavailable due to failure. At this time, searching in the M4 does not get the result.

[0052] Therefore, if the second execution result is empty, the corresponding strategy is executed according to the operation type of the SQL statement. Specifically, the following two cases:

[0053] 1) If the operation type of the SQL statement is a query operation, execute step S218 to return a first specified error code;

[0054] 2) If the operation type of the SQL statement is an update operation, perform step S220, return a second specified error code, and add the SQL statement to the fault table.

[0055] When it is detected that the attribute label of the first target shard is restored from unavailable to available, perform step S222, re-execute the SQL statement corresponding to the first specified error code, and re-execute the corresponding SQL statement in the fault table based on the second specified error code.

[0056] The following lists a processing flow in an expansion state, refer to Figure 3 as shown:

[0057] ① The DBA (database administrator) and the development jointly assess the need for database expansion, and the DBA prepares the database shards, the operation and maintenance modifies the application configuration to synchronize the expansion shard data source information and opens the database expansion switch. ② The self-defined mybatis interceptor is used to determine whether the currently executed SQL (structured query language) is a shard query or an SQL statement for updating data operation. If it is and the first execution result of the SQL is empty, a second execution label is added and a second SQL execution call is performed.

[0058] In the data source management layer (sharding-jdbc), a data shard algorithm is defined, the shard algorithm is determined according to the second execution label, the shard data source is selected, and the SQL statement is executed again.

[0059] In the case of no fault shard, the SQL statement execution of the new operation will not have the problem of unsuccessful execution, therefore, the above mainly explains the case under the query or update operation.

[0060] After the shard expansion, the update and query SQL statements are identified and executed again in the data source layer, realizing zero migration of data after expansion, and the entire expansion process does not require the application to be shut down, and is not sensitive to the business, thereby ensuring the stability of the financial business system.

[0061] The following lists a processing flow in a shrinkage scenario due to shard failure:

[0062] 1. The application database detection process detects single-shard failure, alarms, and marks the shard as unavailable at the application level.

[0063] 2. The self-defined mybatis interceptor is used to mark the operation type label (update, add, query) for the current to-be-executed, and different operations are performed according to the SQL statement execution result:

[0064] Query operation: result, directly return, result is empty and sqlMasterIdEnable = false, then return a specific error code, (query exception, please try again later).

[0065] Update operation: result, direct return, result is empty and sqlMasterIdEnable = false, save the SQL statement to the fault table, and return a specific error code.

[0066] New operation: direct return.

[0067] 3. In the data source management layer (sharding-jdbc), the custom data sharding algorithm calculates the shard ID according to the SQL shard key, and judges whether the shard ID is available. If it is available, it is directly returned. If it is not available, the shard ID is incremented by 1 (get the next available shard ID, reset ID = 0 and increment again when the shard ID is greater than the number of shards, until the next available shard is found) and returned with the unavailable shard SQL operation tag (sqlMasterIdEnable = false).

[0068] 4. Fault recovery processing:

[0069] The application detection process detects the recovery of the fault shard and identifies the shard as available.

[0070] Custom mybatis interceptor, according to the SQL execution result, execute different operations:

[0071] Query operation: result, direct return, result is empty and sqlMasterIdEnable = false, add secondary execution tag and trigger secondary execution.

[0072] Update operation: result, direct return, result is empty and sqlMasterIdEnable = false, add secondary execution tag and trigger secondary execution.

[0073] New operation: direct return.

[0074] Custom data sharding algorithm calculates the shard ID according to the shard key of the SQL statement;

[0075] Judge whether there is a secondary execution tag, if there is, increment the shard ID by 1 (get the next available shard ID, reset ID = 1 and increment again when the shard ID is greater than the number of shards, until the next available shard is found) and return, if not, judge whether the shard ID is in the recovery process, if not, directly return, if in the process, according to the operation type:

[0076] Query operation: set sqlMasterIdEnable = false

[0077] Update operation: set sqlMasterIdEnable = false

[0078] Add operation: return the shard ID directly.

[0079] The application exception processing process scans the fault table, obtains the SQL statement to be processed, and executes on the fault shard, and after the processing is completed, the fault shard is updated to be available.

[0080] The embodiment of the application provides a processing method for SQL statements in a distributed database, which lists different processing methods in four cases. The first case is that the operation type is an add operation and the shard is available. The second case is that the operation type is an add operation and the shard is unavailable. The third case is that the operation type is a query or update operation and the shard is available. The fourth case is that the operation type is a query or update operation and the shard is unavailable. Then, the third case and the fourth case each include two different processing methods. The processing method provided by the embodiment of the application can perform different processing according to the attribute tag of the first target shard and the operation type of the SQL statement. Therefore, whether in the case of expanding the database by adding shards or in the case of shrinking the database due to shard failure, data migration and re-sharding are not required, smooth data processing under database expansion and contraction can be realized, data processing consistency can be ensured, and the demand of financial services can be met.

[0081] Based on the above method embodiment, the embodiment of the application further provides a processing device for SQL statements in a distributed database. The device is applied to an application server, and a database shard in the server corresponds to an attribute tag. The attribute tag includes available or unavailable. Referring to FIG. 4, the device includes: Figure 4

[0082] A statement obtaining module 42 is configured to obtain a SQL statement to be executed. The SQL statement carries a shard key and an operation type. The operation type includes an add operation, an update operation or a query operation. A shard determining module 44 is configured to determine a first target shard corresponding to the SQL statement according to the shard key and the current number of shards. A statement executing module 46 is configured to execute the SQL statement according to the attribute tag of the first target shard and the operation type of the SQL statement.

[0083] When the SQL statement to be executed is received, the embodiment of the application can perform different operations on the SQL statement according to different attribute tags of shards and different operation types of SQL statements. Therefore, whether in the case of expanding the database by adding shards or in the case of shrinking the database due to shard failure, data migration and re-sharding are not required, data processing consistency can be ensured, and the demand of financial services can be met.

[0084] ​In the preferable embodiment of the present application, the operation type of the SQL statement is an adding operation; the statement execution module 46 is further configured to execute the SQL statement based on the first target shard if the attribute label of the first target shard is available; and find a second target shard with an available attribute label in the order of the shard identifiers if the attribute label of the first target shard is not available, and execute the SQL statement based on the second target shard.

[0085] In the preferable embodiment of the present application, the operation type of the SQL statement is a query operation or an update operation; the statement execution module 46 is further configured to execute the SQL statement based on the first target shard if the attribute label of the first target shard is available, and obtain a first execution result; determine an execution strategy corresponding to the SQL statement according to the current state of the database if the first execution result is empty; the current state includes an expansion state or a shrinkage state caused by a fault shard; and if the attribute label of the first target shard is not available, take a shard with an available attribute label corresponding to the next attribute label of the first target shard as a third target shard, and execute the SQL statement based on the third target shard.

[0086] In the preferable embodiment of the present application, the statement execution module 46 is further configured to obtain a historical number of shards before the database is expanded if the current state of the database is the expansion state, determine a fourth target shard according to the shard key of the SQL statement and the historical number of shards, and execute the SQL statement based on the fourth target shard; and if the current state of the database is the shrinkage state, take a shard with an available attribute label corresponding to the next attribute label of the first target shard as a fifth target shard, and execute the SQL statement based on the fifth target shard.

[0087] In the preferable embodiment of the present application, the statement execution module 46 is further configured to obtain a second execution result obtained by executing the SQL statement based on the third target shard after the step of executing the SQL statement based on the third target shard, and execute a corresponding strategy according to the operation type of the SQL statement if the second execution result is empty.

[0088] In the preferable embodiment of the present application, the statement execution module 46 is further configured to return a first specified error code if the operation type of the SQL statement is the query operation, and return a second specified error code if the operation type of the SQL statement is the update operation, and add the SQL statement to a fault table.

[0089] In the preferable embodiment of the present application, the statement execution module 46 is further configured to re-execute the SQL statement corresponding to the first specified error code and re-execute the SQL statement corresponding to the second specified error code in the fault table based on the second specified error code when it is detected that the attribute label of the first target shard is restored from unavailable to available.

[0090] The device provided by the embodiments of the present application has the same implementation principle and technical effects as the foregoing method embodiments. For brevity, the part of the device embodiments not mentioned in the foregoing method embodiments can be referred to the corresponding content in the foregoing method embodiments.

[0091] The embodiments of the present application further provide an electronic device, as shown in Figure 5 The electronic device includes a processor 51 and a memory 50, and the memory 50 stores computer executable instructions capable of being executed by the processor 51, and the processor 51 executes the computer executable instructions to implement the foregoing method.

[0092] In Figure 5 In the embodiment shown, the electronic device further includes a bus 52 and a communication interface 53, and the processor 51, the communication interface 53 and the memory 50 are connected through the bus 52.

[0093] The memory 50 can include a high-speed random access memory (RAM) and can further include a non-volatile memory, for example, at least one disk memory. The communication connection between the system network element and at least one other network element is implemented through at least one communication interface 53 (which can be wired or wireless), and the Internet, a wide area network, a local area network, a metropolitan area network, etc. can be used. The bus 52 can be an ISA (Industry Standard Architecture) bus, a PCI (Peripheral Component Interconnect) bus or an EISA (Extended Industry Standard Architecture) bus, etc. The bus 52 can be divided into an address bus, a data bus, a control bus, etc. For brevity, Figure 5 In the figure, only one bidirectional arrow is used to represent the bus, but it does not mean that there is only one bus or only one type of bus.

[0094] The processor 51 can be an integrated circuit chip with processing capability. In implementation process, each step of the above method can be completed by integrated logic circuit of hardware in the processor 51 or by instructions in the form of software. The processor 51 described above can be a general processor, including a central processing unit (CPU), a network processor (NP), etc.; can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field-programmable gate array (FPGA) or other programmable logic device, a discrete gate or transistor logic device, a discrete hardware component. The general processor can be a microprocessor or the processor can also be any conventional processor. The steps of the method disclosed in combination with the embodiments of the present application can be directly embodied as a hardware code processor to execute, or be executed by a combination of hardware and software modules in the code processor. The software module can be located in a random access memory, a flash memory, a read-only memory, a programmable read-only memory or an electrically erasable programmable memory, a register, or other mature storage medium in the art. The storage medium is located in the storage, and the processor 51 reads the information in the storage, and combines the hardware to complete the steps of the method of the foregoing embodiments.

[0095] The embodiment of the present application further provides a computer readable storage medium, which stores computer executable instructions. When the computer executable instructions are called and executed by a processor, the computer executable instructions cause the processor to implement the above method. For details, refer to the foregoing method embodiments, which will not be described here.

[0096] The computer program product of the method, device and electronic equipment provided by the embodiment of the present application includes a computer readable storage medium storing program codes. The instructions included in the program codes can be used to execute the method described in the foregoing method embodiments. For details, refer to the method embodiments, which will not be described here.

[0097] Unless otherwise specifically stated, the relative steps, numerical expressions and values of the components and steps set forth in these embodiments do not limit the scope of the present application.

[0098] If the functions are implemented in the form of software function units and sold or used as independent products, they can be stored in a nonvolatile computer readable storage medium executable by a processor. Based on this understanding, the technical solutions of the present application or the part of the prior art that essentially contributes to the prior art or the part of the technical solutions can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes a plurality of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the method described in the embodiments of the present application. The aforementioned storage medium includes: a U disk, a mobile hard disk, a read-only memory (ROM, Read-Only Memory), a random access memory (RAM, Random Access Memory), a magnetic disk or an optical disk, and various storage medium that can store program codes.

[0099] In the description of the present application, it should be noted that the terms "center", "upper", "lower", "left", "right", "vertical", "horizontal", "inner", "outer" and the like indicate the orientation or positional relationship based on the orientation or positional relationship shown in the drawings, and are only for the purpose of facilitating the description of the present application and simplifying the description, and do not indicate or imply that the devices or elements referred to must have a particular orientation, be constructed and operated in a particular orientation, and therefore cannot be understood as limiting the present application. In addition, the terms "first", "second", "third" are only for the purpose of description, and cannot be understood as indicating or implying relative importance.

[0100] Finally, it should be noted that: the above-described embodiments are only specific embodiments of the present application, used to illustrate the technical solutions of the present application, and are not limited thereto, the protection scope of the present application is not limited thereto, although the present application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that any person skilled in the art within the technical scope disclosed by the present application can modify or easily think of changes to the technical solutions recorded in the foregoing embodiments, or make equivalent replacement to part of the technical features; and these modifications, changes or replacements do not make the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present application, and should be covered within the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the protection scope of the claims.

Claims

1. A method for processing SQL statements in a distributed database, characterized in that, The method is applied to an application server, and database shards in the server correspond to attribute tags; The attribute tags include: available or unavailable; the method includes: Obtaining a SQL statement to be executed; the SQL statement carries a shard key and an operation type; the operation type includes: an addition operation, an update operation or a query operation; the shard key is a specified non-empty table field in the SQL statement; According to the shard key and the current number of shards, a first target shard corresponding to the SQL statement is determined; According to the attribute tag of the first target shard and the operation type of the SQL statement, the SQL statement is executed; If the operation type of the SQL statement is a query operation or an update operation, the step of executing the SQL statement according to the attribute tag of the first target shard and the operation type of the SQL statement includes: if the attribute tag of the first target shard is available, the SQL statement is executed based on the first target shard to obtain a first execution result; if the first execution result is empty, an execution strategy corresponding to the SQL statement is determined according to a current state of the database; the current state includes: an expansion state or a shrinkage state caused by a fault shard; if the attribute tag of the first target shard is unavailable, a next attribute tag corresponding to the first target shard is set as a third target shard which is available, and the SQL statement is executed based on the third target shard; According to the current state of the database, the step of determining the execution strategy corresponding to the SQL statement includes: if the current state of the database is the expansion state, a historical number of shards before database expansion is obtained, a fourth target shard is determined according to the shard key of the SQL statement and the historical number of shards, and the SQL statement is executed based on the fourth target shard; if the current state of the database is the shrinkage state, a next attribute tag corresponding to the first target shard is set as a fifth target shard which is available, and the SQL statement is executed based on the fifth target shard.

2. The method of claim 1, wherein, If the operation type of the SQL statement is an addition operation, the step of executing the SQL statement according to the attribute tag of the first target shard and the operation type of the SQL statement includes: If the attribute tag of the first target shard is available, the SQL statement is executed based on the first target shard; If the attribute tag of the first target shard is unavailable, a second target shard which is available is searched according to a shard identification order, and the SQL statement is executed based on the second target shard.

3. The method of claim 1, wherein, After the step of executing the SQL statement based on the third target shard, the method further includes: Obtaining a second execution result obtained by executing the SQL statement based on the third target shard; If the second execution result is empty, a corresponding strategy is executed according to the operation type of the SQL statement.

4. The method of claim 3, wherein, The step of executing the corresponding strategy according to the operation type of the SQL statement includes: If the operation type of the SQL statement is a query operation, a first specified error code is returned; If the operation type of the SQL statement is an update operation, a second specified error code is returned, and the SQL statement is added to a failure table.

5. The method of claim 4, wherein, The method further includes: When detecting that the attribute label of the first target shard is restored from unavailable to available, re-executing the SQL statement corresponding to the first specified error code, and re-executing the corresponding SQL statement in the failure table based on the second specified error code.

6. A device for processing SQL statements in a distributed database, characterized in that, The device is applied to an application server, and database shards in the server correspond to attribute labels. The attribute label includes available or unavailable, and the device includes: A statement obtaining module is configured to obtain a SQL statement to be executed, wherein the SQL statement carries a shard key and an operation type; the operation type includes an add operation, an update operation, or a query operation; and the shard key is a specified non-empty table field in the SQL statement. A shard determining module is configured to determine a first target shard corresponding to the SQL statement according to the shard key and a current number of shards. A statement executing module is configured to execute the SQL statement according to an attribute label of the first target shard and the operation type of the SQL statement. The operation type of the SQL statement is a query operation or an update operation; and the statement executing module is further configured to, if the attribute label of the first target shard is available, execute the SQL statement based on the first target shard to obtain a first execution result; if the first execution result is empty, determine an execution strategy corresponding to the SQL statement according to a current state of a database; the current state includes an expansion state or a shrinkage state caused by a failure shard; if the attribute label of the first target shard is unavailable, taking a shard with a next available attribute label corresponding to the first target shard as a third target shard, and executing the SQL statement based on the third target shard; if the current state of the database is the expansion state, obtaining a historical number of shards before database expansion, determining a fourth target shard according to the shard key of the SQL statement and the historical number of shards, and executing the SQL statement based on the fourth target shard; and if the current state of the database is the shrinkage state, taking a shard with a next available attribute label corresponding to the first target shard as a fifth target shard, and executing the SQL statement based on the fifth target shard.

7. An electronic device, comprising: The computer readable storage medium stores computer executable instructions, which, when invoked and executed by a processor, cause the processor to implement the method in any one of claims 1 to 5.

8. A computer-readable storage medium, characterized in that, The computer readable storage medium stores computer executable instructions, which, when invoked and executed by a processor, cause the processor to implement the method in any one of claims 1 to 5.

Images