MySQL总结

-------------------------------数据库事务------------------------------

什么是事务？

事务是将所有对数据的操作（curd）看作是一个整体，作为一个整体就会要么操作完全成功，要么失败。失败回滚到原来的状态。由于并发操作，为了保证数据的一致性，保证事务的完整性。

数据库事务的四个特性？

原子性：要么成功、要么失败，因此要么成功并且完全应用到数据库，要么回滚并且对数据库没有任何影响。
一致性：数据库从一种一致性状态转变到另外一种一致性状态，比如：银行转账，A转给B
50以后，那么A减少50，B增加50。 即：数据库中的事务满足完整性约束。
隔离性：一个事务的执行不影响其他事务的执行，如多个用户并发访问一个数据库。
持久性：事务一旦提交，就会永久性的存储在数据库中。

如果不考虑隔离性，并发事务会出现以下的问题？

脏读：B查询A修改后未提交的数据，当A回滚后，则B的查询是无效的
不可重复读：A第一次查询甲的信息，B修改了甲的信息并且提交，A再次获取甲的信息的时候，A两次获取的信息不同称为不可重复读。
幻读：类似于不可重复读，A查询C表的字段数，B此时增加或者删除C表的字段数并且提交，A两次获取的字段数不一样
不可重复读和幻读的区别在于：不可重复读强调的是信息的改变（update），而幻读则是强调数量上的改变（insert、delete）。

事务的隔离级别？

为了防止以上情况而产生的脏读、幻读、不可重复读
读未提交：最低的级别，任何情况都无法保证。可以读未提交的内容， 可能会产生脏读、幻读、不可重复读。
读已提交：可以避免脏读。读已经提交的内容。
可重复读：可以避免脏读、不可重复读。当事务启动的时候就不允许“update”操作，由此就不会产生不可重读。但是却不能改变“insert、delete”的幻读。
串行化：可以避免脏读、不可重复读、幻读。串行化处理顺序执行
总结：级别越高，效率越低。串行化几乎不用，mysql有四种隔离级别，默认可重复读。
Oracle只支持串行化和读已提交，默认的是读已提交。

可重复读应用于什么场景？

目前都需要实时数据，即两次读取的数据是不一样的。那么不可重读又是适用于什么场景呢？比如我们需要12：00的数据是1000，如果是12：01读就并不是1000了，所以这里需要禁止不可重复读。

--------------------------数据库锁 -------------------------------------

MVCC实现原理

MVCC多版本并发控制：行级锁的一种变种，读写不阻塞，避免了加锁操作。
MVCC的实现通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的（细细品一品）。
MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

• 创建版本号：指示创建一个数据行的快照时的系统版本号；
• 删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了。

实现过程
以下实现过程针对可重复读隔离级别。
当开始一个事务时，该事务的版本号肯定大于当前所有数据行快照的创建版本号，理解这一点很关键。数据行快照的 创建版本号是创建数据行快照时的系统版本号，系统版本号随着创建事务而递增，因此新创建一个事务时，这个事务 的系统版本号比之前的系统版本号都大，也就是比所有数据行快照的创建版本号都大。

1. SELECT
   InnoDB会根据以下两个条件检查每行记录：

• ①InnoDB只查找创建版本号小于或者等于当前事务版本号的数据行（，这 样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的
• ②删除版本号要么未定义，要么大于当前事务版本号（证明其他事务删除了这条数据，为了保证可重复读仍然可以读取到）。这可以确保事务读取到的行，在事务开始之前未被删除。
  只有符合上述两个条件的记录，才能返回作为查询结果

2. INSERT
   将当前系统版本号作为数据行快照的创建版本号。
3. DELETE
   将当前系统版本号作为数据行快照的删除版本号。
4. UPDATE
   将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本 号。可以理解为先执行 DELETE 后执行 INSERT

读写锁
InnoDB实现了以下两种类型的行锁。

• 共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

也叫做读锁：读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。

• 排他锁（X锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

也叫做写锁：写锁是排他的，写锁会阻塞其他的写锁和读锁。

三级封锁协议
一级封锁协议：事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。
二级封锁协议：在一级封锁协议的基础上，事务T要读取数据 A 时必须加 S 锁，，读取完才释放锁。可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。
三级封锁协议：在二级封锁协议的基础上，事务T要读取数据 A 时必须加 S 锁，当事务结束时才会释放锁。可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。
二级协议和三级协议：一个是读完就释放锁，一个是等事务结束才释放锁，这样就是读已提交和可重复读的隔离级别。
间隙锁
当我们用范围条件检索数据而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁
InnoDB使用间隙锁的目的 ：

• 为了防止幻读(上面也说了， Repeatableread隔离级别下再通过GAP锁即可避免了幻读)

MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读

高性能MySQL:08—事务之多版本并发控制（MVCC）

Myisam和Innodb中搜索引擎的不同？

相同点：都是使用B+树作为底层索引的数据结构。
不同点：

• Innodb是聚集索引，索引和数据文件不是分离的。 B+树索引结构的叶子节点存储的数据记录。Myisam是非聚集索引，索引和数据文件是分离的。B+树索引结构的叶子节点存储的是数据记录的地址。
• Innodb支持行锁和表锁，默认支持行锁。Myisam支持表锁
• Innodb支持事务，Myisam不支持事务
• Innodb默认是有主键，Myisam可以不使用主键

