24-Spring-数据库开发期末复习

24 Spring 数据库开发期末复习

ChrisDing1105 version 1.4

Salute to the Legendary Software Workers SEBugMaker ZUOHS huangwei021230 quas-modo

文字版复习录音转写：https://d412hlgjpp.feishu.cn/docx/YsHfdpdljomhApx3kOlcutVQnRf?from=from_copylink

题型

难点：三道SQL

时间、字符串、数值三选二

代码行数不会超过15-20

关键是要记得常见的函数:replace等

数值要考虑空值(平均值) COALESCE 中位数、众数不太好考

日期要知道使用索引有三种写法(前一天后一天范围怎么写、算日期之间的差 practice on class7.7)

标注用的mysql还是oracle

会考一个递归(withas) 树状结构自顶向下查询

起始体，union all，递归体，合并成一个视图的形式，最后写一句sql

有一道可能会使用外连接

会把外连接和那几个sql的计算变量融在一起

结构合理、关键字合理，函数用对都有分，细节对了全分

实在不会就写select from，不要空

索引结构一定会考

n n+1个link

B树？对枝叶节点插入，如果满了会造成分裂，删除可能造成合并 在叶节点和内部节点不一样，是什么逻辑，怎么影响上面的节点(那两张图)

讲过例子，看下PPT

日志

redo undo 怎么完成、区别，怎么实现(物理组织的PPT里写清楚了)

分区分表分库

也是在物理组织部分

原因是什么，能够解决什么问题，会遇到什么问题（又怎么解决）

SQL解释器：基于成本和基于规则

可能会考一个，关于SQL基于成本呢解释器的成本计算，成本优化？

（知道sql优化的基本逻辑）怎么计算优化路径的成本呢

等价变化，算出多个路径，估算每个路径的成本，哪些估算哪些是实际的，，有什么问题，为什么要改写sql

送分题?

可能还会再出一道题，自己思考，有什么建议和想法，纯粹的主观题

以下为复习部分

SQL

聚合函数

注意：当使用聚合函数（如 MAX, SUM, AVG 等）时，所有非聚合字段必须出现在 GROUP BY ⼦句中。否则，SQL 引擎无法确定如何处理这些字段。

Select后面接的结果集字段只有两种

• 要么是group by出现的字段
• 要么是group by后出现的字段+聚合函数的组合

但实际运行好像无论MySQL5.7还是8.0也能选没group的

NULL如何成组?

利用coalesce函数

coalesce函数

COALESCE 是 SQL 中的一个函数，它的作用是返回第一个非空表达式的结果。如果所有的表达式都是空（NULL），则 COALESCE 函数返回 NULL。这个函数常用于处理可能为 NULL 的数据列，确保查询结果中不会出现 NULL 值，而是使用一个默认的值。

语法：

1

COALESCE(expression1, expression2, ..., expressionN)

参数：

expression1, expression2, ..., expressionN：可以是列名或值，COALESCE 会从左到右检查这些表达式，返回第一个非 NULL 的结果。

字符串

substr(完整str, pos, 长度)

Q：把EMP表中的ENAME=KING的字符串拆开显示为4行，每行一个字符

1
2
3
4

select substr(e.ename, iter.pos, 1) as C
from (select ename from emp where ename = 'KING') e,
(select id as pos from t10) iter
where iter.pos <= length(e.ename)

t10表

t10 表：这是一个特殊的表，通常在 Oracle 数据库中用来生成一个从 1 到 10 的数字序列。这个表的每一行都有一个 id 列，包含一个数字，用于表示序列中的一个位置。

在 MySQL 数据库中，并没有像 Oracle 中的 t10 表这样的内置表来直接生成数字序列。不过，MySQL 可以通过其他方式来生成数字序列，例如使用递归的公用表表达式（Common Table Expressions, CTE）或者临时表。

以下是使用递归 CTE 生成数字序列的一个例子：

1 2 3 4 5 6

复制WITH RECURSIVE num(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM num WHERE n < 10 ) SELECT * FROM num;

这个递归 CTE 会生成从 1 到 10 的数字序列。你可以调整 n < 10 的条件来改变生成的序列长度。

replace函数

1

REPLACE(string, search, replace)

参数（会替换所有的）

• string：要搜索和替换的原始字符串。
• search：要在 string 中搜索的子字符串。
• replace：用于替换 search 子字符串的新字符串。

使用 REPLACE 统计字符出现次数

1

SELECT (length('10,CLARK,MANAGER') - length(REPLACE('10,CLARK,MANAGER', ',', ''))) / length(',') as cnt;

思路：把找到的字符串换成空，再计算长度差，注意除以本身长度

translate函数

感觉就是强化版replace

语法（Oracle）：

1

TRANSLATE(string, from_string, to_string)

参数：

• string：要进行字符转换的原始字符串。
• from_string：一个包含要被替换的字符的字符串。
• to_string：一个包含用于替换的字符的字符串，顺序应与 from_string 中的字符顺序相对应。

删除不想要的字符

Oracle才有translate

1
2
3

select replace(translate(ename, 'AEIOU','aaaaa'), 'a', '') as stripped1,
       sal
       replace(sal, 0, '') as stripped2 from emp  

先全换成某个字符，比如’a’，然后再删掉

regexp_replace

分离数字和字符数据

1
2
3
4

select data,
    regexp_replace(ename, '[0-9]', '') as characters
    regexp_replace(ename, '[^0-9]', '') as numbers
from emp

regexp

判断是否含有数字或字符

1
2

