MySQL数据库引擎、事务隔离级别、锁

• 数据库引擎InnoDB和MyISAM有什么区别

  • 大体区别为： MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行效率比InnoDB类型更快，但是不支持事务，而InnoDB提供事务支持以及外键等高级数据库功能。

  • 具体实现的区别：

    1. InnoDB不支持FULLTEXT类型的索引

    2. InnoDB中不保存表的具体行数，也就是说，执行查询SQL时，InnoDB要扫描一遍整个表来计算有多少行，而MyISAM只要简单的读出保存好的行数即可，但是当包含where条件时，两种表的操作是一样的

    3. 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他的字段建立联合索引

    4. 执行删除SQL时，InnoDB不会重新建立表，而是一行一行的删除

    5. LOAD TABLE FROM MASTER操作对InnoDB是不起作用的。解决方法是先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用额外的InnoDB特性（例如外键）的表不适用

  • 构成上的区别：

    1. 每个MyISAM在磁盘上存储分为三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型

       • .frm文件存储表定义

       • .MYD文件为数据文件

       • .MYI文件为索引文件

    2. 基于磁盘的资源是InnoDB表空间的数据文件和它的日志文件, InnoDB的表大小只受限于操作系统文件的大小，一般为2G

    • 事物处理上的区别：

      1. InnoDB支持事物，MyISAM不支持事物。对于InnoDB每一条SQL语句都默认封装成事物，自动提交，这样会影响速度，所以最好把多条SQL语句放在begin和commit之间，组成一个事物。

      2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转换为MyISAM会失败。

      3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询主键，然后在通过主键查询到数据，因此，主键不应该过大，因为主键太大，其他索引也会很大。而MyISAM是非聚焦索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

      4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。

      5. InnoDB不支持全文索引，而MyISAM不支持全文索引，查询效率上MyISAM要高

      6. MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不支持事物。InnoDB支持事物，外部键等高级数据库功能

      7. 如果执行大量的查询select操作，MyISAM是更好的选择

      8. 如果执行大量的insert或者update操作，出于性能方面的考虑，应该使用InnoDB引擎

      9. 执行删除数据操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据执行操作后再改成InnoDB表，但是对于使用额外的InnoDB特性（如外键）的表不适应

    • 对AUTO_INCREMENT的操作

      1. MyISAM为Insert和update操作自动更新，这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。（当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重复使用从序列顶部删除的值的情况）

      2. AUTO_INCREMENT的值可以用ALTER或myisamch来重置

      3. 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他的字段一起建立联合索引

    • mysql中的锁：

      1. 锁是计算机协调多个进程或线程对某一资源并发访问的机制。

      2. Mysql中的锁分为表锁和行锁：顾名思义，表锁就是锁住一张表，而行锁就是锁住一行。

      3. 表锁的特点：开销小，不会产生死锁，发生锁冲突的概率高，并且并发度低。

         行锁的特点：开销大，会产生死锁，发生锁冲突的概率低，并发度高。

      4. 因此MyISAM引擎采用的是表锁，而InnoDB存储引擎采用的是行锁。

  • 如何选择数据库引擎

    • 业务是否需要支持事物，如果需要选择InnoDB，如果不需要可以考虑MyISAM

    • 如果表中绝大多数都只是查询操作，可以考虑MyISAM，如果读写操作频繁，则使用InnoDB

    • 需要考虑系统崩溃后，MyISAM恢复起来更困难，能否接受

    • MySQL5.5版本开始InnoDB已经成为MySQL的默认引擎（之前是MyISAM）

  • MySQL字段宽度

    • MySQL类型关键字后面的括号内指定整数值的显示宽度(例如，INT(11))。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。所以INT(1)和INT(11)默认是没有任何区别的！！！

    • 当结合可选扩展属性ZEROFILL使用时， 默认补充的空格用零代替。例如，对于声明为INT(5) ZEROFILL的列，值4检索为00004。 请注意如果在整数列保存超过显示宽度的一个值，当MySQL为复杂联接生成临时表时会遇到问题，因为在这些情况下MySQL相信数据适合原列宽度。

    • 所有整数类型可以有一个可选(非标准)属性UNSIGNED。当你想要在列内只允许非负数和该列需要较大的上限数值范围时可以使用无符号值 。 如果设置了ZEROFILL扩展属性试，默认就有了无符号属性(UNSIGNED)

    • 所以INT(1)与INT(11)后的括号中的字符表示显示宽度，整数列的显示宽度与MySQL需要用多少个字符来显示该列数值，与该整数需要的存储空间的大小都没有关系，INT类型的字段能存储的数据上限还是2147483647(有符号型)和4294967295(无符号型)。其实当我们在选择使用INT的类型的时候，不论是INT(1)还是INT(11)，它在数据库里面存储的都是4个字节的长度。

    • INT(M) ZEROFILL，加上ZEROFILL后M才表现出不同，比如 INT(3) ZEROFILL,你插入到数据库里的是10,则实际插入为010,也就是在前面补充加了一个0.如果INT(3)和INT(10)不加ZEROFILL,则它们没有什么区别．Ｍ不是用来限制INT列内保存值的范围的．int(M)的最大值和最小值与UNSIGNED有关。

  • 总体来说，两种类型最主要的区别就是InnoDB支持事物处理与外键和行级锁。而MyISAM不支持。所以MyISAM往往会被认为只适合在小项目中使用，而如果就方便性和高扩展性来说，MyISAM绝对是首选。原因如下：

    1、平台上承载的大部分项目是读多写少的项目，而MyISAM的读性能是比Innodb强不少的。

  　　2、MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

  　　3、经常隔1，2个月就会发生应用开发人员不小心update一个表where写的范围不对，导致这个表没法正常用了，这个时候MyISAM的优越性就体现出来了，随便从当天拷贝的压缩包取出对应表的文件，随便放到一个数据库目录下，然后dump成sql再导回到主库，并把对应的binlog补上。如果是Innodb，恐怕不可能有这么快速度，别和我说让Innodb定期用导出xxx.sql机制备份，因为最小的一个数据库实例的数据量基本都是几十G大小。

  　　4、从接触的应用逻辑来说，select count(*) 和order by 是最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的都会锁全表的。

  　　5、还有就是经常有很多应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的frm.MYD,MYI的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

  　　6、如果和MyISAM比insert写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，虽然MyISAM可能会逊色Innodb,但是那么高并发的写，从库能否追的上也是一个问题，还不如通过多实例分库分表架构来解决。

  　　7、如果是用MyISAM的话，merge引擎可以大大加快应用部门的开发速度，他们只要对这个merge表做一些select count(*)操作，非常适合大项目总量约几亿的rows某一类型(如日志，调查统计)的业务表。

  　　8、 当然Innodb也不是绝对不用，用事务的项目就用Innodb的。另外，可能有人会说你MyISAM无法抗太多写操作，但是可以通过架构来弥补。

