mysql-myisam、innodb

对于myisam、innodb的区别主要存在于事务、外键、锁粒度：

  • myisam不支持事务、外键，更新时只能锁表
  • innodb支持事务、外键，更新时可以锁行

myisam写优先

myisam默认写优先（即读和写同时等待表锁时，锁释放时，myisam会先让写操作获得锁），可以通过set low_PRIORITY_UPDATES=1、指定启动参数low-priority-updates或指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性将写优先变为读优先（update  LOW_PRIORITY  table_name  set ......）。

myisam可以通过lock table手动锁表（jdbc如何操作呢？），注意，这里只是锁机制，并不是事务，也不存在提交、回滚之说。

1 lock table t1 read, t2 read; 
2 select count(t1.id1) as 'sum' from t1; 
3 select count(t2.id1) as 'sum' from t2; 
4 unlock tables;

表锁、行锁比较

表级锁：开销小，加锁块；不会出现死锁，锁定粒度大，发生锁冲突的概率高，并发度低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发性高。

myisam和innodb的查询效率

我们说myisam查询效率要比innodb高，为什么呢？

myisam索引的存储逻辑结构是一棵B-树,索引和数据分开存放，每个索引节点都会直接存放数据地址，这样的好处是遍历索引就可以直接定位数据。

innodb索引的存储逻辑结构是一棵B+树，但其索引有主键索引和辅助索引之分，主索引与数据一起存放，索引叶节点存放数据，通过主索引可以直接找到数据，但问题就在于查询走辅索引时，辅索引的叶节点只会存放主键，然后通过该主键从主索引中定位数据，索引查询在走辅索引时就会变慢。但是如果单纯走主索引，innodb就很快了。

还可以从锁的角度来看，myisam读取时加共享表级锁，加锁速度快，如果innodb在读取时也加锁，其会加行级锁，加锁速度慢，但如果innodb读取时不加锁，速度就很快了（由MVCC保证隔离性）。

不知道这样理解myisam查询效率比innodb高之说是否正确。

事务有ACID四大特性,分别是原子性、一致性、隔离性、持久性，数据库中的隔离级别说的就是隔离性。

隔离级别

mysql中隔离级别分为四种（这里说的是当前读）：

READ UNCOMMITTED，即读未提交，这是危险的，可能读到脏数据（即未提交的数据）；

READ COMMITTED，即读提交，可以保证所读数据都是提交过的，即保证所读到的数据不会因为回滚而成为脏数据（通过MVCC实现），可重复读（通过行锁实现），但存在幻读问题（即后面读到的数据与第一次读到的数据不一致）；

REPEATABLE READ，即可重复读，可以保证第一次（条件）读到的数据与后面（相同条件）读到的数据一致，不存在幻读问题（通过gap lock、MVCC实现）；

SERIALIZABLE，即可串行化，完全使用锁机制来替代MVCC(multiversion concurrency control)。

当前读可以保证读到的是最新的数据，快照读是不涉及加锁的，所以完全是MVCC在起作用，读到的可能是旧数据，但并发性更好。比如，read commited快照读，事务1进行query1查询操作，事务2更新query1结果集中相应行后提交，事务1再次query1就会发生不可重复读；mysql的repeatable read快照读的可重复读问题藏的比较深，事务1进行query1查询操作，事务2增加满足query1的where条件的数据行，此时事务1再次query1时可以得到与之前一致的结果，这是MVCC造成的，但如果此时更新新增数据行，再次query1就会发生幻读，如下。

    1. create table ab(a int primary key, b int);

    2. Tx1:
      begin; 
      select * from ab; // empty set

    3. Tx2: 
      begin; 
      insert into ab values(1,1);
      commit; 
    4. Tx1: 
      select * from ab; // empty set, expected phantom read missing. 
      update ab set b = 2 where a = 1; // 1 row affected. 
      select * from ab; // 1 row. phantom read here!!!! 
      commit;

事务回滚

在事务中对表进行更新操作时，由于可能产生回滚，所以必须对“旧数据”备份。mysql的数据项中除去我们显示定义的字段外，系统还会为该数据项额外添加3个字段：DATA_TRX_ID（事务id，自增）、DATA_ROLL_PTR（指向undo数据）、DELETED（删除标记位）。

建立test表举例说明

create table test (id int primary key, comment char(50)) engine=InnoDB;

create index test_idx on test(comment);

insert into test values(9, ‘aaa’);

现在test表中有一个数据项(9, 'aaa')，执行update操作

set autocommit=0;

update test set comment = ‘ccc’ where id = 9;

现在表中数据项的情况如下图所示，数据项已被修改，但为了回滚的需要，需要将“旧数据”备份起来（DATA_ROLL_PTR指向的应该是一个链表，而不单是一项，因为在对于读操作来说，可能会读取很旧的版本）。

MVCC

