Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the design method of MySQL client and middleware". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the design method of MySQL client and middleware?"

Introduction of MySQL Communication Protocol

When executing a MySQL query, such as "selecet * from test", the reply packet of MySQL is called ResultSet, which is a set of logical packets (protocol packages), as shown in figure 1, which contains two parts:

1. Metadata, including the following data packets:-Field_Count: number of columns-Field: column description, usually multiple-Eof: used to mark the end of a section of data transmission when the column information is described, or when the data is sent. In the higher version, the packet was cancelled. two。 Row data, including:-Row: the content of a row of data, multiple-Err: describe errors, when errors occur, for the last logical package or-OK: in a higher version of the protocol, to replace the Eof packet to transmit more information

Figure 1 MySQL result set message structure

Introduction of client Library Interface

Therefore, two sets of function interfaces are provided in MySQL CAPI:

-mysql_store_result/mysql_stmt_store_result-mysql_use_result generally speaking, the difference between the two interfaces is:-mysql_store_result/mysql_stmt_store_result: store the results in the application memory-mysql_use_result: the data is stored on the tcp buffer or database server

But from the perspective of the communication process:

-mysql_store_result/mysql_stmt_store_result: you need to wait for all the data to be transferred and parsed by the client.-mysql_use_result: simply put, as long as you get the row packet, you can return data to the upper api. So, we internally call mysql_use_result streaming, which has two advantages:-faster response speed in the application layer. Because you don't have to wait for the result set to be collected-memory management is more controllable to avoid insufficient memory on the client side.

In the mysql client and in the mysqldump command, you have the following parameters:

-quik/-q. Even if you use mysql_use_result for streaming, you can avoid oom under large amounts of data in mysqldump.

JDBC is more encapsulated in design, and its logic is generally similar to that of CAPI.

Mysql_store_result/mysql_stmt_store_result processing logic is the same, but there are two methods to convert it to streaming mode:-code level: the second and third parameters of prepareStatement are set to ResultSet.TYPE_FORWARD_ONLY, and ResultSet.CONCUR_READ_ONLY-JDBC URL is set (do not modify the code): add the parameter useCursorFetch=true&defaultFetchSize=-2147483648 (this method behaves differently on different versions of jdbc drivers and is not recommended)

The relationship between API and protocol resolution is shown in figure 2:

▲ figure 2 API and protocol parsing

UPSQL Proxy middleware design

Before UPSQL Proxy 2.4.0, blocking mode was used, that is, after collecting the result sets to multiple backends, and then replying to the user, this has two disadvantages:-

The response delay extends the memory control of the Proxy layer, so that the production environment does not support the return of more than 10w rows of data under the sub-database.

UPSQL Proxy 2.4.0 implements streaming, which simply means that the line information is sent to the client in the form of a stream as soon as possible instead of waiting for the middleware to collect and send it. The logic is shown in figure 3:

Flow processing of ▲ figure 3 UPSQL Proxy

That is, in the sub-library scenario, each data node will be accessed concurrently, and when a complete metadata is obtained, it can be returned to the requester immediately, and then the row data can be returned to the requester in a timely manner, so as to reduce the memory requirements of the middleware and improve the corresponding speed of the client.

At this point, I believe you have a deeper understanding of "what is the design method of MySQL client and middleware". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.