MySQL常用存储引擎之Innodb

摘要：所以我们如果使用系统表空间进行表数据的存储，所面临的问题是无法很容易的收缩系统文件，造成大量的空间浪费，并且会产生大量的磁盘碎片，从而降低了系统性能。

  为不了解存储引擎的数据库使用者，提供了很大的便利，因为innodb适应大部分应用场景。

  和myisam不同的是，innodb是一种事务型存储引擎。也就是说，innodb是支持事务的acid特性的。innodb的设计，更适合大量的小事务，而小事务大部分情况下， 都可以正常提交，很少会被回滚。

  innodb和mysiam存储数据方式不同，innodb有自己的表空间的概念。表中的数据是存储在表空间之中的，具体存储在什么样的表空间中了，则由 innodb_file_per_table 这个参数来决定。

ON： 为每个innodb表建立一个空间，tablename.ibd（.ibd后缀）

OFF：则会把数据存储到系统的共享表空间，ibdatax(x代表一个数字,从1开始)

  在mysql5.6之前的innodb参数-innodb_file_per_table = OFF （默认）,也就是数据默认会存储到系统表空间中。

  默认的设置会遇到下面的问题：在一个繁忙的系统中，系统表空间不断的增长。没超过我们磁盘限制是可以接受的，但是一旦我们磁盘空间出现不足，我们为了释放磁盘空间，不得不在系统中删除大量无效的数据（如：长期不使用的日志类数据等）。我们在删除数据后，系统表空间并不会缩小。这种情况下，我们想通过复制文件的方式，对数据库进行备份。由于虽然删除了数据，但表空间大小并不会改变，这就意味着，我们每次删除时，都要浪费很大的空间。

  你不要以为我们不会遇到备份的问题，实际上，目前我们最常用的热备方式，就是这样处理的。这时我们就会遇到使用系统表空间进行存储的一些问题了。

  想要收缩系统表空间的唯一方法就是把系统表空间的表导出后，删除innodb的文件后，在重启mysql服务器，进行表空间的重建，然后在导入数据。这个过程其实是很复杂的，并且十分耗时，这在业务繁忙的生产环境中了，显然是不可能做到的。

  所以我们如果使用系统表空间进行表数据的存储，所面临的问题是无法很容易的收缩系统文件，造成大量的空间浪费，并且会产生大量的磁盘碎片，从而降低了系统性能。

  如果我们使用独立表空间， 对一个大表的数据进行清理之后，可以方便的对这个表进行收缩，使用optimize table命令 ，会对表进行重建。但是对比整个系统进行重建要快的多，而且不需要重启数据库服务器，甚至不会影响这个表的正常访问。所以从这点来看，使用独立的表空间显然要比使用系统表空间要好得多。

  对于系统个表空来说，由于只有一个文件。如果同时对多个表进行数据刷新时，实际上在文件系统层面上来说是顺序进行的，所以会产生一定的io瓶劲。

  对于独立表空间来说，由于每个表都有自己的表空间文件。在进行数据写入时，可以利用多个文件增加io处理的性能。所以对于平凡写入操作的系统来说，不太适合使用系统表空间统一存放数据，而是要使用独立表空间的方式。

  对使用innodb存储引擎的表使用独立表空间进行管理，在mysql5.6后，使用innodb存储引擎的默认表配置就是独立存储空间。

把原来存在于系统表空间中的表转移到独立表空间中的方法

  如果数据库中使用存储过程，触发器，计划事件等，一定要记得一起导出。

  停止mysql服务，如果是主从架构的话，我们可以先从-从服务器上进行这些操作。停止mysql服务器之后，我们需要修改my.cnf文件，并加入innodb_file_per_table这个参数，然后手动删除原来innodb系统中的相关数据文件，这时后系统表空间就不含有任何数据了。

  实际上，由于我们之前已经备份了所有数据表，所以这里可以重建一个data目录，并使用mysql install db脚本来重建数据库目录

  重新导入之前备份的数据，就可以完成数据的恢复，并且把原来存在于系统表空间的数据迁移到独立表空间中了。

  我们也可不按照上面操作，在修改完系统参数后，重启服务后将需要修改的所有innodb表都执行一遍：alter table table_name engine=innodb;.这样也可以把系统表空间的表迁移到独立表空间中，但是却无法对系统表空间所占用的空间进行回收，所以还是按照上面正规步骤吧。

