Mysql两种存储引擎，数据库事务，隔离级别，MVCC，范式和反范式，varchar与char区别，存储过程

Mysql查询请求执行过程

在这里插入图片描述
表是由一行一行的记录组成的，这只是一个逻辑上的概念，在物理上如何表示记录，怎么从表中读取数据以及怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。

人们把mysql服务器处理请求的过程简单地划分为server层和存储引擎层。连接管理，查询缓存，语法解析，查询优化这些并不涉及真实数据存取的功能划分为server层，存取真实数据的功能划分为存取引擎层的功能。

server层完成了查询优化之后，只需按照生成的执行计划调用底层存储引擎提供的接口获取到数据后返回给客户端就好了。

不过需要注意的一点是，server层和存储引擎层交互时，一般是以记录为单位的。以SELECT语句为例，server层根据执行计划先向存储引擎层取一条记录，然后判断是否符合WHERE条件；如果符合，就发送给客户端，否则跳过该记录然后继续向存储引擎索要下一条记录。

MySQL两种存储引擎

MyISAM是MySQL的默认数据库引擎（5.5版本之前），由早期的ISAM（indexed sequential Access Method：有索引的顺序访问方法）所改良。虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）。

InnoDB，是MySQL的数据库引擎之一。InnoDB最大的特点就是支持了ACID兼容的事务（Transaction）功能。

MyISAM和InnoDB两者之间的差别

事务支持
- MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显式放在begin和commit之间，组成一个事务去提交。
- MyISAM是非事务安全型的，而InnoDB是事务安全型的，即默认开启自动提交，应该合并事务，一同提交，减少数据库多次提交导致的开销，大大提高性能。
存储结构
- MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD（MYData）。索引文件的扩展名是.MYI（MyIndex）。
- InnoDB:所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件）。InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
存储空间
- MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表，动态表，压缩表。
- InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
可移植性，备份及恢复
- MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独对某个表进行操作。
- InnoDB：免费的方案可以是拷贝数据文件，在数据量很大的时候就相对痛苦了。
事务支持
- MyISAM：强调的是性能，每次查询具有原子性，其执行速度比InnoDB类型要快，但是不提供事务支持。
- InnoDB：提供事务支持，外键等高级数据库功能。具有事务，回滚和崩溃修复能力的事务安全型表。
AUTO_INCREAMENT
- MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，它可以根据前面几列进行排序后递增。
- InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
表锁差异
- MyISAM：只支持表级锁，用户在操作myisam表时，select，updata，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
- InnoDB：支持事务和行级锁，是InnoDB的最大特色。行锁大幅度提高了多用户并发操作的能力。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
- MyISAM锁的粒度是表级，而InnoDB支持行级锁定。简单来说就是，InnoDB支持数据行锁定，而MyISAM不支持行锁定，只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以MyISAM不适合有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，所以某项读操作比较耗时会使其他写进程饿死。
全文索引
- MyISAM：支持（FULLTEXT类型的）全文索引。
- InnoDB：不支持（FULLTEXT类型的）全文索引，但是InnoDB可以使用插件支持全文索引，并且效果更好。
表主键
- MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
- InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键（用户不可见），数据是主索引的一部分，附加索引保存的是主索引的值。InnoDB的主键范围更大，最大是MyISAM的2倍。
表的具体行数
- MyISAM：保存有表的总行数，如果 select count(*) from table；会直接取出该值
- InnoDB：没有保存表的总行数（只能遍历），如果使用 select count(*) from table；就会遍历整个表，消耗相当大，但是在加了where条件后，myisam和InnoDB处理的方式都一样。
外键
- MyISAM：不支持
- InnoDB：支持
CURD操作
- MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
- InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在InnoDB上如果要清空保存有大量的数据的表，最好使用truncate table这个命令。
查询效率
- 没有where的count(*)时，使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count(*)直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般要伴随where，且where中要包含主键以外的索引列。为什么这里强调“主键以外”因为InnoDB中primary index是和raw data存放在一起的，而secondary index则是单独存放，然后有个指针指向primary index。所以只是count(*)的话使用secondary index扫描更快，而primary index则主要在扫描索引的同时要返回raw data时的作用较大。MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。

MyISAM和InnoDB的应用场景

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

