数据库

数据库基础知识

三大范式五大约束

数据库的三大特性：实体、属性、关系。

三大范式

第一范式（1NF）：数据表中的每一列（属性）必须是不可拆分的最小单元，也就是确保每一列的原子性。

第二范式（2NF）：满足1NF后，要求表中的所有列，都必须依赖于主键，而不能有任何一列与主键没有关系，也就是说一个表只描述一件事情，消除了非主属性对于码的部分函数依赖（商品+供应商）。

第三范式（3NF）：满足2NF后，要求表中的每一列只与主键直接相关而不是间接相关（只依赖于主键），即要求分成多张表，一张表中只能有另一张表中的id（主键），而不能包含其他信息（由主键在另一表查询）。反例：关系 R(学号 , 姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，存在非主属性系主任对于学号的传递函数依赖。

五大约束

primary KEY 主键约束(primary key = unique + not null)
UNIQUE 设置唯一性约束，不能有重复值；
DEFAULT 默认值约束，height DOUBLE(3,2)DEFAULT 1.2 height不输入是默认为1.2
NOT NULL：设置非空约束，该字段不能为空；
FOREIGN key :设置外键约束。

数据类型建议

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。

使用VARCHAR：由于CHAR是固定长度的，可能浪费存储空间（程序需要对行尾空格进行处理），除非是长度稳定且高查询速度要求，建议使用按需长度的VARCHAR。

varchar(n) 字段类型的 n 代表的是最多存储的字符数量.

减少BLOB(支持二进制)和TEXT的使用，使用时分离到单独表
减小主表的碎片，使得检索比较稳定。
如果需要高精度存储，使用定点数DECIMAL
其本质上是字符串。
除非年份跨度大，优先使用TIMESTAMP
日期类型中只有它能够和实际时区相对应。
优先选择满足存储需要的最小的数据类型

SQL

我们通常可以将SQL分为三类：

DDL（数据定义语言）
主要用于创建（create）、删除（drop）、修改（alter）数据库中的对象。
DML（数据操作语言）
主要负责插入数据（insert）、删除数据（delete）、更新数据（update）和查询（select）。
DCL（数据控制语言）。
主要用于授予权限（grant）和召回权限（revoke）。

存储引擎

MyISAM 和 InnoDB

两种类型最主要的差别就是 Innodb 支持事务处理与外键和行级锁。

事务（transaction）是用户定义的一批数据库操作，这些操作要么全执行，要么完全不执行.

InnoDB作为MySQL5.5之后的默认存储引擎（MySQL8.0将所有系统表也改为InnoDB），提供了具有提交、回滚和崩溃恢复能力的事务安全保障，同时提供了更小的锁粒度（完整支持行锁）和MVCC高并发支持，支持真正的在线热备份，拥有独立的缓存和日志，性能有较大的提升。对比MyISAM存储引擎，InnoDB会占用更多的磁盘空间以保留数据和索引。

InnoDB 的特点

自动增长列：
可以通过ALTER TABLE ** AUTO_INCREMENT=...;语句强制设置自动增长列的初始值，默认从1开始.（MySQL8.0之前版本的BUG：对于InnoDB存储引擎来说，这个值只保留在内存中，重启数据库会丢失此值）。
外键约束
主键和索引
不同于其他存储引擎，InnoDB的数据文件的保存方式本身就是聚簇索引（索引结构和数据一起存放的索引），即索引也是InnoDB表的主键，表的每行数据都保存在主索引的叶子节点上。因此，所有InnoDB表都必须包含主键（否则会自动创建long类型隐藏字段作为主键）。建议所有InnoDB表都应如下显式地指定主键：
- 优先考虑使用最经常被当作查询条件的字段或者自增字段；
- 字段值基本不会被修改；
- 使用尽可能短的字段。

其他存储引擎

MEMORY
MEMORY的表对应一个frm文件，数据以哈希索引放在内存中，若服务关闭会丢失数据。主要用于那些内容变化不频繁的代码表，或者作为统计操作的中间结果表。
MERGE
MERGE存储引擎也被称为MRG_MyISAM，是一组同结构MyISAM表的组合。MERGE表本身并没有数据。会有时间记录操作日志表。
TokuDB
非常优秀的存储引擎。高效的插入性能、优秀的压缩特性（比InnoDB高近10倍）、热编辑和BulkLoader快速加载大量数据、主从延迟消除、支持ACID和MVCC。适合：日志（插入频繁）、历史（高压缩）、频繁在线DDL。

InnoDB存储引擎

Compact行格式存储

变长字段的真实数据占用的字节数会按照列的顺序逆序存放，使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。同样地，NULL值列表也这么设计。

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列，表示是否为NULL。

针对行溢出的处理是：当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

语句执行流程

具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:

执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：
- 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
- 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
- 如果一样的话就不进行后续更新流程；
- 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。
InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。
至此，一条记录更新完了。
在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）：
- prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
- commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；
至此，一条更新语句执行完成。

索引数据结构

B+树

索引设计和使用

索引是一种用于快速查询和检索数据的数据结构。相当于目录。将对应的全盘扫描减少至对数级。注意：过度索引会导致写性能的损失，且索引需要占用一定的磁盘空间。

语法：

CREATE [ UNIQUE / FULLTEXT / SPATIAL ] INDEX index_name [index_type]
ON tbl_name(index_col_name,...)
[index_option][algorithm_option/Tock_option]... -- [INVISIABLE]

index_col_name:
	col_name [(length)][ASC/DESC]

索引的设计原则：

最适合索引的列是出现在WHERE子句和连接子句中的条件列，而非查询列。（WHERE、GROUP BY、ORDER BY、JOIN）
尽量使用唯一索引。索引列的基数越大（重复值少），索引的效果就越好。二级索引的叶子节点存放的是主键值，而不是实际数据。
使用短索引、数据类型较短的索引。如：前缀索引，取VARCHAR(100)的(10)。另，InnoDB存储引擎，表的索引都会保存主键的值，为减少索引占用的空间，提升缓存效果，建议显式指定主键。
使用最左前缀。若有多列联合索引，最左索引是优先级最高的索引，如联合索引(c1,c2,c3)，实际相当于建了 (c1)，(c1,c2)，(c1,c2,c3) 三个索引，不可进行c2索引查询。这样可减少索引占用的空间。

有一个比较特殊的查询条件：`where c1 = 1 and c3 = 3 ，符合最左匹配吗？

这种其实严格意义上来说是属于索引截断，不同版本处理方式也不一样。 MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，然后再比对 c 字段的值。

从 MySQL5.6 之后，有一个索引下推功能，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

索引失效

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。

在查询条件中对索引进行函数、表达式计算，也是无法走索引的，因为索引存储的是原数据。同样地，类型转换也可能导致索引失效。

索引下推(ICP)

在非主键索引上的优化。

在没有使用ICP的情况下，MySQL的查询：

存储引擎读取索引记录；
根据索引中的主键值，定位并读取完整的行记录；
存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程：

存储引擎读取索引记录（不是完整的行记录）；
判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

加载大量数据时，禁用索引再加载数据，然后重建索引，这样也许会更快。

事务处理

事务（transaction）：将一组语句（或者说操作）打包成一个逻辑单元进行执行，并提供一种保证，这一组操作要么全部成功（ commit，应用到数据库里），要么全部失败（被动 abort，或者主动 rollback），而不会存在只执行了一半的中间状态。

ACID

原子性（Atomicity): 事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。在发生错误时，会回滚该事务所有已经写入的变更，或给予安全的重试。
一致性（Consistent): 在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。即对数据有特定的预期状态，任何数据更改必须满足这些状态约束（恒等条件） 一致性是需要应用侧和数据库侧共同维护的，应用侧有责任正确定义满足状态约束的事务。
隔离性（Isolation):数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。（完全不可见：强隔离性（串行化）；部分可见：弱隔离性）
持久性（Durable): 事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

不依靠 2PC 来实现原子性的另一种方式是：用一个线程或进程来挖掘数据库的事务或提交日志来发布事件。当应用更新数据库时，更改信息被记录到数据库的事务日志中。事件溯源通过使用不同于以事件为中心的方式来持久化业务实体，实现无 2PC 原子性。应用不用存储实体的当前状态，而是存储一系列状态改变事件。应用通过回放事件来重建实体的当前状态。无论业务实体的状态何时发生变化，其都会将新事件追加到事件列表中。由于保存事件是单一操作，因此是原子性。

回退（rollback）：撤销指定SQL语句的过程，只能在一个事务处理内使用
提交（commit）：将未存储的SQL语句结果写入数据库表
保留点（savepoint）：事务处理中设置的临时占位符（place-holder）你可以对它发布回退（与回退整个事务处理不同）

并发事务带来的问题

脏写（Dirty Write）事务 A 修改了一个对象，但尚未提交，此时另一事务 B 修改同一对象，并且覆盖了 A 未提交的值。通常采用行级锁来防止脏写。
脏读（Dirty Read）一个事务 A 能够读到另一个未提交事务 B 的中间状态，并据此做进一步的处理，产生未提交的数据依赖关系。考虑到读锁会阻塞事务，一般只对读取对象加短时锁。也可以使用多版本并发控制（MVCC） 的方式，即语句级MVCC ，维护待更新对象的旧值和需设置的新值。
不可重复读（Non-Repeatable Read）/ 读倾斜（Read skew）一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经由其他事务提交进行改变（如：此之间出现了别的事务的提交，导致值暂时不一致），导致读取到违反状态约束的暂态。这对于长时间运行的只读分析（备份或查询分析计算）是不可接受的。快照隔离是常用的防范手段，如事务级MVCC。
更新丢失（Lost Update) 当两个或多个事务的写依赖于之前的读并选择同一行进行更新，最后的更新覆盖了由其他事务所做的更新，则本事务稍后的依赖于此读的写就会发生问题。如：并发更新计数器和账户余额。解决方案有：
- 显式加锁：for update以通知对返回的所有结果行加锁。
- 原子写操作：通常采用对读取对象加独占锁的方式实现。
- 自动检测：除了使用锁的（悲观）方式（在数据库层或应用层）强制 read-modify-write 原子地执行；还可以使用乐观方式，允许其并发执行，事务管理器检测到更新丢失则进行重试。
幻读（Phantom Read）一个事务按相同的范围条件重新查询以前检索过的数据，却发现其他事务插入/删除 了满足其查询条件的新数据的现象，导致聚合统计的值不一致。也就是说，一个事务中的写入改变了另一个事务的查询结果，可以理解为多对象的不可重复读。如：两个用户同时开启事务申请住入同一个酒店房间，按开启事务的快照均查询到房间号未被占用，从而导致两个用户都被入住，违反唯一性约束。一种解决幻读的思路是谓词锁（相对于对象锁只针对特定的记录，锁住满足某些搜索条件的所有对象范围 ），用于保护数据库中尚未存在而可能马上会被插入的对象。比如上面的例子，若进行I房间号的查询，则必须获得该查询条件的谓词锁（2pL）。

限于性能，目前的数据库简化了谓词锁，将保护对象扩大，称之索引区间锁(next-key locking)。比如上面的例子，取决于索引，可以锁住当天的所有房间（或房间的所有时间段，如本周等）。索引区间锁会将共享锁附加到对应的索引条目，在另一个事务进行修改时，其更新索引会与共享锁冲突。

写倾斜（Write skew）可以理解为多对象的更新丢失，由 MVCC 实现的快照隔离级别的特有的缺陷，它是由于读依赖同一个不变的快照引起的。

事务隔离级别

读未提交（Read Uncommited）： no dirty write：往数据库写入时，只能覆盖已经提交的数据
读已提交（Read Commited）： no dirty read：从数据库读取时，只能读到已经提交的数据
可重复读（Repeatable Read）/ 快照隔离
可串行化（Serializability）：保证事务最终执行结果与串行（无任何并发）相同。

可重复读表示读取数据一直跟这个事务启动时看到的数据是一致的，别的事务提交的更改不可见。这是InnoDB 引擎的默认隔离级别。

可重复读中存在的幻读现象举例：

MVCC

对于「读已提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 快照来实现的，区别在于创建 Read View 的时机不同（对多个正在进行的事务，保留不同的提交版本），「已提交读」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」（详见下文）生成一个 Read View，然后整个事务期间都在用这个 Read View。这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

注意，执行「开始事务」命令，并不意味着启动了事务。在 MySQL 有两种开启事务的命令，分别是：

第一种：begin/start transaction 命令；
第二种：start transaction with consistent snapshot 命令；这两种开启事务的命令，事务的启动时机是不同的：
执行了 begin/start transaction 命令后，并不代表事务启动了。只有在执行这个命令后，执行了增删查改操作的 SQL 语句，才是事务真正启动的时机；
执行了 start transaction with consistent snapshot 命令，就会马上启动事务。

事务级MVCC的实现：

每个事务开始时会获取一个自增的、唯一的事务 ID（txid），该 txid = max(existing tx id) + 1。
该事务在修改数据时，不会修改以前版本，而会新增一个具有 txid 版本的数据。
该事务只能访问到所有版本 ≤ txid 的数据。
在写入时，如果发现某个数据存在 > txid 的版本，则存在写写冲突。

快照读：读取数据的可见版本（旧版本），不加锁。只有普通select是当前读：读取数据的最新版本（当前版本），加锁。select...for update以及其他的更改操作都是。

分布式事务

分布式事务应用程序包含一个或多个资源管理器和事务管理器。

资源管理器（RM）：提供通向事务资源的途径。
事务管理器（TM）：用于协调作为一个分布式事务一部分的事务。TM与管理每个事务的RMs进行通信。

用于执行分布式事务的过程使用两阶段提交（2pC）：

在第一阶段中，所有的分支被预备好。即它们被TM告知要准备提交。RM会记录分支的行动指示。
在第二阶段中，TM告知RMs是否要提交或回滚。如果在预备分支时，所有的分支指示它们将能够提交，则所有的分支被告知要提交。如果在预备时，有任何分支指示它将不能提交，则所有分支被告知回滚。

分布式事务（XA事务）的SQL语法如下：

XA {START/BEGIN} xid [JOIN/RESUME]

每个XA事务必须有一个唯一的标识xid值，以区分事务。

XA END xid [SUSPEND [FOR MIGRATE]]
XA PREPARE xid -- 使事务进入PREPARE状态，也就是两阶段提交的第一个提交阶段。
XA COMMIT xid [ONE PHASE]
XA ROLLBACK xid -- 这两个命令用来提交或者回滚具体的分支事务,也就是两阶段提交的第二个提交阶段：分支事务被实际提交或者回滚。
XA RECOVER -- 返回当前数据库中处于PREPARE状态的分支事务的详细信息。

分布式简介

串行化隔离与性能在根本上是互相冲突、不可兼得的吗？可串行化的快照隔离（Serializable Snapshot Isolation, SSI) 则提供了比较完整的串行化处理机制，性能与快照隔离相当。它是乐观的，在提交时会检查是否存在冲突：

基于查询条件是否过期：假定对查询结果的变化都会让写事务失效，检测：是否作用于即将过期的MVCC对象（读未提交），根据事务ID进行二次验证。
检查写入是否影响即将完成的读取（读取后被新写入），类似于索引区间锁，检查索引是否存在读目标数据的其他事务。

SQL注入简介

个人理解是，URL传值中包含SQL标识或语句（/*…），导致处理异常。

解决方案是：绑定变量（正常转义），验证机制。

MySQL优化

优化工具

我们可以通过以下操作了解哪些SQL语句执行频率高。

查看表：show status [condition]

explain + SQL语句可以得到该SQL语句的执行步骤。
返回的type值表示效率由低到高从ALL/INDEX/RANGE到CONST/SYSTEM/NULL

锁设计

MySQL有3种锁。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。适合查询为主的应用。
- 表共享读锁：阻塞写请求。
- 表独占写锁：优先获得。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。适合在线处理系统，即大量按索引条件更新的数据、并发查询。
- 共享锁（S):允许一个事务读一行，阻止其他事务获得相同数据集的排他锁。
- 排他锁（X):允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

对于S锁的规则：

多个事务的查询语句可以共用一把共享锁；
如果只有一个事务拿到了共享锁，则该事务可以对数据进行 UPDATE DETELE 等操作；
如果有多个事务拿到了共享锁，则所有事务都不能对数据进行 UPDATE DETELE 等操作。对于X锁的规则：
只有一个事务能获取该数据的排它锁；
一旦有一个事务获取了该数据的排它锁之后，其余事务对于该数据的操作将会被阻塞，直至锁释放。

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。 InnoDB行锁（X）分为3种情形：

Record lock:对索引项加锁。
Gap lock:对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁
Next-key lock: 临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

InnoDB这种行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Lock),这两种意向锁都是表锁。