《前后端面试题》专栏集合了前后端各个知识模块的面试题，包括html，javascript，css，vue，react，java，Openlayers，leaflet，cesium，mapboxGL，threejs，nodejs，mangoDB，MySQL，Linux… 。

前后端面试题-专栏总目录

一、本文面试题目录

21. 写出查询表中重复数据并删除的SQL语句。

查询重复数据（以users表的email列为例）：

SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

删除重复数据（保留最小id的记录）：

DELETE FROM users 
WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email
);

22. 如何实现批量插入数据？COPY命令与INSERT相比有什么优势？

批量插入方法：

1. 多值INSERT：

   INSERT INTO users (name, age) 
   VALUES ('Alice', 25), ('Bob', 30), ('Charlie', 35);
   

2. COPY命令（从文件导入）：

   psql -c "\COPY users (name, age) FROM '/path/to/data.csv' WITH CSV HEADER"
   

COPY优势：

• 性能：比INSERT快10-100倍，直接写入数据文件，跳过SQL解析。
• 事务安全：支持在事务中执行，失败时自动回滚。
• 处理大文件：适合导入GB级数据，内存占用低。

23. 解释窗口函数（Window Function）的作用，举例说明ROW_NUMBER()、RANK()的用法。

窗口函数作用：
在分组数据上执行计算，不合并结果行，保留原始记录。语法：

FUNCTION() OVER (PARTITION BY col ORDER BY col)

示例（按部门排序员工）：

SELECT name, dept_id, salary,ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank,RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank
FROM employees;

• ROW_NUMBER()：连续排名（1,2,3），即使值相同。
• RANK()：跳跃排名（1,1,3），相同值排名相同，后续排名跳过。

24. 如何使用JOIN（内连接、左连接、全连接）实现多表关联查询？

示例表结构：

CREATE TABLE departments (id INT PRIMARY KEY, name TEXT);
CREATE TABLE employees (id INT PRIMARY KEY, name TEXT, dept_id INT REFERENCES departments(id));

JOIN类型：

1. 内连接（INNER JOIN）：只返回匹配的记录。

   SELECT e.name, d.name 
   FROM employees e 
   INNER JOIN departments d ON e.dept_id = d.id;
   

2. 左连接（LEFT JOIN）：返回左表所有记录，右表无匹配时补NULL。

   SELECT e.name, d.name 
   FROM employees e 
   LEFT JOIN departments d ON e.dept_id = d.id;
   

3. 全连接（FULL OUTER JOIN）：返回左右表所有记录，无匹配时补NULL。

   SELECT e.name, d.name 
   FROM employees e 
   FULL OUTER JOIN departments d ON e.dept_id = d.id;
   

25. 写出按条件更新数据的SQL，如何避免更新时的锁冲突？

更新示例（将部门ID为1的员工工资提高10%）：

UPDATE employees 
SET salary = salary * 1.1 
WHERE dept_id = 1;

避免锁冲突的方法：

1. 分批更新：

   UPDATE employees 
   SET salary = salary * 1.1 
   WHERE dept_id = 1 
   LIMIT 1000;  -- 每次更新1000条，循环执行
   

2. 降低隔离级别：使用READ COMMITTED（默认）而非REPEATABLE READ。
3. 减少锁持有时间：避免在事务中执行耗时操作。
4. 使用HOT UPDATE：确保更新时不改变索引列，减少索引锁。

26. 如何查询表的大小、索引大小？使用哪些系统函数？

查询表和索引大小：

-- 表大小（包含TOAST和空闲空间）
SELECT pg_size_pretty(pg_total_relation_size('table_name'));-- 索引大小总和
SELECT pg_size_pretty(pg_indexes_size('table_name')
);-- 每个索引的大小
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes 
WHERE relname = 'table_name';

关键函数：

• pg_total_relation_size()：表总大小（含索引、TOAST）。
• pg_relation_size()：表或索引的原始大小。
• pg_size_pretty()：将字节转换为易读单位（如MB、GB）。

27. 什么是临时表？临时表的生命周期如何管理？

临时表：
临时表是会话级或事务级的表，数据仅对当前会话/事务可见。

创建与生命周期：

-- 会话级临时表（会话结束时自动删除）
CREATE TEMP TABLE temp_users (id INT, name TEXT);-- 事务级临时表（事务结束时自动删除）
CREATE TEMP TABLE temp_logs (msg TEXT) ON COMMIT DROP;

特点：

• 数据存储在pg_temp_*模式中，与主表隔离。
• 可提高复杂查询性能（如缓存中间结果）。

28. 如何实现数据的导入导出（如导出为CSV、导入外部数据）？

导出为CSV：

