SQL优化

日常开发与数据库实训中，慢查询、高并发阻塞、分页卡顿等问题几乎都源于不合理的 SQL 编写与索引设计。很多人只停留在基础 CRUD 编写，不会借助 EXPLAIN 分析执行计划，也不了解联合索引、行锁、临键锁会对查询性能产生连锁影响。本文结合实操 SQL 案例，系统梳理索引规范、语句改写、分页 / 分组优化、锁机制避坑等全套 SQL 优化方案，兼顾理论原理与落地实操。

插入数据（insert优化）

1. 多条语句合并插入

用VALUES一次插入多条数据，提高效率

-- 差：多次IO、多次事务
INSERT INTO user(name) VALUES ('a');
INSERT INTO user(name) VALUES ('b');
INSERT INTO user(name) VALUES ('c');

-- 优：一次提交
INSERT INTO user(name) VALUES ('a'),('b'),('c');

2. 手动提交事务

每次插入数据后系统都会自动提交事务，导致耗时长，手动提交事务，可以在所有数据插入后再提交，提高效率

START TRANSACTION;
INSERT INTO t VALUES(),(),();
INSERT INTO t VALUES(),(),();
COMMIT;

3. 主键顺序插入

由于B+tree是根据主键顺序排序的，如果乱序插入会拉低效率，在后面的主键优化会详细说明

4. 使用load大量插入数据

   #客户端连接服务端时，加上参数 --local-infile
   mysql --local-infile -u root -p
   
   #设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
   set global local_infile = 1;
   
   #执行load指令将准备好的数据，加载到表结构中
   load data local infile '/root/sql1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';
   

主键优化

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表

如图，树叶节点的存储顺序是由主键的大小排序的。

如果主键乱序存放，会导致两种现象

页分裂：

原本23和47在第一页中，每一页放了5个数据，当50想插入时，应该插在47和55之间，而两页都满了，就会出现第一页中数据分为两页，再将50放在47后面，此时重新形成链表

分裂和重新形成链表的时间会造成时间的浪费。

页合并：

页合并是指在删除数据时，被删除数据只是被标记为删除，只有当此页的数据小于某个值，下一页的数据才会和上一页合并。

主键设计原则：

• 满足业务需求的情况下，尽量降低主键的长度。

• 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键。

• 尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号。

• 业务操作时，避免对主键的修改。

order by优化

Using filesort通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。

Using index通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

所以优化order by的方法尽量走联合索引，用Using index

1. 符合最左前缀准则，但是顺序不能颠倒

索引为idx_a_b;
order by a,b; --正确方法
order by b; -- 跳过最左前缀a
order by b,a; -- 顺序颠倒

2. 升序降序有讲究

如果索引是a和b都升序，而order by的时候是a或b其中一个降序，那么也会出现using filesort

解决方法是建立相应顺序的联合索引

CREATE INDEX idx_name ON tb_user(a desc,b asc);

a 降序，b升序

group by优化

Using temporary：生成临时表存分组数据

Using filesort：分组自带排序，额外排序开销

这两种的性能低，为了避免以上，方法如下

建立联合索引:

select gender, count() from tb_user group by gender;*

上面语句，需要***CREATE INDEX idx_gender ON tb_user(gender);***来建立索引优化

count(*)只是获取行数，建立gender的索引既可以通过最左前缀法走索引获取，而不是全表扫描。

如果语句带where

select gender, count() from tb_user where age>18 group by gender;*

则需要建立idx_gender_age(gender,age)索引，且group by字段放前，where字段放后，否则违反范围查询法则。

limit优化

当要搜索很后面的数据时，limit语句先要扫描完前面的语句，再去获取需要的数据，导致时间开销大

优化方案：

1. 利用主键有序，用where id > 偏移值替代大 offset，只扫描目标区间

   SELECT * FROM tb_user WHERE id > 100000 ORDER BY id LIMIT 10;

   而不是limit100000，10

2. 子查询

SELECT s. FROM tb_sku s, (SELECT id FROM tb_sku ORDER BY id LIMIT 90000,10) t WHERE s.id = t.id;*

先用SELECT id FROM tb_sku ORDER BY id LIMIT 90000,10将id拿到，再将这些id新建一个表t，用来查询

---

SQL 优化的核心思路，是通过合理索引缩小扫描范围，同时规避大范围锁、索引失效等隐形性能损耗。写查询语句时善用执行计划校验，遵循索引设计准则，控制事务执行时长，就能大幅降低慢查询与锁等待问题。吃透这套优化逻辑，无论是课程项目、课后实操，还是后续后端开发学习，都能有效提升数据库处理效率

如果喜欢我的内容，觉得我的内容有帮助到你的，可以动动小手点赞收藏，我们下期再见！