Shulou(Shulou.com)06/02 Report--

This article introduces the knowledge of "what is the method of MySQL positioning and optimizing slow query sql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1. How to locate and optimize slow query sql a. Locate slow query sql based on slow log

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

Slow_query_log is turned off by off by default. When you use it, it needs to be turned on by on.

Slow_query_log_file records slow log files.

Long_query_time defaults to 10s. Every time the sql is executed for this length of time, it will be recorded.

SHOW STATUS LIKE'% slow_queries%' to view slow query status

Slow_queries records the number of slow queries. When a sql executes slowly, the vlue is 1 (records the number of slow sql entries in this session).

Note:

How to open a slow query: SET GLOBAL slow_query_log = ON

Change the default time to 1s: SET GLOBAL long_query_time = 1

(after setting up, you need to reconnect to the database, PS: if you only change it here, when you restart the database service again, all settings will automatically return to the default values, and permanent changes need to be changed 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

Then look at the key field of explain.

Type:

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

Extra:

c. Modify sql or let sql follow the index as much as possible

The mysql query optimizer determines which index to take according to the specific situation, not necessarily the primary key (the key in explain can see which key to go). The specific situation depends on the specific situation, when you want to enforce a certain key:

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

two。 The cause of formation of the leftmost matching principle of federated index simply state what is the leftmost matching principle

As the name implies: the leftmost priority, with the leftmost as the starting point, any consecutive index can match. At the same time, encounter a range query (>, 3 and d = 4). If you build an index in the order of (a recordbpcec), d does not need an index, because the c field is a range query, and the fields after it will stop matching.

The principle of leftmost matching principle

The leftmost matching principle is for federated indexes, so it is necessary to understand the principle of federated indexes. Now that you understand the federated index, you can understand why there is a leftmost matching principle.

We all know that the bottom of the index is a B+ tree, then the federated index is of course a B+ tree, but the number of healthy values of the federated index is not one, but multiple. A B+ tree can only be built based on one value, so the database builds the B+ tree based on the leftmost field of the federated index.

Example: if you create a federated index (aforme b), then its index tree looks like this

As you can see, the values of an are in order, 1magin 1pint 2jorgle 2pje 3, while b is out of order 1pas 2pas 1pas 4pas 1pas 2. So the query condition b = 2 cannot take advantage of the index, because the federated index is first sorted by an and b is unordered.

At the same time, we can also find that when a values are equal, b values are arranged in order, but this order is relative. So the leftmost matching principle stops when it encounters a range query, and the remaining fields cannot use the index. For example, an index can be used in the a = 1and bread2areb field, because b is relatively ordered when the a value is determined, and the a > 1and bread2jigma a field can match the upper index, but the b value cannot, because the value of an is a range in which b is unordered.

Causes:

When searching through a joint index such as (col3,col2), you can see that it is also a B+ tree structure to look down. If you search directly through col2, you can not find 34 or 77 directly. You don't need this joint index.

3. Is it better to build as many indexes as possible?

1. Tables with a small amount of data do not need to be indexed, which will increase the extra index overhead.

two。 Data changes require maintenance of indexes, so more indexes mean more maintenance costs.

3. More indexes means more space is also needed.

This is the end of the content of "what is the method for MySQL to locate and optimize slow query sql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.