Detailed explanation of MySQL InnoDB index extension

Index extension: InnoDB automatically extends each secondary index by appending the primary key columns to that index. Create the following table structure:

mysql> CREATE TABLE t1 (
  -> i1 INT NOT NULL DEFAULT 0,
  -> i2 INT NOT NULL DEFAULT 0,
  -> d DATE DEFAULT NULL,
  -> PRIMARY KEY (i1, i2),
  -> INDEX k_d (d)
  ->) ENGINE = InnoDB;

Query OK, 0 rows affected (0.14 sec)

Table t1 has a primary key defined on columns (i1, i2). A secondary index is also defined on column (d), but InnoDB expands this index and treats it as (d,i1,i2).

The optimizer considers the primary key columns of extended secondary indexes when deciding how and whether to use the index. This can result in more efficient query execution plans and better performance.

The optimizer can use extended secondary indexes for ref, range, and index_merge index access, for loose index scans, for join and sort optimizations, and for MIN()/MAX() optimizations.

The following example will show whether the optimizer uses an extended secondary index to influence the execution plan to insert the following data into table t1:

mysql> INSERT INTO t1 VALUES (1, 1, '1998-01-01'), (1, 2, '1999-01-01'), (1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
  ->(1, 5, '2002-01-01'), (2, 1, '1998-01-01'), (2, 2, '1999-01-01'), (2, 3, '2000-01-01'), (2, 4, '2001-01-01'),
  ->(2, 5, '2002-01-01'), (3, 1, '1998-01-01'), (3, 2, '1999-01-01'), (3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
  ->(3, 5, '2002-01-01'), (4, 1, '1998-01-01'), (4, 2, '1999-01-01'), (4, 3, '2000-01-01'), (4, 4, '2001-01-01'),
  ->(4, 5, '2002-01-01'), (5, 1, '1998-01-01'), (5, 2, '1999-01-01'), (5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
  ->(5, 5, '2002-01-01');
Query OK, 25 rows affected (0.05 sec)
Records: 25 Duplicates: 0 Warnings: 0

Suppose the following query is executed:

SET optimizer_switch = 'use_index_extensions=off';
explain select count(*) from t1 where i1=3 and d= '2000-01-01' ;

In this case, the optimizer cannot use the primary key because the primary key contains columns (i1, i2) and the query does not reference i2. Instead, the optimizer can use the secondary index k_d on column (d), and the execution plan depends on whether the extended index is used.

When the optimizer does not consider index expansion, it treats the index k_d as just (d)

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select count(*) from t1 where i1=3 and d= '2000-01-01' \G;
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: t1
  partitions: NULL
     type: ref
possible_keys: PRIMARY,k_d
     key: PRIMARY
   key_len: 4
     ref: const
     rows: 5
   filtered: 20.00
    Extra: Using where
1 row in set, 1 warning (0.00 sec)

When the optimizer considers index expansion, it considers k_d to be (d, i1, i2). In this case, it can use the leftmost index prefix (d, i1) to generate a better execution plan.

mysql> SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select count(*) from t1 where i1=3 and d= '2000-01-01' \G;
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: t1
  partitions: NULL
     type: ref
possible_keys: PRIMARY,k_d
     key: k_d
   key_len: 8
     ref: const,const
     rows: 1
   filtered: 100.00
    Extra: Using index
1 row in set, 1 warning (0.00 sec)

In both cases, key indicates that the optimizer will use the secondary index k_d , but the EXPLAIN output shows these improvements from using the extended index:

.key_len has changed from 4 bytes to 8 bytes, indicating that the key lookup uses columns d and i1, not just d.

The value of .ref changes from const to const,const because the key lookup uses two key columns instead of one.

.rows: Decreased from 5 to 1, indicating that InnoDB will examine fewer rows to generate query results.

The .Extra value changed from Using where; Using index to Using index. This means that querying records only requires using indexes instead of querying data row records.

You can use show status to see the difference between the optimizer with and without extended indexes:

mysql> flush table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.03 sec)

The flush table and flush status statements above are used to clear the table cache and clear status statistics.

The results of show status when index extension is not used are as follows:

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from t1 where i1=3 and d= '2000-01-01';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 5 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.00 sec)

When using the index extension, show status produces the following results, where the value of handler_read_next decreases from 5 to 1, indicating that using this index is more efficient:

mysql> flush table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> flush status
  -> ;
Query OK, 0 rows affected (0.02 sec)

mysql> SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t1 where i1=3 and d= '2000-01-01';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 1 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.01 sec)

The use_index_extensions flag of the optimizer_switch system variable allows the optimizer to exclude primary key columns from consideration when deciding how to use secondary indexes for InnoDB tables. By default, use_index_extensions is enabled. To check whether disabling index extensions can improve performance you can execute the following statement:

mysql> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)

The above is a detailed explanation of MySQL InnoDB index extension. For more information about MySQL index extension, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Detailed explanation of the index and storage structure of the MySQL InnoDB engine
Detailed explanation of MySQL InnoDB secondary index sorting example
In-depth explanation of the principle of MySQL Innodb index
Detailed Introduction to MySQL Innodb Index Mechanism
Mysql Innodb storage engine index and algorithm

<<: Detailed explanation of Linux zabbix agent deployment and configuration methods

>>: Summary of Vue3 combined with TypeScript project development practice