In the article “MySQL common statement locking analysis”, we explained the locking principle of SQL statements in detail and analyzed most simple SQL statements in detail. However, SQL statements in actual business scenarios are often extremely complex and contain multiple conditions. At this time, it is necessary to specifically analyze the index used by SQL. And understand the logic of where conditions.

We can use explain or Optimizer_Trace directly to analyze which indexes are used in SQL statement execution, as described in the first two articles in this series. Today, however, we’ll look at the breakdown and use of conditions for specific Where statements, that is, how complex Where conditions work.

In the words of The Great God He Dengcheng, it is

Given an SQL query, what role does each sub-condition in a WHERE condition play in the execution of the SQL query?

The scenario

We use the following book table as an example, where id is the primary key, ISBN is the secondary unique index, Author is the secondary non-unique index, and score has no index.

Index Key and Table Filter

Based on the above table, let’s examine the following SQL statement with complex Where conditions.

mysql> UPDATE book SET score = 9.0 WHERE Author = 'Tom' AND ISBN > 'N0004' AND ISBN < 'N0007';
Copy the code

The Where condition in the SQL statement above uses two indexes, the secondary unique index ISBN and the secondary non-unique index Author. MySQL selects an Index based on indexes such as Index selectivity, and another Where condition that is not used is used as a common Filter condition. Generally, the used Index is called an Index Key, and the common Filter condition is called a Table Filter. For example, if the above SQL query uses the ISBN Index, then ISBN is the Index Key and Author = ‘Tom’ is the Table Filter.

Therefore, the process of this SQL execution is to read the Index records in the Index Key range successively, then read the complete data records back to the Table, and then return to the MySQL service layer for filtering according to the Table Filter. As for locking, as shown in the following figure, Index records corresponding to the Index Key involved need to be locked.

However, when composite indexes are used, Index Filter can also be used to reduce the number of table returns and the number of records returned to the MySQL service layer, reduce the interaction overhead between the storage engine and the service layer, and improve the efficiency of SQL execution.

Index Filter

Suppose we have a joint index on the ISBN and Author columns of the Book table, and the compound index is selected for the above SQL execution.

For this scenario, MySQL still uses ISBN > ‘N0004’ AND ISBN < ‘N0007’ to determine the sequential position of the SQL query in the index, but Author = ‘Tom’ can be used to filter the index directly. That is, the condition can be directly filtered by using compound indexes instead of the MySQL service layer filtering the Table Filter after reading all data. This is the legendary Index Condition Pushdown (ICP) technology, which uses Index Filter to Filter records that do not meet the criteria without locking.

After judging the query results based on the Index Key and performing preliminary filtering based on the Index Filter, the storage engine returns the remaining data records to the service layer for filtering based on the Table Filter.

ICP (index push down) technology

The ICP technology introduced by MySQL 5.6 is actually the Index Filter technology, only because MySQL is divided into service layer and storage engine layer, and Index Filter “pushes down” filtering operations originally done by the service layer to the storage engine layer. The parts that can be processed by Index Filter in the original Table Filter at the service layer are processed by Index Filter at the engine level, without the need for Table Filter.

The advantage of this is to reduce the number of locked records, reduce the number of back table queries, and improve the efficiency of SQL execution.

This is the last article in the series. Next, we’ll show you how to determine deadlocks and deadlocks based on MySQL information. Please pay attention to it, forward it and like it.

Personal blog, welcome to play