Database Notes

Posted on In

数据库系统

2024.4.22

SQL语句执行过程

  1. 连接器: 连接器负责跟客户端建立连接、获取权限、维持和管理连接
  2. 查询缓存: MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。如果在内存中命中，则直接返回结果
  3. 分析器: 你输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么
  4. 优化器: 优化器是在表里面有多个索引的时候，决定使用哪个索引。或者在一个语句有多表关联(join)的时候，决定各个表的连接顺序
  5. 执行器: MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句

SQL(Structured Query Language)

  • 数据定义（SQL DDL）用于定义SQL模式、基本表、视图和索引的创建和撤消操作
  • 数据操纵（SQL DML）数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作
  • 数据控制（DCL）包括对基本表和视图的授权，完整性规则的描述，事务控制等内容
  • 嵌入式SQL的使用规定（TCL）涉及到SQL语句嵌入在宿主语言程序中使用的规则
  • 关键字：
    • 数据查询：SELECT
    • 数据定义：CREATE(创建), DROP(删除表，模式，视图等), ALTER(修改)
    • 数据操纵：INSERT, UPDATE, DELETE
    • 数据控制：GRANT, REVOKE
    • TABLE(表), VIEW(视图), INDEX(索引), SCHEMA(模式)

数据查询(SELECT)

SELECT [ALL | DISTINCT] <目标列表达式>
FROM <表名或视图名>
WHERE <条件表达式>
GROUP BY <列名> [HAVING <条件表达式>]
ORDER BY <列名> [ASC | DESC]

  • SELECT* 代表查询全部列
  • DISTINCT代表去除重复的，默认为ALL
  • WHERE代表条件匹配，常用的有：比较<, =, >, 范围BETWEEN AND, NOT BETWEEN AND, 确定集合 IN, NOT IN, 字符匹配 LIKE, NOT LIKE, 空置 IS NULL, IS NOT NULL, 逻辑运算 AND, OR, NOT
  • IN, NOT IN用法：可以写成WHERE sdept IN (‘CS’, ‘MATH’), NOT IN (‘FOOD’,’LAW’)
  • 字符匹配：% 代表任意长度（可以为0）的字符串，a%b代表a开头，b结尾，中间任意长度。_ 代表任意单个字符。用法可以是sno LIKE ‘202030323115’ 或 sname LIKE ‘刘%’。ESCAPE代表转义，比如 sname LIKE ‘DB_Design’ ESCAPE ‘' , 代表\为换码字符
  • ORDER BY: ASC升序，DESC降序，默认ASC升序排列，ORDER BY GRADE DESC，按照GRADE 降序排列
  • 聚集函数：COUNT(), COUNT(DISTINCT <列名>), SUM(), AVG(), MAX(), MIN(), 写法：SELECT COUNT(DISTINCT Sno) FROM SC，*聚集函数只能用在SELECT子句与GROUP BY的HAVING子句，不能用在WHERE里面
  • GROUP BY：将查询结果按某一列或多列的值进行分组，值相等的为一组
  • HAVING子句：如果在分组后，需要按照一定条件对组进行筛选，只输出符合条件的，写法：SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) > 3，HAVING作用域组，注意HAVING只能和GROUP BY一起使用，不能和WHERE一起使用
  • 连接查询：连接查询的WHERE子句中用来连接两个表的条件成为连接条件
    • 等值连接：SELECT Student.,SC FROM Student,SC WHERE Student.Sno=SC.Sno
    • 自身连接：表与自己进行连接查询，SELECT FIRST.Cno,SECOND.Cpno FROM Course FIRST,COURSE SECOND WHERE FIRST.Cpno=SECOND.Cno
    • 外连接：若查询结果中有些元组内容是空的，但是仍然想显示出来，可以使用外连接，左外连接(LEFT OUTER JOIN)列出左边表的所有元组, 右外连接(RIGHT OUTER JOIN)列出右边表的所有元组
    • LEFT JOIN：返回左表中的所有行，以及右表中匹配的行。右表没有匹配的行，就用NULL填充
    • RIGHT JOIN：返回右表中的所有行，以及左表中匹配的行。左表没有匹配的行，则用NULL值填充
    • INNER JOIN：返回左右两个表中匹配的行，即返回两个表中共同满足连接条件的行
    • CROSS JOIN：返回两个表中的所有可能的组合，即左表的每一行与右表的每一行进行组合，结果是两个表中行数的乘积
  • 嵌套查询：子查询的SELECT语句中不能使用ORDER BY子句
    • 写法：…… WHERE Sno IN(SELECT ……)
    • 除了使用IN关键字(代表在子查询结果的集合中查找)，也可以使用比较运算符
    • 带有ANY(任意一个)或ALL(所有)的子查询，有时候子查询返回多值，写法：WHERE Sage=ANY(SELECT……)
    • EXISTS: 这个关键字只会产生true或false，不返回数据。若内层非空(NOT NULL)返回True，否则为False
    • NOT EXISTS: 内层为空，则返回True，否则为False
  • 集合查询：包括并操作（UNION），交操作（INTERSECT）和差操作（EXCEPT）。参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同
    • 写法：SELECT * FROM Student WHERE Sdept=’CS’ UNION SELECT * FROM Student WHERE Sage<=19，意义是查询计算机系的学生和年龄小于19岁的。默认UNION DISTINCT（去重）
  • 派生表查询：FROM中，是可以写一个SELECT语句的，写法：FROM SC, (SELECT……) AS T1, AS代表制定一个别名

