什么是索引
索引:帮助存储引擎快速获取数据的一种数据结构,形象一点就是索引是数据的目录,相当于当我们从书籍中查找任务时,通过目录进行查找
索引的分类
按数据结构分类:B+tree索引,Hash索引,Full-text索引
按物理存储分类:聚簇索引(主键索引)、二级索引(辅助索引)
按字段特性分类:主键索引、唯一索引、普通索引、前缀索引
按字段个数分类:单列索引,联合索引
按数据结构分类
InnoDB引擎支持的索引:B+tree索引,Full-text索引,不支持Hash索引,但是在内存结构中有一个自适应hash索引
MyISAM引擎支持的索引:B+tree索引,Full-text索引
Memory引擎支持的索引:B+tree索引,Hash索引
索引列的选择
-
如果有主键,默认会使用逐渐作为聚簇索引的索引键(KEY)
-
如果没有主键,就会选择第一个不包含NULL值的唯一列作为聚簇索引的索引键(KEY)
-
在上面两个都没有的情况下,InnoDB将自动生成一个隐式自增id列作为聚簇索引的索引键
-
其他索引都属于辅助索引,也被称为二级索引或非聚簇索引,创建的主键索引和二级索引默认使用的时B+tree索引
按物理存储分类
从物理存储角度来看,索引分为聚簇索引(主键索引)、二级索引(辅助索引)
主键索引的B+ Tree和二级索引B+Tree的区别:
-
主键索引的B+Tree的叶子节点存放的是实际数据,所有完整的用户记录都存放在主键索引的B+Tree的叶子节点里
-
二级索引的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联合索引,如果查询条件是以下几种,就可以匹配上联合索引
-
where a=1 and b =2;
-
where b=2 and a=3 and c=1;
因为有查询优化器,所以where语序中a字段的顺序并不重要
下面几种查询方式会让联合查询失效
-
where b =2;
-
where c=1 and b =3;
何为失效
因为联合索引是按照a,b,c,先按照a排序,在a相同的情况下再按照b排序,c以此类推,所以b和c是全局无序的,局部有序的,如果没有遵循最左匹配原则的情况下,是无法利用到索引的
联合索引范围查询
注意:并不是查询过程使用了联合索引查询,就代表联合索引中的所有字段都用到了联合索引进行索引查询
联合索引的最左匹配原则,在遇到范围查询(如 > , < )的时候,就会停止匹配,也就是范围查询的字段可以用到联合索引,但是在范围查询字段的后面的字段无法用到联合索引。
注意:对于>=、<=、between、like前缀匹配的范围查询,并不会停止匹配