MySQL学习笔记（一）-事务相关

1、事务基本概念： 事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。
2、事务四个特性 A（原子性），C（一致性）、I（隔离性）、D（持久性）

（1）原子性： 一个事务需被认为一个不可分割的最小执行单位，要么全部成功完成，要么全部失败回滚；
（2）一致性： 数据库必须从一个一致性状态转移到另一个一致性状态；
（3）隔离性： 一个事务的执行不能被其他事务干扰；
（4）持久性： 一个事务一旦提交，其对数据库的数据的改变是永久性的。

3、并发控制中事务可能出现的问题

• （1）丢失修改： 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失

• （2）不可重复读： 是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。
• （3）脏读： 是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。
• （4）幻读： 是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样.

解决方案： 要想解决脏读、不可重复读、幻读等读现象，那么就需要提高事务的隔离级别。但与此同时，事务的隔离级别越高，并发能力也就越低。所以，还需要读者根据业务需要进行权衡。

4、事务的隔离性

在SQL标准中提供了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以提供更高的并发，系统的开销也更低。

• (1)READ UNCOMMITTED（未提交读）:

在该级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读。一般很少使用该隔离级别。

• (2)READ COMMITTED（提交读）

在该级别，一个事务从开始到提交之前，所作的任何修改对其他事务都是不可见的。其也称为不可重复读（NONREPEATABLE READ），因为在同一个事务中执行两次同样的查询，可能会得到不一样的结果。

• (3)REPEATABLE READ（可重复读）

读取了一条数据，这个事务不结束，别的事务就不可以改这条记录，这样就解决了脏读、不可重复读的问题，但是幻读的问题还是无法解决

• （4）SERIALIZABLE（可串行化）

该级别是最高的隔离级别，它通过强制事务串行执行，避免了幻读问题。SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用，实际中很少使用该级别。

（5）多版本并发控制（MVCC）（InnoDB特有）

MVCC可以认为是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。不同的数据库系统实现MVCC的机制都不同，但是大都实现了非阻塞的读操作，写操作也只锁定必要的行。
MVCC的实现，是通过保存数据在某个时间的快照来实现的，也就是说不管需要执行多长的时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。MVCC主要有两种实现机制：乐观并发控制和悲观并发控制。

MVCC具体的操作：

（1） SELECT:

a、InnoDB只查找版本号早于当前事务的数据行（也就是说，行的系统版本号小于或等于事务的系统版本号），这样确保了事务读取的行，要么在事务开始之前就已经存在，要么就是事务自身插入或者修改的数据（这就解决了幻读问题）。

b、行的删除版本（也就是过期时间），要么未定义，要么大于当前事务版本号。这样可以保证事务读取的行在事务开始之前没有被删除。

（2）INSERT:

InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

（3）DELETE:

InnoDB为删除的每一行保存当前系统版本号作为行删除版本号。

（4）UPDATE:

InnoDB为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除版本号。

这样做可以使得大多数读操作都可以不用加锁，读操作性能很好，并且保证只会读到符合标准的行。缺点就是，需要额外的存储空间，做更多的行检查，以及额外的维护工作。

5、常用锁
通常分为两种锁，一是共享锁（读锁），一种是排他锁（写锁）。读锁是共享的，或者说是互相不阻塞的。多个客户在同一时刻可以同时读取同一个资源而互不干扰。写锁则是排他的，也就是说写锁会阻塞其他的读锁和写锁。

MySQL中主要有两种锁：表锁（table lock）和行级锁（row lock）

表锁： 是MySQL中最基本的锁策略，并且是开销最小的策略。表锁会锁定整张表。一个用户在对表进行写操作（插入、删除、更新）之前，必须先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有在没有写锁的情况下，其它读取的用户才能获得读锁，读锁之间是不会互相阻塞的。此外，写锁的优先级是高于读锁的，因此一个写锁请求可能会插入到读锁队列的前面（读锁不可能插入到写锁的前面）。

行级锁： 行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。行级锁只锁定单独的一行，这样其他的用户就可以正常访问其他的行，提升了并发度。

区别：

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。
表级锁： 开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。
行级锁： 开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

6、MySQL引擎的区别

1、InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

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

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

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

5、InnoDB 最小的锁粒度是行锁，MyISAM
最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从MyISAM 变成 InnoDB 的重要原因之一；

InnoDB为什么推荐使用自增ID作为主键？

• 答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

innodb引擎的4大特性

- 插入缓冲（insert buffer)
对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入，若不在，则先放入到一个Insert Buffer对象中。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多个插入合并到一个操作中，大大提高了对于非聚集索引插入的性能。
对于非聚集索引来说，比如存在用户购买金额这样一个字段，索引是普通索引，每个用户的购买的金额不相同的概率比较大，这样导致可能出现购买记录在数据在数据里的排序可能是1000，3，499，35…，这种不连续的数据，一会插入这个数据页，一会插入那个数据页，这样造成的IO是很耗时的，所以出现了Insert Buffer。
mysql对于非聚集索引的插入，先去判断要插入的索引页是否已经在内存中了，如果不在，暂时不着急先把索引页加载到内存中，而是把它放到了一个Insert Buffer对象中，临时先放在这，然后等待情况，等待很多和现在情况一样的非聚集索引，再和要插入的非聚集索引页合并，比如说现在Insert Buffer中有1，99，2，100，合并之前可能要4次插入，合并之后1，2可能是一个页的，99，100可能是一个页的，这样就减少到了2次插入。这样就提升了效率和插入性能，减少了随机IO带来性能损耗。
使用条件：
（1）索引是辅助索引（secondary index）；
（2）索引不是唯一（unique）的；

- 二次写(double write)
解决的问题： 一个数据页的大小是16K，假设在把内存中的脏页写到数据库的时候，写了2K突然掉电，也就是说前2K数据是新的，后14K是旧的，那么磁盘数据库这个数据页就是不完整的，是一个坏掉的数据页。redo只能加上旧、校检完整的数据页恢复一个脏块，不能修复坏掉的数据页，所以这个数据就丢失了，可能会造成数据不一致，所以需要double write。
工作流程：

阶段一：copy过程中，操作系统crash，重启之后，脏页未刷到磁盘，但更早的数据并没有发生损坏，重新写入即可;
阶段二：write到共享表空间过程中，操作系统crash，重启之后，脏页未刷到磁盘，但更早的数据并没有发生损坏，重新写入即可;
阶段三：write到独立表空间过程中，操作系统crash，重启之后，发现：
（1）数据文件内的页损坏：头尾checksum值不匹配（即出现了partial page
write的问题）。从共享表空间中的doublewrite segment内恢复该页的一个副本到数据文件，再应用redo log；
（2）若页自身的checksum匹配，但与doublewrite segment中对应页的checksum不匹配，则统一可以通过apply
redo log来恢复。

- 自适应哈希索引(ahi)
哈希算法是一种非常快的查找方法，在一般情况（没有发生hash冲突）下这种查找的时间复杂度为O(1)。InnoDB存储引擎会监控对表上辅助索引页的查询。如果观察到建立hash索引可以提升性能，就会在缓冲池建立hash索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。
- 预读(read ahead)
一种IO操作，用于异步将磁盘的页读取到buffer pool中，预料这些页会马上被读取到。预读请求的所有页集中在一个范围内。