MySQL--InnoDB笔记

1.InnoDB存储引擎 

• DML(Data manipulation Language)遵循ACID模式，并且事务具有commit、rollback和crash-recovery的功能以保护用户的数据.
• 行级锁(row-level lock)和Oracle风格的一致性读(consistent reads)提升了多用户的并发性和性能.
• InnoDB表会根据主键在磁盘上排列数据以优化查询.每一个InnoDB表都有一个称为聚簇索引(clustered index)的主键索引来组织数据以减少主键查询时的I/O操作.
• 为了维护数据的完整性，InnoDB支持外键(FOREGIN KEY)约束.有了外键，将检查插入、更新和删除操作，以确保它们不会导致不同表之间的不一致.

1.3.使用InnoDB的好处

        这里只挑选了一些个人认为对比较重要的点，更多细节请参考Benefits of Using InnoDB Tables.

• 故障恢复(crash recovery).不管你的服务器因为硬件还是软件的问题发生了故障，不管当时数据库正在发生什么，你不需要在重启数据库之后做任何特殊的操作.InnoDB的故障恢复机制会自动的完成故障前已经提交的修改，而故障前未提交的数据将被丢弃.
• 缓存池(buffer pool):当数据被访问时，InnoDB引擎会在主存中维护自己的缓存池来缓存表以及索引数据.频繁使用的数据将直接从内存中访问.该缓存能适用于多种不同类型的信息，并能提升处理速度.在一些专用的数据库服务器上，通常超过80%的物理内存会分配给缓存池.
• 外键(foreign keys):当把相关联的数据拆分到不同的表中时，可以使用外键.当修改或者删除数据时，在其他表的相关联的数据将会被自动修改或删除.当尝试向一个二级表中插入数据而没有修正主表中的数据时，脏数据将被自动剔除.
• 当你为你的数据库中的每个表设计了适当的主键列时，包含这些列的操作将自动被优化.当在WHERE语句、ORDER BY语句、GROUP BY语句和join操作中引用这些主键列时会变得非常快.
• 插入、修改和删除会被一种称为change buffering的自动机制优化.InnoDB不仅允许并发的读写同一张表，它还会对修改进行缓存以提高磁盘的I/O效率.
• 你可以在对性能和可靠性产生很小影响的情况下创建和删除索引.

1.4.InnoDB表的最佳实践

• 为每一张表指定一个主键.最好使用会被频繁查询的一列或多列，如果没有明显的主键也可以使用一个被标记为自增(auto-increment)的列.
• 当基于指定的ID值从多个表中拉取数据时要使用join操作.为提高性能，可以将这些做join操作的列都定义为外键，并在每个表中都将这些列声明为相同的数据类型.定义为外键可以确保这些列会被索引化，这样可以提高性能.
• 关闭自动提交(autocommit).一秒钟几百次提交可能影响性能(受限于你的存储设备的写入速度).
• 将相关联的DML操作放入事务中.
• 不要使用锁表(LOCK TABLES)语句.InnoDB可以同时处理多个读写同一张表的会话而不会影响可靠性和高性能.为了独占式的获取一些列的写入权限，可以使用SELECT ... FOR UPDATE语法来锁住你想要修改的列.
• 开启innodb_file_per_table选项，使一个表的数据和索引放入不同的文件中(也被称为独立表空间)，而不是放入系统表空间(system tablespace)中.这个设置会要求使用其他的特性，比如表压缩(compression)(默认开启的).
• 评估你的数据和访问模式是否受益于InnoDB的表压缩功能.
• 使用 --sql_mode=NO_ENGINE_SUBSTITUTION参数运行你的服务器以防止表被不同的存储引擎创建.

2.InnoDB和ACID

        ACID模式是一系列强调对业务数据和关键任务应用程序很重要的可靠性方面的数据库设计原则.

• A:atomicity，原子性.
• C:consistency，一致性.
• I:isolation，隔离性.
• D:durability，持久性.

3.InnoDB多版本

