Wechat public account [Huanshao’s Growth Path]

Today I would like to introduce a line of data, why slow to panic. Analyze the bottom running process of MySQL!

case

Log on to check

I don’t know if this has ever happened to you. When an APP client logs in to the user, it clearly only checks one piece of data. Why is it so slow? How do you optimize this kind of problem? How do you solve these problems? Let’s analyze it!

Let’s solve some preliminary questions before we analyze. SQL > select * from table_name; select * from table_name; So why are there three rows here?

explain SELECT * FROM `t_vip` where vipIphone='18360520588'
Copy the code

Extract the where

Line 3 is because of where. Introduce the SQL behind WHERE.

Index Key

The query condition that determines the continuous range (start range + end range) of an SQL query in an Index is called an Index Key. Since a range contains at least one start and one end, the Index Key is also split into the Index First Key and Index Last Key, which are used to locate the start of the Index search and the end condition of the Index query, respectively. In Key Length.

Index First Key

Used to determine the starting range of index queries.

Extraction rule: Starting with the first key value of the index, check whether it exists in the WHERE condition.

1) If there is and the condition is =, >=, add the corresponding condition to Index First Key, continue to read the next Key value of the Index, using the same extraction rule;

2) If it exists and the condition is >, add the corresponding condition to Index First Key and terminate Index First Key extraction;

3) If there is no Index First Key, the extraction of Index First Key is also terminated.

Index First Key (b >= 2, c > 1); As the condition of C is >, the extraction is finished, excluding D.

Index Last Key

The Index Last Key is the opposite of the Index First Key and is used to determine the termination range of Index queries.

Extraction rule: Starting with the first key value of the index, check whether it exists in the WHERE condition.

1) If there is and the condition is =, <=, add the corresponding condition to the Index Last Key, continue to extract the next Key value of the Index, using the same extraction rule;

2) If it exists and the condition is <, add the condition to Index Last Key and terminate extraction;

3) If there is no Index Last Key, the extraction of Index Last Key is also terminated.

Index Last Key (b < 8); Index Last Key (b < 8);

Index Filter

After the Index Key was extracted, we fixed the query range of the Index according to the WHERE condition, but not all items in the range met the query conditions. In the SQL use case above, (3,1,1), (6,4,4) are in the scope, but do not meet the SQL query conditions

The extraction rule of Index Filter: Again, starting from the first column of the Index column, check whether it exists in the WHERE condition

1) If exists and where condition is only =, skip the first column and continue to check the next column of the index. The next column adopts the same extraction rule as the first column of the index;

2) If the where condition is >=, >, <, <=, then skip the first column of the Index and add all the other relevant columns of the Index Filter to the Index Filter.

3) If the first column of the Index contains where conditions other than =, >=, >, <, <=, add the Index related columns in this condition and other WHERE conditions to the Index Filter;

Table Filter

Table Filter is the simplest, most understandable and most convenient to extract. Extraction rule: All query criteria that do not belong to the index column are included in Table Filter.

Similarly, for the SQL use case above, the Table Filter is e! = ‘a’.

summary

The WHERE conditions in SQL statements are extracted from the Index Key (First Key & Last Key), Index Filter, and Table Filter according to the preceding extraction rules.

The Index First Key is only used to locate the initial range of the Index, so it is only used when the Index searches the Path for the First time (traversing the root node of the Index B+ tree to the correct leaf node of the Index).

Index Last Key is used to locate the end range of the Index. Therefore, for each Index record read after the start range, check whether the Index record exceeds the end range of the Index Last Key. If the Index record exceeds the end range, the current query ends.

Index Filter is used to Filter the records that do not meet the search conditions in the Index query range. Therefore, you need to compare each record in the Index range with Index Filter. If the record does not meet the search conditions, the system discards the record and continues to read the next record in the Index.

Table Filter is the Last line of defense for where conditions. It is used to Filter the records that pass the previous Index tests. At this point, the records meet the range of Index First Key and Index Last Key and meet the conditions of Index Filter. The Table reads the complete record and determines whether the complete record meets the query conditions in Table Filter. Similarly, if the complete record does not meet the query conditions, the current record is skipped and the next record of the index is read. If so, the record is returned.

Before MySQL 5.6, Index Filter and Table Filter are not distinguished. All Index records in the range of Index First Key and Index Last Key are read back to the Table, and then returned to MySQL Server layer for filtering.

After MySQL 5.6, Index Filter is separated from Table Filter, Index Filter is filtered down to the Index level of InnoDB, which reduces the record interaction cost of returning to Table and returning to MySQL Server layer, and improves the execution efficiency of SQL