我们把系统表空间数据迁移到独立表空间后，那么现在系统表空间还有什么内容了？

  答案是虽然我们已经把系统表数据迁移了出来，但是对于系统表空间来说，还是有一些很重要的东西要存储的。这其中之一就是 innodb数据字典信息

  数据字典是数据对象结构的元数据信息，存放与数据库对象相关的信息。如：表，列，索引，内键等内容。

mysql是使用 .frm文件存储表结构定义的，那么.frm文件和系统空间中的数据字典又有什么区别了？

  首先，.frm文件是mysql服务器层产生的文件，可以理解为mysql数据库服务器层的数据字典，这对mysql所有存储引擎都是一样的。mysql数据层所保留的东西是与存储引擎无关的，而innodb内部的数据字典是存储引擎内部产生的，并可以保证事务的一些安全性。另外，innodb存储引擎没有直接使用mysql数据上的一些类型，而是自己封装了定义，因此其数据字典存储的都是引擎相关的内容。最后，.frm文件只是简单的二进制文件，而innodb数据字典是通过b树来进行数据管理的

除了innodb数据字典外，系统表空间还存在undo回滚段和innodb临时表

这两种在mysql5.7都是可以从系统表移除的 了，但还会有很多人默认的把他们存储在系统表空间中。这里要声明的是，undo回滚段的存储在mysql5.6就已经支持了。

  完全支持事物的acid特性

redo log 和 undo log

redo log实现事物的持久性，其有两部分组成

innodb_log_buffer_size：配置redo log缓冲区的大小，以字节为单位，程序每隔秒就会把缓存区数据刷新到磁盘里，所以这个缓存区不用配置太大。

innodb_log_files_in_group：决定了数据库目录下的ib_**的文件数量

undo log 帮助对未提交事物进行回滚和实现多版本并发控制

联系

redo log存储的是已提交的事物，undo log存储的是未提交的事物。

对innodb的表进行修改时，不仅会产生redo log 还会产生 undo log

用户事物失败或使用回滚语句，就需要利用undo log中的信息了。

redo log顺序写入，在数据库允许时，不需要对redo log进行读取操作。undo log 需要进行随机读写的，mysql5.6中的undo log可以独立于系统的空间而存在。如果条件允许，我们就可以把undo log存储到固态存储设备上，这样可以获得更好的性能。

  同myisam支持的表级锁是不一样的，行级锁的特点是在进行写操作时，我们所需要锁定的资源更少，这样可以支持的并发就更多。需要注意的是，innodb的行级锁是由存储引擎层实现的，mysql服务器完全不了解存储引擎中锁的实现方式。

  说到锁可能大家还不太了解，数据库中锁的作用是什么了？因为锁对我们数据库的性能有非常大的影响，所以这里介绍下什么是锁。

  可能很多开发人员会问，经常听到dba说的各种各样的锁，比如说行级锁，表级锁，共享锁，独断锁等等。这些锁的作用是啥了？

  锁是数据库系统区别于文件系统的重要特性锁的主要作用是管理共享资源的并发访问。并发访问是一个很让人头疼的事情啊，对于任何一个在窜行环境下工作良好的系统，一旦涉及到并发的情况就有可能出现各种各样的问题。
  比如说邮件系统，在一个邮箱中，所有的邮件都是窜行的叠在一起的，彼此首尾相连。这种结构对于读取和投递邮件都是有很大的好处的。当有新的邮件到来时，只要插入文件末尾就可以了。当有两个用户，同时对邮箱进行邮件投递，那会出现什么样的情况了？很有可能邮箱的数据会被破坏，两封性的内容会交叉在一起。当然了，这可能和我们平常生活的感受有很大的不同。那是因为现在的邮箱系统都是利用了锁进行了控制。锁保证了一个用户向邮箱投递邮件时，另一个用户会阻塞，无法向相同的文件末尾写入文件。
  那么锁的另一个作用就是实现事务的隔离性。前面提到过，我们通过redo log 和 undo log 实现了事务的原子性，一致性和持久性，而隔离性就需要锁来实现。对于未提交的事务锁定的数据是无法被其他事务查询到的。

了解了什么是锁，及锁的作用后，我们来看看锁的常见分类。

共享锁（也称读锁）：可以看出读锁是共享的，不会被阻塞的， 多个线程可以在同一时间读取同一资源，而不相互干扰。
独占锁（也称写锁）：写锁是独占的，是排它的，会阻塞其他的写锁和读锁。这是对数据完整性的考虑，只有这样才能保证在给定的时间里，只有一个线程可以执行写入，并防止其他用户正在读取写入的资源，也就是我们前面说的事务的隔离性。
  对于innodb来说，读锁和写锁都是行锁。兼容性指的是对同一行记录可见兼容的情况

  按照上面介绍，对于同一资源的请求应该是互斥的，但是实际体验并不和以上相同。（锁比上面提到的复杂多了）

