mysql索引

最新推荐文章于 2024-05-04 13:51:18 发布

-归海

最新推荐文章于 2024-05-04 13:51:18 发布

阅读量833

点赞数 24

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2301_78668865/article/details/137882997

版权

索引的分类
innodb索引与myISAM索引的区别
b+树与b树差别
为什么说b+树一般不会超过四层
索引使用的注意事项

索引的分类

索引从功能逻辑上分为普通索引，唯一索引，主键索引，全文索引
从作用字段个数分为单列索引与联合索引
从实现方式上分为聚簇索引与非聚簇索引
从存储结构上分为b+树索引与hash索引

索引的创建，既可以在创建表时增加索引，也可以使用alter table或者create index关键字在已经存在的表上创建索引

mysql8.0新特性

支持降序索引，在想要添加索引的字段后加上desc即可，索引永远跟查找是对应起来的，对于频繁需要降序查找的需求，如果我们添加的索引是降序索引，那么效率则会大大提高
添加隐藏索引操作，当我们想要删除索引时，可以先将它‘隐藏’起来，也就是并不使用它，测试一下是不是符合预期的，在进行删除索引操作，如果直接删除索引，出现了预期之外的结果，那么重建索引十分消耗性能

全文索引

通过利用分词技术分析出文本中关键词的频率与重要性，然后按照一定的算法筛选出我们想要的搜索结果，适合用于大型数据集（数据量较大的字符串类型数据）
随着大数据时代的发展，数据库层面对全文索引的支持已经力不从心，逐渐被专门的搜索引擎替代

聚簇索引

mysql数据库只有innodb存储引擎支持聚簇索引

在innodb中，b+树的叶子结点存储了整行数据的索引被称为聚簇索引，如果b+树的叶子节点只存储了主键的值，那么这种索引便是非聚簇索引，也被称为二级索引

b+树索引的创建过程

每当我们创建一个表时，都会生成一个根节点页面，这个根节点页面也就是最初的父节点页面，当我们向表中插入数据时，先会把数据存储到父节点页面当中，当父节点空间用完时，此时会将父节点中所有记录复制到一个新分配的页，然后对这个新分配的页进行页分裂的操作，而父节点则成为存储目录项记录的页

优点：

将数据内容按索引的顺序进行了物理层面的排序，这样子对于主键的排序查找和范围查找的速度非常快
相比于非聚簇索引，减少了一次回表操作，也就减少了一部分的查询成本

缺点：

插入速度严重依赖插入顺序，因此我们一般会定义一个自增的主键，减少排序的成本
修改索引的成本很高，当对聚集索引关联的键进行修改和插入操作时，数据库需要重新对数据进行物理层面的排序并修改索引
由于数据的物理存储方式只能有一种（也就是说表的物理存储实际上是按聚簇索引的顺序来进行的），所以每个表中只能有一个聚簇索引，一般情况下就是该表的主键。如果没有定义主键，那么innodb会选择一个非空的唯一索引来代替，如果没有这样的索引，那么innodb会隐式的定义一个row_id字段来作为聚簇索引

对b+树这个数据结构而言，无论是创建时的插入还是之后的查找，都需要保证目录页中索引对应字段是唯一的，否则无法判断插入或查询位置是在哪个子节点当中。字段聚簇索引通常选用主键字段来保证唯一性，对于非聚簇索引而言，则会将主键加入目录页，构成一个联合索引，当选用字段相同时，则会按主键来进行插入和查询

联合索引

联合索引是聚簇索引的一种，当我们选取多个字段作为排序规则时，此时的索引便是联合索引，联合索引会依次按给定的字段对数据进行排序

hash索引

innodb 与myisam均不支持hash索引，只有memory存储引擎显式地支持hash索引
优点：在等值判断时效率较高，只需要常数级别的时间复杂度
缺点：

hash索引数据存储是无序的的，如果进行范围查询，或者排序查询，效率较低
如果遇到较多的重复值，那么hash冲突会比较多，降低hash索引效率

虽然innodb不支持显式地hash索引，但是提供自适应hash索引，如果一个数据经常被访问，那么就会把这个数据也的地址存放到hash表当中，下次查询时，就可以直接找到这个页面所在的位置

innodb索引与myISAM索引的区别

在myisam存储引擎中，数据和索引都是分离的，没有聚簇索引，b+树叶子节点只包括主键值和数据记录地址
myisam中的回表操作是十分迅速的，因为是直接拿着地址偏移量去文件中读取数据，而innodb存储引擎需要先获取主键再去聚簇索引中查询数据
innodb要求表必须要有主键，如果没有显示指定，那么mysql系统则会自动选择一个非空唯一字段作为主键，如果没有那么mysql会为innodb表生成一个隐含字段作为主键

b+树与b树差别

b+树与b树的根本差异在与b+树的中间节点并不直接存放数据，所有的数据存于叶子结点，并用一个双向链表维护所有叶子节点

这样子的好处在于：

由于中间节点不用存放具体数据，那么就可以存放更多的节点关键字，那么b+树就可以做到比b树更矮胖，提高查询的效率
由于叶子结点有双向链表的维护，使得范围查找更加方便，效率更高

为什么说b+树一般不会超过四层

innodb存储引擎中页的大小为16kb，一般主键类型为4字节的int 或8字节的bigint，而指针大小一般是4字节，我们估算一下可以存储索引的数量为16kb/12b，那么这个值的数量级在10^ 3,三层索引页那么索引的数量级就有10^9 ，这个容量用量已经很大了

索引使用的注意事项

哪些情况适合创建索引

字段具有唯一性的限制，通过对唯一字段创建索引，那么根据索引可以很快检索的目标值

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。(来源:Alibaba)

某个字段频繁用作where中的判断条件
经常使用order by或group by操作的字段，或者加有distinct关键字的字段

哪些情况不适合创建索引

更新频繁的字段不适合创建索引
数据量小的表不适合创建索引
区分度不高的字段不要创建索引
不要建立冗余的索引，比如有些联合索引已经可是实现某些字段的查找，就没有必要再对这个字段单独建立一个索引

索引创建原则

使用数据类型长度较小的字段创建索引
使用字符串前缀创建索引，避免使用整个字符串作为索引，这样子既能节约空间，又能减少字符串的比较时间，还能大体解决排序问题。那么应该选取多长的前缀呢，可以通过前缀的区分度来判断

Alibaba《Java开发手册》:【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。说明:索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上。区分度可以使用count(distinct left(列名,索引长度)/count(*)来确定。