select ename from V
where ename regexp '[^0-9a-zA-Z]' = 0

rpad

Oracle/Postgre

RPAD 函数的语法如下：

1

RPAD(string, length, pad_string)

• string：要填充的原始字符串。
• length：目标字符串的长度。
• pad_string：用来填充的字符串。

Q：

1
2
3
4
5
6

SELECT REPLACE(
    REPLACE(
        TRANSLATE(REPLACE('Stewie Griffin', '.', ''), 
            'abcdefghijklmnopqrstuvwxyz', 
            RPAD('#', 26, '#')), '#', ''), ' ', '.') || '.'
FROM t1;

Stewie Griffin -> Stewie Griffin -> S##### G###### -> S G -> S.G -> S.G.

数值处理

平均值（要考虑空值

1

select avg(coalesce(salary, 0)) as average_score from emp

累计求和 Running Total

1
2
3
4
5

select e.ename, e.sal
    (select sum(d.sal) from emp d
    where d.empno <= e.empno) as runnin_total
from emp e
order by 3

没什么新的

条件语句-百分比

IF（适合简单语句）

IF(condition, result_if_true, result_if_false)

CASE WHEN（适合多条件）

CASE WHEN 是一个通用的 SQL 语句，用于实现复杂的条件判断。它可以用于所有支持 SQL 的数据库，包括 MySQL、PostgreSQL、Oracle、SQL Server 等。

语法

1
2
3
4
5
6

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

计算部门编号为10的员工薪水总和占所有员工薪水总和的百分比。

1
2
3

SELECT 
    (SUM(IF(deptno = 10, sal, 0)) / SUM(sal)) * 100 AS pct
FROM emp;

计算平均值时去掉最大值和最小值

注意最大最小可能不止一个，所以用not in

1
2
3
4
5
6

SELECT AVG(sal)
FROM emp
WHERE sal NOT IN (
    (SELECT MIN(sal) FROM emp),
    (SELECT MAX(sal) FROM emp)
);

Tag: 修改累计值 没看

日期处理

需要知道算日期的差

1

date_add(hiredate, interval -5 day) as hd_add_1D

相差的日期

1

date_diff(ward_hd, allen_hd)

依据特定时间单位检索数据

monthname，dayname

MYSQL

1
2
3
4

SELECT ename
FROM emp
WHERE MONTHNAME(hiredate) IN ('February', 'December')
   OR DAYNAME(hiredate) = 'Tuesday';

Union

Union All + Distinct = Union

Union All不去重

⼤体⽽⾔，使用union等同于针对union all的输出结果，再执⾏⼀次distinct操作

1
2
3
4
5
6
7
8
9
10
11

SELECT CONCAT(ename, ' ', dname) AS ename_and_dname, deptno
FROM emp
JOIN dept ON emp.deptno = dept.deptno
WHERE emp.deptno = 10

UNION ALL
SELECT '----------' AS ename_and_dname, NULL AS deptno

UNION ALL
SELECT dname AS ename_and_dname, deptno
FROM dept;

注意select选的两个要一样

连接

join = inner join；left join = left outer join等

在 SQL 中，连接是用来结合两个或多个表的记录的一种机制。根据关联条件的不同，连接可以分为多种类型，主要包括内连接（INNER JOIN）、外连接（OUTER JOIN，具体又分为左外连接 LEFT JOIN、右外连接 RIGHT JOIN 和全外连接 FULL OUTER JOIN）以及交叉连接（CROSS JOIN）。下面详细解释每种连接类型，并给出示例。

内连接（INNER JOIN）

内连接返回两个表中符合连接条件的记录。如果在一个表中的记录在另一个表中没有匹配，则这些记录不会出现在结果集中。

示例：有两个表，一个是员工表 employees（包含员工编号 eno 和姓名 ename），另一个是部门表 departments（包含部门编号 dno 和部门名称 dname）。我们想找出所有员工及其所属部门的名称。

1
2
3
4

SELECT employees.ename, departments.dname
FROM employees
INNER JOIN departments
ON employees.dno = departments.dno;

这个查询会返回所有存在于 employees 表和 departments 表中的匹配部门编号的员工姓名和部门名称。

左外连接（LEFT JOIN）

左外连接返回左表（LEFT JOIN 左侧的表）的所有记录和右表中符合连接条件的记录。如果左表的记录在右表中没有匹配，则相关的右表列将返回 NULL。

示例：使用上述的员工表和部门表，如果我们想列出所有员工及其可能的部门名称（即使某些员工没有部门信息）。

1
2
3
4

SELECT employees.ename, departments.dname
FROM employees
LEFT JOIN departments
ON employees.dno = departments.dno;

这个查询会返回所有员工的姓名，对于那些没有部门的员工，部门名称会显示为 NULL。

右外连接（RIGHT JOIN）

右外连接与左外连接相反，它返回右表（RIGHT JOIN 右侧的表）的所有记录和左表中符合连接条件的记录。如果右表的记录在左表中没有匹配，则相关的左表列将返回 NULL。

示例：如果我们想要列出所有部门及其可能的员工名称（即使某些部门没有员工）。

1
2
3
4

SELECT employees.ename, departments.dname
FROM employees
RIGHT JOIN departments
ON employees.dno = departments.dno;

这个查询会返回所有部门的名称，对于那些没有员工的部门，员工名称会显示为 NULL。

全外连接（FULL OUTER JOIN）

全外连接返回左表和右表中的所有记录。如果左表的记录在右表中没有匹配，或右表的记录在左表中没有匹配，则相关的另一侧表列将返回 NULL。注意，并非所有的 SQL 数据库系统都支持全外连接。

