MySQL-InnoDB初步了解（体系结构与存储引擎）

数据库和实例（database/instance）

1. 数据库：物理操作系统文件或其他形式文件类型的集合。在MySQL数据库中，数据库文件可以是frm、MYD、MYI、ibd结尾的文件。
2. 实例：MySQL数据库由后台线程以及一个共享内存区组成。共享内存可以被运行的后台线程所共享。真正用于操作数据库文件的是数据库实例
3. 实例与数据库通常一一对应，即一个数据库对应一个实例，一个实例对应一个数据库。集群时，可能存在一个数据库被多个数据实例使用的情况

 

MySQL体系结构

1. 连接池组件；管理服务和工具软件；SQL接口组件；查询分析器组件；优化器组件；缓冲（cache）组件；插件式存储引擎；物理文件
2. MySQL区别于其他DB最重要的一点就是插件式的表存储引擎；存储引擎是底层物理结构的实现，可以自定义
3. 存储引擎是基于表的，而不是DB

 

MySQL存储引擎

1. 存储引擎的好处：每个存储引擎都各有特点，能够根据具体的应用建立不同存储引擎表。
2. 粗分：MySQL官方存储引擎和第三方存储引擎

 

InnoDB存储引擎

1. InnoDB存储引擎支持事务。主要面向OLTP应用。名词解释：OLTP <联机事务处理>
2. 每张表数据的存储都按主键顺序存放，如果未设定主键则为没有主键的表默认生成6字节的ROWID作为主键。
3. InnoDB的特点：行锁的设计，支持外键，并且支持非阻塞读。
4. InnoDB通过MVCC来获得高并发性，并且实现了四种隔离级别。默认RR
5. InnoDB使用next-key locking策略来避免幻读
6. InnoDB提供插入缓冲，二次写，自适应哈希索引，预读等高性能，高可用功能。

 

MyISAM存储引擎

1. 不支持事务、表锁设计，支持全文索引，面向OLAP应用；OLAP：联机分析处理
2. MySQL5.5.8版本之前的默认存储引擎；
3. 缓冲池只缓存索引文件，而不是缓冲数据文件；
4. 由MYD和MYI组成；MYD存数据文件，MYI存索引文件

 

Memory存储引擎

1. 表中的数据存储在内存中，数据库重启发生崩溃，数据即丢失。
2. 默认使用Hash索引，而非B+索引
3. 表锁设计，并且不支持text及blob字段。varchar字段设计为定长字段，所以浪费内存空间。
4. Mysql数据库会使用Memory存储引擎作为临时表来存放查询中间的结果集。如果结果集有text或者BlOB字段，或者超过Memory存储引擎表的容量，则会使用MyISAM作为中间表而放到磁盘，MyISAM不会缓存数据文件，因此这时产生临时表的性能对于查询会有所损耗。
5. 延伸：工作开发中，设计表应时应避免使用text字段及blob字段及写出临时表过大的SQL。

 

Maria存储引擎

1. 希望取代MyISAM，重新成为默认存储引擎
2. 支持缓存数据和索引文件，行锁设计，MVCC，支持事务和非事务安全的选项，以及更好的BLOB字符类型

 

说说MySQL几种存储引擎应用场景？

Memory存储引擎，速度很快，在内存中，但是很鸡肋。在Redis没出现的时候可以用用，适合存储不太重要的东西。

MyISAM存储引擎，硬件资源比较差可以用，占用资源相对少，适合读操作，不适合频繁更新，不支持事务。

InnoDB支持事务，适合经常更新删除操作的业务场景，支持外键约束。

 

InnoDB存储引擎简述章

InnoDB体系架构简介

1. InnoDB含有多个内存块，构成了一个大的内存池；
2. 负责如下工作：
   • 维护所有进程/线程需要访问的多个内部数据结构
   • 缓存磁盘上的数据，方便快速地读取；同时修改磁盘数据之前现在这里缓存
   • 重做日志（redo log）缓冲

 

后台线程

1. Master Thread（主线程）
   • 负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性
   • 包括脏页的刷新，合并插入缓冲，UNDO页的回收等
2. IO Thread（IO线程）
   • 负责将IO请求的回调处理（call back）
   • 默认读写线程为4
3. Purge Thread(清理线程)
   • 曾经属于主线程的一部分，InnoDB1.1之后独立工作
   • 回收已经使用并分配的undo页
4. Page Cleaner Thread（清理页线程）
   • 曾经属于主线程的一部分，InnoDB1.2之后独立工作
   • 负责刷新脏页（缓冲池中的页与磁盘上的页的数据产生了不一致）

 

缓冲池 (buffer pool) 2.5G

1. 概述：最大的内存部分，用来存放各种缓冲，InnoDB存储引擎工作模式将数据库文件以页(16K)的形式读取到内存中。然后以LRU的形式维持缓冲池。如果数据库发生修改，总是先修改缓存池中的页，此时的页被称为脏页，然后再按一定频率将数据刷新到本地磁盘中。
2. 缓冲池中缓存的数据页类型：索引页;数据页;undo页插入缓冲;自适应哈希索引;InnoDB存储的锁信息;数据字典信息
3. 缓冲池的管理方式
   • LRU List（最近最少使用算法）
     1. 最频繁使用的页在LRU列表的前端，最少使用的页在LRU列表的尾端。当缓冲池满时，将最先释放LRU列表尾端的页
     2. Innodb_old_blocks_pct:把LRU列表分为new/old列表，新读取的页放在中点位置
     3. Innodb_old_blocks_time：超过时间还没有刷出列表，那么就可以列为new列表，主要针对列表没满的情况
   • Free List：启动时，缓存池为空，所有可用空间都在free列表中
   • Flush List：页被修改的同时，也会加入到刷新列表中，与LRU列表互不影响