3.1.多版本

        InnoDB是一个多版本存储引擎(multi-versioned storage engine)：它保存了被修改行的老版本信息来支持像并发性和rollback这样的事务性功能.该信息被存储在表空间中一个叫回滚段(rollback segment)的数据结构之中.InnoDB使用这些信息来支持事务回滚中的undo操作，也使用这些信息来为一致性读(consistent read)操作构建一行数据记录的早期版本.

InnoDB会为数据库中的每一行数据添加3个字段:

DB_TRX_ID：6-byte大小，用来存储上一个插入或者修改当前行的事务id.删除也被当做一个特殊的修改，用一个bit位来标记当前数据行已被删除.

DB_ROLL_PTR：7-byte大小，该指针指向一个回滚段中的undo日志记录.如果当前行被修改，那么undo日志中包含重建修改前数据的所有必要信息.

DB_ROW_ID：6-byte大小，存储一个自增id值.如果InnoDB自动生成聚簇索引(用户没有指定主键的情况下)，索引中就会包含该字段的值.

        回滚段中的undo日志分为插入和修改.插入undo日志只在事务回滚时使用，并且事务一旦提交就可以丢弃.修改undo日志不仅用于事务回滚，也会用于一致性读，只有当没有事务需要使用修改undo日志来为一致性读构建数据记录早期版本的快照(snapshot)时才能进行丢弃.因此，要经常提交你的事务，包括那些只与读操作相关的事务.否则，InnoDB不会丢弃修改undo日志，这样就会导致回滚段变得越来越大以至于填满你的表空间(tablespace).一个回滚段中的undo日志记录的物理大小一般来说会比被插入或被修改的相应的数据行要小，你可以据此来推算回滚段需要的空间大小.

        在InnoDB的多版本方案中，当你用SQL语句删除一个数据行的时候，它并不会马上从数据库中被物理删除.只有当为删除操作建立的修改undo日志被丢弃之后InnoDB才会物理的删除相应的数据行和它的索引.这种移除操作被称为清除(purge)，它非常快，通常采用和执行删除操作的SQL语句相同的时间顺序.

        如果以恒定的速率在表中小批量的插入和删除数据行，清除线程(purge thread)会因为来不及处理清除操作而开始滞后，表就会因为那些已经‘死亡’的数据行而变得越来越大，进而产生disk-bound并且会变得很慢.可以通过调整innodb_max_purge_lag来避免上述情况，但是应该根据具体情况进行分析.

3.2.多版本和二级索引

       InnoDB的MVCC(multi-version concurrency control)对待二级索引(secondary index)和聚簇索引(clustered index)不太一样.在聚簇索引中的数据是实时更新，并且他们包含隐藏的系统列.二级索引数据不包含隐藏的系统列也不是实时更新的.

        当一个二级索引列被修改时，老的二级索引数据被标记为删除，新的数据会被插入，最终被标记为删除的数据会被清除.当一个二级索引记录被标记删除或二级索引页被一个新的事务修改时，InnoDB将会在聚簇索引中查询数据库记录.在聚簇索引中会检查记录的DB_TRX_ID字段，以便在读事务开始后如果记录被其他事务修改的情况下返回正确的undo日志.

        如果一个二级索引记录被标记为删除或者二级索引页被一个新的事务修改，覆盖索引(covering index)技术将不会被使用.InnoDB会在聚簇索引中查找记录，而不是直接从索引结构中返回数据.

         如果启用了index condition pushdown(ICP)优化，并且可以仅使用索引中的字段来评估部分Where条件，则mysql服务器仍会将这部分Where条件下推到存储引擎中，并使用索引对其进行评估.如果找不到匹配的记录，那么就避免了去聚簇索引中查找.如果找到了匹配的记录，即使是被标记为删除的记录，InnoDB都会到聚簇索引中查找记录.

4.InnoDB内存结构

4.1.Buffer Pool

        Buffer Pool是一个用于存放被访问的表和索引数据的内存区域.Buffer Pool越大，mysql越像一个内存数据库.

         为了提升大数据量读操作的效率，buffer pool被划分为可能包含多行数据的页(pages).为了高效的管理缓存，buffer pool被实现为一个linked list，链表中的每一个节点就是一个page.Buffer pool使用一个LRU(least recently used)算法的变体来实现数据的管理.

