A method for visualizing SQL request routing in sharding mode

Abstract

The present invention relates to the field of database technology, and provides a method for visualizing SQL request routing in a sub-library and sub-table mode, including: creating an SQL request according to a user's link tracking request; adding a link tracking annotation to the created SQL request, and sending the SQL request with the added link tracking annotation to a sub-library and sub-table system; parsing the SQL request with the added link tracking annotation received by the sub-library and sub-table system to obtain a link unique identifier and a fragment unique identifier; constructing a link tracking context using the link unique identifier and the fragment unique identifier, and passing the obtained link tracking context in a method for executing an SQL request in a sub-library and sub-table system; sending an SQL request to a physical database through the link tracking context passed in the method for executing an SQL request in a sub-library and sub-table system. The method for visualizing SQL request routing in a sub-library and sub-table mode of the present invention can show in a link tracking system which physical databases the SQL request is finally executed on, and how long it takes to execute.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method for visualizing SQL request routing in a database and table sharding mode. Background Art

[0002] In the scenario of big data and high concurrency, the IO bottleneck and CPU bottleneck of a single database server may cause the database response to become slow. In extreme cases, it may even cause the database to crash. This is where the database sharding technology was born.

[0003] According to our performance or capacity requirements, we split the single table so that the data originally stored in one table is evenly distributed to several shard tables, and then these shard tables are evenly distributed to the physical database. For example, a user table with a planned capacity of storing 100 million data records is split into 100 shard user tables, each of which stores 1 million data records. Figure 1 As shown in the figure, these 100 sharded user tables are distributed to 10 physical databases, and each physical database carries 10 sharded user tables. After sharding, the data is evenly distributed in the physical database and sharded tables. After data sharding, a SQL request will be rewritten after being calculated by the sharding system, and then routed to a specific physical database for execution. It may be routed to one physical database for execution, or it may be routed to multiple physical databases for execution. The execution results of the physical database are summarized by the sharding system and returned to the request initiator.

[0004] Due to the complexity of the sharding system, it has the following deficiencies in practical applications: 1. Developers using the sharding system cannot identify where the returned results come from after initiating a SQL request; 2. When the returned request results do not meet expectations, they cannot be checked.

[0005] Therefore, how to provide a method for visualizing SQL request routing in a sharded database and table system so that developers can execute SQL requests on the corresponding physical database based on routing information to verify the results returned by the sharded database and table system has become a technical problem that needs to be solved urgently. Summary of the invention

[0006] In view of this, in order to overcome the shortcomings of the prior art, the present invention provides a method for visualizing SQL request routing in a sharded database and sharded table mode, so that developers can identify and view which physical databases SQL requests are routed to, and verify how SQL request results are generated, so that developers can view the time consumed by executing SQL requests on physical databases and evaluate the performance of physical databases.

[0007] On the one hand, the present invention provides a method for visualizing SQL request routing in a sharding database and sharding table mode, comprising:

[0008] Step S1: Create an SQL request according to the user's link tracking request;

[0009] Step S2: adding a link tracking annotation to the created SQL request, and sending the SQL request with the link tracking annotation to the sub-library and sub-table system;

[0010] Step S3: parsing the SQL request for adding link tracking annotations received by the sub-library and sub-table system to obtain the link unique identifier and the fragment unique identifier;

[0011] Step S4: construct a link tracking context using the link unique identifier and the fragment unique identifier obtained in step S3, and pass the obtained link tracking context to the method for executing SQL requests in the sub-library and sub-table system;

[0012] Step S5: Sending an SQL request to the physical database through the link tracking context passed in the method of executing the SQL request in the sub-library and sub-table system.

[0013] Furthermore, in step S2 of the method for visualizing SQL request routing in the sub-library and sub-table mode of the present invention, the link tracking annotation consists of a version number, a link unique identifier, a fragment unique identifier, and a current tracking flag.

[0014] Furthermore, in step S2 of the method for visualizing SQL request routing in the database and table sharding mode of the present invention, the current tracking flag of the link tracking annotation is used to mark whether the link data is tracked and sampled.

[0015] Furthermore, step S3 of the method for visualizing SQL request routing in the database and table sharding mode of the present invention includes: intercepting the link tracking annotation in the SQL request to which the link tracking annotation is added, and segmenting the link tracking annotation string to obtain a string of a link unique identifier and a fragment unique identifier.

[0016] Furthermore, in step S4 of the method for visualizing SQL request routing in the database and table sharding mode of the present invention, the obtained link tracking context is transmitted in the method for executing SQL requests in the database and table sharding system, including:

[0017] When the link tracking context is passed to each method that executes an SQL request, before the method that currently executes the SQL request is executed, the name and execution start time of the method that currently executes the SQL request are added to the link tracking context to generate a new link tracking context;

[0018] Pass the new link tracking context to the next method that executes the SQL request;

[0019] After the next method for executing the SQL request is executed, the execution returns to the method currently executing the SQL request and continues. After the current method for executing the SQL request is executed, the execution end time of the method currently executing the SQL request is added to the link tracking context corresponding to the method currently executing the SQL request.

