索引扩展

2020-04-07 23:02 abce 阅读(401) 评论(0) 编辑收藏举报

InnoDB通过将主键列附加到辅助索引来自动扩展每个辅助索引。例如：

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;

表在列（i1，i2）上定义了主键。它还在列（d）上定义了二级索引k_d，但是InnoDB在内部扩展了该索引并将其视为列（d，i1，i2）。

在确定如何以及是否使用该索引时，优化器会考虑扩展二级索引的主键列。这可以导致更有效的查询执行计划和更好的性能。

优化器可以将扩展的辅助索引用于ref，range和index_merge索引访问，松散索引扫描访问(Loose Index Scan access)，join和排序优化以及min()/max()优化。

以下示例显示了优化器是否使用扩展二级索引如何影响执行计划。假设t1填充了以下行：

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');

对于查询语句：

1	`select` `count(*)` `from` `t1` `where` `i1 = 3` `and` `d =` `'2000-01-01';`

当优化器不考虑索引扩展时，它将索引k_d仅视为（d）。查询的EXPLAIN产生以下结果：

>SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)
 
>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.01 sec)

当优化器考虑索引扩展时，它将k_d视为（d，i1，i2）。在这种情况下，它可以使用最左边的索引前缀（d，i1）来制定更好的执行计划：

>SET optimizer_switch = 'use_index_extensions=on';
Query OK, 0 rows affected (0.00 sec)
 
>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)

在这两种情况下，key都表明优化器将使用二级索引k_d，但是EXPLAIN输出显示了使用扩展索引的这些改进：
·key_len从4字节变为8字节，这表明键查找使用的是列d和i1，而不仅仅是d。
·ref值从const变为const，const，因为键查找使用两个键部分，而不是一个。
·rows从5减少到1，这表明InnoDB应该需要检查更少的行就能产生结果。
·Extra从Using where; Using index 改为 Using index。这意味着可以仅使用索引读取行，而无需查阅数据行中的列。

也可以通过show status命令来查看是否开启索引扩展的区别：

#开启
>SET optimizer_switch = 'use_index_extensions=on';
>FLUSH TABLE t1;
>FLUSH STATUS;
>SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
>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     |
+-----------------------+-------+
 
#关闭
>SET optimizer_switch = 'use_index_extensions=off';
>FLUSH TABLE t1;
>FLUSH STATUS;
>SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
>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     |
+-----------------------+-------+