3. SQL优化
SQL性能优化
在日常开发中，MySQL性能优化是一项必不可少的技能。本文以具体案例为主线，结合实际问题，探讨如何优化插入、排序、分组、分页、计数和更新等操作，帮助你实现数据库性能的飞跃。
一、索引设计原则
索引是MySQL优化的核心，合理的索引设计能显著提升性能。以下为关键原则：
二、insert 优化
2.1 批量插入
与单条插入相比，批量插入大幅减少网络开销和SQL解析成本：
-- 单条插入
INSERT INTO users (name, age) VALUES ('Alice', 25);-- 批量插入
INSERT INTO users (name, age) VALUES ('Alice', 25), ('Bob', 30), ('Charlie', 35);
建议：尽量使用批量插入以提升效率。
2.2 手动控制事务
将多条INSERT语句包裹在事务中，减少频繁提交的开销：
START TRANSACTION;
INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
COMMIT;
2.3 大批量数据插入
对于大批量数据，使用LOAD DATA语句导入效率更高：
-- 1. 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 2. 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 3. 执行load指令将准备好的数据，加载到表结构中
LOAD DATA INFILE '/root/mysql-data/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
或
load data local infile '/root/mysql-data/data.sql' into table `users` fields terminated by ',' lines terminated by '\n';
三、order by 优化
MySQL排序主要有两种方式：
- Using filesort：通过临时表排序，性能较低。
- Using index：利用索引直接排序，性能较高。
优化策略
- 创建索引：为
ORDER BY字段创建索引，让MySQL直接使用索引排序。
-- 创建索引
CREATE INDEX idx_age ON users (age);-- 优化查询
SELECT * FROM users ORDER BY age;
原则：
order by优化原则:
- A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- B. 尽量使用覆盖索引。
- C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
- D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小
sort_buffer_size(默认256k)。
四、group by 优化
分组查询GROUP BY可以通过索引来提升性能。索引需满足最左前缀法则：
-- 无索引时的查询
EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY age ASC, phone DESC;-- 添加索引后优化
CREATE INDEX idx_user_age_phone_aa
ON tb_user (age ASC, phone DESC);EXPLAIN SELECT id, age, phone FROM tb_user ORDER BY age ASC, phone DESC;
最左前缀法则：如果联合索引的前几个字段包含分组字段，则查询效率更高。
五、limit 优化
分页查询中，偏移量越大，性能越低。以下是优化思路：
5.1 常规查询（低效）
SELECT * FROM users LIMIT 100000, 10;
5.2 优化方式：使用覆盖索引和子查询
-- 优化分页查询
SELECT * FROM users
WHERE id > (SELECT id FROM users ORDER BY id LIMIT 100000, 1)
LIMIT 10;
覆盖索引：只返回查询字段所需的索引数据，避免访问表的其他列，提高效率。
六、count() 优化
6.1 常见计数方式及性能分析
| 计数方式 | 含义 | 效率 |
|---|---|---|
COUNT(字段) | 遍历表并检查字段是否为NULL。 | 较低 |
COUNT(主键) | 遍历表并直接计数主键行。 | 较高 |
COUNT(1) | 遍历表但不取值，仅直接按行计数。 | 高 |
COUNT(*) | 专门优化，不取值，仅直接按行计数。 | 与COUNT(1)效率相当 |
建议：优先使用
COUNT(*)，性能最优且语义清晰。
七、update 优化
7.1 行锁与表锁
MySQL的InnoDB存储引擎采用行锁，但当索引失效时会升级为表锁，严重影响性能。例如：
-- 锁定单行（高效）
UPDATE course SET name = 'JavaEE' WHERE id = 1;-- 锁定全表（低效）
UPDATE course SET name = 'SpringBoot' WHERE name = 'PHP';
说明：第二条语句因未命中索引，行锁升级为表锁。
7.2 优化策略
- 确保查询条件命中索引。
- 避免一次更新过多行。
-- 添加索引优化
CREATE INDEX idx_name ON course (name);
UPDATE course SET name = 'SpringBoot' WHERE name = 'PHP';
总结
MySQL性能优化的核心在于充分利用索引、批量操作和事务控制，同时针对具体场景调整策略。希望本文的案例和原则能帮助你在实际开发中事半功倍！