[0020] Furthermore, step S5 of the method for visualizing SQL request routing in the sub-library and sub-table mode of the present invention includes: when the link tracking context is passed to the physical database request method, sending an SQL request to the physical database, and adding the name of the physical database to the link tracking context passed to the physical database request method.

[0021] Furthermore, the method of visualizing SQL request routing in the sharding mode of the present invention includes: tracking the corresponding physical database through the link tracking context of each method for executing SQL requests in the sharding system and checking the execution time of the SQL request on the physical database.

[0022] In another aspect, the present invention provides a computer storage medium having a computer program stored thereon, wherein when the computer program is executed, the method described above is performed.

[0023] Finally, the present invention also provides a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the method when executing the program.

[0024] The method for visualizing SQL request routing in the sub-library and sub-table mode of the present invention can view which physical databases the SQL requests are routed to through the link tracking system, so that developers can identify and view which physical databases the SQL requests are routed to, and verify how the SQL request results are generated, so that developers can view the time consumed by executing SQL requests on the physical database and evaluate the performance of the physical database. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0026] Figure 1 The figure is a schematic diagram of distributing a sharded user table to multiple physical databases in the prior art.

[0027] Figure 2The flowchart of the method for visualizing SQL request routing in the database and table sharding mode is shown in the first exemplary embodiment of the present invention. DETAILED DESCRIPTION

[0028] The embodiments of the present invention are described in detail below with reference to the accompanying drawings.

[0029] It should be noted that the following embodiments and features in the embodiments may be combined with each other in the absence of conflict; and, based on the embodiments in the present disclosure, all other embodiments obtained by ordinary technicians in the field without making any creative work are within the scope of protection of the present disclosure.

[0030] It should be noted that various aspects of the embodiments within the scope of the appended claims are described below. It should be apparent that the aspects described herein may be embodied in a wide variety of forms, and any specific structure and / or function described herein is merely illustrative. Based on the present disclosure, it should be understood by those skilled in the art that an aspect described herein may be implemented independently of any other aspect, and two or more of these aspects may be combined in various ways. For example, any number of aspects described herein may be used to implement the device and / or practice the method. In addition, other structures and / or functionalities other than one or more of the aspects described herein may be used to implement this device and / or practice this method.

[0031] Figure 2 FIG. 4 is a flowchart of a method for visualizing SQL request routing in a database and table sharding mode according to an exemplary first embodiment of the present invention. Figure 2 As shown, the method of this embodiment includes:

[0032] Step S1: Create an SQL request according to the user's link tracking request;

[0033] Step S2: adding a link tracking annotation to the created SQL request, and sending the SQL request with the link tracking annotation to the sub-library and sub-table system;

[0034] Step S3: parsing the SQL request for adding link tracking annotations received by the sub-library and sub-table system to obtain the link unique identifier and the fragment unique identifier;

[0035] Step S4: construct a link tracking context using the link unique identifier and the fragment unique identifier obtained in step S3, and pass the obtained link tracking context to the method for executing SQL requests in the sub-library and sub-table system;

[0036] Step S5: Sending an SQL request to the physical database through the link tracking context passed in the method of executing the SQL request in the sub-library and sub-table system.

[0037] Step S5 of the method of this embodiment includes: when the link tracking context is passed to the physical database request method, sending an SQL request to the physical database, and adding the name of the physical database to the link tracking context passed to the physical database request method.

[0038] In actual application, the method of this embodiment can track the corresponding physical database and check the execution time of the SQL request on the physical database through the link tracking context of each method executing the SQL request in the sub-library and sub-table system.

[0039] The method of this embodiment enables developers to identify and view which physical databases SQL requests are routed to, enables developers to verify how SQL request results are generated, and facilitates developers to view the time consumed in executing SQL requests on physical databases and evaluate the performance of physical databases.

[0040] The second exemplary embodiment of the present invention provides a method for visualizing SQL request routing in a database and table sharding mode. Figure 2 In the preferred embodiment of the method shown, in step S2 of the method of this embodiment, the link tracking annotation consists of a version number, a link unique identifier, a fragment unique identifier and a current tracking flag, wherein the current tracking flag is used to mark whether the link data is tracked and sampled.

[0041] In actual application, the method of this embodiment creates an SQL request based on the user's link tracing request. When initiating an SQL request to the sub-library and sub-table system, the Hint mechanism is used to add a link tracing annotation TraceParentHint to the SQL request. The format of TraceParent Hint is ${Version}-${TraceID}-${SpanID}-${Flag}, where Version is the version number, TraceID is the link unique identifier, SpanID is the fragment unique identifier, and Flag is the current tracing flag.

[0042] The third exemplary embodiment of the present invention provides a method for visualizing SQL request routing in a database and table sharding mode. Figure 2 A preferred embodiment of the method shown, step S3 of the method of this embodiment, includes: intercepting the link tracking annotation in the SQL request for adding the link tracking annotation, and segmenting the link tracking annotation string to obtain a string of a link unique identifier and a fragment unique identifier.

