Mysql高级篇（中）—— SQL优化

一、SQL优化的计划或思路

当生产环境中出现SQL语句执行耗时的问题时，为了确保系统的稳定性和性能，此时就需要我们快速分析和解决问题，首先是确认问题，通过查看慢查询日志来确认是哪些SQL语句导致的性能问题；其次通过explain 获取问题SQL的执行计划、通过show profile 进一步查询SQL 的执行细节和生命周期；最后深入分析与优化，数据库参数调优等措施来达到优化SQL的目的。具体思路参考下述内容：

二、关联查询优化

在优化关联查询时，只有在被驱动表上建立索引才有效

• left join 时，左侧的为驱动表，右侧为被驱动表
• EXPLAIN：是判断驱动表和被驱动表的最有效方法。通常，EXPLAIN 输出中第一行的表为驱动表，后面的为被驱动表。
• 表大小、索引、WHERE 子句 ：影响优化器的选择。更小的表和带有索引的表更有可能成为驱动表。
• STRAIGHT_JOIN：可以强制指定驱动表和被驱动表的顺序。
• 子查询尽量不要放在被驱动表，有可能使用不到索引
• left join时，尽量让实体表作为被驱动表
  
  

三、子查询优化

在范围判断时，尽量不要使用 not in 和 not exists，使用 left join on xxx is null 代替

四、exists 和 not exists

1、exists 介绍

EXISTS是SQL中用于检查 子查询结果是否返回数据的关键字。它常用于提升查询性能，尤其是在需要判断某个条件是否满足而返回实际数据时。


EXISTS的值为布尔类型，当子查询时返回结果集时，EXISTS返回TRUE，否则返回FALSE；可以理解为：

• 将主查询的数据放到子查询中作为条件验证更具验证结果（true或false）来决定主查询的数据是否保留。
  
  

子查询结果较大时，EXISTS可以有更好的性能

EXISTS基本用法示例
假设我们有两个表：customers和orders，它们的结构如下：

-- customers表：
customer_id | customer_name
---------------------------
1           | John
2           | Mary
3           | Alice


-- orders表：
order_id | customer_id | order_date
------------------------------------
1001     | 1           | 2023-09-01
1002     | 2           | 2023-09-05
1003     | 1           | 2023-09-10

1.使用EXISTS查询是否存在订单
我们想查询是否有下过订单的客户，而不关心订单的具体信息。可以通过以下方式使用EXISTS：

SELECT customer_name 
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

• 内部子查询SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id用于判断orders表中是否存在与当前customers表中某个customer_id对应的订单。
• 如果存在匹配的记录，EXISTS返回TRUE，那么该客户就会出现在最终结果集中。
• SELECT 1在EXISTS中消耗表示返回任何实际数据，SQL引擎只关心是否有记录存在。

2.EXISTS比较IN： 相同的查询，如果使用IN，则查询如下：

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id 
    FROM orders
);

IN和EXISTS区别：

• IN会首先执行子查询，返回所有满足条件的customer_id，然后在外层查询中逐一进行匹配。这在子查询返回大量数据时，可能会造成性能问题。
• EXISTS对于每一行外层查询，它会在层子查询中逐条检查是否存在满足条件的记录，一旦找到匹配的数据则停止检查，在随后的某些场景下性能会更优。

3.使用EXISTS过滤数据
例如，我们希望查询下一个订单的客户，但只关心订单日期在 2023 年 9 月 1 日之后的客户：

SELECT customer_name 
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
      AND o.order_date > '2023-09-01'
);

• 在此示例中，EXISTS不仅在检查是否存在与客户相关的订单，还通过添加日期条件进一步筛选匹配的订单。这样可以确保我们查询到的客户都是在特定日期之后下订单的客户。

4.使用NOT EXISTS
有时我们需要查询不存在关联数据的情况，那么就可以使用NOT EXISTS。假设我们要找出所有未下过订单的客户：

SELECT customer_name 
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

• NOT EXISTS用于查找对应的没有订单记录的客户。它检查orders表中是否有与每个customers表中的customer_id匹配的记录。如果没有，客户就会被返回。

