【MySQL】MySQL优化

MySQL优化

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

一般人想不到的优化：

GroupBy优化
高基数维度在前，低基数维度在后。

OrderBy优化
高基数维度在前，低基数维度在后。

GroupingSets代替Groupby
GroupingSets可以减少表扫描次数，提升性能。

1. 只读取必要的列

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

例子：

# 不推荐
SELECT * FROM employees;

 
# 推荐
SELECT first_name, last_name FROM employees;

2. 使用Join代替子查询

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

例子：

# 不推荐
SELECT a.last_name 
FROM employees a 
WHERE a.department_id IN (SELECT department_id FROM departments WHERE location_id = 1000);

# 推荐
SELECT a.last_name 
FROM employees a
JOIN departments d ON a.department_id = d.department_id
WHERE d.location_id = 1000;

3. 尽可能少的使用LIKE

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

例子：

# 不推荐
SELECT first_name FROM employees WHERE first_name LIKE '%ji%';

# 推荐
SELECT first_name FROM employees WHERE first_name LIKE 'ji%';

4. 合理使用LIMIT

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

例子：

# 取出前十个员工数据
SELECT * FROM employees LIMIT 10;

5. 避免NUll值

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

# 不推荐
SELECT * FROM employees WHERE phone_number IS NULL;

# 推荐
SELECT * FROM employees WHERE phone_number = '';

6. 尽可能减少where中数据类型的隐式转换

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

例子：

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

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

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

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

例子：

# 不推荐
SELECT * FROM employees WHERE YEAR(birth_date) > 1970;

# 推荐
SELECT * FROM employees WHERE birth_date > '1970-01-01';

8. 使用Union ALL替代Union

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

例子：

# 不推荐
SELECT city FROM table_a
UNION
SELECT city FROM table_b;

# 推荐
SELECT city FROM table_a
UNION ALL
SELECT city FROM table_b;

9. 使用批量插入 (bulk insert)

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

例子：

# 不推荐
INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');
INSERT INTO employees (first_name, last_name) VALUES ('Jane', 'Doe');

# 推荐
INSERT INTO employees (first_name, last_name) 
VALUES ('John', 'Doe'), 
('Jane', 'Doe');

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

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

例子：

# 不推荐
SELECT employee_id 
FROM employees 
WHERE employee_id IN (SELECT employee_id FROM orders);

# 推荐
SELECT employee_id 
FROM employees e
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.employee_id);

11. 使用 EXPLAIN 关键字

EXPLAIN关键字可以告诉你 DBMS 是如何处理 SQL 语句的。通过这种方式，你可能会发现你认为应该被利用的索引实际上并没有被用到，或者查询中使用了全表扫描等低效操作。

# 分析查询的执行计划
EXPLAIN SELECT * FROM employees WHERE department_id = 10;

12. 尽量避免使用HAVING子句

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

例子：

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

# 推荐
SELECT department_id
FROM   (SELECT department_id,
COUNT(*) AS cnt 
FROM employees 
GROUP BY department_id ) as t
WHERE t.cnt > 5;

13. 避免使用笛卡尔积（CROSS JOIN）

CROSS JOIN可能会生成很多行的结果集，可能造成SQL查询运行的效率低下。

# 不推荐
SELECT * FROM employees CROSS JOIN departments;

# 推荐
SELECT * FROM employees, departments WHERE employees.department_id = departments.department_id;

14. 使用索引

在大型数据库中，合理使用索引可以提高查询速度。需要注意，索引并不是越多越好，否则可能在插入和更新操作时拖慢速度。

例子：

# 建立索引
CREATE INDEX idx_employees_last_name
ON employees (last_name);

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

Stored Procedures能够提高应用的性能，通过预编译和重复使用执行计划。而且它们还可以减少网络使用，在数据库和应用之间只需要传递参数就可以了。

CREATE PROCEDURE GetEmployeeCountByDepartment @DepartmentId int
AS
SELECT COUNT(*) FROM Employees WHERE DepartmentId = @DepartmentId;

16. 分解复杂查询

复杂的查询可能会要求数据库进行大量的计算。如果可以，最好将这些复杂查询分解成几个简单的查询。

例子：

# 不推荐
SELECT a.*, b.* FROM a, b WHERE a.id = b.id AND a.id = ... ;

# 推荐
SELECT * FROM a WHERE a.id = ... ;
SELECT * FROM b WHERE b.id = ... ;

17. 合理使用Distinct

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

例子：

# 不推荐
SELECT DISTINCT first_name, last_name FROM employees;

# 若可能，推荐
SELECT first_name, last_name FROM employees;

18. By的优化

GroupBy优化
高基数维度在前，低基数维度在后。

OrderBy优化
高基数维度在前，低基数维度在后。

GroupingSets代替Groupby
GroupingSets可以减少表扫描次数，提升性能。