示例：列出所有员工和所有部门，不论它们是否有匹配。

1
2
3
4

SELECT employees.ename, departments.dname
FROM employees
FULL OUTER JOIN departments
ON employees.dno = departments.dno;

交叉连接（CROSS JOIN）

交叉连接的语法

在SQL中，交叉连接的语法如下：

1
2
3

SELECT *
FROM table1
CROSS JOIN table2;

或者可以用隐式的语法：

1
2

SELECT *
FROM table1, table2;

交叉连接产生左表和右表的笛卡尔积，每个左表的记录与右表的每个记录相组合。

示例：如果我们想要列出员工和部门的所有可能组合。

1
2
3

SELECT employees.ename, departments.dname
FROM employees
CROSS JOIN departments;

这个查询将为每个员工与每个部门之间创建一个组合，不考虑他们之间是否有实际的关联。

递归查询

PPT 6.0 数据库设计

内含 树的三种实际实现：邻接模型、物化路径模型、嵌套集合模型

1. 邻接模型就像家庭树中的每个人都有一个“父亲”指向谁是他的父亲。每个节点都知道自己的父节点是谁，但不一定知道自己的子节点是谁。

2. 物化路径模型就像每个分类都带有一张地图，指示它从根分类到当前分类的完整路径。

   用相同的分类系统，这次每个分类记录路径：

   id	name	path
   1	电子产品	/1
   2	笔记本电脑	/1/2
   3	手机	/1/3
   4	摄像机	/1/4
   5	配件	/1/2/5

3. 嵌套集合模型就像每个分类都有一个范围，表示它和它的所有子分类的范围。范围用两个数字表示：左值（lft）和右值（rgt）。

   还是相同的分类系统：

   id	name	lft	rgt
   1	电子产品	1	10
   2	笔记本电脑	2	7
   3	手机	8	9
   4	摄像机	10	11
   5	配件	3	4

   这里，lft 和 rgt 表示这个分类和它的子分类在树结构中的位置。

with-as

WITH 语法（也称为公用表表达式，Common Table Expressions，CTE）在 SQL 中用于定义临时的结果集，这些结果集可以在单个查询的上下文中多次引用。它使复杂查询变得更清晰和更易于维护。下面是对 WITH 语法的详细解释和一些示例。

基本的 WITH 语法如下：

1
2
3
4
5
6
7
8
9

