MySQL “passive” performance optimization summary!

Time:2021-7-27

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:
MySQL

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

  1. The performance of switch has been improved three times. I only use this move
  2. Several ways to improve the performance of string by 10 times( Source code + principle analysis)
  3. Local variables are five times faster than global variables
  4. How great is the arrival of pool technology? I was surprised to see the comparison between thread and thread pool
  5. The linked list is more than 1000 times slower than the array( Dynamic diagram + performance evaluation)
  6. Seven traversal modes and performance analysis of HashMap
  7. More performance optimization articles

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

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:

  • Active optimization
  • Passive optimization

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.

MySQL

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
Both active optimization and passive optimization shall comply with the following performance optimization principles:

  1. Optimization can not change the logic of service operation, but ensure the reliability of serviceCorrectness
  2. The optimization process and results should ensure the service qualitySecurity
  3. Ensure the quality of servicestability, you cannot sacrifice program stability for performance. For example, you cannot turn off the persistence function to improve the running speed of redis, because the stored data will be lost after the redis server is restarted or powered down.

MySQL

The above principles seem to be nonsense, but they give us an inspiration, that is, weThe means of performance optimization should be: mainly preventing performance problems + supplemented by passive optimization

That is, we shouldFocus on preventing performance problems, avoid performance problems as much as possible in the development phase, andUnder normal circumstances, active optimization should be avoided as far as possible to prevent unknown risks(unless it’s for KPI or free time), especially for the production environment, the last consideration isPassive optimization

PS: in case of slow performance degradation or slow increase of hardware indicators, such as 50% of the memory today, 70% tomorrow and 90% the day after tomorrow, and there is no sign of recovery, we should find and deal with such problems in advance (this situation also belongs to passive optimization).

MySQL passive performance optimization

So this article willFocus 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:

  1. Single SQL runs slowly;
  2. Some SQL is running slowly;
  3. The whole SQL is running slowly.

MySQL

Problem 1: single SQL runs slowly

problem analysis

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

  1. Indexes are not created or used normally;
  2. The amount of data in the table is too large.

Solution 1: create and use indexes correctly

IndexesIt 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, you canThe first thing to do is to check whether the index of this table is created normally

If the index of the table has been created,Next, check whether the SQL statement triggers the index query normally, MySQL will not be able to use indexes normally if:

  1. Use in where clause= Or < > operator, the query reference will abandon the index and scan the whole table;
  2. Leading fuzzy queries, that is, ‘% XX’ or ‘% XX%’, cannot be used. Because leading fuzzy queries cannot use the order of indexes, they must be searched one by one to see whether the conditions are met, which will lead to full index scanning or full table scanning;
  3. If there is an or in the condition, even if there is a conditional index, the index will not be used normally. In order to use or and make the index effective, you can only add an index to each column in the or condition;
  4. Perform expression operations on fields in the where clause.

Therefore, you should try to avoid the above situations, in addition to the normal use of indexes, we can alsoUse the following techniques to optimize the query speed of the index

  1. Try to use the primary key query instead of other indexes, because the primary key query will not trigger the back table query;
  2. The query statement shall be as simple as possible, and the large statement shall be disassembled from the small statement to reduce the locking time;
  3. Try to use numeric fields. If only numeric information is included, try not to design character fields;
  4. Replace in query with exists;
  5. Avoid using is null and is not null on index columns.

Back to table query: after the common index queries the primary key index, it returns to the search process of the primary key index tree. We call it back to table query.

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:

  • Put the uncommon fields in a separate table;
  • Split large fields such as text and blob and put them in the attached table;
  • Often, the columns of combined queries are placed in one table.
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:
  1. Use the smallest data type that can store data;
  2. Using simple data types, int is simpler than varchar in MySQL;
  3. Try to use tinyint, smallint and mediumint as integer types instead of int;
  4. Use not null to define fields as much as possible, because null occupies 4 bytes of space;
  5. Text type should be used as little as possible. When it is necessary, it is best to consider sub table;
  6. Try to use timestamp instead of datetime;
  7. A single table should not have too many fields. It is recommended to have less than 20 fields.

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, the running time exceeds long_ query_ The value of time in the SQL query will be recorded in the 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.
usemysql> show variables like '%slow_query_log%';To query whether the slow query log is enabled. The execution effect is shown in the following figure:
MySQL
slow_ query_ When the value of log is off, it indicates that slow query log is not enabled.

Enable slow query log

To enable slow query logs, you can use the following MySQL commands:

mysql> set global slow_query_log=1

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. The configuration is as follows:

slow_query_log =1
slow_query_log_file=/tmp/mysql_slow.log

When you enable 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 fileAt this point, we can open the log to query all slow SQL for optimization one by one.

Problem 3: the whole SQL is running slowly

problem analysis

The peak running capacity of MySQL database needs to be alleviated by using some methods.

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 hand over the SQL route to spring and control the data source displayed by the 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 can meet the requirements through MySQL middleware, such as MySQL proxy, amoeba, Atlas and other middleware.
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:

explain select * from t where id=5;

As shown in the figure below:
MySQL
Of which:

  • ID – select an identifier. The larger the ID, the higher the priority, and the earlier it is executed;
  • select_ Type – indicates the type of query;
  • Table – the table that outputs the result set;
  • Partitions – matching partitions;
  • Type – indicates the connection type of the table;
  • possible_ Keys – indicates the index that may be used during query;
  • Key – indicates the index actually used;
  • key_ Len – length of index field;
  • ref—   Comparison of columns and indexes;
  • Rows – estimated number of rows;
  • Filtered – percentage of rows filtered by table conditions;
  • Extra – description and description of implementation.

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

  • All – scan all table data;
  • Index – traverse the index;
  • Range – index range search;
  • index_ Subquery – use ref in subquery;
  • unique_ Subquery – use EQ in subquery_ ref;
  • ref_ or_ Null – optimized ref for indexing null;
  • Fulltext – use full-text indexing;
  • Ref – use non unique index to find data;
  • eq_ Ref – use primary key or unique index association in join query;
  • Const – by placing a primary key behind where as a conditional query, the MySQL optimizer can convert the query optimization into a constant. How and when to convert depends on the optimizer, which is better than Eq_ Ref is a little more efficient.

summary

In this paper, we introduce the principles and classification of MySQL performance optimization. MySQL performance optimization can be divided into active optimization and passive optimization, but no matter what optimization, we should ensure the correctness, security and stability of the service. Its inspiration is that we should adopt the scheme of prevention + passive optimization to ensure the stability of MySQL server. The common problems of passive optimization are:

  • Single SQL runs slowly;
  • Some SQL is running slowly;
  • The whole SQL is running slowly.

Therefore, we give the problem analysis and solutions of each passive optimization scheme. I hope this paper can help you.

Pay attention to the official account “JAVA Chinese community“Get more.

MySQL

Recommended Today

Implementation example of go operation etcd

etcdIt is an open-source, distributed key value pair data storage system, which provides shared configuration, service registration and discovery. This paper mainly introduces the installation and use of etcd. Etcdetcd introduction etcdIt is an open source and highly available distributed key value storage system developed with go language, which can be used to configure sharing […]