MySQL相关知识点

MySQL相关知识点（自我学习用）

一、MySQL存储引擎

常用的有InnoDB（默认）和MyISAM，区别如下：

	MyISAM	InnoDB
事务支持	不支持	支持
数据锁定	表锁	行锁
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为前者2倍

MyISAM：节约空间，速度较快
InnoDB： 安全性高、支持事务、可以多表多用户操作

二、关于外键

一般不建议使用数据库级别的外键(即在建表的时候就添加了外键约束)。数据库就是单纯的表，只用来存数据，只有行(数据)和列(字段)。当需要使用多张表的数据时，可以通过程序来实现。外键会使得每次在DELETE或者UPDATE数据时都必须考虑外键的约束，造成麻烦与不便。

三、索引

1、什么是索引

数据库索引，是数据库管理系统中一个排序的数据结构，索引的实现通常使用B树及其变种B+树。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
简单来说，索引就是数据结构

2、索引的作用及优点缺点

索引可以协助快速查询、更新数据库表中数据。但同时为表设置索引也是要付出代价的：一是增加了数据库的存储空间；二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

3、索引类型

普通索引（key）：仅加速查询
唯一索引（unique）：加速查询 + 列值唯一（可以有null）
主键索引（primary key）：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引（fulltext）：对文本的内容进行分词，进行搜索

4、索引使用B+树实现的原因
链接: link.
链接: link.
链接: link.
链接: link.

主要是为了减少磁盘IO次数，相比于对内存的操作，IO操作更耗时

我们都知道二叉查找树的查找的时间复杂度是Ｏ(log N)，其查找效率已经足够高了，那为什么还有Ｂ树和Ｂ＋树的出现呢？难道它两的时间复杂度比二叉查找树还小吗？

答案当然不是，Ｂ树和Ｂ＋树的出现是因为另外一个问题，那就是磁盘IO；众所周知，IO操作的效率很低，那么，当在大量数据存储中，查询时我们不能一下子将所有数据加载到内存中，只能逐一加载磁盘页，每个磁盘页对应树的节点。造成大量磁盘IO操作（最坏情况下为树的高度）。平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁，进而导致效率低下。
　　
所以，为了减少磁盘IO的次数，就必须降低树的深度，将“瘦高”的树变得“矮胖”。一个基本的想法就是：（1）、每个节点存储多个元素。（2）、摒弃二叉树结构，采用多叉树

B树和B+树的比较

①、B树每个节点都存储数据，所有节点组成这棵树。B+树只有叶子节点存储数据（B+数中有两个头指针：一个指向根节点，另一个指向关键字最小的叶节点），叶子节点包含了这棵树的所有数据，所有的叶子结点使用链表相连，便于区间查找和遍历，所有非叶节点起到索引作用。

②、B树中叶节点包含的关键字和其他节点包含的关键字是不重复的，B+树的索引项只包含对应子树的最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址。

③、B树中每个节点（非根节点）关键字个数的范围为[m/2(向上取整)-1,m-1] (根节点为[1,m-1])，并且具有n个关键字的节点包含（n+1）棵子树。B+树中每个节点（非根节点）关键字个数的范围为[m/2(向上取整),m] (根节点为[1,m])，具有n个关键字的节点包含（n）棵子树。

④、B+树中查找，无论查找是否成功，每次都是一条从根节点到叶节点的路径。

B树的优点:
B树的每一个节点都包含key和value（不仅存键还会存对应的数据），如果经常访问的元素离根节点近，那么访问就会更迅速。

B+树的优点：
①、所有的叶子结点使用链表相连，便于区间查找和遍历。B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
②、b+树的中间节点不保存数据，能容纳更多节点元素。

MyISAM和InnoDB实现上的区别：
链接: link.

MyISAM是非聚集索引：
引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。即MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

InnoDB聚集索引：
InnoDB也使用B+Tree作为索引结构，区别一是InnoDB的数据文件本身就是索引文件。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。区别二是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

聚集索引和非聚集索引区别

聚合索引(clustered index):
聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。

非聚合索引(nonclustered index):
非聚集索引指定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。

四、数据库事务
链接: link.

简单理解就是对数据库的一组操作，这些操作构成一个逻辑上的整体，要么都执行成功并提交、永久化对数据的改变至数据库；要么都不成功，回滚到数据修改前的状态。不能有的操作完成有的操作没完成。

1、事务四大特性（ACID）

原子性（Atomicity）:
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（Consistency）:
事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

隔离性（Isolation）:
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

持久性（Durability）:
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

2、并发控制与日志恢复

在事务的ACID特性中,C即一致性是事务的根本追求,而对数据一致性的破坏主要来自两个方面：
一是事务的并发执行
二是事务故障或系统故障

数据库系统是通过并发控制技术和日志恢复技术来避免这种情况发生的。

并发控制技术保证了事务的隔离性,使数据库的一致性状态不会因为并发执行的操作被破坏。日志恢复技术保证了事务的原子性,使一致性状态不会因事务或系统故障被破坏。同时使已提交的对数据库的修改不会因系统崩溃而丢失,保证了事务的持久性。

