首页 「转载」数据库知识点整理
文章
取消

「转载」数据库知识点整理

发表于 2020/10/11 更新于 2022/12/29
作者 张臧乾
57 分钟阅读

数据库知识脑图

存储引擎

InnoDB

InnoDB 是 MySQL 默认的事务型存储引擎，只要在需要它不支持的特性时，才考虑使用其他存储引擎。

InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准隔离级别(未提交读、提交读、可重复读、可串行化)。其默认级别时可重复读（REPEATABLE READ），在可重复读级别下，通过 MVCC + Next-Key Locking 防止幻读。

主索引时聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对主键查询有很高的性能。

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

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

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

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

不支持事务。

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

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

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

InnoDB 和 MyISAM 的比较

  • 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。

  • 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。

  • 外键：InnoDB 支持外键。

  • 备份：InnoDB 支持在线热备份。

  • 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

  • 其它特性：MyISAM 支持压缩表和空间数据索引。

索引

B+ Tree 原理

数据结构

B Tree 指的是 Balance Tree，也就是平衡树，平衡树是一颗查找树，并且所有叶子节点位于同一层。

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

B+ 树有两种类型的节点：内部节点（也称索引节点）和叶子节点，内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存在叶子节点。

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

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

图片

操作

  • 查找

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

  • 插入

    • Perform a search to determine what bucket the new record should go into.

    • If the bucket is not full(a most b - 1 entries after the insertion，b 是节点中的元素个数，一般是页的整数倍),add tht record.

    • Otherwise,before inserting the new record

      • original node has 「(L+1)/2」items
      • new node has 「(L+1)/2」items
      • split the bucket.
      • Move 「(L+1)/2」-th key to the parent,and insert the new node to the parent.
      • Repeat until a parent is found that need not split.

    • If the root splits,treat it as if it has an empty parent ans split as outline above.

    B-trees grow as the root and not at the leaves.

  • 删除

    和插入类似，只不过是自下而上的合并操作。

树的常见特性

  • AVL 树

    平衡二叉树，一般是用平衡因子差值决定并通过旋转来实现，左右子树树高差不超过1，那么和红黑树比较它是严格的平衡二叉树，平衡条件非常严格（树高差只有1），只要插入或删除不满足上面的条件就要通过旋转来保持平衡。由于旋转是非常耗费时间的。所以 AVL 树适用于插入/删除次数比较少，但查找多的场景。

  • 红黑树

    通过对从根节点到叶子节点路径上各个节点的颜色进行约束，确保没有一条路径会比其他路径长2倍，因而是近似平衡的。所以相对于严格要求平衡的AVL树来说，它的旋转保持平衡次数较少。适合，查找少，插入/删除次数多的场景。（现在部分场景使用跳表来替换红黑树，可搜索“为啥 redis 使用跳表(skiplist)而不是使用 red-black？”）

  • B/B+ 树

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

B + 树与红黑树的比较

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

  1. 磁盘 IO 次数，B+ 树一个节点可以存储多个元素，相对于红黑树的树高更低，磁盘 IO 次数更少。
  2. 磁盘预读特性，为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道。每次会读取页的整数倍。操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。

B + 树与 B 树的比较

  1. -B+ 树的磁盘 IO 更低

    B+ 树的内部节点并没有指向关键字具体信息的指针。因此其内部节点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

  2. B+ 树的查询效率更加稳定

    由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

  3. B+ 树元素遍历效率高

    B 树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的操作（或者说效率太低）。

MySQL 索引

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

B+ Tree 索引

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

  1. 因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。

  2. 因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。

  3. 可以指定多个列作为索引列，多个索引列共同组成键。

  4. 适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

图片

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找，这个过程也被称作回表。

图片

哈希索引

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

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

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

全文索引

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

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

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

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

空间数据索引

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

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

索引优化

独立的列

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

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

1

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

多列索引

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

1
2

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

索引列的顺序

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

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

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

1
2
3
4

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

1
2
3

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

前缀索引

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

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

覆盖索引

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

具有以下优点：

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