WITH cte_name AS (
    -- 这里是子查询
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2, ...
FROM cte_name
WHERE condition;

示例

假设有一个员工表 emp，我们想要计算每个部门的平均工资，然后找出平均工资高于特定值的部门。

1
2
3
4
5
6
7
8

WITH avg_salaries AS (
    SELECT deptno, AVG(sal) AS avg_sal
    FROM emp
    GROUP BY deptno
)
SELECT deptno, avg_sal
FROM avg_salaries
WHERE avg_sal > 5000;

你可以定义多个 CTE，通过逗号分隔它们。

递归 SQL 的语法

1
2
3
4
5
6
7
8
9
10
11
12
13

WITH RECURSIVE cte_name (column_list) AS (
    -- 初始查询
    SELECT ...
    UNION ALL
    -- 递归查询
    SELECT ...
    FROM cte_name
    WHERE ...
)
-- 主查询
SELECT ...
FROM cte_name
WHERE ...;

关于column_list

你的递归CTE的写法是正确的，即使没有显式地写出 column_list。在许多SQL数据库中， column_list 是可选的，如果你在初始查询和递归查询中明确了所选列，那么数据库系统会自动推断出列名。

不过，为了代码的可读性和维护性，显式地写出 column_list 会更好，特别是在列名较多或结构复杂的情况下。这可以帮助你和其他阅读代码的人更清楚地了解CTE的输出列结构。

你可以显式地写出 column_list，像这样：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

WITH RECURSIVE DepartmentHierarchy (dept_id, parent_dept_id, dept_name, level) AS ( -- 初始查询：选择顶级部门（没有父部门）作为起点 SELECT dept_id, parent_dept_id, dept_name, 1 AS level FROM Departments WHERE parent_dept_id IS NULL UNION ALL -- 递归查询：连接上一级部门和当前部门 SELECT d.dept_id, d.parent_dept_id, d.dept_name, dh.level + 1 FROM Departments d JOIN DepartmentHierarchy dh ON d.parent_dept_id = dh.dept_id ) -- 主查询：查询所有部门及其层级关系 SELECT dept_id, parent_dept_id, dept_name, level FROM DepartmentHierarchy ORDER BY level, dept_id;

这样写的好处是：

1. 明确性：清楚地定义了CTE的输出列。
2. 一致性：确保初始查询和递归查询的列名一致。
3. 可读性：提高代码的可读性，使得其他人更容易理解。

总结来说，虽然不显式写出 column_list 在大多数情况下也是正确的，但显式地写出它有助于提高代码的清晰度和可维护性。

示例

假设我们有一个部门表 Departments，其结构如下：

希望查询所有部门及其层级关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

WITH RECURSIVE DepartmentHierarchy AS (
    -- 初始查询：选择顶级部门（没有父部门）作为起点
    SELECT dept_id, parent_dept_id, dept_name, 1 AS level
    FROM Departments
    WHERE parent_dept_id IS NULL

    UNION ALL

    -- 递归查询：连接上一级部门和当前部门
    SELECT d.dept_id, d.parent_dept_id, d.dept_name, dh.level + 1
    FROM Departments d
    JOIN DepartmentHierarchy dh ON d.parent_dept_id = dh.dept_id
)
-- 主查询：查询所有部门及其层级关系
SELECT dept_id, parent_dept_id, dept_name, level
FROM DepartmentHierarchy
ORDER BY level, dept_id;

关于递归查询的终止

递归查询的结束条件由以下两部分决定：

1. 递归查询部分的 WHERE 子句：用于控制递归的深度或范围。
2. 数据本身的结构：当没有更多的记录满足递归条件时，递归自然结束。

1.递归查询部分的 WHERE 子句

在递归 CTE 中，WHERE 子句可以限制递归的范围或深度。例如，你可以通过 WHERE 子句限制递归的深度，以避免无限递归。虽然在你的示例中没有使用 WHERE 子句来限制递归，但在某些情况下，添加这样的限制可能是必要的。

示例：限制递归深度

假设我们希望限制部门层级的递归深度为3层，可以使用以下查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

WITH RECURSIVE DepartmentHierarchy AS (
    -- 初始查询：选择顶级部门（没有父部门）作为起点
    SELECT dept_id, parent_dept_id, dept_name, 1 AS level
    FROM Departments
    WHERE parent_dept_id IS NULL

    UNION ALL

    -- 递归查询：连接上一级部门和当前部门，限制层级深度为3
    SELECT d.dept_id, d.parent_dept_id, d.dept_name, dh.level + 1
    FROM Departments d
    JOIN DepartmentHierarchy dh ON d.parent_dept_id = dh.dept_id
    WHERE dh.level < 3
)
-- 主查询：查询所有部门及其层级关系
SELECT dept_id, parent_dept_id, dept_name, level
FROM DepartmentHierarchy
ORDER BY level, dept_id;

在这个查询中，递归查询的 WHERE dh.level < 3 子句限制了递归的深度，确保不会超过3层。

2. 数据本身的结构

即使没有显式的结束条件，递归查询也会根据数据结构自然结束。当递归查询中没有新的记录生成时，递归就会自动结束。在你的示例中，每次递归都从 Departments 表中选择子部门，直到没有更多的子部门可以选择为止。

考虑以下数据结构：

在上述数据中，部门4、5、6、7没有子部门，因此递归在达到这些部门时自然结束。

递归 CTE 工作机制示意

1. 初始查询：找到顶级部门（没有父部门），作为递归的起点。
2. 递归查询：每次递归查询从上一步的结果集中选择子部门。
3. 结束条件：
   • 显式结束条件：如 WHERE dh.level < 3 限制递归深度。
   • 隐式结束条件：当没有更多记录满足递归条件时，递归自然结束。

B树

背景-B树与磁盘

扇出（fanout）：每个节点允许最大的子节点

高扇出，以改善临近键的数据局限性；低高度，以减少遍历期间的寻道次数

B树（B-Tree）是一种平衡树数据结构，广泛用于数据库和文件系统中。它的设计目标是减少磁盘访问次数，提高大规模数据存取的效率。以下是B树与磁盘以及寻道之间的关系：

B树结构和磁盘的关系

块结构与分页

• 节点大小与磁盘块大小匹配：B树的每个节点大小通常与磁盘的块大小（或页大小）匹配。这样，每次从磁盘读取一个节点时，可以充分利用磁盘的读写效率，因为一次I/O操作能够读取或写入完整的节点。
• 减少磁盘I/O次数：由于B树具有较高的扇出（即每个节点可以有多个子节点），树的高度相对较低。这意味着查找、插入或删除操作所需的磁盘访问次数较少，从而减少了I/O操作的开销。

B树与寻道的关系

寻道时间

• 降低寻道次数：B树通过其平衡性和高扇出特性，显著减少了需要访问的节点数量，进而降低了磁盘寻道的次数。寻道时间是机械硬盘中将磁头移动到目标轨道所需的时间，是磁盘访问延迟的一个重要组成部分。

B树构建了一个快速导航和定位搜索项的层次结构，达到这个目标需要高扇出，低树高

B树结构

概念、插入：https://www.bilibili.com/video/BV1tJ4m1w7yR/?spm_id_from=333.788&vd_source=54bdc0734ba281535b1404bbbce896ef

删除：https://www.bilibili.com/video/BV1JU411d7iY/?spm_id_from=333.788&vd_source=54bdc0734ba281535b1404bbbce896ef

B树：多叉平衡搜索树

B树需要满足三个特点(m阶B树就是最多有m个分支)

上取整

内存与硬盘（并且会有查找失败的可能

B 树 与 B+树两者有何异同呢？

• B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，**可能还没有到达叶子节点，检索就结束了。**而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
• 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。

综上，B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

B+树的节点分裂（叶节点）

B+树 叶节点 分裂 选一个移上去就行，需要复制，因为是非叶结点

以下是4阶B树插入11：N是4-1=3

这应该是B+树

**Step 1: **查找算法定位目标叶节点，并将新值关联

Step 2: 有空就插⼊，没空就叫“节点溢出”overflow，必须分裂

1. C1:叶节点：只能放N个值（阶数-1）
2. C2:非叶节点：指针超过N+1（KEY也是只能N个）

**Step 3: **分裂——分配新节点，将⼀半元素从原分裂节点传输给它，并添加它的第⼀个键和指向⽗节点的指针，这时候，键被提升了（promote）

执⾏分裂的数组下标称之为分裂点（也叫中点），分裂点之后的所有元素被传输到新创建的兄弟节点

11插不进去，所以要分裂，先把原来的分裂下，13提上去

B+树的节点分裂（非叶节点）

B+树 非叶节点 分裂选一个直接移上去就行，不需要复制，因为是非叶结点

B+树的叶节点合并（删除16后）

如果是删除16

删除可能会要节点合并

删除会出现下溢出，也就是删完节点太少了，相邻加起来小于某个值了

对于叶节点：两个相邻节点中的键值对数量 小于或等于 N

对于非叶节点：两个相邻节点中指针的数量 小于或等于 N+1（感觉是一个意思）

如果是删除20

⼀般50%是树状结构节点占用率的阈值

如果是删除20，那么20消失就行了

B+树的非叶节点合并（删除10后）

B树的分裂（插入）/合并（删除）

B树的非叶结点分裂合并与B+树的非叶结点分裂合并是一致的

B树的非叶结点分裂合并与B树的叶结点分裂合并是一致的

叶节点自行领会，B+树的叶都得复制之类的，大致理解下

分区分表分库

• 分区

• 就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的

• 分表（手搓分区）

• 就是把一张表按一定的规则分解成N个具有独立存储空间的实体表
• 系统读写时需要根据定义好的规则得到对应的字表明，然后操作它

• 分库

04 B+树结构的物理实现

用来解决下列问题：

• I/O瓶颈
  • 热点数据太多，数据缓存不够，每次查询产生大量I/O —— 分库，垂直分表
  • 网络I/O瓶颈，带宽不够，连接数过多 —— 分库
• CPU瓶颈
  • SQL问题，join、group by、order by —— SQL优化，构建索引
  • 单表数据量过大，扫描行太多，SQL效率过低 —— 水平分表

分区

一张表的数据分为N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的

要解决的问题

分区是一种数据分组方式，数据分组可以：

1、提高并发性和并行性

2、扩增系统架构的可伸缩性

分区目标：

分区想做到的：查询时可以过滤掉很多无用分区、分区本身不会带来很多代价

面对两大问题

• NULL值会使分区过滤无效（PATITION by RANGE COLUMN（order_date））

• 分区列和索引列不匹配（没有索引，或关联查询时关联条件不匹配索引）

• 选择分区的成本可能很高（范围分区的成本需要注意）

• 打开并锁住所有底层表的成本可能很高（开销和分区类型无关，主键查找单行会带来明显开销）

• 维护分区的成本可能很高

分库表

分区：将一个表划分成多个较小的部分（分区），逻辑上是一个表，物理上有多个部分。

分表：将一个表水平拆分成多个独立的子表，可以分布在同一个或多个数据库实例中。

分区表：将一个表按某种规则划分成多个分区，逻辑上是一个表，但在物理上存储在不同的区域。这是分区技术在一个表上的具体应用。

感觉分区表就是一个管理底层表的东西？

分表

一张表按照一定规则分为N个独立存储空间的实体表，系统读写时需要根据定义好的规则得到对应的字表明，然后操作

解决什么问题

• 分表后单表的并发能力提高了，磁盘I/O性能也提高了，写操作效率提高了

  因为单表的并发量小了，不用的表在不同的磁盘上，可以同时读写了

• 数据分布在不同的文件，磁盘I/O性能提高了

  不用的表在不同的磁盘上，可以同时读写了

• 读写锁影响的数据量变小

  当一个分表在进行写操作时，只会锁定这个小表，不会影响到其他分表的读写操作。

• 插入数据库需要重新建立索引的数据量减少

会遇到的问题

如何保证插入不同表的多条记录（事务）要么同时成功，要么同时失败？

• TCC 柔性事务

  TCC柔性事务的三个步骤包括：首先，在Try阶段，系统会预留必要的资源，但不进行最终的操作，比如预留库存、生成临时订单和冻结用户余额。接着，在Confirm阶段，如果所有尝试步骤都成功，系统将正式执行这些操作，真正扣减库存、生成正式订单并扣款。最后，如果任何尝试步骤失败，系统会进入Cancel阶段，取消预留的资源并恢复原状，比如释放预留库存、取消临时订单和解冻余额。这种机制确保了在分布式系统中，所有相关操作要么全部成功，要么全部失败。

分表的实现方式（复杂）

• 需要业务系统配合升级，工作量大

跨分片查询低效：使用中间件来支持跨分片查询

**数据分布不均（数据倾斜）：**使用更好的划分算法，哈希之类的

**维护和管理复杂性：**自动化运维工具，中间件等

补充 by GPT4o

3. 全局唯一ID生成：

• 问题：在多个分表中插入数据时，需要确保每个记录有唯一的标识符（ID）。传统的自增ID可能会在不同的分表中产生冲突。
• 解决方案：
  • UUID：使用全局唯一标识符（UUID），但UUID长度较长，且无序，影响性能。
  • 雪花算法（Snowflake）：生成全局唯一的、有序的ID，可以保证分布式系统中的唯一性和有序性。
  • 分布式ID生成服务：如Twitter的Snowflake、Flink的Flink ID等。

4. 跨分片查询：

• 问题：有时需要对多个分表的数据进行联合查询（如统计分析），这类查询在分表环境中变得复杂和低效。
• 解决方案：
  • 应用层合并：在应用层对各个分表的查询结果进行合并。
  • 中间件：使用数据库中间件（如ShardingSphere、Vitess）来支持跨分片查询。
  • 预计算和缓存：预先计算常用的统计结果，存储在缓存或单独的聚合表中。

5. 数据分布不均（数据倾斜）：

• 问题：如果分表规则不合理，可能导致部分分表的数据量过大或过小，造成负载不均衡，影响系统性能。
• 解决方案：
  • 合理的分片键：选择合适的分片键，保证数据均匀分布。
  • 动态分片：根据数据增长动态调整分片策略。
  • 哈希分片：使用哈希算法进行分片，通常能保证数据的均匀分布。

6. 维护和管理复杂性：

• 问题：分表增加了数据库的维护和管理难度，如备份、恢复、监控等操作变得复杂。
• 解决方案：
  • 自动化运维工具：使用自动化工具进行分表的管理和运维，如备份和恢复脚本、监控系统等。
  • 数据库中间件：使用中间件简化分表的管理工作。

7. 跨分片的事务管理：

• 问题：传统的单机事务无法跨多个分表执行，需要分布式事务支持。
• 解决方案：
  • TCC柔性事务：如你所提到的，Try-Confirm-Cancel模式。
  • XA协议：使用支持XA协议的数据库，进行两阶段提交。
  • 本地事务+补偿机制：先执行本地事务，再通过补偿机制确保最终一致性。

8. 数据迁移和扩容：

• 问题：随着数据量增长，可能需要增加新的分表或重新分配数据，这些操作需要复杂的数据迁移。
• 解决方案：
  • 分片重平衡：使用工具进行分片重平衡，自动将数据迁移到新的分片。
  • 中间件支持：使用支持动态扩容和数据迁移的中间件。

9. 索引管理：

• 问题：每个分表的索引需要单独管理，且在查询时需要考虑跨表的索引优化。
• 解决方案：
  • 统一索引策略：在所有分表上使用相同的索引策略，确保查询优化的一致性。
  • 全局索引：使用中间件或数据库支持全局索引，优化跨表查询。

分区和分表的区别和联系

• 目的都是减少数据库的负担，提高表的增删改查效率
• 分区只是一张表的物理存储位置发生变化，分表是将一张表分为多个实体表
  • 访问量大，数据大，两种配合
  • 访问量不大，数据大，可以只分区（分表可以提升单表的并发能力，所以访问量不大只分个区也行
  • 分表可以多库，分区不可以
• 常见分区分表的策略是类似的

分库

解决什么问题？

• 单台DB的存储空间不够
• 查询量的增加导致数据库服务器已经没法支撑

为什么要分库？

突破单节点数据库服务器的I/O能力限制，解决数据库扩展性的问题

怎么分库？

会遇到什么问题？

SQL成本计算

SQL解释器

• 基于成本优化器

  例如基于成本优化器的计算方式

• 基于规则优化器

优化最重要的方向：连接

连接就是把各个表中的记录都取出来依次进行匹配，并把匹配的组合返回

驱动表和被驱动表 A join B，A是驱动表

嵌套循环连接

驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为 嵌套循环连接 ( Nested-Loop Join )

嵌套循环连接通过在外层循环中逐条读取驱动表记录，并在内层循环中查找被驱动表的匹配记录来实现连接。这种逐条匹配的过程类似于嵌套循环，因此得名嵌套循环连接（Nested-Loop Join）。

1
2
3

SELECT customers.name, orders.order_id
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;

对customers的每一条都访问orders表

一、使用索引加速连接查询

1. 被驱动表有索引时：被驱动表的数据在被驱动表筛选后，会进行多次基于索引的查询以加速连接。

   意思就是比如对驱动表的数据每条进行遍历，那每个驱动表的值就可以看作一个常数，然后就使用索引

2. 多个条件的情况：如果查询有多个条件，优化器会选择最合适的索引来执行查询。

3. 连接查询和过滤条件：连接查询和过滤条件通常只涉及被驱动表的部分列。因此，在实际工作中，不建议使用*作为查询列表。

二、基于块的连接优化：通过减少对被驱动表的多次遍历来提高连接效率（感觉像是倒反天罡

1. 尽量减少访问被驱动表的次数（驱动表的记录不会都放入 join buffer，只会将部分列放入）
2. join buffer 足够大，就可以一次访问被驱动表完成连接
3. join buffer 一般 256KB（相比来看，索引仍然是最好的选择）

连接小结（总结的还可以

• 本质上，连接就是把各个表中的记录都取出来依次进行匹配，并把匹配的组合返回

• 内连接和外连接的本质都是确定驱动表

• 嵌套循环连接算法是：驱动表只访问一次，但被驱动表可能会访问多次，访问次数取决于被驱动表执行单表查询后结果集中有多少条记录

1. 被驱动表会被多次访问，所以，建立合适的索引用以加快访问速度
2. 被驱动表很大，多次访问会导致更多的磁盘 I/O，基于块的嵌套循环算法来缓解

基于成本的优化器 CBO

https://blog.csdn.net/wangen2010/article/details/100516113

要点是执行计划的成本估算

基础仍然是规则方案探索

什么是“成本”

• 一个查询有不同的执行方案，它会选择其中成本最低的（也就是代价最低的）
• 成本一般有两个方面组成
  • I/O 成本，MyISAM 、 InnoDB 存储引擎都是将数据和索引存储到磁盘的
    • 从磁盘到内存的加载，涉及到“物理读写”，这种耗损的时间称之为 I/O 成本
  • CPU 成本，读取记录，以及检测记录是否满足搜索条件、对结果集排序，称之为 CPU 成本
• 一般来说，需要确定不同操作，不同算子的成本常数
  • 物理读取一个页面默认成本是 1.0
  • 逻辑读取和检测条件默认为 0.2

一个单表查询的例子

单表查询，大意指不涉及连接

单表查询成本优化基本步骤：

1. 根据搜索条件，找出所有可能使用的索引

   二级索引就是除了主键索引之外的

   我们分析一下上边查询中涉及到的几个搜索条件：

   • key1 IN ('a', 'b', 'c')，这个搜索条件可以使用二级索引idx_key1。
   • key2 > 10 AND key2 < 1000，这个搜索条件可以使用二级索引idx_key2。
   • key3 > key2，这个搜索条件的索引列由于没有和常数比较，所以并不能使用到索引。
   • key_part1 LIKE '%hello%'，key_part1通过LIKE操作符和以通配符开头的字符串做比较，不可以适用索引。
   • common_field = '123'，由于该列上压根儿没有索引，所以不会用到索引。

   综上所述，上边的查询语句可能用到的索引，也就是possible keys只有idx_key1和idx_key2。

2. 计算全表扫描的代价（row，Data_length)

   这两个信息可以通过语句show table status like 'table_name' \ G查询得到

   • Rows

     本选项表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，由于我们的single_table表是使用InnoDB存储引擎的，所以虽然实际上表中有10000条记录，但是SHOW TABLE STATUS显示的Rows值只有9693条记录。

   • Data_length

     本选项表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：

     1	Data_length = 聚簇索引的页面数量 x 每个页面的大小

     我们的single_table使用默认16KB的页面大小，而上边查询结果显示Data_length的值是1589248，所以我们可以反向来推导出聚簇索引的页面数量：

     1	聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97

   我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值

3. 计算使用不同索引的代价

4. 对比各种执行方案的代价，找出成本最低的那一个

1.1，1.0这些都是常数，加一些偏置值而已

基于规则的优化器 RBO

要点是**结构匹配和替换**

• 应用规则的算法一般需要先在关系代数结构上匹配一部分局部结构
• 再根据结构的特点进行辩护乃至替换操作

基于规则的优化算法

• 变化规则的选择，哪些规则应该被应用，以什么顺序被使用？
• 变换效果的评价，经过变换的查询性能的评估，算子效率和数据集
• 所以，一般固定规则一定会构建人工的优先级顺序 => 通用性下降，适应范围变窄

优先级顺序和通用性

由于规则的应用顺序会影响优化效果，因此，优化器通常会给规则分配一个优先级顺序。这个优先级顺序是基于经验和常见查询模式人工设置的。虽然这种方法可以在特定场景下提高查询效率，但也有其局限性：

• 通用性下降：由于规则的优先级是固定的，优化器在某些情况下可能无法找到最优的查询执行计划。
• 适应范围窄：固定优先级顺序意味着优化器在面对不同类型的查询时，可能不能很好地适应多变的查询模式和数据特性。

一个例子（GPT）

日志

https://www.cnblogs.com/xiaolincoding/p/16396502.html