初始化数据库中数据

测试，两个连接启动一个事务。一个连接给表的第一行记录加个独占锁，不提交事务

另一个连接获取加了独占锁的数据，仍然可以获取到

这和我们介绍的兼容性情况完全不同，这是为什么了？

  这就是innodb在实现锁上的独到之处，这里innodb用到了我们上面提到的undo log中的记录，所以我们在第二个连接中查看的数据实际上是存储在undo log中的版本。也就是说，并不是我们在第一个连接中进行更新的。

  锁的粒度就是锁的策略，指被加锁资源的最小单位。比如在行上加锁，那么最小单位就是行，这锁就称为行级锁。如果说的最小单位是列，那就称为锁的列级锁。同理，如果锁的最小单位是表的话，那么锁的最小单位就是表级锁。

表级锁：mysql中最基本的表策略，开销最小的策略，并发性低。表锁会在加锁时锁定整张表，一个用户在对表进行写操作前，需要先获得写锁。这会阻塞其他用户的读写操作，只有没有写锁时，其他用户才可以获取读锁，读锁之前说了是相互间不会阻塞的。表级锁通常是在mysql服务器层实现的，所以虽然innodb实现了行级锁，但是在一些时候，mysql数据服务层还是会对innodb表加上表级锁。

实验使用两个连接，一个连接加表级独占锁

第二个连接从表获取数据

第一个连接执行解锁操作，第二个连接就会被执行

行级锁：可以最大程度的支持并发处理，同时锁的开销比表级锁要大。目前innodb和其他的存储引擎中实现了行级锁，行级锁只在存储引擎中进行实现，而mysql服务层并没有实现。

什么是阻塞：阻塞是不同锁之间兼容性的关系。在有些时刻，一个事务中的锁需要等待另一事务中的锁释放它所占用的资源，这就形成了阻塞。阻塞是为了确保事务可以并发且正常的运行， 但是，当一个系统中如果出现了大量的阻塞，往往就意味着系统中存在着问题。也许是在一个被频繁更新的表上出现了慢查询或是由于其他的管理操作，如表备份等。对一个频繁访问的资源加上了排他锁，过多的阻塞不是个好的现象，使数据库中的连接大量的堆积，占用大量的系统资源，使得系统的总体性能下降。

什么是死锁：死锁是指两个或两个以上的事务，在执行过程中相互占用了对方等待的资源 ，而产生的一种异常。处在阻塞中的事务占有被阻塞事务的多个资源，而死锁是产生事务的多个事务之间相互占用对方等待的资源，这就是阻塞和死锁最大的不同之处。另外一点不同是，死锁数据库系统会自动发现，并且在多个资源占用的事务中，选择一个资源占用最少的事务来进行回滚操作。这样就可以使其他事务正常的运行了，所以说死锁是可以由系统自动处理的。如果只是有少量的死锁，并不会对系统造成什么影响。只要在应用程序中，发现死锁，并进行重新处理就可以了。但是，如果一个系统中频繁的出现大量的死锁，这时就需要留意了。通常情况下，死锁可以通过在多个事务中按相同的顺序访问所需要的资源来解决，也可通过增加相关的索引来解决。

show engine innodb status 这命令的用法百度

  innodb适合于大多数OLTP应用。无论是否需要事务支持，只要是不使用innodb所不支持的特殊的功能的话，我们都应该在新的系统中使用innodb存储引擎。其中所说的特殊功能可能就包括之前介绍myisam功能中对于空间应用和全文索引这些应用。由于在mysql5.7之前,只支持myisam存储引擎，所以如果我们要使用mysql来存储这类应用数据的话，可能就只能选择myisam存储引擎了。可是这种情况在mysql5.7之后有了改变，innodb支持全文索引和空间函数了。 所以对于这类应用，我们也可以完全使用innodb存储引擎来进行存储了。innodb和myisam是mysql最常用的两种存储引擎，还有一些引擎会使用到，请听下回分解。

InnoDB 引擎独立表空间 innodb_file_per_table
MySQL Server参数优化 - innodb_file_per_table（独立表空间）
show engine innodb status解读
死锁和阻塞的关系