MySQL Covering Index Scan Actually Reads Primary Index

1. Preparation

Create a test table:

CREATE TABLE `t7` (  
  `id` int(10) unsigned NOT NULL,  
  `i1` int(11) DEFAULT '0',  
  `i2` int(11) DEFAULT '0',  
  `i3` int(11) DEFAULT '0',  
  `i4` int(11) DEFAULT '0',  
  `i5` int(11) DEFAULT '0',  
  `i6` int(11) DEFAULT '0',  
  PRIMARY KEY (`id`),  
  KEY `idx_i1_i2_i3` (`i1`,`i2`,`i3`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  

‍

Insert test data:

INSERT INTO `t7` VALUES  
(1,101,102,103,104,105,106),  
(2,201,202,203,204,205,206),  
(3,301,302,303,304,305,306),  
(4,401,402,403,404,405,406),  
(5,501,502,503,504,505,506),  
(6,601,602,603,604,605,606),  
(7,701,702,703,704,705,706),  
(8,801,802,803,804,805,806),  
(9,901,902,903,904,905,906),  
(10,150,151,152,153,154,155),  
(11,250,251,252,253,254,255),  
(12,180,181,182,183,184,185),  
(13,200,201,202,203,204,205),  
(14,550,551,552,553,554,555),  
(15,750,751,752,753,754,755);  

‍

2. Seeing Is Not Always Believing

Example SQL:

SELECT i1, i2 FROM t7 LIMIT 1 FOR UPDATE;  

‍

Use EXPLAIN to output the execution plan of the example SQL:

********* [ 1. row ] *********  
id            | 1  
select_type   | SIMPLE  
table         | t7  
partitions    | <null>  
type          | index  
possible_keys | <null>  
key           | idx_i1_i2_i3  
key_len       | 15  
ref           | <null>  
rows          | 15  
filtered      | 100.0  
Extra         | Using index  

‍

The Extra field value (Using index) indicates that the example SQL uses a covering index scan.

As the saying goes, “Seeing is not always believing,” which applies aptly here.

Although the execution plan shows that the example SQL uses a covering index scan, during actual transaction execution, besides reading the secondary index records, it also accesses the primary index records (i.e., the table records).

Strictly speaking, the scanning method used by the example SQL cannot be called a covering index scan.

3. Indirect Proof

In InnoDB, if a transaction needs to lock records when reading table records, the locking situation can be checked via the data_locks table in the performance_schema database.

The example SQL includes the FOR UPDATE clause, indicating that exclusive locks need to be applied to the read records.

By examining the locking situation of the example SQL, we can indirectly confirm whether both secondary index records and primary index records are accessed during execution.

Create a MySQL connection and execute the following SQL statement:

BEGIN;  
SELECT i1, i2 FROM t7 LIMIT 1 FOR UPDATE;  

‍

Create another MySQL connection and execute the following SQL statement:

SELECT  
  engine_transaction_id, object_name, index_name,  
  lock_type, lock_mode, lock_status, lock_data  
FROM performance_schema.data_locks  
WHERE object_name = 't7' AND lock_type = 'RECORD'\G  

‍

Output:

********* [ 1. row ] *********  
engine_transaction_id | 3600  
object_name           | t7  
index_name            | idx_i1_i2_i3  
lock_type             | RECORD  
lock_mode             | X  
lock_status           | GRANTED  
lock_data             | 101, 102, 103, 1  
  
********* [ 2. row ] *********  
engine_transaction_id | 3600  
object_name           | t7  
index_name            | PRIMARY  
lock_type             | RECORD  
lock_mode             | X,REC_NOT_GAP  
lock_status           | GRANTED  
lock_data             | 1  

‍

We can see that during execution, the transaction locks records as follows:

• An exclusive lock is applied to the secondary index idx_i1_i2_i3 record <i1 = 101, i2 = 102, i3 = 103, id = 1>, indicating that the transaction read the secondary index record.
• An exclusive lock is applied to the primary index record <id = 1>, indicating that the transaction read the primary index record.

Thus, for the example SQL, although the execution plan shows a covering index scan, during actual execution, besides reading secondary index records, it also accesses the primary index records.

4. Direct Proof

For readers interested in code, debugging can directly prove this. The proof involves two key pieces of code:

• The ha_innobase::build_template() method.
• The row_search_mvcc() function.

Relevant code for ha_innobase::build_template() method:

void ha_innobase::build_template(bool whole_row) {  
  ...  
  /* 1 */ if (m_prebuilt->select_lock_type == LOCK_X) {  
    /* 2 */ whole_row = true;  
    /* 3 */  
  } else if (!whole_row) {  
    /* 4 */ ...  
    /* 5 */  
  }  
  /* 6 */ clust_index = m_prebuilt->table->first_index();  
  /* 7 */ index = whole_row ? clust_index : m_prebuilt->index;  
  /* 8 */ m_prebuilt->need_to_access_clustered = (index == clust_index);  
  ...  
}  

‍

During the execution of the example SQL, when the ha_innobase::build_template method is called, the value of the whole_row parameter is false.

The example SQL includes the FOR UPDATE clause, meaning the transaction will apply exclusive locks to table records, satisfying the condition of the if branch at line 1, which changes the whole_row parameter from false to true.

At line 7, since whole_row = true, the local variable index is assigned the value of clust_index.

At line 8, because index was assigned clust_index earlier, (index == clust_index) evaluates to true, and m_prebuilt->need_to_access_clustered is set to true.

m_prebuilt->need_to_access_clustered = true indicates that the primary index records need to be accessed.

Now, let’s look at the row_search_mvcc() function. This function has a lot of code, so we’ll focus only on the key lines:

dberr_t row_search_mvcc(...) {  
  /*  1 */ dict_index_t *index = prebuilt->index;  
  /*  2 */ ...  
  /*  3 */ if (UNIV_LIKELY(direction != 0)) {  
    /*  4 */ ...  
    /*  5 */  
  } else if (dtuple_get_n_fields(search_tuple) > 0) {  
    /*  6 */ ...  
    /*  7 */  
  } else if (mode == PAGE_CUR_G || mode == PAGE_CUR_L) {  
    /*  8 */ pcur->open_at_side(mode == PAGE_CUR_G, index, BTR_SEARCH_LEAF, false, 0, &mtr);  
    /*  9 */  
  }  
  /* 10 */ ...  
  /* 11 */ rec = pcur->get_rec();  
  /* 12 */ ...  
  /* 13 */ if (index != clust_index && prebuilt->need_to_access_clustered) {  
    /* 14 */ ...  
    /* 15 */ err = row_sel_get_clust_rec_for_mysql(  
        /* 16 */ prebuilt, index, rec, thr, &clust_rec, &offsets, &heap,  
        /* 17 */ need_vrow ? &vrow : nullptr, &mtr, prebuilt->get_lob_undo());  
    /* 18 */ ...  
    /* 19 */  
  }  
  /* 20 */ ...  

‍

• Line 1: index is assigned prebuilt->index, which is the secondary index idx_i1_i2_i3.
• Line 11: Read a record from the secondary index.
• Line 13: Two conditions are involved:
  • index is a secondary index, not the primary index, so index != clust_index is true.
  • Earlier, in the ha_innobase::build_template() method, prebuilt->need_to_access_clustered was set to true, so this condition is also true.
    Both conditions of the if branch are met, indicating that the primary index records need to be accessed.
• Line 15: row_sel_get_clust_rec_for_mysql() accesses the primary index records.

5. Why Access the Primary Index?

We have indirectly and directly proven that the example SQL does access the primary index records. Let’s dig deeper to understand why.

This brings us back to the FOR UPDATE clause.

Adding the FOR UPDATE clause to a SELECT statement tells MySQL: This SELECT statement reads records in preparation for subsequent modifications to the table records. During transaction execution, please apply exclusive locks to the read records.

If a covering index scan is used, only secondary index records are read, and only these secondary index records are locked. This means other transactions can modify or delete the primary index records corresponding to these secondary index records.

If another transaction modifies certain field values of the primary index records (these fields also exist in the secondary index) or deletes the primary index records, the primary index records will no longer be consistent with the previously read secondary index records.

To prevent this, even if the execution plan indicates a covering index scan, the primary index records are still accessed during actual execution.

​Purpose:​​
To ensure transaction isolation and avoid data inconsistency by applying exclusive locks to primary index records.

6. Summary

If a transaction needs to apply exclusive locks to table records during execution, to avoid inconsistencies between secondary index and primary index records, even if the execution plan shows a covering index scan, the primary index records will still be accessed.

This ensures transaction isolation and data consistency by applying exclusive locks to primary index records.

‍