侧边栏壁纸
  • 累计撰写 79 篇文章
  • 累计创建 84 个标签
  • 累计收到 7 条评论

目 录CONTENT

文章目录

MySQL学习-索引的基础篇(上)

汤圆学Java
2022-02-07 / 0 评论 / 0 点赞 / 45 阅读 / 2,319 字
温馨提示:
本文最后更新于 2022-02-07,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

前言

索引就好比书的目录,它的目的就是方便数据的快速查找;

通过索引可以直接定位到指定位置,而不用逐行去搜索;

目录

  1. 索引的常见模型
  2. 主键索引和非主键索引
  3. 自增主键
  4. 重建索引

正文

1. 索引的常见模型

常见的两种模型分别是:哈希表、B+树(InnoDB默认的索引模型);

哈希表我们都知道,是以一种键值对(key-value)的方式进行存储数据;

存储时先将一个key哈希处理获取一个位置,然后将value存储到该位置;

如果该位置已经有值了,那么就链表处理;

B+树比较复杂,简单介绍下就是一种平衡多路查找树,是InnoDB引擎默认的索引模型;细节可以参考:Mysql索引BTree、B+Tree详细分解 - 简书 (jianshu.com)

下面我们用表格列一下两者的优缺点:

索引模型哈希表B+树
优点插入快(参考hashMap),适合等值查询查询效率高
缺点范围查询慢(因为内部是无序排列的)可能产生大量的随机IO

2. 主键索引和非主键索引

  • 主键索引:

主键索引在InnoDB中也叫做聚簇索引(clustered index);

存储的是整行数据,这样通过主键索引查询数据时,可以直接返回整行数据;

一般都是把表的id字段作为主键索引;

比如:select * from t where id = 10;这里的id就是主键索引

  • 非主键索引:

非主键索引在InnoDB中也叫做二级索引(secondary index),普通索引;

存储的是主键ID值,这样通过非主键索引查询数据时,还需要回表进行查询数据;

一般是普通字段设置为非主键索引;

回表:就是通过非主键索引查询整行数据时,需要查询两次,第一次根据非主键索引获取主键ID值,第二次再根据主键ID去表中获取整行数据

比如:select * from t where k = 10;这里的k就是非主键索引

因为非主键索引需要回表操作,所以一般建议用主键索引进行查询

3. 自增主键

自增主键指的是:插入时不指定主键id的值,系统自动获取当前id最大值+1作为下一条记录的id值;

这个自增主键的优点有很多,比如:

  • 性能好:因为自增主键都是有序排列,不存在页分裂的情况;
  • 存储空间小:主键id一般为 int(4个字节)或者 bigint(8个字节),此时普通索引中存储主键的字节点就只需要4或8个字节即可;而如果是非自增主键(比如用户唯一的身份证号),那么普通索引中的字节点就会占用20字节;

因此一般都推荐用自增主键

当然也有例外的情况,比如有时候一个表只能设置一个索引,那么此时就可以将业务字段作为主键索引;

因为此时不存在其他索引,所以也就不用考虑其他索引的叶子节点大小问题(这里的其他索引就是普通索引,里面存放的是主键索引值);

此时就可以避免查询时搜索两棵树(也就是前面提到的回表操作)。

4. 重建索引

重建索引,从字面上来理解,就是重新建立一个新的索引,使得旧的索引失效;

为啥要重建索引呢?

索引可能因为删除、页分裂等原因,导致数据页有空洞,此时重建索引会创建一个新的索引,并把数据按顺序插入,这样页面的利用率最高,索引更紧凑,更省空间

页分裂:就是本来一个数据页可以存储下的数据,被分裂到了多个数据页;

比如一个数据页只能存储1000个数据,这里假设它存储的数据是1~2000之间的奇数,顺序排列;

此时如果要插入一个数据1000,那么这个数据页就会裂开,将1000以后的数据单独分配一个数据页,此时旧的数据页存储1~999之间的奇数(尾部留下了空洞),新的数据页存储1000~2000之间的奇数(包括1000);

那要怎么重建索引呢?

分情况;

如果只是普通索引,那么可以通过删除索引+新增索引来重建,命令为:

alter table T drop index k;
alter table T add index(k);

如果是普通索引+主键索引都需要重建,那么就不建议用删除+新建的方式来重建;

因为不管是删除还是新建,都会把整个表进行重建;

此时上面的普通索引的重建就白做了;

建议的做法是:直接更新整个表

alter table T engine = InnoDB;

总结

  1. 索引分为主键索引和非主键索引,非主键索引也叫做普通索引、聚簇索引;

    通过非主键索引查询整行数据时需回表操作;

  2. 主键一般推荐自增,除非业务需要,才将业务字段设置为主键不自增;

  3. 索引的重建,是为了减少页分裂,增加页面的利用率;

    如果只是重建非主键索引,则删除索引+新增索引就可以重建索引;

    如果主键索引和非主键索引都要重建,则直接alter整个表;

0

评论区