数据库事务

事务

事务（transaction）是并发控制的基本单位。所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。例如，银行转账工作：从一个账号扣款并使另一个账号增款，这两个操作要么都执行，要么都不执行。所以，应该把它们看成一个事务。事务是数据库维护数据一致性的饿单位，在每个事务结束时，都能保持数据一致性。

针对上面的描述可以看出，事务的提出主要是为了解决并发情况下保持数据一致性的问题。

事务具有以下4个基本特征。

Atomic（原子性）：原子性是指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，才算整个事务成功。事务中任何一个SQL语句执行失败，已经执行成功的SQL语句也必须撤销，数据库状态应该退回执行事务前的状态。
Consistency（一致性）：一致性指事务将数据库从一种状态转变为下一种一致的状态。事务开始之前和事务结束之后，数据库的完整性约束没有被破坏。
Isolation（隔离性）：事务的隔离性要求每个读写事务的对象对其他事务的操作对象能够相互分离，即该事务提交前对其他事务都不可见。
Durability（持久性）：事务一旦提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。

事务的语句

开始事务：BEGIN TRANSACTION

提交事务：COMMIT TRANSACTION

回滚事务：ROLLBACK TRANSACTION

事务的保存点

SAVE TRANSACTION 保存点名称——自定义保存点的名称和位置

ROLLBACK TRANSACTION 保存点名称——回滚到自定义的保存点

事务的标准定义：指作为单个逻辑工作单元执行的一系列操作，而这些逻辑工作单元需要具有原子性，一致性，隔离性和持久性四个属性，统称为ACID特性。

所谓事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。例如，在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。

事务和程序是两个概念。一般来讲，一个程序中包含多个事务。事务的开始和结束可以由用户显式控制。如果用户没有显式的定义事务，则由DBMS按缺省规定自动划分事务。

显示事务被用begin transaction与end transaction标识起来，其中的update与delete语句或者全部执行或者全部不执行。如

begin transaction T1
update student 
set name='Tank'
where id=2006010
delete from student
where id=2006011
commit

简单的说，事务是一种机制，用以维护数据库的完整性。

实现方式就是将普通的SQL语句嵌入到Begin tran...Commit Tran中（或完整形式Begin Transaction...Commit Transaction）。

如何保证数据库的一致性

数据库本身能为我们解决一部分一致性需求（就是数据库自身可以保证现实世界的一部分约束永远有效）
- Mysql数据库可以为表建立主键，唯一索引，外键，还可以声明某个列为NOT NULL来拒绝NULL值的插入。Mysql还支持使用check语法来自定义约束。虽然CHECK子句对一致性检查没什么用，但我们还是可以通过定义触发器的方式来自定义一些约束条件，以保证数据库数据的一致性。
更多的一致性需求需要靠写业务代码的程序员自己保证

事务的状态

事务是一个抽象的概念，它其实对应着一个或多个数据库操作。

根据这些操作所执行的不同阶段把事务大致划分成了下面几个状态。

活动的（active）：事务对应的数据库操作正在执行过程中时，我们就说该事务处于活动的状态
部分提交的（partially committed）：当事务中的最后一个操作执行完成，但由于操作都在内存中执行，所造成的影响并没有刷新到磁盘时，我们就说该事务处于部分提交的状态
失败的（failed）：当事务处于活动的状态或者部分提交的状态时，可能遇到了某些错误（数据库自身的错误，操作系统错误或者直接断电）而无法继续执行，或者人为停止了当前事务的执行，我们就说该事务处于失败的状态
中止的（aborted）：如果事务执行了半截而变成失败的状态，要进行回滚操作，当回滚操作执行完毕后，也就是数据库恢复到了执行事务之前的状态，我们就说该事务处于中止的状态
提交的（committed）：当一个处于部分提交的状态的事务将修改过的数据都刷新到磁盘中之后，我们就可以说该事务处于提交的状态

在这里插入图片描述
只有当事务处于提交的或者中止的状态时，一个事务的生命周期才算是结束了。对于已经提交的事务来说，该事务对数据库所作的操作将永久生效；对于处于中止状态的事务来说，该事务对数据库所做的所有修改都会回滚到没执行该事务之前的状态。

事务id

