MySQL数据库管理系统——理论篇

一、基础知识 1.执行一条select语句，MySQL内部发生了什么？

答：
主要了解MySQL 内部的架构，知道每个模块的功能。

第一步：连接器

	建立客户端和服务端之间的TCP连接，
	管理连接，
	校验用户身份；

第二步：查询缓存

只针对SQL查询语句，查询语句如果命中查询缓存则直接返回，否则继续往下执行。

第三步：解析SQL语句

通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；

第四步：执行SQL语句

预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

总结：

可以看到， MySQL 的架构共分为两层：Server 层和存储引擎层。

Server 层负责建立连接、分析和执行 SQL：

MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。
另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。

存储引擎层负责数据的存储和提取：

	支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。
	我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

二、事务 1.什么是事务？

答：
事务就是一个完整的业务逻辑，是一个最小的工作单元，不可再分。
如转账业务，所有对数据库的操作是不可分割的，要么全部执行成功 ，要么全部失败。

2.事务有哪些特性？

答：

3.并发事务会带来什么问题？

答：
MySQL 服务端是允许多个客户端连接的， MySQL 会同时处理多个事务。

	对于并发事务A和B：
	脏读：事务A读取到了事务B还未提交的数据
	不可重复读：事务A前后读取的数据不一致（因为事务B在此期间改变了数据并提交，正常逻辑，事务A前后读取的数据都是对的）
	幻读：事务A前后读取的数据一致（即使事务B在此期间改变了数据并提交，事务A前后读取的数据仍然是一样的，幻觉）

4.如何解决并发事务带来的问题？（事务的隔离级别有哪些？）

答：
SQL 标准提出了四种隔离级别来规避这些现象。


要解决脏读现象，就要升级到「读已提交」以上的隔离级别；
要解决不可重复读现象，就要升级到「可重复读」的隔离级别。

不过，要解决幻读现象不建议将隔离级别升级到「串行化」，因为这样会导致数据库在并发事务时性能很差。
InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它通过next-key lock 锁（行锁和间隙锁的组合）来锁住记录之间的“间隙”和记录本身，防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。

5.如何实现四种隔离级别？

答：

三、Buffer Pool缓冲池？ 1.什么是Buffer Pool缓冲池？为什么要有Buffer Pool缓冲池？

答：
Buffer Pool缓冲池就是缓存，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

2.Buffer Pool 有多大？缓存了什么？

答：
Buffer Pool 是在 MySQL 启动的时候，向操作系统申请的一片连续的内存空间，默认配置下 Buffer Pool 只有 128MB 。
可以通过调整 innodb_buffer_pool_size 参数来设置 Buffer Pool 的大小，一般建议设置成可用物理内存的 60%~80%。

按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。
Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 undo 页，插入缓存、自适应哈希索引、锁信息等等。

3.如何管理Buffer Pool ？ 1）如何管理空闲页？

答：
建立Free List （空闲页链表）

2）如何管理脏页？

答：
建立Flush List （脏页链表）

3）如何管理干净页和脏页？（如何提高缓存命中率？）

答：
建立LRU List（Least recently used），管理脏页+干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。

针对出现的预读失效和Buffer Pool 污染问题，InnoDB 对 LRU 做了一些优化：

预读失效是被提前加载进来的数据页，并没有被访问，却占据了LRU链表头部。

	将 LRU 链表 分为young 和 old 两个区域，加入缓冲池的页，优先插入 old 区域；页被访问时，才进入 young 区域

Buffer Pool 污染是当某一个 SQL 语句扫描了大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了。

当[某页被访问」且「 old 区域停留时间超过 innodb_old_blocks_time 阈值（默认为1秒）」**时，才会将页插入到 young 区域，否则还是插入到 old 区域。

4）脏页什么时候被刷入磁盘？

答：
脏页不是每次修改数据都刷入磁盘，这样性能会很差，因此一般都会在一定时机进行批量刷盘。
InnoDB 的更新操作采用的是 先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。