TIP

发生在联合索引上

准备个表

CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `address` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `sex` int(1) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `nax_idx`(`name`, `age`, `sex`) USING BTREE COMMENT 'nax'
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

insert into t_user values (1,'张三','中关村1号',22,0);
insert into t_user values (2,'李四','西二旗',20,1);
insert into t_user values (3,'王五','西北旺',24,1);
insert into t_user values (4,'赵玉田','玉田花圃',30,0);
insert into t_user values (5,'刘能','大脚超时',45,0);

看一下下面几个个例子

首先为了防止索引下推扰乱分析，先将它在暂时关闭

set optimizer_switch='index_condition_pushdown=off';

sqlA

EXPLAIN select * from t_user where name ='张三' and  age  = 22 and sex =0;

+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | t_user | ref  | nax_idx       | nax_idx | 73      | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-----------------------+

sqlB

EXPLAIN select * from t_user where  age  = 22 and sex =0;

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

sqlC

EXPLAIN select * from t_user where  name ='张三' and sex =0;

+----+-------------+--------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_user | ref  | nax_idx       | nax_idx | 63      | const |    1 | Using index condition |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-----------------------+

sqlD

EXPLAIN select * from t_user where age  = 22 and name ='张三' and sex =0;

+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | t_user | ref  | nax_idx       | nax_idx | 73      | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-----------------------+

sqlE

EXPLAIN select name from t_user where age  = 22 and sex =0;

+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t_user | index | NULL          | nax_idx | 73      | NULL |    5 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+

通过上面几个SQL可以判断出来

0、联合索引当条件时，最好按顺序从左到右使用

1、联合索引查询时可以颠倒顺序（优化器会自动帮你调整一下），但不可以跨列

2、如果跨列了，但是恰巧没有回表，所查内容正好是跨列的缺的那个，联合索引也会生效