提升性能：Java工程師必備的20條SQL最佳實踐
環境：MySQL
1. 簡介
在項目開發中，SQL優化的最佳實踐是每位Java工程師必須掌握的關鍵技能。高效的數據庫交互可以顯著提升應用程序的性能，帶來更快的響應時間和更好的用戶體驗。
本篇文章將深入探討了SQL查詢優化的最佳實踐，特別針對Java工程師量身定制。從理解索引的重要性到精通 join 操作以及利用連接池，在本文中涵蓋了編寫高效且高性能SQL查詢所需的所有基本技術及最佳實踐。
2. 實戰案例
2.1 使用索引
索引可以讓數據庫快速定位和訪問數據，從而大大提高查詢性能。
- 在用于WHERE、JOIN、ORDER BY和GROUP BY子句的列上創建索引。
- 使用覆蓋索引來包含查詢所需的所有列。
錯誤示例
SELECT * FROM users WHERE name = 'pack'正確示例
CREATE INDEX idx_name ON users (name);
SELECT name, email FROM users WHERE name = 'pack';在users表的name字段創建索引，以加快查詢效率。
基于函數索引
當頻繁地根據函數或表達式的結果進行搜索或排序時，基于函數的索引可以顯著提高查詢性能。
- 為WHERE、ORDER BY或JOIN條件中常用的表達式創建基于函數的索引。
- 使用基于函數的索引來優化涉及不區分大小寫的搜索或日期/時間操作的查詢。
錯誤示例
SELECT * FROM org WHERE UPPER(pos_code) = 'abc'正確示例
ALTER TABLE org
ADD COLUMN code_upper VARCHAR(100) AS (UPPER(pos_code)) STORED ;
CREATE INDEX idx_code ON org (code_upper) ;注：MySQL 從版本 8.0 開始支持基于函數的索引（也稱為虛擬列索引或表達式索引）。在 MySQL 8.0 之前，MySQL 并不直接支持基于函數的索引。
還有一點需要注意：基于函數的索引可以顯著提高查詢性能，但同時也會增加存儲需求并降低數據修改操作的速度（上面將添加一列，并存儲了對應的數據）。修改原始列也會同步修改對應的虛擬列。
2.2 避免使用 SELECT *
使用 SELECT * 會檢索表中的所有列，這可能會降低效率并導致不必要的數據傳輸。
- 在 SELECT 語句中明確你所需要的列。
錯誤示例
SELECT * FROM users;正確示例
SELECT name, age FROM users;該查詢只獲取name和age列，從而減少了傳輸的數據量。
2.3 正確使用Join
不正確的連接方式可能導致性能問題。為查詢使用正確的join類型。
- 使用 INNER JOIN 來匹配兩個表中的行。
- 使用 LEFT JOIN 來包含左表中的所有行以及右表中匹配的行。
錯誤示例
SELECT u.name, o.create_time FROM users u, orders o
WHERE u.id = o.uid;正確示例
SELECT u.name, o.create_time FROM users u
JOIN orders o ON u.id = o.uid;該查詢使用 INNER JOIN 來合并來自用戶表和訂單表的數據。
2.4 使用WHERE子句過濾數據
在查詢中盡早過濾數據有助于減少處理的數據量。
錯誤示例
SELECT name, age FROM users ;正確示例
SELECT name, age FROM users WHERE status = 0 ;只查詢需要的數據，這里查詢用戶狀態正常的數據，以減少處理的數據量。
2.5 限制返回的行數
如果不需要所有記錄，可使用 LIMIT 子句限制返回的記錄數。
錯誤示例
SELECT name, age FROM users WHERE status = 0 ;正確示例
SELECT name, age FROM users WHERE status = 0 LIMIT 10 ;該查詢會檢索前 10 個有效狀態的用戶，從而減少處理和傳輸的數據量。
2.6 使用EXISTS代替IN
使用 EXISTS 可能比使用 IN 更有效率，尤其是對于大型數據集。
這不是絕對的，請看下面場景：
假設我們有兩個表 orders 和 customers，并且我們想要找出那些至少有一個訂單的所有客戶。
# 1.使用IN
SELECT * FROM customers
WHERE id IN (SELECT cid FROM orders) ;
# 2.使用EXISTS
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cid= c.id);性能對比
- 小結果集：如果 orders 表中的 cid 列有索引，并且結果集很小，IN 可能會更快。
- 大結果集：如果 orders 表中的 cid列沒有索引，或者結果集很大，EXISTS 通常會更快，因為它會在找到第一個匹配項后立即停止。
2.7 避免在 WHERE 子句中使用函數
在 "WHERE" 子句中使用函數可能會使得索引失效，從而導致查詢速度變慢。
錯誤示例
SELECT name, age FROM users WHERE YEAR(create_time) = 2024 ;正確示例
SELECT name, age FROM users
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';該查詢無需使用函數即可對 "create_time" 列進行處理，從而允許使用索引。
2.8 使用 JOIN 代替子查詢
JOIN 通常比子查詢更有效，尤其是對于大型數據集。
錯誤示例
SELECT name, (
-- 這里通過子查詢獲取數據
SELECT create_time
FROM orders
WHERE uid = users.id
) AS create_time
FROM users ;正確示例
SELECT u.name, o.create_time FROM users u
JOIN orders o ON u.id = o.uid ;這里通過JOIN提供了查詢性能。
2.9 優化 GROUP BY 和 ORDER BY 子句
使用 "GROUP BY "和 "ORDER BY "子句可能會耗費大量資源。優化它們可提高性能。
- 在 "GROUP BY "和 "ORDER BY "子句中使用的列上使用索引。
- 減少這些子句中指定的列數。
錯誤示例
SELECT uid, COUNT(*), MAX(create_time) FROM orders
GROUP BY uid, create_time ORDER BY create_time ;正確示例
SELECT uid, COUNT(*) FROM orders
GROUP BY uid ORDER BY uid;查詢按索引列分組和排序，提高了性能。
2.10 使用適當的數據類型
為列選擇正確的數據類型會極大地影響性能和存儲效率。
- 為列使用適當的數據類型。
- 除非必要，避免使用 `TEXT` 或 `BLOB`。
錯誤示例
CREATE TABLE users (
id bigint auto_increment PRIMARY KEY,
name TEXT,
create_time TIMESTAMP
);正確示例
CREATE TABLE users (
id bigint auto_increment PRIMARY KEY,
name VARCHAR 100,
create_time TIMESTAMP
);使用適當的數據類型，提高了性能和存儲效率。
2.11 分析執行計劃
使用 "EXPLAIN" 分析查詢執行計劃并找出性能問題。
EXPLAIN SELECT name, sex, age FROM big_table t WHERE T.name = 'Pack'根據執行結果，分析慢SQL的原因，比如：是否走索引，索引的類型等。
2.12 使用連接池
使用連接池可以減少建立數據庫連接的開銷，提高性能。
- 使用 HikariCP 或 C3P0 等連接池庫。
- 根據應用程序的需求和數據庫的功能配置池的大小。
錯誤示例
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test", "root", "xxxooo"
);
// TODO
conn.close();正確示例
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/test") ;
config.setUsername("root");
config.setPassword("xxxooo");
config.setMaximumPoolSize(10);
HikariDataSource dataSource = new HikariDataSource(config) ;
Connection conn = dataSource.getConnection() ;
// TODO
conn.close() ;在Spring Boot環境中我們只需要在配置文件中進行配置，無需上面這樣自己創建。
2.13 使用批處理
在執行多個插入、更新或刪除操作時，使用批處理可以大大提高性能。
- 批量插入/更新，減少數據庫往返次數。
- 使用預編譯語句進行批處理操作。
錯誤示例
Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
for (User user : userList) {
stmt.executeUpdate("INSERT INTO users (name, age) VALUES ('" + user.getName() + "', user.getAge())") ;
}
stmt.close() ;
conn.close() ;正確示例
Connection conn = dataSource.getConnection() ;
PreparedStatement ps = conn.prepareStatement("INSERT INTO users (name, age) VALUES (?, ?)");
for (User user : userList) {
ps.setString(1, user.getName()) ;
ps.setString(2, user.getAge()) ;
ps.addBatch() ;
}
ps.executeBatch() ;
ps.close() ;
conn.close() ;使用批處理功能來高效插入多條數據。
2.14 優化JOIN
適當優化連接可顯著影響查詢性能，尤其是大數據集。
- 確保連接條件中使用的列已建立索引。
- 連接多個表時，從最小的表開始。
錯誤示例
SELECT u.name, o.create_time FROM orders o
JOIN users u ON u.id = o.uid WHERE u.status = 0 ;正確示例
SELECT u.name, o.create_time FROM users u
JOIN orders o ON u.id = o.uid WHERE u.status = 0 ;該查詢在索引列上連接了 users 和 orders，從而提高了性能。
2.15 優化子查詢
子查詢通常可以用連接或其他更有效的查詢結構來代替。
- 盡可能使用連接而不是子查詢。
- 使用通用表表達式（CTE）進行復雜查詢，以提高可讀性，有時還能提高性能。
錯誤示例
SELECT o.*
FROM orders o
WHERE o.amount > (
SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.customer_id = o.customer_id
);正確示例
-- 計算每個客戶的平均訂單金額
WITH customer_avg_orders AS (
SELECT
customer_id,
AVG(amount) AS avg_amount
FROM
orders
GROUP BY
customer_id
)
-- 找出訂單金額大于其客戶平均訂單金額的訂單
SELECT o.*
FROM orders o
JOIN customer_avg_orders cao ON o.customer_id = cao.customer_id
WHERE o.amount > cao.avg_amount;注：MySQL 從版本 8.0 開始支持 WITH子句。
2.16 優化聚合查詢
在執行聚合查詢時，請使用有效的技術來盡量減少計算負荷。
- 確保 "GROUP BY"子句中使用的列已創建索引。
- 考慮使用匯總表來處理經常匯總的數據。
錯誤示例
SELECT
customer_id,
SUM(amount) AS total_amount,
COUNT(*) AS order_count
FROM
orders
GROUP BY
customer_id;正確示例
-- 創建索引
CREATE INDEX idx_customer_id ON orders (customer_id);
-- 優化后的聚合查詢
SELECT
customer_id,
SUM(amount) AS total_amount,
COUNT(*) AS order_count
FROM
orders
GROUP BY
customer_id;該查詢按 "customer_id" 列分組，為獲得最佳性能，應為該列建立索引。
2.17 使用摘要列
摘要列存儲預先計算的聚合值，從而減少了在查詢執行過程中進行昂貴計算的需要。
錯誤示例
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY uid ;正確示例
ALTER TABLE users ADD total_order_amount DECIMAL(10, 2);
UPDATE users u SET total_order_amount = (SELECT SUM(amount) FROM orders o WHERE o.uid = u.id);這種方法增加了一個摘要列，用于存儲每個用戶的訂單總額。
2.18 使用物化視圖
物化視圖可緩存復雜查詢的結果，從而提高重讀取操作的性能。
錯誤示例
SELECT
uid,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY uid ;正確示例
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT
uid,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY uid;創建一個物化視圖，用于存儲預先計算的用戶訂單信息摘要。
2.19 監控和調整數據庫設置
定期監控和調整數據庫設置，確保最佳性能。
- 根據工作量調整緩沖池大小和緩存大小等內存設置。
- 使用 "EXPLAIN"、"ANALYZE "等工具和特定于數據庫的監控工具來識別和解決性能瓶頸。
2.20 定期審查和重構 SQL 代碼
定期審查和重構 SQL 代碼有助于識別和解決性能問題。
- 定期進行代碼審查，確保優化 SQL 查詢。
- 將復雜的查詢分解成更簡單、更高效的部分。
錯誤示例
-- 原始復雜查詢
SELECT u.name,
(SELECT COUNT(*) FROM orders o WHERE o.uid= u.id) AS order_count
FROM users u;正確示例
-- 重構后性能更佳
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.uid
GROUP BY u.name ;重構后的查詢連接了 "users"和 "orders"，并使用了 "GROUP BY "子句，從而提高了性能。
