Mysql数据库学习笔记

Mysql数据库的底层引擎有很多种，下面介绍两种最常见的

InnoDB和MyiSAM存储引擎

InnoDB，是基于B+树，聚类索引

表是索引组织表，即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+树（以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引。）

（一张学生表，以学号为主键，学号的键值是有序的，按下面的1-15）

最后一层是叶子节点，保存索引行的行数据，也就是说，找到主键索引，就直接找到行数据

因为表只有一个主键，因此一个表只能包含一个聚集索引该索引可以包含多个列（组合索引）

覆盖索引，索引包含了需要查询的字段的值，比如说按主键是（姓名+身高），聚类索引就是姓名和身高，那么如果到第二层的时候就找到了，那么就直接返回，不需要往叶子节点找了。

也就是说，索引包含了需要查询的所有字段。

非聚集索引（非聚簇索引，辅助索引）：以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

也就是说，一张表的主键是学号，用身高和姓名建立一个索引表，，通过身高和姓名找到主键，之后回表，根据主键找到数据行。

MyISAM 同样采用B+树索引，其2中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中，B+ 树索引的叶子节点并不存储数据，而是存储数据的文件地址。

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

区别

InnoDB是聚类索引的B+tree构建的数据引擎，其中叶子节点存放数据

SyiSAM是索引和数据分开存储，非聚类索引的B+Tree的叶子节点存放数据的地址，然后到数据文件中去找数据。

ACID

ACID是事务的4个特性，而InnoDB才支持事务，SyiSAM不支持事务。所以以下都是基于InnoDB的

事务操作共三个

start transaction，commit，rollback

开始事务，提交事务，回滚

当sql语句执行出现问题会rollback回滚。mysql默认auto commit，自动提交，意味着每个sql都是一个事务

通过 set autocommit=0取消自动提交

ACID

原子性，atomicity，事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行

一致性，consistency，在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。

隔离性，isolation，数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

持久性，durability，事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

ACD三个特性是通过Redo log（重做日志）和Undo log 实现的。 而隔离性是通过锁来实现的。

InnoDB默认事务隔离级别是可重复读，不满足隔离性

InnoDB提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)。其中redo log用于保证事务持久性D；undo log则是事务原子性和隔离性实现的基础。

首先是原子性A

当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

接着是持久性D

首先，InnoDB提供了缓存(Buffer Pool)，存在内存中，当修改数据时，会改缓存(Buffer Pool)，缓存会定期存到磁盘。但是如果如果忽然断电，BufferPool就没了，

所以，InnoDB提供了Redo Log。每次修改数据时，会把修改记录写入Redo log写入磁盘，如果断电了，可以根据Redo log来重新修改数据库。

隔离性

与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响

仅考虑最简单的读操作和写操作(暂时不考虑带锁读等特殊操作)，那么隔离性的探讨，主要可以分为两个方面：

1，(一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
2，(一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

锁机制，对数据操作时，需要获得锁才能进行操作。

并发情况下，读操作可能存在的三类问题

（1）脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），

（2）不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，

（3）幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同

1，脏读，读到其它事务未提交的数据，是脏数据

2，不可重复读，读两次数据，中间其他事务提交，zo导致后面的数据是脏的，不一致

3，幻读，读取两次数据，第二次读取的数据和第一次一样，但是行数不一样，可能会多一些内容

可重复读仅能保证再次执行同样的查询时，先前返回【过】的结果一定跟之前一样，而不保证会不会多出别的记录。如果返回了之前没有出现过的记录，就是幻读。

MVCC（Multi-Version Concurrency Control，即多版本并发控制）

MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的