SQL优化最佳实践

---

本文参考：SQL性能优化的21个小技巧

一、选择特定字段而非通配符

反例：

SELECT * FROM orders

正例：
查询SQL尽量使用select*，而是具体字段

SELECT order_id, customer_id, order_date FROM orders

原因：
选择特定字段可以减少不必要的数据传输，节省网络带宽和处理时间。

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

反例：

SELECT * FROM products WHERE category_id = 1 OR price < 100

正例：

(SELECT * FROM products WHERE category_id = 1)
UNION ALL
(SELECT * FROM products WHERE price < 100)

原因：
使用OR可能导致索引失效，尤其是在一个索引列和非索引列之间使用OR时，会使得查询性能下降。

三、优先使用数值类型而非字符串类型

反例：

SELECT * FROM users WHERE gender = 'male'

正例：
尽量使用数值代替字符串类型

SELECT * FROM users WHERE sex = 1 -- 假设1表示男性，0表示女性

• 主键(id)：primary key优先使用数值类型INT或TINYINT
• 性别(sex)：使用0表示女，1表示男

原因：
数值类型在处理速度上优于字符串类型，尤其是在比较操作中，数值类型的比较效率更高。

四、使用VARCHAR代替CHAR

反例：

email CHAR(255) DEFAULT NULL COMMENT '邮箱地址'

正例：

email VARCHAR(255) DEFAULT NULL COMMENT '邮箱地址'

原因：
VARCHAR类型根据实际内容长度存储数据，而CHAR则按声明的最大长度固定存储，导致存储空间浪费。

VARCHAR和CHAR区别：

• char 的长度是固定的，varchar 的长度是可变的
• char 的效率比 varchar 的效率稍高
• char 类型会分配固定长度的空间，varchar 类型根据实际存储的字符长度动态分配空间

应用场景

• CHAR
  适合存储固定长度的字符串，如电话号码、邮政编码等。
  如果字符串长度固定且较短，使用 CHAR 可以简化查询逻辑。
• VARCHAR
  适合存储长度可变的字符串，如姓名、地址等。
  如果字符串长度不确定或较长，使用 VARCHAR 更节省空间。

五、使用默认值代替NULL

反例：

SELECT * FROM users WHERE age IS NOT NULL

正例：

SELECT * FROM users WHERE age > 0

原因：
使用IS NOT NULL可能会导致索引失效，替换为默认值可以简化查询逻辑，同时避免索引失效带来的性能问题。

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

反例：

SELECT * FROM employees WHERE salary != 5000

正例：

SELECT * FROM employees WHERE salary < 5000 OR salary > 5000

原因：
使用!=或<>可能导致索引失效，应尽量避免，除非确实需要排除某些特定值。

七、优先使用INNER JOIN

反例：

SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id

正例：
inner join、left join、right join，优先使用inner join

SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id

原因：
在可能的情况下使用INNER JOIN，以减少返回的行数，提高查询效率，尤其是当结果集较小的时候。

八、优化GROUP BY语句

反例：

SELECT product_name, AVG(price) FROM products GROUP BY product_name HAVING product_name = 'Widget' OR product_name = 'Gadget'

正例：
提高GROUP BY语句效率

SELECT product_name, AVG(price) FROM products WHERE product_name IN ('Widget', 'Gadget') GROUP BY product_name

原因：
先过滤后分组可以减少处理的数据量，提高查询速度，减少中间结果集的大小。

九、清空表时使用TRUNCATE

反例：

DELETE FROM orders

正例：

TRUNCATE TABLE orders

原因：
truncate table在功能上与不带 where 子句的 delete 语句相同，二者均删除表中的全部行。

TRUNCATE TABLE比DELETE速度更快，占用日志资源更少，并且不记录在事务日志中。

DELETE 语句每次删除一行，会在事务日志中为所除的每行记录一项。

TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放

TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变，新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用drop table 语句。

对于由 foreign key约束引用的表，不能使用 truncate table，而应使用不带 where子句的 DELETE语句。由于 truncate table 不记录在日志中，所以它不能激活触发器。

truncate table 不能用于参与了索引视图的表

十、分批处理DELETE或UPDATE操作

反例：

DELETE FROM orders WHERE order_date < '2020-01-01'

正例：

DELETE FROM orders WHERE order_date < '2020-01-01' LIMIT 1000

原因：

• 降低写错SQL的代价
• 避免长事务
  delete 执行时，如果 order_date加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用
• 数据量大的话，容易把CPU打满
• 锁表
  一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作
• 分批处理可以减少锁表时间，提高系统稳定性，防止一次性处理大量数据导致的性能问题

十一、使用UNION ALL代替UNION

反例：

(SELECT product_name FROM products)
UNION
(SELECT supplier_name FROM suppliers)

正例：

(SELECT product_name FROM products)
UNION ALL
(SELECT supplier_name FROM suppliers)

