一. MySQL体系结构


MySQL Server 架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。



1. 网络连接层：


提供与MySQL服务器建立的支持， 我们可以通过服务端编程技术与MySQL建立连接如：Java, C


Python等



2. 服务层：


服务层是MySQL的核心， 包含系统管理和控制工具， 连接池， SQL接口， 解析器， 查询优化器， 缓存六个部分



连接池： 负责存储和管理客服端连接， 一个线程负责管理一个连接



系统管理和控制工具： 例如备份恢复、安全管理、集群 管理等



SQL接口：用于接收客服端发送的SQL命令， 并且返回查询结果。 比如 DML 、 DDL 、存储过程、视图、触发器等。



解析器：负责将接收到的SQL解析成解析树， 然后更加MySQL规则进行合法校验，生成新的解析树



查询优化器： 当解析树通过后， 将交由优化器转化为执行计划， 然后与存储引擎进行交互



缓存：  缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓

存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。



3.  存储引擎层


MySQL 存储引擎是插件式的， 服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现有 很多种存储引擎，各有各的特点，最常见的是 MyISAM 和 InnoDB 。每个表都可以设置不同的引擎



4. 系统文件层


主要包含日志文件，数据文件，配置文件， pid 文件， socket 文件等。



日志文件： 

错误日志（Error log）， 通用查询日志（General query log）


二进制日志（ binary log）： 主要用于数据库恢复和主从复制。 


慢查询日志（ Slow query log ）



范例：查看命令：show variables like %binlog%;



配置文件： 


用于存放 MySQL 所有的配置信息文件，比如 my.cnf 、 my.ini 等。



数据文件： 


db.opt 文件：记录库的的字符集和校验规则   frm文件： 存储表的元数据，每张表对应一个frm件 



MYD文件和MYI文件：MyISAM 存储引擎专用， 分别存储数据和索引 



ibd文件和 IBDATA 文件：存放 InnoDB 的数据文件（包括索引）。IDB存储引擎有俩种表空间， 分为独立表空间（idb） 和 共享表空间(ibdata)



pid文件： 存放自己进程的pid



socket文件： 可以实现 用户在 Unix/Linux 环境下客户端连接可以不通过 TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL 。



二. MySQL的运行机制


a. 建立连接后查询缓存， 缓存命中直接返回


b. 建立连接后， 到解析器生成SQL解析数， 到预处理进行解析树合法校验， 生成新的解析树， 在到查询优化器生成执行计划，再由查询执行引擎与系统文件进行交互，返回数据。



1. 建立连接： MySQL是半双工连接指的某一时刻，要么发送数据，要么接收数据，不能同时。例如早期对讲机，   对于每一个 MySQL 的连接，时刻都有一个 线程状态来标识这个连接正在做什么。



2. 线程状态可以通过：  show processlist; //查看用户正在运行的线程信息， root 用户能查看所有线程，其他用户只能看自 己的



3. 查询缓存： 即是开了查询缓存也不能SQL缓存的情况：


        查询语句使用SQL_NO_CACHE


        查询的结果大于 query_cache_limit 设置


        查询中有一些不确定的参数，比如 now()


show variables like '%query_cache%'; （Qcache） //查看查询缓存是否启用，空间大小，限制等



4. 查询优化器： 可分为静态优化（编译时优化） 和 动态优化（运行时优化）


等价变换策略 (5=5 and a>5 改成 a > 5)  (a < b and a=5 改成b>5 and a=5)基于联合索引，调整条件位置等


b. 基于联合索引，调整条件位置等, InnoDB 引擎 min函数只需要找索引最左边,InnoDB 引擎 max函数只需要找索引最右边, MyISAM 引擎 count(*) ，不需要计算，直接返回


c. 提前终止查询， 使用了 limit 查询，获取 limit 所需的数据，就不在继续遍历后面数据


d.  in的优化MySQL 对 in 查询，会先进行排序，再采用二分法查找数据



5. 查询执行引擎负责执行 SQL 语句, 会先根据表的引擎类型， 以及对应得API接口与底层存储引擎和物理文件进行交互。 如果开启查询缓存，先进结果做缓存操作。返回结果过多，采用增量模式返回



三. MySQL存储引擎：



InnoDB ：支持事务，具有提交，回滚和崩溃恢复能力，事务安全


MyISAM ：不支持事务和外键，访问速度快


 InnoDB 和 MyISAM 进行对比：


1. 事务和外键


InnoDB支持事务和外键， 适合insert, update频繁等常景， 反之


2. 锁机制


InnoDB支持行级锁， 锁定指定记录和基于索引得加锁。 MyISAM锁整张表


3. 索引结构


InnoDB使用聚集索引（聚簇索引) 索引和记录在一起存储  反之。 


4. 并发处理能力


InnoDB的读写阻塞可以根据锁的隔离级别有关，采用多版本控制MVVC来支持高并发


