mysql有enable disable constraint吗 mysql on clause

MySQL架构与特性（二）

MySQL架构与特性（一）

• 事务

mysql提供了两种事务型存储引擎：InnoDB和NDB Cluster。
 mysql采用自动提交模式，可以通过设置AUTOCOMMIT变量来启用或关闭自动提交。
 show variables like 'aotucommit';
 set autocommit = 1;

1或者ON 标识启用，0或者OFF表示禁用，禁用情况下，需要通过commit或者rollback来提交或者回滚。对非事务型存储引擎，修改无影响，一直是开启AUYOCOMMIT状态。

需要注意的是 ：

1. 有些命令，在执行前会强制执行COMMIT提交当前事务，例如DDL语句中ALTER TABLE 还有LOCK TABLES 等语句
2. mysql服务器层不管理事务，事务由下层存储引擎实现。同一事务中，使用多个存储引擎是不可靠的。在事务中混合使用了事务型和非事务型的表，如果回滚，非事务型的表则无法回滚
3. InnoDB采用两阶段锁定协议，在事务执行过程中随时可以执行锁定，锁只有在执行COMMIT或者ROLLBACK时候才会释放，且是在同一时刻释放。InnoDB会根据隔离级别在需要的时候自动加锁，也可以显式指定：

✓ select ... lock in share mode;
✓ select ... for update;

• 事务的特性（ACID)

1. 原子性
   一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有操作要么全部成功，要么全部失败回滚。
2. 一致性
   数据库总是从一个一致性的状态转换到另外一个一致性的状态（每次操作的结果与人民的预期是一致的）
3. 隔离性
   一个事务中所作的修改，在最终提交之前，对其他事务是不可见的。
4. 持久性
   事务一旦提交，则所有的修改都会被保存到数据库中

• 事务的隔离级别

5. 读未提交（READ UNCOMMITTED）
   事务可以读取其他事务未提交的数据，称之为：脏读。
   在read uncommitted 级别中，事务中的修改，即使没有提交，对其他事务也是可见的
6. 读已提交（READ COMMITTED)
   解决脏读问题
   一个事务开始时，只能看到已经提交的事务所作的修改。换句话说，一个事务从开始知道提交之前，所作的任何修改对其他事务都是不可见的。两次执行同样的查询，可能会等到不一样的结果（也称为不可重复读）
7. 可重复读（REPEATABLE READ)
   当某个事务在读取某一个范围的记录是。另外一个事务在该范围内插入了新的记录，当之前的事务再次读取改范围记录会产生幻行（多了记录），称之为：幻读。
   可重复读 解决了脏读，不可重复度等问题，但无法解决幻读问题。在InnoDB和XtraDB存储引擎中，通过多版本并发控制（MVCC）巧妙的解决了幻读
   通过间隙锁策略防止幻读的出现。不仅仅锁定查询设计的行，还对索引中的间隙进行锁定，防止幻行的插入
8. 串行化（SERIALIZABLE)
   事务的最高级别，它强制事务串行执行，避免了脏读，幻读，不可重复读等问题。但是效率也是最低的。
   事务隔离级别对应解决的问题

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
读未提交	Y	Y	Y	N
读已提交	N	Y	Y	N
可重复度（MYSQL默认）	N	N	Y	N
串行化	N	N	N	Y

mysql通过 
SET TRANSACTION ISOLATION LEVEL 命令来设置隔离级别，
新的隔离级别会在下一个事务开始时候生效。
可以在配置文件中设置整个数据的隔离级别，
也可以只改变当前会话的隔离级别：
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;

• 死锁

死锁是指两个或多个事务在统一资源上相互占用，并请求锁定对方占用资源，从而导致的恶性循环的现象。

数据库系统实现了各自死锁建册和死锁超时机制。

1. ✧ 查询时间达到锁等待超时的设定的时间后，放弃锁请求。（不太好）
2. ✧ InnoDB存储引擎，检测到死锁时，立即返回错误。避免出现慢查询，将持有最少行级锁的事务进行回滚（死锁回滚法）

锁的行为和顺序与存储引擎相关。同样顺序执行语句，有些存储引擎会死锁，有些不会。
有些是真正的数据冲突（难避免），有些则是有存储引擎的实现方式导致。

