mysql知识点大全

记录mysql实际开发中密切相关的技术点

---

mysql引擎

一、 innodb和myisam

项目中运用最多的就是这两个，差别很大，项目中运用最多的也是innodb。
原因：innodb支持事务+行锁。myisam表锁。锁的不同对于并发的影响很大。现在的项目一般都需要支持并发的操作。

mysql innodb 锁

一、锁存在的意义

理解锁之前，需要了解事务的概念以及并发的概念。
即锁是为了解决什么问题而产生的——数据之间的相互影响。
mysql隔离级别：

1、未提交读（脏读）：读到了其他事务未提交的数据。（理论级别，不可实际应用）
2、提交读（不可重复读）：即两次读取的结果不一致。（因为事务之间的操作互相影响了）
3、可重复读（幻读）默认隔离级别。（重点说明）
4、串行化。（没啥好说的，一个个来，当然不会互相影响）
为什么有这4种隔离级别：是在数据安全性和程序并发性两个方面给用户自己选择的机会。

接下去的讨论都是在从幻读的级别进行讨论。

1、可重复读级别理解：

这里可以重复读的前提条件是指其他事务对数据进行了update操作，可以重复读(通过mvcc解决)。但是如果insert/ delete 如果没有其他的操作，还是会造成幻读。下面mysql有办法解决这个问题。

mvcc: 多版本并发控制，存储两个字段，一个创建时的事务版本，一个删除的事务版本。
在可重复读隔离级别下，定义了select的规则，只会读到<=事务版本的数据。解决了幻读。
网上也有叫直接select叫做快照读。

说到幻读：说下当前读（update delete insert 都属于），mvcc解决不了当前读的幻读。
需要通过next-key去解决。

既然这样，都能解决幻读了，为什么说这个隔离界别还有幻读呢？
原因是一些很不常见的操作。

a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作），a事务再select出来的结果在MVCC下还和第一次select一样，接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的），a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了，实测在RR级别下确实如此。

二、锁分类

只要是insert update delete 操作默认会加锁，至于加什么锁需要看索引的值。insert比较特殊，只会加当前记录的锁，专业说法叫意向插入锁。
select默认不会加锁。

有不同的区分方法：这里就按照粒度区分。
表锁：意向锁（意向排他锁和意向共享锁）、主键锁（很明显插入主键的时候会锁表，否则会导致主键重复）。
行锁：间隙锁gap锁、next-key 临键锁、记录锁、意向插入锁，都属于行锁。其中 gap/next是为了解决幻读而产生的。

意向锁是mysql自动加的，用户无法操作。它的作用白话理解就是告诉mysql当前数据要加的锁类型。便于其他想加锁的事务判断，是否可以加锁。

2.1 锁之间的互斥（重要）

理解这个很重要，因为这样你就能知道代码中哪些sql是会有锁的，当其他人请求的时候是否会导致数据的污染。互斥指的是当这个数据有某一种锁，如果是互斥的话，其他请求只能阻塞。
兼容：意向锁之间，意向共享锁和共享锁。
互斥：其他的都是互斥。

2.2 如何代码中加锁

排他锁：for update。
共享锁：lock in share mode。

三、锁和索引的关系

索引的影响只是说保证了锁的范围，如果是唯一索引，那么就产生了行锁。如果是非唯一索引（一个key会有多个值），这也是行锁，但是是间隙数和临键锁。如果不是索引，那么会锁表。

四、锁和事务的关系

锁的释放是事务提交的时候。如果没有在事务中，那么也就是那条sql执行的时候会锁住（如果有锁的话）。

五、乐观锁和悲观锁

1、上面说的那些都是悲观锁，乐观锁是需要手动实现的。简单理解就是更新的时候where条件加一个其他字段，比如时间戳或者version，每次更新的时候都会更新这个值。这样如果并发发生的时候，该字段肯定会变，那么并发中的其他的就会不成功。这样做可以减少锁的等待，并发性更好，还能保证数据的正确性。

六、锁优化

1、特别是直接锁表的情况，更加糟糕。所以insert或者update的时候要尽可能走唯一索引。
2、

mysql索引

---

总结

1、锁总结

从实际运用入手，代码中都是增删改查，还有事务的情况，如何更好的利用锁的特点以提高并发的能力还有待去实际运用中提高。代码中运用的比较多的还是for update，可以考虑乐观锁去实现。