Common Inducements and Solutions of MySQL Slow SQL Statements

1. No index, index failure leads to slow query

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

Although indexes are often built, indexes may fail in 1 specific scenario, so index failure is also one of the main reasons for slow query.

2. Lock wait

Commonly used storage engines are InnoDB and MyISAM. The former supports row locking and table locking, while the latter only supports table locking.

If database operations are based on table locking, imagine that if an order table needs to lock the whole table when it is updated, 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, the row lock supported by InnoDB storage engine is more suitable for high concurrency scenarios. However, when using the InnoDB storage engine, pay special attention to the possibility of escalating row locks to table locks. During batch update operations, row locks are likely to be upgraded to table locks.

MySQL thinks that if a large number of row locks are used on a table, the transaction execution efficiency will decrease, which may cause long lock waiting and more lock conflicts in other transactions, 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 the update operation, row locks will also be upgraded to table locks.

Therefore, database operations based on table locks can cause SQL blocking waits, which can affect execution speed. MyISAM storage engine is not recommended for MySQL when 1 update operation (insert\ update\ delete) is greater than or equal to read operation.

In addition to lock escalation, compared with table locks, row locks have finer granularity and improved concurrency, but they also bring new problems, that is, deadlocks. Therefore, when using row locks, care should be taken to avoid deadlocks.

3. Inappropriate SQL statement

Using inappropriate SQL statements is also one of the most common incentives for slow SQL. For example, it is customary to use < SELECT * > , < SELECT COUNT(*) > SQL statement, used in large data tables < LIMIT M,N > Paging queries, sorting non-indexed fields, and so on.

The above is all the knowledge points introduced this time. Thank you for your support to this site.