mysql> show create table s1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| s1 | CREATE TABLE `s1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`key1` varchar(100) DEFAULT NULL,
`key2` int(11) DEFAULT NULL,
`key3` varchar(100) DEFAULT NULL,
`key_part1` varchar(100) DEFAULT NULL,
`key_part2` varchar(100) DEFAULT NULL,
`key_part3` varchar(100) DEFAULT NULL,
`common_field` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_key2` (`key2`),
KEY `idx_key1` (`key1`),
KEY `idx_key3` (`key3`),
KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> explain select key_part1 from s1 where key_part1 = "a" and key_part2 > "b" AND key_part3 > "c";
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | s1 | NULL | range | idx_key_part | idx_key_part | 606 | NULL | 1 | 33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
該explain結(jié)果中，key字段信息表明該查詢使用到了索引: idx_key_part诗轻；Extra信息里面包含了Using where和Using index，其中Using where代表該查詢需要mysql server層進(jìn)行判斷揭北，Using index代表該查詢使用到了覆蓋索引的特性扳炬，不需要回表操作。
所以該sql的執(zhí)行流程是搔体，mysql存儲引擎層將滿足key_part1 = "a"條件的第一條記錄返回給mysql server層恨樟，由于該索引是聯(lián)合索引（包含key_part1,key_part2,key_part3這三個字段），所以除了key_part1字段外疚俱，其他字段也一起會返回給server層劝术，然后server層判斷該記錄是否符合查詢條件，如果符合發(fā)送給客戶端呆奕，否者不發(fā)送养晋；然后server層繼續(xù)向存儲層要下一條記錄，繼續(xù)判斷梁钾，直到第一條不滿足key_part1 = "a"條件的記錄為止匙握，然后存儲層告訴server層，查詢完畢陈轿。
mysql> explain select * from s1 where key_part1 = "a" and key_part2 > "b" AND key_part3 > "c";
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | s1 | NULL | range | idx_key_part | idx_key_part | 606 | NULL | 1 | 33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
explain結(jié)果中，key字段信息表明該查詢使用到了索引: idx_key_part秦忿；Extra信息為Using index condition說明該sql使用了innodb引擎的Index Condition PushDown的能力麦射，即索引下推；
執(zhí)行流程如下：mysql存儲引擎層將滿足key_part1 = "a"條件的第一條記錄灯谣，繼續(xù)判斷該記錄是否滿足key_part2 > "b" AND key_part3 > "c"條件潜秋，如果滿足返回給server層，否者不返回胎许；直到第一條不滿足key_part1 = "a"條件的記錄為止峻呛，查詢完畢。
