读书心得笔记：此心光明，亦复何言？知行合一，以行践言，坚持做难而正确的事。今年生了一场病，目前康复中，这次伤病后，最大体悟就是学会了自身内省，致良知。
一、前言背景
二、MYSQL架构原理相关核心点
2.1 简单说说一条查询SQL的执行过程
2.2 简单说说一条更新SQL的执行过程
2.3 MySQL更新数据为什么要先记录redoLog、undoLog、最后刷盘？直接刷盘不更快吗？
2.4 总结一下MySQL架构各个组件的作用
三、存储引擎相关知识要点
3.1 MySQL的存储引擎用过几种？
3.2 MySQL每个页数据大小是多少？
3.3 bufferpool数据清理算法是什么？
3.4 bufferpool内存脏数据什么时候会刷盘？
3.5 事务的原子性主要与哪个日志相关？
3.6 事务的一致性、持久性主要与哪个日志相关？
四、索引相关高频实用考察点
4.1 什么样的字段适合建索引？
4.2 索引字段数量越多越好吗？
4.3 什么样的字段不宜建索引？
4.4 什么样的查询会导致索引失效？
4.5 聚集索引和联合索引的区别。
4.6 索引是如何存储数据的？
4.7 说说二叉树、平衡二叉树、B树的区别。
一、前言背景
在技术面试过程中，有的面试官喜欢先聊整体架构，再聊细节；有的喜欢先从一个小点引入，循序渐进逐渐聊到整体架构。不同的考察思路，仁者见仁。而今天想分享的是，为了减少八股面试给候选人的压力与困扰，以及切实掌握候选人的技术经验，针对MySQL架构原理这一块的细节，进行阶段的梳理，进行详细探讨。突出面试官提问要务实，表达思路清晰，此外，也希望候选人抓住机会，简明扼要的回答核心点。
二、MySQL架构原理相关核心点
在涉及MySQL架构原理考察，往往2条SQL，一个查询、一个更新，就可以全面覆盖到MySQL架构的各个组件。之前我们系列1、2文章基于5.7版本的MySQL进行过详细分析，现在开启面试17连问模式。
2.1 简单说说一条查询SQL的执行过程
首先客户端通过MySQL驱动连接到MySQL服务端的SQL接口(也叫连接器)，用的是TCP协议，建立的连接是【长连接】，默认是8小时后超时断开，而服务端默认支持最大连接数是151个，最大可以修改到10w个。SQL接口首先会验证客户端的用户密码是否正确，然后查询是否命中缓存。这个缓存组件在MySQL5.7 默认是关闭的，在8.0版本已经被移除。原因是，缓存组件要求有大量重复查询才有效，而这种场景在redis、或者服务应用层实现会非常高效。对比之下，MySQL层提供这种功能业务价值不高，在8.0已经被移除该组件。
查询SQL用户鉴权通过后，到达SQL解析器。在SQL解析器进行词法分析、语法分析、语义分析，并生成语法树。如果SQL的关键字拼写错误、表名、列名不存在，在SQL解析器就报错返回。
经过SQL解析器后得到了查询SQL具体树形结构语法树，SQL优化器，针对查询SQL的可能执行路径进行分析评估，选择一个最优执行路径。这个阶段会将SQL里写的那种无效条件，或者组合条件进行优化。1=1的会被删掉，以及复合索引创建的时候是name+age,条件写成了age =xx and name =xx的，SQL优化器帮你纠正过来，让索引查询生效。查看优化器分析得到的SQL执行计划，可以用explain formation= json SQL语句来查看。
最后，存储引擎（也有的说有个执行器，实际官方架构图是没有执行器这个组件的）执行具体查询任务。从磁盘加载对应数据到bufferpool，最后通过SQL接口返回结果给客户端。存储引擎读取数据这里也有一些细节，包括数据如何加载、索引是否覆盖、是否回表查询、索引如何存储、存储引擎的区别等。后面的问题再深入讨论。
2.2 简单说说一条更新SQL的执行过程
update SQL和select SQL的前面2.1 说的执行过程一致，唯一区别在于存储引擎部分。
首先更新SQL来到存储引擎(默认说的是InnoDB)后，存储引擎需要将目标数据从磁盘加载到bufferpool。在MySQL InnoDB每次加载数据，只加载一页，每页数据大小是16kb，而操作系统的一页数据是4kb。这是一个小细节。然后存储引擎InnoDB bufferpool的大小是128MB。缓存最多可以放8192个页数据。
存储引擎把目标数据加载到内存后进行修改，需要记录undoLog、redoLog两种日志。其中undoLog为了方便回滚，redoLog用于数据恢复。而作为MySQL服务层的二进制日志文件binLog默认是不开启的，如果开启的话，更新sql执行完成也会进行记录binLog，方便主从数据同步备份。
由于更新sql是在内存中进行，效率很高。当undoLog、缓存更新、redoLog记录完成，事务提交，就反馈执行接口给客户端。最后存储引擎再不定时将存储引擎的脏数据更新到磁盘。
2.3 MySQL更新数据为什么要先记录redoLog、undoLog、最后刷盘？直接刷盘不更快吗？
这里涉及的是顺序读写和随机读写。redoLog和undoLog都是顺序读写，以及直接修改内存，这两种都是效率非常高的更新。
而如果改成直接更新磁盘ibd文件，这是一个磁盘随机读写，效率很慢。虽然架构设计、执行步骤上更简单明了，但是两种读写方式决定了直接刷盘效率更低。索引MySQL选择了增加redoLog、undoLog、缓存更新，最后不定时刷盘的架构设计进行高效更新操作。「拉丁解牛说技术，实用至上，坚持用最简洁直白的文字+最少的代码示例分享干货。」
2.4 总结一下MySQL架构各个组件的作用
MySQL架构主要有2层，一个是服务层、一个是存储引擎层。
服务层有SQL接口、解析器、优化器、此外还有binLog日志。
SQL接口通过连接池管理客户端的连接，默认最大连接只有151个，最多可以改成10w个。客户端的SQL连接、结果返回、用户鉴权、超时断开等都是SQL接口负责。
解析器，主要负责词法解析、语法解析，以及生成树状语法树。这里也会把关键字、表名、字段名、计算符号进行校验判断。
优化器，主要生成执行计划，并选择用索引，生成最优执行计划。
binLog，就是记录增删改，还有授权、新建表、改表结构等操作记录日志。这里附带说一下，binLog不会记录select、show这种操作的日志，以及binLog默认是关闭的。
存储引擎层，主要负责缓存查询和更新数据，并从磁盘加载数据。在存储引擎里，还有redoLog、undoLog的记录，当有数据更新时，会涉及内存脏页数据的刷盘。
三、存储引擎相关知识要点
3.1 MySQL的存储引擎用过几种？
最常用的是InnoDB、MYISAM。用的比较少但是也很实用的有Memory、Archive存储引擎。
InnoDB，支持行锁、表锁，事务，支持MVCC的多并发事务控制，支持4种事务隔离级别。innoDB 会将表数据分为2个文件进行存储，一个是ifm-存放表结构，一个是ibd-存放聚集索引（数据和主键索引都在这个索引文件里）。当表有主键primary key列，这个主键就是聚集索引。如果没有PK，则选择一个not null 且unique的列作为聚集索引。如果没有PK，也没有not null 且unique的列，MySQL就默认新增一个隐藏的row-id作为聚集索引，强制让每个InnoDB表都是一个B+树的聚集索引。
MYISAM，在MySQL5.5以前是默认的存储引擎，但是不支持行锁，不支持事务，只支持表锁。在读的时候是共享锁，写的时候是排它锁。所以myisam存储引擎的并发效率低。myisam存储引擎，一个表有3个文件，frm是存储表结构，myd是存储表数据，myi是存储索引文件。如下图:
此外MyISAM有个特点，支持全文索引，是基于分词创建的索引，但是中文分词不好。myisam这个中文支持不友好的特性，在国内很容易被es、mongoDB替换，所以myisam存储引擎大家用的会比较少。
而archive存储引擎，也叫档案引擎。这个引擎只支持新增和查询。不支持修改和删除。优点是，archive存储引擎对数据进行了压缩，采用该引擎存储数据，比myisam、innodb存储要节省大概80%左右的磁盘存储。所以如果是业务系统日志数据存储、历史数据归档备份存储查询，可以考虑archive存储引擎，将大幅节省存储空间。
最后，memory存储引擎，数据仅存在内存，不会存储到磁盘，如果服务宕机，数据就会丢失。memory存储引擎支持hash索引和B树索引。所有字段长度是varchar(10)或char(10)。适合临时缓存数据查询存储、且容许丢失的场景。
3.2 MySQL每个页数据大小是多少？
MySQL每个数据页大小是16KB。这么定义，好处是比操作系统的4kb大，而且是4kb的整数倍。在innoDB存储引擎的B+树索引里，每个节点有16kb大，由于非叶子节点只存储索引字段值，不存在整行数据，B+树的每个非叶子节点就可以存储非常多的数据索引值，整体看整个索引树就是一颗树高大概3~4的胖乎乎的树，但是实际已经可以构建几千万行数据的索引。
当目标数据查到之后，MySQL也是直接按最小16kb一个数据页的大小去磁盘加载、写入数据，由于是操作系统整数倍数据叶大小，IO效率会很高。
这个16kb的数据页大小，与MySQL一个表能存几千万条数据息息相关。
3.3 bufferpool数据清理算法是什么？
采用的是优化过的LRU算法-最近最少使用冷热数据分离算法。具体就是，缓存是一个链表存储，且分2段，上半部5/8+下半部3/8。上半部会是热数据，下半部分是冷数据。
当数据加载到bufferpool后，先进入冷数据头部，如果1秒钟之后仍被访问，就会被置顶挪到热数据头部。如果1s内被访问，位置不变。最近最少使用的缓存，随着时间推移，不断被挪到链表尾部，直到被淘汰。「拉丁解牛说技术，实用至上，坚持用最简洁直白的文字+最少的代码示例分享干货。」
3.4 bufferpool内存脏数据什么时候会刷盘？
所谓脏数据，就是bufferpool里被修改过，还没保存到磁盘的数据。InnoDB有2个参数innodb_max_dirty_pages_pct_lwm+innodb_max_dirty_pages_pc（默认75%）。当脏页比例大于这两个值，就开始进入刷盘模式。默认lwm是零，所以默认内存的脏页数据最多可以达到128M*75%=96Mb，脏数据才开始被刷到磁盘。
3.5 事务的原子性主要与哪个日志相关？
事务的原子性，要求要么全部成功、要么全部失败。当事务失败回滚的时候，需要用到undoLog。undoLog之前我们的日志三宝文章有说过，这个日志记录的是更新前数据值的内容，如果事务回滚，可以利用该日志修改前数据值进行回滚。
3.6 事务的一致性、持久性主要与哪个日志相关？
日志三宝之一redoLog，对MySQL事务的一致性、持久性起到关键作用。redoLog记录的是修改后的值。有自己的更新参数：【innodb_flush_log_at_trx_commit】控制。具体如下：
innodb_flush_log_at_trx_commit=0： 表示每次事务提交时都只是把 redo log 留在 redo log buffer 。
innodb_flush_log_at_trx_commit=1： 表示每次事务提交时都将 redo log 直接持久化到磁盘，
innodb_flush_log_at_trx_commit=2： 表示每次事务提交时都只是把 redo log 写到系统 page cache，这个缓存大概1s左右刷一次到磁盘。
当系统宕机导致缓存脏数据没来得及更新到磁盘，redoLog将支持MySQL进行数据恢复，确保事务提交的数据的一致性和持久性。
四、索引相关高频实用考察点
4.1 什么样的字段适合建索引？
在查询SQL条件之后的字段适合做索引。比如where、group by、having、order by关键字字段后的列。
4.2 索引字段数量越多越好吗？
当然索引数量不是越多越好。索引也是一把双面剑，除了可以帮助提升查询搜索效率，但是索引也带来了而外的存储空间开销，最重要的是会影响更新写入的效率。如果一个字段变成了索引，更新字段值，还有更新其他的索引树数据。
4.3 什么样的字段不宜建索引？
散列度低的字段。计算公式散列度公式：count(distinct(column_name)) / count(*)。
比如性别，只有男、女两种，一个用户表几百万行数据，性别字段的散列度几乎为0，不能用于建索引。这种索引的搜索效率，比全表扫描还要慢。原因是，散列度低的索引，整颗B+数非叶子节点，有非常多值一样的节点，当查询命中索引时，几乎要一个个去随机加载全部索引，最后还有回表查询，所以比直接全部扫描查询慢很多。
4.4 什么样的查询会导致索引失效？
导致索引失效，最常见的是查询条件里有联合索引字段，但不符合最左匹配原则。比如name+city是联合索引，查询的时候，查询条件只有city是不会走索引查询。另外如果查询的时候name是like '%xx%',也不会走索引。
这里有个注意的地方，如果你的sql是where city=‘xx’ and name=‘xx’，理论上是不应该走索引。但是MySQL的查询优化器，自动识别满足符合索引条件，对执行计划进行了优化，最终你的sql是会走索引查询。
4.5 聚集索引和联合索引的区别。
innoDB存储引擎的每个表都有聚集索引。聚集索引的每一行数据是存在B+树的叶子节点，并且是有序的。
而联合索引，也是B+树，但是叶子节点存放的是主键id的值。命中联合索引查数据的时候，最后需要在聚集索引里回表查询。「拉丁解牛说技术，实用至上，坚持用最简洁直白的文字+最少的代码示例分享干货。」
4.6 索引是如何存储数据的？
InnoDB存储引擎，采用的是B+树索引。如下图，新增id为2、1、4、3、6、20的行数据。该数据结构如下：
在非叶子节点上，只存储索引字段值，并且每个节点可以存放很多个索引值。
在叶子节点，如果是聚集索引，将把对应主键的整行数据存放在该节点，而且叶子节点是链表结构，对范围查询非常友好。
在叶子节点，如果是非聚集索引，将存放相关索引值对应的主键值。这样的好处，只有聚集索引有表的全部真实数据，而非聚集索引树的数据大小就变得比较小，避免数据冗余。最终通过回表查询，聚集索引的检索也会很快完成。
4.7 二叉树、平衡二叉树、B树的区别。
二叉树是一种树形结构，每个节点最多有两个子节点，一个左子节点和一个右子节点。
平衡二叉树-AVL树，也是二叉树，但是任意节点的左右子树高度差不超过 1。为了防止二叉查找树退化为链表，产生不良的查找性能。
B树，属于多叉树，也叫做平衡多路查找树（查找路径不只两个，这个和传统二叉树不一样）。数据库索引里大量使用者B树和B+树的数据结构。
我们直接举例，写入1、2、4、3四个值。
二叉树如下图，树高是4。假如有序写入，二叉树的树高将高的惊人，将变成一个链表。
平衡二叉树，按序写入1、2、4、3。树高，只有3层，比二叉树4层好多了，右子树比左子树高1层。
最后，B树，同样按顺序写入1、2、4、3。树高也只有2层。比二叉树、平衡二叉树都矮，树高也小，搜索效率越高。
推荐阅读拉丁解牛相关专题系列(欢迎交流讨论公众号搜：拉丁解牛)：
