MySQL进阶篇——InnoDB存储引擎和管理

InnoDB存储引擎

逻辑存储结构

表空间(.ibd文件)-段-区-页-行

一个mysql实例对应多个表空间，用于存储记录，索引等数据；

段：分为数据段（B+树叶子节点）、索引段（B+树非叶子节点）、回滚段

区：表空间单元结构，每个区大小1M，页大小16K，一个区有64个连续的页

页：磁盘管理的最小单元，为保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区

内存结构和磁盘结构

1、内存结构

（1）buffer pool缓冲池：从磁盘加载数据，增删改查先操作缓冲池的数据，再刷新到磁盘，减少磁盘IO加快速度。缓冲池以页为单位，链表数据结构管理页，三类页free page空闲未使用/clean page使用但数据未修改过/dirty page数据被修改过，数据与磁盘不一致；

（2）change buffer更改缓冲区：针对非唯一的二级索引，缓冲池未加载的数据，用来存修改动作；

（3）自适应hash索引：优化缓冲池查询速度，自动建立；

（4）log buffer日志缓冲区：存日志，定期刷入磁盘；

2、磁盘结构

（1）System Tablespace系统表空间：存储change buffer，ibdata1文件；

（2）File-Per-Table Tablespaces独立表空间：.ibd每张表的独立文件；

（3）通用表空间 (General Tablespaces)：通过create tablespace创建，可以让多张表共用一个外部定义的表空间文件；

（4）撤销表空间Undo Tablespaces：存储undo log日志undo001,undo002；

（5）临时表空间 (Temporary Tablespaces)：临时表；

（6）双写缓冲区 (Doublewrite Buffer Files)：缓冲池数据刷新到磁盘前，先写入双写缓冲区，便于系统异常时恢复数据.dblwr；

（7）重做日志 (Redo Log)：实现事务的持久性，循环写入；

后台线程

InnoDB存储引擎缓冲池的数据合适时间刷新到磁盘文件

1、master thread：核心后台线程，负责调度其他线程，把缓冲池的数据刷新到磁盘，脏页的刷新，合并插入缓存，Undo Log 的回收；

2、IO Thread：负责处理异步 I/O 请求的回调，AIO处理IO请求；

show engine innodb status; —展示InnoDB存储引擎状态信息，包括IO信息

3、Purge Thread：回收事务提交后的Undo Log；

4、Page Cleaner Thread：协助master thread刷新脏页到磁盘；

事务原理

事务四大特性

原子性A：事务是不可分割的最小操作单元，要么全部成功，要么全部失败；

一致性C：事务完成时，必须使所有数据都保持一致状态；

隔离性I：数据库系统提供的隔离机制，保证不同事务不受外部并发操作影响的独立环境下运行；

持久性D：事务一旦提交，它对数据库中数据的改变是永久的，哪怕数据库发生故障；

如何实现四大特性？

redo log（实现持久性）

重做日志，事务提交时数据页的物理修改，包括内存中的redo log buffer和磁盘中的redo log file，刷新脏页发生错误时用于数据恢复；机制WAL先写日志；ib_logfile0/1循环写；

undo log（实现原子性）

回滚日志，记录数据被修改前的信息，作用包括提供回滚和MVCC

redo log是物理日志，而undo log是逻辑日志能读取内容进行回滚，存放在回滚段；

锁和MVCC（实现隔离性）

一致性：redo log 和undo log共同实现

MVCC(Multi-Version Concurrency Control)多版本并发控制

当前读：读取数据的最新版本，读取时加锁保证其他并发事务不能修改当前记录，select lock in share mode(共享锁), select for update, update, insert, delete(排他锁)都是当前读；

快照读：普通select 不加锁，读取的是记录数据的可见版本，非阻塞读；

read committed：每次select都生成一个快照读；

repeatable read：开启事务后第一个select才是快照读的地方；

serializable：快照读会退化为当前读，每次读都加锁；

MVCC：维护一个数据的多个版本，使得读写操作没有冲突（解决普通select）

快照读为MySQL实现MVCC提供非阻塞读功能，具体实现原理：隐式字段、undo log日志、readview。

