mysql索引(二)

2、索引有哪些分类？

索引的分类要按照不同的角度去进行分类：

1、从数据结构的角度可以分为B+树索引、哈希索引、FULLTEXT索引、R-Tree索引（用于对GIS数据创建SPATIAL索引）

2、从物理存储角度可以分为聚簇索引和非聚簇索引

3、从逻辑角度可以分为主键索引、普通索引、唯一索引、组合索引

3、聚簇索引与非聚簇索引

在MYSQL的innodb存储引擎中，数据在进行插入的时候必须要跟某一个索引列绑定在一起进行存储，如果有主键，那么选择主键，如果没有主键，那么选择唯一键，如果没有唯一键，那么系统会生成一个6字节的rowid进行存储，因此：

跟数据绑定存储的索引称之为聚簇索引

没有跟数据绑定存储的索引称之为非聚簇索引

一张表中只有一个聚簇索引，其他非聚簇索引的叶子节点中存储的值为聚簇索引的列值

4、回表、索引覆盖、最左匹配原则、索引下推

（1）回表

回表表示使用非聚簇索引时，数据库引擎会先根据普通索引找到匹配的行，然后根据叶子节点中存储的聚簇索引的值去聚簇索引的索引树中查找整行记录的过程。例如：

有一张表有如下字段：id，name，age，gender，address，其中id是主键，name是普通索引

那么要进行如下SQL语句的查询：

select * from table where name = 'zhangsan';

上述SQL语句的查找过程是：先根据name的值去name的索引树上进行检索，找到匹配的记录之后取出id的值，然后再根据id的值去id的B+树上检索整行的记录，在这个过程中，查找了两棵树，多进行了棵树的IO，因此效率比较低，在生产环境中应该尽量避免回表

（2）索引覆盖

索引覆盖是指一个索引包含了查询所需要的所有数据，从而在查询中无需回表从原表中获取数据

假设有一张表，表中有以下字段：id，name，age，gender，address，其中id是主键，name是普通索引

那么要进行如下SQL语句的查询：

select id,name from table where name = 'zhangsan';

查找过程如下：在name的索引树上包含了要查询的所有字段，所以直接通过name字段去name的B+树上检索对应的记录即可，不需要找到id之后再去id的B+树上检索数据

索引覆盖可以提高查询的性能，所以在生产环境做SQL优化的时候，可以考虑索引覆盖

（3）最左匹配原则

最左匹配原则主要适用于组合索引，指的是多个列值进行匹配的时候要严格遵循从左到右的顺序，否则会导致索引失效

假设有一张表，表中有以下字段：id，name，age，gender，address
id是主键，(name,age)是组合索引

1、Select * from table where name = 'zhangsan' and age = 10;
2、Select * from table where name = 'zhangsan';
3、Select * from table where age = 10;
4、Select * from table where age = 10 and name = 'zhangsan';

上述的四条语句中，1,2,4都可以用到组合索引，3用不到，但是很多同学会有疑问，为什么第四条会用到，明明不符合最左匹配原则的顺序，这里需要注意，如果把第四条SQL语句的条件换一下顺序，会影响最终的查询结果吗？答案是不会的，所以mysql中的优化器会进行优化，调整条件的顺序

（4）索引下推

ICP是针对mysql使用索引从表中检索行的情况进行优化，如果没有ICP，那么存储引擎会根据索引来定位到记录，然后将结果返回给mysql的server，然后在server上对where条件进行筛选。在启用ICP之后，如果where条件的一部分可以通过使用索引中的列来求值，那么mysql会把这部分的where条件筛选下推到存储引擎中。

使用索引下推的时候会有以下的条件：

1、当需要访问完整的行记录时，ICP用于range、ref、eq_ref和ref_or_null访问方法

2、ICP可以用于innodb和myisam表，包括分区的innodb表和myisam表

3、对于innodb表，ICP仅用于二级索引。ICP的目标是减少整行读取的次数，从而减少IO操作

4、在虚拟列上创建的二级索引不支持ICP

5、引用子查询的条件不能下推

6、引用存储函数的条件不能下推

7、触发器条件不能下推

8、不能将条件下推到包含对系统变量引用的派生表中

假设有一张表，表中有以下字段：id，name，age，gender，address，其中id是主键，(name,age)是组合索引

select * from table where name = 'zhangsan' and age = 10;

没有索引下推：mysql执行这条SQL语句的时候，会首先根据name的值去存储引擎中拉取数据，然后将数据返回到mysql server，然后在server层对age进行条件过滤，把符合条件的结果返回给客户端
有索引下推：mysql执行这条SQL语句的时候，会直接根据name和age的值去存储引擎中拉取数据，而无需在server层对数据进行条件过滤

所谓的下推指的是将条件的筛选从server层下推到存储引擎层

可以通过optizizer_switch中的index_condition_pushdown条件来是否开启，默认是开启的

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';