专栏持续更新中：MySQL详解

前语

许多同学都把 MySQL 作为自己的数据库，可是可能用过最多的便是 SQL 句子，以及一些 ORM 的写法，而对底层的完成了解甚少，比方上述问题中，InnoDB 和 MyISAM 分别是什么，可能都不是非常清楚。然而在一些大型公司（比方腾讯）的面试题中，可能会高频率地出现这类的问题，所以关于这类问题的正确理解，就显得非常重要了。

其实 InnoDB 和 MyISAM 是 MySQL 的两个「存储引擎」。

一、数据库存储引擎

数据库存储引擎是数据库底层软件安排，数据库管理体系（DBMS）运用数据引擎进行创建、查询、更新和删去数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功用，运用不同的存储引擎，还能够获得特定的功用。

二、如何知道自己的数据库用的什么引擎呢？

SHOW ENGINES;

三、存储引擎原理

首要针对可能面试会问到的问题「MyISAM 和 InnoDB 两种引擎所运用的索引的数据结构是什么」做一个答复：

都是 B+ 树，不过差异在于：

  • MyISAM 中 B+ 树的数据结构存储的内容是实践数据的地址值，它的索引和实践数据是分隔的，只不过运用索引指向了实践数据。这种索引的形式被称为非集合索引。
  • InnoDB 中 B+ 树的数据结构中存储的都是实践的数据，这种索引有被称为集合索引。

四、B 树和 B+ 树

那么什么是 B+ 树？

MySQL 中InnoDB与MyISAM的差异是什么?

B+ 树是 B 树的一个变种，关于 B 树来说：

B 树属于多叉树又名平衡多路查找树，其规则是：

  • 所有节点要害字是按递增次序排列，并遵循左小右大原则
  • 子节点数：非叶节点的子节点数>1，且<=M ，且M>=2，空树除外（注：M阶代表一个树节点最多有多少个查找途径，M=M 路,当 M=2 则是 2 叉树,M=3 则是 3 叉）
  • 要害字数：枝节点的要害字数量大于等于 ceil(m/2)-1 个且小于等于 M-1 个（注：ceil() 是个朝正无量方向取整的函数 如 ceil(1.1)结果为 2)
  • 叶节点的指针为空且叶节点具有相同的深度

而关于 B+ 树：

  • B+ 树是 B 树的一个升级版，相关于 B 树来说 B+ 树更充沛的运用了节点的空间，让查询速度愈加安稳，其速度彻底接近于二分法查找。

五、MyISAM

回到 MyISAM，其索引结构如下图所示，因为 MyISAM 的索引文件只是保存数据记载的地址。在 MyISAM 中，主索引和辅佐索引（Secondary key）在结构上没有任何差异：

MySQL 中InnoDB与MyISAM的差异是什么?

MyISAM 中索引检索的算法为首要按照 B+Tree 搜索算法搜索索引，假如指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记载。

六、InnoDB

关于 InnoDB 来说，表数据文件自身便是按 B+Tree 安排的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记载。

MySQL 中InnoDB与MyISAM的差异是什么?

因为 InnoDB 运用的数据库主键作为索引 Key，所以 InnoDB 数据表文件自身便是主索引，且因为 InnoDB 数据文件需求按照主键集合，所以运用 InnoDB 作为数据引擎的表需求有个主键，假如没有显式指定的话 MySQL 会尝试主动挑选一个能够仅有标识数据的列作为主键，假如无法找到，则会生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

七、InnoDB与MyISAM的差异

  1. MyISAM不支撑业务，MyISAM:着重的是功用，每次查询具有原子性,其履行数度比InnoDB类型更快，可是不提供业务支撑。InnoDB:提供业务支撑业务，外部键等高级数据库功用。具有业务(commit)、回滚(rollack)和崩溃修正才能(crash recovery capabilities)的业务安全(transaction-safe (ACID compliant))型表。

  2. InnoDB支撑外键，而MyISAM不支撑。对一个包括外键的InnoDB表转为MYISAM会失败；

  3. InnoDB是集合索引，运用B+Tree作为索引结构，数据文件是和（主键）索引绑在一同的（表数据文件自身便是按B+Tree安排的一个索引结构），必须要有主键，经过主键索引效率很高。可是辅佐索引需求两次查询，先查询到主键，然后再经过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。MyISAM对错集合索引，也是运用B+Tree作为索引结构，索引和数据文件是别离的，索引保存的是数据文件的指针。主键索引和辅佐索引是独立的。

    也便是说：InnoDB的B+树主键索引的叶子节点便是数据文件，辅佐索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅佐索引的叶子节点都是数据文件的地址指针。

  4. InnoDB不保存表的具体行数，履行select count(*) from table时需求全表扫描。而MyISAM用一个变量保存了整个表的行数，履行上述句子时只需求读出该变量即可，速度很快（注意不能加有任何WHERE条件）；

  5. Innodb不支撑全文索引，而MyISAM支撑全文索引，在涉及全文索引范畴的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支撑全文索引了

  6. MyISAM表格能够被压缩后进行查询操作

  7. InnoDB支撑表、行(默许)级锁，而MyISAM支撑表级锁

    InnoDB的行锁是完成在索引上的，而不是锁在物理行记载上。潜台词是，假如拜访没有射中索引，也无法运用行锁，即将退化为表锁。

  8. InnoDB表必须有仅有索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默许主键），而Myisam能够没有

在挑选存储引擎时，应该依据使用体系的特色挑选适宜的存储引擎。关于复杂的使用体系，还能够依据实践情况挑选多种存储引擎进行组合。以下是几种常用的存储引擎的运用环境。

  • InnoDB : 是Mysql的默许存储引擎，用于业务处理使用程序，支撑外键。假如使用对业务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了刺进和查询意外，还包括许多的更新、删去操作，那么InnoDB存储引擎是比较适宜的挑选。InnoDB存储引擎除了有效的下降因为删去和更新导致的锁定， 还能够确保业务的完整提交和回滚，关于相似于计费体系或者财政体系等对数据精确性要求比较高的体系，InnoDB是最适宜的挑选。
  • MyISAM ： 假如使用是以读操作和刺进操作为主，只要很少的更新和删去操作，而且对业务的完整性、并发性要求不是很高，那么挑选这个存储引擎是非常适宜的。
  • MEMORY：将所有数据保存在RAM中，在需求快速定位记载和其他相似数据环境下，能够提供几块的拜访。MEMORY的缺点便是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据能够康复，数据库异常终止后表中的数据是能够康复的。MEMORY表通常用于更新不太频频的小表，用以快速得到拜访结果。
  • MERGE：用于将一系列同等的MyISAM表以逻辑方式组合在一同，并作为一个目标引用他们。MERGE表的长处在于能够突破对单个MyISAM表的大小限制，而且经过将不同的表散布在多个磁盘上，能够有效的改进MERGE表的拜访效率。这关于存储诸如数据仓储等VLDB环境非常适宜。

总结

关于面试题来说，一般只会被要求答复到 InnoDB 和 MyISAM 在运用上的差异，不过假如需求深究一下为什么会有那些差异的话，就需求了解其底层的完成原理，趁便还需求关于 B+ 树有一定的了解，信任读者在读完本文后已经能够比较清晰地了解其背后的原理概要了，离拿到希望的 Offer 又近了一步。