聊聊Mysql

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分:

  Server层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

  存储引擎层：是一个可插拔的设计，也就是我们可以随意选择具体的存储引擎。server端通过API与存储引擎进行通信，这些接口屏蔽了不同存储引擎的差异。 支持 InnoDB、MyISAM、Memory 等多个存储引擎。从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。

来解释整个执行流程:

1. mysql内部的一个执行过程是？

  宏观上的话，大概是这几个步骤。首先会通过连接器和mysql建立链接，sql经过分析器，优化器，执行器然后返回执行结果。优化器会根据一些成本的计算，来决定走哪个索引或者是多表连接的顺序，最终生成一个执行计划，执行计划会根据这个计划去调用存储引擎层的api接口。存储引擎是一个可插拔的设计，不同的存储引擎都实现了一套统一的api，可以自由更换，上层无感知。

2. Innodb和Myisam分别适用于哪些场景？

  这两个数据库差别还挺大的，innodb是事务型数据库，我们一般的业务对可靠性有要求的基本都用innodb，myisam适用于olap的场景，就那种读多写少的，比如数仓t+1给我们跑一套聚合的数据，业务只用于展示的，比如支付宝的年度总结。就可以用myisam，查询速度会更快。

3. 为啥在只读的场景myisam会比innodb更快？

  myisam的存储结构是非聚簇索引也就是他的所有索引存储的都是数据行的地址，不需要回表。而且innodb每次查询要去检查mvcc的版本，就会更慢，而myisam不需要

4. 存储引擎的执行细节?

  根据mysql官方的架构图。innodb主要分为两大块，缓存和磁盘。比如我们要根据主键更新某个字段，首先会去查索引树，定位到具体的某条数据。在这期间访问的所有索引页和数据页，都会全部加载进缓存，也就是buffer poll。我们的修改数据实际上是修改的缓存里的值，修改后的缓存页称为脏页，然后这些脏页以一定的规律刷盘到磁盘。在修改前还会记录对应的undolog，修改后会去记录redolog。事务提交用的是一个两阶段的提交，先将事务改为prepare状态，然后binlog刷盘，然后在将事务改为commit状态。事务就算完成了。

5. 为啥要用redolog，不用行不行？

  如果不用redolog，其实也可以。但是为了保证持久性，事务过程中所有的数据的变动，都得刷盘，就意味着undolog，数据页，索引页，changebuffer等修改都要刷盘 。这些文件都在磁盘里不同的位置，刷盘产生大量的随机io，性能影响是很大的。为了保证持久性，又想提高刷盘速度，所以有了redolog，它记录了所有数据的变化，然后用顺序追加写的方式避免随机io。这样其他的文件变动，都只需要修改缓存，不急着刷盘。事务提交的时候把redolog刷盘就好了。

6. 那如果系统突然宕机，还有很多脏页没有刷到磁盘，这些数据要怎么恢复?

  redolog有一个lsn的版本号，随着追加写入的文件不增长，他有三个比较重要的指针，第一个就是更新指针，表示的是redolog buffer中写入的最新的位置，第二个是刷盘指针，指的是redolog buffer已经保存到磁盘的位置。第三个是脏页指针，指的buffer poll那些脏页已经刷盘成功的最新位置。奔溃的时候，我们会在redolog磁盘找到脏页指针一直到最新的位置，把他们对应的数据页全部加载回bufferpoll。根据redolog的日志，来重放之前的修改，这样就能恢复奔溃前的缓存现场了。然后再根据每个事务的事务状态状态来决定是提交还是回滚。

7. 事物的两阶段提交，为啥要两阶段提交？

  两阶段提交是分布式事务的一个常见方案。在mysql内部存储引擎的提交和server层的binlog提交就相当于是一个分布式事务了，所以需要两阶段提交去协调。

8. 那redolog是在哪个阶段刷盘以及为什么是那个阶段？

  redolog是在repare阶段刷盘的，两阶段的一个核心就是如果有一方提交成功了，那么整个事务都必须最终成功。那我们可以假设一下，如果redolog是在commit阶段刷盘的，这时候binlog已经commit了，然后程序宕机，redolog还没保存到磁盘，就会把所有数据给丢失了。如果在repare状态就刷盘，后面再宕机，至少恢复的时候能恢复到prepare状态。两阶段还有个重要的特性，就是支持反查。这时候去反查下binlog有没有提交成功，就可以决定整个事务是提交还是回滚。

9. 刚刚提到buffer pool，它是一个缓存。缓存总归是会满的，会涉及到一个淘汰的问题，并且缓存的命中率也是一个很关键的指标。buffer pool是如何保证这些的？

  bufferpool的内存结构是这样的，他在mysql启动的时候就会初始化好控制块和缓存页，每个控制块对应一个缓存页。控制块里面有一些指针，可以互相串起来形成一条双向链表。bufferpool管理了空闲链表，脏页链表和lru链表。lru链表里面就维护了热点数据的顺序，保证了缓存命中率，头部都是热点数据，如果满了就从链表尾部淘汰那些没被修改过的缓存页。

