当前位置:

首页 > 编程开发 > mysql索引快的原因是什么

mysql索引快的原因是什么

索引就是通过事先排好序,从而在查找时可以应用二分查找等高效率的算法。一般的顺序查找,复杂度为O(n),而二分查找复杂度为O(log2n);当n很大时,二者的效率相差及其悬殊。Mysql作为互联网中非常热门的数据库,其底层的存储引擎和数据检索引擎的设计非常重要,尤其是Mysql数据的存储形式以及索引的设计,决定了Mysql整体的数据检索性能。我们知道,索引的作用是做数据的快速检索,而快速检索的实现的本质是数据结构。通过不同数据结构的选择,实现各种数据快速检索。在数据库中,高效的查找算法是非常重要的,因为数据

本文目录

    索引就是通过事先排好序,从而在查找时可以应用二分查找等高效率的算法。一般的顺序查找,复杂度为O(n),而二分查找复杂度为O(log2n);当n很大时,二者的效率相差及其悬殊。

    Mysql 作为互联网中非常热门的数据库,其底层的存储引擎和数据检索引擎的设计非常重要,尤其是 Mysql 数据的存储形式以及索引的设计,决定了 Mysql 整体的数据检索性能。

    我们知道,索引的作用是做数据的快速检索,而快速检索的实现的本质是数据结构。通过不同数据结构的选择,实现各种数据快速检索。在数据库中,高效的查找算法是非常重要的,因为数据库中存储了大量数据,一个高效的索引能节省巨大的时间。比如下面这个数据表,如果 Mysql 没有实现索引算法,那么查找 id=7 这个数据,那么只能采取暴力顺序遍历查找,找到 id=7 这个数据需要比较 7 次,如果这个表存储的是 1000W 个数据,查找 id=1000W 这个数据那就要比较 1000W 次,这种速度是不能接受的。

    mysql索引快的原因是什么

    一、Mysql 索引底层数据结构选型

    哈希表(Hash)

    哈希表是做数据快速检索的有效利器。

    哈希算法:也叫散列算法,就是把任意值(key)通过哈希函数变换为固定长度的 key 地址,通过这个地址进行具体数据的数据结构。

    mysql索引快的原因是什么

    考虑这个数据库表 user,表中一共有 7 个数据,我们需要检索 id=7 的数据,SQL 语法是:

    select * from user where id=7;

    哈希算法首先计算存储 id=7 的数据的物理地址 addr=hash(7)=4231,而 4231 映射的物理地址是 0x77,0x77 就是 id=7 存储的额数据的物理地址,通过该独立地址可以找到对应 user_name='g'这个数据。这就是哈希算法快速检索数据的计算过程。

    但是哈希算法有个数据碰撞的问题,也就是哈希函数可能对不同的 key 会计算出同一个结果,比如 hash(7)可能跟 hash(199)计算出来的结果一样,也就是不同的 key 映射到同一个结果了,这就是碰撞问题。解决碰撞问题的一个常见处理方式就是链地址法,即用链表把碰撞的数据接连起来。计算哈希值之后,还需要检查该哈希值是否存在碰撞数据链表,有则一直遍历到链表尾,直达找到真正的 key 对应的数据为止。

    mysql索引快的原因是什么
    mysql索引快的原因是什么

    从算法时间复杂度分析来看,哈希算法时间复杂度为 O(1),检索速度非常快。比如查找 id=7 的数据,哈希索引只需要计算一次就可以获取到对应的数据,检索速度非常快。但是 Mysql 并没有采取哈希作为其底层算法,这是为什么呢?

    因为考虑到数据检索有一个常用手段就是范围查找,比如以下这个 SQL 语句:

    select * from user where id \>3;

    针对以上这个语句,我们希望做的是找出 id>3 的数据,这是很典型的范围查找。如果使用哈希算法实现的索引,范围查找怎么做呢?一个简单的思路就是一次把所有数据找出来加载到内存,然后再在内存里筛选筛选目标范围内的数据。但是这个范围查找的方法也太笨重了,没有一点效率而言。

    所以,使用哈希算法实现的索引虽然可以做到快速检索数据,但是没办法做数据高效范围查找,因此哈希索引是不适合作为 Mysql 的底层索引的数据结构。

    二叉查找树(BST)

    二叉查找树是一种支持数据快速查找的数据结构,如图下所示:

    mysql索引快的原因是什么

    二叉查找树的时间复杂度是 O(lgn),比如针对上面这个二叉树结构,我们需要计算比较 3 次就可以检索到 id=7 的数据,相对于直接遍历查询省了一半的时间,从检索效率上看来是能做到高速检索的。此外二叉树的结构能不能解决哈希索引不能提供的范围查找功能呢?

    答案是可以的。观察上面的图,二叉树的叶子节点都是按序排列的,从左到右依次升序排列,如果我们需要找 id>5 的数据,那我们取出节点为 6 的节点以及其右子树就可以了,范围查找也算是比较容易实现。

    但是普通的二叉查找树有个致命缺点:极端情况下会退化为线性链表,二分查找也会退化为遍历查找,时间复杂退化为 O(N),检索性能急剧下降。比如以下这个情况,二叉树已经极度不平衡了,已经退化为链表了,检索速度大大降低。此时检索 id=7 的数据的所需要计算的次数已经变为 7 了。

    mysql索引快的原因是什么

    在数据库中,数据的自增是一个很常见的形式,比如一个表的主键是 id,而主键一般默认都是自增的,如果采取二叉树这种数据结构作为索引,那上面介绍到的不平衡状态导致的线性查找的问题必然出现。因此,简单的二叉查找树存在不平衡导致的检索性能降低的问题,是不能直接用于实现 Mysql 底层索引的。

    AVL 树和红黑树

    二叉查找树存在不平衡问题,因此学者提出通过树节点的自动旋转和调整,让二叉树始终保持基本平衡的状态,就能保持二叉查找树的最佳查找性能了。基于这种思路的自调整平衡状态的二叉树有 AVL 树和红黑树。

    首先简单介绍红黑树,这是一颗会自动调整树形态的树结构,比如当二叉树处于一个不平衡状态时,红黑树就会自动左旋右旋节点以及节点变色,调整树的形态,使其保持基本的平衡状态(时间复杂度为 O(logn)),也就保证了查找效率不会明显减低。比如从 1 到 7 升序插入数据节点,如果是普通的二叉查找树则会退化成链表,但是红黑树则会不断调整树的形态,使其保持基本平衡状态,如下图所示。下面这个红黑树下查找 id=7 的所要比较的节点数为 4,依然保持二叉树不错的查找效率。

    红黑树拥有不错的平均查找效率,也不存在极端的 O(n)情况,那红黑树作为 Mysql 底层索引实现是否可以呢?其实红黑树也存在一些问题,观察下面这个例子。
    红黑树顺序插入 1~7 个节点,查找 id=7 时需要计算的节点数为 4。

    mysql索引快的原因是什么

    红黑树顺序插入 1~16 个节点,查找 id=16 需要比较的节点数为 6 次。观察一下这个树的形态,是不是当数据是顺序插入时,树的形态一直处于“右倾”的趋势呢?从根本上上看,红黑树并没有完全解决二叉查找树虽然这个“右倾”趋势远没有二叉查找树退化为线性链表那么夸张,但是数据库中的基本主键自增操作,主键一般都是数百万数千万的,如果红黑树存在这种问题,对于查找性能而言也是巨大的消耗,我们数据库不可能忍受这种无意义的等待的。

    mysql索引快的原因是什么

    现在考虑另一种更为严格的自平衡二叉树 AVL 树。因为 AVL 树是个绝对平衡的二叉树,因此他在调整二叉树的形态上消耗的性能会更多。

    AVL 树顺序插入 1~7 个节点,查找 id=7 所要比较节点的次数为 3。

    mysql索引快的原因是什么

    AVL 树顺序插入 1~16 个节点,查找 id=16 需要比较的节点数为 4。从查找效率而言,AVL 树查找的速度要高于红黑树的查找效率(AVL 树是 4 次比较,红黑树是 6 次比较)。从树的形态看来,AVL 树不存在红黑树的“右倾”问题。也就是说,大量的顺序插入不会导致查询性能的降低,这从根本上解决了红黑树的问题。

    mysql索引快的原因是什么

    总结一下 AVL 树的优点:

    1. 不错的查找性能(O(logn)),不存在极端的低效查找的情况。

    2. 可以实现范围查找、数据排序。

    看起来 AVL 树作为数据查找的数据结构确实很不错,但是 AVL 树并不适合做 Mysql 数据库的索引数据结构,因为考虑一下这个问题:

    数据库查询数据的瓶颈在于磁盘 IO,如果使用的是 AVL 树,我们每一个树节点只存储了一个数据,我们一次磁盘 IO 只能取出来一个节点上的数据加载到内存里,那比如查询 id=7 这个数据我们就要进行磁盘 IO 三次,这是多么消耗时间的。所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。

    磁盘 IO 有个有个特点,就是从磁盘读取 1B 数据和 1KB 数据所消耗的时间是基本一样的,我们就可以根据这个思路,我们可以在一个树节点上尽可能多地存储数据,一次磁盘 IO 就多加载点数据到内存,这就是 B 树,B+树的的设计原理了。

    B 树

    下面这个 B 树,每个节点限制最多存储两个 key,一个节点如果超过两个 key 就会自动分裂。比如下面这个存储了 7 个数据 B 树,只需要查询两个节点就可以知道 id=7 这数据的具体位置,也就是两次磁盘 IO 就可以查询到指定数据,优于 AVL 树。

    mysql索引快的原因是什么

    下面是一个存储了 16 个数据的 B 树,同样每个节点最多存储 2 个 key,查询 id=16 这个数据需要查询比较 4 个节点,也就是经过 4 次磁盘 IO。看起来查询性能与 AVL 树一样。

    mysql索引快的原因是什么

    但是考虑到磁盘 IO 读一个数据和读 100 个数据消耗的时间基本一致,那我们的优化思路就可以改为:尽可能在一次磁盘 IO 中多读一点数据到内存。这个直接反映到树的结构就是,每个节点能存储的 key 可以适当增加。

    当我们把单个节点限制的 key 个数设置为 6 之后,一个存储了 7 个数据的 B 树,查询 id=7 这个数据所要进行的磁盘 IO 为 2 次。

    mysql索引快的原因是什么

    一个存储了 16 个数据的 B 树,查询 id=7 这个数据所要进行的磁盘 IO 为 2 次。相对于 AVL 树而言磁盘 IO 次数降低为一半。

    mysql索引快的原因是什么

    所以数据库索引数据结构的选型而言,B 树是一个很不错的选择。总结来说,B 树用作数据库索引有以下优点:

    1. 优秀检索速度,时间复杂度:B 树的查找性能等于 O(h*logn),其中 h 为树高,n 为每个节点关键词的个数;

    2. 尽可能少的磁盘 IO,加快了检索速度;

    3. 可以支持范围查找。

    4. B+树

    B 树和 B+树有什么不同呢?

    第一,B 树一个节点里存的是数据,而 B+树存储的是索引(地址),所以 B 树里一个节点存不了很多个数据,但是 B+树一个节点能存很多索引,B+树叶子节点存所有的数据。

    第二,B+树的叶子节点是数据阶段用了一个链表串联起来,便于范围查找。

    mysql索引快的原因是什么

    通过 B 树和 B+树的对比我们看出,B+树节点存储的是索引,在单个节点存储容量有限的情况下,单节点也能存储大量索引,使得整个 B+树高度降低,减少了磁盘 IO。其次,B+树的叶子节点是真正数据存储的地方,叶子节点用了链表连接起来,这个链表本身就是有序的,在数据范围查找时,更具备效率。因此 Mysql 的索引用的就是 B+树,B+树在查找效率、范围查找中都有着非常不错的性能。

    二、Innodb 引擎和 Myisam 引擎的实现

    Mysql 底层数据引擎以插件形式设计,最常见的是 Innodb 引擎和 Myisam 引擎,用户可以根据个人需求选择不同的引擎作为 Mysql 数据表的底层引擎。我们刚分析了,B+树作为 Mysql 的索引的数据结构非常合适,但是数据和索引到底怎么组织起来也是需要一番设计,设计理念的不同也导致了 Innodb 和 Myisam 的出现,各自呈现独特的性能。

    MyISAM 虽然数据查找性能极佳,但是不支持事务处理。Innodb 最大的特色就是支持了 ACID 兼容的事务功能,而且他支持行级锁。Mysql 建立表的时候就可以指定引擎,比如下面的例子,就是分别指定了 Myisam 和 Innodb 作为 user 表和 user2 表的数据引擎。

    mysql索引快的原因是什么
    mysql索引快的原因是什么

    执行这两个指令后,系统出现了以下的文件,说明这两个引擎数据和索引的组织方式是不一样的。

    mysql索引快的原因是什么

    Innodb 创建表后生成的文件有:

    • frm:创建表的语句

    • idb:表里面的数据+索引文件

    Myisam 创建表后生成的文件有

    • frm:创建表的语句

    • MYD:表里面的数据文件(myisam data)

    • MYI:表里面的索引文件(myisam index)

    从生成的文件看来,这两个引擎底层数据和索引的组织方式并不一样,MyISAM 引擎把数据和索引分开了,一人一个文件,这叫做非聚集索引方式;Innodb 引擎把数据和索引放在同一个文件里了,这叫做聚集索引方式。下面将从底层实现角度分析这两个引擎是怎么依靠 B+树这个数据结构来组织引擎实现的。

    MyISAM 引擎的底层实现(非聚集索引方式)

    MyISAM 用的是非聚集索引方式,即数据和索引落在不同的两个文件上。MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树,树的叶子节点存的是对应数据的物理地址。我们拿到这个物理地址后,就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。

    mysql索引快的原因是什么

    当我们为某个字段添加索引时,我们同样会生成对应字段的索引树,该字段的索引树的叶子节点同样是记录了对应数据的物理地址,然后也是拿着这个物理地址去数据文件里定位到具体的数据记录。

    Innodb 引擎的底层实现(聚集索引方式)

    InnoDB 是聚集索引方式,因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树,如左下图所示,而 B+树的叶子节点存储的是主键 ID 对应的数据,比如在执行 select * from user_info where id=15 这个语句时,InnoDB 就会查询这颗主键 ID 索引 B+树,找到对应的 user_name='Bob'。

    这是建表的时候 InnoDB 就会自动建立好主键 ID 索引树,这也是为什么 Mysql 在建表时要求必须指定主键的原因。当我们为表里某个字段加索引时 InnoDB 会怎么建立索引树呢?比如我们要给 user_name 这个字段加索引,那么 InnoDB 就会建立 user_name 索引 B+树,节点里存的是 user_name 这个 KEY,叶子节点存储的数据的是主键 KEY。注意,叶子存储的是主键 KEY!拿到主键 KEY 后,InnoDB 才会去主键索引树里根据刚在 user_name 索引树找到的主键 KEY 查找到对应的数据。

    mysql索引快的原因是什么

    问题来了,为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据,但是其他索引树却不存具体数据呢,而要多此一举先找到主键,再在主键索引树找到对应的数据呢?

    其实很简单,因为 InnoDB 需要节省存储空间。一个表里可能有很多个索引,InnoDB 都会给每个加了索引的字段生成索引树,如果每个字段的索引树都存储了具体数据,那么这个表的索引数据文件就变得非常巨大(数据极度冗余了)。从节约磁盘空间的角度来说,真的没有必要每个字段索引树都存具体数据,通过这种看似“多此一举”的步骤,在牺牲较少查询的性能下节省了巨大的磁盘空间,这是非常有值得的。

    在进行 InnoDB 和 MyISAM 特点对比时谈到,MyISAM 查询性能更好,从上面索引文件数据文件的设计来看也可以看出原因:MyISAM 直接找到物理地址后就可以直接定位到数据记录,但是 InnoDB 查询到叶子节点后,还需要再查询一次主键索引树,才可以定位到具体数据。等于 MyISAM 一步就查到了数据,但是 InnoDB 要两步,那当然 MyISAM 查询性能更高。

    本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系bd@zhengruan.com
    作者最新文章
    编程开发 MySQL
    相关文章 更多
    navicat导入数据库步骤详解及常见问题处理教程
    navicat导入数据库步骤详解及常见问题处理教程

    详解Navicat导入数据库的标准步骤,涵盖SQL脚本执行与CSV数据导入两种核心场景。针对导入过程中常见的字符集乱码、主键冲突及外键约束错误,提供具体的设置方法与修复建议,确保数据迁移准确无误。

    SQL主键约束和唯一约束有什么区别
    SQL主键约束和唯一约束有什么区别

    深入理解SQL PRIMARY KEY与UNIQUE约束的区别。文章从行标识与重复限制的目标出发,详解主键的非空特性、外键引用角色以及多数据库对NULL值的处理差异。结合users表实战示例,指导开发者如何根据字段稳定性与业务语义合理选择约束类型,避免设计误区。

    SQL约束有哪些类型及作用
    SQL约束有哪些类型及作用

    刚开始设计关系型数据库?了解PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、CHECK、DEFAULT等约束的作用、语法和排错方法。本文以PostgreSQL语法为主,结合用户订单场景,清晰展示如何防止脏数据并处理常见约束冲突。

    SQL游标常见错误排查:死锁与资源未释放问题
    SQL游标常见错误排查:死锁与资源未释放问题

    深入解析SQL游标导致的死锁与资源未释放问题。通过PostgreSQL与MySQL语法对比,展示如何识别长事务持锁、分析锁等待链,并提供短事务提交、异常清理及批量处理的最佳实践,确保数据库连接与锁资源及时释放。

    SQL中怎么创建和删除约束
    SQL中怎么创建和删除约束

    后端开发者在维护数据库时,常需对现有表添加主键、唯一或外键约束。若直接执行ADD CONSTRAINT,往往因存量数据不合规导致报错。本文详解操作前的数据清洗步骤、标准SQL语法及MySQL等特殊写法,帮助你在生产环境安全完成结构变更。

    SQL分组统计查询:GROUP BY与HAVING子句用法示例
    SQL分组统计查询:GROUP BY与HAVING子句用法示例

    针对 SQL 初学者的分组统计实战指南。深入解析 GROUP BY 分组机制、WHERE 与 HAVING 的执行时机差异,以及 COUNT 函数对 NULL 值的处理逻辑。通过具体代码示例演示如何正确筛选聚合结果,并排查因 JOIN 连接导致的统计数据异常,提升报表准确性。

    SQL Server与MySQL游标基本语法及完整代码示例
    SQL Server与MySQL游标基本语法及完整代码示例

    本文详细对比SQL Server和MySQL中游标的基本用法。从DECLARE声明到FETCH遍历,再到CLOSE关闭与DEALLOCATE/Handler清理,解析两者在语法结构、变量接收及异常处理上的核心差异。通过标准代码示例展示如何安全使用游标,避免资源泄漏,适用于需要进行数据库存储过程开发或跨平台迁移的开发者。

    SQL多表连接区别:INNER JOIN与LEFT JOIN的使用场景
    SQL多表连接区别:INNER JOIN与LEFT JOIN的使用场景

    深入解析SQL中INNER JOIN与LEFT JOIN的核心区别。通过users和orders表实例,展示匹配行保留与NULL补位机制,重点讲解ON与WHERE子句中放置右表过滤条件的不同后果,提供防止LEFT JOIN退化的实战技巧及行数异常排查步骤。

    SQL CHECK约束的用法与取值范围限制
    SQL CHECK约束的用法与取值范围限制

    深入理解 SQL CHECK 约束在 PostgreSQL、MySQL 等数据库中的实现差异。文章通过订单业务场景,讲解如何定义单列范围与多列逻辑约束,分析 NULL 值对布尔条件的影响,并提供修改已有表结构时的安全操作建议,帮助开发者构建更健壮的数据完整性防线。

    SQL外键约束怎么用及级联操作说明
    SQL外键约束怎么用及级联操作说明

    后端开发中,如何正确使用 SQL 外键约束来维护订单与明细表的关系?本文通过 PostgreSQL 示例,深入讲解外键的创建语法、插入顺序要求,以及 ON DELETE CASCADE、RESTRICT、SET NULL 等策略的实际影响与风险控制,助你构建健壮的数据库结构。

    查看更多
    精品专题 更多
    装机必备
    装机必备

    正软商城装机必备专区,精选办公、浏览器、安全防护、影音播放、压缩解压、设计创作和系统工具等电脑常用正版软件,帮助用户快速完成新电脑软件配置。

    Windows
    Windows

    正软商城Windows软件专区,汇集适用于Windows电脑的办公、设计、安全防护、影音播放、开发工具和系统优化软件,提供软件介绍、系统要求、正版授权及购买下载服务。

    PDF教程
    PDF教程

    正软商城PDF教程频道提供PDF编辑、转换、合并、拆分、压缩及格式处理方法,同时介绍常用PDF软件和工具的使用技巧。

    Mac软件 更多
    讯飞智作 网页版
    讯飞智作 网页版
    Windows/ macOS/ Linux

    讯飞智作是一款基于语音合成技术、虚拟人驱动技术与数字人形象声音构建能力的 AI 数字人音视频创作平台。

    火山引擎 网页版
    火山引擎 网页版
    Windows/ macOS/ Linux

    火山引擎是字节跳动旗下企业级云与AI服务平台。聚焦大模型核心能力,依托字节自研豆包多模态大模型,凭借火山方舟一站式平台,提供模型精调、推理部署、智能应用搭建等全链路服务,结合高性能AI算力,助力各行业快速落地大模型数字化与智能化应用。

    Aperty macOS版
    Aperty macOS版
    macOS

    Aperty 是 Skylum 推出的 AI 人像修图软件,支持自然肤质修饰、瑕疵处理、面部与身体调整、预设及批量同步。

    WINDOWS 更多
    讯飞智作 网页版
    讯飞智作 网页版
    Windows/ macOS/ Linux

    讯飞智作是一款基于语音合成技术、虚拟人驱动技术与数字人形象声音构建能力的 AI 数字人音视频创作平台。

    火山引擎 网页版
    火山引擎 网页版
    Windows/ macOS/ Linux

    火山引擎是字节跳动旗下企业级云与AI服务平台。聚焦大模型核心能力,依托字节自研豆包多模态大模型,凭借火山方舟一站式平台,提供模型精调、推理部署、智能应用搭建等全链路服务,结合高性能AI算力,助力各行业快速落地大模型数字化与智能化应用。

    PixTV 网页版
    PixTV 网页版
    Windows

    PixTV 是一款在浏览器中使用的 AI 视觉内容创作平台,提供图片、视频、音频、文字生成、AI 分镜、局部重绘、提示词反推和无限画布等工具,适合短剧、漫剧、广告及社交媒体内容制作。