Mysql结构简介

 

1.数据库切分概述

      在互联网时代，海量数据的存储与访问成为系统设计与使用的瓶颈问题，对于海量数据处理，按照使用场
景，主要分为两种类型：联机事务处理（OLTP）和联机分析处理（OLAP）。

1. 联机事务处理（OLTP）也称为面向交易的处理系统，其基本特征是原始数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果。
2. 联机分析处理（OLAP）是指通过多维的方式对数据进行分析、查询和报表，可以同数据挖掘工具、统计分析工具配合使用，增强决策分析功能。

MySQL是一个开放源代码的关系数据库(OLTP)管理系统。

2.MySQL逻辑架构

2.1.Connectors

 指的是不同语言中与SQL的交互

2.2 Management Serveices & Utilities： 

系统管理和控制工具

2.3 Connection Pool: 连接池

管理缓冲用户连接，线程处理等需要缓存的需求。

负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，
接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

2.4 SQL Interface: SQL接口。

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

2.5 Parser: 解析器。

SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。

在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。
主要功能：
a . 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
b.  如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

2.6 Optimizer: 查询优化器。

SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query（sql语句） ，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果

他使用的是“选取-投影-联接”策略进行查询。
       用一个例子就可以理解： select uid,name from user where gender = 1;
       这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
       这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
       将这两个查询条件联接起来生成最终查询结果

2.7 Cache和Buffer： 查询缓存。

他的主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做
一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

2.8 、存储引擎接口

存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。

     从图中还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。
    注意：存储引擎是基于表的，而不是数据库。

 

3.InnoDB引擎

• 将数据存储在表空间中，表空间由一系列的数据文件组成，由InnoDb管理

• 支持每个表的数据和索引存放在单独文件中(innodb_file_per_table)；
• 支持事务，采用MVCC来控制并发，并实现标准的4个事务隔离级别，支持外键。
• 索引基于聚簇索引建立，对主键查询有较高性能。
• 数据文件的平台无关性，支持数据在不同的架构平台移植
• 能够通过一些工具支持真正的热备，如XtraBackup等；
• 内部进行自身优化如采取可预测性预读，能够自动在内存中创建bash索引等

3.1innodb整体的体系结构

innodb的整个体系架构就是由多个内存块组成的缓冲池及多个后台线程构成。

缓冲池主要分为三个部分：redo log buffer、innodb_buffer_pool、innodb_additional_mem_pool。（解决cpu速度和磁盘速度的严重不匹配问题）

• innodb_buffer_pool由包含数据、索引、insert buffer ,adaptive hash index,lock 信息及数据字典。
• redo log buffer用来缓存重做日志。
• additional memory pool:用来缓存LRU链表、等待、锁等数据结构。

后台进程分为：master thread，IO thread，purge thread，page cleaner thread。保证缓存池和磁盘数据的一致性（读取、刷新），并保证数据异常宕机时能恢复到正常状态。

• master thread负责刷新缓存数据到磁盘并协调调度其它后台进程。
• IO thread 分为 insert buffer、log、read、write进程。分别用来处理insert buffer、重做日志、读写请求的IO回调。
• purge thread用来回收undo 页
• page cleaner thread用来刷新脏页。

3.2innodb内部协调管理

一条SQL进入MySQL服务器。会依次经过连接池模块（进行鉴权，生成线程），查询缓存模块（是否被缓存过），SQL接口模块（简单的语法校验），查询解析模块，优化器模块（生成语法树），然后再进入innodb存储引擎。进入innodb后，首先会判断该SQL涉及到的页是否存在于缓存中，如果不存在则从磁盘读取相应索引及数据页加载至缓存。如果是select语句，读取数据(使用一致性非锁定读)，并将查询结果返回至服务器层。如果是DML语句，读取到相关页，先试图给这个SQL涉及到的记录加锁。加锁成功后，先写undo 页，逻辑地记录这些记录修改前的状态。然后再修改相关记录，这些操作会同步物理地记录至redo log buffer。如果涉及及非唯一辅助索引的更新，还需要使用insert buffer。事务提交时，会启用内部分布式事务，先将SQL语句记录到binlog中，再根据系统设置刷新redo log buffer至redo log，保证binlog与redo log的一致性。提交后，事务会释放对这些记录所加的锁，并将这些修改的记录所在的页放入innodb的flush list中，等待被page cleaner thread刷新到磁盘。这个事务产生的undo page如果没有被其它事务引用(insert的undo page不会被其它事务引用)，就会被放入history list中，等待被purge线程回收。

　　需要注意的是：
　　a.脏页的刷新采用的是checkpoint机制
　　b.DML语句不同undo页的格式也会不同。insert类型的undo log只记录了主键及对应的主键值，而update、delete则记录了主键及所有变更的字段值
　　c.一条设计不好的SQL，可能会导致大量的离散读、加载很多冗余的数据页至缓存中

3.3innodb内部关键技术

