MySQL Index and performance (3) overlay Index

The overlay index refers to the leaf node of the index that already contains all the columns to query, so there is no need to access the table data, which can greatly improve performance. Overriding indexes are particularly useful for InnoDB clustered index tables, since two queries for InnoDB two-level indexes can be avoided. In MySQL, only a B-tree index can overwrite the index, because the value of the indexed column must be stored, and the hash Index, spatial index, and Full-text index are not available.

When a query that overrides an index is launched, the using index is visible in the extra column of explain, and the following is an example where the table users have a multiple-column index (login_id,status), the execution plan is as follows

Root@test 01:30:35>explain Select Login_id,status from Users\g  
*************************** 1 row *************  
           id:1  
  select_type:simple  
        table:users  
         type:index
possible_keys:null
          key: login_id  
      key_len:387  
          ref:null
         rows:5309293  
        extra:using Index

Another benefit for InnoDB is that the level two index contains the primary key value, so the level two index can also use this message to overwrite the index, as follows:

Root@test 01:34:17>explain Select Login_id,status,id from Users\g  
*************************** 1 row **********  
           id:1  
  select_type:simple  
        table:users  
         type:index
possible_keys:null
          key:login_id  
      key_len:387  
          ref:null
         rows:5309293  
        extra:using Index

From the above we can see that although the definition of a secondary index does not contain a primary key, it is still possible to use an overlay index.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/