5.EXISTS和JOIN的区别
对于某些查询，JOIN与EXISTS结果是相同的，但它们的执行方式不同。

-- JOIN查询

SELECT c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

-- EXISTS查询：
SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
);

• JOIN返回所有匹配的行，如果一个客户有多个订单，客户信息会重复返回多个，可能导致数据丢失。
• EXISTS则仅检查是否存在匹配记录，它不会返回重复数据，因此不需要具体关联数据时，EXISTS更为合适。
• 在数据量增大且表关联关系复杂时，EXISTS可能表现出更好的性能。

6.性能考虑
EXISTS的性能通常是领先的IN，特别是当子查询的结果集非常大时。因为：

• EXISTS通常会停止扫描，一旦找到满足条件的记录即停止，而IN会首先执行子查询并返回所有结果，然后与外层查询匹配。
• 数据库引擎在处理EXISTS时，通常可以更有效地优化查询，尤其是在子查询和外层查询相关联时

总结：

• EXISTS是在子查询中非常有用的工具，适用于检查数据是否存在而不是返回具体值。
• 它在很多场景下，尤其是数据量大的时候，性能出色IN，因为它能够提前终止查询。
• EXISTS虽然与JOIN的执行结果相同，但首先更适合需要检查是否存在数据而不关心返回值的场景。

2、exists 和 not exists

• EXISTS：当子查询有至少一行数据时返回，返回TRUE，用于判断某些条是否存在。
• NOT EXISTS：当子查询没有数据返回时，返回TRUE，用于判断某些记录是否不存在。

代码示例分析：假设我们有两个表：employees和departments。

-- employees 表：
employee_id | employee_name | department_id
-------------------------------------------
1           | Alice         | 10
2           | Bob           | 20
3           | Carol         | 30
4           | David         | 20



-- departments表：
department_id | department_name
--------------------------------
10            | HR
20            | IT

1. 使用EXISTS
需求： 查询在部门表departments中存在关联记录的员工。

SELECT employee_name
FROM employees e
WHERE EXISTS (
    SELECT 1 
    FROM departments d
    WHERE d.department_id = e.department_id
);

• 子查询SELECT 1 FROM departments d WHERE d.department_id = e.department_id会检查每个表中employee_id是否departments存在关联的部门。
• 对于employees表中的每一行，数据库会执行一次子查询。只要有匹配的department_id，EXISTS就返回TRUE。
• 结果会返回Alice、Bob和David，因为他们的department_id在departments表中存在。

2.使用NOT EXISTS
需求： 查询在部门表departments中没有关联记录的员工（即无部门的员工）。

SELECT employee_name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 
    FROM departments d
    WHERE d.department_id = e.department_id
);

• 这NOT EXISTS反过来检查员工的department_id是否 没有 在departments表中匹配。
• 当子查询SELECT 1 FROM departments d WHERE d.department_id = e.department_id没有返回结果时，NOT EXISTS返回TRUE。
• 结果会返回Carol，因为她的department_id在departments表中不存在。

EXISTS 与 NOT EXISTS 的区别和应用场景

-- 使用IN查询部门为IT员工：
SELECT employee_name 
FROM employees 
WHERE department_id IN 
(SELECT department_id 
   FROM departments 
  WHERE department_name = 'IT');


-- 使用EXISTS替代方案IN：
SELECT employee_name 
FROM employees e
WHERE EXISTS (
    SELECT 1 
    FROM departments d 
    WHERE d.department_id = e.department_id 
    AND d.department_name = 'IT'
);

在一些完成下的情况下，EXISTS可能比IN效率更高，因为EXISTS一旦找到匹配数据就会终止子查询，而IN需要先整个子查询。

总结

• EXISTS：检查子查询是否 返回 数据，适用于想确认某个条件是否存在的情况。返回适合快速判断某条记录是否与另一条记录相关联，能够有效避免数据。
• NOT EXISTS：用于检查子查询是否 不返回 数据，适合查找主表中没有匹配关联记录的情况。多用于查找孤立的数据。
  
  在实际开发中，可以根据查询的复杂程度、数据规模和具体需求，合理选择使用EXISTS或NOT EXISTS，以达到最佳的性能和精度。

