引言
作为一名开发人员，MySQL几乎是大家最常用最熟悉的数据库，在面试时也经常被问到。为什么要深入原理层面了解MySQL呢？知其然知其所以然，结合掌握工具的三个层次来回答：会用，会使用SQL完成各种数据操作，也就是按照使用文档先用起来；用好（深），当出现问题和遇到瓶颈怎么办？了解服务端执行原理有助于写出高效SQL，并掌握解决实际问题的最佳姿势；为我所用，借鉴MySQL在文件IO领域好的做法来解决我系统所碰到的类似问题。试问如果不了解原理，又怎么能做到用好和为我所用呢？请先思考！
为什么InnoDB是MySQL应用最广泛的存储引擎？
它的索引为什么要使用B+树？
它的高并发是怎么做到的？
什么是：读脏，不可重复读，读幻象？
各种SQL都加了什么锁？MySQL 架构概览
MySQL内部架构
MySQL采用典型的客户端/服务端架构
客户端（图中Connecters部分）为各编程语言提供基于Socket的连接驱动接入服务端，应用可通过客户端程序发送SQL指令并接收服务端执行返回。
服务端（图中MySQL Server部分）则采用分层架构：
最上层为连接管理层（Connection Pool），提供客户端连接相关的认证/限流/缓存；
中间层为SQL处理层，常用的SQL接口类型包括：DML、DDL、存储过程、视图（Views）、触发器（Triggers）等，该层处理SQL编译（Parser）、绑定表元数据形成含有语义的语法树、优化（Optimizer）生成最优的执行计划、物理执行执行计划并返回结果；
最下层为存储引擎层，存储引擎其实就是对于数据库文件的一种存取机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新、查询数据等技术实现的方法，常用：MyISAM和InnoDB。
此外为方便管理和易于使用，官方还提供了配套的管理工具和企业特性。InnoDB VS MyISAM
关于COUNT(*)，MyISAM会存储总行数，InnoDB需要按行扫描；有where条件，count(*)两个存储引擎性能差不多；数据量大时，InnoDB下执行count(*)容易引起执行阻塞
全文索引都支持，注意：InnoDB从5.6版本开始支持，数据量大并发量大时，会导致小量请求占用大量数据库资源，应该搭建独立的索引服务
事务：InnoDB支持事务，MyISAM不支持事务
行锁与表锁：InnoDB可以支持行锁，MyISAM只支持表锁。InnoDB采用细粒度锁，高并发下性能优异，MyISAM容易死锁
结论：应使用InnoDB，提供行锁，能提高并发；提供事务，能保证数据一致性数据库索引
数据库为什么要设计索引？加快数据查询
哈希(hash)比树(tree)更快，索引结构为什么要设计成树型？
设计成树形，和SQL的需求相关，Hash只适合单行查找，范围查找(>、<、between、in)、模糊查找(like)、排序、分组等需求难以满足
InnoDB索引为什么使用B+树？B+树索引
B+树索引结构
说明：箭头线串联起各磁盘块，数字表示存储的数据项，P和Q表示指向磁盘指针。
结构特点：使用M叉搜索树，能大大降低树的高度；利用非叶子节点存储若干数据key，从根节点开始查找，依次从指向缩小范围的下级节点查找，最终找到目标数据所在的叶子结点磁盘快 ；叶子结点存储若干行数据，叶子结点间有连接指针连接支持横向遍历。
优势：索引和数据都能充分利用磁盘预读，按页读取并缓存，降低IO次数，提高效率；得益于索引的有序性，在叶子之间增加了指向链表，当需要获取相关节点时，不再需要每次经索引做中序遍历，如：范围查找，定位min与max之后，获取中间叶子节点数据，就是最终结果集。索引分类：主键索引和普通索引
主键索引（主索引）又称聚集索引，叶子节点直接存储主键和对应行记录（而不是指针），索引和行记录聚集存储，结构如下图所示
主键索引结构
普通索引（辅助索引），叶子节点只存储主键，查询时先找到匹配字段所在的主键值，在使用主键走聚集索引提取行记录，结构如下图所示
普通索引结构
补充说明：
InnoDB的数据存储采用聚集索引方式要求表必须有唯一聚集索引（通常是主键）；不建议使用较长的列做主键，会导致索引过于庞大；建议使用趋势递增的整型key做主键，利于维护索引和行记录间移动。
普通索引实际上会两次查询索引，先通过所建的普通索引找到目标行主键，再通过主键索引找到行数据。
索引具有模糊查询的最左前缀匹配特性，从上图中为name字段建立的普通索引可以看出字符索引是按字典序组织在一起，如查找Jim，从根节点定位到Alice所在索引块，再依次定位到Eric所在磁盘块，并最终找到Jim的主键值是20。
索引具有组合索引的最左匹配特性，组合索引是复合的数据结构，比如idx_(name,age,sex)的时候，B+树是按照从左到右的顺序来建立搜索树，比如当(张三,20,F)这样的数据来检索的时候，B+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，B+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询， 这个是非常重要的性质。InnoDB 架构
InnoDB架构
内存结构：
缓冲池（Buffer Pool），加速读请求，避免每次数据访问都进行磁盘IO
写缓冲（Change Buffer），加速写请求，避免每次写入都进行磁盘IO
自适应哈希索引（Adaptive Hash Index），加速读请求，减少索引查询的寻路路径
日志缓冲（Log Buffer），随机写优化为顺序写，次次写优化为批量写
磁盘结构：
表空间（Tablespace），用于存放所有数据
回滚日志（Undo Log），用于数据回滚，保障未提交事务不会对数据库的ACID产生影响
重做日志（Redo Log），数据库从崩溃中恢复后重做，保障已提交事务的ACID特性
关于内存结构和磁盘结构的实现细节会留到后续文章讲。MVCC多版本并发控制
现引用阿里《数据库内核月报 - 2017/12》对MVCC的解释，如下：多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，与Postgres在数据行上实现多版本不同，InnoDB是在undolog中实现的，通过undolog可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。
数据多版本是一种能够进一步提高并发的方法，它的核心原理是：
写任务发生时，将数据克隆一份，以版本号区分；
写任务操作新克隆的数据，直至提交；
并发读任务可以继续读取旧版本的数据，不至于阻塞；
多版本控制机制
如上图：
最开始数据的版本是V0；
T1时刻发起了一个写任务，这是把数据clone了一份，进行修改，版本变为V1，但任务还未完成；
T2时刻并发了一个读任务，依然可以读V0版本的数据；
T3时刻又并发了一个读任务，依然不会阻塞；
可以看到，数据多版本，通过“读取旧版本数据”能够极大提高任务的并发度。
提高并发的演进思路，就是如此：
普通锁，本质是串行执行
读写锁，可以实现读读并发
数据多版本，可以实现读写并发Undo Log
先通过演示示例说明Undolog参与事务回滚的工作机制
执行如下SQLstart transaction;delete (2, zhangsan);update set(3, wangwu) to (3, mazi);insert (4, zhaoliu);rollback;
Undo log机制
事务回滚演示
上图说明：
图中的最新数据和Undo Log均是在Buffer进行操作
被删除前的(2, lisi)作为旧版本数据，进入了Undo Log，标记状态：deleted
被修改前的(3, wangwu)作为旧版本数据，进入了Undo Log，标记状态：updated
被插入的数据，(4, zhaoliu)进入了Undo Log，状态：inserted
事务回滚会利用UndoLog做回滚，回滚成功后一切如故，最后清空Undo Log对应内存中的回滚段(Buffer)数据
延展下当事务成功，回滚段里的Undo Log日志可以直接删除
结论
支持快照读（Read View），通过Undo日志多版本构建出之前的历史版本，因Undo log数据不会修改，select可以高并发读取
数据库事务未提交时，会将事务修改数据的镜像（即修改前的旧版本）存放到undo日志里，当事务回滚时，或者数据库崩溃时，可以利用undo日志，即旧版本数据，撤销未提交事务对数据库产生的影响
INSERT操作在事务提交前只对当前事务可见，产生的Undo日志在事务提交/回滚后直接删除；UPDATE/DELETE被归成一类，需要维护多版本信息，事务提交时，会将操作产生的Undo日志按提交顺序加入到历史链表，等待Purge线程回收Redo Log
为什么要有redo log？
数据库事务提交后，必须将更新后的数据刷到磁盘上，磁盘随机写性能较低，如果每次都刷盘，会极大影响数据库的吞吐量。优化方式是，将修改行为先写到redo日志里（此时变成了顺序写/批量写），再定期将数据刷到磁盘上，这样能极大提高性能。
为防止数据库崩溃，在数据库重启后，会重做redo日志里的内容，以保证已提交事务对数据产生的影响都刷到磁盘上，以保证ACID特性。四种事务隔离级别
什么是事务的隔离性？
隔离性是指，多个用户的并发事务访问同一个数据库时，一个用户的事务不应该被其他用户的事务干扰，多个并发事务之间要相互隔离。不同事务的隔离级别，实际上是一致性与并发性的一个权衡与折衷。
隔离级别
读未提交（Read Uncommitted）
每次读取最新数据，可能读到其他事务还未提交的不一致数据，即读脏。并发最高，事务一致性最差。
读提交（Read Committed，RC）
普通select快照读，加锁select/update/delete会使用记录锁，可能出现同一事务的多次读取值不一样，即不可重复读。并发高，一致性高，Oracle默认级别。
可重复读（Repeated Read，RR）
普通select快照读，加锁select/update/delete根据查询情况，会使用记录锁或间隙锁/临键锁，以防止读取到幻影记录（幻读：插入前判断值不存在，插入时可能出现被其他事务先插入引起的重复冲突）。并发较RC高，一致性更高，InnoDB默认级别。
串行读（Serializable）
select隐式转化为select ... in share mode，会被update与delete互斥。并发最低，一致性最高。锁机制概览
Innodb中的锁
Mysql Innodb 中的加锁粒度细、涉及场景多，限于篇幅，不再介绍，推荐感兴趣的读者阅读：MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁总结
应使用InnoDB引擎，提供MVCC&行锁，能提高并发；提供事务，能保证数据一致性。
数据库索引用于加快数据查询，索引结构使用B+树，索引和数据都能充分利用磁盘预读，按页读取并缓存，降低IO次数，提高效率，叶子之间，增加了链表，获取所有节点，不再需要中序遍历。
多版本控制，是一种提高并发的技术，支持快照读，能读读并行、读写并行，InnoDB采用undolog机制实现。
回滚日志（Undo Log），用于数据回滚，保障未提交事务不会对数据库的ACID产生影响。事务未提交时，会将事务修改前数据的镜像快照存放到undo日志里，支持读快照。事务成功，数据被刷到表空间存储。事务回滚，按备份镜像还原数据。最终undolog会被清空。
重做日志（Redo Log），数据库从崩溃中恢复后重做，保障已提交事务的ACID特性。事务数据库事务提交后，为提高更新后的数据刷到磁盘，会启用Purge线程异步刷盘，同时记录redo log用于数据库从崩溃中恢复后重做。
四种事务隔离级别从低到高依次是：读未提交（读脏）、读提交（RC，不可重复读）、可重复读（RR，InnoDB默认级别，一致性高）、串行读（并发差，一致性最高），用得最多的隔离级别是RC。
各种细粒度锁用来提高写写间的并发和一致性保障。
