MySQL之索引

最新推荐文章于 2024-08-07 23:56:32 发布

努力学习的小飞侠

最新推荐文章于 2024-08-07 23:56:32 发布

阅读量1.1k

点赞数 30

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_62592925/article/details/134715910

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

MySQL之索引

在MySQL中存储大量的数据，那么数据如何组织能进行高效的数据库操作就显得尤为重要，那么索引就是帮助数据库高效获取数据的结构

索引的优缺点

优点：

索引大大减少了数据库服务器需要扫描的数据，优化了数据查询，大大提高了效率
提高数据检索的效率，通过对索引进行排序，将随机IO变为了顺序IO，降低数据库的IO成本，
提前将数据进行组织，避免数据库临时建表排序

缺点：

创建索引和维护索引都需要消耗时间，这种时间上的占用随着数据量的不断变大而变大
索引是一种数据结构，需要占用物理空间
索引大大调高了查询的效率，但查询的高效也牺牲了表更新的效率，需要动态的维护索引
如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果
对于非常小的表，大部分情况下简单的全表扫描更高效

索引的分类

根据物理存储形式划分

聚簇索引

聚簇索引可以理解为一种数据存储形式（依靠B+树实现），索引与数据存放在一起，找到索引就找到了数据；且一个表中只能有一个，如果表中存在主键那么主键索引就是聚簇索引，如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引；其余情况InnoDB会自动生成一个row_id作为隐藏的聚集索引。

优点：

1.查找速度快，因为数据和索引存放在同一个B+树中，找到索引就找到了数据，省去了回表操作

2.聚簇索引对于主键的排序查找和范围查找速度非常快

3.节省了大量的IO时间，因为数据在聚簇索引中排列是紧密的

缺点：

1.插入速度严重依赖插入的顺序，按照主键的顺序插入是最快的，否则就会导致页分裂

2.更新主键的代价大，因此一般主键设为不可更新（因为可能设计多表关联）

3.二级索引访问需要两次索引查找，第一次查找主键，第二次查找行数据

非聚簇索引（二级索引）

非聚簇索引的索引和数据时是分开的，一个表中可以有多个二级索引，使用二级索引查找数据总是需要二次查询（回表）

聚簇索引和非聚簇索引的对比

在这里插入图片描述

1.聚簇索引存储的就是数据记录，非聚簇索引存储的数据位置，非聚簇索引不会影响数据表物理存储的顺序

2.一个表只能有一个聚簇索引，因为只能有一种物理存储的方式;可以有多个非聚簇索引，也就是多个索引目录提供检索

3.聚簇索引的查询效率优秀但更新效率低

根据逻辑功能划分

普通索引

没有限制条件，用于提高查询的效率。这类索引可以添加在任何数据类型上，其值是否非空唯一由它本身完整性约束决定

唯一索引

使用UNIQUE设置唯一性索引，保证该索引的值唯一，可以声明多个

主键索引

特殊的唯一性索引 UNIQUE+NOT_NULL一个表中最多只有一个主键（因为一个表只能有一种物理存储形式）

单列索引

在表中的单个字段上创建索引。单列字段只根据该字段进行索引，一个表中可以有多个单列索引

多列索引（联合索引）

多列索引是在指索引以两个或两个以上字段进行组织，可以通过多个字段进行索引查询但是必须遵循最左前缀原则（即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效）

全文索引

搜索引擎使用的关键技术–通过分词进行匹配

空间索引

使用SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上，可以提高系统获取空间数据类型的效率。（目前只有MyISAM支持空间索引）

索引的数据结构

B-TREE

B-Tree（多路平衡搜索树）树高一层意味着多一次的磁盘I/O，下图是5阶B树

在这里插入图片描述

B-树特征：

关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；
自动层次控制；

B+TREE

在这里插入图片描述

B+树特征：

所有的数据都存储在叶子节点的数据页，数据页中的数据以单向链表组织
MySQL对B+树索引进行了优化，数据页之间通过双向链表组织，增强了区间访问的性能
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层

HASH

在这里插入图片描述

Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。也不支持任何范围查询，例如WHERE price > 100。　
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。

InnoDB引擎为什么选择B+索引结构？

B+树相比于B树，在每个节点上取消了存储的数据可以释放更多的空间来存储索引，这样构造的树结果就更矮更扁查询效率高，降低了进行IO的次数；

并且数据页间通过双向链表连接，数据页中的数据通过按照顺序组成一个单向链表，相比于Hash索引，支持区间查询以及排序操作

构造B+树结构有哪些注意事项

1.根页面的位置不动，在生成B+树的过程中经过数据的添加 -->数据页的复制–>目录页–>页分裂但根页面的位置不改变这样的话InnoDB引擎用到这个索引的话从固定的地方取出根节点的页号来访问这个索引

2.内节点目录页的唯一性，二级索引的目录项如果不添加上主键的话有可能出现不唯一的情况，如果存在太多的重复索引，那么使用索引查询的效率也会大大降低

3.一个页面至少存储两条记录

不同存储引擎索引的实现

InnoDB

InnoDB使用B+TREE存储数据，除了主键索引为聚簇索引，其它索引均为非聚簇索引。

一个表中只能存在一个聚簇索引（主键索引），但可以存在多个非聚簇索引。

InnoDB表的索引和数据是存储在一起的，.idb表数据和索引的文件

InnoDB索引优化

InnoDB中主键不宜定义太大，因为辅助索引也会包含主键列，如果主键定义的比较大，其他索引也将很大。如果想在表上定义、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

InnoDB中尽量不使用非单调字段作主键（不使用多列），因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择