搜索
首页数据库mysql教程mysql索引、存储方式对性能的影响

本文配图来自《高性能MySQL(第二版)》。 在数据库中,对性能影响最大的几个策略包括数据库的锁策略、缓存策略、索引策略、存储策略、执行计划优化策略。 索引策略决定数据库快速定位数据的效率,存储策略决定数据持久化的效率。 MySQL中两大主要存储引擎MyIS

本文配图来自《高性能MySQL(第二版)》。

在数据库中,对性能影响最大的几个策略包括数据库的锁策略、缓存策略、索引策略、存储策略、执行计划优化策略。
索引策略决定数据库快速定位数据的效率,存储策略决定数据持久化的效率。
MySQL中两大主要存储引擎MyISAM和InnoDB采用了不同的索引和存储策略,本文将分析它们的异同和性能。

MySQL主要提供2种方式的索引:B-Tree(包括B+Tree)索引,Hash索引。
B树索引具有范围查找和前缀查找的能力,对于N节点的B树,检索一条记录的复杂度为O(LogN)。
哈希索引只能做等于查找,但是无论多大的Hash表,查找复杂度都是O(1)。
显然,如果值的差异性大,并且以等于查找为主,Hash索引是更高效的选择,它有O(1)的查找复杂度。如果值的差异性相对较差,并且以范围查找为主,B树是更好的选择,它支持范围查找。


Hash索引各种引擎大同小异,没有太多可探讨性,本文主要讨论不同形式的B树索引。

B树属于二叉平衡树,平衡树就是任何一个节点的左右节点高度差距不能超过1的树,这才是绝对平衡的树。
平衡树比较好的算法是AVL,它通过左旋、右旋及其组合的操作可以保证树绝对平衡。
下面是AVL算法中的全部旋转操作:
mysql索引、存储方式对性能的影响
平衡树处在任何一个左边的不平衡状态都可以通过相应的旋转操作转移到右边的平衡状态。

数据库采用的B树只在叶子节点记录信息,非叶子节点记录的是范围信息,这是与一般搜索树不同的地方(一般搜索树非叶子节点也记录信息)。
这是一个B+树的结构,InnoDB的索引都采取了这种形式,它在B-树的基础上为每个叶子节点加了一个指针指向下一个叶子节点,这样可以快速的进行范围查找。
MyISAM是否也是B+树我还不能确定,但是B-树我没有想到可以快速进行范围查找的方法,应该也是B+树。
mysql索引、存储方式对性能的影响

例如这个B+树的例子:
mysql索引、存储方式对性能的影响
如果我要查找名字以A开头的全部信息,我只要获取第一个叶子节点,然后顺序沿着指向下一个叶子的指针,直到发现当年叶子节点已经不是以A开头则中止,这样只要搜索到第一个叶子节点(O(LogN))再沿着指针检索(O(N)),就可以获取全部索引,如果N个节点的表扫描M个连续值,就是O(LogN)+O(M),如果B-树则需要回溯到上层节点,这样最差的效率是O(LogN)*M。

对于InnoDB,使用了一种改进的B+树索引,称为聚集索引(Clustered Index),它的不同之处在于索引上不仅有索引值的信息,还有整个索引值所在行的信息,免去了一次通过索引值上的位置去取整行的操作。
mysql索引、存储方式对性能的影响

假设我们有个表有(col1,col2)列,col1是主键,col2也建立索引。那么在MyISAM引擎中,文件会被这样记录,因为MyISAM是按插入顺序把数据写入文件。如果有删除则空出位置,再次插入如果可以放下则会填充空白,对于不定长的行,存储引擎都会在分页中预留位置,以供更新更长的值(一般是VARCHAR),放不下则会添加到文件结尾,并从原位置删除。所以有时候会有空间浪费,需要Optimize Table来优化。
因此:定长的行比不定长的行效率高!把定长数据和不定长数据分开存储,很多时候可以提高效率。
mysql索引、存储方式对性能的影响

再来看MyISAM的主键索引,索引Key是主键值,索引Value是行的文件位置,通过这个位置可以直接读取行。从这个图上来看,MyISAM也是采用B+树。
mysql索引、存储方式对性能的影响

MyISAM的非主键索引,跟逐渐索引没有不同,也是索引行的文件位置。
mysql索引、存储方式对性能的影响

再看InnoDB的主键索引,索引Key是主键值,索引Value是整行的数据。
mysql索引、存储方式对性能的影响

InnoDB的非主键索引,索引Key是列值,索引Value是主键值。
mysql索引、存储方式对性能的影响