五、单路排序和多路排序（了解）

单路排序和多路排序 是数据库在执行排序操作时，为应对不同数据规模和内存限制而采取的两种策略。


MySQL 默认使用单路排序（One-Way Sorting），但这取决于排序的数据量和 sort_buffer_size 的配置。

六、排序分组优化

1、order by

（1）避免临时排序,使用索引排序

MySQL在无法利用索引排序时，会使用临时表进行排序（Using filesort）。应尽量避免这种情况

（2）减少数据扫描

• 无过滤，不索引。where，limt 都相当于一种过滤条件，所以才能使用上索引；因为 没有过滤条件：全表扫描，索引无用

（3）调整 MySQL 参数配置

增大 sort_buffer_size和max_length_for_sort_data 参数配置

sort_buffer_size

sort_buffer_size 参数 指定了排序操作可使用的内存缓冲区的大小。 当数据库需要对数据进行排序时，会使用这个缓冲区。如果缓冲区不够大，MySQL 可能需要在磁盘上进行排序操作，这会显著降低性能。

• 默认值： 一般是256KB（视MySQL版本和配置而定）。
  
  

当 sort_buffer_size 不够大时

• 如果排序操作的数据量超出了 sort_buffer_size，MySQL 会将数据写入磁盘上的临时文件进行排序，这会显著降低查询性能，特别是在大量数据排序的情况下。
• 增加 sort_buffer_size 可以减少磁盘 I/O 操作，提高排序速度，但设置得过大可能导致服务器内存资源耗尽，影响整体性能。
  
  

注意事项

• 适度调整： 增大 sort_buffer_size 可以提高排序性能，但要避免设置得过大，以免导致内存不足。通常，可以在测试环境中调整这个参数，观察对查询性能的影响，找到合适的大小。
• 作用范围： sort_buffer_size 是会话级别的参数，可以为特定会话设置不同的大小，而不影响其他会话。你也可以在全局级别设置它，但是要小心，如果全局设置不当（例如，将 sort_buffer_size 设置得过大），可能会导致服务器内存消耗增加，影响到整个数据库服务器的性能
  

max_length_for_sort_data

max_length_for_sort_data 参数作用是 限制数据进行排序时所能使用的最大数据长度，以避免对非常长的数据进行排序时带来的性能问题。 如：当排序的列包含非常大的数据（如文本、BLOB 等）时，使用 max_length_for_sort_data 可以限制排序数据的长度，避免性能下降。

> 假设我们有一个 documents 表，结构如下：
>  doc_id (主键) 
>  title 
>  content (文本类型，可能非常长)

-----------------------------------------------
SELECT * FROM documents ORDER BY content;
-----------------------------------------------
【解释】：这个查询会按照 content 列的内容进行排序，并返回所有文档。
-----------------------------------------------
【问题】：content 列可能包含非常长的文本，如果直接对完整的文本进行排序，可能会占用大量内存并导致性能问题。
-----------------------------------------------
【解决办法】：这个时候我们就可以使用 max_length_for_sort_data 参数可以限制在排序时最多考虑的数据长度。如果某一行的数据长度超过这个限制，数据库可能只使用部分数据来进行排序。

如：SET max_length_for_sort_data = 1024;

2、group by

group by 使用索引的原则几乎跟 order by 一致 ，唯一区别是 group by 即使没有过滤条件用到索引，也可以直接使用索引。

• 索引的帮助：如果 GROUP BY 中的列上存在索引，数据库可以更快地对数据进行分组。索引可以使得在执行 GROUP BY 操作时，不需要先对所有数据进行排序或扫描整个表，而是直接利用索引的有序性来加速分组过程。
• 索引的局限性：尽管索引可以帮助 GROUP BY 操作更高效，但它的作用不如在 WHERE 或 LIMIT 子句中的明显。GROUP BY 操作的效率还取决于数据的分布和聚合方式等因素。例如，当表中的数据需要大量聚合计算时，索引可能不能显著加速这个过程。