MYSQL是这么走索引的

一、过滤条件对索引的影响
例如：
表 table (a , b ,c, d )
索引 index01 (a,b,c)

如果 select a,b,c from table where a=1 and b=1 and c=1;
那么索引 a b c 都会用到，且不用回表，会最快返回；

如果 select a,b,c from table where a=1 and  c=1;
那么索引只会用到 a 过滤,但仍然不用回表，较快返回；

如果 select a,b,c from table where a=1 and b>1 and b<1 and c=1;
那么索引只会用到 a , b  ，但是仍然不用回表，较快返回；

如果 select a,b,c ,d from table where  b=1 and c=1;
那么索引会无法使用  ，返回时间长；

阶段一 总结：1、MYSQL在碰到复合索引时，只要碰到范围（<,>,<=,>=）的查询字段，过滤该条件后就去回表了，不管后面的字段有没有可以索引可以走
      2、索引无法进行中间跳跃， select count(*) from 除外
      3、索引有首字段匹配原则
      4、如果 select 的结果在索引中都存在，那么就不用回表。
另外：有博客做了测试，并提出了以下观点： ＭＹＳＱＬ优化器对in list是转成“or” 的“多个等值”查询来处理的；并没有转成范围查询 ；

二、select 字段对索引的影响
看表结构

CREATE TABLE `vmc_speeding` (
  `ID_` bigint(20) NOT NULL AUTO_INCREMENT,
  `HPHM_` varchar(11) DEFAULT NULL COMMENT '车牌号',
  `CLLX_` varchar(4) DEFAULT NULL COMMENT '车辆类型',
  `SPEED_` decimal(10,0) DEFAULT NULL COMMENT '平均速度',
  `TRAFFICINID_` varchar(36) DEFAULT NULL COMMENT '驶入通行记录id',
  `TRAFFICOUTID_` varchar(36) DEFAULT NULL COMMENT '驶出通行记录id',
  `TIMEIN_` datetime DEFAULT NULL COMMENT '驶入时间',
  `TIMEOUT_` datetime DEFAULT NULL COMMENT '驶出时间',
  `INTERVALID_` int(11) DEFAULT NULL COMMENT '区间测试配置id',
  `CREATEDATE_` datetime DEFAULT NULL COMMENT '入库时间',
  `TYPE_` varchar(32) DEFAULT NULL COMMENT '违章类型',
  `INPICTURE_` varchar(255) DEFAULT NULL COMMENT '驶入图片',
  `OUTPICTURE_` varchar(255) DEFAULT NULL COMMENT '驶出图片',
  PRIMARY KEY (`ID_`),
  KEY `index01` (`INTERVALID_`,`TIMEIN_`,`TIMEOUT_`)
) ENGINE=TokuDB AUTO_INCREMENT=18919438 DEFAULT CHARSET=utf8 ROW_FORMAT=TOKUDB_QUICKLZ;

来看具体的SQL ：
首先看看特殊的处理 count()

mysql> explain select count(*) from vmc_speeding where TIMEIN_ >= '2015-10-12 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04';
+----+-------------+--------------+-------+---------------+---------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+---------+---------+------+----------+--------------------------+
| 1 | SIMPLE | vmc_speeding | index | NULL | index01 | 23 | NULL | 18907524 | Using where; Using index |
+----+-------------+--------------+-------+---------------+---------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

虽然过滤条件是timein_ 和 timeount_ 按照上文提到的索引首字段匹配原则来看，不会走索引，但是实际上却走了索引！
再看三个例子，分别把 * 替换成 主键、索引中的第一个字段、其他非索引字段

mysql> explain select count(ID_) from vmc_speeding where TIMEIN_ >= '2015-10-12 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04';
+----+-------------+--------------+-------+---------------+---------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+---------+---------+------+----------+--------------------------+
| 1 | SIMPLE | vmc_speeding | index | NULL | index01 | 23 | NULL | 18907524 | Using where; Using index |
+----+-------------+--------------+-------+---------------+---------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select count(INTERVALID_) from vmc_speeding where TIMEIN_ >= '2015-10-12 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04';
+----+-------------+--------------+-------+---------------+---------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+---------------+---------+---------+------+----------+--------------------------+
| 1 | SIMPLE | vmc_speeding | index | NULL | index01 | 23 | NULL | 18907524 | Using where; Using index |
+----+-------------+--------------+-------+---------------+---------+---------+------+----------+--------------------------+
1 row in set (0.01 sec)

mysql> explain select count(TRAFFICOUTID_) from vmc_speeding where TIMEIN_ >= '2015-10-12 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04';
+----+-------------+--------------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | vmc_speeding | ALL | NULL | NULL | NULL | NULL | 18907524 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> explain select count(TIMEIN_) from vmc_speeding_test where TIMEIN_ >= '2015-10-12 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04';
+----+-------------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table             | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | vmc_speeding_test | index | NULL          | index01 | 23      | NULL |    5 | Using where; Using index |
+----+-------------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

发现，替换成 count 主键 和 count 同索引字段 后，仍然可以走索引，但是如果替换成 非索引字段，那么就不会再走索引了。

再看看非 count 的查询

mysql> explain select * from vmc_speeding t1 WHERE ( TIMEIN_ >= '2014-10-22 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04' );
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 18907524 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> explain select t1.id_ from vmc_speeding t1 WHERE ( TIMEIN_ >= '2014-10-22 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04' );
+----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
| 1 | SIMPLE | t1 | index | NULL | index01 | 23 | NULL | 18907524 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select t1.INTERVALID_ from vmc_speeding t1 WHERE ( TIMEIN_ >= '2014-10-22 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04' );
+----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
| 1 | SIMPLE | t1 | index | NULL | index01 | 23 | NULL | 18907524 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select t1.TIMEIN_ from vmc_speeding t1 WHERE ( TIMEIN_ >= '2014-10-22 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04' );
+----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
| 1 | SIMPLE | t1 | index | NULL | index01 | 23 | NULL | 18907524 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select t1.TRAFFICOUTID_ from vmc_speeding t1 WHERE ( TIMEIN_ >= '2014-10-22 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04' );
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 18907524 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

可以看出，结果与 count 基本一致，除了 select * 和 count(*) 不一样以外，
说明：MYSQL 对 count(*) 是有自动优化的

再看看这个对比：

mysql> explain select t1.TRAFFICOUTID_ from vmc_speeding t1 WHERE ( TIMEIN_ >= '2014-10-22 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04' );
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 18907524 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> explain select t1.TRAFFICOUTID_ from vmc_speeding t1 WHERE (INTERVALID_='9' and TIMEIN_ >= '2014-10-22 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04');
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | range | index01 | index01 | 23 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

看来 如果select 的字段里有非索引字段时，MYSQL 还是会执行 索引首字匹配原则


阶段二 总结
1、对于复合索引，如果select 字段只包含主键 或者对应的索引字段的话，即使过滤条件不符合  索引首字匹配 ，也会走索引
2、对于复合索引，如果select 字段包含主键和对应的索引字段以外的字段的话，会启用索引首字匹配原则。select count(*) 除外。