MySQL “passive” performance optimization summary!

When you are young, you don't know the pain of optimization. When you meet a pit, you know the difficulty of optimization—— Uncle Wang at the entrance of the village

The content map of this paper is as follows:

I have talked about performance optimization in many previous articles, such as the following:

Of course, this article is also about performance optimization. Should performance optimization be a shuttle? Or should it comply with some norms and principles?

So before we start (MySQL optimization), let's talk about some principles of performance optimization.

Performance optimization principles and classification

Performance optimization can generally be divided into:

The so-called active optimization refers to a kind of behavior that is carried out spontaneously without external force. For example, when the service is not obviously stuck, down or the hardware indicators are abnormal, the behavior of self starting to optimize can be called active optimization.

Passive optimization is just the opposite of active optimization. It refers to the behavior of optimizing only when the server is stuck, the service is abnormal or the physical index is abnormal.

Performance optimization principle whether active optimization or passive optimization shall comply with the following performance optimization principles:

The above principles seem to be nonsense, but they give us an inspiration that our means of performance optimization should be: prevention of performance problems first + passive optimization second.

In other words, we should focus on preventing performance problems and avoid performance problems as much as possible in the development stage. Under normal circumstances, we should try to avoid active optimization to prevent unknown risks (unless it is for KPI or idle), especially for the production environment. Finally, we should consider passive optimization.

MySQL passive performance optimization

Therefore, this article will focus on the knowledge of MySQL passive performance optimization. According to the knowledge of passive performance optimization, you can get some methods to prevent performance problems, so as to avoid MySQL performance problems.

In this paper, we will start with the problem, and then consider the causes of the problem and the corresponding optimization scheme. In actual development, we usually encounter the following three problems:

Problem 1: single SQL runs slowly

problem analysis

There are two common reasons for slow running of a single SQL:

Solution 1: create and use indexes correctly

Index is a main means to help MySQL improve query efficiency. Therefore, generally, the performance problems of a single SQL are usually caused by not creating or using the index correctly. Therefore, when a single SQL runs slowly, the first thing you need to do is to check whether the index of this table is created normally.

If the index of the table has been created, then check whether the SQL statement triggers the index query normally. If the following occurs, MySQL will not use the index normally:

Therefore, you should try to avoid the above situations. In addition to using the index normally, we can also use the following techniques to optimize the query speed of the index:

Solution 2: data splitting

When the amount of data in the table is too large, the SQL query will be slow. You can consider splitting the table to reduce the amount of data in each table, so as to improve the query efficiency.

1. Vertical split

It refers to splitting a table into multiple tables with more columns. For example, some fields in the user table are often accessed. Put these fields in one table and some fields that are not commonly used in another table. When inserting data, use transactions to ensure the data consistency of the two tables. Principle of vertical split:

2. Horizontal split

It refers to splitting data table rows. When the number of rows exceeds 2 million, it will slow down. At this time, the data of one table can be divided into multiple tables for storage. In general, we use the mode of module to split the table. For example, a 400W user table users. In order to improve its query efficiency, we divide it into four tables users1, users2, users3 and users4, and then use the mode of user ID. at the same time, query, update and delete are also operated by the mode of module.

Other optimization schemes of the table:

Problem 2: some SQL is running slowly

problem analysis

Some SQL runs slowly. The first thing we need to do is locate these SQL, and then see whether these SQL create and use indexes correctly. In other words, we should first use the slow query tool to locate the specific SQL, and then use the solution of problem 1 to process the slow SQL.

Solution: slow query analysis

MySQL comes with the function of slow query log. When it is turned on, it can be used to record statements whose response time exceeds the threshold in mysql, specifically those whose running time exceeds long_ query_ SQL with time value will be recorded in the slow query log. long_ query_ The default value of time is 10, which means running statements for more than 10s. By default, the MySQL database does not start the slow query log. We need to set this parameter manually. If it is not necessary for tuning, it is generally not recommended to start this parameter, because opening the slow query log will have a certain performance impact on the MySQL server. Slow query log supports writing log records to files and database tables. Use MySQL > show variables like '% slow'_ query_ log%'; To query whether the slow query log is enabled. The execution effect is shown in the following figure:

Enable slow query log

To start the slow query log, you can use the following MySQL command:

However, this setting method only takes effect for the current database. If MySQL is restarted, it will also become invalid. If it wants to take effect permanently, you must modify the MySQL configuration file my CNF, configured as follows:

After you start the slow query log, all slow query SQL will be recorded in slow_ query_ log_ In the file configured by the file parameter, the default is / TMP / MySQL_ slow. Log file. At this time, we can open the log and query all slow SQL for optimization one by one.

Problem 3: the whole SQL runs slowly

problem analysis

When the whole SQL runs slowly, it indicates that the bearing capacity of the database has reached the peak. Therefore, we need to use some database expansion methods to alleviate the MySQL server.

Solution: read write separation

Generally speaking, the database is "read more and write less". In other words, the pressure of the database is mostly caused by a large number of data reading operations. We can adopt the scheme of database cluster and use one database as the main database to write data; Other libraries are slave libraries, which are responsible for reading data. This can relieve the access pressure to the database.

There are two common read / write separation schemes for MySQL:

1. Application layer solution

The data source can be routed through the application layer to achieve read-write separation. For example, using spring MVC + mybatis, you can route SQL to spring and control the data source displayed by code through AOP or annotation. Advantages: the routing strategy has strong scalability and controllability. Disadvantages: you need to add coupling control code in spring.

2. Middleware solution

The master-slave cluster is based on MySQL middleware, such as MySQL proxy, amoeba, Atlas and other middleware, which can meet the requirements. Advantages: decoupled from the application layer. Disadvantages: add a risk point for service maintenance. The performance and stability need to be tested. It needs to support code mandatory master-slave and transaction.

Extended knowledge: SQL statement analysis

In mysql, we can use the explain command to analyze the execution of SQL, such as:

As shown in the figure below:

The most important one is the type field. The type value types are as follows: