原理：存储引擎
InnoDB 与MyISAM
查看MySQL提供的所有存储引擎
mysql> show engines;
Mysql 中 MyISAM 和 InnoDB 的区别有哪些？
InnoDB支持事务，MyISAM不支持
对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，
所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
InnoDB支持外键，而MyISAM不支持。
对一个包含外键的InnoDB表转为MYISAM会失败；
InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。
但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。
因此主键不应该过大，因为主键太大，其他索引也都会很大。
而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。
主键索引和辅助索引是独立的。
InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。
而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；
是否支持行级锁
MyISAM 只有表级锁(table-level locking)，
而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
是否支持事务和崩溃后的安全恢复：
MyISAM 强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
但是InnoDB 提供事务支持事务，外部键等高级数据库功能。
具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
是否支持MVCC ：
仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;
MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;
MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一
如何选择：
是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB
系统奔溃后，MyISAM恢复起来更困难，能否接受；
MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，
如果你不知道用什么，那就用InnoDB，至少不会差。
查看MySQL提供的所有存储引擎
mysql> show engines;
查看MySQL当前默认的存储引擎
我们也可以通过下面的命令查看默认的存储引擎。
mysql> show variables like '%storage_engine%';
查看表的存储引擎
show table status like "table_name" ;
InnoDB（事务性数据库引擎
InnoDb 逻辑存储结构图
从InnoDb 存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）。
表空间又由段（segment），区（extent），页（page）组成。页在一些文档中有时候也称为块（block）。InnoDb 逻辑存储结构图如下：
表空间（tablespace）
表空间是Innodb存储引擎逻辑的最高层，所有的数据都存放在表空间中。
默认情况下，Innodb存储引擎有一个共享表空间ibdata1,即所有数据都存放在这个表空间中内。
如果启用了innodbfileper_table参数，需要注意的是每张表的表空间内存放的只是数据、索引、和插入缓冲Bitmap，其他类的数据，比如回滚(undo)信息、插入缓冲检索页、系统事物信息，二次写缓冲等还是放在原来的共享表内的。
段（segment）
表空间由段组成，常见的段有数据段、索引段、回滚段等。
InnoDB存储引擎表是索引组织的，因此数据即索引，索引即数据。数据段即为B+树的叶子结点，索引段即为B+树的非索引结点。
在InnoDB存储引擎中对段的管理都是由引擎自身所完成，DBA不能也没必要对其进行控制。
区（extent）
区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。
为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4~5个区。
默认情况下，InnoDB存储引擎页的大小为16KB，一个区中一共64个连续的区。
页（page）
页是InnoDB磁盘管理的最小单位。
在InnoDB存储引擎中，默认每个页的大小为16KB。
从InnoDB1.2.x版本开始，可以通过参数innodbpagesize将页的大小设置为4K，8K，16K。
InnoDB存储引擎中，常见的页类型有：数据页，undo页，系统页，事务数据页，插入缓冲位图页，插入缓冲空闲列表页等。
Innodb页结构相关示意图
Innodb页结构单体图
InnoDB数据页由以下7部分组成，如图所示：
其中File Header、Page Header、File Trailer的大小是固定的，分别为38，56，8字节，这些空间用来标记该页的一些信息，
如Checksum，数据页所在B+树索引的层数等。User Records、Free Space、Page Directory这些部分为实际的行记录存储空间，因此大小是动态的。
下边我们用表格的方式来大致描述一下这7个部分：
记录在页中的存储流程图
每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，
当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：
不同Innodb页构成的数据结构图
一张表中可以有成千上万条记录，一个页只有16KB，所以可能需要好多页来存放数据。
不同页其实构成了一条双向链表，File Header是InnoDB页的第一部分，它的FILPAGEPREV和FILPAGENEXT就分别代表本页的上一个和下一个页的页号，即链表的上一个以及下一个节点指针。
Innodb索引结构图
我们先看一份数据表样本，假设Col1是主键，如下：
B+树聚集索引结构图
聚集索引就是以主键创建的索引
聚集索引在叶子节点存储的是表中的数据
非聚集索引结构图
假设索引列为Col3，索引结构图如下：
非聚集索引就是以非主键创建的索引
非聚集索引在叶子节点存储的是主键和索引列
使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)
假设所查询的列，刚好都是索引对应的列，不用再回表查，那么这个索引列，就叫覆盖索引。
InnoDB 锁类型思维导图
加锁机制
乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题。
乐观锁
每次去取数据，都很乐观，觉得不会出现并发问题。
因此，访问、处理数据每次都不上锁。
但是在更新的时候，再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。
悲观锁
每次去取数据，很悲观，都觉得会被别人修改，会有并发问题。
因此，访问、处理数据前就加排他锁。
在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁.
锁粒度
表锁： 开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低;不会出现死锁。
行锁： 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。
页锁： 开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
兼容性
共享锁：
又称读锁（S锁）。
一个事务获取了共享锁，其他事务可以获取共享锁，不能获取排他锁，其他事务可以进行读操作，不能进行写操作。
SELECT ... LOCK IN SHARE MODE 显示加共享锁。
排他锁：
又称写锁（X锁）。
如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
SELECT ... FOR UPDATE 显示添加排他锁。
锁模式
记录锁： 在行相应的索引记录上的锁，锁定一个行记录
gap锁： 是在索引记录间歇上的锁,锁定一个区间
next-key锁： 是记录锁和在此索引记录之前的gap上的锁的结合，锁定行记录+区间。
意向锁 是为了支持多种粒度锁同时存在；
MySQL 使用 B+ 树，而不是 B 树或者 Hash
为什么 MySQL 使用 B+ 树，而不是 B 树或者 Hash？
- InnoDB 需要支持的场景和功能需要在特定查询上拥有较强的性能；
- CPU 将磁盘上的数据加载到内存中需要花费大量的时间，这使得 B+ 树成为了非常好的选择；
数据的持久化以及持久化数据的查询其实是一个常见的需求，而数据的持久化就需要我们与磁盘、内存和 CPU 打交道；
MySQL 作为 OLTP 的数据库不仅需要具备事务的处理能力，而且要保证数据的持久化并且能够有一定的实时数据查询能力，这些需求共同决定了 B+ 树的选择
读写性能
很多人对 OLTP 这个词可能不是特别了解，我们帮助各位读者快速理解一下，
与 OLTP 相比的还有 OLAP，它们分别是 Online Transaction Processing 和 Online Analytical Processing，
从这两个名字中我们就可以看出，前者指的就是传统的关系型数据库，主要用于处理基本的、日常的事务处理，
而后者主要在数据仓库中使用，用于支持一些复杂的分析和决策。
作为支撑 OLTP 数据库的存储引擎，我们经常会使用 InnoDB 完成以下的一些工作：
- 通过 INSERT、UPDATE 和 DELETE 语句对表中的数据进行增加、修改和删除；
- 通过 UPDATE 和 DELETE 语句对符合条件的数据进行批量的删除；
- 通过 SELECT 语句和主键查询某条记录的全部列；
- 通过 SELECT 语句在表中查询符合某些条件的记录并根据某些字段排序；
- 通过 SELECT 语句查询表中数据的行数；
- 通过唯一索引保证表中某个字段或者某几个字段的唯一性；
如果我们使用 B+ 树作为底层的数据结构，那么所有只会访问或者修改一条数据的 SQL 的时间复杂度都是 O(log n)，也就是树的高度，
但是使用哈希却有可能达到 O(1) 的时间复杂度，看起来是不是特别的美好。但是当我们使用如下所示的 SQL 时，哈希的表现就不会这么好了：
SELECT * FROM posts WHERE author = 'draven' ORDER BY created_at DESC
SELECT * FROM posts WHERE comments_count > 10
UPDATE posts SET github = 'github.com/draveness' WHERE author = 'draven'
DELETE FROM posts WHERE author = 'draven'
如果我们使用哈希作为底层的数据结构，遇到上述的场景时，使用哈希构成的主键索引或者辅助索引可能就没有办法快速处理了，
它对于处理范围查询或者排序性能会非常差，只能进行全表扫描并依次判断是否满足条件。
全表扫描对于数据库来说是一个非常糟糕的结果，这其实也就意味着我们使用的数据结构对于这些查询没有其他任何效果，最终的性能可能都不如从日志中顺序进行匹配。
使用 B+ 树其实能够保证数据按照键的顺序进行存储，也就是相邻的所有数据其实都是按照自然顺序排列的，使用哈希却无法达到这样的效果，因为哈希函数的目的就是让数据尽可能被分散到不同的桶中进行存储，所以在遇到可能存在相同键 author = 'draven 或者排序以及范围查询 comments_count > 10 时，由哈希作为底层数据结构的表可能就会面对数据库查询的噩梦 —— 全表扫描。
B 树和 B+ 树在数据结构上其实有一些类似，它们都可以按照某些顺序对索引中的内容进行遍历，对于排序和范围查询等操作，B 树和 B+ 树相比于哈希会带来更好的性能，当然如果索引建立不够好或者 SQL 查询非常复杂，依然会导致全表扫描。
与 B 树和 B+ 树相比，哈希作为底层的数据结构的表能够以 O(1) 的速度处理单个数据行的增删改查，但是面对范围查询或者排序时就会导致全表扫描的结果，而 B 树和 B+ 树虽然在单数据行的增删查改上需要 O(log n) 的时间，但是它会将索引列相近的数据按顺序存储，所以能够避免全表扫描。
数据加载
既然使用哈希无法应对我们常见的 SQL 中排序和范围查询等操作，而 B 树和 B 树和 B+ 树都可以相对高效地执行这些查询，那么为什么我们不选择 B 树呢？这个原因其实非常简单 —— 计算机在读写文件时会以页为单位将数据加载到内存中。页的大小可能会根据操作系统的不同而发生变化，不过在大多数的操作系统中，页的大小都是 4KB，你可以通过如下的命令获取操作系统上的页大小:
当我们需要在数据库中查询数据时，CPU 会发现当前数据位于磁盘而不是内存中，这时就会触发 I/O 操作将数据加载到内存中进行访问，数据的加载都是以页的维度进行加载的，然而将数据从磁盘读取到内存中所需要的成本是非常大的，普通磁盘（非 SSD）加载数据需要经过队列、寻道、旋转以及传输的这些过程，大概要花费 10ms 左右的时间。
我们在估算 MySQL 的查询时就可以使用 10ms 这个数量级对随机 I/O 占用的时间进行估算，这里想要说的是随机 I/O 对于 MySQL 的查询性能影响会非常大，而顺序读取磁盘中的数据时速度可以达到 40MB/s，这两者的性能差距有几个数量级，由此我们也应该尽量减少随机 I/O 的次数，这样才能提高性能。
B 树与 B+ 树的最大区别就是，B 树可以在非叶结点中存储数据，但是 B+ 树的所有数据其实都存储在叶子节点中，当一个表底层的数据结构是 B 树时，假设我们需要访问所有『大于 4，并且小于 9 的数据』：
如果不考虑任何优化，在上面的简单 B 树中我们需要进行 4 次磁盘的随机 I/O 才能找到所有满足条件的数据行：
- 加载根节点所在的页，发现根节点的第一个元素是 6，大于 4；
- 通过根节点的指针加载左子节点所在的页，遍历页面中的数据，找到 5；
- 重新加载根节点所在的页，发现根节点不包含第二个元素；
- 通过根节点的指针加载右子节点所在的页，遍历页面中的数据，找到 7 和 8；
当然我们可以通过各种方式来对上述的过程进行优化，不过 B 树能做的优化 B+ 树基本都可以，所以我们不需要考虑优化 B 树而带来的收益，直接来看看什么样的优化 B+ 树可以做，而 B 树不行。
由于所有的节点都可能包含目标数据，我们总是要从根节点向下遍历子树查找满足条件的数据行，这个特点带来了大量的随机 I/O，也是 B 树最大的性能问题。
B+ 树中就不存在这个问题了，因为所有的数据行都存储在叶节点中，而这些叶节点可以通过『指针』依次按顺序连接，
当我们在如下所示的 B+ 树遍历数据时可以直接在多个子节点之间进行跳转，这样能够节省大量的磁盘 I/O 时间，也不需要在不同层级的节点之间对数据进行拼接和排序；
通过一个 B+ 树最左侧的叶子节点，我们可以像链表一样遍历整个树中的全部数据，我们也可以引入双向链表保证倒序遍历时的性能
有些读者可能会认为使用 B+ 树这种数据结构会增加树的高度从而增加整体的耗时，然而高度为 3 的 B+ 树就能够存储千万级别的数据，实践中 B+ 树的高度最多也就 4 或者 5，
所以这并不是影响性能的根本问题。
总结
任何不考虑应用场景的设计都不是最好的设计，当我们明确的定义了使用 MySQL 时的常见查询需求并理解场景之后，再对不同的数据结构进行选择就成了理所当然的事情，当然 B+ 树可能无法对所有 OLTP 场景下的查询都有着较好的性能，但是它能够解决大多数的问题。
我们在这里重新回顾一下 MySQL 默认的存储引擎选择 B+ 树而不是哈希或者 B 树的原因：
哈希虽然能够提供 O(1) 的单数据行操作性能，但是对于范围查询和排序却无法很好地支持，最终导致全表扫描；
B 树能够在非叶节点中存储数据，但是这也导致在查询连续数据时可能会带来更多的随机 I/O，
而 B+ 树的所有叶节点可以通过指针相互连接，能够减少顺序遍历时产生的额外随机 I/O；
如果想要追求各方面的极致性能也不是没有可能，只是会带来更高的复杂度，我们可以为一张表同时建 B+ 树和哈希构成的存储结构，这样不同类型的查询就可以选择相对更快的数据结构，但是会导致更新和删除时需要操作多份数据。
从今天的角度来看，B+ 树可能不是 InnoDB 的最优选择，但是它一定是能够满足当时设计场景的需要，从 B+ 树作为数据库底层的存储结构到今天已经过了几十年的时间，我们不得不说优秀的工程设计确实有足够的生命力。而我们作为工程师，在选择数据库时也应该非常清楚地知道不同数据库适合的场景，因为软件工程中没有银弹。