数据更新

  • 插入数据（INSERT）：
    • INSERT INTO<表名>[<属性列>] VALUES(<插入值>)
    • 字符串常数要用单引号括起来
    • 可以插入子查询的结果，只要写为 INSERT INTO<表名>(<属性列>) SELECT……
  • 修改更新（UPDATA）：
    • UPDATE<表名> SET<列名>=<表达式> WHERE<条件>
    • WHERE子句可以省略，代表更新表的所有元组
    • 可以插入子查询结果，写法：UPDATE SC SET Grade=0 WHERE Sno IN(SELECT……)
  • 删除（DELETE）：
    • DELETE FROM<表名> WHERE<条件>
    • 删除的是表中的数据，如果要删除表，应该使用DROP
    • WHERE可省略，代表删除表的所有元组
    • WHERE子句可以嵌套子查询

空值

  • 空值为NULL
  • 判断空值必须使用IS NULL 或者 IS NOT NULL

索引

  • 数据库索引是一种数据结构，目的是帮助数据库快速检索表中的数据。通过索引，数据库可以避免全表扫描，直接定位到相关的数据行，从而大幅度提高查询效率
  • 种类
    • B+树索引: 所有数据存储在叶子节点，复杂度为O(logn)，适合等值查询，范围查询(Between, <, >), 排序（ORDER BY）。
    • 哈希索引Hash Index: 适合等值(=)查询，检索效率高，一次到位
    • 全文索引Full Text Index: 主要用于快速查找文本中的关键词，常用于查找大量文本字段中的数据
    • 唯一索引Unique Index：唯一索引保证索引列中的所有值都是唯一的（没有重复值）
    • 空间索引Spatial Index: 用于加速空间数据（如地理位置数据）的查询操作，常用R-Tree结构进行存储
  • 什么时候要创建索引
    • 表的主关键字: 自动建立唯一索引
    • 直接条件查询的字段: 经常用于WHERE查询条件的字段，这样能够提高整个表的查询速度。查询中与其它表关联的字段:例如字段建立了外键关系
    • 查询中排序的字段: 排序的字段如果通过索引去访问将大大提高排序速度
    • 唯一性约束列: 如果某列具有唯一性约束，那么为了确保数据的唯一性，可以在这些列上创建唯一索引
    • 大表中的关键列: 在大表中，如果查询的效率变得很低，可以考虑在关键列上创建索引
  • 什么时候不用创建索引
    • 小表: 对小表创建索引可能会带来额外的开销，因为在小数据集中扫描整个表可能比使用索引更快。
    • 频繁的插入、更新和删除操作: 索引的维护成本会随着数据的插入、更新和删除操作而增加。如果表经常被修改，过多的索引可能会影响性能。
    • 数据重复且分布平均的表字段:假如一个表有10万行记录，性别只有男和女两种值，且每个值的分布概率大约为50%，那么对这种字段建索引一般不会提高数据库的查询速度。
    • 很少被查询的列: 如果某列很少被用于查询条件，那么为它创建索引可能没有明显的性能提升。
    • 查询结果总行数较少的表: 如果查询的结果集总行数很少，使用索引可能不会有太大的性能提升

