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