Buffer Pool的LRU算法.

        当需要空间来添加一个新的page时，最近最少使用的page将被丢弃并且新的page将被插入到链表的中间.这个中点插入算法(midpoint insertion strategy)将该list当作两个子列表:

• 在头部，是一个存放最近被访问的新页(new "young" page)的子列表，new sublist.
• 在尾部，是一个存放最近很少被访问的老页(old page)的子列表，old sublist.

下图是Mysql官网中的buffer pool链表的结构图

        这个算法使被频繁访问的pages处于new sublist中.Old sublist中存放很少使用的pages，这些pages是将被丢弃的候选pages.

默认情况下，算法操作如下:

• 3/8的buffer pool被分配给old sublist.
• 中点(midpoint of the list)是new sublist的尾节点和old sublist的头节点相遇的边界处. 如上图.
• 当InnoDB将一个page读入到buffer pool中时，它最初是被插入到中点(old sublist的头节点).
• 当访问一个在old sublist中的page时，将使该page变得年轻(young)，并将它移动到buffer pool的头节点(也就是new sublist的头节点).如果一个page是因为用户操作而被读取(例如一个query语句)，那么第一次访问(first access)就会立即触发并将该page变得年轻(young).如果一个page是因为预读(read-ahead)操作而被读取，那么第一次访问(first access)可能在该page被丢弃之前都不会发生.
• 当数据库运行时，那些在buffer pool中没有被访问的pages通过向队列的尾节点移动来增加年龄(age)，越靠近buffer pool的尾节点，年龄越大.在new sublist和old sublist中的pages的年龄都会因为其他page变得年轻而增加，换句话来说就是每当有一个page变年轻了(该page被移动到buffer pool的头结点)，那么new和old sublist中的其他page都会依次向后移动.在old sublist中的page也会因为在中点插入页而向尾节点移动.最终，当一个未被访问的页到了old sublist的尾节点时将被丢弃.

默认情况下，通过query语句读取的pages会马上被移动到new sublist中，意味着他们能在buffer pool存活更长的时间.一个全表扫描(例如一次mysqldump操作，或者一个没有where条件的select语句)会使大量的数据进入buffer pool中并且会丢弃相同数量的老数据，尽管这些新的数据将不会被再次使用.类似的还有通过预读后台线程加载的pages，在被访问一次之后就会被放入new sublist的头节点中，尽管之后可能并不会访问这些pages.这些情况将导致那些真正会被经常访问的pages进入到old sublist，这样他们就变成了可能被丢弃的对象.

Buffer pool配置

你可以配置buffer pool的各个方面提高性能.

• 理想情况下，可以 将buffer pool的大小设置为一个尽可能大的值，为服务器上的其他进程保留足够的内存，以便在不过度分页的情况下运行.buffer pool越大，InnoDB越像一个内存数据库，只从磁盘上读取一次数据，之后的query操作都直接访问内存.通过 innodb_buffer_pool_size来配置buffer pool的大小.
• 在有充足内存的64位系统中，可以将buffer pool分割成多个部分来减少
• 并发操作对内存结构的竞争.参考 Configuring  Multiple Buffer Pool Instances.
• 你可以将经常被访问的数据保持在内存中，而忽略掉那些在高峰时刻可能带来大量不会经常被访问的数据到buffer pool中的操作.参考 Making the Buffer Pool Scan Resistant.
• 你可以控制后台刷新何时发生并且是否根据workload来动态调整刷新频率.参考 Configuring InnoDB Buffer Pool Flushing.
• 您可以控制何时以及如何执行预读请求，以异步地将pages预取到buffer pool中，以预期很快将需要这些pages.参考 Configuring InnoDB Buffer Pool Prefetching(Read-Ahead).
• 可以微调缓冲池刷新行为的各个方面，以提高性能.参考 Fine-tuning InnoDB Buffer Pool Flushing.
  
• 您可以配置InnoDB如何保留当前buffer pool的状态，以避免服务器重新启动后长时间预热.参考 Saving and Restoring the Buffer Pool State.
  

4.2 Change Buffer

