提升性能：Java工程師必備的20條SQL最佳實(shí)踐

作者：Springboot實(shí)戰(zhàn)案例錦集 2024-10-18 09:14:12

本篇文章將深入探討了SQL查詢優(yōu)化的最佳實(shí)踐，特別針對(duì)Java工程師量身定制。從理解索引的重要性到精通 join 操作以及利用連接池，在本文中涵蓋了編寫高效且高性能SQL查詢所需的所有基本技術(shù)及最佳實(shí)踐。

環(huán)境：MySQL

1. 簡(jiǎn)介

在項(xiàng)目開發(fā)中，SQL優(yōu)化的最佳實(shí)踐是每位Java工程師必須掌握的關(guān)鍵技能。高效的數(shù)據(jù)庫(kù)交互可以顯著提升應(yīng)用程序的性能，帶來(lái)更快的響應(yīng)時(shí)間和更好的用戶體驗(yàn)。

2. 實(shí)戰(zhàn)案例

2.1 使用索引

索引可以讓數(shù)據(jù)庫(kù)快速定位和訪問(wèn)數(shù)據(jù)，從而大大提高查詢性能。

在用于WHERE、JOIN、ORDER BY和GROUP BY子句的列上創(chuàng)建索引。
使用覆蓋索引來(lái)包含查詢所需的所有列。

錯(cuò)誤示例

SELECT * FROM users WHERE name = 'pack'

正確示例

CREATE INDEX idx_name ON users (name);
SELECT name, email FROM users WHERE name = 'pack';

在users表的name字段創(chuàng)建索引，以加快查詢效率。

基于函數(shù)索引

當(dāng)頻繁地根據(jù)函數(shù)或表達(dá)式的結(jié)果進(jìn)行搜索或排序時(shí)，基于函數(shù)的索引可以顯著提高查詢性能。

為WHERE、ORDER BY或JOIN條件中常用的表達(dá)式創(chuàng)建基于函數(shù)的索引。
使用基于函數(shù)的索引來(lái)優(yōu)化涉及不區(qū)分大小寫的搜索或日期/時(shí)間操作的查詢。

錯(cuò)誤示例

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 開始支持基于函數(shù)的索引（也稱為虛擬列索引或表達(dá)式索引）。在 MySQL 8.0 之前，MySQL 并不直接支持基于函數(shù)的索引。

還有一點(diǎn)需要注意：基于函數(shù)的索引可以顯著提高查詢性能，但同時(shí)也會(huì)增加存儲(chǔ)需求并降低數(shù)據(jù)修改操作的速度（上面將添加一列，并存儲(chǔ)了對(duì)應(yīng)的數(shù)據(jù)）。修改原始列也會(huì)同步修改對(duì)應(yīng)的虛擬列。

2.2 避免使用 SELECT *

使用 SELECT * 會(huì)檢索表中的所有列，這可能會(huì)降低效率并導(dǎo)致不必要的數(shù)據(jù)傳輸。

在 SELECT 語(yǔ)句中明確你所需要的列。

錯(cuò)誤示例

SELECT * FROM users;

正確示例

SELECT name, age FROM users;

該查詢只獲取name和age列，從而減少了傳輸?shù)臄?shù)據(jù)量。

2.3 正確使用Join

不正確的連接方式可能導(dǎo)致性能問(wèn)題。為查詢使用正確的join類型。

使用 INNER JOIN 來(lái)匹配兩個(gè)表中的行。
使用 LEFT JOIN 來(lái)包含左表中的所有行以及右表中匹配的行。

錯(cuò)誤示例

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 來(lái)合并來(lái)自用戶表和訂單表的數(shù)據(jù)。

2.4 使用WHERE子句過(guò)濾數(shù)據(jù)

在查詢中盡早過(guò)濾數(shù)據(jù)有助于減少處理的數(shù)據(jù)量。

錯(cuò)誤示例

SELECT name, age FROM users ;

正確示例

SELECT name, age FROM users WHERE status = 0 ;

只查詢需要的數(shù)據(jù)，這里查詢用戶狀態(tài)正常的數(shù)據(jù)，以減少處理的數(shù)據(jù)量。

