数据库优化实战：25 个 SQL 性能调优技巧，查询速度提升十倍

2025-08-16 12:34:18 栏目：宝塔面板 89 阅读

你是否遇到过这样的情况：写好的 SQL 语句，在测试环境运行得好好的，一到生产环境就 “卡成 PPT”？明明只查几条数据，却要等上十几秒，用户投诉电话快被打爆，老板的脸色比锅底还黑……

别慌！今天这篇文章，我把压箱底的 25 个 SQL 性能调优技巧全盘托出，每个技巧都附带真实业务场景的代码示例。哪怕你是刚入行的小白，照着做也能让查询速度瞬间起飞，看完记得转发给团队里总被 “慢查询” 折磨的同事！

一、索引优化：让查询 “快如闪电” 的核心

1. 给过滤条件加索引，跳过全表扫描

没加索引时，查询用户订单列表要扫描全表，100 万条数据能卡到你怀疑人生：

-- 慢查询：无索引，全表扫描


SELECT * FROM orders WHERE user_id = 12345 AND create_time > '2025-01-01';

优化技巧：给过滤字段建联合索引，顺序遵循 “等值在前，范围在后”：

-- 建索引


CREATE INDEX idx_user_create ON orders(user_id, create_time);


-- 优化后查询（瞬间返回结果）


SELECT * FROM orders WHERE user_id = 12345 AND create_time > '2025-01-01';

2. 避免索引失效：别在索引列上做 “小动作”

90% 的新手都会踩这个坑！在索引列上用函数或运算，直接让索引 “罢工”：

-- 索引失效：在索引列create_time上用函数


SELECT * FROM orders WHERE DATE(create_time) = '2025-01-01';

优化技巧：把函数逻辑 “挪” 到等号右边：

-- 索引生效：条件改写


SELECT * FROM orders WHERE create_time >= '2025-01-01 00:00:00'

AND create_time < '2025-01-02 00:00:00';

3. 用覆盖索引，避免 “回表查询”

如果只查几个字段，却用SELECT *，会导致数据库先查索引，再回表取数据，多走一步弯路：

-- 低效：需要回表取数据


SELECT id, user_id, amount FROM orders WHERE user_id = 12345;

优化技巧：建 “包含查询字段” 的覆盖索引，直接从索引拿数据：

-- 建覆盖索引（包含查询的所有字段）


CREATE INDEX idx_cover_user ON orders(user_id, id, amount);


-- 优化后：索引直接返回结果，无需回表


SELECT id, user_id, amount FROM orders WHERE user_id = 12345;

二、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);

5. 小表驱动大表，JOIN 顺序影响性能

新手写 JOIN 时从不考虑表顺序，导致数据库做无用功：

-- 低效：大表在前，小表在后


SELECT * FROM orders o JOIN users u ON o.user_id = u.id

WHERE u.register_time > '2025-01-01';

优化技巧：让小表当 “驱动表”（放在前面），减少循环次数：

-- 高效：小表users在前，大表orders在后


SELECT * FROM users u JOIN orders o ON u.id = o.user_id

WHERE u.register_time > '2025-01-01';

6. 分页查询别用 OFFSET，越往后越慢

当分页到 1000 页后，LIMIT 100000, 10会扫描 10 万行再丢弃，巨慢！

-- 低效：OFFSET越大，速度越慢


SELECT * FROM articles ORDER BY create_time DESC LIMIT 100000, 10;

优化技巧：用 “延迟关联”+ 索引定位，直接跳到目标位置：

-- 高效：先查主键，再关联取数据


SELECT a.* FROM articles a


JOIN (SELECT id FROM articles ORDER BY create_time DESC LIMIT 100000, 10) b


ON a.id = b.id;

三、高级优化：从 “能用” 到 “好用”

7. 批量插入代替循环单条插入

开发时图方便写循环插入，数据库频繁提交事务，性能差到哭：

-- 低效：单条插入，1000条要执行1000次


INSERT INTO logs (content) VALUES ('操作1');


INSERT INTO logs (content) VALUES ('操作2');


...

优化技巧：一次插入多条，减少 IO 次数：

-- 高效：批量插入，1次搞定


INSERT INTO logs (content) VALUES

('操作1'), ('操作2'), ..., ('操作1000');

8. 用 EXPLAIN 分析 SQL，定位性能瓶颈

写完 SQL 别直接上线！用EXPLAIN看执行计划，type字段出现ALL就是全表扫描，必须优化：

-- 查看执行计划


EXPLAIN SELECT * FROM orders WHERE user_id = 12345;

关键指标：

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;

优化技巧：把计算移到等号右边：

-- 高效：索引生效


SELECT * FROM products WHERE price < 100 / 0.8;

10. 大表拆分：水平分表 + 垂直分表

当单表数据超过 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();

优化技巧：使用数据库连接池管理连接，复用连接资源：

// 初始化连接池（以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(); // 实际是归还到连接池，并非真正关闭

12. 避免使用 SELECT ，只查询需要的字段

使用SELECT *会查询所有字段，包括不需要的字段，增加数据传输量和内存消耗：

-- 低效：查询所有字段，包括无用字段


SELECT * FROM users WHERE department_id = 5;

优化技巧：明确指定需要查询的字段：

-- 高效：只查询必要字段


SELECT id, name, email FROM users WHERE department_id = 5;

13. 使用 EXISTS 代替 IN，处理子查询更高效

当子查询结果集较大时，IN的性能较差，EXISTS更适合：

-- 低效：子查询结果集大时，IN性能差


SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 1);

优化技巧：用EXISTS代替IN：

-- 高效：一旦找到匹配项就停止搜索


SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 1);

14. 控制事务范围，避免长事务

长事务会占用数据库资源，可能导致锁竞争和性能问题：

-- 低效：事务范围过大，包含无关操作


BEGIN TRANSACTION;


-- 执行SQL操作1

-- 执行一些耗时的非数据库操作（如调用外部接口）


-- 执行SQL操作2

COMMIT;

优化技巧：缩小事务范围，只包含必要的数据库操作：

-- 高效：事务仅包含数据库操作


BEGIN TRANSACTION;


-- 执行SQL操作1

-- 执行SQL操作2

COMMIT;


-- 执行耗时的非数据库操作（在事务外）

15. 为常用查询创建视图，简化复杂查询

对于频繁使用的复杂查询，创建视图可以提高查询效率和代码复用性：

-- 创建视图


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;

16. 定期清理无用数据，优化表空间

长期不清理的无用数据会占用大量表空间，影响查询性能：

-- 清理3个月前的日志数据


DELETE FROM logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH);


-- 优化表空间（针对InnoDB引擎）


OPTIMIZE TABLE logs;

17. 使用恰当的数据库引擎，提升性能

不同的数据库引擎有不同的特点，根据业务场景选择：

InnoDB：支持事务、行级锁，适合有事务需求的业务，如订单系统。
MyISAM：不支持事务，支持全文索引，适合读多写少的场景，如博客系统。

-- 创建表时指定引擎


CREATE TABLE articles (


   id INT PRIMARY KEY AUTO_INCREMENT,


   title VARCHAR(255),


   content TEXT

) ENGINE=MyISAM;

18. 合理设置数据库参数，优化配置

根据服务器配置和业务需求，调整数据库参数可以提升性能，以 MySQL 为例：

-- 在my.cnf或my.ini中配置


innodb_buffer_pool_size = 4G  # 设置InnoDB缓冲池大小，一般为服务器内存的50%-70%


query_cache_size = 64M  # 设置查询缓存大小，适合读多写少的场景


max_connections = 1000  # 最大连接数，根据并发量设置

19. 避免在循环中执行 SQL，减少交互次数

在循环中执行 SQL 会增加与数据库的交互次数，降低性能：

-- 低效：循环中执行SQL

for (User user : userList) {


   String sql = "INSERT INTO users (name) VALUES ('" + user.getName() + "')";


   // 执行SQL

}

优化技巧：使用批量操作或拼接 SQL 语句（注意 SQL 注入问题）：

-- 高效：批量插入


INSERT INTO users (name) VALUES




   (#{user.name})

20. 使用数据库缓存，减少重复查询

对于不经常变化的数据，使用数据库缓存可以减少数据库访问次数：

-- 开启查询缓存（MySQL 8.0已移除查询缓存，可使用应用级缓存如Redis）


-- 在MySQL配置文件中设置


query_cache_type = ON

-- 执行查询后，结果会被缓存


SELECT * FROM categories;

21. 避免使用 NULL 作为查询条件，影响索引使用

NULL值可能导致索引失效，尽量使用有意义的默认值：

-- 低效：使用IS NULL，可能导致索引失效


SELECT * FROM products WHERE discount IS NULL;

优化技巧：设置默认值，如用 0 表示无折扣：

-- 高效：使用默认值，可利用索引


SELECT * FROM products WHERE discount = 0;

22. 对大文本字段进行压缩存储，节省空间

对于大文本字段（如 TEXT 类型），压缩后存储可以减少存储空间和 IO 操作：

-- 插入时压缩


INSERT INTO articles (title, content) VALUES ('标题', COMPRESS('大量的文本内容...'));


-- 查询时解压


SELECT title, UNCOMPRESS(content) AS content FROM articles WHERE id = 1;

23. 合理使用分区表，提高大表查询效率

对于数据量大的表，使用分区表可以将数据分散到多个分区，提高查询效率：

-- 创建按时间分区的订单表


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'))


);

24. 避免使用存储过程和触发器，减少数据库压力

存储过程和触发器逻辑复杂时，会增加数据库负担，可移至应用层处理：

-- 不推荐：复杂的存储过程


CREATE PROCEDURE complex_procedure()


BEGIN

   -- 大量复杂逻辑


END;

优化技巧：在应用层实现相应逻辑：

// 应用层处理逻辑，减轻数据库压力

public void handleComplexLogic() {


   // 实现原存储过程中的逻辑

}

25. 定期分析表，更新统计信息

数据库优化器需要准确的统计信息来生成最优执行计划，定期分析表可以更新统计信息：

-- 分析表，更新统计信息（MySQL）


ANALYZE TABLE orders;


-- PostgreSQL中


ANALYZE orders;

为什么这些技巧能让查询速度提升 10 倍？

数据库性能瓶颈 90% 出在 “不必要的扫描” 和 “低效的索引使用” 上。上面的技巧看似简单，却直击痛点：

索引优化减少 90% 的扫描行数
SQL 写法优化避免数据库做无用功
批量操作降低 IO 次数，减少事务开销

最后提醒：优化不是一次性工作，上线后要持续监控慢查询日志（开启slow_query_log），定期用pt-query-digest分析 TOP10 慢 SQL，让数据库永远 “飞” 起来！

本文地址：https://www.yitenyun.com/351.html

上一篇：数据库事务与锁机制：十个核心场景 + SQL 实战案···

下一篇：千万别忽视！TIME_WAIT 堆积背后的秘密和应对技···

搜索文章

所有分类

宝塔面板（254） Windows （2） Linux （8）香港服务器（0）美国服务器（0）香港站群服务器（0）美国站群服务器（0）

Tags