Posted 18 minutes read (About 2733 words)

关系型数据库的基础总结

一个好的数据库设计

1 没有数据冗余：

即一个字段在多个库里的表出现，那就是冗余，不仅浪费空间，且容易造成数据不一致。

2 数据完整且准确：

确保一个表没有重复记录，且主键的值是唯一且不为空（表完整）。

字段的用途清晰有效一致（字段完整）。

表之间的关系是完整的，在其中的表更新数据，表中的记录是同步的（关系完整）。

3 数据库易于修改和维护：对字段进行修改不会对其他表造成影响

4 可以花费更少的时间修复数据库

一个错误的示范

大型数据会有数据重复的问题:

1 使得表关系很多

2 维护更新不容易，涉及表和记录太多

3 磁盘和cpu利用低下

4 错误和不一致增加

1
2
3
4
5
6

student_id	name	dept	dept_phone	course	grade
       1	张三	   计算机	67123467	  英语	 80
       2	李四 	数学	    67123468	数学分析	 75
       2	李四	    数学	    67123468	英语	     70
       3	王二	    数学	    67123468	数学分析	 80

对于这样的一个表，可以观察出
1 插入异常：当插入一个学生，但如果他还没经过考试，就无法插入

2 删除异常：当一个学院把英语课程撤销了，不再需要英语课程成绩后，会误删除掉张三学院信息。

3 修改异常：当李四转院到计算机学院，如果仅仅修改第二条是不够的，第三条信息依旧错误。

避免错误示范的方式-范式

1970年埃德加定义了第一范式，第二范式，第三范式的概念，后人完善了很多工作，提出第三范式的改版BC范式，还有第四范式，第五范式，DK范式，和第六范式等等。一般认为满足到第三范式即可，范式过高，虽然对关系型数据有好的约束，但是也导致了表增加，数据库操作多性能下降。

第一范式

第一范式要点，在于列具有原子性，不可再分解，解决列重复问题。

第二范式

在第一范式基础上，表需要有一个主键，非主键列必须完全依赖该主键。不能只依赖主键的一部分。解决对主键的依赖。

第三范式

在满足第一第二范式的情况下，表不能包含已在其他表的非主键字段。

BC范式

在第三范式的基础上，防止主键的某一列依赖主键的其他列。

第四范式

非主属性不应该存在多值

反范式

将一个数据库表故意设计成非范式的形式，从而提高查询效。反范式的目的是避免大量的关联查询，在反范式的数据库里存在大量的冗余数据，虽然提高查询效率，但是却有数据不一致性的问题。

设计过程

  • 1 需求分析，一般是业务分析
  • 2 数据模型分析，实体和其属性的建立
  • 3 逻辑模型分析，表结构的建立，满足范式，主键不为null，关系是一对多还是多对多，引用的完整性等等
  • 4 物理设计，确定索引，分区等等，常见的索引方法有B+树，有clustering，还有hash法。
  • 5 测试优化，demo部署，进行测试，优化性能
  • 6 部署和维护，上生产的部署，维护和管理

SQL

sql分为四部分

  • 1 数据定义语言 (DDL)

  • 2 数据操控语言 (DML)

  • 3 数据控制语言 (DCL)

  • 4 事务控制语言（TCL)
    能做到的事情是

  • 1 执行查询

  • 2 检索数据

  • 3 插入数据

  • 4 更新数据

  • 5 删除数据

  • 6 创建库

  • 7 创建表

  • 8 创建存储过程

  • 9 创建视图

  • 10 设置对表 视图 过程的权限
    示例：创建

1
2
3
4
5
6
7
CREATE DATABASE restaurant;
CREATE TABLE menu (id Int, item varchar(10), date DATE);

INSERT INTO menu
VALUES 
(2, '西红柿鸡蛋', '2023-01-16' ),
(3, '清蒸鲈鱼', '2023-01-16' );

检索：

1
2
3
4
SELECT * FROM menu;
SELECT id, item, date
    FROM   menu  
    WHERE  id = 3;

还有其他的子句
GROUP BY 通过按列分组来组织检索到的数据。

HAVING 与 GROUP BY 相关。应用此子句时，它仅选择并返回那些具有指定条件 TRUE 的行。

ORDER BY 对返回的结果集中的记录进行排序。

DISTINCT 从结果集中删除重复项。

LIMIT 控制查询检索的行数。

ALL 返回所有匹配的行。

HIGH_PRIORITY 确定 MySQL 必须在所有等待同一资源的 UPDATE 操作符之前执行 SELECT 语句。

更新

1
2
3
UPDATE menu
SET item = '青椒鸡蛋', date = ’2023-01-25‘
WHERE id = 2;

删除

1
DELETE FROM menu WHERE item_name='青椒鸡蛋';

查询连续出现n次的数字

