sql语句优化的方法案例

1. 使用合适的索引
   案例： 如果你有一个查询，涉及大量数据筛选条件，可以通过创建索引来提高查询效率。例如：

CREATE INDEX idx_name ON employees (last_name, first_name);

这样可以加速基于last_name和first_name字段的查询。
2. **避免使用SELECT ***
案例： 只查询需要的列，避免使用SELECT *，减少返回数据量。

SELECT first_name, last_name FROM employees WHERE employee_id = 101;
比 
SELECT * FROM employees WHERE employee_id = 101; 更高效。

3. 使用JOIN代替子查询
   案例： 子查询会增加查询的复杂度和执行时间，使用JOIN代替子查询往往能提高效率。

SELECT e.first_name, e.last_name 
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

4. 避免在WHERE子句中使用函数
   案例： 在WHERE子句中使用函数会导致数据库无法使用索引。应避免类似的情况：

-- 不推荐
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

-- 推荐
SELECT * FROM employees WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

5. 适当使用LIMIT
   案例： 如果只需要查询一定数量的数据，使用LIMIT可以减少不必要的计算。

SELECT * FROM employees LIMIT 100;

6. 避免使用OR，改为IN
   案例： OR通常比IN效率低，改用IN可以优化查询。

-- 不推荐
SELECT * FROM employees WHERE department_id = 1 OR department_id = 2;
-- 推荐
SELECT * FROM employees WHERE department_id IN (1, 2);

7. 分区表
   案例： 对于大量数据表，可以使用分区表将数据按某些规则分开存储，从而提高查询性能。

CREATE TABLE employees (
    employee_id INT,
    department_id INT,
    hire_date DATE
)
PARTITION BY RANGE (YEAR(hire_date)) (
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
);

8. 优化JOIN条件
   案例： 确保JOIN时条件的顺序和效率。尽量让小表在前面，以减少内存占用。

SELECT a.name, b.salary 
FROM employees a
JOIN salaries b ON a.employee_id = b.employee_id;

9. 避免使用DISTINCT
   案例： DISTINCT通常会增加额外的计算和排序，只有在确实需要去重时再使用。

-- 不推荐
SELECT DISTINCT department_id FROM employees;
-- 推荐
SELECT department_id FROM employees;

10. 优化GROUP BY和ORDER BY
    案例： GROUP BY和ORDER BY可以增加查询时间，尤其是数据量很大的时候。尝试对相关列建立索引。

CREATE INDEX idx_department ON employees(department_id);
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id ORDER BY COUNT(*) DESC;

11. 合理使用事务
    案例： 在执行多个INSERT或UPDATE时，可以通过开启一个事务来减少磁盘写入的次数。

BEGIN TRANSACTION;
INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');
INSERT INTO employees (first_name, last_name) VALUES ('Jane', 'Smith');
COMMIT;

12. 使用缓存
    案例： 数据库层面可以启用查询缓存来缓存常见的查询结果。
    – 启用缓存的具体方法依赖于数据库类型

SET GLOBAL query_cache_size = 1000000;

13. 使用批量操作
    案例： 在插入大量数据时，批量操作会比逐条插入要快得多。

INSERT INTO employees (first_name, last_name) VALUES
('John', 'Doe'),
('Jane', 'Smith'),
('Robert', 'Johnson');

14. 避免大量的更新操作
    案例： 尽量避免频繁的大量UPDATE操作，因为它们会影响性能。合并多个UPDATE语句，或者使用CASE语句。

UPDATE employees
SET department_id = CASE 
    WHEN department_id = 1 THEN 2
    WHEN department_id = 3 THEN 4
    ELSE department_id
END;

15. 分析查询执行计划
    案例： 使用EXPLAIN分析查询执行计划，找出性能瓶颈。

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

16. 合理使用临时表
    案例： 临时表有助于分阶段处理复杂查询，避免复杂查询一次性执行过多计算。

CREATE TEMPORARY TABLE temp_table AS
SELECT * FROM employees WHERE department_id = 10;
SELECT * FROM temp_table WHERE hire_date > '2020-01-01';

