Mysql全面总结(看这一篇就够了)

声明：本文参考三太子敖丙的《Mysql总结》

断断续续看了许多mysql的文章，留在脑海的就是

• 事务
• 索引
• 集群

本文就梳理下所学的知识

---

1.存储引擎

---

1.1 InnoDB

InnoDB 是 Mysql 默认的事务型存储引擎，只要在需要它不支持的特性时，可以用其他的，InnoDB 擅长写 ，我们讲到主从复制的时候再谈及。

InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别（读未提交、读已提交、可重复读、串行化）。其默认的隔离级别是可重复读，是通过 MVCC + Next-key Locking 来防止幻读的，我们一会会提及这个知识。

主索引是聚簇索引，如果没有索引，mysql会选择一个列做索引，或者默认隐藏一个列，该内容会在文后讲到。索引的好处避免我们直接读取磁盘，因此我们可以快速查询到主键的位置，提高性能。

InnoDB 内部做了优化，包括从磁盘读取数据时采用的可预测性读，能够自动在内存中创建 hash 索引 以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区。

InnoDB 支持真正的在线热备份，Mysql 其他的储存引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而是在读写混合的场景中，听着写入可能也就意味着停止读取。

1.2 MyISAM

设计简单，数据以紧密格式储存，对于只读数据，或者表比较小，可以容忍修复操作，则依然可以使用它，所以我们主从复制的时候从数据库采用的就是Myisam引擎。

提供了大量的特性，包括压缩表，空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表进行加锁，读取时会对需要读到的所有表加入共享锁，写入时则对表加排他锁。但在表有读操作的同时，也可以往表中插入新的记录，这被称为并发插入。

可以手工或者自动检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，并且修复操作数是非常慢的。

如果指定了 DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立即将修改的索引数据写入到磁盘，而是会写入到内存的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

1.3 InnoDB 和 MyISAM 的比较

• 事务： InnoDB 是事务性的，可以使用commit 和 Rollback 语句。
• 并发： MyISAM 只支持表级锁，而 InnoDB支持表锁和行锁。
• 外键： InnoDB 支持外键 ，。
• 备份： InnoDB 支持在线热备份。
• 崩溃修复： Myisam 崩溃后发生损坏的概率比InnoDB高很多，而且恢复的速度也是更慢。
• 其他特性：Myiasm 支持压缩表和空间数据索引。

2.索引

2.1 B+ Tree 原理

数据结构

B Tree 指的是 Blanace Tree ，也就是平衡树，平衡树是一颗查找树，并且所有的叶子节点位于同一层。有人说B 是 是因为 每个非叶子节点的度是（a，b）a>=2,b>a的树，不过我们理解即可。

B+ Tree 是 B 树的一种变形，他是基于B Tree 和 叶子节点顺序访问指针进行实现，通常用于数据库和操作系统的文件系统中。

B+树有两种类型的节点：内部节点（索引节点，不存数据）和叶子节点。内部节点就是非叶子节点，内部节点不存储数据，只储存索引，数据都在叶子节点，并且都在同一层，每个叶子节点都是和后一个叶子节点相连接的。

内部节点中的key都按照从小到大的顺序排列，对于内部节点中的一个key，如果有3个key，则有四个指针，左子树的所有key都小于它，右子树的所有key都大于或者等于它，叶子节点的记录也是按照从小到大排列的。

每一个叶子节点都存有相邻叶子节点的指针

操作

查找：

查找以典型的方式进行，类似于二叉查找树。起始于根节点，自顶向下遍历树，选择其分离值在要查找的符合条件的子指针。在节点内部典型的使用二分查找来确定这个位置。

插入（此处羞涩难懂，涉及到树的移动，我们暂且不深入）：

执行搜索以确定新记录应放入哪个存储区。

如果存储桶未满（插入后最多b-1个条目，b是上游中的元素个数，一般是页的整数倍），则添加记录。

否则，在插入新记录之前