索引的优点

  1. 大大减少了服务器需要扫描的数据行数。

  2. 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。

  3. 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

索引的使用条件

  1. 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；

  2. 对于中到大型的表，索引就非常有效；

  3. 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

为什么对于非常小的表，大部分情况下简单的全表扫描比建立索引更高效？ 如果一个表比较小，那么显然直接遍历表比走索引要快（因为需要回表）。 注：首先，要注意这个答案隐含的条件是查询的数据不是索引的构成部分，否也不需要回表操作。其次，查询条件也不是主键，否则可以直接从聚簇索引中拿到数据。

查询性能优化

使用 explain 分析 select 查询语句

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

select_type

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

table

要查询的表

possible_keys

The possible indexes to choose 可选择的索引

key

The index actually chosen 实际使用的索引

rows

Estimate of rows to be examined 扫描的行数

type

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

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

  • system

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

  • const

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

    1
2
3
4

      SELECT * FROM tbl_name WHERE primary_key=1;

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

    图片

  • eq_ref

    触发条件：在进行联接查询的，使用主键或者唯一索引并且只匹配到一行记录的时候

1
2
3
4
5
6

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

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

    1
2
3
4
5
6
7
8

      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

    1
2
3
4
5
6
7
8
9
10
11

      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

    The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways: 触发条件：

    只扫描索引树

    1. 查询的字段是索引的一部分，覆盖索引。
    2. 使用主键进行排序

    图片

  • all

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

优化数据访问

减少请求的数据量

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

减少服务器端扫描的行数

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

重构查询方式

切分大查询

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

1

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

1
2
3
4
5

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

分解大连接查询

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

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

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

事务

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

ACID

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

  1. Atomicity：原子性
  2. Consistency：一致性
  3. Isolation：隔离性
  4. Durability：持久性

原子性

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

一致性

数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的。

隔离性

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

持久性

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢。

ACID 之间的关系

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

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

图片

隔离级别

未提交读（READ UNCOMMITTED）

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

提交读（READ COMMITTED）

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

可重复读（REPEATABLE READ）

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

可串行化（SERIALIZABLE）

强制事务串行执行。

需要加锁实现，而其它隔离级别通常不需要。

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

锁

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

锁类型

共享锁（S Lock）

允许事务读一行数据

排他锁（X Lock）

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

意向共享锁（IS Lock）

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

意向排他锁

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

MVCC

多版本并发控制（Multi-Version Concurrency Control, MVCC）是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

基础概念

  • 版本号

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

  • 隐藏的列

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

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

  • Undo 日志

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

    图片

实现过程

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

当开始一个事务时，该事务的版本号肯定大于当前所有数据行快照的创建版本号，理解这一点很关键。数据行快照的创建版本号是创建数据行快照时的系统版本号，系统版本号随着创建事务而递增，因此新创建一个事务时，这个事务的系统版本号比之前的系统版本号都大，也就是比所有数据行快照的创建版本号都大。

  • SELECT

    多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在最近的一个有效快照。但是也有例外，如果有一个事务正在修改该数据行，那么它可以读取事务本身所做的修改，而不用和其它事务的读取结果一致。

    把没有对一个数据行做修改的事务称为 T，T 所要读取的数据行快照的创建版本号必须小于等于 T 的版本号，因为如果大于 T 的版本号，那么表示该数据行快照是其它事务的最新修改，因此不能去读取它。除此之外，T 所要读取的数据行快照的删除版本号必须是未定义或者大于 T 的版本号，因为如果小于等于 T 的版本号，那么表示该数据行快照是已经被删除的，不应该去读取它。

  • INSERT

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

  • DELETE

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

  • UPDATE

    将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。

快照读与当前读

在可重复读级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题。

对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库当前版本数据的方式，叫当前读 (current read)。很显然，在MVCC中：

  • 快照读

    MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。

    select * from table ….;

  • 当前读

    MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

    在进行 SELECT 操作时，可以强制指定进行加锁操作。以下第一个语句需要加 S 锁，第二个需要加 X 锁。

    1