如果现在进行查询操作会怎样呢？

事务中的查询利用了更新过程中产生的备份数据，实现了MVCC。对于RC和RR读操作都会建立一个read view，read view中会包含一些列事务相关的字段，如下所示，这些字段共同决定了某数据项对于某事务读操作的可见性。

read_view->creator_trx_id = current-trx;                       当前的事务id
read_view->up_limit_id = trx1;                                      当前活跃事务的最小id
read_view->low_limit_id = trx7;                                     当前活跃事务的最大id
read_view->trx_ids = [trx1, trx3, trx5, trx7];                   当前活跃的事务的id列表
read_view->m_trx_ids = 4;                                            当前活跃的事务id列表长度

对于RC操作，在读前就会建立一个read view，即将读取的数据项的事务id为cur_id，如果cur_id<up_limit_id则说明更新该数据项的事务，早在当前事务开始前就已经提交，即可见，故可以读取；否则，如果cur_id不属于trx_ids则说明更新该数据项的事务，虽然开始于当前事务之后，但此刻也已经提交，即可见，故可以读取；否则，如果cur_id属于trx_ids则说明更新该数据项的事务，开始于当前事务之后，此刻还未提交，即不可见，故不可读取。

RC通过以上策略就可保证只读提交了的数据。

对于RR操作，首次读取时与RC操作类似，再次进行相同读取时，并不会重新创建一个read view，而是使用之前的read view，这样就可以保证可重复读。

由上分析可知，事务更新操作中的历史版本数据，不仅作用于回滚，而且还作用于“快照读”。

快照读、当前读

mysql中有“快照读”和“当前读”之分，“快照读”读的是可见数据，“当前读”读的是最新数据，MVCC是针对“快照读”而言的，其大大提高了mysql的并发性，当前读则使用的是锁机制，分为共享锁和排它锁。

  • 快照读：简单的select操作，属于快照读，不加锁。
    • select * from table where ?;
  • 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
    • select * from table where ? lock in share mode;
    • select * from table where ? for update;
    • insert into table values (…);
    • update table set ? where ?;
    • delete from table where ?;

为什么更新操作也被看做是当前读呢？

如下图所示，update操作，innodb首先会返回相关数据并加锁，server判断后决定是否update（server需要对存储引擎返回的数据进行筛选、确认），delete类似，insert操作会有一个Unique Key冲突检测，所以也需要读。

锁

“快照读”的MVCC前面已经分析过了，下面就来看“当前读”的锁机制。

mysql的锁是加载索引叶节点上的（主索引、辅助索引都是这样），但对于不同的隔离级别，具体的加锁机制会有所不同。

由于RC不需要保证可重复读，所以RC只需要保证使用锁后，对数据项达到互斥访问的目的即可（即数据项本身不被修改）。RC会对查询过程中找过的叶节点上锁，包括主索引和辅索引，由于有一些where条件并不能通过索引筛选，而需要将数据项返回给mysql server筛选，mysql这里有一个优化，当mysql server帅选通过该数据项后，索引的锁会被保持，如果筛选不通过，索引的锁就会被释放（“semi-consistent” read）。

RR需要保证可重复读，既要保证条件内数据项本身不被修改，也要保证条件内范围数据不会被修改，所以就引入了gap lock（有些情况可以不加gap lock）。

RC和RR锁机制举例

1 CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
2 INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
3 COMMIT;

session1

1 SET autocommit = 0;
2 UPDATE t SET b = 5 WHERE b = 3;

session2

1 SET autocommit = 0;
2 UPDATE t SET b = 4 WHERE b = 2;

RR

session1先执行

1 x-lock(1,2); retain x-lock
2 x-lock(2,3); update(2,3) to (2,5); retain x-lock
3 x-lock(3,2); retain x-lock
4 x-lock(4,3); update(4,3) to (4,5); retain x-lock
5 x-lock(5,2); retain x-lock

session2后执行，此时session1未提交

x-lock(1,2); block and wait for first UPDATE to commit or roll back

RC

session1先执行

1 x-lock(1,2); unlock(1,2)
2 x-lock(2,3); update(2,3) to (2,5); retain x-lock
3 x-lock(3,2); unlock(3,2)
4 x-lock(4,3); update(4,3) to (4,5); retain x-lock
5 x-lock(5,2); unlock(5,2)

session2后执行，此时session1未提交

1 x-lock(1,2); update(1,2) to (1,4); retain x-lock
2 x-lock(2,3); unlock(2,3)
3 x-lock(3,2); update(3,2) to (3,4); retain x-lock
4 x-lock(4,3); unlock(4,3)
5 x-lock(5,2); update(5,2) to (5,4); retain x-lock

参考：http://hedengcheng.com/?p=771

　　http://mahl1990.iteye.com/blog/2347029

　　https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

　　https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

　　https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/holoyong/p/7395988.html