热衷学习,热衷生活!😄

沉淀、分享、成长,让自己和他人都能有所收获!😄

一、什么是索引?索引有什么作用?

索引是一种用于快速查询和检索数据的数据结构。常用的索引数据结构有:B树、B+树、Hash表。

索引的作用就相当于目录的作用。比如:我们在查字典的时候如果没有目录,我们就只能一页一页去查找字,速度很慢,如果有目录,我们只需要查找字所在的页数,然后直接翻到那一页就可以了。

索引的优点缺点

优点
  • 索引可以让我们更快的检索出我们需要查找的数据,这也是创建索引的最主要的原因。
  • 可以通过创建唯一索引,保证数据库行数据的唯一性。
缺点
  • 创建索引和维护索引需要耗费更多的时间。当对表中的数据进行增删改时候,如果数据有索引,那么索引也需要动态的修改,会降低SQL执行效率。
  • 索引需要使用物理文件储存,会消耗一定的空间。

但是,使用索引一定能提高查询性能吗?

大多数情况下,索引查询都是比全表扫描的速度要快的,但是如果数据库的数据量不大,那么使用索引不一定能带来很大的提升。

二、索引的底层数据结构

常用的索引底层数据结构有三种:B树、B+树、Hash表。

Hash表

哈希表就是键值对(key-value)的集合,通过键(key)可以快速查找到对应的值(value),查询速度接近O(1)

哈希表通过哈希算法(也叫散列算法)计算key对应的index,然后通过index就可以得到对应的value。哈希算法有一个Hash冲突的问题,也就是多个key最后得到的index相同,通常使用链地址法结果Hash冲突的问题,就是将哈希冲突的数据存放在链表中。比如JDK1.8之前你的HashMap就是通过链地址法来解决哈希冲突,JDK1.8以后HashMap为了减少链表过长搜索时间过长引入了红黑树。

既然哈希表这么快,为什么MySQL没有使用其作为索引的数据结构呢?

主要有以下两个原因:

  1. Hash冲突问题:我们上面也提到过Hash 冲突了,不过对于数据库来说这还不算最大的缺点。
  2. **Hash表不支持顺序查询和范围查询:**这是Hash表最大的缺点,也是MySQL不将Hash表作为索引数据结构最重要的原因。

B树&B+树

B树也称B-树,全称为多路平衡查找树,B+树是B树的一种变体。

B树&B+树之间的差异
  • B树的所有节点既存放键key也存放数据data,而B+树只有叶子节点存放keydata,非叶子节点只存放key
  • B树的叶子节点都是独立的,而B+树的叶子节点都有一条引用链指向它相近的节点。
  • B树的检索过程是相当于对范围内的每个节点的关键词做二分查找法,可能还没到叶子节点检索就结束了,而B+树的检索效率就很稳定,任何查找都是从根节点到叶子节点的过程,叶子节点的顺序检索很明显。
为什么B+ 树比B 树更适合作为索引?
  1. B+树的磁盘读写代价更低

    B+树的data都存放在叶子节点,非叶子节点只存放key,而B树的分支节点既存放data也存放key,这将导致B+树的层高会小于B树,所以B+树平均的IO次数会小于B树。

  2. B+树的查询效率更稳定

    B树查询可能在非叶子节点就结束了,而B+树因为data只存放在叶子节点,所以查询必须从根节点到叶子节点,所以查询效率更稳定。

  3. B+树更便于遍历

    由于B+树的data都存放在叶子节点,非叶子节点只存放key,所以只需遍历叶子节点即可,而B树非叶子夜店也存放data,要找到具体的数据需要进行二分查找。

  4. B+树更擅长范围查询

    B+树叶子节点存放data,且data是按顺序排列的双向链表,所以范围查询更快。而B树范围只能二分查找。

  5. B+树占用内存空间小

    B+树非叶子节点不存放data比较小,在内存有限的情况下,相比于B树索引可以加载更多B+ 树索引。

三、索引类型

主键索引(Primary Key)

数据表的主键列使用的就是主键索引。

一张数据表有且只有一个主键,并且主键不能为null,不能重复。

MySQLInnoDB的表中,如果表没有设置主键时,InnoDB会自动先检查表中是否有唯一索引(Unique Key)且不存在为null的字段,如果有则选择该字段为默认的主键,如果没有InnoDB会自动创建一个6Byte的自增主键。

