谦虚使人退步，如果自己都不为自己骄傲，那别人怎能为你骄傲？
--《天珠变》
目录
1.Mysql基本架构
Mysql架构如上图所示，包括
-
管理服务和工具组件：系统管理和控制工具，例如备份恢复、Mysql复制、集群等
-
连接池组件：管理、缓冲用户的连接，线程处理等需要缓存的需求
-
SQL接口组件：接受用户的SQL命令，并且返回用户需要查询的结果
-
查询分析器组件：SQL命令传递到解析器的时候会被解析器验证和解析(权限、语法结构)
-
优化器组件：SQL语句在查询之前会使用查询优化器对查询进行优化
-
缓存组件：如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据
-
插件式存储引擎：存储引擎是如何管理操作数据（存储数据、如何更新、查询数据等）的一种方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）
-
物理文件
2.存储引擎比较
下表是Mysql几种存储引擎的对比，oracle不存在存储引擎的概念，同时列入oracle进行简单对比 
-
InnoDB存储引擎
从MySql 5.5.8版本开始，InnoDB存储引擎是默认的存储引擎。InnoDB通过多版本并发控制（MVCC）获得高并发性，支持事务并实现了四种隔离级别，支持行级锁和外键。InnoDB每张表都是按照主键的顺序存放的，如果没有显示指定主键，InnoDB存储引擎会给每行生成一个6字节的ROWID作为主键。
-
MyISAM存储引擎
MyISAM存储引擎不支持事务，采用表锁设计，支持全文索引，在Mysql 5.5.8版本之前是默认的存储引擎。MyISAM只缓存索引文件但不会缓存数据文件，数据文件由操作系统进行缓存，其他的存储引擎是有LRU算法进行数据文件的缓存。
-
Memory存储引擎
Memory存储引擎使用内存存储数据，所以速度非常快，但是数据库重启或崩溃，数据会消失，适合用于存储临时数据的临时表。Memory默认使用哈希索引，而不是B+树索引。
-
Archive存储引擎
Archive存储引擎只支持Insert和Select,使用zlib算法对数据进行压缩后存储，压缩比可以达到10:1，非常适合存储归档数据，使用行锁提供高性能的并发新增操作。
3.InnoDB存储引擎
优势：InnoDB是第一个完整支持ACID的Mysql存储引擎，特点是行锁设计、支持MVCC、支持外键、支持一致性非锁定读，同时被设计成最有效的利用内存和CPU，InnDB在1.2.x版本开始支持全文索引。
InnoDB在实现上维护了一个内存池，内存池有多个内存块组成，同时维护了多个后台线程。
内存池负责
-
维护所有进程/线程需要访问的多个内部数据结构
-
缓存磁盘上的数据，方便快速读取，在对磁盘数据修改之前先在这里缓存
-
redo log缓存
后台线程负责
-
刷新内存池中的数据，确保缓存的数据是最新的数据
-
将已修改的数据文件刷新的磁盘文件中
3.1 内存
InnoDB内存主要由缓冲池、重做日志缓冲以及额外内存池三部分组成。
3.1.1 缓冲池
InnoDB存储引擎是基于磁盘进行数据存储的，为了解决CPU速度与磁盘速度之间的鸿沟，引入了缓冲池。
缓冲池是内存中的一块区域，通过内存弥补磁盘速度的不足。
在Mysql进行数据查询时，会先判断该页是否在缓冲池中存在，存在则直接读取该页；如果不存在会从磁盘中将该页读取到缓冲池中。
在Mysql进行数据修改时，会先修改缓冲池中的页，然后再通过checkpoint机制以一定的频率刷新到磁盘。
缓冲池中存储有数据页、索引页、undo页（存放数据修改被修改前的值，用于数据回滚）、插入缓存、自适应哈希索引、锁信息以及数据字典的信息，数据页和索引页占缓冲池的绝大部分空间。InnoDB支持通过innodb_buffer_pool_instance参数设置多个缓冲池实例，每个页会根据哈希值存储到不同的缓冲池实例中，多个缓冲池实例可以减少数据库内部的资源竞争，增加并发能力。
3.1.2 LRU List
LRU算法是最近最少使用算法，将最频繁使用的数据维护在列表前端，最少使用的页维护在列表尾端，InnoDB 存储引擎使用了优化后的LRU 算法对内存进行管理。
在InnoDB 存储引擎中引入了midpoint的概念，新读取到的页并不是直接放到列表首部，而是插入到距离列表尾端37%的位置，midpoint之前的部分称为new列表，也叫热点数据，midpoint之后的部分称为old列表。
midpoint 的位置可以通过参数innodb_old_blocks_pct进行设置，默认为37,可通过show variables like 'innodb_old_blocks_pct'查看。
InnoDB 引入midpoint的原因是：避免非热点数据读出并放置到列表首部导致热点数据被从缓冲池中刷新出导致降低缓冲池的命中率。
比如索引或者数据扫描操作可能会读取很多页甚至所有页，但这些数据可能只使用一次而并非是热点数据，如果直接加载到列表头部很可能导致真正的热点数据被移出缓冲池。
还可以通过设置innodb_old_blocks_time决定多长时间后加载到mid位置的数据被加入到LRU列表的热端,默认为1000ms，如果超过1s还没有被刷出就会移动到热点区域。
通过show engine innodb status可以观察两个指标： youngs/s（每秒移动到新区域的有多少页）和non-youngs/s（每秒没有移动到新区域的有多少页）
youngs/s过大的原因是innodb_old_blocks_time过小或者innodb_old_blocks_pct过大。
non-youngs/s过大的原因可能是存在严重的全表扫描导致数据频繁被刷出或者innodb_old_blocks_time过大或者innodb_old_blocks_pct过小。
从这点看，我们就可以知道为什么在实际的开发中我们要避免全表扫描的出现，因为全表扫描可能导致缓冲池的数据被污染，从而导致缓冲池命中率下降，影响读写性能,我们可以通过"select pool_id,hit_rate from information_schema.INNODB_BUFFER_POOL_STATS"观察缓冲池的命中率,最大值为1000，表示千分之一千的命中率，如果值小于950即命中率小于95%我们就需要检查是否存在严重的全表扫描问题。
3.1.3 Free List
FreeList用于存储空闲页的信息，Free List的数据结构是一个双向链表，每个节点存储空白缓存页的信息，即每个节点指向一个空白的缓存页，当从磁盘中读取一个数据页放到缓冲池中时，会先从Free List中获取一个空闲页，然后将数据存储到该空闲页中，相应的，该页也会从FreeList中移除并加入到LRU List中，当数据页被LRU List排除时又会清除数据并加入到Free List中。
FreeList维护的页和LRU List维护的页并不是缓冲池的全部，还有一部分内存会分配给锁信息、自适应哈希索引等。
3.1.4 Flush List和脏页
当LRU List中的页发生修改后，称该页为脏页，表示缓冲池中的数据与磁盘中的数据不一致。
数据库会通过checkpoint机制将脏页刷新到磁盘中，Flush列表用于维护待处理的脏页，同一个脏页会同时存在与LRU List和Flush List中。LRU列表用于管理缓冲池中页的可用性，Flush List用于管理将页刷新会磁盘，互不影响。
这里再提一下checkpoint机制的设计思路：
如果缓冲池的页信息变更时就立马刷新回磁盘会带来巨大的开销，降低数据库的性能，如果在数据刷新回磁盘前发生宕机，就会导致数据不可恢复，所以InnoDB采用了Write Ahead Log策略，当事务提交时，先写重做日志，再修改页，当发生宕机时通过重做日志进行数据恢复。
但是重做日志不可能无限大，即使可以做到无限大，对全量的重做日志进行恢复耗时也是巨大的，可能需要几个月甚至几年的时间。
所以引入了checkpoint技术，当数据库发生宕机时，checkpoint之前的数据已经全部写入到磁盘了，只需要恢复checkpoint之后的重做日志即可，可以大大缩短恢复时间。
另外，当LRU List要淘汰的页是一个脏页时，也会强制触发checkpoint,将数据刷新到磁盘。
checkpoint什么时候触发数据刷新，每次刷新多少页呢？
InnoDB使用了两种checkpoint机制：Sharp checkpoint和Fuzzy checkpoint.
Sharp checkpoint在数据库关闭时将所有的脏页刷新到磁盘。
Fuzzy checkpoint每次刷新一部分脏页到磁盘，可能存在多种情况，比如
-
Master Thread会以异步的方式以一定的时间间隔将脏页刷新到磁盘；
-
为了保证LRU List至少有一些空闲页可用，会在一个Page cleaner线程中检查并淘汰尾部的页，如果这些页中有脏页，也会触发checkpoint,检查的可用页数量配置。
-
脏页数量过大时会强制进行checkpoint,目的是确保缓冲池中有足够的空闲页，默认当缓冲池中脏页数量占据75%时强制进行checkpoint.
3.1.5 change Buffer
InnoDB存储引擎引入插入缓存(Insert Buffer)用以优化非唯一索引的非聚簇索引的新增，当需要新增一个非唯一索引的非聚簇索引时，会先判断该非聚簇索引页是否在缓冲池中，如果在则直接插入，如果不在会先写入到插入缓存中，然后以一定的频率将多个插入合并到一个操作中，大大提高了非聚簇索引的插入性能。
之所以不支持唯一索引，是因为在插入缓冲时并不会去查找索引页来判断索引的唯一性。
为了防止在写密集的情况下插入缓存占用过多的内存，可以通过pool_size_pre_max_size设置插入缓存的大小，默认情况下插入缓存可以占到缓冲池大小的1/2。
目前InnoDB不仅支持insert buffer ,还支持update、delete操作，统称为change buffer。
Master Thread每秒或者每10秒进行一次Merge Insert Buffer的操作。
3.1.6 自适应哈希索引 AHI
哈希是一种非常快的查询方式，时间复杂度是O(1),一般只需要一次查到，B+树的查找次数取决于树的高度，一般需要三到四次查找才可以定位到数据。
innoDB存储引擎支持使用AHI自动对热点数据建立哈希索引，当然有几个条件：
1.只支持等值查询，不支持范围查询等；
2.对这个页的访问方法必须是连续一样的，也就是查询条件必须是完全一样的，且访问次数超过100次或者超过页中数据量的1/16次；
使用哈希索引后，查询和写入效率可以提升2倍，辅助索引的链接性能可以提高5倍。
3.1.7 重做日志缓冲
InnoDB存储引擎首先会将重做日志写入重做日志缓冲，然后再以一定的频率写入重做日志文件，以提高性能。在三种情况下会触发写入重做日志文件：
1.Master Thread每一秒将重做日志缓冲写入重做日志文件；
2.每个事物提交时触发；
3.重做日志缓冲池的剩余空间小于1/2时触发。
3.2 后台线程
3.2.1 Master Thread
InnoDB的主要工作都是在Matser Thread完成的，包括合并插入缓存等。
3.2.2 IO Thread
InnoDB 存储引擎使用了大量的AIO(Async IO)处理请求以提高数据库的性能，IO Thread主要负责这些IO请求的回调。分为read thread和write thread,数量默认都是4个。
3.2.3 Pruge Thread
innoDB存储引擎使用undo log在数据修改时存储数据修改前的数据，既可以用来在事务回滚时rollback,也可以在其他事务查询时返回修改前的数据。当事务提交后，undolog就不再需要，这时通过Purge Thread来回收undo log
3.2.4 Page Cleaner Thread
Page Cleaner Thread是一个异步线程，被设计来将脏页刷新到磁盘中，减轻主线程的压力和用户线程的阻塞。
2926
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
