提升性能：Java工程师必备的20条SQL最佳实践-51CTO.COM

环境：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 "子句，从而提高了性能。