MySQL 二轮学习笔记·进阶篇·(三) SQL 优化

SQL优化

1.插入优化

(1)批量插入代替单条插入

频繁单条插入，每次建立连接，解析SQL，刷盘，效率太低，改用INSERT INTO user(id, name) VALUES(1, 'A'), (2, 'B'), ..., (1000, 'Z');这样的批量插入语句

(2)关闭自动提交事务

默认情况下，数据库开启 “自动提交”（autocommit=ON），每条插入语句都会触发一次事务提交，而事务提交会强制刷盘（fsync），IO 成本极高

SET autocommit = OFF; -- 关闭自动提交

INSERT INTO user(...) VALUES(...), (...); -- 批量插入多条记录
-- 或者多次执行 INSERT...
INSERT INTO user(...) VALUES(...);
INSERT INTO user(...) VALUES(...);

COMMIT; -- 所有插入成功后才统一提交
SET autocommit = ON; -- 恢复默认

(3)临时关闭非必要索引

若插入为全量初始化（比如数据迁移），可以先删除非主键索引（主键索引不能删除），插入完成后再重建索引，防止实时更新索引导致效率低下

drop index idx_user_name on user;
insert into user(...) values(...),(...);
create index idx_user_name on user(name);

(4)使用load data infile进行大文件导入

大量数据（csv文件等）导入，推荐使用load data infile而不是insert语句

load data local infile '/data/user.csv'
into table user
fields terminated by ',' -- 字段分隔符（csv用逗号）
lines terminated by '\n' -- 行分隔符
ignore 1 rows; -- 忽略首行（若为表头）

2.主键优化

(1)主键必须满足唯一、非空，禁止修改

数据库会强制主键的unique和not null，防止b+树有序性被破坏，影响性能。

若修改主键，引发节点分裂/合并，所有二级索引需要同步更新叶子结点的主键值，开销极大，因此通常禁止主键修改。

eg. 手机号不能作主键

(2)有序且长度尽可能小，避免使用uuid随机串

eg. 身份证号太长不能作主键；uuid太长，存储成本高，且无序，b+树频繁分裂，影响性能

3.order by优化

• Using filesort：数据库无法利用索引排序，需先查询出数据，再在内存 / 磁盘中对数据排序（内存不够时会写临时文件），时间复杂度 O (n log n)，数据量大时极慢。
• Using index：数据库直接利用索引的有序性返回结果（B + 树索引本身是有序的），无需额外排序，时间复杂度 O (log n)，效率极高。

优化核心：满足 “索引覆盖排序”，避免触发 filesort

即构建 “排序字段 + 查询字段” 的联合索引，让ORDER BY和SELECT的字段都在索引中，无需回表查询数据。

• 排序字段包含NULL值：NULL值的排序规则不固定，会干扰索引有序性，建议给排序字段加NOT NULL约束。
• SELECT字段过多：若查询字段不在索引中，需回表取数据，无法利用索引排序（需先查数据再排序）。
• 排序字段使用函数 / 表达式：如ORDER BY SUBSTR(name, 1, 3)，会破坏索引的有序性，无法使用索引。

4.group by优化

优化核心：避免Using temporary(临时表)和Using filesort，借助索引完成分组，构建 “分组字段 + 聚合字段” 的联合索引

• Using temporary：若无法利用索引分组，数据库会创建临时表存储分组结果（如先把数据写入临时表，再按分组字段聚合），临时表可能在内存或磁盘中，磁盘临时表效率极低。

利用索引有序性避免临时表

CREATE INDEX idx_city ON users(city);
SELECT city, COUNT(*) FROM users GROUP BY city;

• 索引 idx_city 按 city 排序
• 扫描时：Beijing, Beijing, Shanghai, Shanghai, ...
• 引擎边扫边计数，城市一变就输出上一组结果
• 无需临时表 → EXPLAIN 中 没有 Using temporary

💡 即使没有覆盖索引（比如还要查 name），只要 GROUP BY 字段有序，也可能避免临时表（但可能仍需回表）。

例子：

SELECT dept_id, SUM(salary) FROM user 
WHERE age > 30 AND dept_id = 5  -- 这里有dept_id条件
GROUP BY dept_id;

索引使用分析：

