首页 ›存档› 技术 › 查看内容

FAQ系列|indexextensions特性介绍 0、导读 1、什么是index extensions 2、怎么利用ind ...

2018-3-30 13:00 |来自: 互联网 299 0

摘要: 0、导读本文介绍MySQL的index extensions特性，以及如何利用这个特性实现SQL查询优化。 1、什么是index extensions index extensions是MySQL 5.6.9之后的新特性，关于这个特性，手册中的解释是这样的：InnoDB autom ...

0、导读

本文介绍MySQL的index extensions特性，以及如何利用这个特性实现SQL查询优化。

1、什么是index extensions

index extensions是MySQL 5.6.9之后的新特性，关于这个特性，手册中的解释是这样的：InnoDB automatically extends each secondary index by appending the primary key columns to it（出处详见手册 8.2.1.7 Use of Index Extensions，原文链接：https://dev.mysql.com/doc/refman/5.6/en/index-extensions.html）。简言之就是，InnoDB引擎表中，会把主键所有列值附加存储在辅助索引中。

假设有这样一个表：
CREATE TABLE t(
a int not null,
b int not null,
c int not null,
d int not null,
PRIMARY KEY(a, b),
KEY i_c(c)
) ENGINE=InnoDB;

意思是，该表中的辅助索引 i_c 的索引键值，实际上也同时存储了主键中的两个列值，也就是说，i_c 的索引数据结构中，实际上存储的列是：c、a、b 三列的值。

我们可通过 innodb_table_monitor 查看验证下：

TABLE: name test/t, id 681, flags 1, columns 7, indexes 2, appr.rows 0
COLUMNS: a: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; b: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; c: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; d: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;

INDEX: name PRIMARY, id 1159, fields 2/6, uniq 2, type 3
root page 3, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: a b DB_TRX_ID DB_ROLL_PTR c d

INDEX: name i_c, id 1160, fields 1/3, uniq 3, type 0
root page 4, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: c a b

可见，确实是如此。我们顺便也看到 PRIMARY KEY 里包含了所有的列值，以及 DB_TRX_ID、DB_ROLL_PTR 等额外属性（InnoDB引擎独有特性，用于实现InnoDB的事务）。

2、怎么利用index extensions

事实上，辅助索引实际也存储主键值的特性，在InnoDB引擎中一直都是如此，只是从5.6.9版本开始后，在计算执行计划时，查询优化器（optimizer）才能识别到这个特性，并且利用这个特性。而在5.6.9以前，虽然这个特性也存在，但并不被查询优化器识别，也就无法被利用了。

这个特性可适用于 ref, range, and index_merge 等多种索引访问方式，在稀松索引扫描（loose index scan）、联接（join）、排序以及MIN()/MAX()等场景下。

我们来看看这个特性怎么被优化器识别并利用的，假设上述测试表中的测试数据有：
SELECT * FROM t;
- - - -
| a | b | c | d |
- - - -
| 1 | 2 | 4 | 2 |
| 1 | 3 | 2 | 2 |
| 1 | 4 | 9 | 2 |
| 1 | 5 | 9 | 2 |
| 1 | 6 | 8 | 2 |
| 2 | 2 | 9 | 2 |
| 3 | 2 | 8 | 2 |
| 4 | 2 | 6 | 2 |
| 5 | 2 | 6 | 2 |
| 6 | 2 | 1 | 2 |
- - - -

MySQL版本：5.6.21-70.0-log Percona Server (GPL), Release 70.0, Revision 688。

假设有下面的查询，看下它的执行计划：
mysql

声明：文章版权归原作者所有部分文章转自互联网如有侵权请联系 [邮箱地址] 删除