5. 存储文件


InnoDB： 一个 .frm 表结构文件，一个 .ibd数据文件  最大支持 64TB



MyISAM： 一个 .frm 表结构文件，一个 MYD 表数据文件，一个 .MYI 索引文件。默认支持256TB





四. InnoDB的存储结构： 

主要分为内存结构和磁盘结构两大部分。



1. 内存结构：


主要包括 Buffer Pool 、 Change Buffer 、 Adaptive Hash Index 和 Log Buffffer 四大组件。



Buffer Pool： BP以Page为单位， 默认16kb, 采用链表数据结构管理Page,  在 InnoDB 访问表记录和索引时会在 Page 页中缓存，以后使用可以减少磁 盘 IO 操作，提升效率。



Page的类型： free Page,  clean Page(被使用，未修改),  dirty Page(脏页)



通过 三种链表结构对上述进行管理：


空闲缓冲区


flush list: 刷盘缓冲区管理 dirty page


lru list ：表示正在使用的缓冲区，管理 clean page 和 dirty page


flush list负责脏页的刷盘操作， 而lru list 负责管理页面 的释放和可用性


LRU算法： 把缓冲区分为俩部分(new 和 old) 中间有个midPoint节点， 当加入元素时， 往midPoint插入， 如果该元素访问频繁往new移动，反之往old移动。



Buffffer Pool 配置参：



show variables like '%innodb_page_size%'; // 查看 page 页大小



show variables like '%innodb_old%'; // 查看 lru list 中 old 列表参数



show variables like '%innodb_buffffer%'; // 查看 buffffer pool 参数



我们可以调整 Buffer Pool来达到减少IO操作：建议为总内存大小为 60%-80%



innodb_buffffer_pool_instances 可以设置为多个，这样可以避免缓存争夺。




Change Buffer： 写缓冲区



当进行写操作时， 如果BP没有改记录， 并不会立即前往磁盘， 而是先将CB数据缓存变更。等下次读操作，在到磁盘和CB合并恢复到BP。减少一次磁盘io



写缓冲区，仅适用于非唯一普通索引页，为什么？



由于唯一索引，必须前往磁盘确定唯一性！所以多CB不适合！




Adaptive Hash Index ：自适应哈希索引，用于优化对 BP 数据的查询。主要占用BD的空间， 对BP的热点数据， 建立hash索引， 实现快速访问！




Log Buffer：日志缓冲区， 用于保存日志到磁盘（Redo/ Undo）log，  日志缓冲

区的内容定期刷新到磁盘log文件中。满时会自动将其刷新到磁盘，  当遇到 BLOB 或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘 I/O 。



LogBuffer 主要是用于记录 InnoDB 引擎日志，在 DML 操作时会产生 Redo 和 Undo 日志。



innodb_flush_log_at_trx_commit 参数控制日志刷新行为：0： 每隔1秒,才写入OS cache, 在刷盘



1： 立刻写入OS cache 和 刷盘  2. 立刻写入OS cache, 隔1秒钟刷盘




2. 磁盘结构：



InnoDB 磁盘主要包含 Tablespaces ， InnoDB Data Dictionary ， Doublewrite Buffer 、 Redo Log



和 Undo Logs 。




双写缓冲区（ Doublewrite Buffffer）： 在 BufferPage 的 page 页刷新到磁盘真正的位置前，会先 将数据存在 Doublewrite 缓冲区。如果在 page 页写入过程中出现操作系统、存储子系统或 mysqld 进程崩溃， InnoDB 可以在崩溃恢复期间从 Doublewrite 缓冲区中找到页面的一个好 备份。



使用 Doublewrite 缓冲区时建议将 innodb_flflush_method 设 置为O_DIRECT 。




重做日志（ Redo Log ）：



MySQL 以循环方式写入重做日志文件，记录 InnoDB 中所有对 Buffer Pool 修改的日志。当宕机时， 可以利用Redo Log 自动恢复数据




撤销日志（ Undo Logs ） ：



撤消日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。撤消日志



属于逻辑日志，根据每行记录进行记录。




五. InnoDB的线程模型： 




IO Thread：



在 InnoDB 中使用了大量的 AIO （ Async IO ）来做读写处理，这样可以极大提高数据库的性能。




Purge Thread:



事务提交之后，其使用的 undo 日志将不再需要，因此需要 Purge Thread 回收已经分配的 undo



页。 show variables like '%innodb_purge_threads%';




Page Cleaner Thread:



是将脏数据刷新到磁盘，脏数据刷盘后相应的 redo log 也就可以覆盖，即可以同步数据，又能

达到 redo log 循环使用的目的。会调用 write thread 线程处理。



show variables like '%innodb_page_cleaners%';




Master Thread: 负责调度其他线程， 作用是将数据异步刷新到磁盘， 保证数据的一致性

