MySQL 知识点概览及总结

关于

部分转自：打工四年总结的数据库知识点-敖丙

基础

三大范式？

第一范式（1NF）：属性的原子性，数据库表中的字段单一不可再分。
第二范式（2NF）：记录的唯一性，要求记录有唯一标识，即实体的唯一性，即不存在部分依赖。（在第一范式的基础上）
第三范式（3NF）：字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖；（在第二范式的基础上）

一般说来，数据库只需满足第三范式（3NF）就行了。

字符集与排序规则？

字符集（Character Set）：【连接、数据库、表、列的字符集】
排序规则（Collation）：【连接、数据库、表、列的排序规则】
Unicode：
中文排序：【convert() 函数临时转换字符集】

数据类型？

数值数据类型：
1. 整数类型（精确值）：INT（INTEGER），SMALLINT，TINYINT，MEDIUMINT，BIGINT
2. 定点类型（精确值）：DECIMAL，NUMERIC
3. 浮点类型（近似值）：FLOAT，DOUBLE
4. 位值类型：BIT
日期和时间：DATE，TIME，DATETIME，TIMESTAMP，YEAR
- YEAR(2) 与 YEAR(4) 【YEAR(2)的缺陷】
- TIMESTAMP 和 DATETIME 的自动初始化和更新【CURRENT_TIMESTAMP 同义词：NOW()，LOCALTIME，LOCALTIME()，LOCALTIMESTAMP，LOCALTIMESTAMP()】
- 日期和时间类型之间的转换（价值可能会有所变化或信息丢失）【date_col = CAST(datetime_col AS DATE)】
字符串数据类型：
1. 二进制：CHAR、VARCHAR、TEXT
2. 非二进制：BINARY、VARBINARY、BLOB
空间数据类型：……
- R 树
JSON 数据类型：……

……

函数和运算符

运算符：
- “<=>”：Null安全的等【NULL=NULL为true（“=”中为false）】
流程函数：
数值函数：
全文搜索：【相关：全文索引】

DDL 与 DML

DDL：

ALTER：
CREATE：
DROP：
RENAME：【重命名表、移动表（跨数据库移动）】
TRCUNCATE：【清空但不删除，比 DROP 快】

DML：

CALL：调用存储过程。
```
CALL sp_name([parameter[,...]])
CALL sp_name[()]
```
- 【“CALL p()”和“CALL p”等效】
- 【可以使用声明为OUT或INOUT参数的参数将值传递回其调用方】

“LOAD DATA”：从文本文件加载语句。

存储过程与函数：

PROCEDURE：
1. 【“CALL”语句调用】
2. 【有三种参数“IN”，“OUT”，“INOUT”，也可以不要参数】
3. 【可以没有输出（不要“OUT/INOUT参数”）】
FUNCTION：
1. 【在表达式使用。函数在表达式求值期间返回一个值】
2. 【参数只能相当于“IN”】
3. 【必需指定“RETURNS”子句】

存储引擎

InnoDB 和 MyISAM 的比较

事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
外键：InnoDB 支持外键。
备份：InnoDB 支持在线热备份。
崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
其它特性：MyISAM 支持压缩表和空间数据索引。InnoDB 支持多版本控制（MVCC）、多种锁（索引记录锁、简索锁、临键锁）

InnoDB 的三大特性

Buffer Pool（缓冲池）、Adaptive Hash Index（自适应Hash索引）、Doublewrite Buffer（双写缓冲区）。

InnoDB 的日志

错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
二进制日志：记录对数据库执行更改的所有操作。
中继日志：
事务日志：

撤销日志？重做日志？

Undo Log & Redo Log【！！！】

Undo Log：在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方为UndoLog），然后进行数据的修改。如果出现了错误或者用户执行了“ROLLBACK”语句，系统可以利用 UndoLog 中的备份将数据恢复到事务开始之前的状态。

Undo Log 是为了实现事务的原子性，在 MySQL 数据库 InnoDB 存储引擎中还用来实现多版本并发控制（简称：MVCC）【用于保存MVCC的数据行快照】

实现原子性和持久化的事务的简化过程：

这里有一个隐含的前提条件：“数据都是先读到内存中，然后修改内存中的数据，最后将数据写回磁盘”

假设有x、y两个数据，值分别为1,2。

A.事务开始.

B.记录x=1到undolog.

C.修改x=3.

D.记录y=2到undolog.

E.修改y=4.