主键索引如下图:

二级索引(辅助索引)

二级索引又称为辅助索引,二级索引的叶子节点存储的数据是主键。当使用二级索引的时候,先通过二级索引查询到主键,然后通过主键获取数据。

当我们创建所以的时候需要选择索引类型,比如:唯一索引、普通索引、前缀索引、全文索引,这些都是属于二级索引。

  • 唯一索引(Unique Key):唯一索引也是一种约束。唯一索引的属性列不允许出现重复的数据,但是允许数据为Null,一张表允许创建多个唯一索引。使用唯一索引的目的大部分是为了保证该属性列的唯一性,而不是为了查询效率。
  • 普通索引(Index):普通索引的唯一作用就是为了快速查找数据,一张表允许创建多个普通索引,并允许数据重复和为null
  • 前缀索引(Prefix):前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引,相比普通索引建立的数据更小。
  • 全文索引(Full Text):全文索引主要是为了检索大文本数据中的关键字信息,是目前搜索引擎数据库使用的一种技术。

二级索引如下图:

四、聚簇索引和非聚簇索引

聚簇索引

聚簇索引是索引结构和数据存放在一起的索引,主键索引属于聚簇索引。

MySQL中,InnoDB引擎的表的.idb文件就包含了该表的索引和数据,该表的索引B+树非叶子节点存放索引,叶子节点存放索引和索引对应的数据。

聚簇索引的优点
  • 聚簇索引的查询速度非常快,因为B+树本就是一颗多平衡二叉树,叶子节点存放索引和数据,非叶子节点存放索引,且叶子节点也是有序的,定位到索引就可以得到数据。
聚簇索引的缺点
  • **依赖有序的数据:**因为B+树是多路平衡树,如果索引的数据不是有序的,那么就需要在插入时排序,如果数据是整数类型还好,否则类似字符串或者UUID这种又长又难比较的数据,插入或者查找的速度肯定慢。
  • 更新代价大:如果对索引列的数据修改时,那么对应的索引也需要修改,而且聚簇索引的叶子节点还存放这数据,修改代价很大,所以对于主键索引来说,主键一般是不可以被修改的。

非聚簇索引

上面说到的二级索引(辅助索引)都是非聚簇索引,非聚簇索引叶子节点只存储主键值。首先通过二级索引找到主键值,再通过主键索引找到数据。

非聚簇索引的优点
  • 更新代价小:因为非聚簇索引的叶子节点值存放索引不存放数据,所以更新代价小。
非聚簇索引的缺点
  • 跟聚簇索引一样也是依赖有序的数据。
  • 可能会二次查询(回表):这应该是非聚簇索引最大的缺点,当通过索引查询到对应的主键时,可能还需要根据主键再到表中查询。

聚簇索引和非聚簇索引如下图:

非聚簇索引一定要回表查询吗(覆盖索引)?

举个栗子,用户准备使用SQL查询用户名,而用户名字刚好创建了索引,查询SQL如下:

SELECT name FROM TABLE WHERE name = 'zhangsan';

那么这个索引的key本身就是name,查询对应的name直接返回就行了,无需返回表查询。

即使是 MYISAM 也是这样,虽然 MYISAM 的主键索引确实需要回表, 因为它的主键索引的叶子节点存放的是指针。但是如果 SQL 查的就是主键呢?

SELECT id FROM TABLE WHERE id = 1;

主键索引本身的 key 就是主键,查到返回就行了。这种情况就称之为覆盖索引了。

五、覆盖索引

如果一个索引覆盖所有需要查询的字段,我们就称之为覆盖索引。在InnoDB存储引擎中,如果不是主键索引,叶子节点存储的是主键,最终要是要回表,这样就会比较慢,覆盖索引就是可以把索引直接返回不需要做回表操作。

覆盖索引即需要查询的字段正好是索引的字段,那么直接根据该索引,就可以查到数据了, 而无需回表查询。

如主键索引,如果一条 SQL 需要查询主键,那么正好根据主键索引就可以查到主键。

再如普通索引,如果一条 SQL 需要查询 name,name 字段正好有索引, 那么直接根据这个索引就可以查到数据,也无需回表。