[0043] In actual application, the method of this embodiment intercepts the string of the link tracking annotation TraceParent Hint in the SQL request for adding the link tracking annotation, and splits the string with a hyphen "-". The second and third substrings obtained are the strings of the link unique identifier TraceID and the fragment unique identifier SpanID.

[0044] In actual applications, after adding a link tracing annotation TraceParent Hint in the format of 00-85f85c3442590b76d0723eed4326ccd8-81e51023670f4313-01 to an SQL request in the format of SELECT*FROM test_record WHERE id>=1000and id<1500, the format of the SQL request to which the link tracing annotation is added is SELECT / *+TraceParent('00-85f85c3442590b76d0723eed4326ccd8-81e51023670f4313-01')+ / *FR OM test_record WHERE id>=1000and id<1500.

[0045] When the SQL request with the link tracking annotation added is passed to the method for executing the SQL request in the sharding system, the link unique identifier TraceID can be parsed as 85f85c3442590b76d0723eed4326ccd8, and the fragment unique identifier SpanID can be parsed as 81e51023670f4313.

[0046] The fourth exemplary embodiment of the present invention provides a method for visualizing SQL request routing in a database and table sharding mode. Figure 2 In the preferred embodiment of the method shown, in step S4 of the method of this embodiment, the obtained link tracking context is passed to the method for executing the SQL request in the sub-library and sub-table system, including:

[0047] When the link tracking context is passed to each method that executes an SQL request, before the method that currently executes the SQL request is executed, the name and execution start time of the method that currently executes the SQL request are added to the link tracking context to generate a new link tracking context;

[0048] Pass the new link tracking context to the next method that executes the SQL request;

[0049] After the next method for executing the SQL request is executed, the execution returns to the method currently executing the SQL request and continues. After the current method for executing the SQL request is executed, the execution end time of the method currently executing the SQL request is added to the link tracking context corresponding to the method currently executing the SQL request.

[0050] The present invention provides a computer storage medium on which a computer program is stored. When the computer program is executed, the method described is performed.

[0051] Finally, the present invention also provides a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the method when executing the program.

[0052] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any changes or substitutions that can be easily thought of by a person skilled in the art within the technical scope disclosed by the present invention should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention shall be subject to the protection scope of the claims.

Claims

1. A method for visualizing SQL request routing in a sharding database and sharding table mode, characterized in that: The method comprises: Step S1: Create an SQL request according to the user's link tracking request; Step S2: adding a link tracking annotation to the created SQL request, and sending the SQL request with the link tracking annotation to the sub-library and sub-table system. The link tracking annotation consists of a version number, a link unique identifier, a fragment unique identifier, and a current tracking flag, and the current tracking flag is used to mark whether the link data is tracked and sampled; Step S3: parsing the SQL request for adding link tracking annotations received by the sub-library and sub-table system to obtain the link unique identifier and the fragment unique identifier; Step S4: construct a link tracking context using the link unique identifier and the fragment unique identifier obtained in step S3, and pass the obtained link tracking context to the method for executing SQL requests in the sub-library and sub-table system; Step S5: sending an SQL request to the physical database through the link tracking context passed in the method of executing the SQL request in the sub-library and sub-table system; The obtained link tracking context is passed to the method of executing SQL requests in the sharding system, including: When the link tracking context is passed to each method that executes an SQL request, before the method that currently executes the SQL request is executed, the name and execution start time of the method that currently executes the SQL request are added to the link tracking context to generate a new link tracking context; Pass the new link tracking context to the next method that executes the SQL request; After the next method for executing the SQL request is executed, the execution returns to the method currently executing the SQL request and continues. After the current method for executing the SQL request is executed, the execution end time of the method currently executing the SQL request is added to the link tracking context corresponding to the method currently executing the SQL request.

2. The method for visualizing SQL request routing in a sharding database and sharding table mode according to claim 1 is characterized in that: Step S3 includes: intercepting the link tracking annotation in the SQL request for adding the link tracking annotation, and segmenting the link tracking annotation character string to obtain character strings of the link unique identifier and the fragment unique identifier.

3. The method for visualizing SQL request routing in a sharding database and sharding table mode according to claim 1 is characterized in that: Step S5 includes: when the link tracking context is passed to the physical database request method, sending an SQL request to the physical database, and adding the name of the physical database to the link tracking context passed to the physical database request method.

4. The method for visualizing SQL request routing in a database and table sharding mode according to claim 1, characterized in that: The method comprises: tracking the corresponding physical database through the link tracking context of each method for executing SQL requests in the sub-library and sub-table system and checking the execution time of the SQL request on the physical database.

5. A computer storage medium, characterized in that: The computer storage medium stores a computer program, and when the computer program is executed, the method according to any one of claims 1 to 4 is performed.

6. A computer device, characterized in that: The computer device comprises a memory, a processor and a computer program stored in the memory and executable on the processor, and the processor executes the method according to any one of claims 1 to 4 when executing the program.

Images