• ✅ age > 30：使用索引第一列（范围扫描）
• ❌ dept_id = 5：在WHERE中失效（因为它在范围查询age>30的右边）
• ✅ GROUP BY dept_id：在GROUP BY中仍然可以利用索引的有序性

利用覆盖索引避免回表

-- 创建覆盖索引
CREATE INDEX idx_city_age_salary ON users(city, age, salary);

-- 查询：所有字段都在索引中
SELECT city, AVG(age), SUM(salary) FROM users GROUP BY city;
-- 直接扫描索引就能完成，不需要回表

• 所有字段 city, age, salary 都在索引中 → 覆盖索引
• 引擎只需读索引，不回表 → EXPLAIN 显示 Using index
• 但如果 city 在索引中不是有序的（比如索引是 (age, city)），GROUP BY city 仍可能需要临时表！

覆盖索引 ≠ 自动避免 Using temporary
它只保证“不回表”，但分组是否需要临时表，取决于 GROUP BY 字段是否有序。

5.limit优化

LIMIT m, n用于分页查询（如LIMIT 10000, 10表示跳过 10000 条，取 10 条），但当 m（偏移量）过大时，会出现 “查询缓慢”，核心原因是 “数据库需扫描前 m+n 条数据，再丢弃前 m 条”。

方案1：用主键过滤替代limit偏移量

-- 第1页：LIMIT 0, 10（正常查询）
SELECT id, name FROM user ORDER BY create_time DESC LIMIT 0, 10;
-- 假设第1页最后一条id为10010

-- 第2页：用id>10010过滤，替代LIMIT 10, 10
SELECT id, name FROM user WHERE id>10010 ORDER BY create_time DESC LIMIT 0, 10;

-- 第10001页：用id>xxx过滤，替代LIMIT 100000, 10
SELECT id, name FROM user WHERE id>xxx ORDER BY create_time DESC LIMIT 0, 10;

create_time需与主键id同序（如自增主键，create_time 随 id 递增），否则需调整过滤条件（如用create_time和id联合过滤）。

方案2：覆盖索引+子查询，避免大量回表

-- 优化前：回表100010条数据
SELECT id, name FROM user ORDER BY create_time DESC LIMIT 100000, 10;

-- 优化后：
-- 1. 子查询用覆盖索引（idx_user_ctime）查10条主键，仅扫描100010条索引（无回表）
-- 2. 主查询用主键查详情，仅回表10条数据
SELECT u.id, u.name 
FROM user u
INNER JOIN (
    SELECT id FROM user ORDER BY create_time DESC LIMIT 100000, 10
) t ON u.id = t.id;

若无法用主键过滤，可先通过 “覆盖索引” 查询出需返回的主键，再回表查询详情，减少回表数据量。

优化方案中，确实存在“回表查询”，但只回表 10 次，而不是 100,010 次。

方案3：禁止 “超大分页”（业务限制）

• 实际业务中，用户很少会翻到第 100 页以后（如电商商品列表，翻到第 20 页已无意义），可直接限制最大分页页数（如最多支持 50 页），超出后提示 “无更多数据”。
• 示例：LIMIT m, n中，若m >= 50*10（假设每页 10 条），直接返回空结果。

6.count优化

COUNT()用于统计记录数，不同用法的效率差异极大，核心是减少数据库扫描的数据量，优先选择 “索引统计” 而非 “全表扫描”。

方案1：优先用 COUNT (*) 或 COUNT (1)

MySQL 对COUNT(*)有特殊优化，会自动选择 “成本最低” 的索引（优先主键索引，其次非空二级索引）进行统计，无需扫描全表；COUNT(1)逻辑与COUNT(*)类似，数据库会忽略1这个常量，直接统计行数，效率基本无差异。

可利用 “非空二级索引” 加速统计

主键索引（聚簇索引）存储了完整数据行，体积较大；而非空二级索引（如idx_user_status(status NOT NULL)）的叶子节点仅存储 “索引键 + 主键”，体积更小，扫描速度更快。

• 示例：若表有非空二级索引idx_user_status(status)，执行COUNT(*)时，MySQL 会自动选择该索引扫描，而非主键索引，减少 IO 开销。
• 注意：需确保二级索引字段为NOT NULL，否则数据库无法用其统计（因COUNT(*)包含NULL行，而二级索引不存储NULL值）。

方案2：缓存统计结果

若业务对统计数据的 “实时性” 要求不高（如报表、后台统计），无需每次查询都实时计算，可通过缓存减少数据库压力：

