MySQL索引，以和索引常见失效的情况

1、MySQL索引数据结构

1、为什么使用B 树

B Tree(B-Tree变种)：
1、非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引；
2、叶子节点包含所有索引字段；
3、叶子节点用指针连接，提高区间访问的性能**（方便范围查询）**；

2、为什么不使用hash结构

1、对索引的key进行一次hash计算就可以定位出数据存储的位置
2、很多时候Hash索引要比B 树索引更高效
3、仅能满足 “=”，“IN”，不支持范围查询**（与B 树相比不能范围查询）**
4、hash冲突问题

3、B树

1、叶节点具有相同的深度，叶节点的指针为空
2、所有索引元素不重复
3、节点中的数据索引从左到右递增排列

2、聚簇索引和非聚簇索引

1、聚簇索引

InnoDB索引实现(聚集)
1、表数据文件本身就是按B Tree组织的一个索引结构文件；
2、聚集索引-叶节点包含了完整的数据记录；
3、建议InnoDB表必须建主键，并且推荐使用整型的自增主键；

   1、如果不建主键的话，mysql会自动维护一列没有重复值的列为主键，如果没有就
     维护一列隐藏为主键，无疑加大的MySQL的工作量。
   2、如果主键自增，mysql在插入的时候会默认往后面连续插入；如果不是连续的话，插
   入的时候可能会引起页的分裂和平衡，会影响性能。

4、非主键索引结构叶子节点存储的是主键值。(一致性和节省存储空间)
如果不是主键索引的话，叶子节点存放的都是主键值，然后再去通过主键去查询。

2、非聚簇索引

MyISAM索引文件和数据文件是分离的(非聚集)，也直接点存放的是地址值，还需要再去(.MYD)文件中去找相应地址值的数据。

3、联合索引

最左前缀原则：只有按照索引建立字段的顺序去查才会走索引，因为底层的联合索引是按照顺序排好，否则不会走索引。

3、索引失效是场景

1、查询条件中，没有包含给定字段最左边的索引字段，索引会失效。
2、使用select *查询所有列的数据，大概率会查询非索引列的数据，非索引列不会走索引，查询效率非常低。
如果select语句中的查询列，都是索引列，那么这些列被称为覆盖索引。这种情况下，查询的相关字段都能走索引，索引查询效率相对来说更高一些。
3、索引列上有计算,索引会失效。
4、sql语句的查询条件中，需要使用函数，也会导致索引失效。
5、字段类型不同，也可能导致索引失效。
如果是varchar类型的传入的是int型的数据类型就会失效；
如果是int型的传入字符串类型的就不会失效。
根据mysql官网上解释，字符串’1’、’ 1 '、'1a’都能转换成int类型的1，也就是说可能会出现多个字符串，对应一个int类型参数的情况。那么，mysql不知道该把int类型的1转换成哪种字符串，用哪个索引快速查值。
6、like左边包含%，索引失效。
7、列对比，会使索引失效。
where a = b; a和b都有索引，但是索引也会失效。
8、使用了or关键字，那么它前面和后面的字段都要加索引，不然所有的索引都会失效。
9、in和exists都可以走索引，有时候使用in关键字时性能不好，这时就能用exists关键字优化sql了，该关键字能达到in关键字相同的效果。
not in和not exists都不可以走索引
10、
1、哪些情况走索引？
order by后面的条件，也要遵循联合索引的最左匹配原则、
order by还能配合where一起遵循最左匹配原则、
order by后面如果包含了联合索引的多个排序字段，只要它们的排序规律是相同的（要么同时升序，要么同时降序），也可以走索引。
2、哪些情况不走索引？
如果order by语句中没有加where或limit关键字，该sql语句将不会走索引。
对不同的索引做order by，也会失效。
不满足最左前缀原则的。
不同的排序，一个字段是用的升序，另一个字段用的降序，最终会导致索引失效。

这篇好文章是转载于：学新通技术网