搜索
首页数据库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性能?Apr 23, 2025 am 12:21 AM

如何有效监控MySQL性能?使用mysqladmin、SHOWGLOBALSTATUS、PerconaMonitoringandManagement(PMM)和MySQLEnterpriseMonitor等工具。1.使用mysqladmin查看连接数。2.用SHOWGLOBALSTATUS查看查询数。3.PMM提供详细性能数据和图形化界面。4.MySQLEnterpriseMonitor提供丰富的监控功能和报警机制。

MySQL与SQL Server有何不同?MySQL与SQL Server有何不同?Apr 23, 2025 am 12:20 AM

MySQL和SQLServer的区别在于:1)MySQL是开源的,适用于Web和嵌入式系统,2)SQLServer是微软的商业产品,适用于企业级应用。两者在存储引擎、性能优化和应用场景上有显着差异,选择时需考虑项目规模和未来扩展性。

在哪些情况下,您可以选择SQL Server而不是MySQL?在哪些情况下,您可以选择SQL Server而不是MySQL?Apr 23, 2025 am 12:20 AM

在需要高可用性、高级安全性和良好集成性的企业级应用场景下,应选择SQLServer而不是MySQL。1)SQLServer提供企业级功能,如高可用性和高级安全性。2)它与微软生态系统如VisualStudio和PowerBI紧密集成。3)SQLServer在性能优化方面表现出色,支持内存优化表和列存储索引。

MySQL如何处理角色集和碰撞?MySQL如何处理角色集和碰撞?Apr 23, 2025 am 12:19 AM

mySqlManagesCharacterSetsetSandCollat​​ionsyutusututf-8asthEdeFault,允许ConfigurationAtdataBase,table和columnlevels,AndrequiringCarefullageLignmentToavoidMismatches.1)setDefeaultCharactersetTercharactersetEtCollacterSeteTandColletationForAdataBase.2)conformentcollecharactersettersetertersetcollat​​ertersetcollat​​ioncollat​​ion

MySQL中有什么触发器?MySQL中有什么触发器?Apr 23, 2025 am 12:11 AM

MySQL触发器是与表相关联的自动执行的存储过程,用于在特定数据操作时执行一系列操作。1)触发器定义与作用:用于数据校验、日志记录等。2)工作原理:分为BEFORE和AFTER,支持行级触发。3)使用示例:可用于记录薪资变更或更新库存。4)调试技巧:使用SHOWTRIGGERS和SHOWCREATETRIGGER命令。5)性能优化:避免复杂操作,使用索引,管理事务。

您如何在MySQL中创建和管理用户帐户?您如何在MySQL中创建和管理用户帐户?Apr 22, 2025 pm 06:05 PM

在MySQL中创建和管理用户账户的步骤如下:1.创建用户:使用CREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';2.分配权限:使用GRANTSELECT,INSERT,UPDATEONmydatabase.TO'newuser'@'localhost';3.修正权限错误:使用REVOKEALLPRIVILEGESONmydatabase.FROM'newuser'@'localhost';然后重新分配权限;4.优化权限:使用SHOWGRA

MySQL与Oracle有何不同?MySQL与Oracle有何不同?Apr 22, 2025 pm 05:57 PM

MySQL适合快速开发和中小型应用,Oracle适合大型企业和高可用性需求。1)MySQL开源、易用,适用于Web应用和中小型企业。2)Oracle功能强大,适合大型企业和政府机构。3)MySQL支持多种存储引擎,Oracle提供丰富的企业级功能。

与其他关系数据库相比,使用MySQL的缺点是什么?与其他关系数据库相比,使用MySQL的缺点是什么?Apr 22, 2025 pm 05:49 PM

MySQL相比其他关系型数据库的劣势包括:1.性能问题:在处理大规模数据时可能遇到瓶颈,PostgreSQL在复杂查询和大数据处理上表现更优。2.扩展性:水平扩展能力不如GoogleSpanner和AmazonAurora。3.功能限制:在高级功能上不如PostgreSQL和Oracle,某些功能需要更多自定义代码和维护。

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脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

DVWA

DVWA

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

螳螂BT

螳螂BT

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

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

mPDF

mPDF

mPDF是一个PHP库,可以从UTF-8编码的HTML生成PDF文件。原作者Ian Back编写mPDF以从他的网站上“即时”输出PDF文件,并处理不同的语言。与原始脚本如HTML2FPDF相比,它的速度较慢,并且在使用Unicode字体时生成的文件较大,但支持CSS样式等,并进行了大量增强。支持几乎所有语言,包括RTL(阿拉伯语和希伯来语)和CJK(中日韩)。支持嵌套的块级元素(如P、DIV),