SQL 优化指南

在数据库设计和查询优化中，有许多策略可以提升性能、减少资源消耗。以下是一些关键的优化方法和建议。

一、查询SQL尽量不要使用 SELECT *

1. 反例：SELECT * FROM users;
2. 正例：SELECT id, name, email FROM users;
3. 理由：
   • 节省资源、减少网络开销。
   • 可能用到覆盖索引，减少回表，提高查询效率。

注意：为节省时间，下面的样例字段都用 * 代替了。

二、避免在 WHERE 子句中使用 OR 来连接条件

1. 反例：

   SELECT * FROM users WHERE id = 1 OR salary = 1000;
   

2. 正例：
   • 使用 UNION ALL
   • 分开两条 SQL 写
3. 理由：
   • 使用 OR 可能会使索引失效，从而导致全表扫描。
   • 当某个条件无法使用索引时，可能会导致整个查询过程都需要全表扫描，增加查询成本。

三、尽量使用数值替代字符串类型

1. 正例：
   • 主键（id）：使用数值类型 int，tinyint 优先。
   • 性别（sex）：0 代表女，1 代表男。
2. 理由：
   • 数字比较效率更高，仅需一次比较，而字符串需要逐字符比较。
   • 数字型数据在查询和连接时性能更好，并且存储开销更小。

四、使用 VARCHAR 代替 CHAR

1. 反例：CHAR(10)
2. 正例：VARCHAR(10)
3. 理由：
   • VARCHAR 变长字段按实际长度存储，节省存储空间。
   • CHAR 按声明大小存储，不足补空格，查询效率可能较低。

五、CHAR 与 VARCHAR2 的区别

1. 区别：
   • CHAR 的长度固定，VARCHAR2 的长度可变。
   • CHAR 比 VARCHAR2 效率稍高，但会占用更多空间。
2. 使用建议：
   • CHAR 适合长度固定且不常修改的字段。
   • VARCHAR2 更适合长度可变的字段，但可能引发“行迁移”问题，导致额外的 I/O。

六、WHERE 中使用默认值代替 NULL

1. 反例：SELECT * FROM users WHERE status IS NULL;
2. 正例：SELECT * FROM users WHERE status = 0;
3. 理由：
   • 避免使用 IS NULL 或 IS NOT NULL 导致索引失效。
   • 使用默认值可以使索引更有效，且语义更加清晰。

七、避免在 WHERE 子句中使用 != 或 <> 操作符

1. 反例：SELECT * FROM users WHERE status != 1;
2. 理由：
   • 使用 != 和 <> 可能导致索引失效，从而引发全表扫描。
   • 如果必须使用，请考虑业务优先。

八、优先使用 INNER JOIN 而非 LEFT JOIN 或 RIGHT JOIN

1. 理由：
   • INNER JOIN 只返回完全匹配的结果，通常性能更好。
   • LEFT JOIN 和 RIGHT JOIN 会返回一侧表的所有行，可能增加返回结果的行数，降低查询性能。

九、提高 GROUP BY 语句的效率

1. 反例：先分组，再过滤
2. 正例：先过滤，后分组
3. 理由：
   • 先过滤掉不必要的记录，减少 GROUP BY 处理的数据量，提高效率。

十、清空表时优先使用 TRUNCATE

1. 理由：
   • TRUNCATE 比 DELETE 快，且消耗的系统和事务日志资源少。
   • TRUNCATE 释放存储页，记录更少的日志信息。

十一、操作 DELETE 或 UPDATE 语句时加 LIMIT

1. 理由：
   • 降低误操作的风险，减少删除或更新数据的影响范围。
   • 避免长事务，提高系统并发能力。

十二、UNION 操作符优化

1. 建议：
   • 使用 UNION ALL 代替 UNION，因为 UNION 会去重，导致额外的排序和计算开销。

十三、批量插入性能提升

1. 建议：
   • 使用批量提交来减少事务开销，提升插入性能。

十四、表连接与索引数量控制

1. 建议：
   • 表连接数控制在 5 个以内，避免复杂查询。
   • 索引数量控制在 5 个以内，平衡查询效率与插入/更新效率。

十五、避免在索引列上使用内置函数

1. 反例：SELECT * FROM users WHERE YEAR(created_at) = 2023;
2. 理由：
   • 使用内置函数会导致索引失效，增加查询成本。

十六、组合索引与最左特性

1. 建议：
   • 创建符合最左特性的组合索引，优化查询性能。

十七、优化 LIKE 语句

1. 反例：SELECT * FROM users WHERE name LIKE '%John%';
2. 正例：SELECT * FROM users WHERE name LIKE 'John%';
3. 理由：
   • 右模糊查询（LIKE 'John%'）可以使用索引，而左模糊查询（LIKE '%John'）无法使用索引。

十八、使用 EXPLAIN 分析 SQL 执行计划

1. 理由：
   • 通过 EXPLAIN 查看 SQL 的执行计划，可以识别性能瓶颈，进行优化。

十九、其他优化建议

1. 设计表时添加注释：提高表和字段的可读性。
2. SQL 书写格式统一：提高代码的可维护性。
3. 重要数据操作前备份：防止数据丢失。
4. 使用 EXISTS 代替 IN：提高查询性能。
5. 避免 WHERE 子句中的隐式类型转换：避免索引失效。
6. 避免在 WHERE 子句中使用表达式：确保索引有效性。
7. 减少大事务操作：提高系统并发能力。
8. 使用 InnoDB 存储引擎：支持事务，性能优良。
9. 避免使用游标：游标效率低，应尽量改写为批量操作。

通过遵循这些最佳实践，可以有效提升 SQL 查询性能，减少资源消耗，确保系统的高效稳定运行。