MySQL知识

MySQL存储引擎

什么是存储引擎？

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

提示：InnoDB事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL5.5之后，InnoDB作为默认存储引擎。

MyISAM是基于ISAM的存储引擎，并对其进行扩展，是在web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。

MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他数据提供快速访问。

如何选择MySQL存储引擎

功能MyISAMMEMORYInnoDBArchive存储限制256TBRAM64TBNone支持事务NoNoYesNo支持全文索引YesNoNoNo支持树索引YesYesYesNo支持哈希索引NoYesYesNo支持数据缓存NoN/AYesNo支持外键NoNoYesNo

事务Transaction

InnoDB引擎，支持事务。 事务，由若干条语句组成的，指的是要做的一系列操作。

关系型数据库中支持事务，必须支持四个属性（ACID）：

特性描述原子性（atomicity）一个事务是一个不可分割的工作单位，事务中包括所有的操作，要么全部做完，要么什么都不做一致性（consistency）事务必须是使数据库从一个一致性状态到另一个一致性状态。一致性与原子性是密切相关的。隔离性（isolation）一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰。持久性（durability）持久性也称为永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

原子性，要求事务中的所有操作，不可分割，不能做了一部分操作，还剩一部分操作；

一致性，多个事务并行执行的结果，应该和事务排队执行的结果一致。如果事务的并行执行和多线程读写共享资源一样不可期，就不能保证一致性。

隔离性，就是指多个事务访问共同的数据，应该互不干扰。隔离性，指的是究竟在一个事务处理期间，其他事务能不能访问的问题。

持久性，比较好理解，就是事务提交后，数据不能丢失。

MySQL隔离级别

隔离性不好，事务的操作就会互相影响，带来不同严重程度的后果。 首先看看隔离性好不好，带来哪些问题：

更新丢失Lost Update 事务A和B，更新同一个数据，它们都读取了初始值100，A要减10， B要减100，A减去10后更新为90，B加100更新为200，A的更新丢失了。

脏读 事务A和B，事务B读取到了事务A未提交的数据（这个数据可能是一个中间值，也可能事务A后来回滚事务）。事务A是否最后提交并不关心。只要读取到了这个被修改的数据就是脏读。

不可重复读 UNrepeatable read 事务A在事务执行中执行相同查询语句，得到了不同的结果，不能保证同一条查询语句重复读相同的结果就是不可以重复读。 例如，事务A查询了一次后，事务B修改了数据，事务A又查询了一次，发现数据不一致。 注意：脏读讲的是可以读到相同数据的，但是读取的是一个未提交的数据，而不是提交的最终结果。

幻读Phantom read 事务A中同一个查询要进行多次，事务B插入数据，导致A返回不同的结果集，如同幻觉，就是幻读。 数据集有记录增加了，可以看做是增加了记录不可重复读。

有了上述问题，数据库必须要解决，提出了隔离级别。

隔离级别

由低到高，如下表：

隔离级别描述READ UNCOMMITTED读取到未提交的数据READ COMMITTED读已经提交的数据，ORACLE默认隔离级别REPEATABLE READ可以重复读，MySQL的默认隔离级别SERIALIZABLE可串行化。事物间完全隔离，事务不能并发，只能串行执行

隔离级别越高，串行化越高，数据库执行效率低；并行度越高，性能越高。

隔离级别越高，当前事务处理的中间结果对其它事务不可见程度越高。

SERIALIZABLE,串行了，解决所有问题

REPEATABLE READ,事务A中同一条查询语句返回同样的结果，就是可以重复读数据。例如：（SELECT * FROM user）。解决的办法有：

对select的数据加锁，不允许其它事务删除、修改的操作第一次select的时候，对最后一次确切提交的事务的结果做快照 解决了不可以重复读，但是有可能出现幻读。因为另一个事务可以增删数据。

READ COMMITTED,在事务中，每次select可以读取到别的事务刚提交成功的新的数据。因为读到的是提交后的数据，解决了脏读，但是不能解决不可重复读和幻读的问题。因为其他事务前后修改了数据或增删了数据。

READ UNCOMMITTED,能读取到别的事务还没有提交的数据，完全没有隔离性可言，出现了脏读，当前其他问题都可能出现。

事务语法

START TRANSACTION或BEGIN开始一个事务，START TRANSACTION是标准SQL的语法。

使用COMMIT提交事务之后，变更为永久变更。

ROLLBACK可以在提交事务之前，回滚变更，事务中的操作就如同没有发生过一样（原子性）。

SET AUTOCOMMIT语句可以禁用或启用默认的autocommit模式，用于当前连接。SET AUTOCOMMIT=0禁用自动提交事务。如果开启自动提交，如果有一个修改表的语句执行后，会立即把更新存储到磁盘。