mysql mysql和innodb原理与机制

mysql自带的数据库

• information_schema：元数据：数据库，表定义，行数，表大小，数据库权限等
• performance_schema：性能，进程的信息：锁，等
• mysql：mysql的信息：权限，参数，状态，主从等信息
• test

mysql

日志

• mysql server层的日志
  • binlog：更新操作，用于同步
  • 通用查询日志：记录一般查询，一般不开启
  • 慢查询：通常我们开启
  • 错误日志：默认开启，连接错误啥的

innodb结构

内存结构

• Buffer Pool：缓冲池
  • 在InnoDB访问表记录和索引时会在Page页中缓存，以后使用可以减少磁盘IO操作，提升效率
  • 以Page页为单位，Page页默认大小16K，链表结构
  • page页的管理：
    • 每当有新的page数据读取到buffer pool时，InnoDb引擎会判断是否有空闲页，是否足够，
      • 如果有就将free page从free list列表删除，放入到LRU列表中。
      • 没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页
• Change Buffer：写缓冲区
  • 当更新一条记录时
    • 该记录在BufferPool存在，直接在BufferPool修改，一次内存操作
    • 如果该记录在BufferPool不存在（没有命中）
      • 会直接在ChangeBuffer进行一次内存操作，不用再去磁盘查询数据，避免一次磁盘IO。
      • 当下次查询记录时，会先从磁盘读取， 然后从ChangeBuffer中读取信息合并，最终放到BufferPool中
  • 写缓冲区，仅适用于非唯一普通索引页
    • 如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。
    • 会直接将记录插入到BufferPool中，然后在缓冲池修改，不会在ChangeBuffer操作
  • ChangeBuffer占用BufferPool空间，默认占25%，最大允许占50%
    • 可以根据读写业务量来进行调整。参数innodb_change_buffer_max_size
• Adaptive Hash Index：自适应哈希索引
  • 用于优化对BP数据的查询
  • InnoDB存储引擎会监控对表索引的查找，
  • 如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。
  • InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引
• Log Buffer：日志缓冲区
  • 在DML操作时会产生Redo日志，被缓冲在日志缓冲区。
  • 日志的刷新：innodb_flush_log_at_trx_commit参数控制日志刷新行为，默认为1
    • 0：每隔1秒写日志文件和刷盘操作（写日志文件LogBuffer–>OS cache，刷盘OScache–>磁盘文件），最多丢失1秒数据
    • 1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁IO操作
    • 2：事务提交，立刻写日志文件，每隔1秒钟进行刷盘操作
  • 日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O。

文件

• 系统表空间（The System Tablespace）
  • 数据字典（InnoDB Data Dictionary）
    • InnoDB数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据。
    • 元数据物理上位于InnoDB系统表空间中。
    • 由于历史原因，数据字典元数据在一定程度上与InnoDB表元数据文件（.frm文件）中存储的信息重叠
  • 双写缓冲区（Doublewrite Buffer）
    • 作用：防止将脏页刷新到磁盘中，出现部分写的问题。
    • innodb的页大小默认是16K，Linux的block size是4K，
    • 刷新过程中os crash或者停电，会导致部分数据写入到磁盘中，导致数据不一致。
    • innodb现将buffer pool中的数据写入到doublewrite buffer中，再刷新到磁盘中。
    • 如果是刷盘过程中出现问题。innodb将doublewrite 中的数据刷新到数据库即可，
    • 若写到doublewrite出错，则用原始数据和redo日志恢复
    • 参考：https://www.cnblogs.com/geaozhang/p/7241744.html
  • 写缓冲区 change buffer
  • 撤销日志（Undo Logs）
  • 用户在系统表空间创建的表数据和索引数据
    • 系统表空间是一个共享的表空间因为它是被多个表共享的。
    • 该空间的数据文件通过参数innodb_data_file_path控制，默认值是ibdata1:12M:autoextend(文件名为ibdata1、12MB、自动扩展)
• 单表表空间（File-Per-Table Tablespaces）
  • 默认开启
  • 被.ibd文件代表
• 共享表空间（General Tablespaces）
  • CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB; //创建表空间ts1
  • CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; //将表添加到ts1表空间
• 临时表空间（Temporary Tablespaces）
  • session temporary tablespaces 存储的是用户创建的临时表和磁盘内部的临时表。
  • global temporary tablespace储存用户临时表的回滚段（rollback segments ）。
  • mysql服务器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。
• 撤销表空间（Undo Tablespaces）
  • 撤销表空间由一个或多个包含Undo日志文件组成
  • 撤消日志是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。
  • 撤消日志属于逻辑日志，根据每行记录进行记录。
  • 撤消日志存在于系统表空间、撤消表空间和临时表空间中。
    

innodb机制总结

• 内存与数据文件
  • 聚簇索引+二级索引，数据文件是通过page管理，page16K
  • 内存中有page的缓存-Buffer Pool，用于缓存查询的索引和数据
  • 对数据的修改，走change buffer，是buffer pool的一部分
    • 唯一索引不走，因为需要校验唯一性，必须要读磁盘
    • buffer pool中有也不走，直接改buffer pool
    • 将修改的数据放到change buffer中
      • 下一次查询时，先查磁盘，再合并change buffer，放到buffer pool中
  • double write
    • https://www.cnblogs.com/geaozhang/p/7241744.html
    • 原因：内存page（脏页）直接刷新磁盘，会有部分写的问题
      • page是16K，操作系统4K，若写入一半时宕机，则有问题
      • redo日志中记录的是整个page的变更，对page的出现一半写入是不能恢复的
      • 脑洞：可以利用page页的校验和，校验page页的完整性？？？
    • 过程
      • 先将页内存copy到双写缓冲内存中2M
      • 分两次顺序写入双写缓冲文件中，每次1M。调用fsync同步磁盘
      • 在将页中的变更随机写入到数据库中
        
• redo
  • redo中记录page的物理变更
  • 用于持久化-崩溃恢复
  • 操作内存，记录redo，后台将脏页刷新到文件
  • redo将对page的随机写转化为顺序写
    • 开脑洞的话，可以直接写数据文件，回滚时，通过binlog和undo log回滚即可

提升效率

• B+树聚簇索引，提升了读取效率
  • 利用硬盘连续读取快而随机读取慢的特性，使用的更加矮胖B+数，数据存放的叶子节点
• 使用内存缓存，提升了读取效率
  • 需要保证内存和硬盘的数据一致性，缓存命中率
  • 缓冲池，缓存热点的索引和记录
    • 自适应hash索引：对有必要的缓冲池的数据自动建立hash索引
  • 日志缓冲
• 使用redo日志，将随机写入转为顺序写入，提升写入效率
• 使用变更缓存，提升了写入效率，不需要提前的读取
  • 适用于 非唯一 索引 页
  • Change buffer的主要目的是
    • 将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果。
    • 对表执行 INSERT，UPDATE和 DELETE操作时， 索引列的值（尤其是secondary keys的值）
    • 通常按未排序顺序排列，需要大量I / O才能使二级索引更新。
    • Change Buffer会缓存这个更新当相关页面不在Buffer Pool中，
    • 从而磁盘上的相关页面不会立即被读避免了昂贵的I / O操作。
  • 流程：
    • 来了一个关于二级索引页面的DML操作，并且这个页面没有在Buffer Pool内，
    • 那么把这个操作存入Change Buffer
    • 下一次需要加载这个页面的时候，将Change Buffer内的更改合并到Buffer Pool，
    • 随后当服务器在空闲的时候，这个更改会刷到disk（磁盘）上。