ACID

• 原子性（Atomicity）：事务的本质要求
  • 单个事务，为一个不可分割的最小工作单元，整个事务中的所有操作要么全部commit成功，要么全部失败rollback，对于一个事务来说，不可能只执行其中的一部分SQL操作，这就是事务的原子性。
• 一致性（Consistency）：数据完整的要求
  • 定义最弱的属性，也是唯一一个可以由开发者控制而不是仅凭数据库自身保证的属性
  • 一致性指的是在事务执行前后，数据库必须从一个一致状态转变为另一个一致状态。也就是说，任何事务都必须使数据库保持一致的状态。具体来说，一致性要求：
    • 数据库规则的满足：事务执行后，所有数据库的完整性约束（如余额不能为负数）都必须满足。
    • 数据的合法性：事务执行后，数据库中的数据必须是合法的，不会出现不合法的数据。
    • 正确的状态转变：事务执行后，数据库状态的变化是正确的，从一个正确的状态变为另一个正确的状态。
• 隔离性（Isolation）：并发的本质要求
  • 通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外一个账户查询余额SQL开始运行，则其看到的信用卡账户的余额并没有被减去100元。后面我们讨论隔离级别(Isolation level)的时候，会发现为什么我们要说事务通常来说是不可见的
• 持久性（Durability）：数据库系统的本质要求
  • 一旦事务提交，那么对其所做的修改就会永久保存在数据库中，此时即使系统崩溃，修改的数据也不会丢失

