一、索引优化：让查询 “快如闪电” 的核心
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
<foreach collection="userList" item="user" separator=",">
(#{user.name})
</foreach>
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，让数据库永远 “飞” 起来！