Back to the case

After introducing the extraction rules for roughly WHERE, let’s go back to the SQL of the case for validation.

At a glance, you can see that the WHERE condition uses the vipIphone field. According to the extraction rules. Hit index first key.

Long query time

If an SQL query takes too long, something is definitely wrong. When we execute explain, we also hit the index. The execution time is also quite normal in the tens of milliseconds. Then why are they sometimes so slow?

In general, the first case is that the table is locked. Execute the following SQL statement to see what state the current statement is in. Then, for each state, we analyze why it happened, how it can reproduce, and how to deal with it.

show processlist
Copy the code

Such as MDL lock

Returns the resultWaiting for table metadata lock. This state indicates that a thread is now requesting or holding an MDL write lock on the table, blocking the SELECT statement. Now we reproduce the following

Create a write lock on t. SessionB is prepared to perform a read operation on table T. An MDL read lock is required to read a table. This is to prevent dirty reading. So we found the lock wait phenomenon.

The only solution is to release the write lock. Kill the write lock by using the kill command. This will restore the read lock.

Such as flush

Waiting for table flush Two instructions on flush are introduced based on this return

flush tables t with read lock;   -- flush table t

flush tables with read lock;  -- Flush all tables
Copy the code

This result indicates that the internal mechanism of MySQL flusher the data page at the time of query. However, both statements are fast and should not block the query for that long. The only possibility is that a modified statement is blocked by flush. The query is blocked by modification again. Now let’s reproduce the process.

  • SessionA starts a sleep operation on the table. The parameter is in seconds. Because this is a full table scan. There’s 100,000 data in the table. So about 100,000 seconds of sleep
  • SessionB starts a flush operation in table B. Close table T before flush. Table T sleeps in the sessionA. The session is blocked by sessionA.
  • SessionC starts the query operation. SessionB is blocked while session sessionA is running. SessionC is blocked by sessionB.

Such as row locks

The MDL read-write lock was described above. Here are the scenarios that have been introduced. Row locks are now being processed.

Execute the following SQL, I first said that I had learned the doubt. Lock in share mode Details are in the extension

select * from t where id=1 lock in share mode; 
Copy the code

Since a read lock is required to access the data with id=1, our SELECT statement will be blocked if a write lock is already held on the record. Let’s reproduce the process.

SessionA starts a transaction that has not been committed. SessionB starts the query and reads the lock. Session B is blocked.

extension

Lock in share mode lock in share mode

For UPDATE: If a transaction locks data exclusively, no other transaction can lock that data. A transaction that acquires an exclusive lock can both read and modify data.

Lock in share mode: if a transaction locks a row, it can read and write data. Other transactions can hold a shared lock on the data, but cannot hold an exclusive lock, and can only read the data, but cannot modify the data. If one object wants to modify data, it must wait for all other objects’ shared locks to be released before it can modify data

Note: Normal SELECT statements do not have locks by default, while CUD operations have exclusive locks by default.

Slow query

Let me start with an example

mysql> select * from t where c=50000 limit 1;
Copy the code

Since there is no index on field C, this statement can only scan the id primary key sequentially, so 50,000 rows are scanned. It took about 11.5 milliseconds. You may say, not very slow ah, 11.5 milliseconds returned, our online configuration is generally more than 1 second is considered slow query. But remember: bad queries are not necessarily slow queries. In our example, there are only 100,000 rows, and the execution time increases linearly as the data volume increases.

Let’s look at a very large example of scanning just one row of data.

select * from t where id=1;
Copy the code

Isn’t that a little weird? We can look up the show log

Let’s look at another picture for comparison

The second diagram is the one that needs to be locked. It should have been longer. The above two pictures are made with the help of Mr. Dinky. The article is a summary of the learning extension.

Let’s repeat the above results.

  • SessionA starts a transaction and queriesid=1This record, and queryid=1 And add read lock query.
  • SessionB performs updates after sessionA starts a transaction

Let me interpret that. The two queries in sessionA are different. The first is a consistent read and the second is a current read. So the second one is very fast, directly through the B+ tree to find the corresponding node data. The first internal process is 1000001, and the undo log is executed one million times before 1 is returned.

conclusion

There are several common reasons why queries are slow. The index key mechanism behind the WHERE condition was introduced at the beginning. If there is no understanding of the place can be wechat attention [huan little growth path] let’s talk about it together.

Some do not understand the place, you can leave a comment