MySQL使用索引的场景分析、不能使用索引的场景分析
一、MySQL中能够使用索引的典型场景
1、匹配全值。对索引中的列都有等值匹配的条件。即使是在and中,and前后的列都有索引并进行等值匹配。
2、匹配值的范围查询,对索引的值能够进行范围查找。
3、匹配最左列前缀,仅仅使用索引中的最左边列进行查找。这个要考虑组合索引了。
4、仅仅对索引进行查询,当查询的列都在索引的字段中时,查询的效率更高。
5、匹配列前缀,仅仅使用索引中的第一列,并且包含索引第一列的开头一部分进行查找。比如 like 'appl%';
6、能够实现索引匹配部分精确而其他部分进行范围匹配。
7、MySQL5.6引入了ICP特性,进一步优化了查询,在某些情况下的条件过滤操作下放到存储引擎。
(以上的如果不懂的话我们可以参考《深入浅出MySQL》书籍273页)
针对第 7 种,当我们在执行计划里面看到这个标志就表示MySQL使用了ICP特性进一步优化查询:
mysql> explain select * from co2 where campaign_id=4561 and id <= 10000 and id >=300 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: co2 partitions: NULL type: ref possible_keys: PRIMARY,campaign_id key: campaign_id key_len: 8 ref: const rows: 1 filtered: 5.00 Extra: Using index condition 1 row in set, 1 warning (0.01 sec)
使用 “Extra: Using index condition” 表示将过滤操作下推到存储引擎层来完成,这样能够降低不必要的IO访问,提高效率。
一般上Extra参数还有其他取值:比如:
1)Extra:using index :这个表示直接访问索引就足够获取到所需要的数据,不需要通过索引回表读取。这样子的好处就是只访问必须访问的数据,一般情况下,减少不必要的数据访问可提高效率。
2)Extra:using where:表示优化器除了利用索引加速查询,还需要根据索引回表查询数据。
除此之外,文件系统使用xfs是最好的,因为这种文件系统也是b-tree结构最接近于数据库的树状结构。
二、MySQL中能够存在索引但不能使用索引的典型场景
1、以 “%” 开头的LIKE查询不能利用B-Tree索引,例如:like '%PLE',如果非要这样子使用,建议此列使用全文索引来解决。
2、数据类型出现隐式转换的时候也不会使用索引。特别是当列类型是字符串,那么一定要在where条件中吧字符常量值用引号引起来,否则即使这个列上有索引,MySQL也不会使用。
3、复合索引下,查询条件不包含索引最左边部分,即不满足最左原则。
4、用or分隔开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。例如:
mysql> explain select * from co2 where id <= 1000000 or cctime= 4565 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: co2 partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 85292357 filtered: 40.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
这里的id是主键,cctime是非索引列。因此不能使用索引。
注意:大家可能会把or与and搞混了,上面的or中是:or前的列有索引,or后面的列无索引,那么此时执行该语句将不会使用索引。但是and并不是这样子,and前的列有索引,而and后的列无论有没有索引,此时该语句无论如何都会使用索引。(小贴士:and后面的列如果是范围查询的话,此列还不如不加索引。type有可能ref变为range。)
mysql> explain select * from co2 where id <= 1000000 and cctime= 4565 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: co2 partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 405614 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
相关文章
- MySQL索引优化与分析(重要)
- Mysql授权允许远程访问解决Navicat for MySQL连接mysql提示客户端不支持服务器请求的身份验证协议;考虑升级MySQL客户端
- MySQL单列索引和组合索引的选择效率与explain分析
- MySQL索引,MySQL性能分析及explain的使用,分析SQL查询性能
- MySQL的Explain关键字查看是否使用索引
- 由浅入深探究mysql索引结构原理、性能分析与优化(转)
- mysql Hash索引和BTree索引区别
- 数据库索引使用数据结构及算法, 及MySQL不同引擎索引实现
- mysql 重新整理——索引简介[七]
- mysql数据向Redis快速导入
- 一篇搞懂mysql中的索引(大白话版)
- MySQL索引详解
- [转]MySQL单列索引和组合索引的区别介绍
- Atitit Mysql查询优化器 存取类型 范围存取类型 索引存取类型 AND or的分析
- ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found 43. Created with MySQL 5
- 第39讲:MySQL常规的索引分类
- mysql对GIS空间数据的支持,包括创建空间索引
- MySQL之自带四库之mysql库
- MySQL高级篇(SQL优化、索引优化、锁机制、主从复制)
- MySQL数据库管理系统安装部署——Linux