MySQL Database optimization One

First look at the entire process of sending and receiving requests from the MySQL database

MySQL logical architecture is divided into three layers, the top layer is the client tier, not unique to MySQL, such as: Connection processing, authorization authentication, security and other functions are processed at this level.

MySQL most of the core services are in the middle layer, including query parsing, analysis, optimization, caching, built-in functions (such as: time, math, encryption and other functions). The functionality of all cross-storage engines is also implemented at this level: stored procedures, triggers, views, and so on.

The bottom layer is the storage engine, which is responsible for data storage and extraction in MySQL. Similar to the file system under Linux, each storage engine has its advantages and disadvantages. The intermediate service layer communicates with the storage engine through the API, which masks the differences between different storage engines.

Client/server-side communication protocol

The MySQL client/server communication protocol is "half duplex": At any one time, either the servers send data to the client or the client sends data to the server, and the two actions cannot occur at the same time. Once one end begins to send a message, the other end receives the entire message to respond to it, so we cannot and do not have to send a message into small pieces independently, nor do we have a way to control traffic.

The client sends the query request to the server with a separate packet, so the parameters need to be set when the query statement is long. max_allowed_packet However, it is important to note that if the query is too large, the server will refuse to receive more data and throw an exception.

Conversely, the server responds to the user with a lot of data, consisting of multiple packets. However, when the server responds to a client request, the client must fully receive the entire return result, rather than simply taking the previous few results and then having the server stop sending. Therefore, in the actual development, it is a good habit to keep the query as simple as possible and return only the necessary data, reducing the size and quantity of packets between the communication, which is also SELECT * one of the reasons why the query avoids using and adding LIMIT the limit.

MySQL uses the cost-based optimizer in MySQL to get the cost of calculating the current query by querying the value of the current session's Last_query_cost.

Large table ALTER TABLE is very time-consuming, and MySQL performs most of the table result operations by creating an empty table with the new structure, inserting all the data from the old table into the new table, and then deleting the old table.

There are not too many columns in the schema. The reason is that the storage engine's API works by copying data between the server layer and the storage engine layer in a row-buffered format, and then decoding the buffered content into columns at the server level, the cost of this conversion process is very high. If you have too many columns and you actually use a very small number of columns, you may incur excessive CPU usage.

The time to add an index is certainly greater than the time it takes to initially add an index

[Inner] Join on/using
If the associated field names of the two tables are the same, you can use using to establish a relationship that is concise and straightforward. If not the same, you can only use on the OH ~

Paging and where statements

SELECT * from reader where id>5000;
SELECT * from reader limit 5000, 10;
Pagination will first sort the records all over again, select 5000 after the 10 data, and where conditional expression query-by-article, greater than 5000 is drawn out, do not need to sort first.

SELECT film_id,description from film ORDER by title LIMIT 50, 5;
When the limit offset is particularly large, grouping is used to reduce the scanned page
Let MySQL scan as few pages as possible to get the columns needed to query the original table based on the associated columns after getting the records that need to be accessed
SELECT film.film_id,film.description
From film INNER JOIN (
SELECT film_id from film ORDER by title LIMIT 50,5
) as TMP USING (film_id);

Specific descriptions refer to the following links:

Https://www.jianshu.com/p/d7665192aaaf

MySQL Database optimization One