Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to expand the index by InnoDB engine in MySQL. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The InnoDB engine extends the index and automatically appends primary key values and their impact on the execution plan.

In MySQL, each table using the InnoDB engine creates a normal index (that is, a non-primary key index) that holds the value of the primary key at the same time. For example, the statement CREATE TABLE T1

I1 INT NOT NULL DEFAULT 0

I2 INT NOT NULL DEFAULT 0

D DATE DEFAULT NULL

PRIMARY KEY (i1, i2)

INDEX krypd (d)

) ENGINE = InnoDB

The T1 table is created with the primary key (i1diem i2), and the index k _ quod based on the d column is created, but in fact, at the bottom, the InnoDB engine extends the index k _ quod to (dmaine i1 ~ i2). The InnoDB engine does this by trading space for performance, and the optimizer will have more column references when determining whether and which index to use, which may generate a more efficient execution plan and achieve better performance. The optimizer uses extended columns for ref, range, and index_merge type access, Loose Index Scan access, join and sort optimization, and MIN () / MAX () optimization. Let's look at an example: root@database-one 15:15: [gftest] > CREATE TABLE T1 (

-> i1 INT NOT NULL DEFAULT 0

-> i2 INT NOT NULL DEFAULT 0

-> d DATE DEFAULT NULL

-> PRIMARY KEY (i1, i2)

-> INDEX krypd (d)

->) ENGINE = InnoDB

Query OK, 0 rows affected (0.06 sec)

Root@database-one 15:15: [gftest] > 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.01sec)

Records: 25 Duplicates: 0 Warnings: 0

Root@database-one 15:21: [gftest] > show index from T1

+- -+

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+- -+

| | T1 | 0 | PRIMARY | 1 | i1 | A | 5 | NULL | NULL | | BTREE |

| | T1 | 0 | PRIMARY | 2 | i2 | A | 25 | NULL | NULL | | BTREE |

| | T1 | 1 | KDD | 1 | d | A | 5 | NULL | NULL | YES | BTREE |

+- -+

3 rows in set (0.01sec)

Appending an extended primary key in a normal index is done by InnoDB at the bottom. Statements such as show index do not show append columns, but we can verify it in other ways. Look at this SQLSELECT COUNT (*) FROM T1 WHERE i1 = 3 AND d = '2000-01-01' if the InnoDB does not have an extended index, the index KExd is (d), the generated execution plan should be similar to this, use the KExd index to find 5 rows of data with d '2000-01-01', then go back to the table to filter out i1 = 3, and finally calculate count. Or use the primary key index to find five rows of data with i1 = 3, then go back to the table and filter out the d = '2000-01-01', and finally calculate the count. The following is only an indication of the situation of taking the KBuild index: mysql > EXPLAIN SELECT COUNT (*) FROM T1 WHERE i1 = 3 AND d = '2000-01-01'\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t1

Type: ref

Possible_keys: PRIMARY,k_d

Key: k_d

Key_len: 4

Ref: const

Rows: 5

Extra: Using where; Using index

If InnoDB expands the index and the index kendd is (dpendium i1 WHERE i2), then the optimizer can use the leftmost index prefix (djournal i1), and the resulting execution plan should be similar to this. Use the kquod index to find 1 row of data with d '2000-01-01' and i1 = 3, and then calculate countmysql > EXPLAIN SELECT COUNT (*) FROM T1 WHERE i1 = 3 AND d = '2000-01-01'\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t1

Type: ref

Possible_keys: PRIMARY,k_d

Key: k_d

Key_len: 8

Ref: const,const

Rows: 1

Extra: Using index

And the d column is 4 bytes of DATE type, and i1 is 4 bytes of INT type, so the key length used in the query is 8 bytes (key_len: 8). Let's take a look at the actual generated execution plan root@database-one 15:35: [gftest] > 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.01sec)

Sure enough, it is consistent with our judgment, pay attention to the details of the implementation plan:

Key_len changed from 4 bytes to 8 bytes, indicating that key lookups use columns d and i1, not just d.

The ref changes from const to const,const, indicating that the lookup uses two keys instead of one.

The rows decreases from 5 to 1, indicating that fewer rows are retrieved.

Extra is changed from Using where; Using index to Using index, which means that it is only read by the index and does not have to go back to the table.

If the InnoDB engine extends the general index at the bottom, you can also use circumstantial evidence by comparing it with the MyISAM engine: root@database-one 16:07: [gftest] > CREATE TABLE t1MyISAM (

-> i1 INT NOT NULL DEFAULT 0

-> i2 INT NOT NULL DEFAULT 0

-> d DATE DEFAULT NULL

-> PRIMARY KEY (i1, i2)

-> INDEX krypd (d)

->) ENGINE = MyISAM

Query OK, 0 rows affected (0.01 sec)

Root@database-one 16:07: [gftest] > INSERT INTO t1myisam 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.02 sec)

Records: 25 Duplicates: 0 Warnings: 0

Root@database-one 16:07: [gftest] > EXPLAIN SELECT COUNT (*) FROM t1myisam WHERE i1 = 3 AND d = '2000-01-01'\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t1myisam

Partitions: NULL

Type: ref

Possible_keys: PRIMARY,k_d

Key: PRIMARY

Key_len: 4

Ref: const

Rows: 4

Filtered: 16.00

Extra: Using where

1 row in set, 1 warning (0.01sec)

As you can see, the same structure and the same data, because the MyISAM engine does not automatically extend the normal index at the bottom, so the execution plan is handled by the primary key index. As described in the official manual, you can also use the SHOW STATUS command to verify root@database-one 16:12: [gftest] > FLUSH TABLE T1

Query OK, 0 rows affected (0.00 sec)

Root@database-one 16:12: [gftest] > FLUSH STATUS

Query OK, 0 rows affected (0.14 sec)

Root@database-one 16:12: [gftest] > SELECT COUNT (*) FROM T1 WHERE i1 = 3 AND d = '2000-01-01'

+-+

| | COUNT (*) |

+-+

| | 1 |

+-+

1 row in set (0.03 sec)

Root@database-one 16:12: [gftest] > 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)

Root@database-one 16:13: [gftest] > FLUSH TABLE t1myisam

Query OK, 0 rows affected (0.01 sec)

Root@database-one 16:13: [gftest] > FLUSH STATUS

Query OK, 0 rows affected (0.00 sec)

Root@database-one 16:13: [gftest] > SELECT COUNT (*) FROM t1myisam WHERE i1 = 3 AND d = '2000-01-01'

+-+

| | COUNT (*) |

+-+

| | 1 |

+-+

1 row in set (0.01 sec)

Root@database-one 16:13: [gftest] > 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)

Handler_read_next indicates the number of times data is fetched from the data file by index when an index scan is performed. For the t1myisam table using the MyISAM engine, the Handler_read_ Nextvalue is 5, and for the T1 table using the InnoDB engine, the Handler_read_ Nextvalue is reduced to 1, because the InnoDB engine extends the index with the primary key, which makes it easier to read and more efficient. By default, the optimizer considers extended columns when analyzing the index of the InnoDB table, but if for special reasons the optimizer does not consider extended columns, you can use the SET optimizer_switch = 'use_index_extensions=off' setting. Root@database-one 16:26: [gftest] > SET optimizer_switch = 'use_index_extensions=off'

Query OK, 0 rows affected (0.01 sec)

Root@database-one 16:26: [gftest] > 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.02 sec) after reading the above, have you mastered how the InnoDB engine in MySQL extends the index? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.