# 方法1：使用psql \copy命令（客户端执行）
psql -c "\COPY users TO '/path/to/export.csv' WITH CSV HEADER"# 方法2：使用SQL（服务端执行，需文件权限）
COPY users TO '/var/lib/postgresql/export.csv' WITH CSV HEADER;

导入CSV：

psql -c "\COPY users FROM '/path/to/import.csv' WITH CSV HEADER"

注意事项：

• \copy由客户端处理，COPY由服务端处理。
• 服务端路径需为PostgreSQL用户可访问的位置。

29. 解释聚合函数（如SUM、COUNT）与GROUP BY的使用场景，如何处理NULL值？

聚合函数与GROUP BY：

• 聚合函数：对一组值执行计算（如SUM()、COUNT()、AVG()）。
• GROUP BY：将结果按指定列分组，每组应用聚合函数。

示例：

-- 计算各部门的总工资（排除NULL值）
SELECT dept_id, SUM(salary) 
FROM employees 
GROUP BY dept_id;-- 计算员工总数（包含NULL值的行）
SELECT COUNT(*) FROM employees;-- 计算非NULL值的数量
SELECT COUNT(salary) FROM employees;

处理NULL值：

• 使用COALESCE(salary, 0)将NULL替换为0。
• COUNT(*)统计所有行，COUNT(col)忽略NULL值。

30. 如何使用正则表达式进行模糊查询？举例说明_、*操作符的区别。

正则表达式查询：

• ~：大小写敏感匹配。
• ~*：大小写不敏感匹配。

示例：

-- 查询以"Mr."开头的名字（大小写敏感）
SELECT * FROM users WHERE name ~ '^Mr\.';-- 查询包含数字的邮箱（大小写不敏感）
SELECT * FROM users WHERE email ~* '[0-9]+';

常用元字符：

• ^：行首匹配。
• $：行尾匹配。
• .：任意单个字符。
• *：零次或多次重复。
• +：一次或多次重复。

31. 写出查询某列非空且满足特定条件的SQL，如何优化这类查询的性能？

查询示例（查找年龄非空且大于30的员工）：

SELECT * FROM employees 
WHERE age IS NOT NULL AND age > 30;

优化方法：

1. 创建索引：

   CREATE INDEX idx_age ON employees (age) WHERE age IS NOT NULL;  -- 部分索引
   

2. 避免函数操作：

   -- 低效：函数导致索引失效
   WHERE UPPER(name) = 'JOHN';-- 高效：使用表达式索引
   CREATE INDEX idx_name_upper ON employees (UPPER(name));
   

3. 统计信息更新：

   ANALYZE employees;  -- 更新统计信息，帮助优化器选择索引
   

32. 如何创建和使用函数（Function）？函数与存储过程（Procedure）的区别是什么？

创建函数（计算部门平均工资）：

CREATE FUNCTION get_dept_avg(dept_id INT) 
RETURNS NUMERIC AS $$
BEGINRETURN (SELECT AVG(salary) FROM employees WHERE employees.dept_id = get_dept_avg.dept_id);
END;
$$ LANGUAGE plpgsql;

调用函数：

SELECT get_dept_avg(1);

函数 vs 存储过程：

特性	函数（Function）	存储过程（Procedure）
返回值	必须返回值（RETURNS）	无返回值（VOID）
事务控制	不可使用COMMIT/ROLLBACK	可控制事务
调用方式	在SQL表达式中调用	使用CALL语句调用
副作用	应无副作用（幂等）	可执行修改操作

33. 如何使用触发器（Trigger）实现数据的自动校验或日志记录？

示例1：自动校验（禁止删除活跃用户）

CREATE FUNCTION prevent_active_user_deletion() 
RETURNS TRIGGER AS $$
BEGINIF OLD.status = 'active' THENRAISE EXCEPTION 'Cannot delete active user';END IF;RETURN OLD;
END;
$$ LANGUAGE plpgsql;CREATE TRIGGER check_active_user
BEFORE DELETE ON users
FOR EACH ROW EXECUTE FUNCTION prevent_active_user_deletion();

示例2：日志记录（记录员工工资变更）

CREATE TABLE salary_log (emp_id INT,old_salary NUMERIC,new_salary NUMERIC,changed_at TIMESTAMP DEFAULT NOW()
);CREATE FUNCTION log_salary_change() 
RETURNS TRIGGER AS $$
BEGININSERT INTO salary_log (emp_id, old_salary, new_salary)VALUES (OLD.id, OLD.salary, NEW.salary);RETURN NEW;
END;
$$ LANGUAGE plpgsql;CREATE TRIGGER salary_change_log
AFTER UPDATE OF salary ON employees
FOR EACH ROW EXECUTE FUNCTION log_salary_change();

34. 如何查询慢查询日志？如何分析慢查询的瓶颈？

启用慢查询日志（修改postgresql.conf）：

