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排序主要有两种方式：

1. Using filesort：通过临时表排序，性能较低。
2. 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性能优化的核心在于充分利用索引、批量操作和事务控制，同时针对具体场景调整策略。希望本文的案例和原则能帮助你在实际开发中事半功倍！