[ MYSQL ] 常用数据存储引擎，索引和隔离级别介绍

MySQL 常用数据存储引擎介绍与区别

Mysql 体系结构介绍

MySQL 的架构使得其能够支持多种存储引擎，分别是 InnoDB，MyISAM，MEMORY、BLACKHOLE，MRG_MYISAM、 CSV、ARCHIVE，PERFORMANCE_SCHEMA，FEDERATED

以下是使用 show engines 展示出的结果：

其中用的最多的就是InnoDB，MyISAM，MEMORY。在这里简单梳理一下他们的特点与区别。

InnoDB

InnoDB是事务型数据库的首选引擎！上图也看到了，InnoDB 是默认的 MySQL 引擎。

主要特性有：

• 支持事务。保证事务安全性。关于事务的一些知识见：数据库事务的实现原理
  
• 支持行级锁。
• 支持外键约束。
• 支持 MVCC 机制
• 采用的索引结构为聚簇索引和辅助索引（二级索引）。

以Mysql的InnoDB为例
主键是聚集索引
唯一索引、普通索引、前缀索引等都是二级索引（辅助索引）

MyISAM

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

主要特性有：

• 不支持事务。
• 锁级别为表锁，表锁优点是开销小，加锁快；缺点是锁粒度大，发生锁冲动概率较高，容纳并发能力低，这个引擎适合查询为主的业务。
• 不支持外键。
• 采用的索引结构为非聚簇索引。

聚簇索引和辅助索引见：聚集索引和辅助索引

非聚簇索引就是 B+ 树的叶子节点只保存了行记录数据的地址。如下图：

MEMORY

Memory 是内存级别存储引擎，数据存储在内存中，所以他能够存储的数据量较小，但是数据访问速度很快，当然，因为内存的特性，MEMORY 存储引擎对数据的一致性支持较差。

主要特性有 ：

• 不支持事务。

• 锁级别为表锁

• 不支持外键。

• 默认使用哈希索引
  

• InnoDB： 支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

• MyISAM： 插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。

• MEMORY： 所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。

乐观锁，悲观锁

首先，我们需要知道的是 mysql 中的锁大方面一共分为两类。一类是共享锁（即读锁），一类是排他锁（即写锁）。 写锁与任何锁都不能共存，只有读锁与读锁能够共存.

很明显，对于读操作而言，只要先加上共享锁，然后读取数据既可。而对于写操作，需要考虑更多。所以就出现了很著名的两种锁----悲观锁和乐观锁 。

悲观锁（借助数据库的锁机制实现的一种并发处理方式）

总是假设最坏的情况，即每次修改数据时都认为其他线程会修改，所以都会加（悲观）锁。一旦加锁，不同线程同时执行时,只能有一个线程执行，其他的线程在入口处等待，直到锁被释放。

在效率上，处理加锁的机制会让数据库产生额外的开销，还会有死锁的可能性。降低并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据。

处理的流程就是：

• 在对数据修改前，尝试增加排他锁。
• 加锁失败，意味着数据正在被修改，进行等待或者抛出异常。
• 加锁成功，对数据进行修改，提交事务，锁释放。
• 如果我们加锁成功，有其他线程对该数据进行操作或者加排他锁的操作，只能等待或者抛出异常。

乐观锁(人为实现发现被更新之后的操作)

乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测。

乐观锁顾名思义就是在操作时很乐观，认为操作不会产生并发问题(不会有其他线程对数据进行修改)，因此不会上锁。但是在数据更新进行提交时才会判断其他线程在这之前有没有对数据进行修改。一般会使用版本号机制 MVCC或CAS(compare and swap)算法实现。

乐观并发控制相信事务之间的数据竞争概率非常小，因此尽可能直接操作，提交的时候才去锁定，不会产生任何锁和死锁。

版本号机制

就是在读取数据时得到旧的版本号，然后更新时，判断是不是与前面取出的旧的版本号相同，如果相同就没有改变过！如果不相同就说明已经被其他的线程所改变，这边具体时重试 还是其他由程序员决定。

MVCC :多版本并发控制（依据高性能而来，可能与现在的实现不同，但是原理是一样的）

CAS 算法

即 compare and swap（比较与交换），是一种有名的无锁算法。无锁编程，即不使用锁的情况下实现多线程同步，也就是在没有线程被阻塞的情况下实现同步，所以也叫非阻塞同步（Non-blocking Synchronization）。