log_statement = 'all'          # 记录所有SQL
log_min_duration_statement = 1000  # 记录执行时间超过1秒的查询

分析方法：

1. 查看日志文件（通常位于pg_log/目录）：

   grep 'duration' /var/lib/postgresql/data/pg_log/postgresql-*.log
   

2. 使用EXPLAIN ANALYZE：

   EXPLAIN ANALYZE 
   SELECT * FROM orders WHERE customer_id = 123;
   

3. 关注指标：
   • cost：优化器估算的执行成本。
   • actual time：实际执行时间。
   • rows：处理的行数。

35. 解释DISTINCT与DISTINCT ON的区别，举例说明DISTINCT ON的用法。

区别：

• DISTINCT：对结果集的所有列去重。
• DISTINCT ON：对指定列去重，保留每行的首条记录。

示例（保留每个部门工资最高的员工）：

SELECT DISTINCT ON (dept_id) dept_id, name, salary
FROM employees
ORDER BY dept_id, salary DESC;  -- 按工资降序，确保最高工资优先

结果：

dept_id	name	salary
1	Alice	8000
2	Bob	7500

36. PostgreSQL有哪些索引类型？GiST、GIN索引的适用场景是什么？

索引类型：

• B-Tree（默认，支持=、<、>等）
• Hash（仅支持=）
• GiST（通用搜索树）
• GIN（倒排索引）
• BRIN（块范围索引）
• SP-GiST（空间分区GiST）

GiST适用场景：

• 空间数据（如PostGIS的几何类型）。
• 全文搜索（如tsvector类型）。
• 范围查询（如int4range）。

GIN适用场景：

• 多值类型（如数组、JSONB）。
• 全文搜索（支持多个关键词查询）。

示例：

-- GiST索引（用于几何类型）
CREATE INDEX idx_geom ON places USING GIST (location);-- GIN索引（用于JSONB）
CREATE INDEX idx_data ON events USING GIN (data jsonb_path_ops);

37. 什么是部分索引（Partial Index）？如何通过部分索引优化查询性能？

部分索引：
仅对表中符合条件的行创建索引，减少索引大小，提高查询效率。

示例（仅索引活跃用户）：

CREATE INDEX idx_active_users ON users (email) 
WHERE status = 'active';

优化场景：

• 过滤频繁的数据：如WHERE deleted = false（软删除场景）。
• 覆盖索引：包含查询所需的所有列，避免回表。

CREATE INDEX idx_cover ON orders (customer_id) 
INCLUDE (order_date, total)  -- PostgreSQL 11+支持INCLUDE
WHERE status = 'paid';

38. 解释索引选择性（Selectivity）的概念，如何判断索引是否有效？

索引选择性：
索引列中不同值的比例，计算公式：

选择性 = 唯一值数量 / 总行数  

选择性越高（接近1），索引效率越好。

判断索引有效性：

1. 查询执行计划：

   EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
   

   • 若显示Index Scan或Bitmap Index Scan，索引有效。
   • 若显示Seq Scan，可能索引未生效。

2. 统计信息：

   SELECT relname, reltuples, relpages 
   FROM pg_class 
   WHERE relname = 'users';
   

   • 行数（reltuples）与页数量（relpages）比值低时，顺序扫描可能更快。

39. 为什么有时索引会失效？列举导致索引失效的常见情况。

索引失效原因：

1. 表达式或函数操作：

   WHERE UPPER(name) = 'JOHN';  -- 函数导致索引失效
   

2. 隐式类型转换：

   WHERE id = '123';  -- 若id为INT，字符串比较会触发全表扫描
   

3. 低选择性数据：

   WHERE is_active = true;  -- 若90%的行都是true，索引可能不被使用
   

4. 统计信息过时：

   ANALYZE users;  -- 更新统计信息
   

5. 错误的查询条件：

   WHERE name LIKE '%john';  -- 以通配符开头的LIKE无法使用索引
   

40. 如何查看查询的执行计划？如何通过执行计划判断是否使用了索引？

查看执行计划：

EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE customer_id = 123 AND order_date > '2023-01-01';

关键判断指标：

1. 操作类型：

   • Index Scan：使用索引扫描。
   • Bitmap Index Scan：使用位图索引。
   • Seq Scan：全表扫描（未使用索引）。

2. 索引名称：

   ->  Index Scan using idx_customer_id on orders  -- 明确使用了idx_customer_id索引
   

3. 成本与行数：

   • cost=0.42..8.44：估算成本。
   • rows=1：估算返回行数，与实际行数（actual rows）对比。

优化建议：

• 若高选择性查询仍使用全表扫描，检查统计信息（ANALYZE）。
• 若索引扫描行数过多，考虑调整查询条件或创建覆盖索引。