F.将undolog写到磁盘。

G.将数据写到磁盘。

H.事务提交

之所以能同时保证原子性和持久化，是因为以下特点：

更新数据前记录 Undo log。
为了保证持久性，必须将数据在事务提交前写到磁盘。只要事务成功提交，数据必然已经持久化。
Undo log必须先于数据持久化到磁盘。如果在G,H之间系统崩溃，undo log是完整的，可以用来回滚事务。
如果在A-F之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。

Undo Log 的缺陷：

每个事务提交前将数据和 Undo Log写入磁盘，这样会导致大量的磁盘I/O，因此性能很低。如果能够将数据缓存一段时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即“Redo log”。

Redo log：记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是RedoLog已经持久化。系统可以根据RedoLog的内容，将所有数据恢复到最新的状态。

Undo + Redo 事务简化过程：

这里有一个隐含的前提条件：“数据都是先读到内存中，然后修改内存中的数据，最后将数据写回磁盘”

假设有x、y两个数据，值分别为1,2.

A.事务开始.

B.记录x=1到undolog.

C.修改x=3.

D.记录x=3到redolog.

E.记录y=2到undolog.

F.修改y=4.

G.记录y=4到redolog.

H.将redolog写入磁盘。

I.事务提交

Undo + Redo 事务的特点：

为了保证持久性，必须在事务提交前将RedoLog持久化。
数据不需要在事务提交前写入磁盘，而是缓存在内存中。
RedoLog 保证事务的持久性。
UndoLog 保证事务的原子性。【？】
有一个隐含的特点，数据必须要晚于redolog写入持久化。

索引

B-Tree（B+-Tree）

InnoDB、MyISAM 使用 B+树作为索引结构。

B+ 树的：

结构
特性
操作
与其他树的比较（B树、红黑树）

见：“MySQL 优化：优化 SQL 语句：优化 SELECT 语句”

InnoDB 的 B+Tree 索引

InnoDB 的 B+Tree 索引分为主索引（聚簇索引）和辅助索引（二级索引）。

主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
辅助索引只能索引到数据的主键，具体读取数据还需要“回表”到聚簇索引。

哈希索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

无法用于排序与分组；
只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”（属于内存结构），当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

必须使用 GIS 相关的函数来维护数据。

索引优化

独立的列：在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。（如“WHERE actor_id + 1 = 5”不能使用索引）
多列索引：（复合索引）在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。
索引列的顺序：让选择性最强的索引列放在前面。（索引的选择性：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。）
前缀索引：对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。前缀长度的选取需要根据索引选择性来确定。
覆盖索引：索引包含所有需要查询的字段的值。【？？？】
具有以下优点：
- 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
- 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
- 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

索引的优点

大大减少了服务器需要扫描的数据行数。
帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

索引的使用条件

对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；
对于中到大型的表，索引就非常有效；
但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

为什么对于非常小的表，大部分情况下简单的全表扫描比建立索引更高效？

如果一个表比较小，那么显然直接遍历表比走索引要快（因为需要回表）。

注：首先，要注意这个答案隐含的条件是查询的数据不是索引的构成部分，否也不需要回表操作。其次，查询条件也不是主键，否则可以直接从聚簇索引中拿到数据。

查询性能优化

使用 explain 分析 select 查询语句，并根据其输出反应进行查询语句调整。

其输出如下：

select_type：查询类型。常用的有 SIMPLE 简单查询，UNION 联合查询，SUBQUERY 子查询等。
table：要查询的表。
possible_keys：可选择的索引。
key：实际使用的索引。
rows：扫描的行数。
type：索引查询类型。经常用到的索引查询类型：
- const：使用主键或者唯一索引进行查询的时候只有一行匹配
- system：表只有一行（特殊的const）
- ref：使用非唯一索引
- eq_ref：在进行联接查询的，使用主键或者唯一索引并且只匹配到一行记录的时候
- range：使用主键、单个字段的辅助索引、多个字段的辅助索引的最后一个字段进行范围查询【？？？】
- index：只扫描索引树。【1）查询的字段是索引的一部分，覆盖索引。 2）使用主键进行排序】
- all：全表扫描，不走索引

优化数据访问

减少请求的数据量

只返回必要的列：最好不要使用“SELECT *” 语句。
只返回必要的行：使用 LIMIT 语句来限制返回的数据。
缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询。

重构查询方式

