范式 #
第一范式（无重复的列）
无重复的列，同一列不能有多个值或者不能有重复的属性
数据库表中的所有字段值都是不可分解的原子值
第二范式（属性完全依赖于主键）
在第一范式的基础上建立，要求数据库表中的每个实例或行必须可以被唯一的区分。为实现通常为表加上一个列，存储各个实例的唯一标识，如 id
确保数据库表中的每一列都和主键相关
第三范式（属性不依赖于其他非主属性）
必须先满足第二范式，要求一个数据库表中不包含已在其他表中已包含的非主关键字信息
确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
关系型数据库和非关系型数据库 #
- 关系型数据库
- 保持了数据一致性
- 更新开销小
- 支持复杂查询（where 字句的查询）
- 非关系型
- 不需要经过 SQL 的解析，读写效率高
- 基于键值对，数据扩展性好
- 支持多种类型数据的存储
MySql #
逻辑架构和存储引擎 #
- 第一层：处理客户连、授权认证等
- 第二层：服务器层，负责查询语句的解析优化、缓存以及内置函数的实现、存储过程等
- 第三层：存储引擎，负责 MySql 中数据的存储和提取。服务器层不管理事务，事务由存储引擎实现
InnoDB 体系结构 #
后台线程
-
Master Thread
主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲（Insert Buffer）、Undo 页的回收
-
IO thread
InnoDB 大量使用 Async IO 来处理写 IO 的请求，而 IO Thread 的工作主要负责这些 IO 的回调。
-
Purge Thread
事务被提交后，其所使用的 undo log 可能不再需要了，purge thread 来回收已经使用并分配的 undo 页。在 InnoDB 1.1 版本之前 undo 页的回收也就是 purge 操作都是在 master thread 中进行的，InnoDB1.1 版本之后，就分配到 Purge Thread 中执行
-
Page Cleaner Thread
将原本放在 master thread 中进行的脏页刷新操作放到了单独的线程中
内存池
-
缓冲池
缓存磁盘上的热点数据，用来解决类似 CPU 快于主存，中间加了层高速缓存的设计。
读取操作：
在数据库中进行读取页的操作，首先从磁盘上读取到的页放在缓冲池中，这个过程称为 FIX 在缓存池中，下一次再调用相同的页时，判断该页是否在缓存池中，如果在就直接取缓存池中该页的数据。
修改操作：
对数据库中进行修改，首先修改缓存池中的页，然后再以一定的频率刷新到磁盘上。（Check Point）
通过 LRU 算法来进行管理。最频繁使用的页在 LRU 列表的最前端，最少使用的页在 LRU 列表的最末端。优化：InnoDB 引擎加入了 midpoint 位置，新读取到的页尽管是最近访问的页名单不是直接放到 LRU 列表的首部，而是 midpoint 位置，midpoint 之前为热端，之后为冷端。（如果加入首端可能把很频繁的内容冲掉）InnoDB 有个参数 innodb_old_blocks_time，表示读取到 mid 位置后的冷端数据要等待多久才会被加入到热端中，这个操作被官方定义为 page made young。
数据库刚启动时，LRU 列表是空的，内存中的空闲页都会被放置在 free 列表中，缓存数据时，LRU 先是询问 free 列表中有没有空闲的页，如果有，free 列表中就移出该页，将该页添加到 LRU 列表中，当 free 列表中没有空闲的页时，就淘汰 LRU 列表末尾的页。
-
重做日志缓冲
在执行事务开始，首先将重做日志信息先放入到这个缓冲区中，然后按照一定的频率刷新到磁盘中，重做日志缓冲一般都不需要设置的很大，因为一般情况下每一秒都会将重做日志缓冲刷新到磁盘中，所以只需要保证每一秒产生的事务量控制在这个缓冲大小即可，默认为 8MB。
重做日志缓冲有三种情况会从缓存中刷入到硬盘
- master thread 每一秒都会将重做日志缓冲刷新到磁盘中
- 事务提交时会将重做日志缓冲刷新到磁盘中
- 当重做日志缓冲池的剩余空间少于一半时，重做日志缓冲也会刷新到磁盘中
-
额外的内存池
在 innodb 引擎中需要对一些数据结构本身进行内存分配时，需要从额外的内存池中进行申请，当额外的内存池不够时，会从缓冲池中申请。
InnoDB 与 MyISAM 比较 #
-
事务：InnoDB 是事务型，可以使用 Commit 和 Rollback 语句
-
并发：MyISAM 只支持表级锁，InnoDB 还支持行级锁
-
外键：InnoDB 支持外键
-
备份：InnoDB 支持在线热备份
-
崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高，且恢复速度更慢
-
其他特性：MyISAM 支持压缩表和空间数据索引
-
InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
-
==使用场景==：
MyISAM 管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的 SELECT 查询，那么 MyISAM 是更好的选择。
InnoDB 用于事务处理应用程序，具有众多特性，包括 ACID 事务支持。如果应用中需要执行大量的 INSERT 或 UPDATE 操作，则应该使用 InnoDB，这样可以提高多用户并发操作的性能。
B+树 #
InnoDB 、MyIsam 都使用 B+ 树作为索引结构
二叉搜索树（BST）：不平衡
可能退化为链表，时间复杂度退化为 O(n)
平衡二叉树（AVL）：旋转耗时
当删除数据时，会导致树失衡，AVL需要维护从被删除节点到根节点这条路径上所有节点的平衡，旋转的量级为O(lgn)。由于旋转的耗时，AVL树在删除数据时效率很低；在删除操作较多时，维护平衡所需的代价可能高于其带来的好处
红黑树：树太高
与AVL树相比，红黑树的查询效率会有所下降，这是因为树的平衡性变差，高度更高。但红黑树的删除效率大大提高。当插入或删除数据时，只需要进行O(1)次数的旋转以及变色就能保证基本的平衡。
对于数据在内存中的情况，红黑树的表现是非常优异的。但是对于数据在磁盘等辅助存储设备中的情况（如MySQL等数据库），红黑树并不擅长，因为红黑树还是太高了。当数据在磁盘中时，磁盘IO会成为最大的性能瓶颈，设计的目标应该是尽量减少IO次数；而树的高度越高，增删改查所需要的IO次数也越多，会严重影响性能。
B树
为磁盘等辅存设备设计的多路平衡查找树，与二叉树相比，B树的每个非叶节点可以有多个子树
一颗 m 阶B树，需要满足以下条件：
- 每个节点最多包含 m 个子节点。
- 如果根节点包含子节点，则至少包含 2 个子节点；除根节点外，每个非叶节点至少包含 m/2 个子节点。
- 拥有 k 个子节点的非叶节点将包含 k - 1 条记录。
- 所有叶节点都在同一层中。
访问局部性原理：当一个数据被使用时，其附近的数据有较大概率在短时间内被使用。B树将键相近的数据存储在同一个节点，当访问其中某个数据时，数据库会将该整个节点读到缓存中；当它临近的数据紧接着被访问时，可以直接在缓存中读取，无需进行磁盘IO；换句话说，B树的缓存命中率更高。
==B+树==
O(h)=O(logdN)_
与 B 树区别：
- B树中每个节点（包括叶节点和非叶节点）都存储真实的数据，B+树中只有叶子节点存储真实的数据，非叶节点只存储键。
- B树中一条记录只会出现一次，不会重复出现，而B+树的键则可能重复重现——一定会在叶节点出现，也可能在非叶节点重复出现。
- B+树的叶节点之间通过双向链表链接。
- B树中的非叶节点，记录数比子节点个数少1；而B+树中记录数与子节点个数相同。
优势
- 更少的IO：B+树的非叶节点只包含键，而不包含真实数据，因此每个节点存储的记录个数比B树多很多（即阶m更大），因此B+树的高度更低，访问时所需要的IO次数更少。此外，由于每个节点存储的记录数更多，所以对访问局部性原理的利用更好，缓存命中率更高。
- 更适于范围查询：在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可。
- **更稳定的查询效率：**B树的查询时间复杂度在1到树高之间(分别对应记录在根节点和叶节点)，而B+树的查询复杂度则稳定为树高，因为所有数据都在叶节点。
劣势：
键会重复出现，因此会占用更多的空间。
索引 #
聚集索引 #
聚集索引和非聚集索引的区别
-
聚集索引一个表只有一个，非聚集索引可以有多个
-
聚集索引的存储记录是物理上连续存在，非聚集索引是逻辑上的连续，物理存储不连续
-
聚集索引：物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序
非聚集索引：物理存储不按照索引排序；是普通索引，仅仅对数据列创建相应的索引，不影响整个表的物理存储顺序。
-
索引的叶节点就是数据节点，叶子节点存放的是一整行的信息。
聚集索引缺点
- 插入速度严重依赖插入顺序
- 更新聚簇索引代价高 ，会导致页分裂
- 导致全表扫描变慢，尤其是行比较稀疏或由于页分裂导致数据存储不连续的时候。
- 二级索引需要两次查找，而不是一次，因为二级索引中保存的不是指向行的物理位置的指针，而是行的主键值。意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对应的主键值，然后再根据这个主键值去聚簇索引中查找到对应的行。
MyISAM 索引实现 #
使用 B+ 树作为索引结果，叶节点的 data 域存放的是数据记录的地址。
主键索引：
辅助索引：
在 MyISAM 中，主索引和辅助索引在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复。
MyISAM 的索引方式为非聚集
MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。
InnoDB 索引实现 #
主键索引：
==树的叶节点 data 域保存了完整的数据记录==，这种索引叫做聚集索引。
InnoDB 对于主键索引的查找非常快，一般树高 2-4
因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键( MyISAM 可以没有)，如果没有显式指定，则 mysql 会自动选择一个可以唯一标识数据记录的列作为主键。如果不存在这种列，则 mysql 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整型。
辅助索引：
InnoDB 的所有辅助索引都引用主键作为 data 域。
辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
==为什么不建议使用过长的字段作为主键==
因为所有的辅助索引都引用主索引，过长的主索引会令辅助索引变得过大
==为什么非单调的字段作为主键在 InnoDB 中不是好主意==
InnoDB 数据本身是一颗 B+ 树，非单调的主键会造成在插入新数据时数据文件为了维持 B+ 树的特性而频繁的分裂调整，十分低效，使用自增字段作为主键是很好的选择
B+ 树索引并不能找到一个给定键值的具体行，B+ 树索引能找到的只是被查找数据行所在的页，然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据
覆盖索引 #
当 sql 语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引中 （联合索引），可以直接使用索引查询而不需要回表。
优势：
-
索引条目通常远远小于数据行大小，如果只需要度索引，则大大减少数据访问量
-
覆盖索引对于 I/O 密集型应用很有帮助，因为索引比数据更小，更容易存入内存中
-
对于 myISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此访问数据需要一次系统调用。
-
避免 innodb 引擎的回表查询
联合索引 #
指多个字段上创建的索引，只有在查询条件中使用了创建索引时 的第一个字段，索引菜户被使用。使用组合索引时遵循最左前缀集合
通常由多个 and 或 or 条件时需要联合索引
==最左前缀匹配==
将选择性最高的列放在索引最前列
前缀索引 #
当要索引的列字符很多时 索引则会很大且变慢，可以只索引列开始的部分字符串 节约索引空间 从而提高索引效率 ，但是会降低索引的选择性。
索引选择性： 不重复的索引值 / 数据表的记录总数
数据表的选择性越高，查询效率越高
计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。
ALTER TABLE demo ADD KEY(text(5)); # 创建前缀索引
缺点：
- 无法做覆盖扫描
- 无法使用
ORDER BY和GROUP BY
使用场景： 针对很长的十六进制唯一 ID 使用前缀索引
主键索引 #
与唯一索引的的区别：
- 唯一索引允许为 null，而主键列不允许为 null，所以主键索引的查询效率特别高
一张表最多建立一个主键，也可以不建立主键
*主键 ：*能够通过某个字段唯一区分出不同过得记录，这个字段称为主键。不使用任何业务相关的字段作为主键，比如身份证、学号等，最好使用自增整数类型
唯一索引 #
在表上一个或者多个字段组合建立的索引，这个字段的值组合起来在表中不可以重复，一张表可以建立任意多个唯一索引，但是一般只建立一个。
==唯一索引和普通索引的区别：==
应尽可能使用普通索引
-
查询过程：
- 普通索引：查到满足条件的第一个记录后，继续查找下一个记录，直到第一个不满足条件的记录
- 唯一索引：由于索引唯一性，查到第一个满足条件的记录后，停止检索
而这性能差距微乎其微，因为 InnoDB 根据数据页来读写
-
更新过程
唯一索引的更新不能使用 change buffer
==change buffer== ：当需要更新一个数据页，如果数据页在内存中就直接更新，如果不在内存中，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，下次查询需要访问这个数据页的时候，将数据也读入缓存中，然后执行 change buffer 中的这个页有关的操作。
change buffer 减少了随机磁盘访问，对更新性能的提升很明显
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时使用效果最好（账单、日志）
redo log 主要节省随机写磁盘的 I/O 消耗，change buffer 主要节省的则是随机读磁盘的 I/O 消耗
change buffer 不会影响持久性，更改 change buffer 时也会写 redo log
全文索引 #
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。
更像是一个搜索引擎，配合 match against 操作使用，而不是一般的 where 语句加 like
只有 char varchar text 列上可以创建全文索引。在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用 CREATE index 创建 fulltext 索引，要比先为一张表建立 fulltext 然后再将数据写入的速度快很多。
ACID #
- 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
- 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
- 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
- 持久性（Durability）：一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。
InnoDB 默认事务隔离级别是可重复读，不满足隔离性
原子性 #
指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个 sql 语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。
undo log
当事务回滚时能够撤销所有已经成功执行的 sql 语句。InnoDB 实现回滚，靠的是 undo log：当事务对数据库进行修改时，InnoDB 会生成对应的 undo log；如果事务执行失败或调用了 rollback，导致事务需要回滚，便可以利用 undo log 中的信息将数据回滚到修改之前的样子。
undo log 属于逻辑日志，它记录的是 sql 执行相关的信息。当发生回滚时，InnoDB 会根据 undo log 的内容做与之前相反的工作
持久性 #
事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
redo log
每次读写数据都需要磁盘 IO，效率会很低。为此，InnoDB 提供了缓存(Buffer Pool)，Buffer Pool 中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从 Buffer Pool 中读取，如果 Buffer Pool 中没有，则从磁盘读取后放入 Buffer Pool ；当向数据库写入数据时，会首先写入 Buffer Pool，Buffer Pool 中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。
**问题：**如果宕机，此时 Buffer Pool 中的数据还没有刷新到磁盘，就会导致数据的丢失，持久性就无法保证
当数据修改时，除了修改 Buffer Pool 中的数据，还会在 redo log 记录这次操作；当事务提交时，会调用 fsync 接口对 redo log 进行刷盘。
除了事务提交时，还有其他刷盘时机：如master thread每秒刷盘一次redo log等，这样的好处是不一定要等到commit时刷盘，commit速度大大加快。
为什么 redo log 比直接将 Buffer Pool 中修改的数据写入到磁盘（刷脏）要快
- 刷脏是随机 IO，灭磁修改的数据位置随机，redo log 是追加操作，属于顺序 IO
- 刷脏是以数据页（page）为单位，MySql 默认页大小为16KB，一个Page 上的修改都要整页写入，而 redo log 中只包含真正需要写入的部分，无效 IO 大大减少
redo log 与 bin log
binlog（二进制日志）也可以记录写操作并用于数据的恢复
- 作用不同：redo log是用于 crash recovery 的，保证 MySQL 宕机也不会影响持久性；binlog 是用于 point-in-time recovery 的，保证服务器可以基于时间点恢复数据，此外 binlog 还用于主从复制。
- 层次不同：redo log是 InnoDB 存储引擎实现的，而 binlog 是 MySQL 的服务器层实现的，同时支持 InnoDB 和其他存储引擎。
- 内容不同：redo log 是物理日志，内容基于磁盘的 Page；binlog的内容是二进制的，根据 binlog_format 参数的不同，可能基于 sql 语句、基于数据本身或者二者的混合。
- 写入时机不同：binlog 在事务提交时写入；redo log 的写入时机相对多元：
隔离性 #
事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。严格的隔离性，对应了事务隔离级别中的 Serializable (可串行化)，但实际应用中出于性能方面的考虑很少会使用可串行化。
- (一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
- (一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性
事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。
表锁在操作数据时会锁定整张表，并发性能较差；行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL 中不同的存储引擎支持的锁是不一样的，例如 MyIsam 只支持表锁，而 InnoDB 同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。
MVCC #
Multi-Version Concurrency Control，即多版本的并发控制协议。
在T5时刻，事务A和事务C可以读取到不同版本的数据。
在SQL标准中，RR是无法避免幻读问题的，但是 InnoDB 实现的RR避免了幻读问题。（快照读的情况下通过 MVCC 避免，当前读的情况下通过 next-key）
MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和 CopyOnWrite 类似。
在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照。
MVCC 最大的优点是读不加锁，因此读写不冲突，并发性能好。
-
隐藏列：InnoDB 中每行数据都有隐藏列，隐藏列中包含了本行数据的事务 id、指向 undo log 的指针等。
-
基于 undo log 的版本链：前面说到每行数据的隐藏列中包含了指向 undo log 的指针，而每条 undo log 也会指向更早版本的 undo log，从而形成一条版本链。
-
ReadView：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本；但是具体要恢复到哪个版本，则需要根据 ReadView 来确定。所谓 ReadView，是指事务（记做事务A）在某一时刻给整个事务系统（trx_sys）打快照，之后再进行读操作时，会将读取到的数据中的事务 id 与 trx_sys 快照比较，从而判断数据对该 ReadView 是否可见，即对事务A是否可见。
**ReadView 流程：**MVCC 维护了一个 ReadView 结构，主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, …}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。
在进行 SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和 TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：
- TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
- TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的，因此不可使用。
- TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：
- 提交读：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。
- 可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。
在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。
按照是否加锁，MySQL的读可以分为两种：
-
一种是非加锁读，也称作快照读、一致性读，使用普通的select语句，这种情况下使用 MVCC 避免了脏读、不可重复读、幻读，保证了隔离性。
-
另一种是加锁读，查询语句有所不同。加锁读在查询时会对查询的数据加锁（共享锁或排它锁）。由于锁的特性，当某事务对数据进行加锁读后，其他事务无法对数据进行写操作，因此可以避免脏读和不可重复读。而避免幻读，则需要通过 next-key lock。**next-key lock是行锁的一种，实现相当于record lock(记录锁) + gap lock(间隙锁)；其特点是不仅会锁住记录本身(record lock的功能)，还会锁定一个范围(gap lock的功能)。**因此，加锁读同样可以避免脏读、不可重复读和幻读，保证隔离性。
一致性 #
事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。
前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。
事务 #
一个数据库事务通常包含对数据库进行读或写的一个操作序列。
存在包含有以下两个目的：
- 为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
- 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。
事务应该具有4个属性：原子性、一致性、隔离性、持久性。ACID
并发问题 #
- 更新丢失 lost update：两个或多个事物同时选择同一行，然后基于最初选定的值更新该行时，每个事务不知道其它事务存在，发生丢失更新的问题（最后的更新覆盖了其它事务所做的更新）
- 脏读 dirty reads：事务A读到事务B已修改但尚未提交的数据，并在这个数据基础上做了操作，此时如果B事务回滚，A读的数据无效，不符合一致性要求
- 不可重复读 Non-Repeatable Reads：一个事务再次读取以前读过的数据，发现读出的数据已经修改过，即，事务A读到了事务B已经提交的修改数据，不符合隔离性
- 幻读 Phantom Reads：事务A读取到了事务B提交的新增数据。与不可重复读区别：前者数据行数变了，后者数据变了
隔离级别 #
| 读数据一致性及允许的并发副作用 隔离级别 |
读数据一致性 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| 未提交读（Read uncommitted） | 最低级别，只保证不读取物理上损坏的数据 | 是 | 是 | 是 |
| 已提交读（Read committed） | 语句级 | 否 | 是 | 是 |
| 可重复读（Repeatable read）（默认） | 事务级 | 否 | 否 | 是 |
| 可序列化（Serializable） | 最高级别，事务级 | 否 | 否 | 否 |
锁 #
三级加锁协议 #
排它锁（X 锁）：事务T对数据A加上X锁时,只允许事务T读取和修改数据A
共享锁（S 锁）：事务T对数据A加上S锁时,其他事务只能再对数据A加S锁,而不能加X锁,直到T释放A上的S锁。
-
一级加锁协议
事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。
可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。
-
二级加锁协议
在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。
可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级加锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。
-
三级加锁协议
在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。
可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。
两阶段锁 #
- 扩展阶段：事务在对任何数据进行读、写操作之前，首先要申请并获得对该数据的封锁
- 收缩阶段：事务在释放一个封锁之后，不再申请和获得其他任何封锁
隐式与显式锁定 #
隐式锁定：InnoDB 采用两阶段锁协议，会根据隔离级别在需要的时候自动加锁，所有的锁在同一时刻被释放
InnoDB 也可以使用特定的语句进行显示锁定：
SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;
乐观锁与悲观锁 #
乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题。
乐观锁：“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。
悲观锁：“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。
Next-Key 锁 #
记录锁 Record Locks #
锁定一个记录上的索引，而不是记录本身
如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。
间隙锁 Gap Locks #
锁定索引之间的间隙，但是不包含索引本身。
临键锁 Next-Key Locks #
记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。避免幻读
MySQL 死锁 #
两个或多个事务在同一资源上互相占用，并请求锁定对方占用的资源，从而导致恶性循环
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB 存储引擎能检测到死锁的循环依赖并立即返回一个错误。
采用 wait-for graph（等待图）的方法进行死锁检测。 InnoDB保存以下两种信息：
- 锁的信息链表
- 事物等待链表
通过上述链表可以构造一张图，而如果在这张图中存在回路，就代表存在死锁，因此资源间发生相互等待。
wait-for graph 是一种较为主动的死锁检测机制，若存在死锁，InnoDB 存储引擎会选择回滚 undo 量最小的事物。
wait-for graph 通常采用深度优先搜索算法实现，用非递归方式实现。
MyISAM 避免死锁
启动加锁的情况下，MyISAM 总是一次获得SQL语句所需要的全部锁，所以 MyISAM 表不会出现死锁。
InnoDB 避免死锁
-
为了在单个 InnoDB 表上执行多个并发写入操作时避免死锁，可以在事务开始时为预期要修改的每个元祖（行）使用
SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。尽可能做到一次锁定所需要的所有资源
-
在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
-
如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
-
通过
SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。 -
改变事务隔离级别
日志 #
undo log #
redo log #
InnoDB 引擎特有的日志
bin log #
位于 Server 层
与 redo log 区别：
-
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
-
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
-
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
-
binlog 是用作人工恢复数据，redo log 是 MySQL 自己使用，用于保证在数据库崩溃时的事务持久性。
bin 与 redo 的执行顺序
- 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
- 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
- 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。
慢查询日志 #
slowlog 可以帮助定位存在问题的SQL语句，从而进行SQL语句层面的优化.
mysql设置一个阈值，将运行时间超过该阈值的SQL语句都记录到慢查询日志文件中。DBA过一段时间对其进行检查。
还可以记录到slow_log且没有使用索引的SQL语句次数。
当慢查询日志中有很多SQL语句时，分析要用到mysqldumpslow.
查询日志 #
错误日志 #
复制 #
主从复制 #
主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。
- binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
- I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
- SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。
复制过程：
- master 将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件 binary log events
- slave 将 master 的 binary log events 拷贝到他的中继日志（relay log）
- slave 重做中继日志中的事件，将改变应用到自己的数据库中。MySQL 复制是异步的且串行化的
读写分离 #
主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。
==读写分离能提高性能的原因在于：==
- 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
- 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
- 增加冗余，提高可用性。
读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。
drop\delete\truncate #
简单回答：
- drop 直接删掉表
- truncate 删除表中数据，再插入时自增长 id 又从 1 开始
- delete 删除表中数据，可以加 where 语句
比较
- delete 用来删除表的全部或者一部分数据行，执行delete 后，用户需要提交（commit）或者回滚（rollback）来执行删除或者撤销删除，会触发这个表上所有的 delete 触发器
- truncate 删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，truncate 比 delete 更快，占用的空间更小
- drop 从数据库中删除表，所有的数据行，索引和权限也被删除，所有的 DML 触发器也不会被触发，这个命令也不能回滚。
在不再需要一张表的时候用 drop，在想删除部分数据行的时候用 delete，在保留表而删除所有数据的时候用 truncate
具体解析
- DELETE 语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
- 表和索引所占空间。当表被 TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而 DELETE 操作不会减少表或索引所占用的空间。drop 语句将表所占用的空间全释放掉。
- TRUNCATE 和 DELETE 只删除数据，而 DROP 则删除整个表（结构和数据）。
- 回滚：delete 为 DML（data manipulation language），这个操作会被放到 rollback segment 中，事务提交后才生效。truncate、drop 是 DDL（data define language），操作立即生效，原数据不放到 rollback segment 中，不能回滚。
优化 #
哪几方面做性能优化：
- 为搜索字段创建索引
- 避免使用
select *，列出需要查询的字段 - 垂直分割分表
- 选择正确的存储引擎