MySQL学习笔记（二）

说明

• 最近学习MySQL DBA功课，这里记录一下笔记。
• 作者：yfang (yangfang at fudan.edu.cn)
• 修改时间：2008-3-9
• 原创文章，欢迎转载，转载请注明出处及作者信息，谢谢

MySQL存储引擎

InnoDB

表空间和日志（Tablespace and Logs）

• InnoDB和其他MySQL的引擎一样，使用frm文件存储表格式
• InnoDB使用独特的表空间来存放数据和索引，以及用于事务回退的数据段（rollback segment）。
• InnoDB默认使用公共的表空间，默认路径是data目录下。
• 启动MySQL时加上--innodb_file_per_table 参数，可以使得新创建的InnoDB表使用独立的表空间。
  • 新创建的InnoDB表会在相应数据库的目录下创建.ibd文件
  • 原有的InnoDB表依然使用共享的表空间。
  • 即使是新的InnoDB表，依然需要公共表空间的支持，因为那里存储了InnoDB的数据字典（data dictionary）和回退段 （rollback segment）。
• InnoDB使用log来存储事务的行为，帮助维护事务的正常工作以及系统崩溃后的恢复工作。

ACID兼容

• InnoDB的事务是完全ACID兼容的。
• ACID 的意思是：
  • Atomic（原子的）：事务中的操作要么都执行，要么都不执行。
  • Consistent（一致的）：数据库中的数据在事务操作中保持一致性。
  • Isolated（隔离的）：事务之间互不干扰。
  • Durable（持续的）：成功完成的事务所做的所有操作都正确的存放在数据库中，不会丢失。
• 在MySQL中，默认设定自动清理InnoDB日志（InnoDB log flushing），用来保证ACID兼容。

InnoDB 事务模型

• InnoDB默认是自动提交(AUTOCOMMIT)的，也就是说每一个SQL命令看成一个事务来处理。
• 改变自动提交的方法

SET AUTOCOMMIT = 0;
    Your Statement 1;
    YOur Statement 2;
    ......
    Your Statement n;
COMMIT; -- 或者 ROLLBACK;
SET AUTOCOMMIT = 1; -- 恢复默认的autocommit，否则下面的命令仍然会被认为是新的事务

• 另一个方法：使用START TRANSACTION; 或者 BEGIN; 或者 BEGIN WORK;的形式显示开始一个事务。

START TRANSACTION;
    Your Statement 1;
    YOur Statement 2;
    ......
    Your Statement n;
COMMIT; -- 或者 ROLLBACK;

• 设置恢复点（SAVE POINT）及恢复

-- 在同一个事务内
SAVEPOINT savepoint_yfang;
   Some Statements ......
ROLLBACK TO SAVEPOINT savepoint_yfang;
-- 之后的命令会相当于从恢复点处开始，而中间的Some Statements中如果再出现恢复点，将会被自动删除

• 下面的命令将会引起当前事务的自动提交，并开启新的事务

ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
LOCK TABLES
UNLOCK TABLES -- 仅当前面显示执行过LOCK TABLES操作时有效
SET AUTOCOMMIT = 1 -- 仅当当前的AUTOCOMMIT是0的情况下有效
START TRANSACTION

• 事务隔离，因此不能嵌套
• 使用START TRANSACTION; 或者 BEGIN; 或者 BEGIN WORK;的形式显示开始一个事务，将会隐式释放掉表锁。

InnoDB锁特征

• InnoDB中不必为了保证事务中的一致性而锁表，因为它本身就支持多版本控制，根据设置，事务可以连续读表中的数据。
• InnoDB使用行锁，加强了读写并发的性能
  1. 不同的客户端可以同时读同一行数据
  2. 不同的客户端可以同时写不同行数据
  3. 不同的客户端不能同时修改同一行数据。如果一个事务正在写一行数据，其他事务将不能修改这行数据，读也会被阻塞，除非使用READ UNCOMMITTED 隔离层。
• InnoDB发现死锁之后，会把变更比较少的一个事务Rollback。如果它没有检测死锁，事务超时之后也会自己Rollback。
• InnoDB支持两种SELECT锁
  1. LOCK IN SHARE MODE，允许并发读，不过会禁止其他事务写；同样有未提交事务更改行的话，这个SELECT将被阻塞。
  2. FOR UPDATE，阻塞其他读写进程，锁定所有SELECT到的行

隔离层（Isolation Levels），多版本控制（Multi-Versioning）和并发

• 有三种事务可能带来的并发问题
  1. 脏读（dirty read），就是说T1，T2两个事务并发，T1更改了一行数据，但是尚未提交，T2读到T1更新后的数据。
  2. 不可重复读（non-repeatable read），T1在事务开始读了一行数据，T2修改了它并提交，T1后来读到了和开始不一样的数据。
  3. 幽灵行（phantom），T1在事务开始读表，T2添加一行并提交，T1后来发现了开始并不存在的一行。
• InnoDB的隔离层
  1. READ UNCOMMITTED，事务可以看到其他事务尚未提交的更新。
  2. READ COMMITTED，事物只允许看到其他事务已经提交的更新。
  3. REPEATABLE READ，保证同一事务内的两次同样读操作得到同样的结果，不过有些引擎下有可能发生幽灵行，但是InnoDB不会。
  4. SERIALIZABLE，类似REPEATABLE READ，而且要求一个未提交的事务读过的行不允许其他事务更新。
• InnoDB默认使用REPEATABLE READ。使用--transactionisolation参数可以设定默认的隔离级（READ-UNCOMMITTED , READ-COMMITTED , REPEATABLEREAD, SERIALIZABLE）
• 运行的时候也可以更改隔离级

SET GLOBAL TRANSACTION ISOLATION LEVEL isolation_level; -- 设定全局参数，所有新建立的事务将受到影响
SET SESSION TRANSACTION ISOLATION LEVEL isolation_level; -- 设定Session参数，所有本客户连接上新建立的事务将受到影响
SET TRANSACTION ISOLATION LEVEL isolation_level; -- 设定临时参数，仅有本客户连接上的下一个事务受到影响。

外键