4. InnoDB是持久化在磁盘的，为了弥补磁盘跟CPU的鸿沟，在内存中创建一片缓冲池，用来缓存“页”。

 

重做日志缓冲(redo log buffer) 8M

1. Innodb_log_buffer_size:设置重做日志缓冲池的大小，一般8MB足矣
2. 原因：以下三种情况会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中
   • 主线程每秒将重做日志缓冲刷新到重做日志文件
   • 每个事物提交时会将重做日志缓冲刷新到重做日志文件
   • 当重做日志缓冲池剩余空间小于一半时，重做日志缓冲刷新到重做日志文件

 

额外的内存池(additional memory pool) 8M

1. 对一些数据结构本身分配内存时，需要从额外内存中获取，当该区域内存不够用时会去缓冲池中申请。

 

Master Thread工作方式

1. 由多个循环（loop）组成，主线程会根据数据运行状态在多个循环中切换：插入缓存：新增操作，即要产生个新页，但是页能装很多东西，所以要合并一下。
   • 主循环（loop）：
     1. 每秒一次的操作：
        1. 总是将日志缓存刷新到磁盘，即使这个事务还没有提交（所以再大的事务提交（commit）的时间也很短）
        2. 有可能合并插入缓冲（innodb判断当前一秒内发生的I/O次数是否小于5次，如果是，会认为当前的IO压力很小，可以执行合并插入缓冲）
        3. 有可能（最多）刷新100个innoDB缓冲池中的脏页到磁盘（当前缓冲池中脏页的比例超过innodb_max_dirty_pages_pct参数值时）
        4. 如果当前没有用户活动，则有可能切换到后台循环
     2. 每十秒一次的操作
        1. 可能刷新100个脏页到磁盘（10秒之内磁盘的IO操作小于200次时可以执行）
        2. 总是合并最多5个插入缓冲
        3. 总是将日志缓冲刷新到磁盘
        4. 总是删除无用的Undo页
        5. 总是100个或者10个脏页到磁盘
           1. 如果缓冲池中的脏页比例大于75%，则同步100个到磁盘
           2. 如果小于75%则同步10个到磁盘。
   • 后台循环（backgroup loop）；当前没有用户活动/数据库空闲时或者数据库关闭（shutdown），就会切换到这个循环；后天循环结束后会切换到暂停循环：
     1. 删除无用的Undo页（总是）
     2. 合并20个插入缓冲（总是）
     3. 跳回到主循环（总是）
     4. 不断刷新100个页直到符合条件（可能，有可能跳转到flush loop中完成）
   • 刷新循环（flush loop）：不断刷新100个脏页，直到脏页比例没有超过阈值
   • 暂停循环（suspend loop）：将master Thread挂起，等待事件的发生

 

InnoDB关键特性

1. 插入缓冲（insert Buffer）
   • 原理：判断插入的非聚焦索引页是否在缓冲池中，如果在就直接插入；不在的话，先放在Insert Buffer对象中。然后以一定的频率进行insert buffer和辅助索引页子节点的合并操作
   • 条件：非唯一的辅助索引
   • 名词解释：聚焦索引（primary key）
   • 带给InnoDB性能上的提升
2. 二次写（double write）
   • 带给InnoDB数据页的可靠性
   • 发生情景：数据库宕机时，可以InnoDB存储引擎正在写入某个页到表中，而这个页只写了一部分，发生了部分写失效
   • 解决：在应用重做日志之前，用户需要一个副本页，当写入失效时，先通过副本页来还原该页，在进行重做
3. 自适应哈希索引（adaptive hash index/AHI）
   • 哈希：Hash是一种非常快的查找方法，通常查找的时间复杂度为O(1)，一次查找便可以定位数据
   • 建立：InnoDB会监控对表上各索引页的查询。如果观察到建立哈希索引就可以带来速度提升，则建立自适应哈希索引（AHI）；通过缓冲池的B+树页构造，因此建立的速度很快，不需要对整表构建哈希索引。（会对热点页建立）
   • 平常咱们写SQL时，涉及到传当前日期等频繁调用的SQL，咱们最好将值传递下来，这样便可能用上自适应哈希索引。

 

CheckPoint

1. 解决问题：
   • 缩短数据库的恢复时间
   • 缓冲池不够用时，将脏页刷新到磁盘
   • 重做日志不可用时，刷新脏页
2. 分类
   • Sharp checkpoint：数据库关闭时将所有的缓冲数据刷新到磁盘，innodb_fast_shutdown=1
   • Fuzzy checkpoint：只刷新部分脏页
3. 出现Checkpoint的情况：
   • 主线程每秒或每十秒都会进行checkpoint
   • Lru列表需要保证有足够的空闲页
     1. MySQL5.6开始通过innodb_lru_sacn_depth来控制空闲页数量
     2. 单独放在page_cleaner线程中进行
   • 重做日志不可用的时候出现同步/异步checkpoint
     1. MySQL5.6单独放在page cleaner Thread进行
   • 脏页太多；innodb_max_dirty_pages_pct(脏页栈缓冲池的百分比；Innodb1.0之前，该参数默认值为90，之后为75)