Common causes and solutions for slow MySQL SQL statements

1. Slow query due to lack of index or invalid index

If you use a non-indexed column as a query condition in a table with tens of millions of data, the query will be very time-consuming in most cases. This query is undoubtedly a slow SQL query. Therefore, for queries with large amounts of data, it is necessary to create appropriate indexes to optimize the queries.

Although indexes are often created, they may still fail in certain scenarios, so index failure is also one of the main causes of slow queries.

2. Lock Wait

Commonly used storage engines include InnoDB and MyISAM. The former supports row locks and table locks, while 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 it is updated, then 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.

At this time, the row lock supported by the InnoDB storage engine is more suitable for high concurrency scenarios. However, when using the InnoDB storage engine, special attention should be paid to the possibility of row locks being upgraded to table locks. During batch update operations, row locks are likely to be upgraded to table locks.

MySQL believes that if a large number of row locks are used on a table, the efficiency of transaction execution will decrease, which may cause other transactions to wait for a long time and more lock conflicts, resulting in serious performance degradation. Therefore, MySQL will upgrade row locks to table locks. In addition, row locks are locks based on indexes. If the conditional index fails during an update operation, the row lock will be upgraded to a table lock.

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

In addition to lock upgrades, row locks have finer granularity and improved concurrency capabilities compared to table locks, but they also bring new problems, namely 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, you are accustomed to using <SELECT *>, <SELECT COUNT(*)> SQL statements, using <LIMIT M,N> paging queries in large data tables, and sorting non-indexed fields.

The above is all the knowledge points introduced this time. Thank you for your support of 123WORDPRESS.COM.