10. 那如果我全表扫描一次，会不会把lru链表里的缓存页全部来一次大改变?

  这个不会的，buffer pool将lru链表分成了两段，一个段冷数据，一段热数据。然后再搭配一个时间间隔的参数。这样全表扫描的数据就都会再冷数据区，只有指定时间间隔后再被访问，才会到热数据区，这样能保证热点数据不会随便的被全表扫描，或者预读的数据给置换掉。

11. 事物是什么？

  事务就是acid，innodb的原子性是靠undolog保证，持久性是靠redolog，隔离性是靠行锁和mvcc实现，一致性就是靠上面三个来共同保证。

12. innodb怎么是怎么解决幻读的?

  把隔离性调到串行化就解决幻读了,主要还是靠临键锁解决的幻读吧。幻读的基本表现就是，比如我统计大于5的有多少行，在事务中别人突然在6的位置插入一行，这时候再读，就多了一行，产生幻读。隔离级别调到串行化后，所有读都会加共享的临建锁。临建锁 就是记录锁+间隙锁 的组合。这时候我再统计大于5的行数 ，就会把大于5的区间全部上锁 ，不让别人插入了。

13. mvcc能解决幻读吗？

  他只能在快照读的情况下解决幻读。到当前读就不行了。如果要说mvcc解决不了幻读，那么它连不可重复读甚至都没有解决。我更愿意认为，他就是在某种程度上解决了幻读。

14. mvcc的工作原理?

  mvcc有两个重要的模块，版本链和读视图。记录行还有两个隐藏参数，一个是保存了该记录最后被操作的事务id，还有一个是undolog回滚指针，指向的是能够回滚到上一个版本的undolog。当我们开启事务后的第一次读，会生成一个读视图，里面存的就是当前活跃的事务id，然后再统计一下里面的最小事务id和最大事务id。下一次再进行读操作的时候，就会和读到的记录里的事务id进行一个比较，如果事务id小于我们读视图的最小事务id，说明这个记录很早就被提交了，是可见的，如果这个这个记录匹配上了视图里的活跃事务id或者大于最大的事务id，说明在第一次快照读的时候这个事务是未提交的，需要沿着版本链回滚。它在rc级别每次读都会生成一个读视图，在rr级别只有第一次读才会生成一个读视图，大概就是这样。

15. 数据库经常会成为一个性能瓶颈点，开发中，如何去调优？

  根据我以往的开发经验，这方面的优化分了几个层面，从底层到上层，优化的成本会更低，效果也会更加凸显。先从最上层说起吧，有些查询的瓶颈，是可以靠调整业务功能来解决的。比如百度搜索只能查77页避免深翻页，比如阿里双十一提前一个月预售分担流量，比如我们之前有个活动要展示多少人已经领取，后面直接用了个随时间增长的假数据。然后再到我们一些系统架构的优化，比如redis缓存，比如数据库连接池，比如分库分表，读写分离。还有一些代码开发规范，像长事务拆成多个小事务，容易产生锁竞争的语句放在最后执行。接着是一些表设计，比如适当的增加冗余字段，主键需要时自增id，字段非空限制。像sql层面的编写，核心就是记得加索引，记得避免索引失效。像联表字段类型不匹配，模糊查询，索引列函数运算这些提前避免，还是有很多忽略的问题或者是优化器的毛病，我们加强监控就好了。出现慢查询的时候去用explain分析一下执行计划。然后再继续优化，问题都不大。再到最后就是数据库参数的调优，比如增大buffer pool，修改一些提交模式，甚至升级硬件。这些基本都是dba管理的，我们也很少操心。核心点其实是监控啦，哪里慢了改哪里。

16. 我们的表设计，为啥主键要自增？

  主键自增插入都是追加的形式，可以避免从中间插入，产生页分裂的问题。

17. 说到字段冗余。那我改了一个字段，其他地方要怎么更新啊？

  这其实就是一个取舍吧，也是一种写扩散的思想，为了查询方便，更新的时候就复杂。我们可以用cdc的方式通过canel监听表的变化，所有关心变化的冗余字段，去监听消息然后做出修改就好了。

18. 为什么Myisam比InnoDB快？

• Myisam只缓存了索引块，减少了缓存换入换出的频率。
• Myisam的表结构非聚簇索引，而InnoDB是聚簇索引，InnoDB的二级索引需要找到id回表查一级索引，而Myisam所有的索引直接指向数据行的存储位置offset。
• InnoDB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护，而Myisam 表锁.牺牲了写性能,提高了读性能.

19. 为什么binlog没有crash-safe能力？

  binlog是server层的逻辑日志，只保存数据执行的逻辑语句。它并不感知存储层的数据具体是否落盘，哪些落盘成功。

20. count(0)和count(*)和count(id)有啥区别？

  count(id)，虽然能达到想要的效果，但是效率会更慢。采用count(*)mysql优化器会选择非聚簇索引的最小二级索引来统计全行，这样一页可以存更多的记录行，减少跳页导致的随机IO。

  mysql官网提到过count(0)和count(*)并没有区别，但我们最好用count(*)是因为这个是sql标准定义的一个规范，别的数据库也会支持count(*)的规范，但别的数据库不一定会支持count(0)