跳转至

数据库面试题（山月）

如何写一个 SQL 获取分组 top n 的数据

原文：https://q.shanyue.tech/server/db/7.html

更多描述

一个学校中的每个学生属于一个班级，如何获取每个班的前三名

假设有学生表 student(id, score, class_id)，score 代表分数，class_id 代表班级，如何写出 SQL 取每个班级分数前三名

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 7(opens new window)

Author

回答者: shfshanyue(opens new window)

通过一个自连接可以获取到

select id, class_id, score from student s1 where (
  -- 对比每行 student，通过自连接找到该班中比该 student 分数要高与等于的人数，即排名
  select count(*) from student s2 where s2.class_id = s1.class_id and s2.score >= s1.score
) = 1

Author

回答者: Skylor-Tang(opens new window)

select id, class_id, score from student s1 where exists(select count(*) from student s2 where s2.score >= s1.score and s2.class_id=s1.class_id group by s2.class_id having count(*)<=3) order by class_id, score desc;

如何写一个 SQL 获取分组 top 1 的数据

原文：https://q.shanyue.tech/server/db/8.html

更多描述

一个学校中的每个学生属于一个班级，如何获取每个班的前三名

假设有学生表 student(id, score, class_id)，score 代表分数，class_id 代表班级，如何写出 SQL 取每个班级分数前一名

与 Top n(opens new window) 类似，不过有更简单的方法

（当然考虑到并列情况就会复杂很多

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 8(opens new window)

Author

回答者: shfshanyue(opens new window)

单纯的获取 Top 1，只需要 group by 加聚合函数，不过复杂的还是参考 top n(opens new window)

select class_id, max(score) from student group by class_id

在 postgres 中，查询时如何对 jsonb 数据格式化

原文：https://q.shanyue.tech/server/db/25.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 25(opens new window)

Author

回答者: shfshanyue(opens new window)

使用 jsonb_pretty 函数，示例如下

> select jsonb_pretty('{"a": {"b": 4}}'::jsonb)
+----------------+
| jsonb_pretty   |
|----------------|
| {              |
|     "a": {     |
|         "b": 4 |
|     }          |
| }              |
+----------------+
SELECT 1
Time: 0.018s

数据库 postgres 与 mysql 相比有哪些优劣

原文：https://q.shanyue.tech/server/db/159.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 159(opens new window)

当执行 SQL 语句 select * from user where id = 1 时发什么了什么

原文：https://q.shanyue.tech/server/db/181.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 181(opens new window)

什么是隔离级

原文：https://q.shanyue.tech/server/db/208.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 208(opens new window)

在 postgres 中如何查看慢查询语句

原文：https://q.shanyue.tech/server/db/209.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 209(opens new window)

如何得知一条 SQL 执行的时长？

原文：https://q.shanyue.tech/server/db/210.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 210(opens new window)

在 postgres/mysql 中如何判断当前版本是多少

原文：https://q.shanyue.tech/server/db/213.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 213(opens new window)

Author

回答者: shfshanyue(opens new window)

使用 select version()

postgres 示例如下:

postgres@db:school> select version()
+---------------------------------------------------------------------------------------+
| version                                                                               |
|---------------------------------------------------------------------------------------|
| PostgreSQL 12.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit |
+---------------------------------------------------------------------------------------+
SELECT 1
Time: 0.028s

mysql 示例如下：

> select version()
+-------------+
| version()   |
|-------------|
| 5.6.16-log  |
+-------------+
1 row in set
Time: 0.003s

什么是隔离级，都有哪些隔离级

原文：https://q.shanyue.tech/server/db/214.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 214(opens new window)

Author

回答者: shfshanyue(opens new window)

隔离级事关并发事务的隔离机制，ANSI SQL 中定义了四种隔离级，分别是

  • Read Uncommited
  • Read Committed
  • Repetable Read
  • Serializable

数据库中如何查看当前的连接数

原文：https://q.shanyue.tech/server/db/223.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 223(opens new window)

Author

回答者: shfshanyue(opens new window)

PostgresSQL 的最大连接数与当前连接数

-- 最大连接数
show max_connections;

-- 当前连接数
select count(*) from pg_stat_activity;

mysql 的最大连接数与当前连接数

-- 最大连接数
show variables like 'max_connections';

-- 当前连接数
show full processlist;

数据库查询如何更好地利用数据库缓存进行优化

原文：https://q.shanyue.tech/server/db/224.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 224(opens new window)

误操作了一个 delete from 语句，如何恢复数据

原文：https://q.shanyue.tech/server/db/225.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 225(opens new window)

数据库死锁是怎么产生的

原文：https://q.shanyue.tech/server/db/227.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 227(opens new window)

Author

回答者: shfshanyue(opens new window)

多个事务对资源的交替顺序访问，如

事务 1，访问 A 表，A 表锁住，访问 B 表，此时 B 表却被事务 2 锁住，等待 事务 2，访问 B 表，B 表锁住，访问 A 表，此时 A 表却被事务 1 锁住，等待

由此观之，此死锁出现的条件极为苛刻

  1. 并发，产生多个事务
  2. 顺序，对相同资源的不同顺序访问 (干嘛要不同顺序呀)
  3. 时机，恰好两个事物都刚刚走完了第一步

更多示例(opens new window)

在数据库中一个字段如果是字符串类型的，那应该设置哪种数据类型

原文：https://q.shanyue.tech/server/db/230.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 230(opens new window)

Author

回答者: shfshanyue(opens new window)

以下答案仅说明在 postgres 中的情况：

在 postgres 有三种 varchar，char 以及 text，其中三者没有性能差异，见官方文档