一个事务可以是一个只读事务，也可以是一个读写事务

可以通过START TRANSACTION READ ONLY语句开启一个只读事务。在只读事务中，不可以对普通的表进行增删改操作，但可以对临时表进行增删改操作
可以通过START TRANSACTION READ WRITE开启一个读写事务，使用BEGIN,START TRANSACTION语句开启的事务默认也是读写事务。在读写事务中可以对表执行增删改操作

如果某个事务在执行过程中对某个表执行了增删改操作，那么innoDB存储引擎就会给它分配一个独一无二的事务id。

分配方式如下：

对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增删改操作时，才会为这个事务分配一个事务id，否则是不分配事务id的
对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增删改操作时，才会为这个事务分配一个事务id，否则是不分配事务id的。

有时，虽然我们开启了一个读写事务，但是这个事务中全都是查询语句，并没有执行增删改操作的语句，这也就意味着这个事务并不会被分配一个事务id。

如果不为某个事务分配事务id，那么它的事务id值默认为0

事务id怎么生成的

这个事务id本质上是一个数字，具体策略如下：

服务器会在内存中维护一个全局变量，每当需要为某个事务分配事务id时，就会把该变量的值当作事务id分配给该事务，并且把该变量自增1
每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间中页号为5的页面中一个名为MaxTrxID的属性中，这个属性占用8字节的存储空间
当系统下一次重新启动时，会将这个MaxTrxID属性加载到内存中，将该值加上256以后赋值给前面提到的全局变量

这样就可以保证整个系统中分配的事务id值是一个递增的数字。先分配事务id的事务得到的是较小的事务id，后分配事务id的事务得到的是较大的事务id。

隔离级别

READ UNCOMMITTED级别（未提交读）
- 在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也称为脏读（Dirty read）。这个会导致很多问题，从性能上说，READ UNCOMMITTED不会比其他的级别好太多，但却缺乏其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。
READ COMMITTED（提交读）
- 大多数数据库系统的默认隔离级别都是READ COMMITTED（但MySQL不是）。READ COMMITTED满足前面提到的隔离性的简单定义：一个事务开始时，只能看到已经提交的事务所作的修改。换句话说，一个事务从开始直到提交之前，所作的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。
REPEATABLE READ（可重复读）
- REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是在理论上还是无法解决另一个幻读（Phantom Read）的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。InnoDB通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。
SERIALIZABLE（可串行化）
- SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

隔离级别

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
READ UNCOMMITTED	YES	YES	YES	NO
READ COMMITTED	No	Yes	Yes	No
REPEATABLE READ	No	No	Yes	No
SERIALIZABLE	No	No	No	Yes

因为脏写这个现象对一致性影响太严重，无论是哪种隔离级别，都不允许脏写的情况发生。

事务并发执行时遇到的一致性问题

脏写
- 如果一个事务修改了另一个未提交事务修改过的数据，就意味着发生了脏写现象
脏读
- 如果一个事务读到了另一个未提交事务修改过的数据，就意味着发生了脏读现象
不可重复读
- 如果一个事务修改了另一个未提交事务读取的数据，就意味着发生了不可重复读现象，或者叫模糊读现象
幻读
- 如果一个事务先根据某些搜索条件查询出一些记录，在该事务未提交时，另一个事务写入了一些符合那些搜索条件的记录（这里的写入可以指insert, delete, update），就意味着发生了幻读现象。
- 对于Mysql来说，幻读强调的就是一个事务在按照某个相同的搜索条件多次读取记录时，在后读取时读到了之前没有读到的记录。这个“后读取到的之前没有读到的记录”可以是由别的事务执行insert语句插入的，也可能是别的事务执行了更新记录键值的update语句而插入的。这些之前读取时不存在的记录也可以称为幻影记录
丢失更新
丢失更新简单来说就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。
例如
（1）事务T1将行记录r更新为v1，但是事务T1未提交
（2）与此同时，事务T2将行记录r更新为v2，事务T2未提交
（3）事务T1提交
（4）事务T2提交
在当前数据库的任何隔离级别下，都不会导致数据库理论意义上的丢失更新问题。这是因为，即使在READ UNCOMMITTED的事务隔离级别，对于行的DML操作，需要对行或者其他粗粒度级别的对象加锁，因此在上述步骤2中，事务T2并不能对行记录r进行更新操作，其会被阻塞，直到事务T1提交。
虽然数据库能阻止丢失更新问题的产生，但是在生产应用中还有另一个逻辑意义的丢失更新问题，而导致该问题的并不是因为数据库本身的问题。实际上，在所有多用户计算机系统环境下都有可能产生这个问题，简单说来，出现下面这种情况时，就会发生丢失更新。
（1）事务T1查询一行数据，放入本地内存，并显式给一个终端用户User1
（2）事务T2也查询该行数据，并将取得的数据显示给终端用户User2
（3）User1修改这行记录，更新数据库并提交
（4）User2修改这行记录，更新数据库并提交
要避免丢失更新发生，需要让事务在这种情况下的操作变成串行化，而不是并行的操作。即在上述4个步骤的1中，对用户读取的记录加一个排他X锁，同样，在步骤2的操作过程中，用户同样也需要加一个排他X锁，通过这种方式，步骤2就必须等待步骤1和步骤3完整，最后完成步骤4。

