【实战篇】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;

运行报错：

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;

运行时间：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;

运行时间：900ms 左右，勉强接受！！！

这个优化的逻辑主要通过使用 LEFT JOIN 连接 safe 表和 tbl_file 表，然后使用聚合函数 MAX 结合 CASE 表达式来获取每个 file_class 对应的文件路径。

下面逐步解释优化的逻辑：

1. 左连接 (LEFT JOIN)：
   • LEFT JOIN 用于连接 safe 表和 tbl_file 表，即使在没有匹配的情况下也会保留左表中的所有行。
   • 这确保了即使某个 safe 行没有对应的文件记录，也会在结果中保留。
2. 聚合函数 MAX：
   • MAX 函数用于获取每个 file_class 对应的最大文件路径。在这里，实际上是获取了每个 file_class 对应的唯一文件路径（因为 MAX 函数作用于单个值的集合）。
3. CASE 表达式：
   • CASE 表达式用于在每个 MAX 函数中选择正确的文件路径。对于每个 file_class，CASE 表达式返回对应的 file_path 值。
4. GROUP BY 子句：
   • GROUP BY 子句用于按照 safe 表中的 id 分组，确保每个 safe 行只有一行结果。
   • 在每个分组中，MAX 函数将获取每个 file_class 对应的最大文件路径。

这种优化的主要思想是将多个子查询合并成一个联接查询，以减少对数据库的多次扫描。同时，使用聚合函数将每个 file_class 对应的多个文件路径合并为一个结果行，使得结果更加简洁。

执行计划对比

1、子查询版

2、条件表达式优化版

分页优化

分页优化的常用技巧包括：

1. 使用覆盖索引：如果一条 SQL 语句可以通过索引直接获取查询的结果，不再需要回表查询，这个索引就称为覆盖索引。覆盖索引可以显著提高查询性能。
2. 使用子查询优化：这种方式先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况。
3. 使用 id 限定优化：这种方式假设数据表的 id 是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的 id 的范围，可以使用 id between and 来查询。
4. 使用临时表优化：这种方式已经不属于查询优化，但是可以在处理大量数据时提高效率。
5. 使用合适的索引：为了提高查询性能，可以使用适当的索引，如主键、唯一索引、联合索引等。
6. 根据自增且连续的主键排序的分页查询：这种方式可以利用主键的连续性，通过计算偏移量来提高查询效率。
7. Join 关联查询优化：对于涉及到多表连接的查询，可以通过优化连接算法来提高查询性能。

利用好 小表驱动大表 ！！！

• Java性能调优–SQL篇：优化“分页查询“_java项目显示数据库中的数据时,涉及到了大数据量的查询和分页,怎么进行优化,使性-优快云博客
• 数据量很大，分页查询很慢，优化方案 - 小虾米的java梦 - 博客园 (cnblogs.com)

not exist 子查询优化

可用 join + where 子句替代来优化性能