SQL优化
- 一、SQL优化的计划或思路
- 二、关联查询优化
- 三、子查询优化
- 四、exists 和 not exists
- 1、exists 介绍
- 2、exists 和 not exists
- 五、单路排序和多路排序（了解）
- 六、排序分组优化
- 1、order by
- （1）避免临时排序,使用索引排序
- （2）减少数据扫描
- （3）调整 `MySQL` 参数配置
- sort_buffer_size
- max_length_for_sort_data
- 2、group by
一、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-101.使用
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_idAND 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 | IT1. 使用
EXISTS
需求： 查询在部门表departments中存在关联记录的员工。
SELECT employee_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments dWHERE 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 dWHERE 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操作的效率还取决于数据的分布和聚合方式等因素。例如，当表中的数据需要大量聚合计算时，索引可能不能显著加速这个过程。