环境：MySQL

1. 简介

在项目开发中，提升条SQL优化的工程最佳实践是每位Java工程师必须掌握的关键技能。高效的师必实践数据库交互可以显著提升应用程序的性能，带来更快的最佳响应时间和更好的用户体验。

本篇文章将深入探讨了SQL查询优化的提升条最佳实践，特别针对Java工程师量身定制。工程从理解索引的师必实践重要性到精通 join 操作以及利用连接池，在本文中涵盖了编写高效且高性能SQL查询所需的最佳所有基本技术及最佳实践。

2. 实战案例

2.1 使用索引

索引可以让数据库快速定位和访问数据，提升条从而大大提高查询性能。工程

在用于WHERE、师必实践JOIN、最佳ORDER BY和GROUP BY子句的提升条列上创建索引。使用覆盖索引来包含查询所需的工程所有列。 错误示例 复制SELECT * FROM users WHERE name = pack1. 正确示例 复制CREATE INDEX idx_name ON users (name); SELECT name,师必实践 email FROM users WHERE name = pack;1.2.

在users表的name字段创建索引，以加快查询效率。

基于函数索引

当频繁地根据函数或表达式的结果进行搜索或排序时，基于函数的索引可以显著提高查询性能。

为WHERE、香港云服务器ORDER BY或JOIN条件中常用的表达式创建基于函数的索引。使用基于函数的索引来优化涉及不区分大小写的搜索或日期/时间操作的查询。 错误示例 复制SELECT * FROM org WHERE UPPER(pos_code) = abc1. 正确示例 复制ALTER TABLE org ADD COLUMN code_upper VARCHAR(100) AS (UPPER(pos_code)) STORED ; CREATE INDEX idx_code ON org (code_upper) ;1.2.3.

注：MySQL 从版本 8.0 开始支持基于函数的索引（也称为虚拟列索引或表达式索引）。在 MySQL 8.0 之前，MySQL 并不直接支持基于函数的索引。

还有一点需要注意：基于函数的索引可以显著提高查询性能，但同时也会增加存储需求并降低数据修改操作的速度（上面将添加一列，并存储了对应的数据）。修改原始列也会同步修改对应的虚拟列。

2.2 避免使用 SELECT *

使用 SELECT * 会检索表中的所有列，这可能会降低效率并导致不必要的数据传输。

在 SELECT 语句中明确你所需要的列。错误示例 复制SELECT * FROM users;1. 正确示例 复制SELECT name, age FROM users;1.

该查询只获取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;1.2. 正确示例 复制SELECT u.name, o.create_time FROM users u JOIN orders o ON u.id = o.uid;1.2.

该查询使用 INNER JOIN 来合并来自用户表和订单表的数据。

2.4 使用WHERE子句过滤数据

在查询中尽早过滤数据有助于减少处理的数据量。

错误示例 复制SELECT name, age FROM users ;1. 正确示例 复制SELECT name, age FROM users WHERE status = 0 ;1.

只查询需要的数据，这里查询用户状态正常的数据，以减少处理的数据量。

2.5 限制返回的行数

如果不需要所有记录，可使用 LIMIT 子句限制返回的记录数。

错误示例 复制SELECT name, age FROM users WHERE status = 0 ;1. 正确示例 复制SELECT name, age FROM users WHERE status = 0 LIMIT 10 ;1.

该查询会检索前 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);1.2.3.4.5.6.

性能对比

小结果集：如果 orders 表中的免费源码下载 cid 列有索引，并且结果集很小，IN 可能会更快。大结果集：如果 orders 表中的 cid列没有索引，或者结果集很大，EXISTS 通常会更快，因为它会在找到第一个匹配项后立即停止。 

2.7 避免在 WHERE 子句中使用函数

在 "WHERE" 子句中使用函数可能会使得索引失效，从而导致查询速度变慢。

错误示例 复制SELECT name, age FROM users WHERE YEAR(create_time) = 2024 ;1. 正确示例 复制SELECT name, age FROM users WHERE create_time >= 2024-01-01 AND create_time < 2025-01-01;1.2.

