MySQL（二） |
您所在的位置：网站首页 › mysqlbinlog查看事务执行时间 › MySQL（二） |
|
一、InnoDB与MyISAM对比
事务和外键
InnoDB支持事务和外键，具有安全性和完整性，适合大量insert和update操作。
MyISAM不支持事务和外键，提供高速的存储和检索，适合大量的select操作。
锁机制
InnoDB支持行级锁，锁定指定记录，基于索引来加锁实现。
MyISAM支持表级锁，锁定整张表。
索引结构
InnoDB使用聚簇索引，索引和记录在一起存储，既存储索引，也缓存记录。
MyISAM使用的非聚簇索引，索引和记录分开。
并发处理能力
MyISAM使用表锁，会导致操作并发率低，读之间不阻塞，但读写阻塞。
InnoDB的读写阻塞可以与隔离级别有关，可以采用MVCC来支持高并发。
存储文件
InnoDB表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。InnoDB表最大支持64TB。
MyISAM表对应三个文件，一个.frm表结构文件，一个.MYD表数据文件，一个.MYI索引文件。从5.0开始默认限制256TB。
适用场景
MyISAM
●不需要事务支持
●并发相对较低
●数据修改相对较少，以读为主
●数据一致性要求不高
InnoDB
●需要事务支持
●行级锁对高并发有很好的适应能力
●数据更新较为频繁的场景
●数据一致性要求高
●缓存能力好，能减少磁盘IO
二、InnoDB存储结构
InnoDB引擎架构图.png
InnoDB内存架构
内存结构主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer四部分。
Buffer Pool：缓冲池，简称BP，BP是以Page页为单位，默认大小16k，BP底层采用链表来管理。InnoDB在访问表记录和索引时，会在Page页中进行缓存，以减少磁盘IO，提升效率。
Change Buffer：写缓冲区，简称CB。在进行DML操作时，如果BP中没有相应的Page数据。并不会立即将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取时，再将数据合并到BP中。
Adaptive Hash Index：自适应哈希索引，用于优化对BP数据的查询。InnoDB会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。
Log Buffer：日志缓冲区，用来保存要写入磁盘的日志文件（Undo/Redo），日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到BLOB或者多行更新的大事务操作时，增加日志缓冲区可以节省磁盘I/O。
InnoDB磁盘架构
InnoDB磁盘主要包含TableSpaces、InnoDB Data Dictionary、Doublewrite Buffer、Redo Log和Undo Logs。
TableSpaces：表空间，用于存储表结构和数据。表空间包括系统表空间、独立表空间、通用表空间、临时表空间和Undo表空间等多种类型。
●系统表空间，包含InnoDB数据字典、Doublewrite Buffer、Change Buffer、Undo Logs的存储区域。系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。
●独立表空间，默认开启，独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。每个表空间文件由一个.ibd数据文件代表，该文件默认创建于数据库目录中。
●通用表空间，通过Create tablespace语法创建的共享表空间，通用表空间可以创建于mysql数据目录外的其他表空间，可以容纳多张表，且支持其所有行格式。
●撤销表空间，由一个或多个包含Undo日志文件组成。
●临时表空间，分为session temporary tablespaces和global temporary tablespace两种。session temporary tablespaces存储的是用户创建的临时表和磁盘内部的临时表。global temporary tablespace存储的是用户临时表的回滚段（rollback segments）。mysql服务器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。
InnoDB Data Dictionary：InnoDB数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据。
Doublewrite Buffer：双写缓冲区，位于系统表空间，是一个存储区域。在BufferPage的page页刷新到磁盘真正的位置前，会先将数据存在Doublewrite缓冲区。如果在page页写入过程中出现操作系统、存储子系统或者mysqld进程崩溃，InnoDB可以在崩溃恢复期间从Doublewrite缓冲区找到页面的一个好备份。
Redo Log：重做日志，重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据。mysql以循环方式写入重做日志文件，记录InnoDB中所有对Buffer Pool修改的日志。当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时需redo，重新把数据更新到数据文件。读写事务在执行的过程中，都会不断的产生redo log。
Undo Logs：撤销日志，是在事务开始之前保存的被修改数据的备份，用于例外情况时回滚事务。撤销日志属于逻辑日志，根据每行记录进行记录。撤销日志存在于系统表空间、撤销表空间和临时表空间中。
三、InnoDB线程模型
InnoDB线程模型图.jpg
●IO Thread：在InnoDB中使用了大量的AIO（Async IO）来做读写处理，极大的提高了数据库性能。IO Thread一共有write、read、insert buffer和log thread四种（10个）。
read thread：负责读取操作，将数据从磁盘加载到缓存page页。4个
write thread：负责写操作，将缓存脏页刷新到磁盘。4个
log thread：负责将日志缓冲区内容刷新到磁盘。1个
insert buffer thread：负责将写缓冲内容刷新到磁盘。1个
●Purge Thread：事务提交之后，其使用的undo日志不再需要，因此需要Purge Thread回收已经分配的undo页。
●Page Cleaner Thread：将脏数据刷新到磁盘，脏数据刷新后的redo log也可以覆盖，既可以同步数据，又能达到redo log循环使用的目的。会调用write thread线程处理。
●Master Thread：是InnoDB的主线程，负责调度其他各线程，优先级最高。作用是将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。包含：脏页的刷新（Page Cleaner Thread）、undo页回收（Purge Thread）、redo日志刷新（Log Thread）、合并写缓冲等。内部有两个主处理，分别是每隔1秒和10秒处理。
每1秒的操作：
刷新日志缓冲区，刷到磁盘；
合并写缓冲数据，根据IO读写压力来决定是否操作；
刷新脏页数据到磁盘，根据脏页比例达到75%才操作。
每10秒的操作：
刷新脏页数据到磁盘；
合并写缓冲区数据；
刷新日志缓冲区；
删除无用的undo页。
四、InnoDB文件存储结构
InnoDB文件存储结构.jpg
一个ibd数据文件——>Segment（段）——>Extent（区）——>Page（页）——>Row（行）
●Table Space：表空间，用于存储多个ibd数据文件，存储表的记录和索引。一个文件包含多个段。
●Segment：段，用于管理多个Extent，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment）。一个表至少会有两个segment，一个管理数据，一个管理索引。每多创建一个索引，会多两个segment。
●Extent：区，一个区包含64个连续的页，大小为1M。当表空间不足时，需要分配新的页资源，不会一页一页的分，直接分配一个区。
●Page：页，用于存储多个Row行记录，大小为16K。包含很多种页类型，比如数据页、undo页、系统页、事务数据页、大的BLOB对象页。
●Row：行，包含了记录的字段值、事务ID（Trx id）、滚动指针（Roll pointer）、字段指针（Field pointer）等信息。
五、Undo log
简述
数据库事务开始之前，会将要修改的记录存放到Undo日志中，当事务回滚或者数据库崩溃时，可以利用Undo日志，撤销未提交事务对数据库产生的影响。
产生和销毁：Undo log在事务开始前产生；事务提交时，并不会立即删除Undo log，InnoDB会将事务对应的Undo log放到删除列表中，后面通过Purge thread进行回收处理。Undo log属于逻辑日志，记录一个变化过程。比如执行一个delete，Undo log会记录一个insert；执行一个update，会记录一个相反的update。
作用
●实现事务的原子性
事务处理过程中，如果出现了错误或者用户执行了Rollback语句，MySQL可以利用Undo log中的备份数据恢复到事务开始之前的状态。
●实现多版本并发控制（MVCC）
事务未提交之前，Undo log保存了未提交之前的版本数据，可作为数据旧版本快照供其他并发事务进行快照读。
六、Redo log和Binlog
Redo log简述
事务中修改的任何数据，将最新的数据备份的位置（Redo log），称为重做日志。
生成和释放：随着事务操作的执行，就会生成Redo log，在事务提交时会将产生的Redo log写入Log buffer，并不是随着事务的提交就立即写入磁盘。等事务操作的脏页写入到磁盘后，Redo log的使命也就完成了，Redo log占用的空间就可以重用。
Redo log工作原理
Redo log是为了实现事务的持久性。防止在事务发生故障的时间点，还有脏页未写入表的ibd文件中，在重启MySQL服务的时候，根据Redo log进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。
Binlog日志
●Binlog记录模式
Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作。Binlog是以事件形式记录，还包含语句所执行的消耗时间。Binlog主要应用场景有：
主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复，达到主从数据一致性。
数据恢复：通过mysqlbinlog工具来恢复数据。
●Binlog文件结构
Binlog中Log event结构.png
●Binlog文件操作
Binlog状态查看：
show variables like "log_bin";
开启Binlog功能： set global log_bin=mysqllogbin;或者修改my.cnf或my.ini文件，在[mysqld]下面增加log_bin=mysql_log_bin，重启mysql服务。 #log-bin=ON #log-bin-basename=mysqlbinlog binlog-format=ROW #(ROW、STATMENT、MIXED，三种模式) log-bin=mysqlbinlog使用Binlog恢复数据： //按指定时间恢复 mysqlbinlog --start-datetime="开始日期" --stop-datetime="结束日期" Binlog文件名 | mysql -uroot -p密码 //按事件位置号恢复 mysqlbinlog --start-position=起始位置号 --stop-position=终止位置号 | mysql -uroot -p密码删除Binlog文件： purge binary logs to 'mysqlbinlog.000001'; //删除指定文件 purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件 reset master; //清除所有文件●Redo log和Binlog区别 Redo log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录； Redo log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程； Redo log是循环写，日志空间大小是固定的，Binlog是追加写，写完一个写下一个，不会覆盖； Redo log作为服务器异常宕机事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |