关于java:打工四年总结的数据库知识点

30次阅读

共计 17257 个字符，预计需要花费 44 分钟才能阅读完成。

国庆在家无聊，我顺手翻了一下家里数据库相干的书籍，这一翻我就看上瘾了，因为大学比拟相熟的一些数据库范式我竟然都忘了，怀揣着好奇心我就看了一个小国庆。

看的过程中我也做了一些小笔记，可能没我之前零碎文章那么乏味，然而相对也是干货十足，适宜大家去回顾或者面试突击的适宜看看，也不多说先放图。

存储引擎

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

    • split the bucket.

      • original node has「(L+1)/2」items
      • new node has「(L+1)/2」items
    • 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 作为索引构造，次要有以下两个起因：

（一）磁盘 IO 次数

B+ 树一个节点能够存储多个元素，绝对于红黑树的树高更低，磁盘 IO 次数更少。

（二）磁盘预读个性

为了缩小磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行程序读取，程序读取不须要进行磁盘寻道。每次会读取页的整数倍。

操作系统个别将内存和磁盘宰割成固定大小的块，每一块称为一页，内存与磁盘以页为单位替换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能齐全载入一个节点。

B + 树与 B 树的比拟

B+ 树的磁盘 IO 更低

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

B+ 树的查问效率更加稳固

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

B+ 树元素遍历效率高

B 树在进步了磁盘 IO 性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+ 树应运而生。B+ 树只有遍历叶子节点就能够实现整棵树的遍历。而且在数据库中基于范畴的查问是十分频繁的，而 B 树不反对这样的操作（或者说效率太低）。

MySQL 索引

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

B+ Tree 索引

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

  • 因为不再须要进行全表扫描，只须要对树进行搜寻即可，所以查找速度快很多。
  • 因为 B+ Tree 的有序性，所以除了用于查找，还能够用于排序和分组。
  • 能够指定多个列作为索引列，多个索引列独特组成键。
  • 实用于全键值、键值范畴和键前缀查找，其中键前缀查找只实用于最左前缀查找。如果不是依照索引列的程序进行查找，则无奈应用索引。

InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着残缺的数据记录，这种索引形式被称为聚簇索引。因为无奈把数据行寄存在两个不同的中央，所以一个表只能有一个聚簇索引。

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

哈希索引

哈希索引能以 O(1) 工夫进行查找，然而失去了有序性：

  • 无奈用于排序与分组；
  • 只反对准确查找，无奈用于局部查找和范畴查找。

InnoDB 存储引擎有一个非凡的性能叫“自适应哈希索引”，当某个索引值被应用的十分频繁时，会在 B+Tree 索引之上再创立一个哈希索引，这样就让 B+Tree 索引具备哈希索引的一些长处，比方疾速的哈希查找。

全文索引

MyISAM 存储引擎反对全文索引，用于查找文本中的关键词，而不是间接比拟是否相等。

查找条件应用 MATCH AGAINST，而不是一般的 WHERE。

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

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

空间数据索引

MyISAM 存储引擎反对空间数据索引（R-Tree），能够用于天文数据存储。空间数据索引会从所有维度来索引数据，能够无效地应用任意维度来进行组合查问。

必须应用 GIS 相干的函数来保护数据。

索引优化

独立的列

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

例如上面的查问不能应用 actor_id 列的索引：

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

多列索引

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

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

索引列的程序

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

索引的选择性是指：不反复的索引值和记录总数的比值。最大值为 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

前缀索引

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

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

笼罩索引

索引蕴含所有须要查问的字段的值。

具备以下长处：

  • 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
  • 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因而，只拜访索引能够不应用零碎调用（通常比拟费时）。
  • 对于 InnoDB 引擎，若辅助索引可能笼罩查问，则无需拜访主索引。

