MySQL笔记（一）

一、锁

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

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

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

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

二、事务

事务是一组原子性的SQL查询，或者说是一个独立的工作单元。一个运行良好的事务处理系统必须通过严格的ACID测试，ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。

原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。

一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态，

隔离性：通常来说（由事务的隔离级别决定），一个事务所做的修改在最终提交之前，对其他事务是不可见的。

持久性：一旦事务提交，则其所做的修改就会永久保存在数据库中。

MySQL的事务型存储引擎：InnoDB和NDB Cluster，非事务型存储引擎：MyISAM

一般不建议在一个事务中混合使用事务型和非事务型的表，在正常提交的情况下没有什么问题，但是如果需要回滚的话，非事务型的表上的修改就无法撤销。

1、事务隔离级别

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

READ UNCOMMITTED（未提交读）

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

READ COMMITTED（提交读）

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

REPEATABLE READ（可重复读）

REPEATABLE READ解决了脏读问题，该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是无法解决幻读问题，幻读是指当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的记录再次读取该范围的记录时会产生幻行。InnoDB存储引擎通过MVCC（多版本并发控制）解决了幻读问题。该隔离级别是MySQL的默认隔离级别。

SERIALIZABLE（可串行化）

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

2、多版本并发控制（MVCC）

MVCC可以认为是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。不同的数据库系统实现MVCC的机制都不同，但是大都实现了非阻塞的读操作，写操作也只锁定必要的行。

MVCC的实现，是通过保存数据在某个时间的快照来实现的，也就是说不管需要执行多长的时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。MVCC主要有两种实现机制：乐观并发控制和悲观并发控制。

InnoDB的MVCC实现原理：

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（删除时间），但是存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的版本号作为事务的版本号。

MVCC具体的操作：

  SELECT:

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

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

  INSERT:

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

  DELETE:

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

  UPDATE:

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

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

三、InnoDB和MyISAM存储引擎

1、InnoDB

InnoDB是MySQL的默认事务型引擎，也是最重要的、使用最广泛的存储引擎。其用于处理大量的短期事务，短期事务大部分情况下是正常提交的，很少会被回滚。

InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列数据文件组成。InnoDB采用MVCC支持高并发，并且实现了四个标准的隔离级别。其通过间隙锁策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询所涉及的行，还会对索引中间隙进行锁定，以防止幻影行的插入。

InnoDB表是基于聚簇索引建立的。聚簇索引对主键查询性能很高，不过二级索引中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。

此外，InnoDB支持真正的热备份，MySQL的其它存储引擎都不支持热备份。

2、MyISAM

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且一大缺陷就是崩溃后无法安全恢复。MyISAM会将表存储在两个文件中，数据文件和索引文件。

MyISAM会对整张表加锁。读取时会对需要读到的所有表加共享锁。写入时则对表加排他锁。对于MyISAM表，MySQL可以手工或者自动执行检查和修复操作，但是这与事务恢复和崩溃恢复是不同的，执行表的修复可能会导致一些数据丢失，而且修复操作是非常缓慢的。

3、InnoDB和MyISAM的区别

以下是一些细节和具体实现的差别：
　◆1、InnoDB不支持全文索引，而MyISAM支持。InnoDB支持聚簇索引，而MyISAM不支持。
　◆2、InnoDB中不保存表的具体行数，而MyISAM中保存有表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的，都要扫描一遍整个表。
　◆3、对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
　◆4、DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。MyISAM则会重新建立一张表格，并将剩余的行（不需要删除的行）复制到该表格里面，返回该表。
　◆5、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
　另外，InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”

　两种类型最主要的差别就是InnoDB支持事务处理和行级锁。而MyISAM不支持。

参考：

《高性能MySQL》