并发控制

读写锁

共享锁（share lock）和排他锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）。读锁是共享的，多个用户同一时刻呆以读一个资源，互不干扰。写锁是排他的，一个写锁会阻塞其它的写锁和读锁

锁粒度
  • 表锁
  • 基本策略，开销最小，对表进行写操作时，会阻塞所有读写操作，没有写锁时，读间不相互阻塞
  • 特定的场景中，表锁也可能有良好的性能，例如READ LOCAL表锁支持某些类型的并发写操作。另外，写锁比读锁有更高的优先级
  • ALTER TABLE 之类的语句会使用表锁，忽略存储引擎的锁机制
  • 行锁
  • 最大程度支持并发处理，最大的锁开销
  • 行级锁只在存储引擎层实现，而在MySQL服务器层没有实现

事务

ACID

原子性（atomicity）

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部成功，要么全部失败

一致性（consistency）

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

隔离性（isolation）

通常来说，一个事务所做的修改在最终提交以前，对其它事务是不可见的

持久性（durability）

一旦事务提交，则其所做的修改就会永久的保存的数据库中，此时即使系统崩溃，修改的数据也不会丢失

隔离级别

READ UNCOMMITTED（未提交读）

事务中的修改，即使没有提交，对其它事务也是可见的

READ COMMITTED（提交读）

一个事务开始时，只能看见已经提交的事务所做的修改。两次执行同样的查询，可能会得到不一样的结果

REPEATABLE READ（可重复读）

MySQL默认的事务隔离级别。保证在同一事务中多次读取同样数据结果是一致的，但是会出现幻读（Phantom Read）的问题。MySQL通过多版本并发控制（MVCC,Multiversion Concurrency Control）解决了此问题。

SERIALIZABLE（可串行化）

最高的隔离级别，每次读取时都加锁

死锁

InnoDB处理死锁的方式

将持有最少行级排他锁的事务进行回滚

MySQL中的事务

MySQL提供两种事务型存储引擎： InnoDB和NDB Cluster

自动提交（AUTOCOMMIT）

MySQL默认使用自动提交模式，也就是说，如果不是显式的开始一个事务，则每个查询都作一个事务执行提交操作。可以通过设置
AUTOCCOMMIT变量来启动或禁用提交模式

mysql> SET AUTOCOMMIT = 1
  • 此修改对非事务型表没有任何影响
  • 有一些命令在执行之前会强行执行COMMIT提交当前的活动事务，比如ALTER TABLE等会导致大量数据改变的操作

MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL来设置隔离级别，新的隔离级别会在下一个事务开始时生效，可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别：

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED
在事务中混合使用存储引擎

MySQL服务器层不管理事务，由下层存储引擎实现，所以在同一个务中，使用多种存储引擎是不可靠的，如果事务回滚，非事务型的表上的变更无法回滚，导致数据库处于不一致的状态。

多版本并发控制

MVVC的实现 ，是通过保存数据在某个时间点的快照来实现的，在很多情况下避免了加锁操作，开销更低。
不同的存储引擎的ＭＶＣＣ实现是不同的，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。

InnoDB的MVCC 是通过在每行记录后面保存两个隐藏列来实现的，一列保存行的创建时的系统版本号，一个保存行的删除时的系统版本号，每开始一个新事务，系统版本号都会自动递增。事务开始时的系统版本号会作为事务的版本号。注：MVCC只在REPEATABLE READ 和 READ COMMITTED两个隔离级别下工作

MySQL的存储引擎

MySQL使用文件系统的目录和文件来保存数据库和表的定义，所以大小写敏感性和具体的平台相关。

InnoDB

概览
  • 数据存储在表空间中(tablespace)
  • 采用MVCC来支持高并发，并实现了四个标准隔离级别，默认是REPEATABLE READ
  • 基于聚簇索引建立，对主键查询有很高的性能
  • 从磁盘读取数据时采用可预测性读
  • 自动在内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash idex)
  • 加速插入操作的插入缓冲区(insert buffer)
  • 支持真正的热备份，MySQL的其它存储引擎不支持热备份

MyISAM

特性
  • 表锁，读取时加共享锁，写入时加排他锁，但是在读取时，可以插入新记录
  • 索引，对于 BLOB和TEXT可以基于前500个字符创建索引，也支持全文索引
  • 延迟更新索引，索引先写入内存缓冲区，提升写入性能
  • 压缩表，可以对表进行压缩，压缩表不能修改（要先解压），但极大减少磁盘空间占用，减少磁盘I/O，从而提升查询性能。压缩表也支持索引（只读）
  • 崩溃后无法安全恢复

转换表引擎

ALTER TABLE
mysql> ALTER TABLE mytable ENGINE = InnoDB
  • 可以使用于任何存储引擎
  • 需要执行很长的时间，MySQL会按 行将数据从原表复制到新表中，同时原表会加读锁。
  • 转换表的存储引擎，将丢失原引擎相关的特性，例如从InnoDB->MyISAM->InnoDB，原InnoDB表的所有外键将丢失
导出与导入

使用mysqldump将数据导出到文件，修改CREATE TABLE语句的存储引擎选项，再导入

创建与查询

综合了第一种方法的高效和第二种方法的安全

mysql> CREATE TABLE innodb_table LIKE myisam_table;
mysql> ALTER TABLE innodb_table ENGINE = InnoDB;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;

如果数据量很大，则要考虑分批处理，针对每一段数据执行事务提交操作，避免大事务产生过多的undo

mysql> START TRANSACTION;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
mysql> COMMIT;