覆盖索引如下图:

六、联合索引

联合索引就是使用表中的多个字段创建的索引,也叫组合索引或者复合索引。

最左前缀匹配原则

最左前缀匹配原则指的是,在使用联合索引时,MySQL会根据联合索引中的字段顺序,从左到右依次到查询条件中去匹配,如果查询条件中存在与联合索引中最左侧字段相匹配的字段,则就会使用该字段过滤一批数据,直至联合索引中全部字段匹配完成,或者在执行过程中遇到范围查询,如><between已%开头的like查询等条件才会停止匹配。

所以我们在使用联合索引时,可以将区分度高的字段放在最左边。

七、索引下推

索引下推是MySQL5.6版本中提供的一项索引优化功能,可以在非聚簇索引遍历过程中,对索引中包含的字段先做判断,过滤掉不符合条件的记录,减少回表次数。

八、创建索引的注意事项

  1. 选择合适的字段创建索引

    • 不为NULL字段:索引字段的数据应该尽量不为NULL,因为对于数据为NULL的字段,数据库比较难优化。如果字段频繁被查询,但又避免不了为NULL,建议使用0,1,ture,false这样语义较为清晰的短值或者短字符作为替代。
    • **被频繁查询的字段:**我们创建索引的字段应该是查询操作非常频繁的字段。
    • **被作为条件查询的字段:**被作为WHERE条件查询的字段,应该被考虑建立索引。
    • **频繁需要排序的字段:**索引已经排序,这样查询可以利用索引的排序,加快排序查询时间。
    • **被经常频繁用于连接的字段:**经常用于连接的字段可能是一些外键列,对于外键并一定要建立外键,只是说该列涉及到表与表的关系。对于频繁被连接查询的字段,可以考虑建立索引,提高多表连接查询的效率。
  2. 被频繁更新的字段应该慎重建议索引。

    虽然索引能带来查询上的效率,但是维护索引的成本也是不小的。如果一个字段不被经常查询,反而被经常修改,那么就更不应该在这种字段上建立索引了。

  3. 尽可能的考虑建立联合索引而不是单列索引。

    因为索引是需要占用磁盘空间的,可以简单理解为每个索引都对应着一颗B+树。如果一个表的字段过多,索引过多,那么当这个表的数据达到一个体量后,索引占用的空间也是很多的,但修改索引时,耗费的时间也是很多的。如果是联合索引,多个字段在一个索引上,那么将会节约很大磁盘空间,且修改数据的操作效率也会提升。

  4. 注意避免冗余索引。

    冗余索引指的是索引的功能相同,能够命中索引(a, b)就肯定能命中索引(a) ,那么索引(a)就是冗余索引。如name,cityname这两个索引就是冗余索引,能够命中前者的查询肯定是能够命中后者的 在大多数情况下,都应该尽量扩展已有的索引而不是创建新索引。

  5. 考虑在字符串类型的字段上使用前缀索引代替普通索引

    前缀索引仅限于字符串类型,较普通索引会占用更小的空间,所以可以考虑使用前缀索引代替普通索引。

九、使用索引的一些建议

  • 对于中到大型表索引都是非常有效的,但是特大型表的话维护开销会很大,不适合建索引。
  • 避免 where 子句中对字段施加函数,这会造成无法命中索引。
  • 在使用 InnoDB 时使用与业务无关的自增主键作为主键,即使用逻辑主键,而不要使用业务主键。
  • 删除长期未使用的索引,不用的索引的存在会造成不必要的性能损耗MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用。
  • 在使用limit offset 查询缓慢时,可以借助索引来提高性能。

十、MySQL 如何为表字段添加索引

  1. 添加 PRIMARY KEY(主键索引)

    ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );
    
  2. 添加UNIQUE(唯一索引)

    ALTER TABLE `table_name` ADD UNIQUE ( `column` );
    
  3. 添加 INDEX(普通索引)

    ALTER TABLE `table_name` ADD INDEX index_name ( `column` );
    
  4. 添加 FULLTEXT(全文索引)

    ALTER TABLE `table_name` ADD FULLTEXT ( `column`);
    
  5. 添加多列索引

    ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
    

参考:https://javaguide.cn/database/mysql/mysql-index.html