现在几乎所有的CPU指令都支持CAS的原子操作，X86下对应的是 CMPXCHG 汇编指令。有了这个原子操作，我们就可以用其来实现各种无锁（lock free）的数据结构。

该指令的含义用C语言来描述就是下面这个样子，意思就是说，看一看内存*reg里的值是不是oldval，如果是的话，则对其赋值 newval 。

int compare_and_swap (int* reg, int oldval, int newval) // 原子的 
{
  int old_reg_val = *reg;
  if (old_reg_val == oldval)
     *reg = newval;
  return old_reg_val;
}

所以CAS算法涉及到三个操作数

• 需要读写的内存值 V
• 进行比较的值 A
• 拟写入的新值 B

当且仅当 V 的值等于 A时，CAS通过原子方式用新值B来更新V的值，否则不会执行任何操作（比较和替换是一个原子操作）。一般情况下是一个自旋操作，即不断的重试。

使用场景：

• 读的多，冲突几率小，乐观锁。
• 写的多，冲突几率大，悲观锁。

三个范式是什么

• 首先要明确的是：满足第三范式，那么就一定满足第二范式、满足第二范式就一定满足第一范式

第一范式：字段是最小的的单元不可再分

学生信息组成学生信息表，有年龄、性别、学号等信息组成。这些字段都不可再分，所以它是满足第一范式的(字段值还可以继续拆分的，就不满足1NF)

address 中国陕西西安(不满足1NF，中国陕西西安应该拆分开来)

范式设计的越详细，对于某些操作可能会更好，但不一定！，以实际开发为主

第二范式：满足第一范式，除主键外的每一列都必须完全依赖于主键，而不能只依赖于主键的一部分。

其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的(依赖唯一，然后成为唯一)

学号为1024的同学，姓名为Java3y，年龄是22岁。姓名和年龄字段都依赖着学号主键。

如果主键是两个字段组成的，也就是说有的字段可能只依赖于一部分的主键，那么就得拆分才能满足2NF！！！

第三范式：满足第二范式，非主键外的所有字段必须互不依赖,即非主键列必须直接依赖于主键，不能存在传递依赖

就是数据只在一个地方存储，不重复出现在多张表中，可以认为就是消除传递依赖

比如，我们大学分了很多系（中文系、英语系、计算机系……），这个系别管理表信息有以下字段组成：系编号，系主任，系简介，系架构。那我们能不能在学生信息表添加系编号，系主任，系简介，系架构字段呢？不行的，因为这样就冗余了，非主键外的字段形成了依赖关系(依赖到学生信息表了)！正确的做法是：学生表就只能增加一个系编号字段。

第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于:

2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；
3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

如果换个方式来理解，这个问题就简单了，表是对象的集合。

• 1NF, 字段不可再分。这个关系数据库强制了，想建立复合的字段也建立不起来。关系数据库出现之前才有这个问题。
• 2NF，主键依赖，就是一张表里面的字段，必须是跟主键相关的，不能把无关的数据放进来。主键依赖，实质就是，这个信息如果是对象的属性，就放进来，否则就不放。
• 3NF，就是不能重复存储相同的信息。这个情况，其实是在一个对象里引用了另外一个对象，这个时候，存一个引用就够了，而不是重复的存储这个对象的多个副本。

2NF和3NF的本质是，对象的属性依赖对象。

作者：软件民工
链接：https://www.zhihu.com/question/24696366/answer/133284744
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

具体的例子见：如何理解关系模式三范式？

BC 范式

在关系模式中每一个决定因素都包含候选键，也就是说，只要属性或属性组A能够决定任何一个属性B，则A的子集中必须有候选键。BCNF范式排除了任何属性(不光是非主属性，2NF和3NF所限制的都是非主属性)对候选键的传递依赖与部分依赖。

比如我们有一个学生导师表，其中包含字段：学生ID，专业，导师，专业GPA，这其中学生ID和专业是联合主键。

StudentId Major Advisor MajGPA
1 人工智能 Edward 4.0
2 大数据 William 3.8
1 大数据 William 3.7
3 大数据 Joseph 4.0
这个表的设计满足三范式，有主键，不存在主键的部分依赖，不存在非主键的传递依赖。但是这里存在另一个依赖关系，“专业”函数依赖于“导师”，也就是说每个导师只做一个专业方面的导师，只要知道了是哪个导师，我们自然就知道是哪个专业的了。

