南宁 网站建设,wordpress自定义背景,兰州网站推广优化,麒麟区住房和城乡建设局网站文章目录 前言一、数据库存储引擎二、如何知道自己的数据库用的什么引擎呢#xff1f;三、存储引擎原理四、B 树和 B 树五、MyISAM六、InnoDB七、InnoDB与MyISAM的区别总结 前言
许多同学都把 MySQL 作为自己的数据库#xff0c;但是可能用过最多的就是 SQL 语句#xff0c… 文章目录 前言一、数据库存储引擎二、如何知道自己的数据库用的什么引擎呢三、存储引擎原理四、B 树和 B 树五、MyISAM六、InnoDB七、InnoDB与MyISAM的区别总结 前言
许多同学都把 MySQL 作为自己的数据库但是可能用过最多的就是 SQL 语句以及一些 ORM 的写法而对底层的实现了解甚少比如上述问题中InnoDB 和 MyISAM 分别是什么可能都不是非常清楚。然而在一些大型公司比如腾讯的面试题中可能会高频率地出现这类的问题所以对于这类问题的正确理解就显得非常重要了。
其实 InnoDB 和 MyISAM 是 MySQL 的两个「存储引擎」。
一、数据库存储引擎 数据库存储引擎是数据库底层软件组织数据库管理系统DBMS使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能使用不同的存储引擎还可以获得特定的功能。 二、如何知道自己的数据库用的什么引擎呢
SHOW ENGINES;三、存储引擎原理
首先针对可能面试会问到的问题「MyISAM 和 InnoDB 两种引擎所使用的索引的数据结构是什么」做一个回答
都是 B 树不过区别在于
MyISAM 中 B 树的数据结构存储的内容是实际数据的地址值它的索引和实际数据是分开的只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。InnoDB 中 B 树的数据结构中存储的都是实际的数据这种索引有被称为聚集索引。
四、B 树和 B 树
那么什么是 B 树 B 树是 B 树的一个变种对于 B 树来说
B 树属于多叉树又名平衡多路查找树其规则是
所有节点关键字是按递增次序排列并遵循左小右大原则子节点数非叶节点的子节点数1且M 且M2空树除外注M阶代表一个树节点最多有多少个查找路径MM 路,当 M2 则是 2 叉树,M3 则是 3 叉关键字数枝节点的关键字数量大于等于 ceil(m/2)-1 个且小于等于 M-1 个注ceil() 是个朝正无穷方向取整的函数 如 ceil(1.1)结果为 2)叶节点的指针为空且叶节点具有相同的深度
而对于 B 树
B 树是 B 树的一个升级版相对于 B 树来说 B 树更充分的利用了节点的空间让查询速度更加稳定其速度完全接近于二分法查找。
五、MyISAM
回到 MyISAM其索引结构如下图所示由于 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中主索引和辅助索引Secondary key在结构上没有任何区别 MyISAM 中索引检索的算法为首先按照 BTree 搜索算法搜索索引如果指定的 Key 存在则取出其 data 域的值然后以 data 域的值为地址读取相应数据记录。
六、InnoDB
对于 InnoDB 来说表数据文件本身就是按 BTree 组织的一个索引结构这棵树的叶节点 data 域保存了完整的数据记录。
由于 InnoDB 利用的数据库主键作为索引 Key所以 InnoDB 数据表文件本身就是主索引且因为 InnoDB 数据文件需要按照主键聚集所以使用 InnoDB 作为数据引擎的表需要有个主键如果没有显式指定的话 MySQL 会尝试自动选择一个可以唯一标识数据的列作为主键如果无法找到则会生成一个隐含字段作为主键这个字段长度为6个字节类型为长整形。
七、InnoDB与MyISAM的区别 InnoDB支持事务MyISAM不支持对于InnoDB每一条SQL语言都默认封装成事务自动提交这样会影响速度所以最好把多条SQL语言放在begin和commit之间组成一个事务 InnoDB支持外键而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败 InnoDB是聚集索引数据文件是和索引绑在一起的必须要有主键通过主键索引效率很高。但是辅助索引需要两次查询先查询到主键然后再通过主键查询到数据。因此主键不应该过大因为主键太大其他索引也都会很大。而MyISAM是非聚集索引数据文件是分离的索引保存的是数据文件的指针。主键索引和辅助索引是独立的。 InnoDB不保存表的具体行数执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数执行上述语句时只需要读出该变量即可速度很快 Innodb不支持全文索引而MyISAM支持全文索引查询效率上MyISAM要高
MyISAMInnoDB构成上的区别每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始扩展名指出文件类型。 .frm文件存储表定义。 数据文件的扩展名为.MYD (MYData)。 索引文件的扩展名是.MYI (MYIndex)。基于磁盘的资源是InnoDB表空间数据文件和它的日志文件InnoDB 表的大小只受限于操作系统文件的大小一般为 2GB事务处理上方面:MyISAM类型的表强调的是性能其执行数度比InnoDB类型更快但是不提供事务支持InnoDB提供事务支持事务外部键(foreign key)等高级数据库功能SELECT UPDATE,INSERTDelete操作如果执行大量的SELECTMyISAM是更好的选择1.如果你的数据执行大量的INSERT或UPDATE出于性能方面的考虑应该使用InnoDB表 2.DELETE FROM table时InnoDB不会重新建立表而是一行一行的删除。 3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的解决方法是首先把InnoDB表改成MyISAM表导入数据后再改成InnoDB表但是对于使用的额外的InnoDB特性(例如外键)的表不适用对****AUTO_INCREMENT的操作每表一个AUTO_INCREMEN列的内部处理。 MyISAM为INSERT和UPDATE操作自动更新这一列**。这使得AUTO_INCREMENT列更快(至少10%)。在序列顶的值被删除之后就不能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列可以出现重使用从序列顶部删除的值的情况)。 AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置 对于AUTO_INCREMENT类型的字段InnoDB中必须包含只有该字段的索引但是在MyISAM表中可以和其他字段一起建立联合索引 更好和更快的auto_increment处理如果你为一个表指定AUTO_INCREMENT列在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器它被用在为该列赋新值。 自动增长计数器仅被存储在主内存中而不是存在磁盘上 关于该计算器的算法实现请参考 AUTO_INCREMENT列在InnoDB里如何工作表的具体行数select count() from table,MyISAM只要简单的读出保存好的行数注意的是当count()语句包含 where条件时两种表的操作是一样的InnoDB 中不保存表的具体行数也就是说执行select count(*) from table时InnoDB要扫描一遍整个表来计算有多少行锁表锁提供行锁(locking on row level)提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)另外InnoDB表的行锁也不是绝对的如果在执行一个SQL语句时MySQL不能确定要扫描的范围InnoDB表同样会锁全表 例如update table set num1 where name like “%aaa%”
在选择存储引擎时应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。
InnoDB : 是Mysql的默认存储引擎用于事务处理应用程序支持外键。如果应用对事务的完整性有比较高的要求在并发条件下要求数据的一致性数据操作除了插入和查询意外还包含很多的更新、删除操作那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定 还可以确保事务的完整提交和回滚对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统InnoDB是最合适的选择。MyISAM 如果应用是以读操作和插入操作为主只有很少的更新和删除操作并且对事务的完整性、并发性要求不是很高那么选择这个存储引擎是非常合适的。MEMORY将所有数据保存在RAM中在需要快速定位记录和其他类似数据环境下可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制太大的表无法缓存在内存中其次是要确保表的数据可以恢复数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表用以快速得到访问结果。MERGE用于将一系列等同的MyISAM表以逻辑方式组合在一起并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制并且通过将不同的表分布在多个磁盘上可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。
总结
对于面试题来说一般只会被要求回答到 InnoDB 和 MyISAM 在使用上的区别不过如果需要深究一下为什么会有那些区别的话就需要了解其底层的实现原理顺便还需要对于 B 树有一定的了解相信读者在读完本文后已经可以比较清晰地了解其背后的原理概要了离拿到希望的 Offer 又近了一步。