MVCC多版本并发控制

Mysql的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制。

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

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

InnoDB的MVCC，是通过在每行记录后面保存的两个隐藏列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查操作，以及一些额外的维护工作。

MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容，因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁。

范式和反范式

在范式化的数据库中，每个事实数据会出现并且只出现一次。相反，在反范式化的数据库中，信息是冗余的，可能会存储在多个地方。

范式的优点和缺点

当因为性能问题而寻求帮助时，经常会被建议对schema进行范式化设计，尤其时写密集的场景。这通常是个好建议。因为下面这些原因，范式化通常能够带来好处。

范式化的更新操作通常比反范式化要快。
当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改较少的数据。
范式化的表通常很小，可以更好的放在内存中，所以执行操作会更快。
很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。在非范式化的结构中必须使用DISTINCT或GROUP BY才能获得一份唯一的表。

范式化设计的schema的缺点时通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引。

反范式的优点和缺点

反范式的schema因为所有的数据在同一张表，可以很好地避免关联。

如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O。

单独的表也能使用更有效的索引策略。假设有一个网站，允许用户发送消息，并且一些用户是付费用户。现在想查看付费用户最近的10条信息。如果是范式化的结构并且索引了发送日期字段published，这个查询也许看起来像这样：

select message_text, user_name
from message
inner join user on message.user_id=user.id
where user.account_type='premium'
order by message.published desc limit 10

要更有效的执行这个查询，Mysql需要扫面message表的published字段的索引。对于每一行找到的数据，将需要到user表里检查这个用户是不是付费用户。如果只有一部分用户是付费用户，那么这是效率低下的做法。

另一种可能的执行计划是从user表开始，选择所有的付费用户，获得它们所有的信息，并且排序。但这可能更加糟糕。

主要问题是关联，使得需要在一个索引中又排序又过滤。如果采用反范式化组织数据，将两张表的字段合并一下，并且增加一个索引（account_type，published），就可以不通过关联写出这个查询。

select message_text,user_name
from user_messages
where account_type='premium'
oreder by published DESC
limit 10

混用范式化和反范式化

完全的范式化和完全的反范式化schema都是实验室才有的东西。在真实世界中，很少会这么极端的使用。在实际应用中经常需要混用，可能使用部分范式化的schema，缓存表，以及其他技巧。

最常见的反范式化数据的方法是复制或者缓存。

VARCHAR和CHAR类型

VARCHAR和CHAR是两种最主要的字符串类型。

VARCHAR

varchar类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间（例如，越短的字符串使用越少的空间）。varchar需要使用1或2个额外字节记录字符串的长度：如果列的最大长度不小于或等于255字节，则只是用1个字节表示，否则使用2个字节。varchar节省了存储空间，所以对性能也有帮助，但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。

下面这些情况使用VARCHAR是合适的：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。

CHAR

CHAR类型是定长的：mysql总是根据定长的字符串长度分配足够的空间。当存储CHAR值时，Mysql会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。CHAR适合存储很短的字符串，或者所有值都接近同一个长度。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片，对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集，只需要1个字节，但是VARCHAR(1)却需要两个字节，因为还要一个记录长度的额外字节。