• 实现方式：

  1. 定时任务（如每 5 分钟）执行COUNT(*)，将结果存入 Redis（如SET user_count 100000）；
  2. 业务查询时直接从 Redis 获取（GET user_count），而非访问数据库。
• 适用场景：用户总数、订单总数等变化不频繁，且允许 5-10 分钟延迟的数据。

注意避免 COUNT (DISTINCT)，用 “先去重再统计” 替代

COUNT(DISTINCT 字段)需先对字段去重（可能触发临时表和文件排序），再统计，效率极低。

原 SQL：SELECT COUNT(DISTINCT dept_id) FROM user;

-- 优化后：先通过索引去重获取所有dept_id，再统计行数
SELECT COUNT(*) 
FROM (
    SELECT dept_id 
    FROM user 
    GROUP BY dept_id -- 利用idx_user_dept索引去重，效率高于DISTINCT
) t;

GROUP BY可借助索引有序性去重，避免DISTINCT的全量数据排序，尤其当dept_id有索引时，优化效果显著。

7.update优化

优化关键：避免行锁升级为表锁

UPDATE语句的锁机制直接影响并发性能：行锁（Row Lock）仅锁定修改的行，并发高；表锁（Table Lock）锁定整个表，并发极低。优化核心是 “确保数据库能精准定位到行，避免触发行锁升级”。

• 行锁触发：UPDATE语句的WHERE条件能通过 “索引” 精准定位到 1 行或多行数据，数据库仅锁定这些行（InnoDB 引擎默认支持行锁）。
• 表锁触发：UPDATE语句的WHERE条件无索引（或索引失效），导致全表扫描，数据库无法确定要修改的行，会将行锁升级为表锁，阻塞所有对该表的读写操作。

优化核心：WHERE条件必须使用 “有效索引”

避免批量更新大量数据：若UPDATE一次修改 10000 行，即使触发行锁，也会持有大量行锁，导致其他事务等待。建议拆分批量更新（如每次更新 100 行，加短暂休眠），降低锁竞争。

-- 拆分前：一次更新10000行，持有10000个行锁
UPDATE user SET status=1 WHERE create_time < '2024-01-01';

-- 拆分后：每次更新100行，循环执行
UPDATE user SET status=1 WHERE create_time < '2024-01-01' LIMIT 100;
-- 休眠100ms，释放锁，给其他事务机会
SELECT SLEEP(0.1);
-- 重复执行直到无数据可更

明确UPDATE的字段：避免UPDATE user SET ...（不指定字段），仅更新必要字段，减少锁持有时间（字段更新越少，事务执行越快，锁释放越早）。

小结

SQL 优化的核心逻辑是 “减少数据库的 IO 开销和计算开销”，以上模块可归纳为 3 大核心原则，覆盖 90% 的优化场景：

(1)索引是优化的核心：“建对索引 + 用好索引”

• 建对索引：

  • 插入 / 主键：优先自增主键，避免 UUID；非必要索引在批量插入时临时删除，后重建。
  • 查询 / 排序 / 分组：构建 “过滤字段 + 排序字段 + 分组字段 + 查询字段” 的联合索引，满足 “最左前缀原则” 和 “覆盖索引”。
  • 更新：WHERE条件字段必须加索引，避免行锁升级。
• 用好索引：避免索引失效（如函数操作、类型不匹配、% 开头的模糊查询）。

(2)减少数据扫描量：“能过滤的先过滤，能批量的不逐条”

• 查询：用WHERE先过滤无关数据，再排序 / 分组（如WHERE dept_id=10 ORDER BY age比ORDER BY age WHERE dept_id=10更高效，逻辑上一致，但数据库执行顺序是WHERE→ORDER BY）。
• 插入：批量插入（INSERT ... VALUES(...)）替代单条插入，大文件用LOAD DATA INFILE。
• 更新：拆分大量更新为小批量，减少锁竞争。

(3)规避低效操作：“远离临时表、文件排序、全表扫描”

• 排序 / 分组：避免Using filesort和Using temporary，通过覆盖索引实现 “索引排序 / 分组”。
• 统计：优先COUNT(*)/COUNT(1)，利用非空二级索引，非实时场景用缓存。
• 分页：大偏移量用 “主键过滤” 替代LIMIT m, n，避免扫描大量数据。