Shulou(Shulou.com)06/01 Report--

Index Extensions meaning

At the beginning of MySQL5.6, the function that InnoDB can automatically expand the secondary index through the primary key is called Index Extensions, that is, the secondary index stores not only the key value of the index of this column, but also the key value of the primary key column.

Create the following test table

Mysql > show create table T1\ Gateway * 1. Row * * Table: t1Create Table: CREATE TABLE `t1` (`i1` int (11) NOT NULL DEFAULT'0), `i2` int (11) NOT NULL DEFAULT'0), `d` date DEFAULT NULL, PRIMARY KEY (`i1`, `i2`) KEY `kd` (`d`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) 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.08 sec) Records: 25 Duplicates: 0 Warnings: 0

The competition is (i1), and the secondary index is (d), but InnoDB will automatically extend the secondary index to (dmaine i1) i2, which can improve performance for some special queries.

Open index extensions and test the use of this feature

Namely: use_index_extensions=on

Mysql > show variables like 'optimizer_switch%'\ gateway * 1. Row * * Variable_name: optimizer_switch Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on Block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on1 row in set (0.00 sec) mysql > FLUSH TABLE T1 Query OK, 0 rows affected (0.02 sec) mysql > FLUSH STATUS;Query OK, 0 rows affected (0.01 sec) mysql > SELECT COUNT (*) FROM T1 WHERE i1 = 3 AND d ='0-01-01-01-01-01-01-01-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / 01 / +-- +-+ | 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.00 sec) mysql > desc SELECT COUNT (*) FROM T1 WHERE i1 = 3 AND d = '2000-01-01' | +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | T1 | NULL | ref | PRIMARY KDDD | KDDD | 8 | const Const | 1 | 100.00 | Using index | +-- +- -+-+ 1 row in set 1 warning (0.00 sec)

Turn off index extensions and test that the feature is not used

Namely: use_index_extensions=off

Mysql > set session optimizer_switch='use_index_extensions=off';Query OK, 0 rows affected (0.00 sec) mysql > FLUSH TABLE T1 itself query OK, 0 rows affected (0.02 sec) mysql > FLUSH STATUS;Query OK, 0 rows affected (0.02 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) mysql > desc SELECT COUNT (*) FROM T1 WHERE i1 = 3 AND d = '2000-01-01' | +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -- + | 1 | SIMPLE | T1 | NULL | ref | PRIMARY KDDD | PRIMARY | 4 | const | 5 | 20.00 | Using where | + -- + 1 row in set 1 warning (0.00 sec)

Summary

When Index Extensions is turned on, the index extension index (dmaine i1) is used. As you can see from key=k_id,key_len=8,ref=const,const, it goes directly through the index without going back to the table (Using index) and returns 1 row of results.

When you turn off Index Extensions, you use partial contention, retrieve 5 rows, filter 20% through the condition behind where, that is, return 1 row of data, and need to return to the table. The efficiency is certainly not as good as the previous one.

Reference link

Https://dev.mysql.com/doc/refman/5.7/en/index-extensions.html

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.