【mysql】innodb学习笔记

mysql

mysql 的组成

1. 连接池组件

2. 管理服务和工具组件

3. sql接口组件

4. 优化器

5. 解析分析器组件

6. 缓存

7. 插件式存储引擎 （基于表而非数据库）

8. 物理文件

   mysql 是开源的，可根据mysql预定义的存储引擎接口实现个性化的需求

   mysql 的存储引擎介绍

   1. InnoDB
      1. 支持事务
      2. 面向在线事务处理系统（OLTP：Online transaction processing）
      3. 非锁定读：读取操作不会产生锁
   2. MyISAM
      1. 不支持事务，锁
      2. 支持全文索引
      3. 缓存只缓存索引，不缓存文件
      4. 面向在线分析系统（OLAP ：Online Analytical Processing 数据仓库类型的应用，侧重数据的分析查询，决策）
      5. 由MYD （存放数据）和MYI（存放索引）组成
   3. NDB
      1. 集群存储引擎，数据全部存放在内存，1.5版本后，可将非索引数据存放在磁盘上
   4. memory
      1. 数据存放于内存，重启或者崩溃口数据消失，适合做临时表，存放实时数据
      2. 不支持text/blog 如果有则被转换成myisam存储引擎，并放于磁盘
      3. 使用hash索引而不使用b+树索引
   5. archive
      1. 只支持insert，select操作
      2. 使用zib算法将数据行压缩后存储，试用于归档数据（日志）

innodb

1. innodb是多线程引擎 后台现场介绍

1. master thread ： 负责将缓冲区中的脏数据（相对于磁盘中的发生了改变了的内存中的数据）刷新到磁盘，保证内存与磁盘数据的一致性
2. io thread ： 采用异步io ，且多个io thread （write，read，insert，log）
3. purge thread ： 负责undolog 的 回收，从master thread 中分离出来的

2. innodb 内存

1. mysql基于磁盘存储，以页为基本管理单位，使用缓冲池技术提高性能
2. 页数据从缓冲池刷新到磁盘并不是页发生变更后马上触发刷新操作，而是采用 checkpoint 机制
3. 相关配置参数
   1. 缓冲池大小配置参数：innodb_buffer_pool_size
   2. 缓冲池个数配置参数：innodb_buffer_pool_instances
4. 内存的管理
   1. LRU列表： 最新最近使用的热点数据列表
   2. 重做日志（redoLog）：在更新数据操作前，先记录数据操作的日志
   3. 脏页（frush页）：缓冲池中需要更新到磁盘的数据
   4. 热点数据页在 lru列表的表头，非热点数据页在列表尾部，每次新增列表数据页时，并不会把页直接添加到表头，而是放在midPoint位置
   5. 数据流转顺序：freeList ——》 LRUList ——》frush page—— 磁盘
5. 缓冲中重做日志内容刷新到磁盘的触发点
   1. master thread 定时工作
   2. 事务提交
   3. 重做日志缓冲池中的空间小于 1/2
6. checkpoit
   1. 缓冲池数据刷新到内存的机制，方便故障发生时快速恢复数据（checkpoint 点后最近的一个undoLog）
7. undolog , redolog
   1. 日志先于数据持久化
   2. redolog ：实现持久化， 记录每个页的物理操作情况，记录innodb 本身的事务日志，是一种操作日志（当要修改数据库内容时，会先查询磁盘数据到内存，修改内存中的拷贝数据并记录到 redolog ，先buffer 再 批量写入）
   3. undolog ： 实现事务的原子性，是数据的备份（在操作任何数据前，将数据备份到undolog，如果事务处理失败或回滚，则用undolog 恢复到操作前的状态），事务成功提交后，undolog 放入删除列表（没有真正删除）

master thread

1. 由多个循环组成（主循环，后台循环）
2. 主循环：分为每秒的操作和每10秒的操作
   1. 每秒的操作
      1. 缓冲日志刷新到磁盘（总是）
      2. 当io压力小时，合并插入（可能）
      3. 最多刷新100个脏页到磁盘（可能）
      4. 如果没有获得用户切换到后台循环background loop （可能）
   2. 每10秒的操作
      1. 刷新100个脏页到磁盘 （可能）
      2. 合并插入 （总是）
      3. 缓冲日志刷新到磁盘（总是）
      4. 删除无用undolog （总是）
3. 后台循环（background loop）
   1. 删除无用undo页
   2. 合并20个插入缓冲
   3. 调回主循环

innodb 新特性

1. 插入缓存（insert buffer）： 当插入不是顺序的时候，插入之前需要离散的寻找插入的位置，而这个过程是比较耗时的，所以提供一个缓冲区，记录要插入的数据，而后以一定的频率进行合并插入操作

2. 两次写（double write）：在写入某个页的数据到表中的时候发生宕机，导致部分数据写入失败，为了保持数据的一致性，防止部分写失败带来的数据丢失，采用两次写机制

   1. 脏页数据先写入 double writer buffer ， double writer buffer 顺序的写入磁盘共享表空间，再离散的写入数据文件，如果部分写失败，可从共享表空间中恢复数据到数据文件

   

3. 自适应哈希索引（数据库自动优化）

   1. 要求对页的查询模式一致（查询条件），并以该模式访问了100次

4. 异步io

5. 刷新邻接页：刷新一个脏页时，检测该页所在区的所有页，如果有脏页，则一起刷新，且连续的页，可将多个io合并操作，提供io使用率

索引组织表

1. 概述: 根据主键顺序组织存放的表，如没有指定主键，按如下方式创建主键
   1. 表中第一个定义的非空的唯一索引
   2. 如没有非空唯一索引，自动创建一个6字节大小的指针

逻辑存储结构

)

1. innerDB 默认一个共享表空间（存储undolog，插入缓冲索引页，系统事务信息，二次写缓冲），通过启用 innodb_file_per_table ，每表数据单独存放在一个表空间（数据页，索引页，插入缓冲索引页）

页

1. 页是innobd 磁盘管理的最小单元
2. 默认16kb,可通innodb_page_size 参数调整
3. 常见类型
   1. 数据页（B-tree node）
   2. undo 页 （undo log page）
   3. 系统页 （system page）
   4. 事务数据页（transaction system page）
   5. 插入缓冲位图页（insert buffer bitmap）
   6. 插入缓冲空闲列表页（insert buffer free list）
   7. 未压缩的二进制大对象页（UNcompress blob page）
   8. 压缩的二进制大对象页

行

1. 格式：compact，redundant
2. 行溢出数据：存放在数据页之外（uncompress blob page）的数据(一般是大对象的数据，text/blob)

约束 （保证数据的完整性）

1. innodb 提供的约束
   1. primary key
   2. unique key
   3. foreign key (参照完整性)
   4. default
   5. not null
2. mysql 在某种模式下约束不会对数据进行正确性校验，如非空字段插入空，任然可以成功插入，只是会有告警信息，但不会有错误提示，可通过修改sql-model 参数修改约束，对数据正确性进行校验

触发器

在执行insert ,update , delete 等更新操作前后自动调用sql命令或者存储过程

视图

view ,虚表，没有实际的物理存储，非持久

物化视图（oracle）/索引视图（sql server） 根据基表，真实存在的表，在表发生变化时，不同机制下的物化事务也会发生变化，mysql 不支持物化视图