所以这个表的部分主键依赖于非主键部分，那么我们可以进行以下的调整，拆分成2个表：

学生导师表：

StudentId Advisor MajGPA
1 Edward 4.0
2 William 3.8
1 William 3.7
3 Joseph 4.0
导师表：

Advisor Major
Edward 人工智能
William 大数据
Joseph 大数据

更多参考：一看就懂的数据库范式介绍（1NF,2NF,3NF,BC NF,4NF,5NF)

以下都是一些其他杂项，仅用于保存 。

SQL 约束有哪几种？

• NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
• UNIQUE: 控制字段内容不能重复，一个表允许有多个 Unique 约束。
• PRIMARY KEY: 也是用于控件字段内容不能重复不能为空，唯一确定一条记录。（联合与单个, auto_increment ）
• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
• CHECK: 用于控制字段的值范围。

主键约束，自赠约束，唯一约束，非空约束，默认约束(DEFAULT)，外键约束，check约束

主键的修改和删除

alter table user  add primary key(id);
alter table user  drop  primary key;
alter table user  modfiy id int   primary key;

SQL的四种连接查询

1. 内联连接

inner join或者join

内联查询，其实就是两张表中的数据，通过某个字段相对，查询出相关记录数据．

select *  from person inner join card on person.cardId = card.id;

2. 外连接

• 左连接:left join / left outer join

select *  from person left join card on person.cardId = card.id;

• 右连接:right join/right outer join
  

• 完全外连接:full join/ full outer join(mysql 不支持)

什么是索引？

索引是一种数据结构

索引的目地在于提高查询效率，类比字典

如果要查"mysql",通过索引就可以直接定位在"m"字母的范围，然后依次往下找就行了
如果没有索引．肯定就是要遍历了a-z

索引：快速查找+排序

缺点：

• 索引占空间
• 更新索引

索引的分类：

• 单值索引：一个索引只包含单个列，一个表可以有多个单列索引．
• 唯一索引：索引列的值必须唯一，允许有空值（主键自动创建唯一索引）
• 复合索引：一个索引包含多个列

单值索引

//创建索引
create [unique]  index indexName on tableName(columnname(collength));
// create [unique]  index indexName on tableName(col);
//如果是CHAR，VARCHAR类型，length可以小于字段实际长度；
//如果是BLOB和TEXT类型，必须指定 length。
//添加索引
alter table tableName add [UNIQUE] index indexName(indexName);
//–创建表的时候同时创建索引
 CREATE TABLE tb2 (
 id int(11) NOT NULL AUTO_INCREMENT ,
 title char(255) ,
 content varchar(20),
 time int DEFAULT NULL ,
 
 [UNIQUE] INDEX index_name (title(length))//重点在这里
 )
 //删除索引
 DROP INDEX index_name ON table
 
 show index from tablename ; \G(格式化)

唯一索引:（见上面）

复合索引(一般都是用这个):

create [unique]  index indexName on tableName(col1,col2);

将索引节点加载到内存，经过二分查找，找到下一阶段的物理位置，然后在经过一次IO，找到下一节点，最终找到叶子节点的数据．

创建索引的考虑：

SQL优化分析：

explain select *  from tablename
explain + SQL语句

如何查看隔离级别？

如何修改隔离级别？

set global transaction idolation level read  uncommitted 

---

1. 脏读:一个事务读取到另外一个事务未提交的数据(read uncommitted)

例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多。

实际开发中绝对不允许脏读

---

2. 不可重复读:一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改(read commmited)

注：A查询数据库得到数据，B去修改数据库的数据，导致A多次查询数据库的结果都不一样【危害：A每次查询的结果都是受B的影响的，那么A查询出来的信息就没有意思了】

---

3. 虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。(Repeatable read)

事务a和事务b同时操作一张表，事务a提交的数据　事务B select 查不出来，但是当insert 相同数据时就会报错！！！

---

4. 串行化：(Serializable)

不允许同时写入，直到另一端的事务 commit 完成

会存在等待超时

串行化三个字比较形象！！！！

---

性能：隔离级别越高，性能越差

mysql 默认：Repeatable read

---

参考：
https://www.bilibili.com/video/av39807944/?p=12