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

This article mainly explains "what are the reasons for the slow SQL". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the reasons for the slow SQL?"

-mind map-

Write operation

As a back-end development, write and read operations are most commonly used in daily operations of the database. Read operations we will talk about below, in this category we will mainly look at why write operations cause SQL to slow down.

Brush the dirty page

A dirty page is defined as this: when a memory data page is inconsistent with a disk data page, the memory data page is called a dirty page.

So why do dirty pages appear, and how can brushing dirty pages cause SQL to slow down? Then we need to see what the process looks like when writing.

For a SQL with a write operation, the execution process involves writing logs, memory and synchronizing disks.

-Mysql architecture diagram-

A log file is mentioned here, which is called redo log, which is located in the storage engine layer and is used to store physical logs. During the write operation, the storage engine (Innodb discussed here) writes the record to the redo log and updates the cache so that the update operation is complete. The subsequent operation storage engine will synchronize the operation records to disk at the appropriate time.

If you see here, you may have a question: isn't redo log a log file, which is stored on disk? isn't it very slow to write?

In fact, the process of writing redo log is sequential disk writing, disk sequential writing reduces seek time, and the speed is much faster than random writing (similar to Kafka storage principle), so writing redo log is very fast.

All right, let's go back to the initial question of why dirty pages appear and why dirty pages slow down SQL. If you think about it, the redo log size is fixed and is written in a loop. In high concurrency scenarios, redo log is quickly full, but before the data is synchronized to disk, dirty pages will be generated and subsequent writes will be blocked. SQL execution will naturally slow down.

Lock

Another situation where SQL is slow when writing is that you may encounter a lock, which is easy to understand. For example, if you share a room with someone else and have only one bathroom, you both want to go at the same time, but the other person loses it before you do. Then you can only go in after the other person comes out.

Corresponding to the Mysql, when the row to be changed by a SQL happens to be locked, then the subsequent operation can only be done after the lock is released.

But there is also an extreme situation, your roommate has been occupying the bathroom, so what you should do at this time, you can not pee your pants, how humiliating. Corresponding to the Mysql is encountered deadlock or lock waiting situation. What should be done at this time?

Mysql provides a way to view the current lock situation:

You can view the currently running transactions by executing the statements in the diagram on the command line. Here are several important parameters in the query results:

If the current transaction waits too long or has a deadlock, you can release the current lock by "kill thread ID".

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

Read operation

After talking about the write operation, you may be relatively familiar with the read operation. The problem that slow SQL leads to slow read operations is often involved at work.

Slow query

Before we talk about the reasons for the slow operation, let's take a look at how to locate the slow SQL. There is something called the slow query log in Mysql, which is used to record SQL statements that exceed a specified time. It is off by default, and the slow query log can be enabled for positioning only through manual configuration.

The specific configuration is as follows:

Check whether the current slow query log is enabled:

Enable slow query log (temporary):

Note that the slow query log is only temporarily enabled. If mysql is restarted, it will become invalid. It can be configured in my.cnf to make it permanent.

Reason of existence

Now that you know how to view slow-executing SQL, let's move on to see why read operations lead to slow queries.

(1) missed index

One of the reasons why SQL query is slow is that it may miss the index. There are many things on the Internet about why using the index can make the query faster and what to pay attention to when using it, so I won't dwell on it here.

(2) dirty page problem

The other is the case of scrubbing the dirty page mentioned above, but unlike the writing operation, the dirty page is scrubbed while reading.

Are you a little confused? don't worry, listen to me:

In order to avoid the IO overhead of accessing the disk every time reading and writing data, the Innodb storage engine increases the read and write speed by loading the corresponding data pages and index pages into the buffer pool (buffer pool) of memory. The cached data in the buffer pool is then retained in accordance with the principle of least recent use.

Then when the data page to be read is not in memory, you 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 upper limit, the longest unused data page needs to be eliminated from memory. But if the dirty pages are eliminated, then the dirty pages need to be brushed to disk before they can be reused.

You see, back to the situation of brushing dirty pages, you can understand that the reading operation is slower, right?

Take precautions against trouble.

Knowing the reason, how can we avoid or alleviate this situation?

First, let's take a look at what happens when the index is missed:

I don't know if you are in the habit of using explain in Mysql, but I always use it to check the current SQL hit index. To avoid some unknown hidden dangers.

Here is a brief introduction to how to use it. You can analyze the current SQL execution plan by adding explain before the SQL being executed:

A summary description of the fields corresponding to the executed result is shown below:

Here you need to focus on the following fields:

1 、 type

Represents how MySQL finds the required rows in the table. The commonly used types are: ALL, index, range, ref, eq_ref, const, system, NULL, these types from left to right, the performance is getting better.

ALL:Mysql traverses the entire table to find matching rows

Index: differs from ALL in that index only traverses the index tree

Range: only retrieve a given range of rows, using an index to select rows

Ref: indicates the join matching condition of the above table, and which columns or constants are used to find the value on the index column

Eq_ref: similar to ref, the difference is whether you are using a unique index. For each index key value, only one record in the table matches. To put it simply, primary key or unique key is used as the association condition in multi-table joins.

Const, system: use these types of access when Mysql optimizes some part of the query and converts it to a constant. If you put the primary key in the where list, Mysql can convert the query to a constant. System is a special case of const type. Use system when the query table has only one row.

NULL:Mysql breaks up statements during optimization and executes without even accessing tables or indexes. 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 not necessarily, displayed as NULL when there is no index).

3 、 key

The index actually used.

4 、 rows

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

5 、 Extra

The more common ones are the following:

Useing index: indicates that an overlay index is used without going back to the table

Using where: instead of reading all the information in the table, you can get the required data only through the index. This occurs when all the request columns of the table are part of the same index, indicating that the mysql server will filter the rows after the storage engine has retrieved them.

Using temporary: indicates that MySQL needs to use temporary tables to store result sets, commonly used in sorting and grouping queries, and common group by,order by

Using filesort: when the Query contains order by operations, and the sort operation that cannot be done with the index is called "file sorting".

For the case of dirty pages, we need to control the proportion of dirty pages, so that it is not often close to 75%. Also control the write speed of redo log and tell InnoDB your disk capacity by setting the innodb_io_capacity parameter.

Summary

Write operation

When the redo log is full, the dirty page will be scrubbed, and the write operation will be terminated, so the SQL execution will naturally become slower.

When you encounter a lock on the data row or table you want to modify, you need to wait for the lock to be released before you can perform subsequent operations, and SQL execution will be slow.

Read operation

A common reason for slow read operations is that the index is missed, resulting in a full table scan, and the SQL statement can be analyzed by explain.

Another reason is that during the read operation, the data page to be read is not in memory, and you need to eliminate dirty pages in order to apply for a new data page, resulting in slower execution.

At this point, I believe you have a deeper understanding of "what are the reasons for the slow SQL?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.