MySQL

MySQL | 进阶-SQL优化

本文更新于 2026-04-04

插入数据

普通 Insert 优化 (批量插入)

如果需要插入多条数据，不要每一条都执行一次 INSERT 语句。

  • 错误做法：循环执行 1000 次 INSERT INTO user VALUES (...);
  • 优化做法：使用批量插入（建议一次批量 500-1000 条）。
	INSERT INTO user VALUES (1,'Tom'), (2,'Jerry'), (3,'Rose');
  • 原理：减少了客户端与数据库之间的网络连接开销，降低了 SQL 解析的次数。

事务优化 (手动提交事务)

MySQL 默认是自动提交（autocommit）的，这意味着每执行一次 INSERT，数据库就会进行一次磁盘同步（Disk Flush）。

  • 优化做法：手动开启并提交事务。
	START TRANSACTION;
	INSERT INTO user VALUES (...);
	INSERT INTO user VALUES (...);
	-- 执行成百上千条后再统一提交
	COMMIT;
  • 原理：将多次磁盘 I/O 操作合并为一次，极大提升写入性能。

主键顺序插入

在设计表结构时，主键的选择直接影响插入效率。

  • 优化建议：尽量使用自增主键，保证主键顺序插入。
  • 原因：InnoDB 使用 B+Tree 索引。如果主键乱序（如 UUID），会导致频繁的页分裂 (Page Split) 和记录移动，增加磁盘开销。顺序插入则只需在索引页末尾追加。

大批量数据加载 (load data local infile)

如果你有成百万条数据存在文本文件（如 CSV）中，使用 INSERT 语句会非常缓慢。此时应使用 MySQL 提供的 load 指令。

  • 操作步骤：
    1. 客户端连接时开启本地文件加载：mysql --local-infile -u root -p
    2. 设置全局参数：set global local_infile = 1;
    3. 执行加载语句：
	load data local infile '/root/sql_data.log' 
	into table `user` 
	fields terminated by ',' 
	lines terminated by '\n';
  • 性能：这种方式直接绕过了部分 SQL 解析逻辑，比普通的 INSERT 快一个数量级。

主键优化

InnoDB 数据组织方式 (Index Organized Table)

在 InnoDB 存储引擎中，表数据都是根据主键顺序组织存放的。这种存储方式的表称为 索引组织表 (Index Organized Table, IOT)

  • 核心特征：
    • 行数据即索引：在 InnoDB 的聚集索引中，叶子节点直接存储了整行数据。
    • 主键即路径：当你通过主键查询时，不需要额外的寻址，找到索引即找到了数据。
    • 强制性：由于数据必须按序存放，如果表没有显式定义主键，InnoDB 会自动寻找或生成隐式主键来组织数据。

数据存储原理：页分裂 (Page Split)

InnoDB 的最小存储单元是 页 (Page)，默认大小为 16KB。 页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。

  • 顺序插入：如果主键自增，数据会按顺序填满物理页。当一页满时，直接开辟新页继续写入，效率极高。
  • 乱序插入 (如 UUID)：如果主键是随机的，新行可能需要插入到已经写满的页中间。此时，InnoDB 必须将该页拆分为两页，并移动部分数据，这被称为页分裂。
  • 后果：页分裂会导致大量的磁盘 I/O，并产生物理碎片。

数据存储原理：页合并 (Page Merge)

当删除一行记录时，实际上记录并未从磁盘物理删除，只是被标记为“已删除”，其空间可以被覆盖利用。

  • 合并逻辑：当一个页中被删除的记录达到一定比例（默认为 MERGE_THRESHOLD 50%），InnoDB 会尝试寻找相邻的页，看是否可以将两个页合并以优化空间。
  • 后果：频繁的页合并和分裂会使索引树结构变得不稳定。

主键设计原则 (Best Practices)

尽量降低主键长度

二级索引（辅助索引）的叶子节点存储的是主键值。如果主键过长，会导致所有的二级索引都占用巨大的空间，增加磁盘 I/O 并降低内存命中率。

尽量选择自增主键 (AUTO_INCREMENT)

  • 保证顺序插入，完全规避页分裂问题。
  • 写入速度最快，索引树最紧凑。

避免使用 UUID 或其他随机字符串

  • 原因：UUID 是随机无序的，插入时会引发频繁的页分裂；且 UUID 通常较长（36位），会增加二级索引的负担。
  • 方案：如果业务需要全局唯一标识，建议使用雪花算法 (Snowflake) 生成的趋势递增整数。

业务逻辑不要作为主键

  • 不要使用身份证号、手机号等具有业务意义的字段作为主键。
  • 原因：业务逻辑可能发生变化（如身份证号升位），一旦主键修改，所有二级索引都要同步更新，代价极大。

ORDER BY 优化

MySQL 的排序实现方式主要有两种：

  1. Using index：通过有序索引顺序扫描直接返回有序数据，不需要额外排序，效率最高。
  2. Using filesort：通过表的索引或全表扫描读取数据，然后在排序缓冲区 sort_buffer 中进行排序。

排序优化的核心原则

如果要实现 Using index 提升性能，必须满足以下条件：

  • 最左前缀法则：ORDER BY 的字段顺序必须符合联合索引的列顺序。
  • 方向一致性：如果排序包含多个列，这些列的排序方向（升序 ASC / 降序 DESC）必须一致（MySQL 8.0 引入了降序索引，可部分解决此问题）
  • 降序索引：
	-- 创建联合索引：age 升序，phone 降序
	CREATE INDEX idx_user_age_phone_desc ON user(age ASC, phone DESC);
  • 覆盖索引：SELECT 的字段最好只包含索引列，否则可能因为回表开销过大，导致优化器放弃索引排序而选择 filesort。
