InnoDB和MyISAM的技术资料
2010-08-11 21:51:54 
阿炯
InnoDB和MyISAM是在使用MySQL最常用的两个表类型，各有优缺点，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快， 但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。
innodb和myisam的主要区别
innodb
1. 支持事务
2.锁粒度为行级别
3.支持mvcc多版本并发控制
4.不支持地理空间
5.最大支持64TB存储
6.支持哈希索引
7.不支持全文索引(新的5.6以上支持)
8.支持聚集索引
9.半支持压缩数据
10.支持外键
11.支持缓冲数据
myisam
1.不支持事务
2.锁粒度为表级别
3.不支持mvcc多版本并发控制
4.支持地理空间
5.没有存储限制
6.不支持哈希索引
7.支持全文索引
8.不支持聚集索引
9.支持压缩数据
10.不支持外键
11.不支持缓冲数据
InnoDB
InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现，在InnoDB表中不需要扩大锁定(lock escalation)，因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。
InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与其它的不一样，举例来说，在 MyISAM 中，表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小，一般为 2 GB。
InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。
InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在myisam表中每个表被存在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
MyISAM
MyISAM 是MySQL缺省存贮引擎。每张MyISAM 桌被存放在盘在三个文件：frm 文件存放表格定义， 数据文件是MYD (MYData) ，索引文件是MYI (MYIndex) 引伸。ISAM是 Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。与其他存储引擎比较，MyISAM具有检查和修复表格的大多数工具。MyISAM表格可以被压缩而且它们支持全文搜索，它们不是事务安全的且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量的SELECT，MyISAM是更好的选择。
为了提升性能，我们把日志文件和数据文件放在不同的磁盘，另外还可以采用裸的磁盘分区来存储数据以提升性能。需要提醒的是，在 Linux x86 上不要把内存设置太高，glibc 会把进程堆增长到线程堆栈之上，能把服务器给搞垮，下面的值接近于 2GB 时就很危险：
innodb_buffer_pool_size + key_buffer + max_connections * (sort_buffer + record_buffer) + max_connections * 2 MB
每个线程将使用 2MB(二进制版本为 256 KB) 的堆栈，在最坏的情况下，还会使用 sort_buffer + record_buffer 的附加内存。
MySQL InnoDB的性能问题讨论
MySQL最为人垢病的缺点就是缺乏事务的支持，MyISAM 性能虽然出众，不是没有代价的，InnoDB 又如何呢？InnoDB 的磁盘性能很令人担心，MySQL 缺乏良好的 tablespace 真是天大的缺陷！
InnoDB的表空间分成三种，一种是裸设备，一种是若干个 ibdata 文件(缺省方式)，再一种是 Per-Table 文件，第一种用得少，第二种显然比第三种效率更差，本文的讨论基于 Per-Table，也即innodb_file_per_table 配置参数。
现象重现：导出一个几百万行数据、带若干索引、有过频繁更新的表出来再导入，如果能以真实环境下的表来做测试就更理想，到 data 目录下观察对应的数据文件的 size 增长情况，会发现前 1G 速度相当令人满意，可是越往后效率越低，到后面基本就是蜗牛般的速度了。
InnoDB 跟磁盘相关的文件存储，可以分成两个部分，一个是日志文件，另一个是数据文件。当有频繁的 INSERT/UPDATE 操作的时候，InnoDB 需要分别写入这两个文件，日志文件是顺序操作，数据文件包括了表数据和索引数据两个部分(和 MyISAM 直接拆开成表文件和索引文件不同，InnoDB 的表和索引是在同一个文件当中的)。
InnoDB 的索引用的是 BTREE 格式，如果当前更新的记录影响到索引的变化，逻辑上就存在三个操作，从原来的 BTREE 找到并摘除原来这行的记录并做调整、插入行数据、根据新数据查找 BTREE 相应的位置并重新插入新索引信息，假设索引数为 N，相应的逻辑操作数就为 1 + 2*N，显然这些信息不能保证在同一个磁盘连续空间上，因此需要 1 + 2*N 次的磁头移动，行数越大、文件尺寸越大，磁头的移动幅度也就可能越大，带来的后果显然是极差的磁盘 IO 效率。
MyISAM:这个是默认类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量 的SELECT，MyISAM是更好的选择。
InnoDB：这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性,因此如果需 要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑，应该使用InnoDB表,
对于支持事物的InnoDB类型的标，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物(即使autocommit打 开也可以)，将大大提高性能。
MyIASM是IASM表的新版本，有如下扩展：
1、二进制层次的可移植性。
2、NULL列索引。
3、对变长行比ISAM表有更少的碎片。
4、支持大文件。
5、更好的索引压缩。
6、更好的键码统计分布。
7、更好和更快的auto_increment处理。
InnoDB 是 MySQL 上第一个提供外键约束的引擎，除了提供事务处理外，InnoDB 还支持行锁，提供和 Oracle 一样的一致性的不加锁读取，能增加并发读的用户数量并提高性能，不会增加锁的数量。
InnoDB 的设计目标是处理大容量数据时最大化性能，它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 有它自己的缓冲池，能缓冲数据和索引，InnoDB 还把数据和索引存放在表空间里面，可能包含好几个文件，这和 MyISAM 表完全不同，在 MyISAM 中，表被存放在单独的文件中，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。
InnoDB所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件，或者是独立的表空间文件),相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。
MyISAM 是MySQL缺省存储引擎(5.5以前，5.5后innodb为默认存储引擎)。
每张MyISAM 表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex) 引伸。
因为MyISAM相对简单所以在效率上要优于InnoDB..小型应用使用MyISAM是不错的选择。MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
以下是一些细节和具体实现的差别：
1、InnoDB不支持FULLTEXT类型的索引。
2、InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
3、对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
4、DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
5、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
6、InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”
综上所述，任何一种表都不是万能的，只有恰当的针对业务类型来疡合适的表类型，才能最大的发挥MySQL的性能优势。两种类型最主要的差别就是 InnoDB 支持事务处理与外键和行级锁.而MyISAM不支持.所以Myisam往往就容易被人认为只适合在小项目中使用。
MyISAM和InnoDB优化
key_buffer_size – 这对MyISAM表来说非常重要。如果只是使用MyISAM表，可以把它设置为可用内存的 30-40%。合理的值取决于索引大小、数据量以及负载 -- 记住，MyISAM表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。尽管如此，需要总是检查是否所有的 key_buffer 都被利用了 -- .MYI 文件只有 1GB，而 key_buffer 却设置为 4GB 的情况是非常少的。这么做太浪费了。如果你很少使用MyISAM表，那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。
innodb_buffer_pool_size – 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以，然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 -- 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了。
innodb_additional_pool_size – 这个选项对性能影响并不太多，至少在有差不多足够内存可分配的操作系统上是这样。不过如果你仍然想设置为 20MB(或者更大)，因此就需要看一下Innodb其他需要分配的内存有多少。
innodb_log_file_size 在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。我经常设置为 64-512MB，跟据服务器大小而异。
innodb_log_buffer_size 默 认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可 以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存 -- 它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。
innodb_flush_logs_at_trx_commit 是否为Innodb比MyISAM慢1000倍而头大？看来也许你忘了修改这个参数了。默认值是 1，这意味着每次提交的更新事务(或者每个事务之外的语句)都会刷新到磁盘中，而这相当耗费资源，尤其是没有电池备用缓存时。很多应用程序，尤其是从 MyISAM转变过来的那些，把它的值设置为 2 就可以了，也就是不把日志刷新到磁盘上，而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去，因此通常不会丢失每秒1-2次更新的消耗。如果设置 为 0 就快很多了，不过也相对不安全了 -- MySQL服务器崩溃时就会丢失一些事务。设置为 2 指挥丢失刷新到操作系统缓存的那部分事务。
table_cache -- 打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁，所以通常要加大缓存数量，使得足以最大限度 地缓存打开的表。它需要用到操作系统的资源以及内存，对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话，那么设置为 1024 也许比较合适(每个线程都需要打开表)，如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。
thread_cache -- 线程的创建和销毁的开销可能很大，因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created 的值也比较大，那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。
query_cache -- 如果你的应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用。不要把它设置太大了，因为想要维护它也需要不少开销，这会导致MySQL变慢。通 常设置为 32-512Mb。设置完之后最好是跟踪一段时间，查看是否运行良好。在一定的负载压力下，如果缓存命中率太低了，就启用它。
sort_buffer_size –如果你只有一些简单的查询，那么就无需增加它的值了，尽管你有 64GB 的内存。搞不好也许会降低性能。
事务相关的技术术语
Checkpoint技术
为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。当由于宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务ACID中Durability(持久性)的要求。
Checkpoint技术是用来解决以下几个问题：
缩短数据库的恢复时间；
缓冲池不够用时，将脏页刷新到磁盘；
重做日志不可用时，刷新脏页。
当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。故数据库只需对Checkpoint后的重做日志进行恢复。这样就大大缩短了恢复的时间。此外，当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷新回磁盘。
重做日志出现不可用的情况是因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的。重做日志可以被重用的部分是指这部分重做日志已经不再需要，即当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。若此时这部分重做日志还需要使用，那么必须强制产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。
对于InnoDB存储引擎而言，其是通过LSNLog Sequence Number来标记版本的。而LSN是8字节的数字，其单位是字节。每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。
重做日志
在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。这两个文件就是重做日志文件，或者事务日志。重做日志的目的：万一实例或者介质失败，重做日志文件就能派上用场。例如，数据库由于所在主机掉电导致实例失败，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。
每个InnoDB存储引擎至少有一个重做日志文件组，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0、ib_logfile1。InnoDB存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，当重做日志文件2也被写满时，会再被切换到重做日志文件1中。
重做日志缓冲
重做日志缓冲一般不需要设置的很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。默认为8MB。系统在以下三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中：
Master Thread每一秒将重做日志缓冲刷新到重做日志文件；
每个事务提交时会将重做日志缓冲刷新到重做日志文件；
当重做日志缓冲池剩余空间小与1/2时，重做日志缓冲刷新到重做日志文件。
事务的实现
事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性。redo和undo都可以视为一种恢复操作，redo恢复提交事务修改的页操作，而undo回滚行记录到某个特定版本。因此两者记录的内容不同，redo通常是物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行记录。
redo
重做日志用来实现事务的持久性所以关于上面原子性的实现，有待商榷。其由两部分组成：一是内存中的重做日志缓冲，其是易失的；二是重做日志文件，其是持久的。
InnoDB是事务的存储引擎，其通过Force Log at Commit机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化。
为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，为了确保日志写入磁盘因为这里有一个文件系统缓存，必须进行一次fsync操作。由于fsync的效率取决于磁盘的性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。
InnoDB存储引擎允许用户手工设置非持久性的情况发生，以此提高数据库的性能。即当事务提交时，日志不写入重做日志文件，而是等待一个时间周期后再执行fsync操作。这可以显著提高数据库的性能，但是当数据库发生宕机时，由于部分日志文件未写入磁盘，因此会丢失最后一段时间的事务。
LSN
LSN是Log Sequence Number的缩写，其代表的是日志序列号。在InnoDB存储引擎中，LSN占用8字节，并且单调递增。LSN代表的含义有：
重做日志写入的总量
checkpoint的位置
页的版本
LSN表示事务写入重做日志的字节总量。例如，当前重做日志的LSN是1000，事务T1写入了100字节的重做日志，LSN就变成1100，又有事务T2写入200字节的重做日志，那么LSN变成：1300。可见LSN记录的是重做日志的总量，其单位是字节。
每个页的头部也有一个LSN，记录的是该页最后刷新时LSN的大小。重做日志记录的是每个页的物理更改日志，因此页中的LSN用来判断是否需要进行恢复操作。例如：页的LSN为10000，数据库启动时，写入重做日志的LSN为13000，表明该事务已经提交，数据库需要恢复；重做日志中的LSN小于页中的LSN，不需要进行重做，因为页中的LSN表示已经刷新到该位置。
恢复
InnoDB存储引擎在启动时不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作。
undo
重做日志记录了事务的行为，可以很好的通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要undo。如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。
除了回滚操作，undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过读取之前的行版本信息，以此实现非锁定读。
最后也是最为重要的一点是，undo log也会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。
purge
purge用于最终完成delete和update操作。这样设计是因为InnoDB存储引擎支持MVCC，所以记录不能在事务提交时立即进行处理。这时其他事务可能正在引用该行，故InnoDB存储引擎需要保存记录之前的版本。而是否可以删除该条记录通过purge来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真正的delete操作。
group commit
若事务为非只读事务，则每次事务提交时需要进行一次fsync操作，以此保证重做日志都已经写入了磁盘。然而磁盘的fsync性能是有限的，为了提高效率，当前数据库都提供了group commit功能，即一次fsync可以刷新确保多个事务日志被写入文件实现用了队列和流水线。
MySQL预写日志机制一览
在 MySQL 中，InnoDB 存储引擎实现了 WAL 机制。包含 Redo log buffer、Redo log、Undo Log 等，来记录事务已提交但未写入数据文件的数据变更以及事务回滚后的数据还原。
关于预写日志机制
一般情况下，大部分数据库都是将表和索引存储在磁盘文件中。当新增数据时，数据库系统会先写入内存，然后将其写入磁盘上的数据文件。
那为什么不直接写入磁盘？主要是每次新增都直接写入磁盘性能很低，放在内存中，可以批量写入磁盘以提升性能。但有一个问题，如果数据在写入磁盘文件中途断电怎么办？当来电恢复后并重启数据库发现数据不一致，又该如何处理。所以需要一些其他机制来避免断电引发的数据不一致，其实 MySQL 已经考虑到了这一点，内部已经实现一套 WAL（预写日志）机制来避免这一点。
MySQL 设计有健壮的恢复机制，特别是使用 InnoDB 存储引擎的情况下，它能够在断电后重启而不会崩溃。InnoDB 存储引擎使用预写日志（WAL）机制来确保数据的一致性和原子性。预写日志机制是一种数据库事务日志技术，它要求在任何数据库修改被写入到永久存储（也就是磁盘）之前，先将这些修改记录到日志中。
这样当 MySQL 遇到意外的断电情况时，它会在重启后利用 Redo log 来恢复已提交但未写入数据文件的事务继续写入数据文件，从而保证一致性，再利用 undo log 来撤销未提交事务的需改，从而保证原子性。
MySQL 的预写日志机制会涉及到其架构中的以下内容：
Buffer Pool（缓冲池）
Buffer Pool （缓冲池）是 InnoDB 存储引擎中非常重要的内存结构，顾名思义，缓冲池就是起到一个缓存的作用，因为我们都知道 MySQL 的数据最终是存储在磁盘中的，如果没有这个 Buffer Pool 那么我们每次的数据库请求都会磁盘中查找，这样必然会存在 IO 操作，这肯定是无法接受的。
但是有了 Buffer Pool 就是我们第一次在查询的时候会将查询的结果存到 Buffer Pool 中，这样后面再有请求的时候就会先从缓冲池中去查询，如果没有再去磁盘中查找，然后在放到 Buffer Pool 中。
Redo log buffer（日志缓冲区）
Redo log buffer 是用作数据变更记录写入 Redo log 文件前的一块内存区域。日志缓冲区大小由 innodb_log_buffer_size 变量定义，默认大小为 16MB。
日志缓冲区的内容会定期刷新到 Redo log 文件中，大型日志缓冲区允许大型事务运行，而无需在事务提交之前将 Redo log 数据写入磁盘。因此如果事务涉及的更新、插入或删除操作数据量较大时，可以增加日志缓冲区的大小可以节省磁盘 I/O。
MySQL 提交事务的时候，会将 Redo log buffer 中的数据写入到 Redo log 文件中，刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置：
1.值为 0 表示不刷入磁盘
2.值为 1 表示立即刷入磁盘
3.值为 2 表示先刷到 os cache
为了提高性能，MySQL 首先将修改操作写入到日志缓冲区，之后以 innodb_flush_log_at_trx_commit 参数设置落盘时机，将日志缓冲区刷入到磁盘的 Redo log 文件中去。
Redo Log
Redo Log 是 InnoDB 存储引擎中的一个重要组件，它是一种磁盘基础的数据结构，用于在崩溃重启期间修复由已提交事务但未写入数据文件的数据。
在正常操作中，Redo log 记录了由 SQL 语句执行导致的表数据变更记录。将 Redo log buffer 中的数据持久化到磁盘中，就是将 Redo log buffer 中的数据写入到 Redo log 磁盘文件中。
数据在由 Redo log buffer 写入 Redo log 时的触发时机如下：
1.MySQL 正常关闭时触发
2.当 Redo log buffer 中记录的写入量大于 Redo log buffer 内存空间的一半时，会触发落盘
3.InnoDB 的后台线程每隔 1 秒，将 Redo log buffer 持久化到磁盘
4.每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略就是由上文提高 innodb_flush_log_at_trx_commit 参数控制）
Redo log 是 WAL 机制的核心，它记录了事务所做的所有修改。如果数据库发生故障，可以使用 Redo 日志来重做事务，从而确保数据的一致性。
Undo Log
Undo Log 记录了如何撤销一个事务的修改。如果需要回滚事务或在执行事务时还未提交，数据库就发生了崩溃，这时我们就需要将未提交事务前的数据回滚回去，难道这个操作有我们自己来做吗？显然 MySQL 也考虑到了这一点。
MySQL 会使用 Undo log 来撤销未提交的修改。在操作数据前，MySQL 首先将数据备份到 Undo log，然后进行数据修改。如果出现错误或者用户执行了 Rollback 语句，系统可以利用 Undo log 中的备份将数据恢复到事务操作前的状态。
通过 Undo log 撤销修改，从而确保数据的原子性。
结合 Buffer Pool、Redo log buffer、Redo log、Undo log 后，我们在MySQL 中更新一条数据的流程如下，
图片来源于此处，相关步骤如下：
1.准备更新一条 SQL 语句
2.Innodb会先去缓冲池（Buffer Pool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（Buffer Pool）中
3.在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
4.innodb 会在 Buffer Pool 中执行更新操作
5.更新后的数据会记录在 Redo log buffer 中
6.MySQL 提交事务的时候，会将 Redo log buffer 中的数据写入到 Redo log 文件中，刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置
7.MySQL 重启的时候会将 Redo log 恢复到缓冲池中。
额外知识：检查点（Checkpoint）
检查点是什么？为什么有了 Redo log、Undo log 还要引入检查点。明明借助 Redo log、Undo log 就可以实现 MySQL 的故障恢复了。
虽然数据在写入 Redo log 文件后，就代表数据变更已经生效了，但是还未写入到数据文件，也就是还没有完成事务的持久性。
检查点就是帮助 MySQL 实现事务的持久性。
如果说 Redo log 可以无限地增大，能够保存所有数据库变更的数据，那么在发生宕机时完全可以通过 Redo log 来恢复数据库系统的数据到宕机发生前的情况。
然而现实是物理磁盘文件大小是有效的。即使达成无限了，如果数据库运行了很久后发生宕机，那么使用 Redo log 进行恢复的时间也会非常的久。所以在 Redo log 文件容量是有限的情况下，还需要定期将 Redo log 写入数据文件完成数据的持久化，在这样的情况下，就引入了 Checkpoint（检查点）技术。
Checkpoint 技术不仅仅是会同步 Redo log 写入数据文件，也会同步脏页数据写入数据文件。检查点的触发时机有两种如下：
1.Sharp Checkpoint(完全检查点)
将内存中所有脏页全部写到磁盘就是完全检查点，比如数据库实例关闭时。
2.Fuzzy Checkpoint(模糊检查点)
将部分脏页刷新到磁盘，就是模糊检查点，一般就是脏页达到一定数量时触发。数据库实例运行过程产生的检查基本上就是这种类型的检查点。
因此其实 Checkpoint 就是指一个触发点（时间点），当发生 Checkpoint 时，会将脏页写回磁盘，以确保数据的持久性和一致性。并且 Redo log、Undo log 文件也可以重新覆写，这样可以保证重启时不会因为 Redo log、Undo log 文件太大而导致重启时间过长。
断电故障恢复案例
假如正在使用 MySQL 添加数据。在提交事务的过程中，突然发生了断电，那么这个数据会丢吗？结合上文MySQL中更新一条数据的流程，来分析下具体场景：
1.数据在写入 Buffer Pool、Redo log buffer 中时，发生断电
先说结论，会丢。因为数据没有写入 Redo log 前，MySQL 是没办法保证数据一致性的。但是这没关系的，因为 MySQL 会认为本次事务是失败的，在重启后可以根据 Undo log 文件将数据恢复到更新前的样子，并不会有任何的影响。
2.数据在写入 Redo log 文件后，发生断电
先说结论，不会丢。因为 Redo log buffer 中的数据已经被写入到 Redo log 了，就算数据库宕机了，在下次重启的时候 MySQL 也会将 Redo log 文件内容恢复到 Buffer Pool 中进行重放。
详解undo log与redo log
数据库通常借助日志来实现事务，常见的有undo log、redo log，undo/redo log都能保证事务特性，这里主要是原子性和持久性，即事务相关的操作，要么全做，要么不做，并且修改的数据能得到持久化。
假设数据库在操作时，按如下约定记录日志：
1.事务开始时，记录START T
2.事务修改时，记录T，x，v，说明事务T操作对象x，x的值为v
3.事务结束时，记录COMMIT T
undo log原理
undo log是把所有没有COMMIT的事务回滚到事务开始前的状态，系统崩溃时，可能有些事务还没有COMMIT，在系统恢复时，这些没有COMMIT的事务就需要借助undo log来进行回滚。
使用undo log时，要求：
1.记录修改日志时redo log，(T，x，v中v为x修改前的值，这样才能借助这条日志来回滚；
2.事务提交后，必须在事务的所有修改包括记录的修改日志都持久化后才能写COMMIT日志；这样才能保证，宕机恢复时，已经COMMIT的事务的所有修改都已经持久化，不需要回滚。
使用undo log时事务执行顺序
1.记录START T
2.记录需要修改的记录的旧值要求持久化
3.根据事务的需要更新数据库要求持久化
4.记录COMMIT T
使用undo log进行宕机回滚
1.扫描日志，找出所有已经START,还没有COMMIT的事务。
2.针对所有未COMMIT的日志，根据redo log来进行回滚。
如果数据库访问很多，日志量也会很大，宕机恢复时，回滚的工作量也就很大，为了加快回滚，可以通过checkpoint机制来加速回滚:
在日志中记录checkpoint_start (T1,T2…Tn) (Tx代表做checkpoint时，正在进行还未COMMIT的事务
等待所有正在进行的事务T1~TnCOMMIT
在日志中记录checkpoint_end
借助checkpoint来进行回滚
从后往前，扫描undo log
1.如果先遇到checkpoint_start, 则将checkpoint_start之后的所有未提交的事务进行回滚；
2.如果先遇到checkpoint_end，则将前一个checkpoint_start之后所有未提交的事务进行回滚；在checkpoint的过程中，可能有很多新的事务START或者COMMIT)。
使用undo log，在写COMMIT日志时，要求redo log以及事务的所有修改都必须已经持久化，这种做法通常很影响性能。
redo log原理
redo log是指在回放日志的时候把已经COMMIT的事务重做一遍，对于没有commit的事务按照abort处理，不进行任何操作。
使用redo log时，要求：
1.记录redo log时，(T,x，v中的v必须是x修改后的值，否则不能通过redo log来恢复已经COMMIT的事务。
2.写COMMIT日志之前，事务的修改不能进行持久化，否则恢复时，对于未COMMIT的操作，可能有数据已经修改，但重放redo log不会对该事务做任何处理，从而不能保证事务的原子性。
使用redo log时事务执行顺序
1.记录START T
2.记录事务需要修改记录的新值要求持久化
3.记录COMMIT T要求持久化
4.将事务相关的修改写入数据库
使用redo log重做事务
1.扫描日志，找到所有已经COMMIT的事务；
2.对于已经COMMIT的事务，根据redo log重做事务；
在日志中使用checkpoint
1.在日志中记录checkpoint_start (T1,T2...Tn) (Tx代表做checkpoint时，正在进行还未COMMIT的日志
2.将所有已提交的事务的更改进行持久化；
3.在日志中记录checkpoint_end
根据checkpoint来加速恢复
从后往前，扫描redo log
1.如果先遇到checkpoint_start, 则把T1~Tn以及checkpoint_start之后的所有已经COMMIT的事务进行重做；
2.如果先遇到checkpoint_end, 则T1~Tn以及前一个checkpoint_start之后所有已经COMMIT的事务进行重做；
与undo log类似，在使用时对持久化以及事务操作顺序的要求都比较高，可以将两者结合起来使用，在恢复时，对于已经COMMIT的事务使用redo log进行重做，对于没有COMMIT的事务，使用undo log进行回滚。redo/undo log结合起来使用时，要求同时记录操作修改前和修改后的值，如T，x，v，w，v为x修改前的值，w为x修改后的值，具体操作顺序为：
1.记录START T
2.记录修改日志T，x，v，w要求持久化，其中v用于undo，w用于redo
3.更新数据库
4.记录 COMMIT T
4和3的操作顺序没有严格要求，并且都不要求持久化；因为如果宕机时4已经持久化，则恢复时可通过redo log来重做；如果宕机时4未持久化，则恢复时可通过undo log来回滚；在处理checkpoint时，可采用与redo log相同的处理方式。
innodb和myisam的主要区别
innodb
1. 支持事务
2.锁粒度为行级别
3.支持mvcc多版本并发控制
4.不支持地理空间
5.最大支持64TB存储
6.支持哈希索引
7.不支持全文索引(新的5.6以上支持)
8.支持聚集索引
9.半支持压缩数据
10.支持外键
11.支持缓冲数据
myisam
1.不支持事务
2.锁粒度为表级别
3.不支持mvcc多版本并发控制
4.支持地理空间
5.没有存储限制
6.不支持哈希索引
7.支持全文索引
8.不支持聚集索引
9.支持压缩数据
10.不支持外键
11.不支持缓冲数据
InnoDB
InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现，在InnoDB表中不需要扩大锁定(lock escalation)，因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。
InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与其它的不一样，举例来说，在 MyISAM 中，表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小，一般为 2 GB。
InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。
InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在myisam表中每个表被存在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。
MyISAM
MyISAM 是MySQL缺省存贮引擎。每张MyISAM 桌被存放在盘在三个文件：frm 文件存放表格定义， 数据文件是MYD (MYData) ，索引文件是MYI (MYIndex) 引伸。ISAM是 Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。与其他存储引擎比较，MyISAM具有检查和修复表格的大多数工具。MyISAM表格可以被压缩而且它们支持全文搜索，它们不是事务安全的且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量的SELECT，MyISAM是更好的选择。
为了提升性能，我们把日志文件和数据文件放在不同的磁盘，另外还可以采用裸的磁盘分区来存储数据以提升性能。需要提醒的是，在 Linux x86 上不要把内存设置太高，glibc 会把进程堆增长到线程堆栈之上，能把服务器给搞垮，下面的值接近于 2GB 时就很危险：
innodb_buffer_pool_size + key_buffer + max_connections * (sort_buffer + record_buffer) + max_connections * 2 MB
每个线程将使用 2MB(二进制版本为 256 KB) 的堆栈，在最坏的情况下，还会使用 sort_buffer + record_buffer 的附加内存。
MySQL InnoDB的性能问题讨论
MySQL最为人垢病的缺点就是缺乏事务的支持，MyISAM 性能虽然出众，不是没有代价的，InnoDB 又如何呢？InnoDB 的磁盘性能很令人担心，MySQL 缺乏良好的 tablespace 真是天大的缺陷！
InnoDB的表空间分成三种，一种是裸设备，一种是若干个 ibdata 文件(缺省方式)，再一种是 Per-Table 文件，第一种用得少，第二种显然比第三种效率更差，本文的讨论基于 Per-Table，也即innodb_file_per_table 配置参数。
现象重现：导出一个几百万行数据、带若干索引、有过频繁更新的表出来再导入，如果能以真实环境下的表来做测试就更理想，到 data 目录下观察对应的数据文件的 size 增长情况，会发现前 1G 速度相当令人满意，可是越往后效率越低，到后面基本就是蜗牛般的速度了。
InnoDB 跟磁盘相关的文件存储，可以分成两个部分，一个是日志文件，另一个是数据文件。当有频繁的 INSERT/UPDATE 操作的时候，InnoDB 需要分别写入这两个文件，日志文件是顺序操作，数据文件包括了表数据和索引数据两个部分(和 MyISAM 直接拆开成表文件和索引文件不同，InnoDB 的表和索引是在同一个文件当中的)。
InnoDB 的索引用的是 BTREE 格式，如果当前更新的记录影响到索引的变化，逻辑上就存在三个操作，从原来的 BTREE 找到并摘除原来这行的记录并做调整、插入行数据、根据新数据查找 BTREE 相应的位置并重新插入新索引信息，假设索引数为 N，相应的逻辑操作数就为 1 + 2*N，显然这些信息不能保证在同一个磁盘连续空间上，因此需要 1 + 2*N 次的磁头移动，行数越大、文件尺寸越大，磁头的移动幅度也就可能越大，带来的后果显然是极差的磁盘 IO 效率。
MyISAM:这个是默认类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量 的SELECT，MyISAM是更好的选择。
InnoDB：这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性,因此如果需 要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑，应该使用InnoDB表,
对于支持事物的InnoDB类型的标，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物(即使autocommit打 开也可以)，将大大提高性能。
MyIASM是IASM表的新版本，有如下扩展：
1、二进制层次的可移植性。
2、NULL列索引。
3、对变长行比ISAM表有更少的碎片。
4、支持大文件。
5、更好的索引压缩。
6、更好的键码统计分布。
7、更好和更快的auto_increment处理。
InnoDB 是 MySQL 上第一个提供外键约束的引擎，除了提供事务处理外，InnoDB 还支持行锁，提供和 Oracle 一样的一致性的不加锁读取，能增加并发读的用户数量并提高性能，不会增加锁的数量。
InnoDB 的设计目标是处理大容量数据时最大化性能，它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 有它自己的缓冲池，能缓冲数据和索引，InnoDB 还把数据和索引存放在表空间里面，可能包含好几个文件，这和 MyISAM 表完全不同，在 MyISAM 中，表被存放在单独的文件中，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB。
InnoDB所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件，或者是独立的表空间文件),相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。
MyISAM 是MySQL缺省存储引擎(5.5以前，5.5后innodb为默认存储引擎)。
每张MyISAM 表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex) 引伸。
因为MyISAM相对简单所以在效率上要优于InnoDB..小型应用使用MyISAM是不错的选择。MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
以下是一些细节和具体实现的差别：
1、InnoDB不支持FULLTEXT类型的索引。
2、InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
3、对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
4、DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
5、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
6、InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”
综上所述，任何一种表都不是万能的，只有恰当的针对业务类型来疡合适的表类型，才能最大的发挥MySQL的性能优势。两种类型最主要的差别就是 InnoDB 支持事务处理与外键和行级锁.而MyISAM不支持.所以Myisam往往就容易被人认为只适合在小项目中使用。
作为使用mysql的用户角度出发，innodb和myisam都是比较喜欢的，但是从我目前运维的数据库平台要达到需求：99.9%的稳定性，方便的扩展性和高可用性来说的话，myisam绝对是首选。
myisam只有索引缓存而innodb不论是索引还是数据文件都会加载到innodb buffer。myisam只能管理索引，在索引数据大于分配的资源时，会由操作系统来cache，数据文件依赖于操作系统的cache。innodb不管是索引还是数据，都是自己来管理。
索引差异
1)、关于自动增长
myisam引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
innodb引擎的自动增长咧必须是索引，如果是组合索引也必须是组合索引的第一列。
2)、关于主键
myisam允许没有任何索引和主键的表存在。
myisam的索引都是保存行的地址。
innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)。
innodb的数据是主索引的一部分，附加索引保存的是主索引的值。
3)、关于count()函数
myisam保存有表的总行数，如果select count(*) from table;会直接取出出该值。
innodb没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
4)、全文索引
myisam支持 FULLTEXT类型的全文索引。
innodb不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好(sphinx 是一个开源软件，提供多种语言的API接口，可以优化mysql的各种查询)。
5)、delete from table
使用这条命令时，innodb不会从新建立表，而是一条一条的删除数据，在innodb上如果要清空保存有大量数据的表，最 好不要使用这个命令。(推荐使用truncate table，不过需要用户有drop此表的权限)
6)、索引保存位置
myisam的索引以表名+.MYI文件分别保存。
innodb的索引和数据一起保存在表空间里。
InnoDB与Myisam的六大区别
构成上的区别：
每个MyISAM在磁盘上存储成三个文件。
文件的名字以表的名字开始，扩展名指出文件类型。
表的结构文件扩展名为.frm(Frame)
数据文件的扩展名为.MYD (MYData)
索引文件的扩展名是.MYI (MYIndex)
<------
基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB
InnoDB不支持FULLTEXT类型的索引(5.6后支持)
事务处理上方面:
MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持
<------
InnoDB提供事务支持事务，外部键等高级数据库功能
SELECT UPDATE,INSERT，Delete操作
如果执行大量的SELECT，MyISAM是更好的选择
<------
1.如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表
2.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除。
3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用
对AUTO_INCREMENT的操作
每表一个AUTO_INCREMEN列的内部处理。
MyISAM为INSERT和UPDATE操 作自动更新这一列。这使得AUTO_INCREMENT列更快(至少10%)。在序列顶的值被删除之后就不 能再利用(当AUTO_INCREMENT列被定义为多列索引的最后一列， 可以出现重使用从序列顶部删除的值的情况)。
AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置
对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但 是在MyISAM表中，可以和其他字段一起建立联合索引更好和更快的auto_increment处理
<------
如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数 器的计数器，它被用在为该列赋新值。
自动增长计数 器仅被存储在主内存中，而不是存在磁盘上
表的具体行数
select count(*) from table,MyISAM只要简单的读出保存好的行数，注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的
<------
InnoDB 中不 保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行
锁
表锁
<------
提供行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执 行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”
MyISAM和InnoDB优化
key_buffer_size – 这对MyISAM表来说非常重要。如果只是使用MyISAM表，可以把它设置为可用内存的 30-40%。合理的值取决于索引大小、数据量以及负载 -- 记住，MyISAM表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。尽管如此，需要总是检查是否所有的 key_buffer 都被利用了 -- .MYI 文件只有 1GB，而 key_buffer 却设置为 4GB 的情况是非常少的。这么做太浪费了。如果你很少使用MyISAM表，那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。
innodb_buffer_pool_size – 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以，然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 -- 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了。
innodb_additional_pool_size – 这个选项对性能影响并不太多，至少在有差不多足够内存可分配的操作系统上是这样。不过如果你仍然想设置为 20MB(或者更大)，因此就需要看一下Innodb其他需要分配的内存有多少。
innodb_log_file_size 在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。我经常设置为 64-512MB，跟据服务器大小而异。
innodb_log_buffer_size 默 认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可 以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存 -- 它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。
innodb_flush_logs_at_trx_commit 是否为Innodb比MyISAM慢1000倍而头大？看来也许你忘了修改这个参数了。默认值是 1，这意味着每次提交的更新事务(或者每个事务之外的语句)都会刷新到磁盘中，而这相当耗费资源，尤其是没有电池备用缓存时。很多应用程序，尤其是从 MyISAM转变过来的那些，把它的值设置为 2 就可以了，也就是不把日志刷新到磁盘上，而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去，因此通常不会丢失每秒1-2次更新的消耗。如果设置 为 0 就快很多了，不过也相对不安全了 -- MySQL服务器崩溃时就会丢失一些事务。设置为 2 指挥丢失刷新到操作系统缓存的那部分事务。
table_cache -- 打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁，所以通常要加大缓存数量，使得足以最大限度 地缓存打开的表。它需要用到操作系统的资源以及内存，对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话，那么设置为 1024 也许比较合适(每个线程都需要打开表)，如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。
thread_cache -- 线程的创建和销毁的开销可能很大，因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created 的值也比较大，那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。
query_cache -- 如果你的应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用。不要把它设置太大了，因为想要维护它也需要不少开销，这会导致MySQL变慢。通 常设置为 32-512Mb。设置完之后最好是跟踪一段时间，查看是否运行良好。在一定的负载压力下，如果缓存命中率太低了，就启用它。
sort_buffer_size –如果你只有一些简单的查询，那么就无需增加它的值了，尽管你有 64GB 的内存。搞不好也许会降低性能。
事务相关的技术术语
Checkpoint技术
为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。当由于宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务ACID中Durability(持久性)的要求。
Checkpoint技术是用来解决以下几个问题：
缩短数据库的恢复时间；
缓冲池不够用时，将脏页刷新到磁盘；
重做日志不可用时，刷新脏页。
当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘。故数据库只需对Checkpoint后的重做日志进行恢复。这样就大大缩短了恢复的时间。此外，当缓冲池不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行Checkpoint，将脏页也就是页的新版本刷新回磁盘。
重做日志出现不可用的情况是因为当前事务数据库系统对重做日志的设计都是循环使用的，并不是让其无限增大的。重做日志可以被重用的部分是指这部分重做日志已经不再需要，即当数据库发生宕机时，数据库恢复操作不需要这部分的重做日志，因此这部分就可以被覆盖重用。若此时这部分重做日志还需要使用，那么必须强制产生Checkpoint，将缓冲池中的页至少刷新到当前重做日志的位置。
对于InnoDB存储引擎而言，其是通过LSNLog Sequence Number来标记版本的。而LSN是8字节的数字，其单位是字节。每个页有LSN，重做日志中也有LSN，Checkpoint也有LSN。
重做日志
在默认情况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。这两个文件就是重做日志文件，或者事务日志。重做日志的目的：万一实例或者介质失败，重做日志文件就能派上用场。例如，数据库由于所在主机掉电导致实例失败，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。
每个InnoDB存储引擎至少有一个重做日志文件组，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0、ib_logfile1。InnoDB存储引擎先写重做日志文件1，当达到文件的最后时，会切换至重做日志文件2，当重做日志文件2也被写满时，会再被切换到重做日志文件1中。
重做日志缓冲
重做日志缓冲一般不需要设置的很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。默认为8MB。系统在以下三种情况下会将重做日志缓冲中的内容刷新到外部磁盘的重做日志文件中：
Master Thread每一秒将重做日志缓冲刷新到重做日志文件；
每个事务提交时会将重做日志缓冲刷新到重做日志文件；
当重做日志缓冲池剩余空间小与1/2时，重做日志缓冲刷新到重做日志文件。
事务的实现
事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的redo log和undo log来完成。redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性。redo和undo都可以视为一种恢复操作，redo恢复提交事务修改的页操作，而undo回滚行记录到某个特定版本。因此两者记录的内容不同，redo通常是物理日志，记录的是页的物理修改操作。undo是逻辑日志，根据每行记录进行记录。
redo
重做日志用来实现事务的持久性所以关于上面原子性的实现，有待商榷。其由两部分组成：一是内存中的重做日志缓冲，其是易失的；二是重做日志文件，其是持久的。
InnoDB是事务的存储引擎，其通过Force Log at Commit机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化。
为了确保每次日志都写入重做日志文件，在每次将重做日志缓冲写入重做日志文件后，为了确保日志写入磁盘因为这里有一个文件系统缓存，必须进行一次fsync操作。由于fsync的效率取决于磁盘的性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。
InnoDB存储引擎允许用户手工设置非持久性的情况发生，以此提高数据库的性能。即当事务提交时，日志不写入重做日志文件，而是等待一个时间周期后再执行fsync操作。这可以显著提高数据库的性能，但是当数据库发生宕机时，由于部分日志文件未写入磁盘，因此会丢失最后一段时间的事务。
LSN
LSN是Log Sequence Number的缩写，其代表的是日志序列号。在InnoDB存储引擎中，LSN占用8字节，并且单调递增。LSN代表的含义有：
重做日志写入的总量
checkpoint的位置
页的版本
LSN表示事务写入重做日志的字节总量。例如，当前重做日志的LSN是1000，事务T1写入了100字节的重做日志，LSN就变成1100，又有事务T2写入200字节的重做日志，那么LSN变成：1300。可见LSN记录的是重做日志的总量，其单位是字节。
每个页的头部也有一个LSN，记录的是该页最后刷新时LSN的大小。重做日志记录的是每个页的物理更改日志，因此页中的LSN用来判断是否需要进行恢复操作。例如：页的LSN为10000，数据库启动时，写入重做日志的LSN为13000，表明该事务已经提交，数据库需要恢复；重做日志中的LSN小于页中的LSN，不需要进行重做，因为页中的LSN表示已经刷新到该位置。
恢复
InnoDB存储引擎在启动时不管上次数据库运行时是否正常关闭，都会尝试进行恢复操作。
undo
重做日志记录了事务的行为，可以很好的通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要undo。如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。
除了回滚操作，undo的另一个作用是MVCC，即在InnoDB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过读取之前的行版本信息，以此实现非锁定读。
最后也是最为重要的一点是，undo log也会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。
purge
purge用于最终完成delete和update操作。这样设计是因为InnoDB存储引擎支持MVCC，所以记录不能在事务提交时立即进行处理。这时其他事务可能正在引用该行，故InnoDB存储引擎需要保存记录之前的版本。而是否可以删除该条记录通过purge来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真正的delete操作。
group commit
若事务为非只读事务，则每次事务提交时需要进行一次fsync操作，以此保证重做日志都已经写入了磁盘。然而磁盘的fsync性能是有限的，为了提高效率，当前数据库都提供了group commit功能，即一次fsync可以刷新确保多个事务日志被写入文件实现用了队列和流水线。
MySQL预写日志机制一览
在 MySQL 中，InnoDB 存储引擎实现了 WAL 机制。包含 Redo log buffer、Redo log、Undo Log 等，来记录事务已提交但未写入数据文件的数据变更以及事务回滚后的数据还原。
关于预写日志机制
一般情况下，大部分数据库都是将表和索引存储在磁盘文件中。当新增数据时，数据库系统会先写入内存，然后将其写入磁盘上的数据文件。
那为什么不直接写入磁盘？主要是每次新增都直接写入磁盘性能很低，放在内存中，可以批量写入磁盘以提升性能。但有一个问题，如果数据在写入磁盘文件中途断电怎么办？当来电恢复后并重启数据库发现数据不一致，又该如何处理。所以需要一些其他机制来避免断电引发的数据不一致，其实 MySQL 已经考虑到了这一点，内部已经实现一套 WAL（预写日志）机制来避免这一点。
MySQL 设计有健壮的恢复机制，特别是使用 InnoDB 存储引擎的情况下，它能够在断电后重启而不会崩溃。InnoDB 存储引擎使用预写日志（WAL）机制来确保数据的一致性和原子性。预写日志机制是一种数据库事务日志技术，它要求在任何数据库修改被写入到永久存储（也就是磁盘）之前，先将这些修改记录到日志中。
这样当 MySQL 遇到意外的断电情况时，它会在重启后利用 Redo log 来恢复已提交但未写入数据文件的事务继续写入数据文件，从而保证一致性，再利用 undo log 来撤销未提交事务的需改，从而保证原子性。
MySQL 的预写日志机制会涉及到其架构中的以下内容：
Buffer Pool（缓冲池）
Buffer Pool （缓冲池）是 InnoDB 存储引擎中非常重要的内存结构，顾名思义，缓冲池就是起到一个缓存的作用，因为我们都知道 MySQL 的数据最终是存储在磁盘中的，如果没有这个 Buffer Pool 那么我们每次的数据库请求都会磁盘中查找，这样必然会存在 IO 操作，这肯定是无法接受的。
但是有了 Buffer Pool 就是我们第一次在查询的时候会将查询的结果存到 Buffer Pool 中，这样后面再有请求的时候就会先从缓冲池中去查询，如果没有再去磁盘中查找，然后在放到 Buffer Pool 中。
Redo log buffer（日志缓冲区）
Redo log buffer 是用作数据变更记录写入 Redo log 文件前的一块内存区域。日志缓冲区大小由 innodb_log_buffer_size 变量定义，默认大小为 16MB。
日志缓冲区的内容会定期刷新到 Redo log 文件中，大型日志缓冲区允许大型事务运行，而无需在事务提交之前将 Redo log 数据写入磁盘。因此如果事务涉及的更新、插入或删除操作数据量较大时，可以增加日志缓冲区的大小可以节省磁盘 I/O。
MySQL 提交事务的时候，会将 Redo log buffer 中的数据写入到 Redo log 文件中，刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置：
1.值为 0 表示不刷入磁盘
2.值为 1 表示立即刷入磁盘
3.值为 2 表示先刷到 os cache
为了提高性能，MySQL 首先将修改操作写入到日志缓冲区，之后以 innodb_flush_log_at_trx_commit 参数设置落盘时机，将日志缓冲区刷入到磁盘的 Redo log 文件中去。
Redo Log
Redo Log 是 InnoDB 存储引擎中的一个重要组件，它是一种磁盘基础的数据结构，用于在崩溃重启期间修复由已提交事务但未写入数据文件的数据。
在正常操作中，Redo log 记录了由 SQL 语句执行导致的表数据变更记录。将 Redo log buffer 中的数据持久化到磁盘中，就是将 Redo log buffer 中的数据写入到 Redo log 磁盘文件中。
数据在由 Redo log buffer 写入 Redo log 时的触发时机如下：
1.MySQL 正常关闭时触发
2.当 Redo log buffer 中记录的写入量大于 Redo log buffer 内存空间的一半时，会触发落盘
3.InnoDB 的后台线程每隔 1 秒，将 Redo log buffer 持久化到磁盘
4.每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略就是由上文提高 innodb_flush_log_at_trx_commit 参数控制）
Redo log 是 WAL 机制的核心，它记录了事务所做的所有修改。如果数据库发生故障，可以使用 Redo 日志来重做事务，从而确保数据的一致性。
Undo Log
Undo Log 记录了如何撤销一个事务的修改。如果需要回滚事务或在执行事务时还未提交，数据库就发生了崩溃，这时我们就需要将未提交事务前的数据回滚回去，难道这个操作有我们自己来做吗？显然 MySQL 也考虑到了这一点。
MySQL 会使用 Undo log 来撤销未提交的修改。在操作数据前，MySQL 首先将数据备份到 Undo log，然后进行数据修改。如果出现错误或者用户执行了 Rollback 语句，系统可以利用 Undo log 中的备份将数据恢复到事务操作前的状态。
通过 Undo log 撤销修改，从而确保数据的原子性。
结合 Buffer Pool、Redo log buffer、Redo log、Undo log 后，我们在MySQL 中更新一条数据的流程如下，
图片来源于此处，相关步骤如下：
1.准备更新一条 SQL 语句
2.Innodb会先去缓冲池（Buffer Pool）中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池（Buffer Pool）中
3.在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
4.innodb 会在 Buffer Pool 中执行更新操作
5.更新后的数据会记录在 Redo log buffer 中
6.MySQL 提交事务的时候，会将 Redo log buffer 中的数据写入到 Redo log 文件中，刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置
7.MySQL 重启的时候会将 Redo log 恢复到缓冲池中。
额外知识：检查点（Checkpoint）
检查点是什么？为什么有了 Redo log、Undo log 还要引入检查点。明明借助 Redo log、Undo log 就可以实现 MySQL 的故障恢复了。
虽然数据在写入 Redo log 文件后，就代表数据变更已经生效了，但是还未写入到数据文件，也就是还没有完成事务的持久性。
检查点就是帮助 MySQL 实现事务的持久性。
如果说 Redo log 可以无限地增大，能够保存所有数据库变更的数据，那么在发生宕机时完全可以通过 Redo log 来恢复数据库系统的数据到宕机发生前的情况。
然而现实是物理磁盘文件大小是有效的。即使达成无限了，如果数据库运行了很久后发生宕机，那么使用 Redo log 进行恢复的时间也会非常的久。所以在 Redo log 文件容量是有限的情况下，还需要定期将 Redo log 写入数据文件完成数据的持久化，在这样的情况下，就引入了 Checkpoint（检查点）技术。
Checkpoint 技术不仅仅是会同步 Redo log 写入数据文件，也会同步脏页数据写入数据文件。检查点的触发时机有两种如下：
1.Sharp Checkpoint(完全检查点)
将内存中所有脏页全部写到磁盘就是完全检查点，比如数据库实例关闭时。
2.Fuzzy Checkpoint(模糊检查点)
将部分脏页刷新到磁盘，就是模糊检查点，一般就是脏页达到一定数量时触发。数据库实例运行过程产生的检查基本上就是这种类型的检查点。
因此其实 Checkpoint 就是指一个触发点（时间点），当发生 Checkpoint 时，会将脏页写回磁盘，以确保数据的持久性和一致性。并且 Redo log、Undo log 文件也可以重新覆写，这样可以保证重启时不会因为 Redo log、Undo log 文件太大而导致重启时间过长。
断电故障恢复案例
假如正在使用 MySQL 添加数据。在提交事务的过程中，突然发生了断电，那么这个数据会丢吗？结合上文MySQL中更新一条数据的流程，来分析下具体场景：
1.数据在写入 Buffer Pool、Redo log buffer 中时，发生断电
先说结论，会丢。因为数据没有写入 Redo log 前，MySQL 是没办法保证数据一致性的。但是这没关系的，因为 MySQL 会认为本次事务是失败的，在重启后可以根据 Undo log 文件将数据恢复到更新前的样子，并不会有任何的影响。
2.数据在写入 Redo log 文件后，发生断电
先说结论，不会丢。因为 Redo log buffer 中的数据已经被写入到 Redo log 了，就算数据库宕机了，在下次重启的时候 MySQL 也会将 Redo log 文件内容恢复到 Buffer Pool 中进行重放。
详解undo log与redo log
数据库通常借助日志来实现事务，常见的有undo log、redo log，undo/redo log都能保证事务特性，这里主要是原子性和持久性，即事务相关的操作，要么全做，要么不做，并且修改的数据能得到持久化。
假设数据库在操作时，按如下约定记录日志：
1.事务开始时，记录START T
2.事务修改时，记录T，x，v，说明事务T操作对象x，x的值为v
3.事务结束时，记录COMMIT T
undo log原理
undo log是把所有没有COMMIT的事务回滚到事务开始前的状态，系统崩溃时，可能有些事务还没有COMMIT，在系统恢复时，这些没有COMMIT的事务就需要借助undo log来进行回滚。
使用undo log时，要求：
1.记录修改日志时redo log，(T，x，v中v为x修改前的值，这样才能借助这条日志来回滚；
2.事务提交后，必须在事务的所有修改包括记录的修改日志都持久化后才能写COMMIT日志；这样才能保证，宕机恢复时，已经COMMIT的事务的所有修改都已经持久化，不需要回滚。
使用undo log时事务执行顺序
1.记录START T
2.记录需要修改的记录的旧值要求持久化
3.根据事务的需要更新数据库要求持久化
4.记录COMMIT T
使用undo log进行宕机回滚
1.扫描日志，找出所有已经START,还没有COMMIT的事务。
2.针对所有未COMMIT的日志，根据redo log来进行回滚。
如果数据库访问很多，日志量也会很大，宕机恢复时，回滚的工作量也就很大，为了加快回滚，可以通过checkpoint机制来加速回滚:
在日志中记录checkpoint_start (T1,T2…Tn) (Tx代表做checkpoint时，正在进行还未COMMIT的事务
等待所有正在进行的事务T1~TnCOMMIT
在日志中记录checkpoint_end
借助checkpoint来进行回滚
从后往前，扫描undo log
1.如果先遇到checkpoint_start, 则将checkpoint_start之后的所有未提交的事务进行回滚；
2.如果先遇到checkpoint_end，则将前一个checkpoint_start之后所有未提交的事务进行回滚；在checkpoint的过程中，可能有很多新的事务START或者COMMIT)。
使用undo log，在写COMMIT日志时，要求redo log以及事务的所有修改都必须已经持久化，这种做法通常很影响性能。
redo log原理
redo log是指在回放日志的时候把已经COMMIT的事务重做一遍，对于没有commit的事务按照abort处理，不进行任何操作。
使用redo log时，要求：
1.记录redo log时，(T,x，v中的v必须是x修改后的值，否则不能通过redo log来恢复已经COMMIT的事务。
2.写COMMIT日志之前，事务的修改不能进行持久化，否则恢复时，对于未COMMIT的操作，可能有数据已经修改，但重放redo log不会对该事务做任何处理，从而不能保证事务的原子性。
使用redo log时事务执行顺序
1.记录START T
2.记录事务需要修改记录的新值要求持久化
3.记录COMMIT T要求持久化
4.将事务相关的修改写入数据库
使用redo log重做事务
1.扫描日志，找到所有已经COMMIT的事务；
2.对于已经COMMIT的事务，根据redo log重做事务；
在日志中使用checkpoint
1.在日志中记录checkpoint_start (T1,T2...Tn) (Tx代表做checkpoint时，正在进行还未COMMIT的日志
2.将所有已提交的事务的更改进行持久化；
3.在日志中记录checkpoint_end
根据checkpoint来加速恢复
从后往前，扫描redo log
1.如果先遇到checkpoint_start, 则把T1~Tn以及checkpoint_start之后的所有已经COMMIT的事务进行重做；
2.如果先遇到checkpoint_end, 则T1~Tn以及前一个checkpoint_start之后所有已经COMMIT的事务进行重做；
与undo log类似，在使用时对持久化以及事务操作顺序的要求都比较高，可以将两者结合起来使用，在恢复时，对于已经COMMIT的事务使用redo log进行重做，对于没有COMMIT的事务，使用undo log进行回滚。redo/undo log结合起来使用时，要求同时记录操作修改前和修改后的值，如T，x，v，w，v为x修改前的值，w为x修改后的值，具体操作顺序为：
1.记录START T
2.记录修改日志T，x，v，w要求持久化，其中v用于undo，w用于redo
3.更新数据库
4.记录 COMMIT T
4和3的操作顺序没有严格要求，并且都不要求持久化；因为如果宕机时4已经持久化，则恢复时可通过redo log来重做；如果宕机时4未持久化，则恢复时可通过undo log来回滚；在处理checkpoint时，可采用与redo log相同的处理方式。
该文章最后由 阿炯 于 2023-12-11 10:24:10 更新，目前是第 2 版。