（1）隐式字段：除了建表定义的字段外，会额外增加DB_TRX_ID（最近插入/修改这条记录的事务ID）、DB_ROLL_PTR（回滚指针，指向记录的上一个版本）、DB_ROLL_ID（隐藏主键，无主键时自动生成）三个字段；

（2）undo log日志：insert的时候，产生的undo log日志只在回滚时需要，事务提交后可被立即删除（因为没有这条记录的历史版本），而update和delete产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会被立即删除；

（3）undo log版本链：不同事务/相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链；

（4）readview：读视图，是快照读执行时MVCC提取数据的依据，记录并维护（快照）系统当前未提交（活跃）的事务id，决定读取undo log中的哪个版本；readview是执行快照读（普通select）时创建的；

我是creator_trx_id，是readview创建者的事务ID，通过这四个字段和访问规则，判断我能看到哪个版本的记录（根据trx_id带入访问规则从新→旧依次判断），最新版本在记录里，其他版本在undo log里（如上图）

下图要修改一点：m_ids是不包括creator的其他当前活跃的事务ID集合；

访问规则（从上到下）

（1）若trx_id=creator_trx_id，说明是本人修改的版本，因此这条trx_id可以看；

（2）若trx_id < min_trx_id（自己也是活跃事务，但不用保存在m_ids中，因此总存在min_trx_id ≤ creator_trx_id）说明自己创建时，trx_id早就提交了，因此这条trx_id可以看；

（3）若trx_id >= max_trx_id（max_trx_id是下一条事务的ID）说明是ReadView 之后才开始的事务，属于未来，因此这条trx_id不可以看；

（4）若min_trx_id <= trx_id < max_trx_id在这个范围内要进行二次判断，若 trx_id在m_ids里面，拍快照时未提交，因此这条trx_id不可以看；若trx_id 不在 m_ids 里面，说明拍快照之前已经提交，因此这条trx_id可以看；

注意！事务 ID（trx_id）是在事务开始时分配的，不是修改数据时才分配的

不同隔离级别生成readview的时机不同：

read committed：事务中每次select都执行快照读，每次执行快照读时生成readview；

repeatable read：仅在事务中第一个select执行快照读，第一次执行快照读时生成readview，后续复用该readview；

RC例子（修改图中：两个m_ids不包括5）

第一个readview读到的是trx_id=2的版本的数据；

第二个readview读到的是trx_id=3的版本的数据；

RR例子

只生成一个readview

MySQL管理

MySQL自带四个数据库

mysql：表，mysql服务器正常运行信息，用户权限等；

information_schema：表和视图，InnoDB引擎等元数据信息，数据库，表，字段类型等；

performance_schema：运行状态底层监控，收集性能参数；

sys：方便开发性能调优和诊断的视图；

#以下均在命令行shell执行，而不是MySQL交互式环境 mysql [options] [database] mysql -h192.168.139.128 -P3306 -uroot -p0119 itcast -e"select * from stu" #其中-u指定用户名，-p密码，-h服务器IP，-P连接端口，-e执行sql语句并退出（脚本） #未登陆的查询 mysqladmin #管理MySQL服务器 mysqladmin -uroot -p0119 variables #查看系统变量 mysqladmin -uroot -p0119 create db02 #创建数据库 mysqlbinlog [options] log-files1 log-files2 #二进制日志查看工具 mysqlbinlog -s binlog.001 #查看binlog：-s简单信息，-d指定数据库 mysqlshow #客户端对象查看工具 mysqlshow -uroot -p1234 db01 --count #查看指定数据库统计信息，有哪些表，字段数，记录数 mysqlshow -uroot -p1234 db01 course -i #db01数据库course表的状态信息：存储引擎，创建时间等 #重要（备份/迁移）备份的是纯sql语句 mysqldump [options] db_name [tables] > file.sql #备份数据库/不同数据库之间数据迁移 #-t只导出数据不导出建表语句；-d只导出结构不导数据；-T导出两个文件（.sql+.txt） mysqldump -uroot -p0119 -t db01 > /backup/db01.sql #备份db01数据库 mysqlimport -uroot -p0119 db01 /var/lib/mysql-files/score.txt #备份的txt数据导入 # 连接mysql后执行 source /backup/db01.sql #备份的sql全部执行-恢复数据库