数据库优化实战：25 个 SQL 性能调优技巧，查询速度提升十倍
你是否遇到过这样的情况：写好的 SQL 语句，在测试环境运行得好好的，一到生产环境就 “卡成 PPT”？明明只查几条数据，却要等上十几秒，用户投诉电话快被打爆，老板的脸色比锅底还黑……
别慌！今天这篇文章，我把压箱底的 25 个 SQL 性能调优技巧全盘托出，每个技巧都附带真实业务场景的代码示例。哪怕你是刚入行的小白，照着做也能让查询速度瞬间起飞，看完记得转发给团队里总被 “慢查询” 折磨的同事！
一、索引优化：让查询 “快如闪电” 的核心
1. 给过滤条件加索引，跳过全表扫描没加索引时，查询用户订单列表要扫描全表，100 万条数据能卡到你怀疑人生：
复制
-- 慢查询：无索引，全表扫描
SELECT * FROM orders WHERE user_id = 12345 AND create_time > 2025-01-01;1.2.3.4.
优化技巧：给过滤字段建联合索引，顺序遵循 “等值在前，范围在后”：
复制
-- 建索引
CREATE INDEX idx_user_create ON orders(user_id, create_time);
-- 优化后查询（瞬间返回结果）
SELECT * FROM orders WHERE user_id = 12345 AND create_time > 2025-01-01;1.2.3.4.5.6.7.8.9.10.
90% 的新手都会踩这个坑！在索引列上用函数或运算，直接让索引 “罢工”：
复制
-- 索引失效：在索引列create_time上用函数
SELECT * FROM orders WHERE DATE(create_time) = 2025-01-01;1.2.3.4.
优化技巧：把函数逻辑 “挪” 到等号右边：
复制
-- 索引生效：条件改写
SELECT * FROM orders WHERE create_time >= 2025-01-01 00:00:00
AND create_time < 2025-01-02 00:00:00;1.2.3.4.5.6.
如果只查几个字段，却用SELECT *，会导致数据库先查索引，再回表取数据，多走一步弯路：
复制
-- 低效：需要回表取数据
SELECT id, user_id, amount FROM orders WHERE user_id = 12345;1.2.3.4.
优化技巧：建 “包含查询字段” 的覆盖索引，直接从索引拿数据：
复制
-- 建覆盖索引（包含查询的所有字段）
CREATE INDEX idx_cover_user ON orders(user_id, id, amount);
-- 优化后：索引直接返回结果，无需回表
SELECT id, user_id, amount FROM orders WHERE user_id = 12345;1.2.3.4.5.6.7.8.9.10.
二、SQL 写法优化：细节决定速度
4. 用 IN 代替 OR，批量查询更高效当条件字段有索引时，OR会导致索引失效，换成IN性能提升 10 倍：
复制
-- 低效：OR导致全表扫描
SELECT * FROM users WHERE id = 100 OR id = 200 OR id = 300;
-- 高效：IN走索引
SELECT * FROM users WHERE id IN (100, 200, 300);1.2.3.4.5.6.7.8.9.10.
新手写 JOIN 时从不考虑表顺序，导致数据库做无用功：
复制
-- 低效：大表在前，小表在后
SELECT * FROM orders o JOIN users u ON o.user_id = u.id
WHERE u.register_time > 2025-01-01;1.2.3.4.5.6.
优化技巧：让小表当 “驱动表”（放在前面），减少循环次数：
复制
-- 高效：小表users在前，大表orders在后
SELECT * FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.register_time > 2025-01-01;1.2.3.4.5.6.
当分页到 1000 页后，LIMIT 100000, 10会扫描 10 万行再丢弃，巨慢！
复制
-- 低效：OFFSET越大，速度越慢
SELECT * FROM articles ORDER BY create_time DESC LIMIT 100000, 10;1.2.3.4.
优化技巧：用 “延迟关联”+ 索引定位，直接跳到目标位置：
复制
-- 高效：先查主键，再关联取数据
SELECT a.* FROM articles a
JOIN (SELECT id FROM articles ORDER BY create_time DESC LIMIT 100000, 10) b
ON a.id = b.id;1.2.3.4.5.6.7.8.9.10.
三、高级优化：从 “能用” 到 “好用”
7. 批量插入代替循环单条插入开发时图方便写循环插入，数据库频繁提交事务，性能差到哭：
复制
-- 低效：单条插入，1000条要执行1000次
INSERT INTO logs (content) VALUES (操作1);
INSERT INTO logs (content) VALUES (操作2);
...1.2.3.4.5.6.7.8.9.10.
优化技巧：一次插入多条，减少 IO 次数：
复制
-- 高效：批量插入，1次搞定
INSERT INTO logs (content) VALUES
(操作1), (操作2), ..., (操作1000);1.2.3.4.5.6.
写完 SQL 别直接上线！用EXPLAIN看执行计划，type字段出现ALL就是全表扫描，必须优化：
复制
-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE user_id = 12345;1.2.3.4.
关键指标：
type：const> eq_ref> ref> range> ALL（出现ALL立即优化）rows：预估扫描行数，越小越好Extra：出现Using filesort（文件排序）、Using temporary（临时表）要警惕9. 避免在 WHERE 子句中使用函数或计算对字段做计算会让索引失效，比如price*0.8，数据库无法利用price索引：
复制
-- 低效：字段参与计算，索引失效
SELECT * FROM products WHERE price * 0.8 < 100;1.2.3.4.
优化技巧：把计算移到等号右边：
复制
-- 高效：索引生效
SELECT * FROM products WHERE price < 100 / 0.8;1.2.3.4.
当单表数据超过 1000 万行，查询必然变慢，分表是唯一出路：
水平分表：按时间拆分订单表（orders_202501、orders_202502）垂直分表：把大字段（如content）从articles表拆分到articles_content表11. 合理使用数据库连接池，避免频繁创建连接频繁创建和关闭数据库连接会消耗大量资源，尤其是在高并发场景下：
复制
-- 低效：每次操作都创建新连接
Connection conn1 = DriverManager.getConnection(url, user, password);
// 执行操作1
conn1.close();
Connection conn2 = DriverManager.getConnection(url, user, password);
// 执行操作2
conn2.close();1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.
优化技巧：使用数据库连接池管理连接，复用连接资源：
复制
// 初始化连接池（以HikariCP为例）
HikariConfig config = new HikariConfig();
config.setJdbcUrl(url);
config.setUsername(user);
config.setPassword(password);
config.setMaximumPoolSize(10); // 设置最大连接数
HikariDataSource dataSource = new HikariDataSource(config);
// 高效：从连接池获取连接，用完归还
Connection conn = dataSource.getConnection();
// 执行操作
conn.close(); // 实际是归还到连接池，并非真正关闭1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.
使用SELECT *会查询所有字段，包括不需要的字段，增加数据传输量和内存消耗：
复制
-- 低效：查询所有字段，包括无用字段
SELECT * FROM users WHERE department_id = 5;1.2.3.4.
优化技巧：明确指定需要查询的字段：
复制
-- 高效：只查询必要字段
SELECT id, name, email FROM users WHERE department_id = 5;1.2.3.4.
当子查询结果集较大时，IN的性能较差，EXISTS更适合：
复制
-- 低效：子查询结果集大时，IN性能差
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 1);1.2.3.4.
优化技巧：用EXISTS代替IN：
复制
-- 高效：一旦找到匹配项就停止搜索
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 1);1.2.3.4.
长事务会占用数据库资源，可能导致锁竞争和性能问题：
复制
-- 低效：事务范围过大，包含无关操作
BEGIN TRANSACTION;
-- 执行SQL操作1
-- 执行一些耗时的非数据库操作（如调用外部接口）
-- 执行SQL操作2
COMMIT;1.2.3.4.5.6.7.8.9.10.11.12.13.14.
优化技巧：缩小事务范围，只包含必要的数据库操作：
复制
-- 高效：事务仅包含数据库操作
BEGIN TRANSACTION;
-- 执行SQL操作1
-- 执行SQL操作2
COMMIT;
-- 执行耗时的非数据库操作（在事务外）1.2.3.4.5.6.7.8.9.10.11.12.13.14.
对于频繁使用的复杂查询，创建视图可以提高查询效率和代码复用性：
复制
-- 创建视图
CREATE VIEW v_user_order_summary AS
SELECT u.id AS user_id, u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_amount
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- 高效：查询视图，简化操作
SELECT * FROM v_user_order_summary WHERE user_id = 123;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.
长期不清理的无用数据会占用大量表空间，影响查询性能：
复制
-- 清理3个月前的日志数据
DELETE FROM logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH);
-- 优化表空间（针对InnoDB引擎）
OPTIMIZE TABLE logs;1.2.3.4.5.6.7.8.9.10.
不同的数据库引擎有不同的特点，根据业务场景选择：
InnoDB：支持事务、行级锁，适合有事务需求的业务，如订单系统。MyISAM：不支持事务，支持全文索引，适合读多写少的场景，如博客系统。复制
-- 创建表时指定引擎
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255),
content TEXT
) ENGINE=MyISAM;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.
根据服务器配置和业务需求，调整数据库参数可以提升性能，以 MySQL 为例：
复制
-- 在my.cnf或my.ini中配置
innodb_buffer_pool_size = 4G # 设置InnoDB缓冲池大小，一般为服务器内存的50%-70%
query_cache_size = 64M # 设置查询缓存大小，适合读多写少的场景
max_connections = 1000 # 最大连接数，根据并发量设置1.2.3.4.5.6.7.8.9.10.
在循环中执行 SQL 会增加与数据库的交互次数，降低性能：
复制
-- 低效：循环中执行SQL
for (User user : userList) {
String sql = "INSERT INTO users (name) VALUES (" + user.getName() + ")";
// 执行SQL
}1.2.3.4.5.6.7.8.9.10.11.
优化技巧：使用批量操作或拼接 SQL 语句（注意 SQL 注入问题）：
复制
-- 高效：批量插入
INSERT INTO users (name) VALUES
<foreach collection="userList" item="user" separator=",">
(#{user.name})
</foreach>1.2.3.4.5.6.7.8.9.10.11.12.
对于不经常变化的数据，使用数据库缓存可以减少数据库访问次数：
复制
-- 开启查询缓存（MySQL 8.0已移除查询缓存，可使用应用级缓存如Redis）
-- 在MySQL配置文件中设置
query_cache_type = ON
-- 执行查询后，结果会被缓存
SELECT * FROM categories;1.2.3.4.5.6.7.8.9.10.11.12.
NULL值可能导致索引失效，尽量使用有意义的默认值：
复制
-- 低效：使用IS NULL，可能导致索引失效
SELECT * FROM products WHERE discount IS NULL;1.2.3.4.
优化技巧：设置默认值，如用 0 表示无折扣：
复制
-- 高效：使用默认值，可利用索引
SELECT * FROM products WHERE discount = 0;1.2.3.4.
对于大文本字段（如 TEXT 类型），压缩后存储可以减少存储空间和 IO 操作：
复制
-- 插入时压缩
INSERT INTO articles (title, content) VALUES (标题, COMPRESS(大量的文本内容...));
-- 查询时解压
SELECT title, UNCOMPRESS(content) AS content FROM articles WHERE id = 1;1.2.3.4.5.6.7.8.9.10.
对于数据量大的表，使用分区表可以将数据分散到多个分区，提高查询效率：
复制
-- 创建按时间分区的订单表
CREATE TABLE orders (
id INT PRIMARY KEY,
order_no VARCHAR(50),
create_time DATETIME
) PARTITION BY RANGE (TO_DAYS(create_time)) (
PARTITION p202501 VALUES LESS THAN (TO_DAYS(2025-02-01)),
PARTITION p202502 VALUES LESS THAN (TO_DAYS(2025-03-01)),
PARTITION p202503 VALUES LESS THAN (TO_DAYS(2025-04-01))
);1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.
存储过程和触发器逻辑复杂时，会增加数据库负担，可移至应用层处理：
复制
-- 不推荐：复杂的存储过程
CREATE PROCEDURE complex_procedure()
BEGIN
-- 大量复杂逻辑
END;1.2.3.4.5.6.7.8.9.10.11.12.
优化技巧：在应用层实现相应逻辑：
复制
// 应用层处理逻辑，减轻数据库压力
public void handleComplexLogic() {
// 实现原存储过程中的逻辑
}1.2.3.4.5.6.7.8.
数据库优化器需要准确的统计信息来生成最优执行计划，定期分析表可以更新统计信息：
复制
-- 分析表，更新统计信息（MySQL）
ANALYZE TABLE orders;
-- PostgreSQL中
ANALYZE orders;1.2.3.4.5.6.7.8.9.10.
为什么这些技巧能让查询速度提升 10 倍？
数据库性能瓶颈 90% 出在 “不必要的扫描” 和 “低效的索引使用” 上。上面的技巧看似简单，却直击痛点：
索引优化减少 90% 的扫描行数SQL 写法优化避免数据库做无用功批量操作降低 IO 次数，减少事务开销最后提醒：优化不是一次性工作，上线后要持续监控慢查询日志（开启slow_query_log），定期用pt-query-digest分析 TOP10 慢 SQL，让数据库永远 “飞” 起来！
阅读剩余
THE END