17. 优化NULL值查询
    案例： 对于包含NULL值的字段，避免直接在WHERE子句中用=或者!=比较。

-- 不推荐
SELECT * FROM employees WHERE department_id IS NULL;
-- 推荐
SELECT * FROM employees WHERE department_id IS NOT NULL;

18. 避免在索引列上使用通配符
    案例： 在索引列上使用通配符（%）会导致数据库无法使用索引。

-- 不推荐
SELECT * FROM employees WHERE last_name LIKE '%Smith';
-- 推荐
SELECT * FROM employees WHERE last_name LIKE 'Smith%';

19. 避免过多的连接
    案例： 在复杂查询中，避免连接过多的表，因为每增加一个表，查询的复杂度都会大大增加。

-- 不推荐
SELECT a.*, b.*, c.*, d.* FROM employees a 
JOIN departments b ON a.department_id = b.department_id
JOIN projects c ON a.project_id = c.project_id
JOIN salaries d ON a.salary_id = d.salary_id;
-- 推荐：减少连接的表
SELECT a.*, b.* FROM employees a 
JOIN departments b ON a.department_id = b.department_id;

20. 考虑查询时的并行处理
    案例： 在一些支持并行查询的数据库中，可以配置查询并行处理来加速长时间运行的查询。
    – 根据数据库设置并行查询

SELECT /*+ PARALLEL(employees, 4) */ * FROM employees;

21. 避免使用子查询，改用JOIN
    案例： 子查询可能导致多次扫描表，而JOIN可以提高查询效率。

-- 不推荐
SELECT first_name, last_name FROM employees 
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

-- 推荐
SELECT e.first_name, e.last_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

22. 使用EXISTS代替IN
    案例： EXISTS通常比IN性能更好，尤其是在处理大数据量时。

-- 不推荐
SELECT first_name, last_name FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');
-- 推荐
SELECT first_name, last_name FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE e.department_id = d.department_id AND d.department_name = 'Sales');

23. 合理使用表连接的顺序
    案例： 对于多个JOIN的查询，按较小的表先连接，减少内存消耗。

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN salaries s ON e.salary_id = s.salary_id
ORDER BY e.last_name;

24. 避免过多的UNION操作
    案例： UNION会自动去重，可能会增加计算量。如果不需要去重，使用UNION ALL。

-- 不推荐
SELECT first_name FROM employees WHERE department_id = 10
UNION
SELECT first_name FROM employees WHERE department_id = 20;

-- 推荐
SELECT first_name FROM employees WHERE department_id = 10
UNION ALL
SELECT first_name FROM employees WHERE department_id = 20;

25. 优化大数据量查询的分页
    案例： 使用LIMIT和OFFSET分页时，查询性能会随着数据量的增大而下降，使用WHERE来避免大OFFSET。

-- 不推荐
SELECT * FROM employees ORDER BY employee_id LIMIT 100 OFFSET 1000;

-- 推荐
SELECT * FROM employees WHERE employee_id > 1000 ORDER BY employee_id LIMIT 100;

26. 定期清理数据库
    案例： 定期清理历史数据，尤其是对于大表，减少不必要的记录存储和查询负担。

DELETE FROM employees WHERE hire_date < '2000-01-01';

27. 避免大批量的INSERT时的重复锁定
    案例： 在大批量的INSERT操作中，避免一次性插入大量数据。可以使用分批插入以避免锁定表过长时间。

-- 不推荐
INSERT INTO employees (first_name, last_name) VALUES (...), (...), ..., (...);  -- 数量过多时

-- 推荐
INSERT INTO employees (first_name, last_name) VALUES (...);
INSERT INTO employees (first_name, last_name) VALUES (...);

28. 使用CASE表达式减少查询次数
    案例： 用CASE表达式合并多个查询条件，减少查询次数和中间结果集的生成。

SELECT 
    employee_id,
    first_name,
    last_name,
    CASE 
        WHEN department_id = 1 THEN 'Sales'
        WHEN department_id = 2 THEN 'Engineering'
        ELSE 'Other'
    END AS department_name
FROM employees;