原因：
UNION ALL不会去除重复行，UNION ALL操作只是简单的将两个结果合并后就返回，处理速度更快，减少了排序和去重的操作。

十二、批量插入数据

反例：

INSERT INTO orders (order_id, customer_id) VALUES (1, 100);
INSERT INTO orders (order_id, customer_id) VALUES (2, 101);
INSERT INTO orders (order_id, customer_id) VALUES (3, 102);

正例：

INSERT INTO orders (order_id, customer_id) VALUES 
(1, 100),
(2, 101),
(3, 102);

原因：
批量插入可以减少事务处理次数，提高效率，尤其是在插入大量数据时。

十三、限制表连接和索引的数量

正例

• 限制表连接数量，不超过5个
• 控制索引数量，不超过5个
• 阿里规范中，建议多表联查三张表以下

原因
过多的表连接和索引会影响查询性能，增加编译时间和资源消耗。

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

反例：

SELECT * FROM orders WHERE DATE_ADD(order_date, INTERVAL 7 DAY) >= NOW()

NOW() 函数返回当前的日期和时间
INTERVAL 7 DAY：INTERVAL 在SQL 中用于表示时间间隔的一种语法，表示一个持续时间为7天的时间间隔
正例：

SELECT * FROM orders WHERE order_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)

原因：
在索引列上使用内置函数可能导致索引失效，从而影响查询性能。

十五、合理使用组合索引

正例：

CREATE INDEX IDX_ORDER_DATE_CUSTOMER_ID ON orders(order_date, customer_id);
SELECT * FROM orders WHERE order_date = '2024-01-01' AND customer_id = 100 ORDER BY order_date, customer_id ASC;

原因：
使用组合索引时，应遵循最左前缀原则，以充分利用索引。

十六、复合索引最左前缀原则

正例：

• 创建复合索引

ALTER TABLE employees ADD INDEX idx_name_salary (name, salary);

• 满足复合索引的最左特效，哪怕只是部分，复合索引生效

SELECT * FROM employee WHERE NAME='约翰'

• 没有出现左边字段，则不满足最左特效，复合索引失效

SELECT * FROM employees WHERE name = '约翰' AND salary = 5000;

• 复合索引全使用，按左侧顺序出现 name，salary，索引生效

SELECT * FROM employee WHERE NAME='约翰' AND salary=5000

• 虽然违背了最左特性，但MySQL执行SQL时会进行优化，底层进行颠倒优化

SELECT * FROM employee WHERE salary=5000 AND NAME='约翰'

原因：
复合索引也称为联合索引，当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、
(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

联合索引不满足最左原则，索引一般会失效。

复合索引在查询时应按创建顺序使用，否则可能导致索引失效，影响查询效率。

十七、优化LIKE查询

反例：

select * from citys where name like '%大连' (不使用索引)
select * from citys where name like '%大连%' (不使用索引)

正例：

select * from citys where name like '大连%' (使用索引) 

原因：

• 尽量避免模糊查询：模糊查询通常性能较差，应尽量避免使用。
• 如果必须使用，不采用全模糊查询：如果必须使用模糊查询，尽量避免使用全模糊查询。
• 采用右模糊查询：如果必须使用模糊查询，尽量使用右模糊查询，如 '%大连'。
• 左模糊查询优化：左模糊查询可以通过逆序索引reverse + function index的形式进行优化。
• 全模糊查询优化：全模糊查询通常无法优化，建议使用搜索引擎或其他专门的技术。

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

正例：

EXPLAIN SELECT * FROM orders WHERE order_date = '2024-01-01'

原因：
EXPLAIN可以帮助分析查询的执行计划，找出性能瓶颈，以便进一步优化。

十九、其他优化策略

1. 表设计：为所有表和字段添加注释。
2. SQL格式：保持关键字大小写一致，使用缩进。
3. 数据备份：修改或删除重要数据前，先备份。
4. 使用EXISTS代替IN：在某些情况下，EXISTS比IN更高效。
5. 避免隐式类型转换：确保比较操作符两侧的数据类型一致。
6. 避免使用NULL：尽可能使用非空(NOT NULL)字段。
7. 伪删除设计：标记删除而非物理删除。
8. 字符集统一：使用统一的字符集如UTF8。
9. 避免无条件的COUNT(*)：避免无条件的统计查询。
10. 避免在WHERE子句中对字段进行表达式操作：保持字段干净无表达式。
11. 合理使用临时表：避免频繁创建和删除临时表。
12. 避免在索引列上有大量重复值：如性别字段不适合建立索引。
13. 减少DISTINCT的使用：减少使用DISTINCT过滤字段的数量。
14. 避免大事务操作：提高系统并发能力。
15. 使用InnoDB存储引擎：支持事务处理，行级锁机制。
16. 避免使用游标：游标操作效率较低。

---