切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
减少锁竞争；
在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
查询本身效率也可能会有所提升。例如下面的例子中，使用“IN()”代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。【！！！！】

事务

事务是指满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

ACID 模型？

Atomicity（原子性）：事务被视为不可分割的最小单元，事务的所有操作要么全部成功，要么全部失败回滚。【事务】
Consistency（一致性）：数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的。【事务，内部 InnoDB 处理】
Isolation（隔离）：一个事务所做的修改在最终提交以前，对其他事务是不可见的。【事务】
Durability（耐久性）：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢。【事务，硬件相关】

ACID 之间的关系

事务的 ACID 特性不是一种平级关系：【？？？】

只有满足一致性，事务的结果才是正确的。
在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。【事务隔离级别】
事务满足持久化是为了能应对数据库崩溃的情况。

隔离级别

未提交读（READ UNCOMMITTED）：事务中的修改，即使没有提交，对其他事务也是可见的。
提交读（READ COMMITTED）：一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其他事务是不可见的。
可重复读（REPEATABLE READ）：保证在同一个事务中多次读取同样数据的结果是一样的。
可串行化（SERIALIZABLE）：强制事务串行执行。需要加锁实现，而其它隔离级别通常不需要。

InnoDB 的 RepeatableRead 隔离级别不会出现“幻读”（因为非锁定读有MVCC的支持、锁定读有临键锁的支持）。

快照读与当前读

快照读 (snapshot read)：即非锁定读，读取的是数据行快照。（由 MVCC 支持，无需加锁）

当前读（current read）：即锁定读，读取的是最新数据。（需要加锁）

“SELECT ... LOCK IN SHARE MODE”：使用共享锁；
“SELECT ... FOR UPDATE”：使用排他锁；

MVCC

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现“提交读”（READ COMMITTED）和“可重复读”（REPEATABLE READ）这两种隔离级别。

而“未提交读”（READ UNCOMMITTED）隔离级别总是读取最新的数据行，无需使用 MVCC。“可串行化”（SERIALIZABLE）隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

Version（版本号）：

系统版本号（当前版本号）：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
事务版本号：事务开始时的系统版本号。

MVCC 在每行记录后面都保存着两个隐藏的列，用来存储两个版本号：

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

MVCC 快照：MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来。【？？？】

MVCC 如何实现“快照读”（非锁定读）

当开始一个事务时，该事务的版本号肯定大于当前所有数据行快照的创建版本号。

SELECT：
1. 事务所要读取的数据行快照的“创建版本号”必须小于等于“当前版本号”（系统版本号）【否则表示已被修改或正被修改】，
2. 并且，数据行快照的“删除版本号”必须是未定义或者大于“当前版本号”【否则表示已被删除】。
- 多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在最近的一个有效快照。
- 对该数据行进行修改的事务可以读取事务本身所做的修改，而不用和其它事务的读取结果一致。
INSERT：将“当前版本号”作为数据行快照的“创建版本号”。
UPDATE：将“当前版本号”作为更新前的数据行快照的“删除版本号”，并更新后将“当前版本号”作为数据行快照“创建版本号”。可以理解为先执行 DELETE 后执行 INSERT。【！！！】
DELETE：将当前版本号”作为数据行快照的“删除版本号”。

锁

锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。

锁类型

共享锁（S Lock）：允许事务读一行数据
排他锁（X Lock）：允许事务删除或者更新一行数据
意向锁：
1. 意向共享锁（IS Lock）：事务想要获得一张表中某几行的共享锁
2. 意向排他锁：事务想要获得一张表中某几行的排他锁

锁算法

InnoDB 的锁都是作用在索引上，而非表（记录）上。

索引记录锁（Record Lock）：锁定一个记录上的索引，而不是记录本身。
- 如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。
间隙锁（Gap Lock）：锁定索引之间的间隙，但是不包含索引本身。
临键锁（Next-Key Lock）： Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。

锁问题

脏读：不同事务下，当前事务可以读取到另外事务未提交的数据。
不可重复读：同一事务内，多次读取同一数据集合得到不一样的数据。
幻读：同一事务下，连续执行两次同样的 sql 语句可能返回不同的结果，第二次的 sql 语句可能会返回之前不存在的行。
丢失更新：一个事务的更新操作会被另一个事务的更新操作所覆盖。

分库分表数据切分【？？？】

水平切分

水平切分又称为 Sharding（分片），它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。

垂直切分

