本页讨论
- InnoDB 和 MyISAM 的区别
- InnoDB 缓存相关优化
- InnoDB事务优化
- InnoDB数据存储优化
InnoDB 存储引擎和 MyISAM 存储引擎最大区别主要有 4点：
- 缓存机制；
- 事务支持；
- 锁定实现；
- 数据存储方式差异；
innodb_buffer_pool_size 的合适设置
InnoDB 不仅仅缓存索引，同时还会缓存实际的数据。所以， InnoDB 存储引擎可以使用更多的内存来缓存数据库的相关信息。
innodb_buffer_pool_size 参数用来设置 InnoDB 最主要的 Buffer（InnoDB Buffer Pool）的大小，缓存用户表及索引数据的最主要缓存空间，对 InnoDB 整体性能影响也最大。
InnoDB 的 Buffer Pool 简单设置为整个系统物理内存的 50%~80%之间，具体设置多大要根据实际环境技术，例如一台单独 MySQL 主机， 物理内存 8G， MySQL 最大连接数为 500，同事还是用了 MyISAM 存储引擎，这时整体内存分配如下：
- 系统使用，假设预留 800MB；
- 线程独享，最大约为 2GB = 500 * （1MB + 1MB + 1MB + 512KB + 512KB）
- sort_buffer_size： 1MB
- join_buffer_size: 1MB
- read_buffer_size：1MB
- read_rnd_buffer_size：512KB
- thread_statck：512KB
- MyISAM Key Cache：假设大概 1.5GB。
- InnoDB Buffer Pool 最大可用： 8GB - 800MB - 2GB - 1.5GB = 3.7 GB
生产环境 InnoDB 存储引擎哟啊根据 Buffer Pool 实时信息作出进一步分析。
show status like "Innodb_buffer_pool_%";
上图可看出 总共有 8191 个page， 有 7303 个是 Free 状态， 有 788个 page 有数据， read 请求 31569次，其中有 710次请求使用物理磁盘获取
InnoDB Buffer Pool 的 Read 命中率大概： （31569-710)/ 31569 * 100% = 97.75%
Innodb_buffer_pool_read_ahead_rnd：记录进行随机读的时候产生的预读次数；
Innodb_buffer_pool_read_ahead_seq：记录连续读的时候产生的预读次数；
预读 -- 在一些高端存储才会有，简单来说，就是通过分析数据请求的特点来自动判断客户端在请求当前数据块之后可能会继续请求的数据块。通过该自动判断，存储引擎可能会一次性将当前请求的数据库和后面可能请求的下一个（或者几个）数据库全部读出，以期望通过这种方式减少磁盘 IO 次数，提高 IO 性能 。
innodb_log_buffer_size 参数的使用
设置 InnoDB 的 Log Buffer 大小，系统默认 1MB。 Log Buffer 的主要作用就是 缓冲 Log 数据，提高写 Log 的 IO 性能。一般来说，如果系统不是写负载非常高而且以大事务居多的话， 8MB 以内大小足够。
也可以通过 系统状态 参数提供的性能统计数据来分析 Log 的使用情况
show status like "Innodb_log_%";
innodb_additional_mem_pool_size 参数
设置用户存放 InnoDB 的字典信息和其他内部结构所需要的内存空间。所以， InnoDB 表越多，需要的空间自然也就越大，系统默认 1MB。
一个常规的 几百个 InnoDB 表的 MySQL ，如果不是每个表都是上百个字段， 20MB 内存已经足够了。
该参数对系统整体性能并无太大影响，设置超过实际所需存储，只是浪费内存而已。
Double Write Buffer
Double Write Buffer 是 InnoDB 所使用的一种较为独特的文件 Flush 实现技术，主要作用是在减少文件同步次数提高 IO 性能的情况下，提高系统崩溃（Crash）或断电情况下的安全性，避免写入的数据不完整。
Adaptive Hash Index
Adaptive Hash index 的目的并不是为了改善磁盘 IO 性能，而是为了提高 Buffer Pool 中的数据访问效率，也就是给 Buffer Pool中的数据做索引。
脏读：指当一个事物正在访问数据，并且对数据进行修改，而这种修改没有提交到数据库中，这时，另外一个事物也访问这个数据，然后使用了这个数据。 不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据不一样，因此成为不可重复读。
幻读：指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改了这个表中的数据，如想表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还没有修改的数据行，就好像发生了幻觉一样。
选择合适的事务隔离级别
- READ UNCOMMITTED ：
- 常被称为 Dirty Reads（脏读），事务上的最低隔离级别。
- READ COMMITTED：
- 这一隔离级别下不会出现 Dirty Reads（脏读），但是可能出现 Non-Repeatable Reads（不可重复读）和 Phantom Reads（幻读）
- REPEATABLE READ：
- 是 InnoDB默认的事务隔离级别。 这一隔离级别下不会垂涎 Dirty Reads，也不会出现 Non-Repeateable Reads，但可能会出现 Phantom Reads。
- SERIALIZABLE：
- 标准事务隔离级别中的最高级。这一隔离级别下， Phantom Reads 也不会出现。
对于高并发应用来说，为了尽可能保证数据的一致性，避免并发可能带来的数据不一致，自然是事务隔离级别越高越好。但是，对于 InnoDB 来说，所使用的事务隔离级别越高，实现复杂度自然就会更高，所要做的事情也会更高，整体性能也就更差。虽然 InnoDB 存储引擎默认的事务隔离级别是 REPEATABLE READ，但实际上在大部分应用中，只需要 READ COMMITED 的事务隔离级别就可以满足需求了。
InnoDB 修改数据操作，实际上修改的是Buffer Pool中的数据。
为了保障数据的安全稳定，不丢失数据。InnoDB并不是一个事务提交后就将 Buffer Pool 中被修改的数据同步到磁盘上，而是要先记录到redo log日志中，以防崩溃之后可以恢复。
最后再从Buffer Pool 中把脏页连续写入磁盘。
而是记录到redo log日志中也不是直接写磁盘，而是先写到redo log 缓冲区
控制 InnoDB 事务日志刷新方式参数：innodb_flush_log_at_trx_commit :
- innodb_flush_log_at_trx_commit = 0， InnoDB 中的 Log Thread 每隔 1秒将 log buffer 中的数据写入文件，同时还会通知文件系统进行与文件同步的 flush操作，保证数据确实已经写入磁盘。
- innodb_flush_log_at_trx_commit = 1， InnoDB 默认设置。每次事务的结束都会出发 Log Thread 将 Log Buffer 中的数据写入文件、并通知文件系统同步文件。这个设置最安全，能够保证不论是 MySQL 崩溃、OS崩溃还是主机断电都不会丢失任何已经提交的数据。
- innodb_flush_log_at_trx_commit = 2， 每次事务结束的时候将数据写入事务日志，仅仅是调用了文件系统的文件写入操作。而文件系统都是有缓存机制的，所以 Log Thread 的写入并不能保证内容已经写入到物理磁盘完成持久化的动作。文件系统什么时候会将缓存中的数据同步到物理磁盘、文件， Log Thread 就完全不知道，所以，当设置 2 的时候， MySQL 崩溃并不会造成数据的丢失，但是 OS 崩溃或主机断电后可能丢失的数据量就完全控制在文件上了。
分析:
设置1 最安全，由于 IO 同步操作多，所以，性能最低。
设置 0 ，则每秒一次同步，性能相对高一下。
如果设置 2 ，性能可能是三种最好的。但是也可能出现故障后丢失数据最多的一种。如果 OS 足够稳定，主键硬件设备足够好，且主机供电系统足够安全，可将设置为2 ，让系统整体性能尽可能高。【建议设置为 2】
理解 InnoDB 数据及索引文件存储格式
- Page
- InnoDB 存储引擎中的所有数据，不论是表还是索引，或是存储引擎自己的各种结构，都以 page 作为最小物理单位来存放，每个 page 默认大小 16KB。
- extent
- extend 是多个连续 page 组成的一个物理存储单位。一般来说，每个 extend 为 64 个page。
- segment
- segment 在 InnoDB 粗才能引擎中实际上也代表 “ file"，每个 segment 由一个或多个 extent 组成，而且每个 segment 都存放同一种数据。一般来说，每个表数据会存放于一个单独的 segment 中，实际上也就是每个 聚簇索引会存放一个单独的 segment 中。
- tablespace
- InnoDB中最大的物理结构单位，由多个 segment 组成。
查看 InnoDB 表空间的使用情况：
show table status like "tablename"\G;
对性能有较大影响的 4 个要点：
- 为了尽量减小 secondary index 的大小，提高访问效率，作为主键的字段所占用的存储空间越小越好，最好是 INTEGER 类型。当然这并不是绝对的，字符串类型的数据同样可以作为 InnoDB表的主键。
- 创建表的时候尽量自己制定相关的主键，让数据按照预设的顺序排序存放，以提高特定条件下的访问效率。
- 尽可能不要在主键上进行更新操作，减少因为主键值的变化带来的数据的移动。
- 尽可能提供后主键条件进行查询。
分散IO 提升磁盘相应：
由于 InnoDB 和其他非事务存储引擎相比，在记录数据文件的同事还记录相应的事务日志（Transaction Log）， 相当于增加了整体的 IO 量。虽然事务日志是以完全顺序的方式写入磁盘，但总是会有一定的IO消耗，所以对没有使用REID 的存储系统来说，建议将数据文件和事务日志文件分别存放于不同的物理磁盘以降低磁盘的相互争用，提高整体 IO 性能。
- innodb_log_group_home_dir：指定InnnDB 日志存放位置；
- innodb_data_home_dir ： 指定 InnoDB 数据文件存放位置；
- innodb_autoextend_increment：设置自行控制表空间文件每次增加的大小；