Different from other database systems, in PostgreSQL, there is no performance difference among three character types. In most situation, you should use text or varchar, and varchar(n) if you want PostgreSQL to check for the length limit

所以，选择 text 是最好的方案

一个关于并发更新的事务以及隔离级的问题

原文：https://q.shanyue.tech/server/db/234.html

更多描述

当多并发场景下有以下事务执行 (计数器自增)，会出现什么问题

begin;

-- select count from counter;

update counter set count = count + 1 where id = 1;

commit;

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 234(opens new window)

Author

回答者: shfshanyue(opens new window)

如果在 pg 下:

如果隔离级为 RC，则多个事务在更新同一行时，会受到阻塞 (Row Lock) 如果隔离级为 RR，则多个事务在更新同一行时，会报错

could not serialize access due to concurrent update

mysql 未测试

以下 SQL 语句会有什么问题 (悲观锁)

原文：https://q.shanyue.tech/server/db/235.html

更多描述

一个计数器，自增一

begin;
select count from user;

-- 根据以上 SQL 查询出来的 count 来进行自增
-- 如果在此次事务中，已有多次事务对 count 进行了多次更改怎么办？
update user set count = $count + 1 where id = 1;
commit;

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 235(opens new window)

Author

回答者: shfshanyue(opens new window)

在并发情况下会出现问题，先查看本次事务的流程

  1. 查询当前计数 -> 此时为 10
  2. 对当前计数自增 -> 此时为 11？在自增时，有可能被多并发的其它事务已经自增到 100 了，此时若设置为 11，肯定有问题

如何解决？

要在第一步时加锁，同一时间只放行一个事务，可以设置分布式锁和悲观锁

  • 分布式锁 (redis)：SET LOCK_KEY RANDOM_VALUE EX 100 NX
  • 悲观锁：select count from user for update

一张员工表，一个字段代表它的上级，如何查询该员工的所有上级

原文：https://q.shanyue.tech/server/db/272.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 272(opens new window)

解释下 OLTP 与 OLAP

原文：https://q.shanyue.tech/server/db/277.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 277(opens new window)

mysql 如何开启及查看慢查询日志

原文：https://q.shanyue.tech/server/db/333.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 333(opens new window)

当分页 10000 页时数据库查询 offset 过大如何解决性能问题

原文：https://q.shanyue.tech/server/db/334.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 334(opens new window)

当数据库进行版本升级时，如何进行数据迁移

原文：https://q.shanyue.tech/server/db/335.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 335(opens new window)

mysql 是如何实现 MVCC 的

原文：https://q.shanyue.tech/server/db/336.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 336(opens new window)

试着讲述数据库的几个范式

原文：https://q.shanyue.tech/server/db/343.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 343(opens new window)

为什么在 mysql 不能够使用 = null

原文：https://q.shanyue.tech/server/db/344.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 344(opens new window)

A, B 复合索引时，A=? and B=? 与 B=? and A=? 效果是否一致

原文：https://q.shanyue.tech/server/db/345.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 345(opens new window)

postgres 中 Index Scan 与 Index Only Scan 有何区别

原文：https://q.shanyue.tech/server/db/348.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 348(opens new window)

Author

回答者: shfshanyue(opens new window)

假设在 user 表简历索引 (name, age)

Index Scan 指查询中除利用索引外还有额外的查询条件，如 (name, age) 是索引，但是 sex 不是

select * from user where name = '张三' and age = 10 and sex = 'MALE'

Index Only Scan 指查询中可以全部利用索引，如 (name, age) 都是索引

select * from user where name = '张三' and age = 10

如何保存数据库与缓存的双写一致性

原文：https://q.shanyue.tech/server/db/371.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 371(opens new window)

你们的后端项目的数据库索引做了哪些优化

原文：https://q.shanyue.tech/server/db/384.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 384(opens new window)

什么是覆盖索引

原文：https://q.shanyue.tech/server/db/386.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 386(opens new window)

Author

回答者: shfshanyue(opens new window)

假设一个用户表 User(id primary, name unique, age)，此时 name 为唯一索引。

聚合索引: select * from User where id in (1, 2, 3, 4, 5)，由于 id 都在 B+ tree 的叶子节点，不需要去磁盘进一步 IO，称作聚合索引

假设有一个需求: 根据 name 查询该人对应的 age，在现有情况下是这样的：

  1. 根据 name 上的 B+ tree 快速找到该行对应的 id
  2. 根据 id 上的 B+ tree 快速找到该行对应的 age，此时需要通过主键 id 进一步 IO，称作回表

如果建立一个联合索引 (name, age)，直接在 name 上的 B+ tree 就可以找到它对应的 age，省了回表操作，称为覆盖索引

数据库设计时 char 与 varchar 有什么区别

原文：https://q.shanyue.tech/server/db/445.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 445(opens new window)

mysql 中 limit offset 过大导致查询缓慢的原因及解决方案

原文：https://q.shanyue.tech/server/db/467.html

Issue

欢迎在 Gtihub Issue 中回答此问题: Issue 467(opens new window)

Author

回答者: shfshanyue(opens new window)

SELECT * FROM User LIMIT 1000000, 10

如上语句，跳过 1000000 行用户，取其后的 10 个用户。其中 offset 过大，将会进行 1000000 行扫描，导致磁盘 IO 多大，拖垮数据库

解决方案也很简单: 取出这 10 个用户的 ID，由于查询 ID 使用聚簇索引，避免 100000 行的磁盘 IO，再通过子查询找到这 10 个用户的所有数据

更多链接: Mysql index configuration(opens new window)

我们一直在努力

apachecn/AiLearning

【布客】中文翻译组