Change buffer是一个特殊的数据结构用来缓存不在buffer pool中的二级索引页的变更 .这些变更，可能来自INSERT，UPDATE或者DELETE操作，将在这些pages被其他读操作加载到buffer pool中之后被合并.

参考MySQL官网中的结构图:

不像聚簇索引，二级索引通常并不是唯一的，而且插入二级索引是以一个相对随机的顺序.类似的，删除和修改操作可能会影响在索引树种不相邻的二级索引页.当pages被其他操作读取到buffer pool中时才去合并缓存的变更，这样避免了大量的随机I/O访问将二级索引页从disk中读取到buffer pool中.

定期的，在系统空闲或者在slow shutdown期间运行的清洗操作(purge operation)，会将被修改的索引页写入到磁盘中.清洗操作(purge operation)可以将一系列索引数据写入到磁盘块中，这样比将每一个数据立马写入磁盘更加高效.

当有很多被影响的行和很多二级索引需要更新时，Change buffer合并可能要花费好几个小时.在这期间，磁盘I/O可能会增加，这可能导致disk-bound的查询显著的变慢.Change buffer合并可能在一个事务被提交，甚至在一个服务被showdown或restart之后也会持续发生.

在内存中，change buffer占据了一部分buffer pool.在磁盘上，change buffer是系统表空间的一部分，这是当服务shut down的时候来缓存索引变更的.

在change buffer中缓存的数据的类型是受 innodb_change_buffering 变量控制的.

当一个索引包含降序的索引列或主键包含一个降序的索引列时，二级索引不支持变更缓存.

配置Change Buffering

当INSERT、UPDATE和DELETE操作在一个表上被执行时，被索引列的值通常是未排序的，需要大量的I/O来使二级索引实时更新.当相关的page不在buffer pool中时，Change buffer会缓存对这些二级索引项的修改，这样就避免了立即从磁盘读取page的昂贵的I/O操作.这些被缓存的变更会在相关的page被加载到buffer pool中时被合并到page中，这些被修改的page会在随后被刷新到磁盘上.InnoDB的主线程会在服务空闲或在slow shutdown期间对这些缓存变更进行合并.

因为它能减少磁盘的读写，因此change buffer对那些磁盘绑定的工作负载最优价值，例如有高频DML操作(例如大量的insert)的应用.

Change buffer占用了一部分的buffer pool，因此会减少缓存数据页的内存空间.如果工作集大部分都能在buffer pool中(也就是说要操作的大部分pages都已经在buffer pool中)，或者你的表只有相应的一些二级索引，那么禁用change buffering可能是有效的.因为change buffer只对那些不在buffer pool中的pages有用.

可以通过 innodb_change_buffering  配置参数来控制change buffering的范围.一个修改操作是一个插入操作和删除操作的联合.可用的 innodb_change_buffering值如下:

• all:默认值，缓存inserts，delete-marking操作，和清除操作.
• none:不缓存任何操作.
• inserts:只缓存插入操作.
• deletes:只缓存delete-marking操作.
• changes:缓存inserts和delet-marking操作.
• purges:缓存在后台发生的物理删除操作.

你可以通过在MySQL的配置文件(my.cnf或者my.ini)中的 innodb_change_buffering 设置或者通过 SET GLOBAL 语句来进行动态配置.

配置Change Buffer最大大小

innodb_change_buffer_max_size参数允许配置change buffer占buffer pool的最大百分比.默认是25，最大能设置为50.

4.3 自适应Hash索引

基于观察搜索的模式，来利用索引key的前缀来创建hash索引.这个前缀可以是任意长度的，可能只有一个B-tree的的值会出现在hash索引中.Hash索引是针对那些经常被访问的索引页来建立的.

如果一个表的数据基本都可以在内存中匹配，那么hash索引可以提高查询的速度.InnoDB有一种机制监控索引查询.如果InnoDB觉得查询可以受益于创建hash索引，那么它就会自动建立hash索引.

在某些工作负载下，hash索引查询的速度提升远大于监控索引查询和维护hash索引结构的额外工作.在过量工作负载的情况下，访问自适应hash索引有时可能会成为竞争的一个源头，例如多个并发连接(我理解当存在并发情况的时候，只有一个线程会去创建hash索引，因此会存在竞争).LIKE语句不会使用hash索引.在MySQL 5.6版本使得禁用自适应hash索引比之前的版本更加合适.