• 事物有哪几个特性：

  1. 原子性

  2. 一致性

  3. 隔离性

  4. 持久性

• InnoDB中的事务隔离级别和锁的关系

  • 一次*&&两段锁

    • 一次*： 因为有大量的并发访问，为了预防死锁，一般应用中推荐使用一次*法。就是在方法的开始阶段，已经预先知道了会用到哪些数据，然后全部锁住，在方法运行之后，再全部解锁。这种方式可以有效的避免循环死锁。但是这种方式在数据库中却并不适用，因为在事务开始阶段，数据库并不知道会用到哪些数据。

    • 两段锁

      数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段

      虽然这种方式无法避免死锁，但是两段锁协议可以保证事务的并发调度是串行化的（串行化很重要，尤其是在数据库恢复和备份的时候）

      • 加锁阶段： 在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁），其他事务可以继续加共享锁，但不能加排它锁。在进行写操作之前要申请并获得X锁（排它锁），其他事务不能再获得任何锁。如果锁不成功，则事务进入等待状态，直到加锁成才能继续执行

      • 解锁阶段： 当事务释放了一个*之后，事务进入解锁极端，在该阶段只能进行解锁不能再进行其他加锁操作

  • 事务的四种隔离级别

    在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。数据库锁也是为了构建这些级别存在的。

    • 未提交读 ： 允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

    • 提交读 ： 只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别

    • 可重复读 ： 可重复读取数据。在同一个事务内的查询都是事务开始时保持一致的。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻读

    • 串行读 ： 完全串行化的读取数据。每次读都需要获得表级共享锁，读写相互都会堵

  • MySQL中的锁

    1. MySQL中锁的种类有很多，有常见的表锁和行锁，也有新加入的Metadata Lock等等。

    2. 表锁是对一整张表加锁，虽然可分为读锁和写锁，但毕竟是锁住整张表，会导致并发能力下降，一般是做DDL处理时使用

    3. 行锁是锁住数据行，这种加速方式比较复杂，但是由于只锁住有限的数据，对于其他数据不加锁，所以并发能力强，MySQL一般都是用行锁来处理并发事务，行锁可以防止不同事务版本额数据修改提交时造成的数据冲突的情况

    4. 在RC（Read Committed => 读取提交内容）级别中，数据的读取都是不加锁的，但是数据的写入、修改、删除是需要加锁的

    5. 由于MySQL的InnoDB默认是使用RR级别，所以需要先将该session开启成RC级别，并且设置binlog的模式

    6. 如果一个条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由MySQL Server层进行过滤

    7. 但在实际使用过程中，MySQL做了一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录释放锁（违背了二段协议的约束）。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略。这种情况同样适用于MySQL的默认隔离级别RR。所以对一个数据量很大的表做批量修改的时候，如果无法使用相应的索引，MySQL Server过滤数据的时候特别慢，就会出现虽然没有修改某些行的数据，但是它们还是被锁住了的现象。

  • 不可重读和幻读的区别：

    1. Repeattable Read（可重读），这是MySQL中InnoDB默认的隔离级别。可重读这个概念是一事务的多个实例在并发读取数据时，会看到同样的数据行。在MySQL的RR级别中，解决了幻读的问题

    2. 不可重读重点在于update和delete，而幻读的重点在于insert

    3. 如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其他事务无法读取这些数据，就可以实现可重复读了。但这种方法无法锁住insert数据，所以当事务A先前读取了数据或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效避免幻读、不可重复读、脏读等问题，但是会极大的降低数据库的并发能力。

    4. 不可重复读和幻读的最大区别，就在于如何通过锁机制来解决他们产生的问题。可以使用悲观锁机制来处理这两种问题，但是MySQL、Oracle、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了乐观锁为理论基础的MVCC(多版本并发控制)来避免这两种问题

  • 乐观锁和悲观锁

    • 悲观锁 ==> 它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态

      1. 悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

      2. 在悲观锁的情况下，为了你保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改数据时也要加锁，其它事务无法读取这些数据。

      3. 悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独立性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受

    • 乐观锁 ==> 相对于悲观锁，乐观锁机制采取了更加宽松的加锁机制。乐观锁，大多数是基于数据版本（为数据增加一个版本标识）记录机制实现，再基于数据库表的版本解决方案中，一般是通过为数据库表在哪个家一个version字段来实现。读取数据时，将此版本一同读出，之后更新时，对此版本加一。此时，将提交数据的版本数据与数据库表对应的当前版本信息进行对比，如果提交数据的版本号大于数据库表当前版本，则予以更新，否则认为是过期数据

  • MVCC在MySQL的InooDB的实现

    1. MVCC的实现没有固定的规范，每个数据库都会有不同的实现方式

    2. 在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。在实际操作中，存储的并不是时间，而是事务的版本号，每次开启一个新事务，事务的版本号就会递增。在可重读Repeatable reds事务隔离级别下：

       • select时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。

       • insert时，保存当前事务版本号为行的创建版本号

       • delete时，保存当前事务版本号为行的删除版本号

       • update时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

    3. 通过MVCC，虽然每行记录都需要额外的存储空间，更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多数操作都不用加锁，读取数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，也只锁住必要行

  • MySQL中的“读”与事务隔离级别中的“读”的区别

    1. 在RR级别中，虽然让数据变得可重复读，但是我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就可能出现问题。对于这种读取历史数据的方式，叫做快照读，而在读取数据库当前版本数据的方式，叫做当前读。显然，在MVCC中，快照读就是select，当前读是特殊的读操作，insert/update/delete操作，属于当前读，处理的都是当前度的数据，需要加锁

    2. 事务的隔离级别实际上都是定义了当前读的级别，MySQL为了减少锁的处理（包括等待其他锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁，而update、insert、delete这些“当前读”，就需要另外的模块来解决

    3. 当前读： 事务的隔离级别中虽然定义了读数据的要求，实际上这也可以说是写数据的要求。为了解决当前读中的幻读问题，MySQL事务使用了Next-Key锁

    4. Next-key锁是行锁和GAP（间隙锁）的合并，行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的Next-Key锁共同解决了RR级别在写数据时的幻读问题

    5. Serializable级别： 读操作加共享锁，写操作加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。如果业务并发的特别少或者没有并发，同时数据及时可靠，可以使用这种模式，在Serializable这个级别，select还是会加锁的