索引的长处

  • 大大减少了服务器须要扫描的数据行数。
  • 帮忙服务器防止进行排序和分组，以及防止创立长期表（B+Tree 索引是有序的，能够用于 ORDER BY 和 GROUP BY 操作。长期表次要是在排序和分组过程中创立，不须要排序和分组，也就不须要创立长期表）。
  • 将随机 I/O 变为程序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

索引的应用条件

  • 对于十分小的表、大部分状况下简略的全表扫描比建设索引更高效；
  • 对于中到大型的表，索引就十分无效；
  • 然而对于特大型的表，建设和保护索引的代价将会随之增长。这种状况下，须要用到一种技术能够间接辨别出须要查问的一组数据，而不是一条记录一条记录地匹配，例如能够应用分区技术。

为什么对于十分小的表，大部分状况下简略的全表扫描比建设索引更高效？

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

注：首先，要留神这个答案隐含的条件是查问的数据不是索引的形成局部，否也不须要回表操作。其次，查问条件也不是主键，否则能够间接从聚簇索引中拿到数据。

查问性能优化

应用 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

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

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

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 语句来限度返回的数据。
  • 缓存反复查问的数据：应用缓存能够防止在数据库中进行查问，特地在要查问的数据常常被反复查问时，缓存带来的查问性能晋升将会是非常明显的。

缩小服务器端扫描的行数

最无效的形式是应用索引来笼罩查问。

重构查问形式

切分大查问

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

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

合成大连贯查问

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

  • 让缓存更高效。对于连贯查问，如果其中一个表发生变化，那么整个查问缓存就无奈应用。而合成后的多个查问，即便其中一个表发生变化，对其它表的查问缓存仍然能够应用。
  • 分解成多个单表查问，这些单表查问的缓存后果更可能被其它查问应用到，从而缩小冗余记录的查问。
  • 缩小锁竞争；
  • 在应用层进行连贯，能够更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
  • 查问自身效率也可能会有所晋升。例如上面的例子中，应用 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);

事务

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

ACID

事务最根本的莫过于 ACID 四个个性了，这四个个性别离是：

  • Atomicity：原子性
  • Consistency：一致性
  • Isolation：隔离性
  • 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 在每行记录前面都保留着两个暗藏的列，用来存储两个版本号：

  • 创立版本号：批示创立一个数据行的快照时的零碎版本号；
  • 删除版本号：如果该快照的删除版本号大于以后事务版本号示意该快照无效，否则示意该快照曾经被删除了。

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 的加锁操作。

INSERT;
UPDATE;
DELETE;

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

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

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

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

关系数据库设计实践

函数依赖

记 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 的信息就会失落。
  • 插入异样：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无奈插入。

范式

范式实践是为了解决以上提到四种异样。

高级别范式的依赖于低级别的范式，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 实体，第一个是先修课程，后一个是后修课程，因而须要用两条线来示意这种关系。

分割的多向性

尽管老师能够开设多门课，并且能够传授多名学生，然而对于特定的学生和课程，只有一个老师传授，这就形成了一个三元分割。

示意子类

用一个三角形和两条线来连贯类和子类，与子类无关的属性和分割都连到子类上，而与父类和子类都无关的连到父类上。

参考资料

  • 姜承尧. 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

总结

这都是些基础知识，我没想到再次回顾大半我都已忘却了，也庆幸有这样的假期可能从新拾起来。

说实话做自媒体后我充电的工夫少了很多，也少了很多工夫钻研技术栈深度，国庆假期我也思考反思了很久，前面筹备持续压缩本人业余时间，比方看手机看 B 站的工夫压缩一下，还是得按时充电，目前作息还算法则早睡早起都做到了，咱们一起加油哟。

我是敖丙，你晓得的越多，你不晓得的越多 ，感激各位人才的： 点赞 、 珍藏 和评论，咱们下期见！

正文完
java
发表至： java
2020-10-14
 0
Kafka消息系统基础知识索引
关于java:面试官你了解Java中的锁优化吗
关于java:微服务架构中常见的注册中心
关于java:JAVA集合框架总结二