一、过滤条件对索引的影响
例如:
表 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 的结果在索引中都存在,那么就不用回表。
另外:有博客做了测试,并提出了以下观点: MYSQL优化器对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(*) 除外。
阅读(2491) | 评论(0) | 转发(0) |