原始节点具有「（L + 1）/ 2」个项目
新节点具有「（L + 1）/ 2」个项目
分开桶。
将“（L + 1）/ 2”键移到父级，然后将新节点插入到父级。
重复直到找到不需要拆分的父级。
如果根裂开了，请按照上面的概述处理它，好像它有一个空的父级并裂开一样。

B树作为根而不是叶子生长。

删除：

和插入类似。需要移动树，不过是自下而上的合并操作。

树的常见特性

AVL树：

平衡二叉树，一般是用平衡因子差值决定并通过旋转来实现，左右子树树高差不超过1，

那么和红黑树比较他是严格的平衡二叉树，平衡条件非常苛刻，

只要插入或删除不满足上面的条件就要通过旋转来保持平衡。

由于旋转是非常耗费时间的，

所以AVL树适用于插入/删除次数比较少，但是查找多的场景。

红黑树：

通过对从根节点到叶子节点路径上的各个节点的颜色进行约束，

确保没有一条路径会比其他路径长2倍，因而使近似平衡的。

所以相对于严格要求平衡的AVL树来说，它的旋转保持平衡次数较少。

适合，查找少，插入/删除次数多的场景。

（现在部分场景使用跳表来替换红黑树，可搜索“为啥 redis 使用跳表(skiplist)而不是使用 red-black？”）

B/B+树

多路查找树，出度高，磁盘io低，一般用于数据库系统中。

B+树与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用B+树作为索引结构，主要有一下两个原因。

1. 磁盘io次数
   B+树一个节点可以存储多个元素，相对于红黑树的树高更低，磁盘io次数更少
2. 磁盘预读特性
   为了减少磁盘IO操作，磁盘往往不是严格按照按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，每次读取都是页的整数倍。

操作系统一般将内存和磁盘分割成固定大小的块，每一块成为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次io就能完全载入一个节点。

B+ 树与 B 树的比较

B+树的磁盘io更低

B+树的内部节点并没有指向关键词具体信息的指针。

因此其内部节点相对B树更小。

如果把所有同一内部节点的关键字放在同一磁盘块中，

那么磁盘块能够容纳的关键词数量也就越多。

一次性读入内存中的需要查找关键词也就越多，

相对来说io读写次数也就降低了。

B+树查找效率更稳定

由于非叶子节点并不是指向最终文件内容的节点，

而是只有叶子节点中的关键词索引。

所以任何关键词的查找必须走一条从根节点到叶子节点的路。

所有关键词查找的路径长度相同，导致每一个数据的查找效率相当。

B+树元素遍历效率高

B树在提高了磁盘io性能的同时并没有解决元素遍历低下的问题。

正是为了解决这个问题，B+树产生。

B+树只要遍历叶子节点就可以实现整棵树的遍历。

而且在数据库中基于范围的查找是非常频繁的，

而b树做这样的操作效率太低

3. mysql 索引

索引是在存储引擎层实现的，而不是在服务器层实现的，所有不同的存储引擎具有不同的索引类型来实现。

3.1 B+ 树索引

是大多数MySQL 存储引擎的默认的索引类型

• 因为不需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。
• 因为 B+ 树的有序性，所以除了用于查找，还可以用于排序和分组。
• 可以指定多个列作为索引列，多个索引列共同组成键。
• 适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找，如果不是按照索引列的顺序进行查找，则无法使用主键，这是我们索引失效的一个点。

InnoDB 的 B+ 树索引分为主键索引和辅助索引，主索引的叶子节点data域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行放在两个不同的地方，所以一个表只能有一个聚簇索引，所以我们后面用到的其他非聚簇索引用来查找特定聚簇索引的键获取数据。

辅助索引的叶子节点的data域记录着主键值，因此在使用辅助索引进行查找的时候，需要先查询到主键，然后在到主索引中进行查找数据，这个过程别成为回表。

3.2 哈希索引

