本文依旧主要参考沈剑大佬的十多篇博文，以及数位网友的优秀分享，文末是完整参考。
1、索引
1.1 B 树
多叉平衡查找树，多叉平衡以及磁盘预读使得 B 树查找效率特别高。
- 多叉（多路）性：每个结点有若干个关键字，相同数量的 key 在btree中生成的节点要远远小于二叉搜索树节点，相差的节点数目正比于树的高度正比与磁盘io的次数，达到一定数量时，性能差异明显。
- 平衡性：效率稳定，不像二叉查找树那样会退化成链表。
- 磁盘预读：磁盘读写是以页为单位读写，未使用到的数据其实是被预读出来了的，但是因为空间局部性原理，这些数据很可能会被访问，这样就省去了后续再次访问磁盘调取数据的开销，合理设置 B 树每个结点的元素个数，让每个结点尽量在同一页，这样每个结点仅需一次访存即可调取到内存中。
1.1.1 btree定义
1.每个节点最多有 m 个子树
2.若根不是叶子结点，则根节点至少有两个子树
3.分支节点至少拥有m/2棵子树（除根和叶子）
4.所有叶子节点都在同一层，这些叶子结点不存储有效的信息
- 每个节点最多可以有m-1个 key 并且升序排列，相同数量的 key 在btree中生成的节点要远远小于二叉搜索树节点，相差的节点数目正比于树的高度正比与磁盘io的次数，达到一定数量时，性能差异明显。
1.2 B+Tree 索引
是大多数 MySQL 存储引擎的默认索引类型。
因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。
因为 B+ Tree 的有序性，所以除了用于单点查询，还能够很好的支持排序、分组和范围查询；
可以指定多个列作为索引列，多个索引列共同组成键。
适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。
InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚集索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。
辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主键索引中进行查找。
1.2.1 B+ 树相较于 B 树的区别
- 叶子节点包含了所有关键字信息，并且叶子节点按照从小到大的顺序链接在一起。极大加快了范围查询的查找效率。
- 所有的叶子结点可以看成是索引部分，不含有效信息 (而B 树的非叶子点也包含需要查找的有效信息)，所以在相同内存的情况下，B+树能够存储更多索引；
1.3 哈希索引
哈希索引能以 O(1) 时间进行查找，但是失去了有序性：
精确查找效率高，排序、分组和范围查找效率低，时间复杂度会退化为O(n)；
InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”， Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引页被频繁访问，说明二级索引页对应的数据页成为热数据，那么InnoDB会自动给这些热点数据页建立哈希索引，加快这些热点数据页的查询速度。（哈希索引无法被InnoDB用户手动创建，只能被 InnoDB 自动创建）
key 是索引健值，val 是数据页。
参考：InnoDB关键特性之自适应hash索引、Mysql5.7 自适应hash索引 Adaptive hash index
1.4 聚集索引（UnClustered Index）
主键索引（Primary Inkex）、普通索引（Secondary Index）、聚集索引（Clustered Index）、非聚集索引（UnClustered Index）
聚集索引叶子结点直接存储行记录；非聚集索引叶子结点存储主键的值。所以对于非聚集索引的查询，往往需要回表，也就是二次查询主键索引才能获取行记录，特例是覆盖索引。
InnoDB的表必须要有聚集索引（可以不显示声明主键）。主键是逻辑概念，一般值字段，聚集索引是物理概念，一般是只索引，但是大部分情况都指的是同一个东西。
1.4.1 主键选择原则
可以通过雪花算法生成全局唯一且趋势递增的 id 作为主键。
（1）不能为空的列；
（2）不能重复的列；
（3）很少改变的列；
画外音：行是按照聚集索引物理排序的，如果主键频繁改变，物理顺序会改变，性能会急剧降低。
（4）经常被检索(where key=XXX)的列
画外音：被检索的列上要建立索引，如果该索引是聚集索引，能够避免回表，性能提升几乎一倍。
（5）不是太长的列
画外音：普通索引叶子节点会存储主键值，如果主键值太长，会增加普通索引的大小。
1.4.2 主键常见使用说明
1、建表时，可以不声明主键。
(1)如果表定义了主键 (PK, Primary Key)，则PK就是聚集索引；
(2)如果表没有定义PK，则第一个非空 unique 列是聚集索引；
(3)否则，InnoDB 会创建一个隐藏的 row-id 作为聚集索引；
2、建表时，可以不声明主键非空，innodb 会自动加上 unique 的限制，但是如果没有设置默认值，不会自动加上默认值
如果没有设置默认值，且插入语句语句中没有指定主键的值，那么会提示 Field 'id' doesn't have a default value
3、可以指定自增列的值，但是可能会出现空洞
如果主键是自增id，自增键从 1 开始。插入行记录时，如果不指定值，InnoDB自动增加。同时，也可以手动指定值：
（1）手动指定的值与已有值重复，则主键冲突；
（2）与已有值不冲突，则插入成功；同时如果指定的主键值大于当前表的自增值，会把此次手动插入的主键值加一作为当前自增值。未来不指定值插入，会从新的自增值往后继续递增；从而自增 id 出现了空洞。
4、建表时，可以选择多个字段做主键
此时聚集索引是一个联合索引
5、建表时，可以使用联合自增主键，但是自增 id 列必须在联合主键的第一列。
建表时，声明联合主键(a,b)，并且有一个是自增ID，插入若干元素，包含自增ID，有些不包含。
如果指定了自增键的值且值为大于当前自增键最大值的合法值，那么自增键将变为刚才插入的值加一
2、MyISAM 跟 Innodb 的区别
事务、索引、锁类型
2.1 区别
2.1.1 索引区别
数据结构都是 B+ 树。
InnoDB 区分聚集索引和非聚集索引，聚集索引叶子结点直接存储行记录；非聚集索引叶子结点存储主键的值，所以命中主键索引后效率非常高。一定有且仅有一个聚集索引。
MyISAM 主键索引和非主键索引都是非聚集索引，叶子结点存储的都是指向行记录的指针。因为主键索引跟非主键索引数据结构一样，主键索引查找没有效果更好，所以 MyISAM 可以没有主键。
2.1.2 事务和外键
InnoDB 支持事务和外键，MyISAM 不支持事务和外键。
实践：不管哪种存储引擎，在数据量大并发量大的情况下，都不应该使用外键，而建议由应用程序来保证完整性。
2.1.3 锁区别
InnoDB 有行锁、表锁、间隙锁、意向锁等各种锁，锁的粒度小，冲突小，但是操作复杂，行锁的数量与行记录数相关，非常耗内存。
MyISAM 只有表锁，冲突大，但是操作简单，而且表锁占用内存少很多。
2.1.4 记录存储区别
MyISAM 每次记录插入都是插入到数据文件尾部，插入效率非常高，索引的叶子结点只存储记录行的指针。而且数据都是插入在文件尾部，查询尾部之前的数据可以不用加锁，读写同时进行，不用加表锁。这里有个小问题是因为没有加锁，刚写入的数据可能暂时查询不到。
发生删除或者修改操作可能导致数据文件中间出现空闲块，出现空洞后先填充空洞，随后继续把记录填充在文件尾部。但是填充空洞时无法读写并发执行，需要加表锁。
InnoDB 记录存在聚簇索引的叶子结点。
2.2 场景使用推荐
数据一致性要求不高，不需要事务，只有 insert 和 select 的场景，可以考虑使用 MyISAM，因为MyISAM在文件尾部顺序增加记录速度极快，而查询尾部之前的数据可以不用加锁，读写同时进行，不用加表锁，实现了读写并发，效率非常高，但是 update 和 delete 操作可能导致数据文件出现空洞，填补空洞需要时无法读写并发，影响 MyISAM 的效率。
数据一致性要求高，需要事务，写操作多样，数据量大，并发量大时，表锁冲突大，推荐使用 InnoDB 。
3、InnoDB 并发高的原因
MVCC 快照读，实现了读写并发，不需要加锁。
redo log 把写操作的随机写磁盘改成顺序写文件，批量刷磁盘，提高写操作效率。
3.1 MVCC（多版本并发控制）
快照读，实现了读写并发，不需要加锁。
3.1.1 数据多版本
多版本指的是写任务发生时，会将数据备份一份到 undo log中，以版本号区分；且在提交之前，写任务都是只操作新克隆的数据，并发读任务可以继续读取旧版本的数据，不需要阻塞。另外为了保证读操作的旧版本是正确的版本，设计了一套可见性算法。
3.1.2 可见性算法
每次事务第一个 select 开始时，建立读视图，把当前活跃的事务 id 列表保存在视图里，第二次 select 时检查记录行当前状态对于读视图是否可见，如果可见则直接读取，否则通过回滚指针所指向的回滚段中找到对自己可见的 undo log 版本。
3.1.3 隐藏字段
InnoDB的内核，会对所有row数据增加三个内部属性：
(1)DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID；
(2)DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针；
(3)DB_ROW_ID，6字节，单调递增的行ID；
通过 MVCC 可以实现 RC 和 RR 隔离级别，两者实现上的区别就是提交读事务内的每个 select 重新建立读视图，可重复读同个事务内的所有 select 复用最开始的读视图。
3.2 什么是 redo 日志、undo 日志、回滚段
3.2.1 redo log （提高数据库吞吐量且保障已提交事务的ACID特性）
数据库事务提交后，必须将更新后的数据刷到磁盘上，以保证ACID特性。磁盘随机写性能较低，如果每次都刷盘，会极大影响数据库的吞吐量。
优化方式是，将修改行为追加到到 redo 日志文件尾部，redo log 由于顺序写所以效率非常高，再定期将数据刷到磁盘上，这样能极大提高性能。
3.2.2 undo log（配合 MVCC 以及方便回滚）
用于记录写操作发生时备份的旧版本数据，方便事务回滚和并发读任务并发读取旧版本数据。如果写操作是 insert 操作，undo日志记录新数据的PK(ROW_ID)，回滚时直接删除旧数据；如果是delete/update操作，undo日志记录旧数据row，回滚时直接恢复；
undo log 存储在回滚指针所指向的回滚段中。
4、四种隔离级别以及实现方式
- 读未提交(Read Uncommitted)：所有 select 不加锁，可能出现脏读；
- 读提交(Read Committed, RC)：普通 select 快照读，加锁 select /update /delete 会使用记录锁，可能出现不可重复读、幻读；
- 可重复读(Repeated Read, RR)：普通 select 快照读，加锁 select /update /delete 根据查询条件情况，会选择记录锁，或者间隙锁/临键锁，以防止读取到幻影记录；
- 串行化(Serializable)：一致性最好，但是并发性最差的隔离级别。所有 select语句都会被隐式的转化为 select ... lock in share mode. 而 in share mode 会给表加上表锁。阻塞写操作，读写无法并发。
InnoDB默认的隔离级别是RR，用得最多的隔离级别是RC。（这个还未考证过）
4.1 实现方式更细节表述
4.1.1 读提交
(1)普通读是快照读；
(2)加锁的select, update, delete等语句，除了在外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会封锁区间，其他时刻都只使用记录锁；
4.1.2 可重复读
(1) 普通的select通过 MVCC 实现不加锁快照读(snapshot read)。
(2) 加锁的select(select ... in share mode / select ... for update), update, delete等语句，它们的锁，依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(unique search condition)，或者范围查询条件(range-type search condition)：
- 在唯一索引上使用唯一的查询条件，会使用记录锁(record lock)
- 范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，以及避免不可重复的读
4.1.3 串行化：
一致性最好，但是并发性最差的隔离级别。所有select语句都会被隐式的转化为 select ... in share mode. 而 in share mode 会给表加上意向锁。阻塞写操作，读写无法并发。
5、问题
问：怎么查看自适应哈希功能是否开启
查询自适应哈希功能是否开启：show variables like "innodb_adaptive_hash_index"; 默认是开启的。
查询自适应hash的使用情况：show engine innodb status;
问：什么是回表
通过普通索引查找到主键值，还需要查询聚集索引才能获得完整记录，这就叫回表。只有覆盖索引和使用主键查询才不需要回表。
问：为什么不建议使用较长的列作为主键
浪费空间、页分裂
1、所有的普通索引都会存储主键，会导致普通索引过于庞大；
2、字段较长的列很可能不是趋势递增的，由于主键索引的数据行跟索引存在一起，如果不是趋势递增，很可能导致大量索引分裂，行记录移动，其中索引分裂也就导致了物理页的分裂。
问：select * 会使用索引吗
看 where 查询条件，如果命中了索引则会使用索引，否则不会使用。不推荐 select * 的原因是不是因为无法使用索引，而是需要多召回字段，浪费空间，而且因为没有指定具体查询的字段列表，表结构变更很容易引入 bug。
问：假如某一时刻，数据库崩溃，还没来得及刷盘的数据会丢失吗
不会，数据在保存到磁盘前都已经记录在了 redo log中，在数据库重启后，会重做 redo 日志里的内容，以保证已提交事务对数据产生的影响都刷到磁盘上。
问：写写需要互斥，是说任一时刻只能有一个写事务在运行吗
不是，没有发生资源竞争的写写事务可以同时执行和提交，发生了资源竞争的事务，后获取资源锁的事务需要等先获取资源锁的事务执行完毕释放锁之后继续执行。
问：现在的版本都默认是RR，间隙锁会导致性能下降很多，一不注意就锁住大范围，甚至锁表，但是大多数应用其实不需要间隙锁的。那么一般情况下是不是应该修改成RC呢?
大部分业务可以用RC。 当然，具体和业务场景，数据量，并发量有关。
问：MyISAM 会存储记录行数， count(*) 效率特别高？真的吗
MyISAM会直接存储总行数，但是只有查询全表的总行数，MyISAM才会直接返回结果，当加了where条件后，两种存储引擎的处理方式类似。
问：Innodb 和 MyISAM 支持全文索引吗
都支持，但是有可能会导致小量请求占用大量数据库资源，全文索引建议还是使用 ES 等外置索引来实现。
问：innodb 主键字段修改会导致聚集索引重建吗
会，所以一般不建议修改主键的字段，修改主键，应该建新表，然后导数据。
问：表锁相较于行锁的优势
InnoDB 有行锁、表锁、间隙锁、意向锁等各种锁，锁的粒度小，冲突小，但是操作复杂，行锁的数量与行记录数相关，非常耗内存。
MyISAM 只有表锁，冲突大，但是操作简单，而且表锁占用内存少很多。
如果业务经常读写表中很大一部分数据时，表锁会更快，因为此时只涉及一个锁，而不是同时管理N多个锁；
如果业务经常使用group by，表锁会更快，group by读取多行，只要一个读锁。如果groupby用 innodb，会产生多个行锁
表锁释放时：
如果写锁队列和读锁队列里都有锁，写有更高的优先级，即写锁队列先出列。这么做的原因是，如果有“大查询”，可能会导致写锁被批量“饿死”，而写锁往往释放很快。如果有大量并发update请求，select会等所有update请求执行完才执行。
表锁性能更优的场景
A：大部分SQL都是读请求。
B：SQL是读写混合，写请求是单行的delete或update。
update t set c=xxx where unique_key=yyy;
delete from t where unique_key=zzz;
C：SQL是读写混合，写请求是高并发的insert，很少有delete或update。
D：SQL会扫描大量行记录，且有很多group by。
问：如何查看表锁状态， 如何判断表锁是不是主要冲突点？
如果要分析表锁冲突情况，可查看这两个状态变量：
Table_locks_immediate：立刻获得表锁的次数；
Table_locks_waited：需要等待表锁的次数；如果等待表锁的次数占比较大，说明表锁可能是潜在瓶颈。
问：某个session占有了表写锁，有另外N个session又要对表进行写操作，MySQL是如何处理的？
进入写锁队列
某问：个session释放了表写锁，有另外N个session要对表进行写操作，同时还有M个session要对表进行读操作，谁先抢到锁，为什么？
如果写锁队列和读锁队列里都有锁，写有更高的优先级，即写锁队列先出列。这么做的原因是，如果有“大查询”，可能会导致写锁被批量“饿死”，而写锁往往释放很快。如果有大量并发update请求，select会等所有update请求执行完才执行。
（6）如何高效的实现并发插入与查询，如何互斥？
问：MyISAM什么情况下，数据文件会出现空洞？假如数据文件有空洞，新插入的数据是先补上空洞，还是插入在文件尾部？
发生删除或者修改操作可能导致数据文件中间出现空闲块，出现空洞后先填充空洞，随后继续把记录填充在文件尾部。但是填充空洞时无法读写并发执行，需要加表锁。
6、完整参考
惯例强推沈剑大佬的公众号“架构师之路”
频繁插入(insert)的业务，用什么存储引擎更合适？ | 数据库系列
Mysql5.7 自适应hash索引 Adaptive hash index
MySQL InnoDB锁机制之Gap Lock、Next-Key Lock、Record Lock解析
通过各种简单案例，让你彻底搞懂 MySQL 中的锁机制与 MVCC