2

      - select * from table where ? lock in share mode;
  - select * from table where ? for update;

    事务的隔离级别实际上都是定义的当前读的级别，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些“当前读”的隔离性，就需要通过加锁来实现了。

锁算法

Record Lock

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

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

Gap Lock

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

1

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）解决的。

锁问题

脏读

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

例如：

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

图片

不可重复读

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

例如：

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

图片

在 InnoDB 存储引擎中，SELECT 操作的不可重复读问题通过 MVCC 得到了解决，而 UPDATE、DELETE 的不可重复读问题是通过 Record Lock 解决的，INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。

Phantom Proble（幻影读）

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

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

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

丢失更新

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

例如：

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

图片

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

分库分表数据切分

水平切分

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

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

图片

垂直切分

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

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

图片

Sharding 策略

  • 哈希取模：hash(key)%N
  • 范围：可以是 ID 范围也可以是时间范围

  • 映射表：使用单独的一个数据库来存储映射关系

  • Sharding 存在的问题

    • 事务问题

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

    • 连接

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

    • 唯一性

    • 使用全局唯一 ID （GUID）
    • 为每个分片指定一个 ID 范围
    • 分布式 ID 生成器（如 Twitter 的 Snowflake 算法）

复制

主从复制

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

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

图片

读写分离

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

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

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

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

图片

JSON

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

  1. 在 where 条件中有通过 json 中的某个字段去过滤返回结果的需求

  2. 查询 json 字段中的部分字段作为返回结果（减少内存占用）

JSON_CONTAINS

JSON_CONTAINS(target, candidate[, path])

如果在 json 字段 target 指定的位置 path，找到了目标值 condidate，返回 1，否则返回 0

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

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 |
+----------------------------------------+

实际使用：

1
2
3
4
5
6
7
8

        $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.

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

      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)

    实际使用：

    1

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

SnoSnameSdeptMnameCnameGrade
1学生-1学院-1院长-1课程-190
2学生-2学院-2院长-2课程-280
2学生-2学院-2院长-2课程-1100
3学生-3学院-2院长-2课程-295

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

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

范式

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

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

第一范式 (1NF)

属性不可分。

第二范式 (2NF)

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

可以通过分解来满足。

分解前

SnoSnameSdeptMnameCnameGrade
1学生-1学院-1院长-1课程-190
2学生-2学院-2院长-2课程-280
2学生-2学院-2院长-2课程-1100
3学生-3学院-2院长-2课程-295

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

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

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

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

分解后

关系-1

SnoSnameSdeptMname
1-1学院-1院长-1
2-2学院-2院长-2
3-3学院-2院长-2

有以下函数依赖：

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

关系-2

SnoCnameGrade
1课程-190
2课程-280
2课程-1100
3课程-295

有以下函数依赖：

  • Sno, Cname -> Grade

第三范式 (3NF)

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

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

  • Sno -> Sdept -> Mname

可以进行以下分解：

关系-11

SnoSnameSdept
1学生-1学院-1
2学生-2学院-2
3学生-3学院-2

关系-12

SdeptMname
学院-1院长-1
学院-2院长-2

ER 图

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

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

实体的三种联系

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

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

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

图片

表示出现多次的关系

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

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

图片

联系的多向性

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

图片

表示子类

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

图片

参考资料

  • 姜承尧. MySQL 技术内幕: InnoDB 存储引擎 [M]. 机械工业出版社, 2011.
  • CS-Notes-MySQL
  • B+ tree
  • 红黑树、B(+)树、跳表、AVL等数据结构，应用场景及分析，以及一些英文缩写
  • B树、B+树、红黑树、AVL树比较
  • 8.8.2 EXPLAIN Output Format
  • 最官方的 mysql explain type 字段解读
  • 12.18.3 Functions That Search JSON Values

原文地址：国庆肝了8天整整2W字的数据库知识点

技术
DB 转载
本文由作者按照 CC BY 4.0 进行授权
最近更新
文章内容

Maven 打包可执行 Jar 包

MySQL MVCC 多版本并发控制