MySQL事务，读写锁，行表锁及存储引擎 MyISAM 与 InnoDB

MySQL的存储引擎是MySQL体系架构中的重要组成部分，也是MySQL体系结构的核心，插件式的存储引擎更是它区别于其它数据库的重要特征。它处于MySQL体系架构中Server端底层，是底层物理结构的实现，用于将数据以各种不同的技术方式存储到文件或者内存中，不同的存储引擎具备不同的存储机制、索引技巧和锁定水平。常见的MySQL存储引擎有InnoDB、MyISAM、Memory、Archive等等，它们具备各自的特征，我们可以根据不同的具体应用来建立对应的存储引擎表。

在谈不同的存储引擎之前，我们需要先理解几个基本概念：

（1）  事务

   事务是一组原子性的SQL语句或者说是一个独立的工作单元，如果数据库引擎能够成功对数据库应用这组SQL语句，那么就执行，如果其中有任何一条语句因为崩溃或其它原因无法执行，那么所有的语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。

举个银行应用的典型例子：

   假设银行的数据库有两张表：支票表和储蓄表，现在某个客户A要从其支票账户转移2000元到其储蓄账户，那么至少需求三个步骤：

a.检查A的支票账户余额高于2000元；

b.从A的支票账户余额中减去2000元；

c.在A的储蓄账户余额中增加2000元。

    这三个步骤必须要打包在一个事务中，任何一个步骤失败，则必须要回滚所有的步骤，否则A作为银行的客户就可能要莫名损失2000元，就出问题了。这就是一个典型的事务，这个事务是不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，不可能只执行其中一部分，这也是事务的原子性特征。

 

（2）  读锁和写锁

   无论何时，只要有多个SQL需要同一时刻修改数据，都会产生并发控制的问题。

   假设一个公共邮箱，用户A正在读取邮箱，同时，用户B正在删除邮箱中的某个邮件，会产生什么结果呢？客户A可能读取时会报错退出，也可能会读取到不一致的邮箱数据。如果把邮箱当作数据库中的一张表，可见其存在同样的问题。

    解决这类经典问题的方法就是并发控制，即在处理并发读或者写时，可以通过实现一个由两种类型的锁组成的锁系统来解决问题。这两种锁就是共享锁和排他锁，也叫读锁和写锁。

     读锁是共享的，即相互不阻塞的，多个客户在同一时刻可以读取同一资源，互不干扰。写锁是排他的，即一个写锁会阻塞其它的写锁和读锁，只有这样，才能确保给定时间内，只有一个用户能执行写入，防止其它用户读取正在写入的同一资源。写锁优先级高于读锁。

 

（3）  行锁和表锁

    实际数据库系统中每时每刻都在发生锁定，锁也是有粒度的，提高共享资源并发行的方式就是让锁更有选择性，尽量只锁定需要修改的部分数据，而不是所有的资源，因此要进行精确的锁定。但是由于加锁也需要消耗资源，包括获得锁、检查锁是否解除、释放锁等，都会增加系统的开销。所谓的锁策略就是要在锁的开销和数据的安全性之间寻求平衡，这种平衡也会影响性能。  

    每种MySQL存储引擎都有自己的锁策略和锁粒度，最常用的两种重要的锁策略分别是表锁和行锁。

    表锁是开销最小的策略，会锁定整张表，用户对表做写操作时，要先获得写锁，这会阻塞其它用户对该表的所有读写操作。没有写锁时，其它读取的用户才能获得读锁，读锁之间是不相互阻塞的。行锁可以最大成都支持并发处理，但也带来了最大的锁开销，它只对指定的记录加锁，其它进程还是可以对同一表中的其它记录进行操作。表级锁速度快，但冲突多，行级锁冲突少，但速度慢。      

 

下面聊聊常用引擎：

MySQL两个最常用的存储引擎，MyISAM和InnoDB。

一、关于count(*)

知识点：MyISAM会直接存储总行数，InnoDB则不会，需要按行扫描。

    潜台词是，对于select count(*) from t; 如果数据量大，MyISAM会瞬间返回，而InnoDB则会一行行扫描。

    实践：数据量大的表，InnoDB不要轻易select count(*)，性能消耗极大。

    
常见坑：只有查询全表的总行数，MyISAM才会直接返回结果，当加了where条件后，两种存储引擎的处理方式类似。

例如：
t_user(uid, uname, age, sex);

