Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1862701
  • 博文数量: 273
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 2000
  • 用 户 组: 普通用户
  • 注册时间: 2014-03-31 14:30
个人简介

自己慢慢积累。

文章分类

全部博文(273)

分类: Mysql/postgreSQL

2015-10-23 18:21:07


一、过滤条件对索引的影响
例如:
表 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 字段对索引的影响
看表结构

点击(此处)折叠或打开

  1. CREATE TABLE `vmc_speeding` (
  2.   `ID_` bigint(20) NOT NULL AUTO_INCREMENT,
  3.   `HPHM_` varchar(11) DEFAULT NULL COMMENT '车牌号',
  4.   `CLLX_` varchar(4) DEFAULT NULL COMMENT '车辆类型',
  5.   `SPEED_` decimal(10,0) DEFAULT NULL COMMENT '平均速度',
  6.   `TRAFFICINID_` varchar(36) DEFAULT NULL COMMENT '驶入通行记录id',
  7.   `TRAFFICOUTID_` varchar(36) DEFAULT NULL COMMENT '驶出通行记录id',
  8.   `TIMEIN_` datetime DEFAULT NULL COMMENT '驶入时间',
  9.   `TIMEOUT_` datetime DEFAULT NULL COMMENT '驶出时间',
  10.   `INTERVALID_` int(11) DEFAULT NULL COMMENT '区间测试配置id',
  11.   `CREATEDATE_` datetime DEFAULT NULL COMMENT '入库时间',
  12.   `TYPE_` varchar(32) DEFAULT NULL COMMENT '违章类型',
  13.   `INPICTURE_` varchar(255) DEFAULT NULL COMMENT '驶入图片',
  14.   `OUTPICTURE_` varchar(255) DEFAULT NULL COMMENT '驶出图片',
  15.   PRIMARY KEY (`ID_`),
  16.   KEY `index01` (`INTERVALID_`,`TIMEIN_`,`TIMEOUT_`)
  17. ) ENGINE=TokuDB AUTO_INCREMENT=18919438 DEFAULT CHARSET=utf8 ROW_FORMAT=TOKUDB_QUICKLZ;
来看具体的SQL :
首先看看特殊的处理 count()

点击(此处)折叠或打开

  1. mysql> explain select count(*) from vmc_speeding where TIMEIN_ >= '2015-10-12 14:00:04' AND TIMEOUT_ < '2015-10-22 14:00:04';
  2. +----+-------------+--------------+-------+---------------+---------+---------+------+----------+--------------------------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +----+-------------+--------------+-------+---------------+---------+---------+------+----------+--------------------------+
  5. | 1 | SIMPLE | vmc_speeding | index | NULL | index01 | 23 | NULL | 18907524 | Using where; Using index |
  6. +----+-------------+--------------+-------+---------------+---------+---------+------+----------+--------------------------+
  7. 1 row in set (0.00 sec)
虽然过滤条件是timein_ 和 timeount_ 按照上文提到的索引首字段匹配原则来看,不会走索引,但是实际上却走了索引!
再看三个例子,分别把 * 替换成 主键、索引中的第一个字段、其他非索引字段

点击(此处)折叠或打开

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

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

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

  22. 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 的查询

点击(此处)折叠或打开

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

  8. 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' );
  9. +----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
  10. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  11. +----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
  12. | 1 | SIMPLE | t1 | index | NULL | index01 | 23 | NULL | 18907524 | Using where; Using index |
  13. +----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
  14. 1 row in set (0.00 sec)

  15. 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' );
  16. +----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
  17. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  18. +----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
  19. | 1 | SIMPLE | t1 | index | NULL | index01 | 23 | NULL | 18907524 | Using where; Using index |
  20. +----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
  21. 1 row in set (0.00 sec)

  22. 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' );
  23. +----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
  24. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  25. +----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
  26. | 1 | SIMPLE | t1 | index | NULL | index01 | 23 | NULL | 18907524 | Using where; Using index |
  27. +----+-------------+-------+-------+---------------+---------+---------+------+----------+--------------------------+
  28. 1 row in set (0.00 sec)

  29. 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' );
  30. +----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
  31. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  32. +----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
  33. | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 18907524 | Using where |
  34. +----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
  35. 1 row in set (0.00 sec)
可以看出,结果与 count 基本一致,除了 select * 和 count(*) 不一样以外,
说明:MYSQL 对 count(*) 是有自动优化的

再看看这个对比:

点击(此处)折叠或打开

  1. 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' );
  2. +----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
  3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  4. +----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
  5. | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 18907524 | Using where |
  6. +----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
  7. 1 row in set (0.00 sec)

  8. 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');
  9. +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  10. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
  11. +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  12. | 1 | SIMPLE | t1 | range | index01 | index01 | 23 | NULL | 1 | Using where |
  13. +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  14. 1 row in set (0.00 sec)
看来 如果select 的字段里有非索引字段时,MYSQL 还是会执行 索引首字匹配原则


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

阅读(1622) | 评论(0) | 转发(0) |
0

上一篇:MSYQL 让 drop table更快

下一篇:NUMA的取舍

给主人留下些什么吧!~~