1
2
3
4
5
6
7
8
9
10
SELECT num AS ConsecutiveNums
FROM (
  SELECT *, 
         @count := IF(@prev = num, @count + 1, 1) AS count, 
         @prev := num
  FROM Logs, (SELECT @count := 0, @prev := NULL) AS vars
  ORDER BY id
) AS tmp
WHERE count >= 3
GROUP BY num

删除重复的电子邮箱

1
2
3
4
5
Delete p1.*
FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

连接

SQL的连接主要是为了多表合并，Join操作允许这一点，多个表通过Join连接起来成为一个更大的表格，进而查找自己想要的数据。连接分四种。

1 内连接：返回两个表满足连接的条件

1
2
3
4
5
6
7
8
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

SELECT column_name(s)
FROM table1, table2
WHERE table1.column_name = table2.column_name;

自连接是一种特殊的内连接

1
2
3
4
SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

2 左外连接：返回左表所有的行，以及右表满足连接的行

1
2
3
4
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

3 右外连接：返回右表所有的行，以及左表满足连接的行

1
2
3
4
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

4 全连接：返回被连接的所有记录，缺少的匹配项目会通过null填充

1
2
3
4
5
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

连接的利弊，尽可能减少三个表的join！

查询优化

  • MySQL 的慢 SQL 优化一般如何来做？除此外还有什么方法优化?
  • MySQL 的 explain 有用过吗？讲一讲数据库的慢查询
  • 如何定位一个慢查询，SQL 慢查询的常见优化步骤是什么?
    围绕这三个问题进行解答

SQL查询优化的策略一般包含：

1 sql语句分析：首先保证合理性，正确性。

2 索引优化：对索引的优化和使用。

3 缓存优化：数据库缓存可以减少磁盘访问，意味着速度上升。

4 查询重构：优化查询条件，减少连接，使用更合理的子查询。

关键本质还是执行查询的过程，计算机计算的次数，所以减少该次数是关键。

  • 表大小：查询命中一个几百万行的表，上亿的表，当然很糟糕。
  • 连接：查询过程不必要的连接了多个表。
  • 聚合：不必要的聚合多行的临时表进行查询，当然浪费了很多计算量。
    还有一些无法控制的，比如其他人也在做并行查询。

还可以使用 EXPLAIN SELECT * FROM table_name WHERE conditions;

也就是EXPLAIN语句来分析性能瓶颈。关注 selete_type, key, rows这三字段。

说说减少查询访问数目

1 消除join，可以把单个查询分解为多个查询，稍后再进行连接，避免斗鱼的连接，子查询。

2 避免使用 SELECT DISTINCT

3 避免使用 SELECT *， 尽可能使用的是 SELECT 字段

索引优化

skip

分库分表

一些计算！

围绕这三个问题进行

  • 为什么要 MySQL 要分库分表
  • 分库分表存在的问题及其解决方法
  • 现在有哪些成熟的分库分表的中间件
    随着数据量不断增加，把一个大数据库拆分为若干的小库是必须，把一张大表拆份成若干个小表也是必须的。综上所述，就是把大的数据量分散到不同的数据库示例和表中。总共四种方式，垂直分库，水平分库，垂直分表，水平分表。

垂直分表：

就是把字段进行拆份，适用于过多字段，且字段之间的联系较小的场景。

1 减少IO冲突，锁表的几率：浏览商品详情和浏览商品信息的不会互相影响。

2 发挥热数据的操作效率：商品信息操作高效不会被商品详情信息(图片，视频，长文本）的访问低效所拖累。

垂直分库

将一个大型的数据库的数据按照业务模块进行拆份，专库专用。

  • 1 解决业务的耦合，业务清晰。
  • 2 能够对不同业务分级管理，维护，监控，拓展（比如有些业务的信息迅速扩张，有些趋于停滞，投放的资源和人力是完全不一样的）。
  • 3 高并发的情况下，分库能提升IO，减少单库连接数目，提升性能。
    应用于业务复杂，表间关联很小的场景。

水平分库

根据库里的数据行进行拆份（比如1-10w行在库A，10w-20w行在库B，不影响表结构的本身)。

1 解决单库数据量大并发瓶颈

2 提升系统的稳定可用性，减少IO冲突，锁定几率

水平切分后，却需要路由的工作，所以系统复杂程度变大。

水平分表

和水平分库类似，对单个大表进行拆份，性质和水平分库类似，不同的是水平分库后的表会分散到不同的库实例里，水平分表的散落的小表依旧存在一个库里。

分库分表后存在的问题

一些分库分表的中间件

比如淘宝的TDDL，还有sharding-jdbc，还有mycat（读写分离，分库分表，容灾管理）

怎么去设计一个表的主键

实时设计一个用户表

分页查找

#

Links

Recents

Archives

Tags

AI(人工智能)21
Agents3
Language(c++)4
Language(python)2
Linux系统2
Triton1
docker容器1
llm(语言模型)17
redis1
unfinish1
前端1
后段架构3
向量数据库1
多线程1
操作系统2
数据2
数据库1
算法2

Subscribe for updates

×