所谓的索引扩展指的是在二级索引上,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方式,整体的效果更好