最新实战案例锦集：《Spring Boot3实战案例合集》持续更新，每天至少更新一篇文章，订阅后将赠送文章最后展示的所有MD文档（学习笔记）。
环境：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 orgADD 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 oWHERE u.id = o.uid;
正确示例
SELECT u.name, o.create_time FROM users uJOIN 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.使用INSELECT * FROM customersWHERE id IN (SELECT cid FROM orders) ;# 2.使用EXISTSSELECT * FROM customers cWHERE 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 usersWHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';
该查询无需使用函数即可对 "create_time" 列进行处理，从而允许使用索引。
2.8 使用 JOIN 代替子查询
JOIN 通常比子查询更有效，尤其是对于大型数据集。
错误示例
SELECT name, (-- 这里通过子查询获取数据SELECT create_timeFROM ordersWHERE uid = users.id) AS create_timeFROM users ;
正确示例
SELECT u.name, o.create_time FROM users uJOIN 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 ordersGROUP BY uid, create_time ORDER BY create_time ;
正确示例
SELECT uid, COUNT(*) FROM ordersGROUP 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");// TODOconn.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() ;// TODOconn.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 oJOIN users u ON u.id = o.uid WHERE u.status = 0 ;
正确示例
SELECT u.name, o.create_time FROM users uJOIN orders o ON u.id = o.uid WHERE u.status = 0 ;
该查询在索引列上连接了 users 和 orders，从而提高了性能。
2.15 优化子查询
子查询通常可以用连接或其他更有效的查询结构来代替。
尽可能使用连接而不是子查询。
使用通用表表达式（CTE）进行复杂查询，以提高可读性，有时还能提高性能。
错误示例
SELECT o.*FROM orders oWHERE o.amount > (SELECT AVG(o2.amount)FROM orders o2WHERE o2.customer_id = o.customer_id);
正确示例
-- 计算每个客户的平均订单金额WITH customer_avg_orders AS (SELECTcustomer_id,AVG(amount) AS avg_amountFROMordersGROUP BYcustomer_id)-- 找出订单金额大于其客户平均订单金额的订单SELECT o.*FROM orders oJOIN customer_avg_orders cao ON o.customer_id = cao.customer_idWHERE o.amount > cao.avg_amount;
注：MySQL 从版本 8.0 开始支持 WITH子句。
2.16 优化聚合查询
在执行聚合查询时，请使用有效的技术来尽量减少计算负荷。
确保 "GROUP BY"子句中使用的列已创建索引。
考虑使用汇总表来处理经常汇总的数据。
错误示例
SELECTcustomer_id,SUM(amount) AS total_amount,COUNT(*) AS order_countFROMordersGROUP BYcustomer_id;
正确示例
-- 创建索引CREATE INDEX idx_customer_id ON orders (customer_id);-- 优化后的聚合查询SELECTcustomer_id,SUM(amount) AS total_amount,COUNT(*) AS order_countFROMordersGROUP BYcustomer_id;
该查询按 "customer_id" 列分组，为获得最佳性能，应为该列建立索引。
2.17 使用摘要列
摘要列存储预先计算的聚合值，从而减少了在查询执行过程中进行昂贵计算的需要。
错误示例
SELECT user_id, SUM(amount) AS total_amountFROM ordersGROUP 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 使用物化视图
物化视图可缓存复杂查询的结果，从而提高重读取操作的性能。
错误示例
SELECTuid,COUNT(*) AS order_count,SUM(amount) AS total_amountFROM ordersGROUP BY uid ;
正确示例
CREATE MATERIALIZED VIEW user_order_summary ASSELECTuid,COUNT(*) AS order_count,SUM(amount) AS total_amountFROM ordersGROUP 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_countFROM users u;
正确示例
-- 重构后性能更佳SELECT u.name, COUNT(o.id) AS order_countFROM users uLEFT JOIN orders o ON u.id = o.uidGROUP BY u.name ;
重构后的查询连接了 "users"和 "orders"，并使用了 "GROUP BY "子句，从而提高了性能。
以上是本篇文章的全部内容，如对你有帮助帮忙点赞+转发+收藏
推荐文章
技巧，非常实用！Spring Boot接口参数这样获取真优雅
Spring Boot 不同HTTP客户端 同步&异步请求对比
Jackson才是王！SpringBoot优雅的控制JSON数据
优雅！SpringBoot通过函数式编程模型声明Restful API接口
强大！必须掌握！Spring Security这些技巧你知道吗？
基于SpringBoot通过3种方式轻松搞定敏感字段加密处理
