MySQL中对于索引的理解

1、对于索引的理解

索引是帮助MySQL高效获取数据的数据结构。
在MySQL中，索引的底层使用hash索引或者是B+树索引。InnoDB存储引擎默认B+树索引。
索引的出现是为了提高数据的查询效率，就像书的目录一样。一本书500页，如果你想快速找到其中的某一个知识点，在不借助目录的情况下，你可能需要找好一会儿。同样，对于数据库而言，索引其实就是它的“目录”。
与此同时，索引也会带来很多负面影响：创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用物理空间，每个索引也需要占用物理空间；当对表进行增删改查的时候，索引也需要动态维护。
建立索引的原则：
（1）在最频繁使用的、用以缩小查询范围的字段上建立索引；
（2）在频繁使用、需要排序的字段上建立索引；
不适合建立索引的情况：
（1）对于查询中很少涉及的列或者重复值很多的列，不宜建立索引；
（2）对于一些特殊的数据类型，不宜建立索引，比如：文本字段（text）等。

2、索引的分类

按照物理实现方式，可分为聚簇索引和非聚簇索引，其中非聚簇索引又称为二级索引或者辅助索引。
按照底层数据结构角度可以分为树索引（时间复杂度O(log(n))）和Hash索引（时间复杂度O(log(1))）.

2.1聚簇索引

聚簇索引是一种数据存储方式（所有的用户记录都存储在叶子结点），也就是所谓的索引即数据，数据即索引。“聚簇”表示数据行和相邻的键值聚簇在一起。如下图

聚簇的优点和缺点

优点： 1、数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快；
2、聚簇索引对于主键的排序查找和范围查找速度非常快； 缺点：
1、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键；
2、更新主键的代价很高，因为会导致被更新的行移动。因此，对于InnoDB表，一般定义主键为不可更新。 限制：
1、对于MySQL数据库目前只有InnoDB数据引擎支持聚簇索引，而MyISAM并不支持聚簇索引；
2、由于数据的物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。一般情况就是该表的主键。
3、为了充分利用聚簇索引的聚簇特征，所以InnoDB表的主键尽量选用有序的顺序ID。

2.2非聚簇索引（二级索引）

由于聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该怎么办呢？肯定不能是从头到尾沿着链表依次遍历记录一遍。
答案：可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。比方说我们用C2列作为数据页、页中记录的排序规则，再建一棵B+树，效果如下图所示，（此时叶子节点存储的不是用户记录，第二次按主键查找时才是）

【补充知识：回表；根据这个以C2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据C2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程就称为回表。也就是根据C2列的值查询一条完整的用户记录需要使用到2棵B+树！】
二级索引需要一次回表操作，不能直接把完整的用户记录放到叶子节点上吗？
答：如果把完整的用户记录放到叶子结点是可以不用回表，但是太占地方了。相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就太浪费空间了。

聚簇索引和二级索引的区别？

1、聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。
2、一个表只能有一个聚簇索引，因为只能有一种排序存储方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据索引。
3、使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低。

3、索引失效案例

3.1、最佳左前缀法则
MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
注：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

3.2、索引列参与运算、函数、类型转换
运算：
第一条使用stuno+1没办法使用上索引；第二条可以使用上索引。
因为数据库需要全表扫描出所有的id字段值，然后对其计算，计算之后再与参数值900000进行比较。
建议的使用方式是：先在内存中进行计算好预期的值，或者在SQL语句条件的右侧进行参数值的计算。

函数：
索引列使用了函数，导致索引失败，和上一种原因一样，因为数据库先要全表扫描，获取数据之后再进行截取、计算，导致索引失效。

explain select * from t_user where SUBSTR(id_no,1,3) = '100';
1

类型转换
第一条索引失效；第二条使用上了索引；（注：name是字符串类型）
当索引列是字符串时，如果传入的条件参数是整数，会先转换成浮点数，再全表扫描，导致索引失效。
因为在MySQL里“1”，“ 1”，“1a”，“01”这样的字符串转成数字int后都是1。

3.3、错误的Like使用
%是模糊查找，放在第一位会造成全局遍历。

3.4、范围条件右边的列索引失效
分析：索引的建立是age----classId-------Name；在查询过程中，由于classId是范围查找，所以会导致name索引失效。

3.5、不等于（!= 、<>）索引失效

3.6、前后存在非索引列，索引失效

4、索引下推

索引下推全称是索引条件下推：Index Condition PushDown：ICP
索引下推是MySQL5.6中的新特性，是一种在存储引擎层使用索引过滤数据的优化方式，一般使用在联合索引中，并且其中有一个索引或者多个索引失效的情况。

案例：

数据库先找到第一个字母大于’z’的索引，存储到表中，可能会产生分页等等操作，然后在去比对第二个字母是不是’a’；
而索引下推是指，在找到第一个字母大于’z’的时候，还会去比对第二个字母，过滤数据，减少了系统访问次数。