29. 避免使用过多的HAVING
    案例： HAVING一般用于过滤聚合结果，但它通常会导致性能下降。优先使用WHERE来过滤数据。

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

-- 推荐
SELECT department_id, COUNT(*) 
FROM employees 
WHERE department_id IN (SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 10)
GROUP BY department_id;

30. 使用合适的数据类型
    案例： 数据类型应尽量小，以减少存储空间和提高处理效率。例如，使用INT而不是BIGINT，如果值不需要非常大的范围。

-- 不推荐
CREATE TABLE employees (
    employee_id BIGINT,
    department_id BIGINT,
    salary DECIMAL(15, 2)
);

-- 推荐
CREATE TABLE employees (
    employee_id INT,
    department_id INT,
    salary DECIMAL(10, 2)
);

31. 避免使用LIKE模糊查询时的开头通配符
    案例： 在LIKE查询中使用开头的%会导致全表扫描，避免这种写法。

-- 不推荐
SELECT * FROM employees WHERE first_name LIKE '%John';

-- 推荐
SELECT * FROM employees WHERE first_name = 'John';

32. 使用合适的批处理存储过程
    案例： 对于需要频繁执行的批量查询或更新，封装成存储过程以提高性能。

DELIMITER $$
CREATE PROCEDURE UpdateEmployeeSalary(IN emp_id INT, IN new_salary DECIMAL)
BEGIN
    UPDATE employees SET salary = new_salary WHERE employee_id = emp_id;
END $$
DELIMITER ;

33. 监控数据库的慢查询日志
    案例： 启用慢查询日志，定期分析查询的执行时间并针对性优化。

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 设置超过2秒的查询为慢查询

34. 分解复杂的查询
    案例： 将一个复杂的查询分解成多个简单的查询，通过临时表存储中间结果。

CREATE TEMPORARY TABLE temp AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

SELECT e.first_name, e.last_name, t.avg_salary
FROM employees e
JOIN temp t ON e.department_id = t.department_id;

35. 避免频繁的表结构变更
    案例： 表结构的频繁更改会导致性能下降。尽量减少对表结构的修改。

-- 不推荐频繁修改表结构
ALTER TABLE employees ADD COLUMN address VARCHAR(255);
-- 推荐在设计时优化表结构

36. 避免在WHERE子句中使用IS NULL与IS NOT NULL组合
    案例： IS NULL和IS NOT NULL条件可能会导致索引失效，最好避免它们出现在同一个查询中，特别是当涉及到索引时。

-- 不推荐
SELECT * FROM employees WHERE department_id IS NULL OR department_id IS NOT NULL;

-- 推荐
SELECT * FROM employees WHERE department_id IS NOT NULL;

37. 避免重复查询同一数据
    案例： 对同一数据执行多次查询，增加了不必要的开销。可以通过使用临时表或WITH语句来缓存数据，从而减少重复查询。

-- 不推荐
SELECT * FROM employees WHERE department_id = 1;
SELECT * FROM employees WHERE department_id = 1;

-- 推荐
WITH temp AS (SELECT * FROM employees WHERE department_id = 1)
SELECT * FROM temp;

38. 避免使用不必要的LEFT JOIN
    案例： LEFT JOIN会返回所有匹配的数据以及左表中不匹配的行，因此如果你只关心有匹配的数据，使用INNER JOIN会更高效。

-- 不推荐
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 推荐
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

39. 避免多次查询相同数据
    案例： 对相同数据执行多个查询时，避免重复的访问，可以考虑使用缓存或者一次性查询所有相关数据。

-- 不推荐
SELECT * FROM employees WHERE department_id = 1;
SELECT * FROM employees WHERE department_id = 2;

-- 推荐
SELECT * FROM employees WHERE department_id IN (1, 2);

40. 优化数据库连接池
    案例： 通过合理配置数据库连接池，避免每次查询时都创建和销毁数据库连接。这样能大幅减少连接创建的开销，尤其是在高并发的情况下。
    – 这通常涉及到数据库连接池的配置，在应用层面优化
    – 例如在Spring中设置合理的连接池大小

spring.datasource.hikari.maximum-pool-size=20;