包含：脏页的刷新（ page cleaner thread ）、 undo 页 回收（purge thread ）、 redo 日志刷新（ log thread ）、合并写缓冲等。内部有两个主处理，分别 是每隔1 秒和 10 秒处理。



每 1秒的操作：刷新日志缓存区， 刷到磁盘； 合并写缓冲区数据； 刷新脏页数据到磁盘，根据脏页比例达到 75% 才操作；



每10秒操作： 刷新脏页数据到磁盘； 合并写缓冲区数据； 刷新日志缓冲区； 删除无用的 undo 页；





六. InnoDB数据文件：



一个ibd数据文件-->Segment（段）-->Extent（区）-->Page（页）-->Row （行）




表空间： 存储多个idb文件， 用于存储索引和记录， 一个文件包含多个段




Segment：用于管理Extent, 分为数据段，索引段， 回滚段。一个表至少俩个段， 一个索引一个数据。 每多创建一个索引， 将会多俩个段




Extent： 区，一个区固定包含 64 个连续的页，大小为 1M 。当表空间不足，直接分配一个区




Page： 用于存储多个 Row 行记录，大小为 16K。 比如数据页， undo 页，系 统页，事务数据页，大的 BLOB 对象页。




Row：  行，包含了记录的字段值，事务 ID （ Trx id ）、滚动指针（ Roll pointer ）、字段指针（ Field pointers ）等信息。



Page 是文件最基本的单位，无论何种类型的 page ，都是由 page header ， page trailer 和 page



body 组成。



七. Undo Log: 


1. 实现事务的原子性==>MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。


2. 实现多版本并发控制（ MVCC）==> 事务一更新数据时， 会先把old data 放入Undo Log 中，

Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。



Undo Log 产生和销毁： Undo Log 在事务开始前产生；事务在提交时，并不会立刻删除 undo



log ， innodb 会将该事务对应的 undo log 放入到删除列表中，后面会通过后台线程 purge thread进



行回收处理。 Undo Log 属于逻辑日志，记录一个变化过程。例如执行一个 delete ， undolog 会记



录一个 insert ；执行一个 update，undolog会记录一个相反的update 。




Undo Log 存储： undo log 采用段的方式管理和记录。在 innodb 数据文件中包含一种 rollback



segment 回滚段，内部包含 1024 个 undo log segment 。可以通过下面一组参数来控制 Undo log 存



储。  show variables like '%innodb_undo%';




八： Redo Log和Binlog




Redo Log 和 Binlog 是 MySQL 日志系统中非常重要的两种机制，也有很多相似之处，下面介绍下两者细 节和区别。



Redo Log： 

实现事务的持久性而出现的产物， 防止在发生故障的时间点，尚有脏页未写入表的 IBD 文件中。在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。




写数据缓存在BP， 减少IO操作， 但是Redo Log每隔1秒就进行刷盘到磁盘Redo Log中，不耗时吗？



因为Redo Log操作是按照顺序进行循环的方式写入文件， 所以耗时比较短， 而直接写数据到磁盘是进行随机操作， 所以耗时比较长。




Binlog日志：



MySQL Server自己的日志的二进制日志，  Binlog 是记录所有数据库表结构变更以及表数据修改的二进制 日志，不会记录 SELECT 和 SHOW 这类操作。 Binlog 日志是以事件形式记录，还包含语句所执行的 消耗时间。开启 Binlog 日志有以下两个最重要的使用场景。



        主从复制： 在主库中开启 Binlog 功能，这样主库就可以把 Binlog 传递给从库，从库拿到

Binlog 后实现数据恢复达到主从数据一致性。



        数据恢复：通过 mysqlbinlog 工具来恢复数据。



开启命令： set global log_bin=mysqllogbin;



需要修改 my.cnf 或 my.ini 配置文件



#log-bin=ON 
#log-bin-basename=mysqlbinlog 
binlog-format=ROW 
log-bin=mysqlbinlog



使用 binlog 恢复数据 



//按指定时间恢复 
mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stop- datetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p
//按事件位置号恢复 
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p



可以通过设置 expire_logs_days 参数来启动自动清理功能。默认值为 0 表示没启用。设置为 1 表示超



出 1 天 binlog 文件会自动删除掉。




Redo Log和Binlog区别：



1.  Redo Log 是属于 InnoDB 引擎功能， Binlog 是属于 MySQL Server 自带功能，并且是以二进制 文件记录。




2. Redo Log 属于物理日志，记录该数据页更新状态内容， Binlog 是逻辑日志，记录更新过程。




3.  Redo Log 日志是循环写，日志空间大小是固定， Binlog 是追加写入，写完一个写下一个，不 会覆盖使用。




Redo Log 作为服务器异常宕机后事务数据自动恢复使用， Binlog 可以作为主从复制和数据恢 复使用。 Binlog 没有自动 crash-safe 能力。