MySQL SQL优化

优化核心原则

SQL 优化的核心目标是减少全表扫描、充分利用索引、降低数据库资源消耗，最终提升查询与写入效率。

高频 SQL 优化技巧

1. 避免索引列使用函数，确保索引生效

问题本质：MySQL 不支持“函数索引”，若在索引列上使用函数（如日期函数、字符串函数），会导致索引失效，触发全表扫描。

反例（低效）：

  SELECT * FROM table WHERE YEAR(date) >= 2020;  -- date字段有索引但失效，全表扫描
  SELECT * FROM user WHERE SUBSTR(name, 1, 2) = '张';  -- 索引列name用函数，索引失效

正例（高效）：

  SELECT * FROM table WHERE date >= '2020-01-01';  -- 直接对字段值做条件判断，索引生效
  SELECT * FROM user WHERE name LIKE '张%';  -- 替代SUBSTR函数，利用前缀索引

建议：将函数操作转移到条件值上，而非索引列本身，让优化器能正常匹配索引。

　

2. 用 IN 替代 OR，提升查询效率

问题本质：当条件中包含多个等值判断时，OR 会导致 MySQL 难以优化执行计划，而 IN 能更好地利用索引（尤其适合索引列的多值匹配）。

反例（低效）：

  SELECT * FROM table WHERE id = 10 OR id = 20 OR id = 30;  -- 多OR条件，索引利用不充分

正例（高效）：

  SELECT * FROM table WHERE id IN (10, 20, 30);  -- IN适配索引，执行效率更高

注意：IN 后的参数列表不宜过长（建议不超过 1000 个），若参数过多，可拆分为多个小批量 IN 查询或使用临时表关联。

　

3. 控制 LIKE 通配符使用，避免索引失效

问题本质：LIKE 模糊查询中，前缀通配符（%xxx）会导致索引失效，而后缀通配符（xxx%）可正常使用前缀索引。

反例（低效）：

  SELECT * FROM table WHERE name LIKE '%hi%';  -- 前后双百分号，索引失效，全表扫描
  SELECT * FROM table WHERE name LIKE '%hi';  -- 前缀百分号，索引失效

正例（高效）：

  SELECT * FROM table WHERE name LIKE 'hi%';  -- 后缀百分号，利用前缀索引

替代方案：若需实现“包含”查询（类似 %hi%），可使用全文索引（FULLTEXT INDEX）替代 LIKE，尤其适合大文本字段（如文章内容、备注）。

　

4. 保证数据类型一致，避免隐式转换

问题本质：查询条件中字段类型与传入值类型不一致时，MySQL 会进行隐式类型转换，导致索引失效（如 int 型字段匹配字符串值）。

反例（低效）：

  -- id字段定义为INT类型，传入字符串'10000'，触发隐式转换，索引失效
  SELECT * FROM table WHERE id = '10000';

正例（高效）：

  SELECT * FROM table WHERE id = 10000;  -- 类型一致，索引正常生效

常见场景：

  • 字符串类型字段（如 varchar）避免匹配数值（如 where phone = 13800138000 改为 where phone = '13800138000'）；

  • 日期类型字段避免匹配字符串（如 where create_time = '2024-01-01' 需确保字符串格式与字段类型兼容）。

　

5. 分组查询（GROUP BY）禁止默认排序

问题本质：MySQL 对 GROUP BY 子句默认会按分组字段排序，若无需排序，该操作会额外消耗性能（尤其数据量较大时）。

反例（低效）：

  SELECT goods_id, COUNT(*) FROM table GROUP BY goods_id;  -- 默认排序，额外消耗资源

正例（高效）：

  SELECT goods_id, COUNT(*) FROM table GROUP BY goods_id ORDER BY NULL;  -- 显式禁止排序

注意：若业务需按分组字段排序，可直接在 GROUP BY 后指定排序（如 GROUP BY goods_id ASC）；若无需排序，必须添加 ORDER BY NULL。

　

6. 优先使用批量插入，替代循环单条插入

问题本质：循环执行单条 INSERT 语句会频繁建立数据库连接、提交事务，性能极低；批量插入可减少连接开销和事务提交次数。

反例（低效）：

  -- 循环执行多条单条插入（性能差）
  INSERT INTO table (name, age) VALUES ('张三', 20);
  INSERT INTO table (name, age) VALUES ('李四', 22);
  INSERT INTO table (name, age) VALUES ('王五', 25);

正例（高效）：

  -- 批量插入，合并为一条语句
  INSERT INTO table (name, age) 
  VALUES ('张三', 20), ('李四', 22), ('王五', 25);

注意：批量插入的记录数建议控制在 1000 条以内（避免单条 SQL 过大导致锁等待），同时确保字段顺序与值列表顺序一致。

　

7. 避免使用 SELECT *，指定具体字段

问题本质：SELECT * 会查询表中所有字段，包括无需使用的字段，导致：

  • 数据传输量增大（尤其大字段如 text、blob）；

  • 无法利用覆盖索引（需回表查询数据），降低查询效率。

反例（低效）：

  SELECT * FROM table WHERE id = 1;  -- 查询所有字段，冗余数据多

正例（高效）：

  SELECT id, name, age FROM table WHERE id = 1;  -- 仅查询所需字段

建议：

  • 明确业务所需字段，按需查询；

  • 若需查询的字段均为索引列，可触发覆盖索引（Extra: Using index），无需回表，性能最优。

总结

SQL 优化的核心是“让 MySQL 少干活”——减少扫描行数、充分利用索引、降低资源消耗。以上技巧覆盖查询、插入、分组等高频场景，实际开发中需结合执行计划（EXPLAIN）验证优化效果，同时兼顾业务逻辑的合理性。

关键原则回顾：

  1. 索引列避免函数操作、隐式转换、前缀通配符；

  2. 多值匹配用 IN 替代 OR，分组查询禁用无用排序；

  3. 写入优先批量操作，查询仅取必要字段。

相关文章