《前后端面試題》專欄集合了前后端各個知識模塊的面試題，包括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）。
• 若索引掃描行數過多，考慮調整查詢條件或創建覆蓋索引。