mysql 联合索引 最左匹配原则

Question

建表语句：

CREATE TABLE `t_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `f_1` varchar(255) DEFAULT NULL,
  `f_2` varchar(255) DEFAULT NULL,
  `f_3` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_fh` (`f_1`,`f_2`,`f_3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

索引index_fh是f_1、f-2、f_3三个字段创建的联合索引。

根据最左匹配原则，

select * from abcd where f_1 = '1';
select * from abcd where f_1 = '1' and f_2 = '1';
select * from abcd where f_1 = '1' and f_2 = '1' and f_3 = '1';

上面三个sql语句会使用索引index_fh。
但是

select * from abcd where f_2 = '1';
select * from abcd where f_3 = '1';
select * from abcd where f_2 = '1' and f_3 = '1';

应该不会使用索引，可是使用explain查看查询计划，仍然使用了索引。

+------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| id   | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
|    1 | SIMPLE      | t_1  | index | NULL          | index_fh | 2304    | NULL |    1 | Using where; Using index |
+------+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+

建表语句：

CREATE TABLE `t_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `f_1` varchar(255) DEFAULT NULL,
  `f_2` varchar(255) DEFAULT NULL,
  `f_3` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_fh` (`f_2`,`f_3`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建表语句和上面基本相同，只是索引使用了f_2、f_3两个字段，此时查询语句

select * from t_2 where f_3 = '1';

查看查询计划

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t_2   | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

没有使用索引。

为什么select * from t_2 where f_3 = '1';在两个表中都不符合最左匹配原则，查询时一个使用了索引，一个没使用索引？

Answer 1

覆盖索引，如果加个字段f_4就不会走索引了。

Answer 2

Using index，索引内部完成搜索

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row.