使用索引扩展

所谓的索引扩展指的是在二级索引上，innodb内部将主键添加到了二级索引的key上，这样通常会获得很好的性能和查询计划，对于ref,range,index_merge，松散索引扫描，join和排序优化的索引扫描可以使用索引扩展，

 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 `k_d` (`d`)

这个是使用了索引扩展的执行计划
mysql> EXPLAIN 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,k_d   | k_d  | 8       | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-
这个是没有使用索引扩展的执行计划
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';
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | PRIMARY,k_d   | PRIMARY | 4       | const |    5 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

看到在使用了扩展的执行计划中key_len更长，ref是使用了2次的const，rows更少，extra中使用了using index方式，整体的效果更好