哈希索引能以o(1)的时间复杂度进行查找，但是失去了有序性；

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

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

3.3 全文检索

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

查找条件使用 match against ，而不是普通的 where

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

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

3.4 空间数据索引

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

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

4. 索引优化

4.1 独立的列

在进行查询是，索引列不是表达式的一部分，也不能是函数的参数，否则无法使用索引。

例如下面的查询不能使用actor_id 列的索引；

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

4.2 多列索引

在需要使用多个列作为条件进行查询的时候，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

4.3 索引列的顺序

让选择性最强的索引列放在前面。

索引的选择性是指：不重复的索引值和记录总数的比值。最大值是1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询的效率也越高。看例子

例如下面显示的结果中customer_id 的选择性比 staff_id 更高，因此最好把customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

结果

   staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               COUNT(*): 16049

4.4 前缀索引

对于blob、text、和varchar 类型的列、必须使用前缀索引，只索引开始的部分字符。

前缀长度的选取需要根据索引类型来确定。

4.5 覆盖索引

索引包含所有需要查询的字段值

具有以下优点：

• 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
• 一些存储引擎（例如myisam）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不适用系统调用（通常比较耗时）。
• 对于InnoDB 引擎，如辅助索引能够覆盖查询，则无需访问主索引。

5. 索引的优点

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

6.索引的使用条件

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

为什么非常小的表，全表扫描比索引快？

如果表比较小，那么显然直接遍历表比走索引快，索引需要回表

前提条件（查询的数据不是索引的构成部分，或者是索引但不是主键索引

因为主键索引是聚簇索引可以直接获取数据）。

7，索引性能优化

使用 explain 分析 select 查询语句，也成为查看sql执行计划，看看索引类型以及命中率之类的。

explain 用来分析 Select 查询语句，

开发人员可以通过分析 Explain 结果来优化查询语句。

Select_type

常用的有 SIMPLE 简单查询， UNION 联合查询， SUBQUERY 子查询。

table

要查询的表

Possible_keys

可选择的索引

key

实际使用的key

row

扫描行数

type

索引查询类型，经常用到的索引查询类型

• const： 使用主键或者唯一索引进行查询的时候只有一行匹配
• ref：使用非唯一索引
• range：使用主键、单个字段的辅助索引、多个字段的辅助索引的最后一个字段进行范围查询
• index：和all的区别是扫描的是索引树
• all：扫描全表

system

触发条件是表只有一行数据，这是一个const type 的特殊情况

const

触发条件是在使用主键或者唯一索引进行查询的时候只有一行匹配。

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref (我没懂)

触发条件是在进行连接查询的，

使用主键或者唯一索引并且只匹配到一行记录的时候

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

ref

触发条件是使用非唯一索引

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

range

触发条件时只有在使用主键、单个字段的辅助索引、

多个字段的辅助索引的最后一个字段进行范围查询才是 range

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index

触发条件是只扫描索引树时

查询的字段是索引的一部分，覆盖索引

使用主键进行排序

all

触发条件是：全表扫描，不走索引

优化数据访问

直接：减少请求的数据量

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

减少服务器端扫描的次数

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

8.重构查询方式

切分大查询

一个大查询如果一次性执行，可能一次锁住很多数据、

占满整个事务日志、耗尽系统资源、阻塞很多小而重要的查询。

DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

分解大连接查询

将一个大连接查询分解成对每一个表进行一个单表的查询

然后再应用程序中进行关联（mysql做简单处理，复杂处理交给Java代码）

好处：

• 让缓存更加高效，对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其他表的查询缓存依然可用。
• 分解多个单表查询，这些单表查询的缓存结果更可能被其他查询用到，从而减少数据的冗余查询。
• 减少锁竞争（表锁或者行锁长时间占用）
• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
• 查询本身效率也可能会有所提升。例如下面的例子中，使用 in （）代替连接查询，可以让 mysql 按照 id 顺序进行查询，这可能比随机的连接更高效。、

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