该查询无需使用函数即可对 "create_time" 列进行处理，从而允许使用索引。

2.8 使用 JOIN 代替子查询

JOIN 通常比子查询更有效，尤其是对于大型数据集。

错误示例 复制SELECT name, ( -- 这里通过子查询获取数据 SELECT create_time FROM orders WHERE uid = users.id ) AS create_time FROM users ;1.2.3.4.5.6.7. 正确示例 复制SELECT u.name, o.create_time FROM users u JOIN orders o ON u.id = o.uid ;1.2.

这里通过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 ;1.2. 正确示例 复制SELECT uid, COUNT(*) FROM orders GROUP BY uid ORDER BY uid;1.2.

查询按索引列分组和排序，提高了性能。

2.10 使用适当的数据类型

为列选择正确的数据类型会极大地影响性能和存储效率。

为列使用适当的数据类型。除非必要，避免使用 `TEXT` 或 `BLOB`。

错误示例

复制CREATE TABLE users ( id bigint auto_increment PRIMARY KEY, name TEXT, create_time TIMESTAMP );1.2.3.4.5.

正确示例

复制CREATE TABLE users ( id bigint auto_increment PRIMARY KEY, name VARCHAR 100, create_time TIMESTAMP );1.2.3.4.5.

使用适当的数据类型，提高了性能和存储效率。

2.11 分析执行计划

使用 "EXPLAIN" 分析查询执行计划并找出性能问题。

复制EXPLAIN SELECT name, sex, age FROM big_table t WHERE T.name = Pack1.

根据执行结果，分析慢SQL的原因，比如：是否走索引，索引的类型等。

2.12 使用连接池

使用连接池可以减少建立数据库连接的开销，提高性能。

使用 HikariCP 或 C3P0 等连接池库。根据应用程序的需求和数据库的功能配置池的大小。 错误示例 复制Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/test", "root", "xxxooo" ); // TODO conn.close();1.2.3.4.5. 正确示例 复制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() ;1.2.3.4.5.6.7.8.9.

在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() ;1.2.3.4.5.6.7. 正确示例 复制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() ;1.2.3.4.5.6.7.8.9.10.

使用批处理功能来高效插入多条数据。

2.14 优化JOIN

适当优化连接可显著影响查询性能，尤其是大数据集。

确保连接条件中使用的列已建立索引。连接多个表时，从最小的表开始。 错误示例 复制SELECT u.name, o.create_time FROM orders o JOIN users u ON u.id = o.uid WHERE u.status = 0 ;1.2. 正确示例 复制SELECT u.name, o.create_time FROM users u JOIN orders o ON u.id = o.uid WHERE u.status = 0 ;1.2.

该查询在索引列上连接了 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 );1.2.3.4.5.6.7. 正确示例 复制-- 计算每个客户的平均订单金额 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;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.

注：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;1.2.3.4.5.6.7.8. 正确示例 复制-- 创建索引 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;1.2.3.4.5.6.7.8.9.10.11.

该查询按 "customer_id" 列分组，为获得最佳性能，应为该列建立索引。

2.17 使用摘要列

摘要列存储预先计算的聚合值，从而减少了在查询执行过程中进行昂贵计算的需要。

错误示例 复制SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY uid ;1.2.3. 正确示例 复制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);1.2.

这种方法增加了一个摘要列，用于存储每个用户的订单总额。

2.18 使用物化视图

物化视图可缓存复杂查询的结果，从而提高重读取操作的性能。

错误示例 复制SELECT uid, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY uid ;1.2.3.4.5.6. 正确示例 复制CREATE MATERIALIZED VIEW user_order_summary AS SELECT uid, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM orders GROUP BY uid;1.2.3.4.5.6.7.

创建一个物化视图，用于存储预先计算的用户订单信息摘要。

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;1.2.3.4. 正确示例 复制-- 重构后性能更佳 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 ;1.2.3.4.5.

重构后的查询连接了 "users"和 "orders"，并使用了 "GROUP BY "子句，从而提高了性能。

(责任编辑：IT科技类资讯)