BYT0723的博客

Record Programming

0%

sql-interview

发表于 更新于 分类于 Valine：

1. SQL 语句的执行流程

1.1 逻辑剖析

sql 语句 > 查询缓存 > 解释器 > 优化器 > 执行器

2. 如何在生产环境中为表格增加字段

3.事务

3.1 ACID

  • Atomicity，原子操作
  • Consistency，一致性
  • Isolation，隔离性
  • Durability，持久性

解释如下可能会用到 2~3 个事务，这里简称 ABC。

  • 脏写

    1. A 先将数据从 NULL 更新为 1
    2. 在 A 未提交时，B 将数据从 1 更新为 2
    3. A 在执行过程中发生错误并回滚，将数据回滚为 NULL
    4. B 提交后发现数据并不是 2，并且自己的事务并没有执行失败

    B 写失败了

  • 脏读

    1. A 先将数据从 NULL 更新为 1
    2. 在 A 未提交时，B 读取数据为 1
    3. A 在执行过程中发生错误并回滚，将数据回滚为 NULL
    4. B 再次读取数据发现是 NULL

    B 在 step 2 时读取了脏数据

脏写和脏读类似，都是对未提交事务修改的数据进行操作，在该事务发生错误回滚后，就会导致脏写和脏读发生

  • 不可重复读（update）
    A 在执行过程中多次读取数据，但在执行过程中，B 和 C 修改并提交了数据，导致 A 多次读取读取到了不同的值

  • 幻读（insert,delete）
    A 在执行过程中多次查询数据，但在执行过程中，B 和 C 插入并提交新的数据，导致 A 多次查询，查到了不同条数的结果

3.2 事务隔离级别

  • Read Uncommitted（读未提交）
    顾名思义，事务可以读取到其他事务未提交的数据。会发生脏读、不可重复读和幻读。

  • Read Committed（读以提交）
    事务无法读取到其他事务未提交的数据，杜绝脏读出现，但仍然会发生不可重复读和幻读。

  • Repeatable Read（可重复读）
    MVCC(Multi-Version Concurrency Control)，多版本并发控制。事务开始时，会进行快照读，如果想读取新的数据，可进行当前读。当然这样会出现不可重复读和幻读的现象

  • Serializable（序列化）
    事务按照顺序执行，需要的数据行或表都会被加锁

4. clickhouse 这种列式数据库和传统数据库的优劣

  1. clickhouse
    优点：

    • 高效查询：列式数据库，可快速进行聚合查询、分析等，有较强的并发处理能力
    • 可扩展性：clickhouse 可水平扩展，通过添加更多节点来扩展数据库的存储和处理能力

    缺点：

    • 数据更新操作相对较慢：不适合需要频繁进行数据更新的场景
    • 不支持复杂的关系型数据库模型：由于是列式数据库，不支持传统的关系型数据库的数据模型

  2. mysql 等
    优点：

    • 事务支持：传统关系型数据库有强大的事务支持，可以确保数据的一致性和可靠性
    • 灵活性：支持复杂的查询和数据操作，可以满足各种不同的场景
    • 成熟的生态系统：传统的关系型数据库有很多成熟的工具和框架，可以帮助开发人员进行开发、测试和部署。

    缺点：

    • 不太适合大数据场景：传统的关系型数据库通常不太适合处理大数据，会遇到性能瓶颈
    • 可扩展性有限：传统数据库扩展性有点，需要进行垂直扩展即，增加更多的计算和存储资源，这会带来较高的成本。

5. 开窗函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 排序
row_number() over([partition by ...] [order by ...])
rank()       over([partition by ...] [order by ...])
dense_rank() over([partition by ...] [order by ...])

-- 聚合
count() over([partition by ...] [order by ...])
max()   over([partition by ...] [order by ...])
min()   over([partition by ...] [order by ...])
sum()   over([partition by ...] [order by ...])
avg()   over([partition by ...] [order by ...])

-- 其它
first_value() over([partition by ...] [order by ...])
last_value()  over([partition by ...] [order by ...])
lag()         over([partition by ...] [order by ...])
lead()        over([partition by ...] [order by ...])
  1. 排序开窗函数
函数名 功能
row_number() 相同值排名顺延，结果1,2,3,4
rank() 相同值排名相同，但不顺延，结果1,2,2,4
dense_rank() 相同值排名相同，但结果顺延,结果1,2,2,3
ntile(n) 将结果分为 n 组, 结果1,1,2,2,...,n,n
  1. 聚合函数
函数名 功能
sum() 分组求和
count() 分组求总数
min() 分组求最小值
max() 分组求最大值
avg() 分组求平均值
  1. 其他函数
函数名 功能
lag(column,n,0) 返回 column 字段向上第 n 行数值，若不存在默认为第三个参宿，若第三个参数无，则默认为 null
lead(column,n,0) 同上，但是为向下偏移
first_value() 分组内排序后，截止目前行的第一个值
last_value() 分组内排序后，截止目前行的最后一个值

开窗实践

1
......