事务处理

  • 事务transaction：是用户定义的一个数据库操作序列，这些操作要么全做，要么全部做，不可分割
  • COMMIT提交, ROLLBACK回滚
  • 事务4个特性：原子性atomicity，一致性consistency，隔离性isolation，持续性durability，即为ACID特性, 在运行中要保证事务的ACID特性
  • 数据库系统中的故障：
    • 事务内部故障，如计算溢出，死锁等，可以使用事务撤销UNDO
    • 系统故障：OS故障，CPU故障，可以使用重做REDO
    • 介质故障：磁盘损坏，磁场干扰
    • 计算机病毒
  • 数据库回复的可以使用冗余的方法。建立冗余数据最常用的技术是数据转储和登记日志文件。转储分为静态转储和动态转储，海量转储，增量转储

事务特性

  • atomicity: 一个事务的所有操作要么全部完成要么全部不完成，事务是不可分割的最小工作单元
  • consistency: 一个事务执行之前和执行之后必须时一致的状态。（a和b两个账户初始资金总和1000，相互转账多少次资金总和必须还是1000）
  • isolation：允许多个并发的事务同时对数据修改和读取，执行互不干扰，防止多个事务并发执行由于交叉执行造成数据不一致的情况。和隔离的级别有关,例如read和commitied,一个事务只能读到已经提交的修改
  • durability： ⼀个事务⼀旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作

并发事务控制

  • 并发事务操作即同时执行多个事务
  • 并发事务操作会带来数据不一致性，包括修改丢失phantom read，不可重复读no-repeatable read（无法复现原来读取的结果），读脏数据dirty read（读到的数据和数据库中不一样，可能因为原来事务被撤销了）
  • 为了解决这三个问题，引出隔离级别：包括读未提交read uncommitted, 读提交read committed，可重复读repeatable read，串行化serializable
  • 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
  • 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
  • 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
  • 串行化，顾名思义是对于同一行记录，“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

并发控制技术

  • 事务是并发控制的基本单位
  • 并发控制技术：封锁locking，时间戳timestamp，乐观控制法optimistic scheduler，多版本并发控制MVCC
  • 封锁locking：即为加锁，分为排它锁（exclusive locks，X锁，写锁）与共享锁（share lock，s锁，读锁）。写锁：事务都对象A加锁后，只有该事务可以进行读写，其他事务都不能对A加锁。读锁：当事务对对象A加锁后，该事务可以读但是不能写，其他事务可以加读锁不能加写锁。
    • 一级封锁协议：事务在修改数据时必须加X锁，直到事务结束释放
    • 二级封锁协议：在一级基础上，在读取数据时必须加S锁，读取完释放S锁
    • 三级封锁协议：在一级基础上，在读取数据时必须加S锁，直到事务结束释放
    • 活锁：某个事务可能永远在等待某个关键资源，可以采用先来先服务策略解决
    • 死锁：某两个事务由于互相等待，永远不能结束，造成死锁。
      死锁的预防：一次封锁发（事务一次性对所有需要的资源加锁），顺序封锁法（规定封锁顺序，然后按规定进行封锁）
      死锁诊断和解除：超时法（等待时间超过界限）等待图法（用有向图来表示事务，若形成环，则为死锁）
  • 可串行化调度（serializable）：多个事务的并发执行是正确的，当且仅当其结果与按某一次序穿行的执行这些事务时的结果相同。可串行性是并发事务正确调度的准则

