MySQL的索引分类

什么是索引

索引：帮助存储引擎快速获取数据的一种数据结构，形象一点就是索引是数据的目录，相当于当我们从书籍中查找任务时，通过目录进行查找

索引的分类

按数据结构分类：B+tree索引，Hash索引，Full-text索引

按物理存储分类：聚簇索引(主键索引)、二级索引(辅助索引)

按字段特性分类：主键索引、唯一索引、普通索引、前缀索引

按字段个数分类：单列索引，联合索引

按数据结构分类

InnoDB引擎支持的索引：B+tree索引，Full-text索引，不支持Hash索引，但是在内存结构中有一个自适应hash索引

MyISAM引擎支持的索引：B+tree索引，Full-text索引

Memory引擎支持的索引：B+tree索引，Hash索引

索引列的选择

1. 如果有主键，默认会使用逐渐作为聚簇索引的索引键(KEY)

2. 如果没有主键，就会选择第一个不包含NULL值的唯一列作为聚簇索引的索引键(KEY)

3. 在上面两个都没有的情况下，InnoDB将自动生成一个隐式自增id列作为聚簇索引的索引键

4. 其他索引都属于辅助索引，也被称为二级索引或非聚簇索引，创建的主键索引和二级索引默认使用的时B+tree索引

按物理存储分类

从物理存储角度来看，索引分为聚簇索引(主键索引)、二级索引(辅助索引)

主键索引的B+ Tree和二级索引B+Tree的区别：

1. 主键索引的B+Tree的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的B+Tree的叶子节点里

2. 二级索引的B+Tree的叶子节点存放的是主键值，而不是实际数据

在查询时，如果使用了二级索引，并且查询的数据能在二级索引里直接查到的，不需要回表，这个过程就是覆盖索引，如果查找的数据不在二级索引里，就会先检查二级索引，找到对应的叶子节点，获取到逐渐后，再通过检索主键索引，查找到需要的数据，这个过程就是回表；

按字段特性分类

从字段忒选哪个角度来看，索引分为：主键索引，唯一索引，普通索引，前缀索引

主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值

create table teble_name(
    ....
    primary key (index_column_1) using Btree
);

唯一索引

唯一索引建立在UNIQUE字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值

创建唯一索引方式

create table teble_name(
    ....
    unique key (index_column_1,index_column_2)
);

建表后，如果要创建唯一索引，可以使用下面这条命令

create unique index index_name
on table_name(index_column_1,index_column_2);

普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为UNIQUE

创建普通索引

create table teble_name(
    ....
    index (index_column_1) 
);

建表后，如果要创建普通索引，可以使用下面这条命令

create index index_name
on table_name(index_column_1,index_column_2);

前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为char，varchar，binary，varbinary的列上

优点：为了减少索引占用的存储空间，提升查询效率

create table teble_name(
    column_list,
    index (column_name(length))
);

建表后，创建前缀索引

create index index_name
on table_name(column_name(length));

按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引(复合索引)

建立在单列上的索引称为单列索引，比如主键索引

建立在多列上的索引称为联合索引

联合索引

将多个字段作为索引

例如：有一个商品表，表内字段：商品id，商品name，商品制造商，我们将商品id和name作为联合索引

当我们查询商品id =1，name为喜之郎果冻

我们查询时会先按id进行排序，再按照name进行排序，这也就是最左匹配原则，也就是按照最左优先的方式进行索引的匹配，如果不遵循最左匹配原则，联合索引就会失效

举例：

创建了一个a,b,c联合索引，如果查询条件是以下几种，就可以匹配上联合索引

1. where a=1 and b =2;

2. where b=2 and a=3 and c=1;

因为有查询优化器，所以where语序中a字段的顺序并不重要

下面几种查询方式会让联合查询失效

1. where b =2;

2. where c=1 and b =3;

何为失效

因为联合索引是按照a，b，c，先按照a排序，在a相同的情况下再按照b排序，c以此类推，所以b和c是全局无序的，局部有序的，如果没有遵循最左匹配原则的情况下，是无法利用到索引的

联合索引范围查询

注意：并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询

联合索引的最左匹配原则，在遇到范围查询(如 > , < )的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

注意：对于>=、<=、between、like前缀匹配的范围查询，并不会停止匹配