SQL语句优化
# SQL 语句优化
# 日期比较
日期比较： 将 DATEDIFF 改为使用 CURDATE()，并将条件修改为 expire_date <= CURDATE() + INTERVAL 30 DAY，这样可以避免在每一行上执行函数。
# 修改前
AND DATEDIFF( expire_date, NOW() ) <= 30
# 修改后
AND (expire_date <= CURDATE() + INTERVAL 30 DAY)
# 多个 left join 同一张表优化
# 原代码
select safe.*,f0.file_path file,f1.file_path file1,
f2.file_path file2,f3.file_path file3,f4.file_path file4,
f5.file_path file5
from
safe
left join tbl_file f0 on f0.obj_id = safe.id and f0.file_class = 0
left join tbl_file f1 on f1.obj_id = safe.id and f1.file_class = 1
left join tbl_file f2 on f2.obj_id = safe.id and f2.file_class = 2
left join tbl_file f3 on f3.obj_id = safe.id and f3.file_class = 3
left join tbl_file f4 on f4.obj_id = safe.id and f4.file_class = 4
left join tbl_file f5 on f5.obj_id = safe.id and f5.file_class = 5
where safe.is_deleted = 0
ORDER BY
safe.create_time DESC
LIMIT 20;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
运行报错：
Can't create/write to file '/tmp/MYfEfAaN' (OS errno 24 - Too many open files)
这错误表明在执行 SQL 查询时，MySQL 打开的文件句柄数达到系统限制，导致无法再打开新的文件。错误信息中的 "/tmp/MYfEfAaN" 是 MySQL 试图创建或写入的文件路径。
这个问题通常是由系统的文件句柄限制引起的。每个打开的文件都需要一个文件句柄，而系统默认对于一个进程能够同时打开的文件数量有一个限制。在这种情况下，MySQL 打开了太多的文件，超过了系统允许的限制。
# 子查询修改
SELECT
safe.*,
(SELECT file_path FROM tbl_file WHERE obj_id = safe.id AND file_class = 0 LIMIT 1) AS file,
(SELECT file_path FROM tbl_file WHERE obj_id = safe.id AND file_class = 1 LIMIT 1) AS file1,
(SELECT file_path FROM tbl_file WHERE obj_id = safe.id AND file_class = 2 LIMIT 1) AS file2,
(SELECT file_path FROM tbl_file WHERE obj_id = safe.id AND file_class = 3 LIMIT 1) AS file3,
(SELECT file_path FROM tbl_file WHERE obj_id = safe.id AND file_class = 4 LIMIT 1) AS file4,
(SELECT file_path FROM tbl_file WHERE obj_id = safe.id AND file_class = 5 LIMIT 1) AS file5
FROM
safe
where safe.is_deleted = 0
ORDER BY
safe.create_time DESC
LIMIT 20;
2
3
4
5
6
7
8
9
10
11
12
13
14
运行时间：15s 左右，性能太差！！！
注意：
LIMIT 1 的作用是：限制查询结果返回的行数，只返回一条记录。在第一条 SQL 查询中，每个文件类别都使用了 LIMIT 1 来确保只选择一条与之关联的文件路径。这样做的目的是为了避免在选择多个文件路径时出现重复的情况，因为 tbl_file 表中可能存在多条与同一个 obj_id 关联的文件记录。通过使用 LIMIT 1，可以确保每个文件类别只选择一条记录，从而避免重复。
根据实际情况使用，如果一定不会重复，可不写。
# 条件表达式优化（最终版）
SELECT
safe.*,
MAX( CASE WHEN file_class = 0 THEN file_path END ) AS file,
MAX( CASE WHEN file_class = 1 THEN file_path END ) AS file1,
MAX( CASE WHEN file_class = 2 THEN file_path END ) AS file2,
MAX( CASE WHEN file_class = 3 THEN file_path END ) AS file3,
MAX( CASE WHEN file_class = 4 THEN file_path END ) AS file4,
MAX( CASE WHEN file_class = 5 THEN file_path END ) AS file5
FROM
safe
LEFT JOIN tbl_file f ON safe.id = f.obj_id
where safe.is_deleted = 0
GROUP BY safe.id
ORDER BY
safe.create_time DESC
LIMIT 20;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
运行时间：900ms 左右，性能不错！！！
这个优化的逻辑主要通过使用 LEFT JOIN 连接 safe 表和 tbl_file 表，然后使用聚合函数 MAX 结合 CASE 表达式来获取每个 file_class 对应的文件路径。
下面逐步解释优化的逻辑：
- 左连接 (
LEFT JOIN)：LEFT JOIN用于连接safe表和tbl_file表，即使在没有匹配的情况下也会保留左表中的所有行。- 这确保了即使某个
safe行没有对应的文件记录，也会在结果中保留。
- 聚合函数
MAX：MAX函数用于获取每个file_class对应的最大文件路径。在这里，实际上是获取了每个file_class对应的唯一文件路径（因为MAX函数作用于单个值的集合）。
CASE表达式：CASE表达式用于在每个MAX函数中选择正确的文件路径。对于每个file_class，CASE表达式返回对应的file_path值。
GROUP BY子句：GROUP BY子句用于按照safe表中的id分组，确保每个safe行只有一行结果。- 在每个分组中，
MAX函数将获取每个file_class对应的最大文件路径。
这种优化的主要思想是将多个子查询合并成一个联接查询，以减少对数据库的多次扫描。同时，使用聚合函数将每个 file_class 对应的多个文件路径合并为一个结果行，使得结果更加简洁。
# 执行计划对比
# 1、子查询版
# 2、条件表达式优化版
# 分页优化
分页优化的常用技巧包括：
- 使用覆盖索引：如果一条 SQL 语句可以通过索引直接获取查询的结果，不再需要回表查询，这个索引就称为覆盖索引。覆盖索引可以显著提高查询性能。
- 使用子查询优化：这种方式先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况。
- 使用 id 限定优化：这种方式假设数据表的 id 是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的 id 的范围，可以使用 id between and 来查询。
- 使用临时表优化：这种方式已经不属于查询优化，但是可以在处理大量数据时提高效率。
- 使用合适的索引：为了提高查询性能，可以使用适当的索引，如主键、唯一索引、联合索引等。
- 根据自增且连续的主键排序的分页查询：这种方式可以利用主键的连续性，通过计算偏移量来提高查询效率。
- Join 关联查询优化：对于涉及到多表连接的查询，可以通过优化连接算法来提高查询性能。
利用好 小表驱动大表 ！！！
Java性能调优--SQL篇：优化“分页查询“_java项目显示数据库中的数据时,涉及到了大数据量的查询和分页,怎么进行优化,使性-CSDN博客 (opens new window)
数据量很大，分页查询很慢，优化方案 - 小虾米的java梦 - 博客园 (cnblogs.com) (opens new window)
# not exist 子查询优化
可用 join + where 子句替代来优化性能