场景索引 idx(a, b) 是否生效备注
ORDER BY a是符合最左前缀
WHERE a = 1 ORDER BY b是a 已固定，b 在索引内有序
ORDER BY a, b是顺序一致
ORDER BY b否违背最左前缀
WHERE a > 10 ORDER BY b否a 是范围查询，其后的 b 在全局上是无序的
ORDER BY a ASC, b DESC否排序方向不一致（MySQL 8.0 以下）

优化建议总结 (Best Practices)

  1. 根据排序字段建立索引：尤其是针对 WHERE 过滤后还需要 ORDER BY 的场景，建立联合索引。
  2. 遵循最左前缀：确保排序字段在索引中的位置正确。
  3. 只取必要的字段：避免 SELECT *，尽量使用覆盖索引。
  4. 统一排序方向：除非使用了 MySQL 8.0 的降序索引功能，否则尽量保证所有排序字段都是 ASC 或都是 DESC。
  5. 不可避免 filesort 时：对于大数据量排序，适当调大 sort_buffer_size 减少磁盘 I/O。

GROUP BY优化

MySQL 在执行 GROUP BY 时，主要有两种方式：

  1. Using index：通过索引直接进行分组，效率最高
  2. Using temporary; Using filesort：需要建立临时表并在内存或磁盘中排序，性能较差

核心优化原则：利用索引

由于分组操作通常涉及排序，因此 最左前缀法则 在 GROUP BY 中同样适用。

场景分析

联合索引：idx_user_pro_age (pro, age)

场景类型SQL 语句执行状态说明
全匹配（索引生效）SELECT pro, count(*) FROM user GROUP BY pro, age;Using index-
符合最左前缀（索引生效）SELECT pro, count(*) FROM user GROUP BY pro;Using index-
不符合最左前缀（索引失效）SELECT age, count(*) FROM user GROUP BY age;Using temporary; Using filesort-
结合 WHERE 使用（索引生效）SELECT age, count(*) FROM user WHERE pro = '软件工程' GROUP BY age;Using index虽然 GROUP BY 跳过了第一列，但 WHERE 子句已经补齐了最左前缀。

LIMIT优化

性能瓶颈：深度分页问题

当执行 LIMIT 1000000, 10 时，MySQL 并不是直接跳过前 100 万行，而是：

  1. 全表扫描（或索引扫描）：读取前 1000010 条记录。
  2. 回表查询：如果查询的是 SELECT *，则需要回表 1000010 次获取行数据。
  3. 丢弃数据：抛弃前 100 万条，仅返回最后 10 条。
  • 结论：偏移量（Offset）越大，磁盘 I/O 越高，查询越慢。

2. 优化方案 ：覆盖索引 + 子查询 (延迟关联)

这是最通用的优化方案。通过子查询先在索引树上定位主键 ID（不回表），再关联原表获取数据。

  • 优化前（慢）：
SELECT * FROM user LIMIT 1000000, 10;
  • 优化后（快）：
	SELECT * FROM user t, (
		SELECT id FROM user ORDER BY id LIMIT 1000000, 10
	)a 
	WHERE t.id = a.id;
  • 原理：子查询 (SELECT id ...) 利用了覆盖索引，极大地减少了回表次数。

COUNT 优化

存储引擎的差异

  • MyISAM：把一个表的总行数存在了磁盘上，因此执行 count(*) 时会直接返回这个数，效率极高（前提是没有 WHERE 条件）。

  • InnoDB：由于多版本并发控制 (MVCC) 的原因，InnoDB 表“应该返回多少行”是不确定的。它必须把数据一行一行从引擎里面读出来，然后累计计数。

    • 优化结论：在 InnoDB 中，count(*) 是需要全表扫描或索引扫描的。

COUNT 的几种用法对比

假设执行 SELECT COUNT(X) FROM table;，性能排序通常为： count(*) ≈ count(1) > count(主键ID) > count(字段)

用法执行逻辑性能评估
count(主键ID)遍历整张表，取每行的 ID，返回给服务层，服务层判断不为 NULL 后累加。较快，但涉及取值。
count(字段)遍历整张表，取该字段值。有 NOT NULL 约束则直接累加；没有则需判断是否为 NULL。最慢，涉及取值和判断。
count(1)遍历整张表，但不取值。服务层对返回的每一行放入一个数字“1”，直接累加。极快，不涉及取值。
count(*)MySQL 专门优化过。不取值，直接按行累加。它会选择体积最小的二级索引树进行扫描。推荐使用，性能最高。

UPDATE 优化

核心原理：行锁与表锁

InnoDB 存储引擎支持行级锁 (Row Lock)，这是它支持高并发的关键。但需要注意：

  • 行锁是针对索引加的锁：不是针对记录加的锁。

  • 索引失效则锁全表：如果 UPDATE 语句的 WHERE 条件没有使用索引，或者索引失效，InnoDB 会对全表所有记录加锁，实际上变成了表级锁。

	-- 有索引 -> 行锁
	UPDATE course SET name = 'SpringBoot' WHERE id = 3;
	-- 无索引 -> 表锁
	UPDATE course SET name = 'SpringBoot' WHERE name = 'JavaEE';
今日访问 ... 次 | 今日访客 ... 人 | 本页阅读 ... 次
小站已萌萌哒运行了 0 天 0 时 0 分
已累计耕耘 33 篇博文 · 共 115.17k 个字
总访问量 ... 次
备案图标 豫公网安备41019702004633号