checkpoint:
如果我们有足够大的内存且可以接受漫长的数据库恢复时间的话，那我们没有必要引入checkpoint机制。checkpoint通过标志redo log不可用，刷新缓存中的脏页，解决内存容量瓶颈，缩短恢复时间。innodb会在四种情况下会触发checkpoint：master thead的定时刷新、LRU列表中没有足够的空闲页时（脏页太多时）、redo log不可用时（async/sync flush checkpoint）及数据库关闭时。checkpoint有两种工作模式sharp checkpoint和 fuzzy checkpoint。一般情况下都是使用fuzzy checkpoint(刷新部分脏页)，只有数据库关闭且设置了innodb_fast_shutdown=1时，才会使用sharp checkpoint(刷新所有脏页回磁盘)。innodb系统日志会根据redo log的生命周期保存四个LSN号。分别是：当前系统LSN最大值、当前已经写入日志文件的最大LSN号、已经刷新到磁盘的数据页的最大LSN、已经写入检查点的LSN，后面的LSN值总是小于等于前面的LSN值。当数据库宕机时，可以通过只恢复检查点的LSN至已经写入到日志文件的最大LSN之间的数据来恢复数据库。需要注意的是当脏页容量触碰到低水位线时，调用async flush checkpoint异步刷新脏页至磁盘，当脏页容量触碰到高水位线时会调用sync flush checkpoint 疯狂刷新脏页，磁盘会很忙，存在IO风暴。低水位线=75%total_redo_log_file_size 高水位线=90%total_redo_log_file_size
insert buffer：
专门为维护非唯一辅助索引的更新设计的。因为innodb的记录是按主键的顺序存放的，所以主键的插入是顺序的，而聚集索引对应的辅助索引的更新则是离散的，为了避免大量离散读写，先检查要更新的索引页是否已经缓存在了内存中，如果没有，先将辅助索引的更新都放入缓冲（inset buffer区），等待合适机会（master thread的定时操作，索引块需要被读取时，insert buffer bitmap检测到对应的索引页不够用时）进行insert buffer和索引页的合并。因为辅助索引缓存到insert buffer中时并不会读取磁盘上的索引页，以至于无法校验索引的唯一性，所以不适用唯一辅助索引。innodb中所有的非唯一辅助索引的insert buffer均由同一棵二叉树维护。二叉树的非叶子节点由space（表空间id）+marker（兼容老版本的insert buffer）+offset（在表空间中的位置）构成，叶子节点由space+marker+offset+metadata(进入顺序+类型+标志)+辅助索引构成，进行merge合并时，按顺序进行回放。mysql5.1之后，insert buffer支持change_buffer，还可以缓冲非唯一辅助索引的update\delete操作。insert buffer的二叉树结构是存放在共享表空间中的，所以通过独立表空间恢复表时，执行check table操作会失败，因为辅助索引的数据可能还在insert buffer中，需要通过repair table 重建表上全部的辅助索引。为了保证每次 merge insert buffer成功，表空间中每隔256个连续区就有一个insert buffer bitmap页用来记录索引页的可用空间。insert buffer bitmap页总是处于这个连续区间的第二页，每个索引页在insert buffer bitmap中占4 bit。可以通过show engine innodb stauts\G;查看insert buffer and adaptive hash index 查看insert buffer的合并数量、空闲页数量、本身的大小、合并次数及索引操作次数。通过索引操作次数与合并次数的的比例可以判断出insert buffer所带来的性能提升。
double write：
因为脏页刷新到磁盘的写入单元小于单个页的大小，如果在写入过程中数据库突然宕机，可能会使数据页的写入不完成，造成数据页的损坏。而redo log中记录的是对页的物理操作，如果数据页损坏了，通过redo log也无法进行恢复。所以为了保证数据页的写入安全，引入了double write。double write的实现分两个部分，一个是缓冲池中2M的内存块大小，一个是共享表空间中连续的128个页，大小是2M。脏页从flush list刷新时，并不是直接刷新到磁盘而是先调用函数（memcpy），将脏页拷贝到double write buffer中，然后再分两次，每次1M将double write buffer 刷新到磁盘double write 区，之后再调用fsync操作，同步到磁盘。如果应用在业务高峰期，innodb_dblwr_pages_written:innodb_dblwr_writes远小于64:1，则说明，系统写入压力不大。虽然，double write buffer刷新到磁盘的时候是顺序写，但还是是有性能损耗的。如果系统本身支持页的安全性保障（部分写失效防范机制），如ZFS，那么就可以禁用掉该特性（skip_innodb_doublewrite）。
adaptive hash index：
innodb会对表上的索引页的查询进行监控，如果发现建立hash索引能够带来性能提升，就自动创建hash索引。hash索引的创建是有条件的,首先是必定能够带来性能提升。其次数据库以特定模式的连续访问超过了100次，通过该模式被访问的页的访问次数超过了1/16的记录行数。自适应hash根据B+树中的索引构造而来，只需为这个表的热点页构造hash索引而不是为整张表都构建。同样可以通过show engine innodb status\G中的 insert buffer and adaptive hash index(hash searches/s non-hash searches)查看hash index的使用情况。 
刷新邻近页：
innodb进行脏页刷新时，会检查该脏页所在区内是否还存在其它脏页，如果存在则一同刷新，通过AIO，进行IO合并，一定程度上减少了IO压力。但是它也存在一个问题，就是把原本不怎么脏的页也刷新到了磁盘。可能很快这个不怎么脏的页又被读取到缓冲中，又增加了IO的压力。对于普通的机械盘开启这个特性可以带来很大的性能提升，但是如果是读写速度非常高的随机盘，可以关闭这个特性（innodb_flush_neighbors=0）性能反而会更好。（因为对该特性的维护也是需要消耗性能的）
异步IO：
mysql 5.5之前并不支持异步IO，而是通过innodb代码模拟实现。5.5之后开始提供AIO支持。数据库可以连续发出IO请求，然后再等待IO请求的处理结果。异步IO带来的好处就是可以进行IO合并操作，减少磁盘压力。要想mysql支持异步IO还需要操作系统支持，首先操作系统必须支持异步IO，像windows，linux都是支持的，但是 mac osx却不支持。同时在编译和运行时还需要有libaio依赖包。可以通过设置innodb_use_native_aio来控制是否启用这个特性，一般开启这个特性可以使数据恢复带来75%的性能提升。
事务：
innodb中一个逻辑事务包含一组物理事务。不管是物理事务还是逻辑事务，都需要满足ACID特性（原子性，一致性，隔离性，及持久性）。如果一个逻辑事务需要操作多个页，那么它对每个页的操作会以一个物理事务来进行。物理事务对页进行处理时，先根据页的space_id,page_no找到对应的页，再试图对该页加锁。如果申请加的锁和该页原本已经加上的锁冲突，则进入等待状态。否则直接加锁，并将该页加入到memo动态数组中，之后物理事务就可以访问这个页了。如果对该页进行的是变更操作，那么针对这些操作就会在local buffer中产生redo log record记录。当物理事务提交时，会在redo log record后追加一串结束标志日志来保证物理事务的完整性。物理事务提交后，redo log record会被提交到redo log buffer的块中，一个块的大小是512字节，一个redo log record可能会出现在多个块中，这取决于redo log record的长度（每个块开始的两个字节记录的是第一个mtr在该段中开始的位置，如果是0，则表明还是上一个block的同一个mtr）。同时被分配到一个LSN号，LSN号确定了它在redo log中的位置，这个LSN号也将会被写入到物理事务操作的页的页头中。物理事务提交后，会检查memo数组中的这些页是否被修改，若修改了则将其加入到innodb的flush list中。flush list中只能存放一个关于这个页的记录。如果页没有被修改，则直接释放加在它上面的锁。当逻辑事务提交时，会将redo log buffer以块为单位顺序刷新到redo log中。多个逻辑事务并发时，可能会出现多个逻辑事务的物理事务交叉记录在redo log buffer中。也会出现未提交的逻辑事务的部分物理事务日志持久化在redo log中。但这并不会造成日志重做的时候，重做未提交的逻辑事务。原因是，虽然重做的时候是以物理事务为单位进行重做，但它会判断该物理事务所在的逻辑事务包含的所有物理事务是否完整，如果不完整，那么该逻辑事务所涉及的所有物理事务都不会重做。物理事务的工作过程，可以很好的解释一个逻辑事务在执行的过程中是在不断地写redo日志，而且不断地往flush list中加塞脏页的。
innodb还支持内外部分布式事务。分布式事务的实现是：应用通过一个事务管理器实现对多个相同或不同的数据库实例的事务管理。分布式事务与本地事务的区别是多了一个prepare的阶段，待收到所有节点的同意信息后再commit或rollback。内部分布式事务最常见的是binlog和innodb存储引擎之间。事务提交时会先写binlog再写redo log，因为有内部分布式事务，在写完binlog宕机的情况下，mysql再重启会先检查准备的uxid事务是否已经提交，若没有则存储引擎层再做一次提交。
MVCC：
多版本并发控制，mysql仅在RC，RR隔离级别下支持MVCC。主要是结合undo log来实现的一个数据的多个版本，保证读不会堵塞写，写也不会堵塞读来提高并发。mvcc下，select操作默认是一致性非锁定读，除非显式给select加in share或for update锁，才会使用一致性锁定读。
多隔离级别：
innodb支持四种隔离级别RU\RC\RR\serializable。RU不使用MVCC，读取的时候也不加锁。RC利用MVCC都是读取记录最新的版本，RR利用MVCC总是读取记录最旧的版本，并通过next-key locking来避免幻读，serializable不使用MVCC，读取记录的时候加共享锁，堵塞了其它事务对该记录的更新，实现可串行化。隔离级别越高，维护成本越高，并发越低。RC隔离级别下要求二进制日志格式必须是row格式的，因为RC隔离级别下，不会加gap锁，不能禁止一个事务在执行的过程中另一个事务对它的间隙进行操作的情况。这种情况下，对于事务开始的和提交的顺序是先更改后提交，后更改先提交的情况，statement格式的binlog只会是按照事务提交的顺序进行记录。这可能会导致复制环境的slave数据和master数据不一致。通过设置innodb_locks_unsafe_for_binlog=1也可以使用statement格式，但是主从数据的一致性没法保证。