SQL优化是提升数据库性能的关键手段，通过改进查询语句、优化表结构和合理使用索引等方式，减少资源消耗，提高查询效率。有效的SQL优化可以显著提升应用程序响应速度，降低数据库服务器负载。
一、查询语句优化
1. 避免使用SELECT *
只查询需要的字段，减少数据传输量和IO操作。
不推荐：
SELECT * FROM users;
推荐：
SELECT id, username, email FROM users;
2. 优化WHERE子句
（1）避免在WHERE子句中对字段进行函数或表达式操作
-- 不推荐：索引失效 SELECT * FROM orders WHERE YEAR(create_time) = 2023; -- 推荐：使用索引字段直接比较 SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
（2）避免使用不等于（!=、<>）、NOT IN等操作符
-- 不推荐：可能导致全表扫描 SELECT * FROM products WHERE price != 100; -- 推荐：使用范围查询替代（如果业务允许） SELECT * FROM products WHERE price > 100 OR price < 100;
（3）合理使用LIKE查询
-- 不推荐：通配符开头导致索引失效 SELECT * FROM users WHERE username LIKE '%john'; -- 推荐：通配符放在末尾，可使用索引 SELECT * FROM users WHERE username LIKE 'john%';
3. 优化JOIN操作
（1）尽量使用INNER JOIN，避免 cartes ian product（笛卡尔积）
（2）JOIN时使用索引字段作为连接条件
（3）小表驱动大表，将数据量小的表作为驱动表
-- 推荐：小表orders驱动大表users SELECT o.order_id, u.username FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE o.status = 1;
4. 优化排序和分组
（1）为ORDER BY和GROUP BY的字段创建索引
-- 为排序字段创建索引 CREATE INDEX idx_create_time ON logs(create_time); -- 优化后的查询 SELECT * FROM logs WHERE level = 'ERROR' ORDER BY create_time DESC;
（2）避免在大数据集上使用ORDER BY RAND()
-- 不推荐：效率低 SELECT * FROM products ORDER BY RAND() LIMIT 10; -- 推荐：使用随机数结合索引查询 SELECT * FROM products WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM products))) LIMIT 10;
5. 合理使用子查询
（1）复杂子查询可改为JOIN操作，提高效率
-- 子查询方式 SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 1); -- 推荐：JOIN方式 SELECT u.* FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.status = 1 GROUP BY u.id;
（2）避免多层嵌套子查询
二、索引优化
1. 创建合适的索引
（1）为WHERE、JOIN、ORDER BY、GROUP BY涉及的字段创建索引
（2）对区分度高的字段创建索引（如身份证号、邮箱）
（3）合理设计复合索引，遵循最左前缀原则
-- 复合索引设计：查询频繁的字段放前面 CREATE INDEX idx_name_price ON products(name, price); -- 可使用索引的查询 SELECT * FROM products WHERE name = '手机'; SELECT * FROM products WHERE name = '手机' AND price > 1000; -- 无法使用索引的查询（不满足最左前缀） SELECT * FROM products WHERE price > 1000;
2. 避免过度索引
（1）删除未使用或重复的索引
（2）频繁更新的表应减少索引数量
（3）小表通常不需要索引
3. 使用前缀索引
对于长字符串字段，可只对前N个字符创建索引，减少索引大小。
-- 对username字段的前10个字符创建索引 CREATE INDEX idx_username_prefix ON users(username(10));
三、表结构优化
1. 选择合适的数据类型
（1）使用最小可行的数据类型
-- 不推荐：使用过大的类型 age INT(10) -- 推荐：使用合适的类型 age TINYINT UNSIGNED -- 适用于0-255的年龄范围
（2）优先使用整数类型存储字符串（如使用枚举或字典表）
-- 不推荐：存储字符串状态 status VARCHAR(20) -- 'pending', 'completed', 'cancelled' -- 推荐：使用整数+枚举 status TINYINT -- 1: pending, 2: completed, 3: cancelled
（3）避免使用NULL，可设置默认值
-- 推荐：设置默认值而非允许NULL username VARCHAR(50) NOT NULL DEFAULT ''
2. 合理设计表结构
（1）遵循三范式，减少数据冗余
（2）适当反范式化，提高查询效率（如添加冗余字段减少JOIN）
（3）拆分大表（水平拆分和垂直拆分）
-- 垂直拆分：将大表拆分为多个小表 -- 用户基本信息表 CREATE TABLE user_basic (id INT PRIMARY KEY, username VARCHAR(50), ...); -- 用户详细信息表 CREATE TABLE user_details (user_id INT PRIMARY KEY, address TEXT, ...);
四、查询执行计划分析（EXPLAIN）
使用EXPLAIN命令分析查询语句的执行计划，识别性能瓶颈。
1. 基本用法
EXPLAIN SELECT * FROM users WHERE username = 'john';
2. 关键字段解读
（1）type：表示连接类型，从好到差为：
system > const > eq_ref > ref > range > index > ALL
目标：至少达到range级别，最好是ref或eq_ref
（2）key：实际使用的索引，如果为NULL表示未使用索引
（3）rows：估计需要扫描的行数，值越小越好
（4）Extra：额外信息，常见重要值：
- Using index：使用覆盖索引，性能好
- Using where：需要过滤数据
- Using filesort：需要额外排序，性能差
- Using temporary：需要创建临时表，性能差
五、数据库配置优化
1. 调整缓存设置
（1）增大InnoDB缓冲池（innodb_buffer_pool_size），建议设置为服务器内存的50%-70%
（2）优化查询缓存（query_cache_size），但MySQL 8.0已移除该功能
2. 调整连接设置
（1）合理设置最大连接数（max_connections）
（2）设置连接超时时间（wait_timeout）
3. 日志优化
（1）生产环境关闭通用查询日志（general_log）
（2）合理设置慢查询日志（slow_query_log），记录执行时间长的查询
六、其他优化技巧
1. 使用批量操作
-- 不推荐：多次单条插入
INSERT INTO users (username) VALUES ('user1');
INSERT INTO users (username) VALUES ('user2');
-- 推荐：批量插入
INSERT INTO users (username) VALUES ('user1'), ('user2'), ('user3');
2. 合理使用视图和存储过程
复杂查询可封装为视图，重复执行的逻辑可使用存储过程，但需注意过度使用可能导致性能问题。
3. 定期维护表
-- 优化表（整理碎片，优化索引） OPTIMIZE TABLE 表名; -- 分析表（更新统计信息，帮助优化器生成更好的执行计划） ANALYZE TABLE 表名;
4. 读写分离和分库分表
对于大型应用，可采用：
- 读写分离：主库写入，从库读取
- 分库分表：水平拆分（按数据量）或垂直拆分（按业务）
七、优化原则总结
1. 减少数据访问：只查询需要的字段和行
2. 减少数据传输：优化网络和IO操作
3. 利用索引：合理设计和使用索引
4. 优化表结构：选择合适的数据类型和表设计
5. 分析执行计划：使用EXPLAIN指导优化
6. 循序渐进：先定位问题，再针对性优化
7. 测试验证：优化后需测试性能是否真的提升
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