9.事务

事务会在另一篇文章讲到，这里做简单的描述。

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

ACID:

事务最基本的莫过于 ACID 四个特性了，分别是

• Atomicity 原子性
• Consistency 一致性
• Isolation 隔离性
• Durability 持久性

原子性

事务被视为不可分割的最小单元，事物的所有操作要么全部成功，

要么全部回滚，和redis不一样。

一致性

数据库在事务执行前后都保持一致性状态，在一致性状态下，

所有事务对一个数据的读取结果都是相同的。

比如转账前 小明+小华转账的总余额是1000，那么事务操作后总状态也是1000

隔离性

一个事务所做的修改在最终提交之前，对其他事务都是不可见的，

多个事务直接互不干扰。

持久性

一旦事务提交，则其所做的修改都是永远保存到数据库中的。

即使断电或者系统崩溃，事务执行的结果也能丢。

这里的事务可以看另一篇数据库事务的文章

10.ACID 之间的关系

事务的 ACID 特性概念很简单，但是不好理解，主要是因为这几个特性不是一种平级关系

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

11.隔离级别

读未提交（READ UNCOMMITTED）

事务中的修改，即使没有提交，对其他事务也是可见的。

读已提交（READ COMMITTED）

一个事务只能读取已经提交的事务所做的修改，

换句话说，一个事务所做的修改在提交之前对其他事务是不可见的。

可重复读（REPEATABLE READ）默认是这个

保证在同一个事务中多次读取同样的数据的结果是一样的。

换句话说，即使其他事务提交数据，一个事务内第二次读取还是未修改前的数据。

串行化（serializable）

强制事务串行化执行。

需要加锁，其他的不需要，通过mvcc控制无锁事务

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

脏读：

事务A正在读的数据恰好是事务B修改的数据，但是这个事务并没有提交。

如果事务B回滚了，那么这个事务A的数据就是错数据，也成为脏数据。

不可重复读：

事务A假如需要读取两遍数据，在事务A第一次读取完数据到第二次未读该数据之间

如果事务B修改了该数据并提交，就会导致两次读取的同样的sql不是同一个数据

如果事务A和事务B修改的是同一个数据源，
那么后提交的事务就会覆盖先提交的事务（第二类更新丢失）


举个不恰当的例子(伪代码)：
student stu= select * from user where id = 1

dosomething

student stu2 =select * from user where id = 1

两次数据内容不一样，强调的是内容前后不一致

幻读

和可重复读差不多 

指的是：A事务再两次读取数据的过程中，事务B进行了数据集的添加或者删除，

导致我们读取到的数据据在完整性上是错误的。

伪sql

int count = select count(1) from user;

dosomething

int count2 = select count(1) from user;

sout(count == count2)

这样会导致我们两次的数据集不一致

12. 锁

锁是数据库系统区别文件系统的一个关键特性。

锁机制用于管理对共享资源的并发访问。

12.1锁类型

共享锁 (s lock)

允许事务读取一行数据

排他锁（x Lock）

允许事务删除或者更新一行数据

意向共享锁（is lock）

事务想要获取一张表中某几行的共享锁

意向排他锁（Ix lock）

事务想要获取一张表中的某几行数据的排他锁

12.2 MVCC

多版本并发控制（Multi-Version concurrent Control）是 mysql 的 InnoDB 存储引擎实现隔离级别的一种具体的方式，用于实现提交读和可重复读这两种隔离级别，而读未提交总是读取最新的数据，
串行化需要加锁，不能用单纯的mvcc实现。

基础概念

版本号：

• 系统版本号，是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
• 事务版本号，事务开始时的系统账号。

隐藏的列

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

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

Undo日志

mvcc 使用到快照存储在undo日志中，该日志通过回滚指针把一个数据行（record）的所有快照连接起来

实现过程

以下实现过程针对可重复读隔离级别。

当开始一个事务时，