垂直切分是将一张表按列分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直气氛将“经常使用的列”和“不经常使用的列”切分到不同的表中。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不通的库中，例如将原来电商数据部署库垂直切分称商品数据库、用户数据库等。

Sharding 策略

哈希取模：hash(key)%N
范围：可以是 ID 范围也可以是时间范围
映射表：使用单独的一个数据库来存储映射关系

Sharding 存在的问题

事务问题：使用分布式事务来解决，比如 XA 接口。（分布式事务）【见：“XA 事务”】
连接：可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接。【！】
唯一性：
- 使用全局唯一 ID （GUID）
- 为每个分片指定一个 ID 范围
- 分布式 ID 生成器（如 Twitter 的 Snowflake 算法）

复制【？？？】

主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

binlog 线程：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
SQL 线程：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

主从服务器负责各自的读和写，极大程度缓解了锁的争用；
从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器：

JSON【？？？】

实际业务中经常会使用到 JSON 数据类型，在查询过程中主要有两种使用需求：

在 where 条件中有通过 json 中的某个字段去过滤返回结果的需求
查询 json 字段中的部分字段作为返回结果（减少内存占用）

部分内容，见：“MySQL 数据类型：JSON 数据类型”。

“JSON_SET()”：替换现有路径的值，并添加不存在的路径的值。
“JSON_INSERT()”：添加新值，但不替换现有值。
“JSON_REPLACE()”：替换现有值，并忽略新值。
“JSON_REMOVE()”：接收 JSON 文档和一个或多个指定要从该文档中删除的值的路径。返回值是原始文档减去文档中存在的路径选择的值。

路径语法

路径语法使用前导“$”字符表示所考虑的 JSON 文档，还可以选择后面跟有选择器，这些选择器依次指示文档的更具体部分：

句点后跟一个键名，使用给定的键为对象中的成员命名。如果路径表达式中的名称不合法（例如，名称包含空格），则必须在双引号中指定键名称。

“[N]”附加到 path（用于选择数组）后，将该值命名为数组中 N 位置的值。数组位置是从零开始的整数。如果 path 未选择数组值，则 path[0] 的计算结果与 path 相同：

mysql> SELECT JSON_SET('"x"', '$[0]', 'a');
+------------------------------+
| JSON_SET('"x"', '$[0]', 'a') |
+------------------------------+
| "a"                          |
+------------------------------+
1 row in set (0.00 sec)

路径可以包含“*”或“**”通配符：
1. “.[*]”为 JSON 对象中所有成员的值。
2. “[*]”计算 JSON 数组中所有元素的值。
3. “prefix**suffix”计算所有以命名前缀开头并以命名后缀结尾的路径。
- 路径可能不包含序列“***”。
文档中不存在的路径（评估为不存在的数据）评估为 NULL。

JSON_CONTAINS

“JSON_CONTAINS(target, candidate[, path])”：如果在 json 字段 target 指定的位置 path，找到了目标值 condidate，返回 1，否则返回 0。

如果只是检查在指定的路径是否存在数据，使用“JSON_CONTAINS_PATH()”。

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
|                             1 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.b') |
+-------------------------------+
|                             0 |
+-------------------------------+

mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
|                             0 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.c') |
+-------------------------------+
|                             1 |
+-------------------------------+

JSON_CONTAINS_PATH

“JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)”：如果在指定的路径存在数据返回 1，否则返回 0。

one_or_all：

one：一个路径有数据就返回 1。
all：所有路径有数据才返回 1。

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+

实际使用：【？？？】

  $conds = new Criteria();
        $conds->andWhere('dept_code', 'in', $deptCodes);
        if (!empty($aoiAreaId)) {
            $aoiAreaIdCond = new Criteria();
            $aoiAreaIdCond->orWhere("JSON_CONTAINS_PATH(new_aoi_area_ids,'one', '$.\"$aoiAreaId\"')", '=', 1);
            $aoiAreaIdCond->orWhere("JSON_CONTAINS_PATH(old_aoi_area_ids,'one', '$.\"$aoiAreaId\"')", '=', 1);
            $conds->andWhere($aoiAreaIdCond);
        }

column->path 与 column->>path

用于获取指定路径的值。

“->”运算符只是提取一个值，而“->>”运算符还取消了对提取结果的引用。【！！！】

