原标题:MySQL使用索引的场景分析、不能使用索引的场景分析
MySQL中能够使用索引的典型场景
1.匹配全值。对索引中的列都有等值匹配的条件。即使是在and中,and前后的列都有索引并进行等值匹配。2.匹配值的范围查询,对索引的值能够进行范围查找。3.匹配最左列前缀,仅仅使用索引中的最左边列进行查找。这个要考虑组合索引了。4.仅仅对索引进行查询,当查询的列都在索引的字段中时,查询的效率更高。5.匹配列前缀,仅仅使用索引中的第一列,并且包含索引第一列的开头一部分进行查找。比如 like 'appl%';6.能够实现索引匹配部分精确而其他部分进行范围匹配。7.MySQL5.6引入了ICP特性,进一步优化了查询,在某些情况下的条件过滤操作下放到存储引擎。
针对第 7 种,当我们在执行计划里面看到这个标志就表示MySQL使用了ICP特性进一步优化查询:
mysql>explainselect*fromco2wherecampaign_id=4561andid<=10000andid>=300G;
***************************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:Usingindex condition
1rowinset,1warning(0.01sec)
使用 “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>explainselect*fromco2whereid<=1000000orcctime=4565G
***************************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:Usingwhere
1rowinset,1warning(0.00sec)
这里的id是主键,cctime是非索引列。因此不能使用索引。
注意:大家可能会把or与and搞混了,上面的or中是:or前的列有索引,or后面的列无索引,那么此时执行该语句将不会使用索引。但是and并不是这样子,and前的列有索引,而and后的列无论有没有索引,此时该语句无论如何都会使用索引。(小贴士:and后面的列如果是范围查询的话,此列还不如不加索引。type有可能ref变为range。)
mysql>explainselect*fromco2whereid<=1000000andcctime=4565G
***************************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:Usingwhere
1rowinset,1warning(0.00sec)
原创地址:
https://www.cnblogs.com/FengGeBlog/p/10288567.html
原创作者: 峰哥ge返回搜狐,查看更多
责任编辑: