The cause of slow SQL statement execution is a common question in interviews and a must for server-side development.

In a production environment, SLOW SQL execution is a serious event. So how to locate slow SQL, slow cause and how to prevent it. Let’s start this tour with these questions!

– Mind mapping –

The write operation

As back-end development, the most commonly used daily operation databases are write and read operations. Read operations will be covered below, but in this category we’ll focus on how write operations can slow DOWN SQL.

Brush the dirty pages

The definition of a dirty page is as follows: when the in-memory data page and the disk data page are inconsistent, the in-memory data page is called dirty.

So why do dirty pages appear, and how do dirty pages cause SQL to slow down? That requires us to take a look at what the flow looks like at write time.

For A SQL write operation, the execution involves logging, memory, and disk synchronization.

– Mysql architecture –

One log file mentioned here is the redo log, which is located in the storage engine layer and is used to store physical logs. At write time, the storage engine (in this case Innodb) writes records to the redo log and updates the cache, so the update is complete. Subsequent operations The storage engine synchronizes the operation records to disk at an appropriate time.

Redo logs are stored on disk. Aren’t they slow to write?

In fact, the redo log is written sequentially to disk. Sequentially writes reduce seek time and are much faster than random writes (similar to the Kafka storage principle), so redo logs are fast.

Okay, let’s go back to the original question of why dirty pages occur and why they slow down SQL. If you think about it, redo logs are of a fixed size and are written in cycles. In high concurrency scenarios, redo logs fill up quickly, but data is not synchronized to disk, resulting in dirty pages and blocking subsequent writes. SQL execution is naturally slow.

The lock

Another case where SQL is slow while writing is when you might encounter a lock, which is easy to understand. For example, if you share a room with someone with only one bathroom, you both want to go at the same time, but the other person beat you to it. Then you can only go in after the other person comes out.

In Mysql, when a row that you want to change happens to be locked, you can only perform subsequent operations after the lock is released.

At the other extreme, your roommate is always using the bathroom. What should you do? You should not pee your pants. In Mysql, deadlock or lock wait is encountered. How to deal with it?

Mysql provides a way to check the current lock status:

You can view the current transaction status by executing the statement in the command line diagram. Here are some important parameters in the query result:

If the transaction waits for a long time or deadlocks occur, you can kill the thread ID to release the current lock.

The thread ID here refers to the trx_mysQL_thread_id parameter in the table.

A read operation

Now, write operations, read operations you’re probably more familiar with. The problem of slow SQL read operation is often involved in work.

The slow query

Before we look at why the read operation is slow, let’s look at how to locate slow SQL. Mysql has something called a slow query log, which is used to record SQL statements that take longer than a specified time. This function is disabled by default. You can manually enable slow log query for location.

The specific configuration is as follows:

Check whether the current slow query log is enabled:

Enable slow Log Query (temporary) :

Note that slow query logging is enabled temporarily. If mysql restarts, it will be disabled. You can configure it in my.cnf to make it permanent.

There is reason

Now that you know how to look at slow-executing SQL, let’s look at why read operations cause slow queries.

(1) The index is not matched

One of the reasons why SQL queries are slow is that they may not hit the index. There are already a lot of notes on the web about why indexes can make queries faster and why they should be used.

(2) Dirty pages

The other is the dirty page brush we mentioned above, but unlike the write operation, is the dirty page brush while reading.

Don’t worry, don’t worry, listen to me:

To avoid the IO overhead of accessing disk every time data is read or written, Innodb storage engine loads the corresponding data pages and index pages into the buffer pool of memory to improve read/write speed. The cached data in the buffer pool is then retained according to the least recently used rule.

So when the data page to be read is not in memory, we need to apply for a data page in the buffer pool, but the data page in the buffer pool is certain, when the data page reaches the maximum, we need to remove the data page that is not used for the longest time from the memory. But if you’re weeding out dirty pages, you need to brush them to disk to reuse them.

You see, it’s back to the dirty page, you can understand the slow reading operation?

Nip it in the bud

Knowing the cause, how can we avoid or mitigate this situation?

First let’s look at the missed index:

I don’t know if you have the habit of using explain in Mysql, but I will use it every time to check the current SQL hit index situation. Avoid some unknown pitfalls.

To analyze the current SQL execution plan, add explain before the SQL execution:

The fields corresponding to the results after execution are described as follows:

Focus on the following fields:

1, the type,

Represents the way MySQL finds the desired rows in the table. The commonly used types are: ALL, index, range, ref, eq_ref, const, system, NULL these types from left to right, performance gradually improved.

ALL: Mysql traverses the entire table to find matching rows;
Index: only traverses the index tree, which is different from ALL.
Range: retrieves only rows in a given range, using an index to select rows;
Ref: indicates the join matching conditions of the above table, which columns or constants are used to find values on index columns;
Eq_ref: similar to ref, except that a unique index is used. For each index key value, only one record in the table matches.
Const, system: These types of access are used when Mysql optimizes part of the query and converts it to a constant. If you place the primary key in a WHERE list, Mysql can convert the query to a constant. System is a special case of const type.
NULL: Mysql breaks down statements during optimization without even accessing the table or index. For example, selecting a minimum value from an index column can be done through a separate index lookup.

2, possible_keys

The index that may be used in the query (but may not be used, NULL if there are no indexes).

3, the key

The actual index used.

4, rows

Estimate the number of rows needed to find the corresponding record.

5, Extra

More common are the following:

Useing Index: Indicates that an overwrite index is used and there is no need to back up the table.
Using WHERE: the mysql server will retrieve rows from the storage engine before filtering them out. The mysql server will retrieve rows from the storage engine before filtering them out.
Using temporary: indicates that MySQL needs to use a temporary table to store the result set. This is common in sort and group queries, such as group by and order by.
Using filesort: when a Query contains an order by operation that cannot be done Using an index, the sort operation is called “filesort”.

In the case of dirty pages, we need to control the percentage of dirty pages and not let it approach 75% too often. You should also control the redo log write speed and tell InnoDB your disk capacity by setting innodb_io_capacity.

conclusion