• 事务日志

事务日志可以帮助提高事务的效率
 存储引擎在修改表数据时只需要修改其内存拷贝，再把该修改的行为记录持久在磁盘上的事务日志中，
 而不用每次都将修改的数据本身持久化到磁盘。事务日志采用追加的方式，写日志是顺序I/O，相对会快一些。
 通常称为预写式日志，修改数据需要写两次磁盘。

如果写入日志后，本身并没有写回磁盘，则此时系统崩溃。存储引擎在重启时候能够自动恢复这部分修改的数据。

• 多版本并发控制（MVCC Multiversion Concurrency Control）

MVCC是通过保存数据在某一个时间点的快照来实现的，不管需要执行多长时间，
 每个事务看到的数据是一致的，有乐观并发控制和悲观并发控制两个实现

InnoDB的MVCC实现（简化版）是通过在每行记录后保存两个隐藏的列来实现：这两个列，一个保存了行的创建时间（时间不是实际的时间，是系统版本号），一个保存过期时间（或删除时间）；没开始一个新的事务，版本号都递增，开始时刻的版本号作为事务的版本号，查询时与记录的版本号对比。具体如下（REPEATABLE READ隔离级别）：

select
 InnoDB会根据两个条件检查每行记录：
 ➢ InnoDB只查找版本号早于当前事务版本的数据行，可以确保当前事务读取的行，要么是事务开始之前已经存在，要么是事务自身插入或者修改过的。
 ➢ 行的删除版本要么未定义，要么大于当前事务版本号，可以确保事务读取到的行，在事务开始之前未被删除
 insert
 InnoDB为新插入的每一行保存当前事务系统版本号作为行版本号。
 delete
 InnoDB为删除的每一行保存当前事务系统版本号作为行删除标识。
 update
 InnoDB为插入一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识
 （修改是增加新的记录，删除原来记录？）

MVCC仅在REPEATABLE READ 和READ COMMITED两个隔离级别下工作，其他均不兼容。因为 READ UNCOMMITED总数读取最新的行数据，SERIALIZABLE则会对所有读取的行都加锁。

• mysql的存储引擎

1. InnoDB存储引擎

✧ 2008年InnoDB plugin 发布适用于mysql5.1版本，其拥有着是InnoDB不是MySQL。
✧ Oracle收购Sun公司，5.5版本才彻底使用看原生编译的InnoDB（默认存储引擎）。
✧ InnoDB的数据存储在表空间中，将每个表的数据和索引存放在单独的文件中。
✧ 采用MVCC来支持高并发，实现了四个标准的隔离级别。默认是可重复的。并且通过间隙锁策略防止幻读的出现。不仅仅锁定查询设计的行，还对索引中的间隙进行锁定，防止幻行的插入。
✧ InnoDB的表是居于聚簇索引建立的，对主键的查询有很高的性能。
✧ InnoDB从磁盘读取数据是采用可预测读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引

1. MyISAM存储引擎

MySQL5.1及之前的版本，默认的存储引擎都是MyISAM。MyISAM提供了大量的特性：全文索引、压缩、空间函数等，但不支持事务和行级锁，且崩溃后无法安全回复。

存储

MyISAM将表存储在两个文件中：数据文件和索引文件。可存的行记录数受限于磁盘空间或者操作系统中对单个文件的最大尺寸

特性

✧ 加锁与并发

MyISAM对整张表加锁，读取时加共享锁，写入时则加排它锁，但在表有读取查询的同时，也可以往表中插入新的记录，称之为：并发插入。

✧ 索引特性

即使是Blob和text字段，也可以基于前500个字符创建索引，支持全文索引，hash索引等

✧ 性能

设计简单，数据存储格式机密，只读情况下，性能很好

• 选择合适的存储引擎

**在大多数情况下，InnoDB都是正确的选择**

如果使用其他存储引擎，主要考虑以下几种因素：

➢ 事务
 ➢ 备份
 ➢ 崩溃恢复
 ➢ 特有的特性

下一篇：MySQL架构与特性（三）——数据库与数据类型优化