2.1、事务的并发控制

从理论上来说, 事务应该彼此完全隔离, 以避免并发事务所导致的问题，然而, 那样会对性能产生极大的影响, 因为事务必须按顺序运行， 在实际开发中, 为了提升性能, 事务会以较低的隔离级别运行， 事务的隔离级别可以通过隔离事务属性指定。

并发会导致的问题：
①、脏读：对于两个事务A、B，A读取了被B修改但还没有被B提交的字段，之后若B回滚，那么A读取的数据就是临时且无效的。
②、不可重复读：对于两个事务A、B，A读取了一个字段，然后这时B更新了该字段的值，A再次读取同一个字段，会发现字段值和之前读取的不同了
③、幻读：对于两个事务A、B，A读取了一个字段，然后B在该字段中插入了一些新的行。之后，A再次读取会发现数据多了几行
幻读是针对插入和删除的，不可重复读是针对修改的

数据库的隔离级别
从低到高依次是

读未提交(READ UNCOMMITTED)
读已提交(READ COMMITTED)
可重复读(REPEATABLE READ) （MySQL默认值）
串行化(SERIALIZABLE)

隔离级别	脏读	不可重复读	幻读
读未提交	X	X	X
读已提交	√	X	X
可重复读	√	√	X
串行化	√	√	√

2.2、事务隔离性的实现—常见的并发控制技术

并发控制技术是实现事务隔离性以及不同隔离级别的关键,实现方式有很多,按照其对可能冲突的操作采取的不同策略可以分为乐观并发控制和悲观并发控制两大类。

①、乐观并发控制（乐观锁）:对于并发执行可能冲突的操作,假定其不会真的冲突,允许并发执行,直到真正发生冲突时才去解决冲突,比如让事务回滚。包括： 基于有效性检查的并发控制、基于快照隔离的并发控制

②、悲观并发控制（悲观锁）:对于并发执行可能冲突的操作,假定其必定发生冲突,通过让事务等待(锁)或者中止(时间戳排序)的方式使并行的操作串行执行。包括：基于封锁的并发控制、基于时间戳的并发控制

基于封锁的并发控制：
核心思想:对于并发可能冲突的操作,比如读-写,写-读,写-写,通过锁使它们互斥执行。是一种悲观锁

锁通常分为共享锁和排他锁两种类型。
①、共享锁(S):事务T对数据A加共享锁,其他事务只能对A加共享锁但不能加排他锁。
②、排他锁(X):事务T对数据A加排他锁,其他事务对A既不能加共享锁也不能加排他锁

基于时间戳的并发控制：
核心思想:对于并发可能冲突的操作,基于时间戳排序规则选定某事务继续执行,其他事务回滚。是一种悲观锁

系统会在每个事务开始时赋予其一个时间戳,这个时间戳可以是系统时钟也可以是一个不断累加的计数器值,当事务回滚时会为其赋予一个新的时间戳，先开始的事务时间戳小于后开始事务的时间戳。

基于有效性检查的并发控制：
核心思想:事务对数据的更新首先在自己的工作空间进行,等到要写回数据库时才进行有效性检查,对不符合要求的事务进行回滚。

该方法允许可能冲突的操作并发执行,因为每个事务操作的都是自己工作空间的局部变量，直到有效性检查阶段发现了冲突才回滚。因而这是一种乐观的并发策略。

基于快照隔离的并发控制：
快照隔离是多版本并发控制(mvcc)的一种实现方式，其核心思想是:数据库为每个数据项维护多个版本(快照),每个事务只对属于自己的私有快照进行更新,在事务真正提交前进行有效性检查,使得事务正常提交更新或者失败回滚。

事务间可能冲突的操作通过数据项的不同版本的快照相互隔离,到真正要写入数据库时才进行冲突检测。因而这也是一种乐观并发控制。

2.3、故障与故障恢复技术

数据库运行过程中可能会出现故障,这些故障包括事务故障和系统故障两大类
事务故障：比如非法输入,系统出现死锁,导致事务无法继续执行。
系统故障：比如由于软件漏洞或硬件错误导致系统崩溃或中止。

这些故障可能会对事务和数据库状态造成破坏,因而必须提供一种技术来对各种故障进行恢复,保证数据库一致性,事务的原子性以及持久性。数据库通常以日志的方式记录数据库的操作从而在故障时进行恢复，因而可以称之为日志恢复技术。

五、MySQL锁机制
链接: link.
链接: link.
链接: link.

1、锁的分类

按锁的粒度划分，可分为表级锁、行级锁、页级锁（mysql）
按锁级别划分，可分为共享锁、排他锁
按使用方式划分，可分为乐观锁、悲观锁

①、表级锁（偏向于读）
优缺点：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
支持引擎：MyISAM
表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

②、行级锁
优缺点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
支持引擎：InnoDB
行级锁定分为行共享读锁（共享锁）与行独占写锁（排他锁）

③、页级锁
对于行级锁与表级锁的折中，开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般