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

Today, I will talk to you about how to optimize slow queries in Mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

1. Open the slow query log of Mysql:

Method 1: modify my.ini

/ / A query that defines the number of seconds is a slow query. Here, set the 2-second long_query_time=2//5.5 and above to configure the following slow-query-log=onslow_query_log_file= "mysql_slow_query.log" / / record the querylog-query-not-using-indexes that does not use the index.

Method 2: open the console

Set global slow_query_lon=ONset global long_query_time=3600set global log_querise_not_using_indexes= on II. Use EXPLAIN to analyze sql slow query statements

The index is used in the image above, but not in the image below.

Type from good to bad is: system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL

Const: when using a unique index or primary key, the return record must be the equivalent where condition of a row of records, the type is usually const. Other databases are also called unique index scans

Eq_ref: when it appears in a query plan to join a table, the driven table returns only one row of data, which is the primary key or unique index of the second table, and must be not null. When the unique index and primary key are multiple columns, eq_ref will appear only if all columns are used for comparison.

Ref: unlike eq_ref, which requires join order, and no primary key and unique index, it may occur as long as it is retrieved using equality conditions, and it is common for equivalent lookups to be used with secondary indexes. Or in a multi-column primary key or unique index, using a column other than the first column as an equivalent lookup may also appear. In short, an equivalent lookup that returns data that is not unique may occur.

Range: index range scan, commonly used >, = 23423 limit 11; (10 items per page) Select * from table WHERE id > = 23434 limit 11 select * from table WHERE id > = (select id from table limit 10000lt 1) limit 10x select * from table INNER JOIN (SELECT id from table limit 10000lm10) USING (id) program takes ID: Select id from table limit 10000Magne10 select * from table WHERE ID in (123456 …)

Paging mode 4:

Paging method 3:

Paging method 2:

Paging method 1:

SELECT * FROM pre_forum_post ORDER BY pid ASC LIMIT 7332000,1000

Select a. * from pre_forum_post a, (select tid,position from pre_forum_post ORDER BY pid ASC LIMIT 7332000 Magi 1000) b where a.tid=b.tid and a.position=b.position

Https://blog.csdn.net/qq_35513598/article/details/79813098

Https://blog.csdn.net/qq_35571554/article/details/82800463

Optimize in query

An In query:

Select * from a where id in (select id from b)

He is equivalent to:

Select * from a where exists (select * from b where b.id=a.id)

The execution principle of exists related subquery is that every record in table an is compared with table b in a loop, and the condition for comparison is a.id=b.id. See if the id of each record in table an exists in table b, and if so, return the record in table a.

From the exists implementation principle, table a (appearance) can not use the index, must be full table scan, because it is to take the data of table a to table b to check. And you have to use the data from table a to look it up in table b (from the outside to the inside), and the order is fixed.

To optimize, you can only build an index on b, because the index mysql on table an is not available.

Further optimize to change the query to an inner join join query:

Select * from an inner join b on a.id=b.id

Why not left join and right join? At this time, the order of the join between the tables is fixed, for example, the left join must first check the left table to scan the whole table, and then one by one to another appearance to query, the right connection is the same. It's still not the best option. On the other hand, the order of inner join is decided by mysql itself, and the best sequence is chosen finally.

After reading the above, do you have any further understanding of how to optimize slow queries in Mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.