MySQL索引我终于明白了

索引是什么

MySQL索引是一种数据结构，用于加快数据库查询的速度和性能
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度

MySQL索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。

打个比方，如何合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字

索引的分类

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

每一种存储引擎支持的索引类型不一定相同，以下拿InnoDB、MyISAM、Memery举例

	B+tree索引	Hash索引	Full-Text索引
InnoDB	支持	不支持	不支持
MyISAM	支持	不支持	支持
Memory	支持	支持	不支持

InnoDB是现在MySQL的默认存储引擎，B+Tree也是MySQL存储引擎采用最多的索引

在创建表时，InnoDB会根据不同的场景选取不同的列作为索引：

• 若有主键，则默认会使用主键作为聚簇索引的索引键
• 若无主键，就会选择第一个不为null值的唯一列作为聚簇索引的索引键
• 若以上两个都没有，则会默认自动生成一个隐式自增id列作为聚簇索引的索引键

其他的索引都属于辅助索引，即二级索引或非聚簇索引。创建的主键索引和二级索引默认的索引类型都是B+tree类型

按照物理存储分类：聚簇索引（主键索引）、二级索引（辅助索引）

主键索引和二级索引的区别：

• 主键索引B+tree的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的B+tree的叶子节点里；
• 二级索引B+tree的叶子节点存放的是主键值，而不是实际记录

所以，在查询时，使用了二级索引，若是查询的数据在二级索引中可以查询到，那么就不需要回表，这个过程就叫做覆盖索引；若在二级索引中查询不到将要查询的数据，则会先检索二级索引，找到对应的叶子节点，获取的相对应的主键值，然后再检索主键索引，就能查询到数据了，这个过程叫做回表.若读取二级索引需要三次I/O操作，再检索主键索引又需要三次I/O操作,这个过程就会大大减慢查询效率

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

主键索引

主键索引就是建立再主键列上的索引,通常在建立表的时候一起创建,一张表最多有一个主键,索引列的值不允许有null值

创建方法如下:

create table (
    ...
    primary key (index_column_1) using btree
);

唯一索引

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

在创建表时,创建唯一索引的方式如下

create table (
    ...
    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(
    ...
    index(index_column_1,index_column_2,...)
);

或者在建表后,创建普通索引方式如下:

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

前缀索引

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

使用前缀索引的目的是为了减少索引占用的存储空间,提升查询效率

在建表时创建前缀索引方式如下:

create table{
    ...
    index(column_name(length))
);
    

建表后建立前缀索引方式如下:

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

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

• 建立在单列字段上的索引,称之为单列索引,如主键索引
• 建立在多列字段上的索引,称之为联合索引

联合索引

联合索引就是通过将多个字段组合成一个索引

创建方式如下:

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

联合索引采用最左匹配原则

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

• select * from table_name where a = 1
• select * from table_name where a = 1,b = 1
• select * from table_name where a = 1,c = 1
• select * from table_name where a = 1,b = 1,c = 1

因为有查询优化器,所以a字段在where子句里的顺序并不重要

建立联合索引时,要把区分度大的字段排在前面,这样区分度大的字段就有可能被更多的SQL语句使用到

索引的缺点

索引的优点是提高查询效率,当然也有缺点,如下所示

• 索引需要占用额外的存储空间
• 对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能
• 过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引