如何选择：

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。

InnoDB关键特性插入缓冲(Insert Buffer)、两次写(doublewrite)、自适应哈希索引(AHI)、预读。
InnoDB关键特性

---------------------------索引-----------------------------------------------

索引

可以分为聚集索引和非聚集索引

聚集索引和非聚集索引的区别在于叶子节点是存放的是主键还是数据记录。聚集索引的叶子节点存放的是数据记录。非聚集索引的叶子的节点存放的是主键和列值.。同时非聚集需要通过回表来定位数据记录。
分类：唯一索引、主键索引、普通索引、联合索引。

主键索引和聚集索引的区别?
创建主键的时候，如果没有聚集索引且没有指定聚集索引列，那么这个主键索引就会当作聚集索引。

最左匹配原则

联合索引的顺序是比较重要的，决定了最左匹配原则。
（a、b、c、d）四个列组成的联合索引。当遇到>、<、between等范围查找时，后面的索引就会无效。比如（a=1,b=3,c>4,d=5）d=5就不会往下匹配了。
还有当（b=2，c=2，d=3）时这个索引也是无效的。因为最左边的索引没有使用。
最左原理以及相关优化

---------------------------------索引的数据结构--------------------------

如果MySQL里的数据不用索引那会是什么样子呢？

Mysql的数据存储是页，每条记录存在页里面(硬盘和内存之间交互也是以页为单位)

由上图可以看出，记录是插在页里，也就是我们的数据会以记录的形式放在页里。
页和页之间是双端链表
每个页里面的记录是以**单链表（排序的）**的形式组成

• 所以说每个页上都会一个页目录（key），当我们查找某个记录的时候先定位页，然后在通过二分法查找定位到某条记录。
• 如果以非主键作为搜索条件，那么将不会使用索引，而是依次遍历单链表中的每条记录。

所以说，如果我们写 select * from user where username =’ Java '这样没有进行任何优化的sql语句，默认会这样做：

定位到记录所在的页

• 需要遍历双向链表，找到所在的页
  从所在的页内中查找相应的记录
• 由于不是根据主键查询，只能遍历所在页的单链表了

数据库的索引和锁到底是怎么回事

为什么Mysql中Innodb的索引结构采取B+树？

为什么使用二叉查找树、红黑树、平衡二叉树呢？

二叉查找树、红黑树、平衡二叉树虽然有很高的查询效率，但是数据都是存储在硬盘上的，考虑效率的同时也要考虑磁盘IO次数。
比如一颗二叉树存放1亿个数据（2^27-1），大于需要27层。如果说在最坏情况下会有27次磁盘IO。
而B树/B+的每个节点可以存放多个数据，那么放一亿个数据只需要2层（阶数为1024）。

为什么不适用B树？

从结构上分析：
B树每个节点都存放key和data。而B+树的data只存在叶子节点上，同时叶子节点之间以单链表且排序的方式连接。
从查询上分析：
由于其数据结构上不同，所以使用的方式也不同。B树比较适合单一查询，而不能适用于范围查询，也不适合做遍历操作。而B+树的数据在叶子节点上，同时以单链表排序的形式连接。比较适合范围查询以及遍历。
关系型数据库（Mysql）对遍历操作和范围查询要求较高些。

B树真的一无是处吗？

B树也有优点就是单一查询性能较B+树较为优秀。目前MongoDB这种非关系型数据库使用B树作为索引。只是非关系型数据库对遍历操作的需求较少，对单个查询的需求相对较多。
由于内存和硬盘以页为单位交换数据，那么我们把一个树结点的大小设计成一个页。 这样设计每次就可以通过一次磁盘IO就可以把该结点载入。如果说树的阶数是1024，那么从600亿个数据查询某个数据也只需要不到4次的磁盘IO。
另外一点有时B树的磁盘IO次数要小于B+树。但是差别不是很大。

MySQL中Innodb的索引结构采取B+树？

不管是B树还是B+树都在获得查询性能的同时减少了磁盘IO的次数。只是B+树在结构上设计有利于遍历。
Mysql索引用B+树，而Mongodb索引用B树
mysql索引的认识
【经典数据结构】B树与B+树
MySQL索引背后的数据结构及算法原理

--------------------------------------------------------------------------------

数据库三大范式

第一范式：列是不可分割的，也就是原子性的
第二范式：非主属性依赖于主属性。例如:订单表(订单编号、产品编号、定购日期、价格、……)，"订单编号"为主键，"产品编号"和主键列没有直接的关系，即"产品编号"列不依赖于主键列，应删除该列。
第三范式：不存在数据依赖，消除冗余。:订单表(订单编号，定购日期，顾客编号，顾客姓名，……)，初看该表没有问题，满足第二范式，每列都和主键列"订单编号"相关，再细看你会发现"顾客姓名"和"顾客编号"相关，"顾客编号"和"订单编号"又相关，最后经过传递依赖，"顾客姓名"也和"订单编号"相关。为了满足第三范式，应去掉"顾客姓名"列，放入客户表中。