Mysql索引原理


总所周知,Mysql的核心知识都是围绕索引展开的,如果没有索引,Mysql就和普通IO存数据没有任何区别了。了解索引,就能够在工作和面试中应对大部分数据库的问题,让我们开始吧。

存储引擎

最常见的两种存储引擎是InnoDB和MyIsam。

InnoDB

InnoDB是一种支持事务和外键行级锁的存储引擎。它使用**多版本并发控制(MVCC)**来实现事务的隔离性。

MVCC(Multi-Version Concurrency Control)多版本并发控制。核心思想是为每个事务创建一个独立的版本,确保在多个并发事务修改数据库时数据的一致性和隔离性。

在MVCC中,当一个事务要修改一行数据时,它会复制一份原始数据,称为快照(Snapshot)。在事务中查询一行数据时,它只能查到它的快照版本。如果这个过程中其他事务对这行数据进行了修改,这些修改对当前事务是不可见的。这样可以确保事务之间互不干扰,保证数据的一致性和隔离性。

MVCC实现机制,是通过在每行记录上创建一个隐藏的“行版本号”和一个“系统版本号”来实现的。当一个事务启动时,它会获得一个特定的系统版本号,并只能看到已经存在于此版本号之前的行版本。这样可以避免读取到其他并发事务中修改的数据,保证数据的一致性和隔离性。

同时,MVCC使用锁机制来防止并发事务同时对同一行数据进行修改。对一条数据读写需要获得一个行级锁,分为读锁和写锁。其中写锁会与其他锁互斥,必须等待前一个写锁释放,后续获取锁的操作才会成功,这用于保证数据的一致性。

MyISAM

MyISAM是一种表级锁的存储引擎。

表级锁特点是:降低写的效率,提升读的效率。原因如下:

  • 减少锁冲突:表级锁可以减少锁冲突的可能性,因为当多个事务同时访问同一张表时,只有一个事务可以获取到锁,其他事务则需要等待。这样可以避免多个事务同时读取或修改同一行数据,从而减少锁竞争和锁冲突,提高并发处理能力。

  • 减少锁开销:相对于行级锁,表级锁的锁开销更小。在使用行级锁时,每个事务需要维护自己的锁,因此需要更多的内存和CPU资源来处理锁冲突和锁释放。而使用表级锁时,只需要维护一张表的锁,可以减少锁开销和消耗的系统资源。

  • 优化查询计划:当一个事务锁定了一张表时,MySQL可以根据锁的信息来优化查询计划,从而提高查询效率。例如,如果一个查询需要扫描整个表,MySQL可以使用全表扫描来加快查询速度,因为此时不会有其他事务对该表进行修改操作。

索引的分类

逻辑上分为5类

普通索引(NORMAL)

字段值可以重复,也可以为null。

唯一索引(UNIQUE)

顾名思义,不允许有重复值,但允许有null值,并且可以有多个null值。

唯一索引与普通索引基本相同,唯一区别是如果插入的值重复了会报错

主键索引(PRIMARY)

只能用于主键字段的索引,必须是非空且唯一的。一个表只能有一个主键索引,一个主键索引可以包含多个字段。

如果不指定主键索引:

  1. 自动给一个没有null值的唯一索引创建主键索引
  2. 如果没有,将会给默认的RowId创建主键索引,他是一个int型,自动递增的隐藏字段

全文索引(FULLTEXT)

只能对字符串字段(char、varchar、text等)创建全文索引。
全文检索比较占空间,匹配效率也不是特别高。很少使用,一般用其他全文检索(如ElasticSearch)中间件代替。

空间索引(SPATAIL)

只能对地理类型字段(geometry)创建。使用的比较少。

也可以根据索引的字段数,分为单列索引和复合索引。上面的所有类别索引都可以创建为符合索引。


索引方法

索引方法分为2种。

B+树索引

一个表可以有多个索引,B+树索引会为每个索引创建一颗B+树。B+树主要具有以下特点:

  • 多路平衡查找树
    多路:每个节点可以包含多个索引键,使得树很粗但是矮,减少查找路径长度,提高查找效率
    平衡:B+树是高度平衡的,叶子节点的高度差不超过1,保证查询时间的稳定性

  • 叶子节点有序
    B+树的非叶子节点不存储数据(只存索引键),只有叶子节点存储数据
    叶子节点按照关键字顺序排列,并通过指针链接在一起形成链表。这样可以方便地进行范围查询、关键字排序等操作。

B+树的这些特点使得它在数据库系统中被广泛应用,能够高效地支持数据的插入、删除和查询操作,尤其擅长支持范围查询和区间查找等操作。

回表

我们知道一个表可以有多个B+树索引,但并不是每一个B+树的索引都有数据,实际上只有主键索引有数据,非主键索引的叶子节点存储的是这条数据的主键。

包含数据的索引成为聚簇索引,不包含数据的索引成为非聚簇索引

当我们使用一个非主键索引(非聚簇索引)查找时,只能得到一个主键。必须把这个主键拿到主键索引(聚簇索引)进行二次查询,才能得到数据。这就是回表,回表会增加数据库的开销。

索引覆盖

但是有一种情况,我们是不需要回表的:即非聚簇索引的关键字,已经包含了需要查询的字段。这种情况根本不需要再走一次主键索引得到完整的数据。这就是索引覆盖,索引覆盖可以减少数据库开销。

Hash索引

哈希索引是一种基于哈希表实现的索引方式,它的原理如下:

  • 哈希函数:使用哈希函数对索引键运算得到一个固定长度的哈希值。哈希函数需要满足以下条件:

    • 能够快速计算出对应的哈希值;
    • 对于不同的索引键,计算出的哈希值应该是不同的;
    • 对于相同的索引键,计算出的哈希值应该是相同的。
  • 哈希表:哈希表存储索引键数据地址之间的对应关系。哈希表是一个数组,每个元素称为一个桶(bucket),桶实际上是一个链表,链表中存储哈希值相同的索引键。

当执行查询操作时,先计算索引键的哈希值,然后在哈希表中查找对应的桶,查找桶中哈希值相同的索引键,在链表中遍历查找对应的数据地址。

需要注意的是,哈希索引只适用于等值查询,不支持范围查询和排序操作。此外,为了避免哈希冲突,需要合理选择哈希函数和桶的数量,并且需要定期重新构建哈希表来保证查询性能和效率。

索引的使用

  1. 对于复合索引,如果查询字段不是最左边的值(1-n个都可以),将不会走索引
  2. like同理,如果模糊匹配的字段,最左边不是确定的,将不会走索引
  3. 复合索引的字段之间使用or会导致索引失效,但是在两个独立的索引字段之间使用or则不会
  4. 类型不匹配不会走索引,如字符串111,即使不加单引号也可以执行,但mysql会把它当作int处理,无法走字符串的索引
  5. 索引可以增加查询效率,但会降低增删改的效率,因为这些操作需要重新维护索引。
    是否对一个字段创建索引,需要根据具体场景来考量
  6. not in、not exist、!=、<>、in (select语句) 会导致索引失效
  7. 对字段进行运算或者函数,不会走索引
  8. 查询条件是null,不会走索引
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值