2.5 限制返回的行數(shù)

如果不需要所有記錄，可使用 LIMIT 子句限制返回的記錄數(shù)。

錯(cuò)誤示例

SELECT name, age FROM users WHERE status = 0 ;

正確示例

SELECT name, age FROM users WHERE status = 0 LIMIT 10 ;

該查詢會(huì)檢索前 10 個(gè)有效狀態(tài)的用戶，從而減少處理和傳輸?shù)臄?shù)據(jù)量。

2.6 使用EXISTS代替IN

使用 EXISTS 可能比使用 IN 更有效率，尤其是對(duì)于大型數(shù)據(jù)集。

這不是絕對(duì)的，請(qǐng)看下面場(chǎng)景：

假設(shè)我們有兩個(gè)表 orders 和 customers，并且我們想要找出那些至少有一個(gè)訂單的所有客戶。

# 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);

性能對(duì)比

小結(jié)果集：如果 orders 表中的 cid 列有索引，并且結(jié)果集很小，IN 可能會(huì)更快。
大結(jié)果集：如果 orders 表中的 cid列沒有索引，或者結(jié)果集很大，EXISTS 通常會(huì)更快，因?yàn)樗鼤?huì)在找到第一個(gè)匹配項(xiàng)后立即停止。

2.7 避免在 WHERE 子句中使用函數(shù)

在 "WHERE" 子句中使用函數(shù)可能會(huì)使得索引失效，從而導(dǎo)致查詢速度變慢。

錯(cuò)誤示例

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';

該查詢無(wú)需使用函數(shù)即可對(duì) "create_time" 列進(jìn)行處理，從而允許使用索引。

2.8 使用 JOIN 代替子查詢

JOIN 通常比子查詢更有效，尤其是對(duì)于大型數(shù)據(jù)集。

錯(cuò)誤示例

SELECT name, (
  -- 這里通過(guò)子查詢獲取數(shù)據(jù)
  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 ;

這里通過(guò)JOIN提供了查詢性能。

2.9 優(yōu)化 GROUP BY 和 ORDER BY 子句

使用 "GROUP BY "和 "ORDER BY "子句可能會(huì)耗費(fèi)大量資源。優(yōu)化它們可提高性能。

在 "GROUP BY "和 "ORDER BY "子句中使用的列上使用索引。
減少這些子句中指定的列數(shù)。

錯(cuò)誤示例

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 使用適當(dāng)?shù)臄?shù)據(jù)類型

為列選擇正確的數(shù)據(jù)類型會(huì)極大地影響性能和存儲(chǔ)效率。

為列使用適當(dāng)?shù)臄?shù)據(jù)類型。
除非必要，避免使用 `TEXT` 或 `BLOB`。

錯(cuò)誤示例

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
);

使用適當(dāng)?shù)臄?shù)據(jù)類型，提高了性能和存儲(chǔ)效率。

2.11 分析執(zhí)行計(jì)劃

使用 "EXPLAIN" 分析查詢執(zhí)行計(jì)劃并找出性能問(wèn)題。

EXPLAIN SELECT name, sex, age FROM big_table t WHERE T.name = 'Pack'

根據(jù)執(zhí)行結(jié)果，分析慢SQL的原因，比如：是否走索引，索引的類型等。

2.12 使用連接池

使用連接池可以減少建立數(shù)據(jù)庫(kù)連接的開銷，提高性能。

使用 HikariCP 或 C3P0 等連接池庫(kù)。
根據(jù)應(yīng)用程序的需求和數(shù)據(jù)庫(kù)的功能配置池的大小。

錯(cuò)誤示例

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環(huán)境中我們只需要在配置文件中進(jìn)行配置，無(wú)需上面這樣自己創(chuàng)建。

2.13 使用批處理

在執(zhí)行多個(gè)插入、更新或刪除操作時(shí)，使用批處理可以大大提高性能。

批量插入/更新，減少數(shù)據(jù)庫(kù)往返次數(shù)。
使用預(yù)編譯語(yǔ)句進(jìn)行批處理操作。