mysql> SELECT * FROM jemp WHERE g > 2;
+-------------------------------+------+
| c                             | g    |
+-------------------------------+------+
| {"id": "3", "name": "Barney"} |    3 |
| {"id": "4", "name": "Betty"}  |    4 |
+-------------------------------+------+
2 rows in set (0.01 sec)

mysql> SELECT c->'$.name' AS name
    ->     FROM jemp WHERE g > 2;
+----------+
| name     |
+----------+
| "Barney" |
| "Betty"  |
+----------+
2 rows in set (0.00 sec)

mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name
    ->     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> SELECT c->>'$.name' AS name
    ->     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

如上，“c->>'$.name'”相当于“JSON_UNQUOTE(c->'$.name')”，结果表现为“Barney”而非“"Barney"”（即，取消了对路径的引用）。

实际使用：【？？？】

$retTask = AoiAreaTaskOrm::findRows(['status', 'extra_info->>"$.new_aoi_area_infos" as new_aoi_area_infos', 'extra_info->>"$.old_aoi_area_infos" as old_aoi_area_infos'], $cond);

关系数据库设计理论

函数依赖

记“A->B”表示 A 函数决定 B，也可以说 B 函数依赖于 A。

如果 {A1，A2，... ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。【？？？】
对于 A->B，如果能找到 A 的真子集 A'，使得 A'-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖。
对于 A->B，B->C，则 A->C 是一个传递函数依赖。

异常【？？？】

以下的学生课程关系的函数依赖为 {Sno, Cname} -> {Sname, Sdept, Mname, Grade}，键码为 {Sno, Cname}。也就是说，确定学生和课程之后，就能确定其它信息。

不符合范式的关系，会产生很多异常，主要有以下四种异常：

冗余数据：例如学生-2 出现了两次。
修改异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
删除异常：删除一个信息，那么也会丢失其它信息。例如删除了课程-1 需要删除第一行和第三行，那么学生-1 的信息就会丢失。
插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

范式

范式理论是为了解决以上提到四种异常。

高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

第一范式 (1NF)：属性不可分。
第二范式 (2NF)：每个非主属性完全函数依赖于键码。
第三范式 (3NF)：非主属性不传递函数依赖于键码。

ER 图

Entity-Relationship，用来进行关系型数据库系统的概念设计。

E-R 图有三个组成部分：实体、属性、联系。

实体的三种联系

包含一对一，一对多，多对多三种。

一对一：如果 A 到 B 是一对多关系，那么画个带箭头的线段指向 B；
一对多：如果是一对一，画两个带箭头的线段；
多对多：如果是多对多，画两个不带箭头的线段。

下图的 Course 和 Student 是一对多的关系：

表示出现多次的关系

一个实体在联系出现几次，就要用几条线连接。

下图表示一个课程的先修关系，先修关系出现两个 Course 实体，第一个是先修课程，后一个是后修课程，因此需要用两条线来表示这种关系：

联系的多向性

虽然老师可以开设多门课，并且可以教授多名学生，但是对于特定的学生和课程，只有一个老师教授，这就构成了一个三元联系。

类与子类

用一个三角形和两条线来连接类和子类，与子类有关的属性和联系都连到子类上，而与父类和子类都有关的连到父类上。

MySQL 知识点概览及总结

关于

基础

三大范式？

字符集 与 排序规则？

数据类型？

函数和运算符

DDL 与 DML

存储引擎

InnoDB 和 MyISAM 的比较

InnoDB 的三大特性

InnoDB 的日志

Undo Log & Redo Log【！！！】

索引

B-Tree（B+-Tree）

InnoDB 的 B+Tree 索引

哈希索引

全文索引

空间数据索引

索引优化

索引的优点

索引的使用条件

查询性能优化

优化数据访问

减少请求的数据量

减少服务器端扫描的行数

重构查询方式

切分大查询

分解大连接查询

事务

ACID 模型？

ACID 之间的关系

隔离级别

快照读与当前读

MVCC

MVCC 如何实现“快照读”（非锁定读）

锁

锁类型

锁算法

锁问题

分库分表数据切分【？？？】

水平切分

垂直切分

Sharding 策略

Sharding 存在的问题

复制【？？？】

主从复制

读写分离

JSON【？？？】

路径语法

JSON_CONTAINS

JSON_CONTAINS_PATH

column->path 与 column->>path

关系数据库设计理论

函数依赖

异常【？？？】

范式

ER 图

实体的三种联系

表示出现多次的关系

联系的多向性

类与子类

导航菜单

搜索

字符集与排序规则？