uid PK
age index

select count(*) where age<18 and sex='F';

查询未成年少女个数，两种存储引擎的处理方式类似，都需要进行索引扫描。

启示：不管哪种存储引擎，都要建立好索引。

 

二、关于全文索引

知识点：MyISAM支持全文索引，InnoDB5.6之前不支持全文索引。

    实践：不管哪种存储引擎，在数据量大并发量大的情况下，都不应该使用数据库自带的全文索引，会导致小量            请求占用大量数据库资源，而要使用《索引外置》的架构设计方法。

    启示：大数据量+高并发量的业务场景，全文索引，MyISAM也不是最优之选。

 

三、关于事务

知识点：MyISAM不支持事务，InnoDB支持事务。

    实践：事务是选择InnoDB非常诱人的原因之一，它提供了commit，rollback，崩溃修复等能力。在系统异常崩溃时，MyISAM有一定几率造成文件损坏，这是非常烦的。但是，事务也非常耗性能，会影响吞吐量，建议只对一致性要求较高的业务使用复杂事务。

画外音：Can't open file 'XXX.MYI'. 碰到过么？

小技巧：MyISAM可以通过lock table表锁，来实现类似于事务的东西，但对数据库性能影响较大，强烈不推荐使用。

 

四、关于外键

知识点：MyISAM不支持外键，InnoDB支持外键。

实践：不管哪种存储引擎，在数据量大并发量大的情况下，都不应该使用外键，而建议由应用程序保证完整性。

 

五、关于行锁与表锁

知识点：MyISAM只支持表锁，InnoDB可以支持行锁。

分析：
MyISAM：执行读写SQL语句时，会对表加锁，所以数据量大，并发量高时，性能会急剧下降。

           InnoDB：细粒度行锁，在数据量大，并发量高时，性能比较优异。

实践：网上常常说，select+insert的业务用MyISAM，因为MyISAM在文件尾部顺序增加记录速度极快。楼主的        建议是，绝大部分业务是混合读写，只要数据量和并发量较大，一律使用InnoDB。

 

常见坑：
InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。
潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

画外音：Oracle的行锁实现机制不同。

例如：
t_user(uid, uname, age, sex) innodb;

uid PK
无其他索引

update t_user set age=10 where uid=1;

命中索引，行锁。

update t_user set age=10 where uid != 1;

未命中索引，表锁。

update t_user set age=10 where name='shenjian';

无索引，表锁。

启示：InnoDB务必建好索引，否则锁粒度较大，会影响并发。

 

Memory 与 Archive

Memory存储引擎

Memory存储引擎将表中数据放在内存中，因此速度非常快，但因其支持表锁，所以并发性能较差，最糟糕的是这个存储引擎在数据库重启或崩溃之后表中的数据将全部丢失，它只适用于存储临时数据的临时表，MySQL中一般使用这个存储引擎来存放查询的中间结果集，如MySQL自带的默认的information_schema库中就存在较多使用Memory存储引擎的表。

 

 

Archive存储引擎

   Archive存储引擎置只支持INSERT和SELECT操作，支持行锁，但本身并不是事务安全的存储引擎，其最大的优点是其具有较好的压缩比，压缩比一般可达到1:10，可以将同样的数据以更小的磁盘空间占用来存储。

   Archive存储引擎非常适合存储归档数据，如历史数据、日志信息数据等等，这类数据往往数据量非常大，并且基本只有INSERT和SELECT操作，使用这个存储引擎可以非常节约磁盘空间。

  以某个库里的有2.5亿条记录的历史表为例：

mysql> select TABLE_ROWSfrom TABLES where TABLE_NAME='history';

 

当使用Archive存储引擎重建上述表，并且重新插入同样的数据后，该表的大小变为少于2G，可见该存储引起具有很好的压缩比。

   

 

总结

在大数据量，高并发量的互联网业务场景下，对于MyISAM和InnoDB
有where条件，count(*)两个存储引擎性能差不多
不要使用全文索引，应当使用《索引外置》的设计方案
事务影响性能，强一致性要求才使用事务
不用外键，由应用程序来保证完整性
不命中索引，InnoDB也不能用行锁

结论
在大数据量，高并发量的互联网业务场景下，请使用InnoDB:
行锁，对提高并发帮助很大
事务，对数据一致性帮助很大
这两个点，是InnoDB最吸引人的地方。