錯(cuò)誤示例

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() ;

使用批處理功能來(lái)高效插入多條數(shù)據(jù)。

2.14 優(yōu)化JOIN

適當(dāng)優(yōu)化連接可顯著影響查詢性能，尤其是大數(shù)據(jù)集。

確保連接條件中使用的列已建立索引。
連接多個(gè)表時(shí)，從最小的表開始。

錯(cuò)誤示例

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 ;

該查詢?cè)谒饕猩线B接了 users 和 orders，從而提高了性能。

2.15 優(yōu)化子查詢

子查詢通常可以用連接或其他更有效的查詢結(jié)構(gòu)來(lái)代替。

盡可能使用連接而不是子查詢。
使用通用表表達(dá)式（CTE）進(jìn)行復(fù)雜查詢，以提高可讀性，有時(shí)還能提高性能。

錯(cuò)誤示例

SELECT o.*
FROM orders o
WHERE o.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
);

正確示例

-- 計(jì)算每個(gè)客戶的平均訂單金額
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 優(yōu)化聚合查詢

在執(zhí)行聚合查詢時(shí)，請(qǐng)使用有效的技術(shù)來(lái)盡量減少計(jì)算負(fù)荷。

確保 "GROUP BY"子句中使用的列已創(chuàng)建索引。
考慮使用匯總表來(lái)處理經(jīng)常匯總的數(shù)據(jù)。

錯(cuò)誤示例

SELECT 
    customer_id, 
    SUM(amount) AS total_amount, 
    COUNT(*) AS order_count
FROM 
    orders
GROUP BY 
    customer_id;

正確示例

-- 創(chuàng)建索引
CREATE INDEX idx_customer_id ON orders (customer_id);
-- 優(yōu)化后的聚合查詢
SELECT 
    customer_id, 
    SUM(amount) AS total_amount, 
    COUNT(*) AS order_count
FROM 
    orders
GROUP BY 
    customer_id;

該查詢按 "customer_id" 列分組，為獲得最佳性能，應(yīng)為該列建立索引。

2.17 使用摘要列

摘要列存儲(chǔ)預(yù)先計(jì)算的聚合值，從而減少了在查詢執(zhí)行過(guò)程中進(jìn)行昂貴計(jì)算的需要。

錯(cuò)誤示例

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);

這種方法增加了一個(gè)摘要列，用于存儲(chǔ)每個(gè)用戶的訂單總額。

2.18 使用物化視圖

物化視圖可緩存復(fù)雜查詢的結(jié)果，從而提高重讀取操作的性能。

錯(cuò)誤示例

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;

創(chuàng)建一個(gè)物化視圖，用于存儲(chǔ)預(yù)先計(jì)算的用戶訂單信息摘要。

2.19 監(jiān)控和調(diào)整數(shù)據(jù)庫(kù)設(shè)置

定期監(jiān)控和調(diào)整數(shù)據(jù)庫(kù)設(shè)置，確保最佳性能。

根據(jù)工作量調(diào)整緩沖池大小和緩存大小等內(nèi)存設(shè)置。
使用 "EXPLAIN"、"ANALYZE "等工具和特定于數(shù)據(jù)庫(kù)的監(jiān)控工具來(lái)識(shí)別和解決性能瓶頸。

2.20 定期審查和重構(gòu) SQL 代碼

定期審查和重構(gòu) SQL 代碼有助于識(shí)別和解決性能問(wèn)題。

定期進(jìn)行代碼審查，確保優(yōu)化 SQL 查詢。
將復(fù)雜的查詢分解成更簡(jiǎn)單、更高效的部分。

錯(cuò)誤示例

-- 原始復(fù)雜查詢 
SELECT u.name,
 (SELECT COUNT(*) FROM orders o WHERE o.uid= u.id) AS order_count
FROM users u;

正確示例

-- 重構(gò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 ;

重構(gòu)后的查詢連接了 "users"和 "orders"，并使用了 "GROUP BY "子句，從而提高了性能。

責(zé)任編輯：武曉燕來(lái)源： Spring全家桶實(shí)戰(zhàn)案例源碼