MySql学习笔记（七） - 索引没有被用的原因

mysql的索引存储分类，mysql目前提供4中索引。分别为B-tree索引，大部分引擎都支持B树索引，Hash索引是只有memory引擎支持，R-tree（空间索引）是myisam的一个特殊索引，主要用来存储空间数据类型，通常使用的比较少。full-text全文索引也是myisam的一个特殊索引类型，主要用于全文索引，在mysql5.6开始就对全文索引进行了支持。

mysql目前不支持函数索引，但是能够对列的前一部分进行索引，列入title字段只索引前10个字符。这个特性可以打打缩小索引文件的大小，但是前缀索引也有缺点。在排序order by和分组group by操作的时候无法使用。用户在设计表的时候要灵活应用。

创建一个前缀索引：

create indexx_title on film(title(10));

比较常用的索引就是B-tree和Hash索引，hash索引相对比较简单，只有memory/heap引擎支持Hash索引。hash索引适合key-value查询，通过hash索引要比通过B-tree索引查询更为迅速。hash索引不适合范围查询，类如<、>、=这类操作。使用memory索引并且where条件中不使用=作为检索条件，那么也是不会使用索引的。

B-tree索引比价复杂，下面详细分析mysql是如何利用B-tree索引的。

B-tree索引是最常见的索引，构造类似二叉树，能够根据键值提供一行或者一行集的快速访问，通常只需要很少的读操作就可以找到正确的行，不过B-tree中的B并不是二叉树而是代表平衡树（balanced）。

可以利用B-tree索引进行全文关键字，关键字范围和关键字前缀查询。

alter table rental drop index rental_date;
alter table rental and index idx_rental_date(rental_date,inveentory_id,customer_id);

1.mysql中能够使用索引的典型场景

匹配全值，对索引中的所有列都指定具体值。类如，租赁表rental中通过指定出租日期rental_date+库存编号inventory_id+客户编号customer_id的组合条件查询，从执行计划的key和extra两个字段的值看到优化器使用了复合索引idx_rental_date;

explain输出的结果中字段type为const，表示常量。字段key为idx_rental_date，表示优化器使用了符合索引进行检索。

匹配值得范围查询，对索引的值能够进行范围查找。例如，检索租赁表rental中的客户编号customer_id在指定范围内的记录。

通过explain分析得，type为range说明优化器选择了范围查询，索引key为idx_fk_customer_id，而且extra列为using where，表示优化器出了使用索引来加速查询还需要根据索引徽标查询数据。

匹配最左前缀，仅仅使用索引中的最左列进行查找，比如在三个字段上的联合索引能够被包含最左的列等值利用到，但是不能被后边列的组合利用。最左匹配原则是mysql的B-tree索引使用的首要原则。

匹配列前缀，仅仅用用索引的第一列，并且包含所有的第一列的开头一部分。例如现在查询标题title为african来投的电影信息，从执行计划能清晰的看到idx_title_desc_part索引被利用上了。

如果使用索引，并且column is null就会使用索引，例如

select * from payment where rental_id is null;

index condition pushdown特性进一步优化了查询，pushdown表示操作下放，如果extra为using index condition那么就表示使用了操作下放的优化。

存在索引，但不使用索引的情况

 select * from actor where last_name like ‘%123’；

对于这种检索，一般推荐使用全文索引，或者先扫描二级索引idx_last_name获得满足条件的last_name like ‘%123’对应的actor_id,然后根据组件回表去查询记录，这样就避免了扫描全表产生的大量IO操作。

select * from (select actor_id from actor where last_name like ‘%123’) a,actor b where a.actor_id=b.actor_id ;

数据类型出现隐式类型转化的时候不会使用索引，特别是列类型为字符串的时候。

select * from actor where last_name = 1；
select * from actor where last_name = ‘1’

复合索引情况下，假如查询条件不包含索引列的最左部分，是不会使用符合索引的。

如果mysql估计是用索引比全表扫描慢，则不使用索引。

用or分隔开的条件，如果or前的条件中的列中有索引，而后边的列中没有索引，那么设计的索引都不会被使用。