该事务的版本号肯定大于当前所有数据行快照的创建版本号

理解这一点很关键

数据行快照的创建版本号时创建数据行快照时的系统版本号，

系统版本号随着创建的事务递增的，因此创建一个事务时

这个事务的系统版本号比之前的版本号都大

也就是比所有数据行快照的创建版本号都大。

select

多个事务必须读取到同一个数据行的快照，

并且这个快照是距离现在最近的一个有效快照。

但是有例外，如果有一个事务正在修改该数据行

那么它可以读取事务本身所做的修改

而不和其他事务读取的结果一致

把没有对一个数据行修改的事务称为T，

T所要读取的数据行的快照的创建版本号必须小于等于T的版本号

因为如果大于T的版本号，那么表示该数据行快照是其他事务的最新修改

因此不能去读取它。除此之外，

T所要读取的数据行快照的删除版本号必须是未定义的或者大于T的版本号，

因为如果小于等于T的版本号，

那么表示该数据行快照是已经被删除的，不应该读取它。

Insert

将当前系统版本号作为数据行创建的版本号。

Delete

将当前系统版本号作为数据行快照的删除版本号

Update

将当前版本号作为更新前的数据行快照的删除版本号，

并将当前系统版本号作为更新后的数据行快照的创建版本号。

可以理解为 先 delete 再 insert

快照读与当前写

在可重复读级别中，通过mvcc机制，虽然让数据变得可重复读，

但是我们读取的数据可能是历史数据，是不及时的数据，不是数据库的当前数据。

这在一些对于数据的时效性特别敏感的业务中，就很可能出现问题。

对于这种读取历史数据的方式，我们叫他快照读（snapshot read）

而读取数据库当前版本数据的方式叫做当前都（current read）

快照读

mvcc的select操作是快照中的数据，不需要进行加锁的操作

select * from table ….;

当前读

mvcc其他会对数据库进行修改的操作（Insert、update、delete）

需要进行加锁操作，从而读取最新的数据。

可以看到Mvcc并不是完全的不用加锁，而是避免了select的加锁过程。

INSERT;
UPDATE;
DELETE;

在进行select操作时，可以强制指定加锁操作。

以下第一个语句需要加s锁，

第二个需要加 x 锁

select * from table where ? lock in share mode;

select * from table where ? for update;

事务的隔离级别实际上都是定义的当前读的级别，

mysql 为了减少锁处理（包括等待其他锁的时间），提升并发能力

引入了快照的概念，使得select不用加锁，而update insert 这些当前读的隔离性

就需要加锁来实现

13.锁算法

Record Lock

---

锁定一个记录上的索引，而不是记录本身。

如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此Rcord Locks 依然可以使用。

---

Gap Lock

---

锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其他事务就不能在t.c中插入15.

SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

---

Next-Key Lock

---

它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引包含以下值：10，11，13，and 20，那么就需要锁定以下区间；

---

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +∞)

在InnoDB 存储引擎中，Select 操作的不可重复读问题通过 MVCC 得到了解决，而 UPDATE,DELETE 的不可重复读问题通过 Record Lock解决，Insert 的不可重复读问题通过 next—key Lock（Record Lock + Gap Lock）解决的

---

14.锁问题（在前面11已经简述过）

脏读

---

脏读指的是不同事务下，当前事务读到了另外未提交事务的数据。

例如：

T1修改了一个数据，T2随后读到了这个数据。如果T1撤销了这次修改，那么T2读取的数据就是脏数据。错误的数据。

---

不可重复读

---

不可重复读指的是同一个事务内多次读取到同一数据集合，读取得到的数据是不一致的情况。

例如：

T2读取一个数据，T1对数据进行了修改，如果T2再次读取到这个数据，此时读取的结果和第一次读取的结果不同。

---

幻读

---

幻读指的是在同一事务下，连续执行两次同样的sql语句可能返回不同的结果，第二次可能返回之前不存在的行。