对比MyISAM和InnoDB的索引策略:
mysql索引、存储方式对性能的影响
可以发现MyISAM所有列的索引都是一样,索引Key是列值,索引Value是行的文件位置。
InnoDB的主键索引包含了行的全部信息,索引Key是主键值,索引Value是整行的值。而非主键索引索引Key是列值,索引Value是主键值,取数据时到主键索引中取。

并且在InnoDB中,一个聚集索引是必须的,如果没有定义主键,InnoDB也会自己隐含的建立一个聚集索引作为主键,因为InnoDB的主键索引还有个重要的功能就是行锁,这在我的另一篇文章中分析过。

再来看看我们插入值时会发生什么:
mysql索引、存储方式对性能的影响
InnoDB会按主键索引顺序组织文件,如果按主键顺序插入,可以直接在最尾部加入。并且只填充页面的15/16,这样可以预留部分空间以供行修改,这样组织的数据是非常紧凑的。

如果主键不是顺序的,我们来看看会发生什么,因为要按主键顺序存放,数据会被不断地移动,调整页面。
mysql索引、存储方式对性能的影响
所以:InnoDB引擎按主键顺序插入记录是非常必要的,否则性能将会面临很大风险。

声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
图文详解mysql架构原理图文详解mysql架构原理May 17, 2022 pm 05:54 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于架构原理的相关内容,MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层,下面一起来看一下,希望对大家有帮助。

mysql的msi与zip版本有什么区别mysql的msi与zip版本有什么区别May 16, 2022 pm 04:33 PM

mysql的msi与zip版本的区别:1、zip包含的安装程序是一种主动安装,而msi包含的是被installer所用的安装文件以提交请求的方式安装;2、zip是一种数据压缩和文档存储的文件格式,msi是微软格式的安装包。

mysql怎么去掉第一个字符mysql怎么去掉第一个字符May 19, 2022 am 10:21 AM

方法:1、利用right函数,语法为“update 表名 set 指定字段 = right(指定字段, length(指定字段)-1)...”;2、利用substring函数,语法为“select substring(指定字段,2)..”。

mysql怎么替换换行符mysql怎么替换换行符Apr 18, 2022 pm 03:14 PM

在mysql中,可以利用char()和REPLACE()函数来替换换行符;REPLACE()函数可以用新字符串替换列中的换行符,而换行符可使用“char(13)”来表示,语法为“replace(字段名,char(13),'新字符串') ”。

mysql怎么将varchar转换为int类型mysql怎么将varchar转换为int类型May 12, 2022 pm 04:51 PM

转换方法:1、利用cast函数,语法“select * from 表名 order by cast(字段名 as SIGNED)”;2、利用“select * from 表名 order by CONVERT(字段名,SIGNED)”语句。

MySQL复制技术之异步复制和半同步复制MySQL复制技术之异步复制和半同步复制Apr 25, 2022 pm 07:21 PM

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于MySQL复制技术的相关问题,包括了异步复制、半同步复制等等内容,下面一起来看一下,希望对大家有帮助。

mysql怎么判断是否是数字类型mysql怎么判断是否是数字类型May 16, 2022 am 10:09 AM

在mysql中,可以利用REGEXP运算符判断数据是否是数字类型,语法为“String REGEXP '[^0-9.]'”;该运算符是正则表达式的缩写,若数据字符中含有数字时,返回的结果是true,反之返回的结果是false。

mysql怎么删除unique keymysql怎么删除unique keyMay 12, 2022 pm 03:01 PM

在mysql中,可利用“ALTER TABLE 表名 DROP INDEX unique key名”语句来删除unique key;ALTER TABLE语句用于对数据进行添加、删除或修改操作,DROP INDEX语句用于表示删除约束操作。

See all articles

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
3 周前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳图形设置
3 周前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.如果您听不到任何人,如何修复音频
3 周前By尊渡假赌尊渡假赌尊渡假赌

热工具

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

螳螂BT

螳螂BT

Mantis是一个易于部署的基于Web的缺陷跟踪工具,用于帮助产品缺陷跟踪。它需要PHP、MySQL和一个Web服务器。请查看我们的演示和托管服务。

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

功能强大的PHP集成开发环境

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

DVWA

DVWA

Damn Vulnerable Web App (DVWA) 是一个PHP/MySQL的Web应用程序,非常容易受到攻击。它的主要目标是成为安全专业人员在合法环境中测试自己的技能和工具的辅助工具,帮助Web开发人员更好地理解保护Web应用程序的过程,并帮助教师/学生在课堂环境中教授/学习Web应用程序安全。DVWA的目标是通过简单直接的界面练习一些最常见的Web漏洞,难度各不相同。请注意,该软件中