MVCC（Multi-Version Concurrency Control，多版本并发控制）

  • 是一种用于处理数据库并发操作的技术，它允许多个事务并发地读取和写入数据库，而不会互相阻塞。MVCC 在数据库中通过保留数据的多个版本来实现，能够在保证数据一致性的同时提高并发性，避免了许多锁机制带来的性能问题
  • MVCC 通过在每次修改数据时为数据保留不同的版本，使多个事务可以访问数据库的不同“快照”（版本），从而实现事务的隔离和并发控制。具体而言，MVCC允许：
    • 读操作 不会被写操作阻塞
    • 写操作 可以在不同的事务中同时进行，只要它们不修改相同的数据
  • 工作原理：为数据保留多个版本，避免直接使用锁来管理并发控制。MVCC 系统通过为每条记录附加额外的信息来追踪事务之间的关系，常见的附加信息包括：
    • 创建版本号（事务开始时间戳）：表示该版本是由哪个事务创建的
    • 删除版本号（事务结束时间戳）：表示该版本在哪个事务中被删除或更新
    • 事务ID：用来标识哪个事务操作了这条记录
  • 优点：
    • 提高并发性：MVCC允许读写操作同时进行，提高了并发性能
    • 避免读写冲突：读操作不会被阻塞，因为读取的是事务的数据历史版本
    • 实现快照隔离snapshot isolation: MVCC 可以为每个事务提供独立的数据快照，使得每个事务看到的都是事务开始时的数据视图，保证了事务的隔离性和一致性
    • 避免锁等待和死锁: 由于读操作不需要等待写锁，MVCC 可以有效避免锁等待和死锁的发生，从而提高系统的吞吐量和稳定性
  • 相关实现：MySQL的InnoDB存储引擎通过Undo Logs来实现。每次更新操作都会生成一条回滚记录，并存储到 Undo Logs中，新的修改则应用到当前数据行。查询时，InnoDB会根据事务的开始时间和Undo Logs中的历史版本判断应该返回哪个版本的数据

关系型数据库与索引、NoSQL数据库与分片、分布式文件系统与复制、以及内存数据库

  • 关系型数据库与索引（Relational Database with Indexing）：SQLServer，MySQL
    • 适用于结构化数据管理（适用于管理结构化且有明确关系的数据，如客户信息、订单、产品等），复杂查询，事务处理（通过ACID特性提高数据一致性和完整性）等场景、
    • 适用于电子商务系统、银行系统、ERP系统
  • NoSQL数据库与分片（NoSQL Database with Sharding）：Redis，MongoDB
    • 大规模非结构化数据：适用于存储和处理非结构化或半结构化数据，如社交媒体内容、日志数据、物联网（IoT）数据
    • 高并发写入：NoSQL数据库设计为水平扩展，支持高并发写入和读取操作，适用于需要处理大量实时数据的应用
    • 灵活的数据模型：NoSQL数据库无需预定义数据模式，适用于数据模型频繁变化或灵活性较高的场景，如社交平台、在线游戏和实时分析系统
    • 适用于社交网络平台、日志系统、物联网数据存储、实时推荐系统
  • 分布式文件系统与复制（Distributed File System with Replication）
    • 大规模文件存储
    • 高可用性和冗余：复制机制，分布式文件系统能确保数据冗余和高可用性，适用于需要数据备份和故障恢复的场景
    • 批处理大数据：常用于处理大规模的批量数据分析任务，如Hadoop等分布式大数据处理平台
    • 适用于大数据分析、视频点播平台、云存储服务（如HDFS）
  • 内存数据库（In-memory Database）
    • 高速读写需求：适用于需要极低延迟的读写操作场景，如实时数据分析、缓存系统、交易系统。
    • 实时性要求高：如在线交易处理系统、实时数据分析平台等，内存数据库能够提供毫秒级的响应时间。
    • 数据暂时性或频繁更新
    • 适用于Redis缓存、实时交易平台、实时数据分析、在线游戏的排行榜或状态存储