幻读是一种特殊的不可重复读问题。

---

更新丢失

---

一个事务的更新操作会被另一个事务的跟新操作所覆盖。

例如：

T1和T2两个事务对同一个数据进行了修改，T1先修改，T2后修改，T2的修改覆盖了T1的修改。

---

这类型问题可以通过给select 操作加上排他锁来解决，不过这可能会引出性能问题，具体使用要视业务逻辑场景而定

15.分库分表数据拆分（我会在mycat文章中再次说到）

15.1水平拆分

---

水平拆分又称为sharding，他是讲同一个表中的记录拆分到多个结构相同的表中。

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

---

15.2垂直拆分

---

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

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

Sharding 策略

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

Sharding存在问题

事务问题：

使用分布式事务来解决，比如XA接口

连接

可以将原来的连接分解成多个单表查询，然后在用户程序中进行连接

唯一性

使用全局唯一id
为每个分片指定一个id范围
分布式id生成器（如 雪花算法）

16 复制

16.1主从复制

---

主要涉及三个线程：binlog线程（主）、io线程（从）、sql线程（从）

• binlog线程：负责将主服务器上的数据更改为二进制日志（binary log）中。
• io线程：负责从主服务器上获取二进制日志，并写入从服务器的中继日志（Relay log）
• sql线程：负责读取中继日志，解析从主服务器已经执行的数据更改并在从服务器中重放。

16.2读写分离

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

读写分离能提高性能的原因如下：

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

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

17.Json(我没用过)

---

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

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

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

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

获取指定路径的值

-> vs ->>

Whereas the -> operator simply extracts a value, the ->> operator in addition unquotes the extracted result.

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)

实际使用：

$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);

18.关系数据库设计理论

函数依赖

记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}。也就是说，确定学生和课程之后，就能确定其它信息。

Sno	Sname	Sdept	Mname	Cname	Grade
1	学生-1	学院-1	院长-1	课程-1	90
2	学生-2	学院-2	院长-2	课程-2	80
2	学生-2	学院-2	院长-2	课程-1	100
3	学生-3	学院-2	院长-2	课程-2	95

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

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

19.范式

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

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

第一范式（1NF）

属性不可分。

第二范式（2NF）

每个非主键完全函数依赖于键码。

可以通过分解来满足

分解前：

Sno	Sname	Sdept	Mname	Cname	Grade
1	学生-1	学院-1	院长-1	课程-1	90
2	学生-2	学院-2	院长-2	课程-2	80
2	学生-2	学院-2	院长-2	课程-1	100
3	学生-3	学院-2	院长-2	课程-2	95

以上的学生课程中，{Sno, Cname} 为键码，有如下函数依赖：

• sno -> Sname,Sdept
• Sdept -> Mname
• Sno,Cname -> Grade

Grade 完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课程有特定的成绩。

Sname，Sdept和Mname都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。

分解后

关系-1

Sno	Sname	Sdept	Mname
1	学生-1	学院-1	院长-1
2	学生-2	学院-2	院长-2
3	学生-3	学院-2	院长-2

有以下函数依赖：

• Sno -> Sname, Sdept
• Sdept -> Mname

关系-2

Sno	Cname	Grade
1	课程-1	90
2	课程-2	80
2	课程-1	100
3	课程-2	95

有以下函数依赖：

• Sno, Cname -> Grade

第三范式 (3NF)

非主键不传递函数依赖于键码

上面的关系-1中存在以下传递函数依赖：

• Sno->Sdept->Mname

可以进行以下分解：

关系-11

Sno	Sname	Sdept
1	学生-1	学院-1
2	学生-2	学院-2
3	学生-3	学院-2

关系-12

Sdept	Mname
学院-1	院长-1
学院-2	院长-2

ER 图

Entity-Relationship，有三个组成部分：实体、属性、联系。

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

实体的三种联系

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

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

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

表示出现多次的关系

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

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

联系的多向性

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

表示子类

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