缓冲区管理

请求页的基本步骤

• 检查该页是否已被缓存
  1. 如果该页在缓存中，直接返回缓存的页；
  2. 如果没有缓存，则页缓存会将其逻辑地址或页id转化为物理地址，加载到内存，并返回；
     1. 一旦返回，这个存有缓存页内容的缓冲区就被称为被引用的（referenced）
     2. 用完之后将其归还给页缓存或解除引用
• 若想让页缓存不要换出某些页，则可以将其固定（pin）
• 如果某些页被修改，标记为脏页（dirty page），脏页表示内容与磁盘不同步，换出时必须将其刷写到磁盘

恢复Recovery

意思是本来事务提交是在缓存完成就修改就事务提交了，这样能快一点，但是就会存在在提交和写入磁盘之间崩溃的可能，无法恢复，违反持久性；所以一个方案是使用写入disk才算完成，这样固然可以，但是显然太麻烦，因此redo日志诞生。

Redo log（重做日志） 的特点

1. 占用空间很小
2. 顺序写入磁盘（顺序 I/O）

redo日志格式

比ppt清晰

https://www.cnblogs.com/kuangtf/articles/16353184.html#4log-sequeue-number

Redo 日志格式不同数据库有不同的定义，但整体的分类就这三种：

1. 记录具体位置的物理修改
2. 记录一个 page 的全部修改
3. 记录操作（执行恢复的参数）

