Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what are the common reasons for MySQL slow SQL statements". The content is simple and easy to understand and organized. I hope it can help you solve your doubts. Let Xiaobian lead you to study and learn "what are the common reasons for MySQL slow SQL statements".

1. No index, index failure leads to slow query

If you query a column with no index in a table with tens of millions of data, most of the time the query will be very time-consuming, and this query is undoubtedly a slow SQL query. Therefore, for large data queries, it is necessary to establish a suitable index to optimize the query.

Although indexes are often established, in some specific scenarios, indexes may fail, so index failure is also one of the main reasons for slow queries.

2. lock wait

Common storage engines are InnoDB and MyISAM, the former supports row locks and table locks, the latter only supports table locks.

If database operations are implemented based on table locks, imagine that if an order table needs to be locked when updating, a large number of other database operations (including queries) will be in a waiting state, which will seriously affect the concurrency performance of the system.

In this case, row locks supported by the InnoDB storage engine are more suitable for high-concurrency scenarios. However, when using the InnoDB storage engine, pay special attention to the possibility of row locks being upgraded to table locks. During batch update operations, row locks are likely to escalate to table locks.

MySQL believes that if you use a large number of row locks on a table, it will cause transaction execution efficiency to decrease, which may cause other transactions to wait for a long time and more lock conflicts, resulting in serious performance degradation, so MySQL will upgrade row locks to table locks. Also, row locks are index-based locks, and if the conditional index fails during an update operation, row locks are upgraded to table locks.

Therefore, database operations based on table locks cause SQL to block and wait, which affects execution speed. MySQL does not recommend using the MyISAM storage engine when some update operations (insert\update\delete) are greater than or equal to read operations.

In addition to lock escalation, row locks, although more granular and concurrent than table locks, also bring new problems, that is, deadlocks. Therefore, when using row locks, be careful to avoid deadlocks.

3. Inappropriate SQL statements

Using inappropriate SQL statements is also one of the most common causes of slow SQL. For example, custom use, SQL statements, pagination queries in large data tables, sorting non-indexed fields, and so on.

The above is "MySQL slow SQL statement common reason is what" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

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.