Detailed example of locating and optimizing slow query sql in MySQL

1. How to locate and optimize slow query SQL

a. Locate slow query SQL based on slow logs

SHOW VARIABLES LIKE '%query%' to query slow log related information

slow_query_log is off by default. When using it, you need to turn it on.

slow_query_log_file records the slow log file

The default value of long_query_time is 10S. Every time the SQL statement executed reaches this time, it will be recorded.

SHOW STATUS LIKE '%slow_queries%' to view the status of slow queries

Slow_queries records the number of slow queries. When a SQL statement is executed slowly, this value is 1 (recording the number of slow SQL statements in this session).

Notice:

How to turn on slow query: SET GLOBAL slow_query_log = ON;

Change the default time to 1S: SET GLOBAL long_query_time = 1;

(You need to reconnect to the database after setting. PS: If you only change it here, when you restart the database service again, all settings will automatically return to the default values. For permanent changes, you need to change it in my.ini)

b. Use tools such as explain to analyze SQL

Add explain before the SQL to be executed. For example: EXPLAIN SELECT menu_name FROM t_sys_menu ORDER BY menu_id DESC;

Next, look at the key fields of explain

type:

If the value of type is found to be one of the last two, the proof statement needs to be optimized.

extra:

c. Modify SQL or try to make SQL go through the index

The MySQL query optimizer will determine which index to use based on the specific situation. It does not necessarily use the primary key (the key in explain can show which key is used). The specific situation depends on the specific situation. When you want to force a certain key to be used:

Add force index(primary) at the end of the query to force the primary key.

2. The cause of the leftmost matching principle of the joint index

Briefly explain what is the leftmost matching principle

As the name implies: leftmost first, any consecutive index starting from the leftmost can be matched. At the same time, if a range query (>, <, between, like) is encountered, the matching will stop.

For example: b = 2. If you create an index in the order of (a, b), it will not match the (a, b) index. However, if the query condition is a = 1 and b = 2 or a = 1 (or b = 2 and b = 1), it will work because the optimizer will automatically adjust the order of a and b. For example, if a = 1 and b = 2 and c > 3 and d = 4 and an index is created in the order of (a, b, c, d), d will not be indexed because the c field is a range query and the fields after it will stop matching.

The principle of the leftmost matching principle

The leftmost matching principle is for joint indexes, so it is necessary for us to understand the principle of joint indexes. After understanding the joint index, you will understand why there is the leftmost matching principle.

We all know that the underlying layer of the index is a B+ tree, so the joint index is of course still a B+ tree, but the number of key values ​​in the joint index is not one, but multiple. A B+ tree can only be constructed based on one value, so the database constructs the B+ tree based on the leftmost field of the joint index.

Example: If you create a joint index (a, b), then its index tree is like this:

You can see that the values ​​of a are in order: 1, 1, 2, 2, 3, 3, while the values ​​of b are not in order: 1, 2, 1, 4, 1, 2. Therefore, the query condition b = 2 cannot use the index because the joint index is first sorted by a, and b is unordered.

At the same time, we can also find that when the a values ​​are equal, the b values ​​are arranged in order, but this order is relative. Therefore, the leftmost matching principle will stop when encountering a range query, and the remaining fields cannot use indexes. For example, if a = 1 and b = 2, both a and b fields can use the index because b is relatively ordered when the a value is determined. If a>1and b=2, the a field can match the index, but the b value cannot, because the a value is a range and b is unordered within this range.

Cause:

When searching through a joint index like (col3, col2), you can see that it is also a B+ tree structure that searches downward. If you search directly through col2, you cannot directly find 34 and 77. This joint index is no longer needed.

3. Is it better to create more indexes?

1. Tables with small amounts of data do not need to be indexed, as indexing will increase additional index overhead.

2. Data changes require index maintenance, so more indexes mean more maintenance costs.

3. More indexes mean more space is required.

Summarize

This is the end of this article about locating and optimizing MySQL slow query sql. For more relevant MySQL locating and optimizing slow query sql content, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!