4.4 Log Buffer

Log buffer是存储要那些将要写入磁盘日志文件中的数据的一块内存区域.可以通过innodb_log_buffer_size来定义log buffer的大小.默认大小是16MB.Log buffer中的内容是周期性的刷新到磁盘中.一个大的log buffer可以使那些大的事务在事务提交前不需要将redo log数据写入到磁盘中.因此，如果你有会修改、插入或者删除很多行的事务，那么你可以增加log buffer的大小来减少I/O操作.

innodb_flush_log_at_trx_commit参数控制log buffer的数据是如何写入和刷新到磁盘的.innodb_flush_log_at_timeout参数控制log的刷新频率.

5.InnoDB磁盘(On-Disk)结构

过长的事务:当事务开启时，系统会在事务开始时保存一份数据的快照，因此如果存在大量的插入、修改或删除操作使，会导致大量的开销.

• 当事务完成时，始终进行COMMIT或ROLLBACK操作.
• ROLLBACK是相对昂贵的操作，最好使大部分的修改能成功commit，而进行较少的rollback.
• 对于进行大量插入操作的语句，应该周期性的进行commit操作，避免一次性commit很长时间(例如几个小时).如果出错，最好truncate表然后从头开始执行，而不要执行rollback操作.

过短的事务:过短的事务会导致频繁的I/O操作，也会产生大量开销.

• 对InnoDB表的大部分操作，最好设置autocommit = 0.从效率的角度来看，这样可以避免不必要的I/O操作.从安全的角度来说，这样也可以在发生错误的时候执行ROLLBACK进行回滚.
• 即使是一个SELECT语句也会创建一个事务，因此在最后也需要加上COMMIT操作或者关闭mysql session.

处理死锁(Deadlocks):对InnoDB表来说，deadlock并不是一个严重的问题，通常也不需要相应的纠正措施.当两个事务开始修改不同的表，并且以不同的顺序访问这些表时，就可能会因为互相等待而都不能再继续执行.MySQL会立即发现这个情况，并且取消(roll back)较小(smaller)的事务，然后允许其他的事务继续执行.如果死锁发生的频率比较高的恶化，你可能需要review代码然后对SQL操作进行重新排序，或者将一个大的事务拆分成几个更小的事务.

5.1 InnoDB中处理AUTO_INCREMENT

InnoDB提供了一种可配置的锁机制，它可以显著提高带AUTO_INCREMENT列的SQL语句的扩展性和性能，这里的SQL语句是指向表中添加数据的SQL语句.

术语:

• "INSERT-like"语句:所有能在表中产生新行的语句，包括INSERT、INSERT ... SELECT、REPLACE、REPLACE ... SELECT和LOAD DATA.包括"simple-inserts"、"bulk-inserts"和"mixed-mode inserts".
• "Simple inserts": 能够提前知道将被插入的行的数量的语句.包括单行和多行的INSERT和REPLACE语句(不包含子查询)，不包括INSERT ... ON DUPLICATE KEY UPDATE.
• "bulk inserts":无法提前知道将被插入行的数量的语句.包括INSERT ... SELECT、REPLACE ... SELECT和LOAD DATA，不包括简单INSERT语句.在处理每一行时，InnoDB为AUTO_INCREMENT列分配一个新的值.
• "mixed-mode inserts":在"simple inserts"的语句中一部分(不是全部)指定了auto-increment的值.如下所示.

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

innodb_autoinc_lock_mode配置参数可选的配置为0、1、2，分别表示traditional、consecutive、interleaved.

• traditional锁模式:在这种模式下，所有的'INSERT-like'语句获取一个特殊的table-level AUTO-INC锁来进行带AUTO-INCREMENT列的插入.这个锁通常会持有到语句结束的时候(不是事务结束的时候)以保证以可预测和可重复的顺序为给定的insert语句序列分配自动增量值，同事也保证为语句分配的自动增量值是连续的.

TO BE CONTINUED...