SQL优化

当我们在写 SQL 语句时，有许多优化方法可以提高查询的性能和效率。下面便列出了一些主要的优化技巧，以及对应的简单SQL语句示例：

只读取必要的列

不要获取数据库中所有的列，而仅仅获取你需要的列。这可以减少数据传输量，提高处理速度。

-- 例子
# 不推荐
SELECT * FROM TABLE_NAME;

# 推荐
SELECT id,uname,age FROM TABLE_NAME;

尽可能少的使用LIKE

LIKE 操作符是一种开销较大的操作，所以尽量避免使用它，或者至少确保它不以通配符开始。

-- 例子
# 不推荐
SELECT uname FROM TABLE_ONE WHERE uname LIKE '%Za%';

# 推荐
SELECT uname FROM TABLE_ONE WHERE uname LIKE 'Za%'

合理使用 LIMIT

如果仅仅需要一部分结果，比如 ORDER BY 时，使用 LIMIT 可以极大缩短查询时间。

-- 例子
# 查询TOP10
SELECT * FROM TABLE_ONE LIMIT 10;

避免 NULL 值

尽可能让字段保持非 NULL ，因为处理 NULL 值需要消耗更多的 CPU 和 I/O 资源。可以设计表结构让字段默认不为 NULL 。设置为别的值，比如 -1 等。

# 不推荐
SELECT * FROM TABLE_ONE WHERE money IS NULL;

# 推荐
SELECT * FROM TABLE_ONE WHERE money = '';

尽可能减少 WHERE 中数据类型的隐式转换

如果在条件查询中，数据类型存在隐式转换，那么数据库可能无法正确使用索引。

-- 例子
# 在 id 为整数类型的情况下
# 不推荐
SELECT * FROM TABLE_ONE WHERE id = '123';

# 推荐
SELECT * FROM TABLE_ONE WHERE id = 123;

尽量避免在 WHERE 子句中对字段进行函数操作

这样会导致数据库无法使用索引，从而进行全表扫描。

# 不推荐
SELECT * FROM TABLE_ONE WHERE YEAR(one_date) > 1970;

# 推荐
SELECT * FROM TABLE_ONE WHERE one_date > '1970-01-01';

使用 UNION ALL 代替 UNION

使用 UNION 时，数据库会进行额外的步骤去去除重复的记录，消耗性能。如果明确知道，两个查询返回的记录集合没有交际，那么使用 UNION ALL 会更加高效。

# 不推荐
SELECT * FROM TABLE_ONE
UNION
SELECT * FROM TABLE_TWO;

# 推荐
SELECT * FROM TABLE_ONE
UNION ALL
SELECT * FROM TABLE_TWO;

使用批量插入(bulk insert)

对于大量数据的插入，尽可能使用批量插入，而不是一次插入单行，这将显著提高性能。

# 不推荐
INSERT INTO TABLE_ONE (uname, age) VALUES ('Zack', 25);
INSERT INTO TABLE_ONE (uname, age) VALUES ('Jane', 27);

# 推荐
INSERT INTO TABLE_ONE (uname, age) VALUES ('Zack', 25), ('Jane', 27);

使用 EXISTS 关键字代替 IN 关键字

在某些数据库 (如MySQL) 中，当查询大数据集时，EXISTS 通常比 IN 更快。如果查询生成的结果集非常大，那么 IN 关键字会使查询变得非常低效。

# 不推荐
SELECT uid
FROM TABLE_ONE
WHERE uid IN (SELECT uid FROM TABLE_TWO);

# 推荐
SELECT uid
FROM TABLE_ONE AS O
WHERE EXISTS (SELECT 1 FROM TABLE_TWO AS T WHERE O.uid = T.uid);

-- EXISTS是一个条件表达式，用于检查括号内的子查询是否返回至少一行数据。如果子查询返回至少一行数据，则EXISTS表达式的结果为真（TRUE），否则为假（FALSE）。

使用 EXPLAIN 关键字

EXPLAIN 关键字可以告诉你 DBMS 是如何处理 SQL 语句的 (执行计划)。通过这种方式，可以了解当前查询将如何被优化和执行，包括它将访问哪些表、使用哪些索引（如果有的话）、连接的顺序、以及预期的查询成本等。

# 分析执行计划
EXPLAIN SELECT * FROM TABLE_ONE WHERE uid = 10;

尽量避免使用 HAVING 子句

只有当必须在聚合函数上应用条件时，才使用 HAVING 。HAVING 子句用于过滤聚合结果，如果可能，尝试通过调整 WHERE 子句条件来实现相同的结果。

# 不推荐
SELECT uid,COUNT(*)
FROM TABLE_ONE
GROUP BY uid
HAVING COUNT(*) > 5;

# 推荐
SELECT uid
FROM
	(SELECT uid,COUNT(*) AS CNT
     FROM TABLE_ONE
     GROUP BY uid) AS O
WHERE O.CNT > 5;

使用 Join 代替子查询

通常，使用JOIN代替子查询 (sub-query) 可以改善查询性能。

-- 例子
# 不推荐
SELECT a.uname
FROM TABLE_ONE AS a
WHERE a.id IN 
	(SELECT id FROM TABLE_TWO WHERE uid = 1000);
	
# 推荐
SELECT a.uname
FROM TABLE_ONE AS a
JOIN TABLE_TWO AS b
ON a.id = b.id
WHERE b.uid = 1000;

-- 需要注意的是，当子查询是独立的（即不依赖于外部查询的列）且仅返回少量数据时，其性能可能并不比 JOIN 差，甚至在某些数据库系统中可能更优。

在适当的地方使用存储过程

创建存储过程通常使用SQL的CREATE PROCEDURE语句。以下是一个简单的示例：

CREATE PROCEDURE GetEmployeeById  
    @EmployeeID INT  
AS  
BEGIN  
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;  
END;

BEGIN 和 END 关键字被用作界定一个代码块的开始和结束。这些代码块通常包含了一系列的SQL语句，这些语句需要被当作一个整体来执行。

在这个示例中，GetEmployeeById是一个存储过程，它接受一个名为@EmployeeID的输入参数，并返回与指定EmployeeID匹配的员工记录。

调用存储过程通常使用SQL的EXEC或EXECUTE语句。以下是一个调用上面创建的存储过程的示例：

-- 调用
EXEC GetEmployeeById @EmployeeID = 1;

# 注意事项
-- 存储过程可能会增加数据库的复杂性，因为它们引入了额外的抽象层。
-- 在某些情况下，存储过程可能会成为性能瓶颈，特别是当它们包含大量逻辑或处理大量数据时。
-- 数据库管理员需要仔细管理存储过程的权限，以确保只有授权用户才能执行它们。

合理使用 DISTINCT

使用 DISTINCT 关键字可以删除查询结果中的重复行，不过在进行删除操作时，优化器需要对结果进行排序，这会带来巨大的性能开销。尽量在必要的时候使用。

GROUP BY 优化和 ORDER BY 优化

高基数维度在前，低基数维度在后。