首先回顾一下这三个的定义

定义回顾

  • Using where
    当有where条件，但是不能使用索引或者使用索引后仍需扫描全表或者索引树判断条件的情况，简单来说，有效的where条件就Using where。
  • Using index
    索引覆盖，索引树已包含所有需要的数据，无需回表查询
  • Using index condition
    官方文档：https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html
    索引条件下推（Index Condition Pushdown，ICP）是MySQL使用索引的情况的优化。
    简单来说，在服务器需要扫描表的情况下
    当没有ICP时，存储引擎扫描可以明确地使用索引的条件，将符合条件的记录返回给服务器。
    当使用ICP时，只要条件可以在索引上判断出来，就由存储引擎在索引树上完成判断，再将符合条件的记录返回给服务器。
    ICP可以减少存储引擎必须访问基本表的次数以及服务器必须访问存储引擎的次数，这是是否使用ICP的最可靠的判断条件

ICP有以下条件限制：

  • 当type为ref、eq_ref和ref_or_null且需要访问完整表行
  • InnoDB或MyISAM引擎
  • 对于InnoDB来说，ICP只适用于二级索引，可以减少全表扫描从而减少IO次数。对于聚集索引，数据和索引都已经在InnoDB缓冲池中，使用ICP不能减少IO操作。
  • 虚拟列上的二级索引不支持ICP
  • 子查询条件不能下推
  • 不能使用存储函数，存储引擎不能调用这些函数
  • 触发条件不能被下推

是否启用ICP
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

实战分析

数据库来自MySQL官方employees库

表结构如下：
MySQL8.0.19

CREATE TABLE `employees` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `index_all` (`birth_date`,`last_name`,`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

birth_date, last_name, first_name三列联合索引，编码utf8

无条件覆盖索引

explain
select emp_no, first_name, last_name
from employees;

+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | index_all | 97      | NULL | 299423 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+

type为index，扫描整个索引树，服务器层不需要再判断条件，所以Extra只有Using index
使用三列，key_len = 3 + (14 * 3 + 2) + (16 * 3 + 2) = 97

有条件覆盖索引

explain
select emp_no, last_name
from employees
where birth_date = '1999-09-09';

+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ref  | index_all     | index_all | 3       | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+

满足最左匹配原则，且非唯一索引，故type为ref，走覆盖索引，Using index
key_len = 3

不满足最左匹配

explain
select emp_no, first_name
from employees
where last_name like '%达到%';

+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | index | NULL          | index_all | 97      | NULL | 299423 |    11.11 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+--------------------------+

where条件不满足最左匹配，但是select的列在索引树中可以全部找到，故Using index，并且type为index，存储引擎扫描整个索引树，找到满足last_name like '%达到%'的数据（where条件有效），返回给服务器，服务器再过滤不需要的字段。
因为使用了整个联合索引，key_len = 97

如果select了不在联合索引的字段，则不能使用覆盖索引，只有Using where了。

索引下推

explain
select emp_no, first_name, hire_date
from employees
where birth_date = '199-09-09'
  and last_name like '%达到%';

+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | index_all     | index_all | 3       | const |    1 |    11.11 | Using index condition |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-----------------------+

首先明确一点，这条SQL必须访问基本表，因为有hire_date字段，所以一定没有Using index。
满足最左匹配，birth_date字段走索引，但是last_name不能走索引。
在有ICP的情况下，last_name的判断由存储引擎在索引树上完成，返回给服务器后，因为有hire_date字段，服务器访问基本表获取所有数据。

当没有ICP情况下，last_name由服务器完成判断，只有Using where，服务器读取基本表的部分行可能是不满足条件的。

注意：Using index condition下推的条件并没有用到索引，而是全部扫描，所以key_len不计算在内，所谓用到索引，就是利用了B+树查找的特性

+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ref  | index_all     | index_all | 3       | const |    1 |    11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+

去掉hire_date

explain
select emp_no, first_name
from employees
where birth_date = '199-09-09'
  and last_name like '%达到%';

+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
| id | select_type | table     | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | index_all     | index_all | 3       | const |    1 |    11.11 | Using where; Using index |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+

没有hire_date，服务器不需要访问基本表，ICP优化也就没必要了。

Q.E.D.

一切很好，不缺烦恼。