一些数据库也会做一些压缩操作，比如 space id，page number 等

对应第一种

方案2大体对应第二种

大体上是第三种

Mini-Transaction

以组的形式写入 redo 日志

• 一组操作，一组日志的不可分割性

• 索引、基本表、聚簇、二级索引、目录等多个页的操作

MLOG_MULTI_REC_END

redo log block

512B，512K也太大了

redo log 的刷盘时机

redo log刷盘是指将这些日志从内存中的log buffer（日志缓冲区）写入磁盘的过程。这个过程确保了数据的持久性，即使在系统崩溃或宕机后，数据库也能通过这些日志恢复到一致的状态。

• log buffer空间不足（50%的阈值）
• 事务提交时
• 脏页刷新
• 定时进程，固定频率刷新（1s，log buffer中的redo log刷新到硬盘）
• 正常关闭服务器

lsn值（log sequence number）

check point的步骤

1. 计算当前系统可以被覆盖的redo日志对应的lsn值最大是多少

2. 将信息写入日志文件的管理信息中，记录check point的操作

Checkpoint是数据库系统中的一个关键机制，用于确保数据一致性和加速恢复过程。执行checkpoint时，数据库会做以下事情：

1. 刷新脏页：将所有脏页（内存中被修改但尚未写入磁盘的数据页）写入磁盘。
2. 记录LSN：将当前的LSN记录在某个稳定存储位置（如日志文件头部），这标志着所有在这个LSN之前的变更都已经持久化到磁盘。(checkpoint_lsn)

checkpoint就是之前的都完成了，lsn是目前有多少了，最新的一个增长，应该跟buf_free对应的？

恢复

https://www.cnblogs.com/kuangtf/articles/16353184.html#4log-sequeue-number

博客讲的很详细

小trick就是使用Hash表，相同的页面不用多次取回

Undo日志（Undo log）

• 事务保证原子性靠的就是日志
  • 错误：服务器、操作系统、断电
  • 手动或自动rollback
• 对每一条记录进行改动的时候，需要留一手
  • INSERT，记录主键，rollback就删除主键
  • DELECT，记录内容，rollback恢复记录
  • UPDATE，记录内容，rollback恢复记录

每一个事务对数据的修改都会被记录到 undo log ，当执行事务过程中出现错误或者需要执行回滚操作的话，MySQL 可以利用 undo log 将数据恢复到事务开始之前的状态。

undo log 属于逻辑日志，记录的是 SQL 语句

比如说事务执行一条 DELETE 语句，那 undo log 就会记录一条相对应的 INSERT 语句。同时，undo log 的信息也会被记录到 redo log 中，因为 undo log 也要实现持久性保护。并且，undo-log 本身是会被删除清理的，例如 INSERT 操作，在事务提交之后就可以清除掉了；UPDATE/DELETE 操作在事务提交不会立即删除，会加入 history list，由后台线程 purge 进行清理。

Write-Ahead Log WAL（GPT）

基本原理

• 保证数据库系统的持久性语义，即操作日志必须在修改页之前写入磁盘。
• 系统崩溃时，通过操作日志重建内存中丢失的更改。

性能优化

• 后台独立进程循环刷写（如PostgreSQL的后台刷写器）。
• 定期执行Checkpoint操作。

日志语义

• WAL是仅追加的，已写入内容不可变。
• 强制刷盘操作确保事务提交记录完成后才视为“已提交”。
• LSN（Log Sequence Number）唯一且单调递增。

Redo log & Undo log

• 前像（before-image）和后像（after-image）的相互转换

• Undo：一个事务在执行过程中，还未提交，发生崩溃或者需要回滚

  保障原⼦性、实现MVCC（多版本并发控制）

  • Undo log 撤销回滚的日志，记录更新前的数据到undo日志文件中
  • Undo日志记录的是操作记录，插入记录主键、删除记录内容、更新记录旧值
  • Undo日志只在乎“操作之前”（roll_pointer指针串成链表/版本表，trx_id事务id）

• Redo：掉电，磁盘I/O崩了，之前提交的记录如何保存（crash-safe 奔溃恢复）

  保障持久性

  • 事务提交时，未必检查点同步，事务提交成功的标记是——redo日志持久化了
  • Redo日志记录的是物理修改，（xxx数据页yyy的偏移量做了zzz的修改/影子页）
  • 循环写，不用于备份恢复、主从复制，用于掉电等故障恢复——binlog用于全局备份

• 被修改的Undo log本身，也会记录Redo log

  

steal和force策略

事务提交前能不能刷脏页？能 steal（要undo日志，未能提交的话要恢复）

事务提交前要不要保证所有的脏页都刷了？不要保证 no-force（要redo日志），要保证 force

SQL题目补充（一开始跳了几道

全用mysql，不用oracle吧

Any和All关键字

其实可以用min/max等价实现

any/some

1. Select * from t1 where m1 > any (select m2 from t2)
2. 如果子查询结果集存在小于m1列的值，则表达式为true
3. Select * from t1 where m1 > (select min(m2) from t2)

all

1. Select * from t1 where m1 > all(select m2 from t2)
2. 如果子查询的结果集中所有值都小于m1，则表达式为true
3. Select * from t1 where m1 > (select max(m2) from t2)

char_length和length