MySQL慢查询日志中的SQL重写技巧

MySQL慢查询日志概述

什么是慢查询日志

MySQL慢查询日志是MySQL数据库提供的一种日志记录机制，用于记录执行时间超过指定阈值（由参数long_query_time定义，默认值为10秒）的SQL语句。通过分析慢查询日志，数据库管理员和开发人员能够定位系统中执行效率较低的SQL语句，进而对其进行优化，提升数据库整体性能。

例如，假设我们有一个电商系统，其中订单查询功能执行缓慢，通过慢查询日志我们可以发现类似如下的SQL语句：

SELECT * FROM orders 
WHERE order_date > '2023-01-01' 
  AND customer_id = 123 
  AND status = 'completed';

若这条语句执行时间超过了long_query_time设定的值，就会被记录到慢查询日志中。

慢查询日志的作用

性能瓶颈定位：在复杂的应用系统中，数据库往往是性能瓶颈的高发区域。慢查询日志能够精准指出哪些SQL语句执行缓慢，帮助开发团队将优化精力集中在关键问题上。比如在一个社交平台，用户动态加载缓慢，查看慢查询日志可能发现获取用户好友动态的SQL语句耗时过长。
优化系统架构：长期监控慢查询日志，可以发现系统在设计上的不足。如果频繁出现某类关联查询的慢查询，可能意味着数据库表结构设计不合理，需要进行范式化或反范式化调整。
数据库优化效果验证：在对SQL语句进行优化后，再次查看慢查询日志，确认优化后的语句是否不再被记录，以此验证优化效果。

开启慢查询日志

在MySQL中开启慢查询日志相对简单，通过修改配置文件（通常是my.cnf或my.ini）来设置相关参数。

修改配置文件：在配置文件的[mysqld]部分添加或修改以下参数：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2

这里slow_query_log = 1表示开启慢查询日志；slow_query_log_file指定了日志文件的路径和名称；long_query_time = 2将慢查询的阈值设置为2秒，即执行时间超过2秒的SQL语句会被记录。 2. 重启MySQL服务：修改配置文件后，需要重启MySQL服务使配置生效。在Linux系统下，一般使用以下命令：

sudo systemctl restart mysql

在Windows系统下，可以通过服务管理界面重启MySQL服务。

SQL重写基础概念

为什么要重写SQL

执行计划优化：不同的SQL写法，MySQL优化器生成的执行计划可能差异巨大。例如，使用子查询和连接查询在某些情况下性能不同。假设我们有两张表employees和departments，要获取每个部门的员工数量。使用子查询的方式：

SELECT department_id, 
       (SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id) AS employee_count 
FROM departments d;

使用连接查询的方式：

SELECT d.department_id, COUNT(e.employee_id) AS employee_count 
FROM departments d 
JOIN employees e ON d.department_id = e.department_id 
GROUP BY d.department_id;

通常情况下，连接查询的执行效率会更高，因为MySQL优化器在处理连接时可以更好地利用索引等优化策略。 2. 索引利用：合理的SQL重写可以让MySQL更好地利用索引。比如有一张products表，包含product_name、price和category_id等字段，并且在category_id字段上有索引。如果原SQL是：

SELECT * FROM products WHERE UPPER(product_name) = 'BOOK';

由于使用了UPPER函数，MySQL无法使用category_id上的索引。可以重写为：

SELECT * FROM products WHERE product_name = 'BOOK';

这样在product_name字段有索引的情况下就能利用索引，提升查询效率。

SQL重写的基本原则

简单化原则：尽量简化SQL语句，减少不必要的子查询、嵌套和复杂表达式。例如，避免多层嵌套的子查询，能用连接解决的尽量用连接。
索引友好原则：重写后的SQL要能够充分利用已有的索引。了解表结构和索引情况，合理调整查询条件的顺序和写法。
逻辑等价原则：重写后的SQL必须与原SQL在逻辑上等价，即返回的结果集必须相同。

常见SQL重写技巧

子查询优化

子查询转连接
- 相关子查询：相关子查询是指子查询的执行依赖于外层查询的值。例如，查询每个客户的最新订单信息：

SELECT customer_id, 
       (SELECT order_date 
        FROM orders o 
        WHERE o.customer_id = c.customer_id 
        ORDER BY order_date DESC 
        LIMIT 1) AS latest_order_date 
FROM customers c;

这种相关子查询效率较低，因为对于外层customers表的每一行，子查询都要执行一次。可以将其重写为连接查询：

SELECT c.customer_id, o.order_date AS latest_order_date 
FROM customers c 
JOIN (
    SELECT customer_id, order_date, 
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn 
    FROM orders
) o ON c.customer_id = o.customer_id AND o.rn = 1;

这里使用了窗口函数ROW_NUMBER()，通过PARTITION BY customer_id按客户分组，ORDER BY order_date DESC按订单日期降序排列，然后只取每组中rn = 1的记录，即每个客户的最新订单。

非相关子查询：非相关子查询的执行不依赖于外层查询。例如，查询购买了特定产品的客户：

SELECT customer_id 
FROM orders 
WHERE product_id = (SELECT product_id FROM products WHERE product_name = 'Widget');

可以重写为连接查询：

SELECT o.customer_id 
FROM orders o 
JOIN products p ON o.product_id = p.product_id AND p.product_name = 'Widget';

使用IN替代子查询：在某些情况下，子查询可以用IN操作符替代。比如查询购买了特定类别产品的客户：原子查询：

SELECT customer_id 
FROM orders 
WHERE product_id IN (SELECT product_id FROM products WHERE category = 'Electronics');

可以避免嵌套子查询，直接写成：

SELECT o.customer_id 
FROM orders o 
JOIN products p ON o.product_id = p.product_id AND p.category = 'Electronics';

虽然表面上看起来相似，但连接查询在大数据量下可能有更好的执行计划。

连接优化

内连接与外连接的选择：在重写SQL时，要根据业务需求准确选择内连接和外连接。内连接只返回满足连接条件的行，而外连接（左连接、右连接、全外连接）会返回一侧或两侧表的所有行，即使不满足连接条件。例如，有orders表和customers表，要获取所有客户及其订单信息（包括没有订单的客户），应该使用左连接：

SELECT c.customer_id, o.order_id 
FROM customers c 
LEFT JOIN orders o ON c.customer_id = o.customer_id;

如果使用内连接，没有订单的客户信息将不会出现在结果集中。 2. 连接顺序优化：MySQL优化器通常会自动选择最优的连接顺序，但在某些复杂情况下，手动调整连接顺序可能会提升性能。假设我们有三张表A、B和C，连接条件分别为A.id = B.a_id和B.id = C.b_id。如果表A数据量最小，通常应该将A放在连接的最左侧，这样中间结果集相对较小，例如：

SELECT * 
FROM A 
JOIN B ON A.id = B.a_id 
JOIN C ON B.id = C.b_id;

减少笛卡尔积：笛卡尔积是指在连接时没有指定连接条件，导致结果集行数为两张表行数的乘积，这会产生大量不必要的数据，严重影响性能。比如错误的写法：

SELECT * FROM table1, table2;

应正确指定连接条件，如：

SELECT * FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id;

索引优化相关重写

避免索引失效：如前文所述，函数操作、使用LIKE '%...'（不以通配符开头的LIKE可以利用索引，如LIKE '...%'）等情况会导致索引失效。对于函数操作导致的索引失效，比如有users表，birth_date字段有索引，原SQL：

SELECT * FROM users WHERE YEAR(birth_date) = 1990;

应重写为：

SELECT * FROM users WHERE birth_date BETWEEN '1990-01-01' AND '1990-12-31';

这样就能利用birth_date上的索引。 2. 复合索引的利用：复合索引是指在多个字段上创建的索引。假设我们有一张employees表，创建了复合索引idx_name_salary (name, salary)。原SQL：

SELECT * FROM employees WHERE salary > 50000;

由于复合索引的最左前缀原则，该SQL无法利用复合索引。应重写为：

SELECT * FROM employees WHERE name IS NOT NULL AND salary > 50000;

这样就可以利用复合索引，因为它满足最左前缀原则，先使用name字段，再使用salary字段。

聚合函数优化

避免在聚合函数中使用子查询：例如，要获取每个部门的平均工资与公司平均工资的差值。原SQL：

SELECT department_id, 
       AVG(salary) - (SELECT AVG(salary) FROM employees) AS salary_diff 
FROM employees 
GROUP BY department_id;

子查询在每个分组中都会执行一次，效率较低。可以重写为：

SELECT e1.department_id, 
       e1.avg_salary - e2.avg_salary AS salary_diff 
FROM (
    SELECT department_id, AVG(salary) AS avg_salary 
    FROM employees 
    GROUP BY department_id
) e1 
JOIN (
    SELECT AVG(salary) AS avg_salary 
    FROM employees
) e2 ON 1 = 1;

这里先分别计算每个部门的平均工资和公司的平均工资，然后通过连接计算差值，避免了子查询在每个分组中重复执行。 2. 使用SUM(CASE ...)替代GROUP BY：在某些情况下，使用SUM(CASE ...)可以简化查询并提升性能。比如要统计每个客户不同状态订单的数量，原SQL：

SELECT customer_id, status, COUNT(*) AS order_count 
FROM orders 
GROUP BY customer_id, status;

可以重写为：

SELECT customer_id, 
       SUM(CASE WHEN status = 'new' THEN 1 ELSE 0 END) AS new_order_count, 
       SUM(CASE WHEN status = 'processing' THEN 1 ELSE 0 END) AS processing_order_count, 
       SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_order_count 
FROM orders 
GROUP BY customer_id;

这样在查询结果展示上更加直观，并且在某些场景下执行效率可能更高。

SQL重写实践案例

案例一：电商订单查询优化

原始SQL及问题分析：假设在电商系统中有orders表（包含order_id、customer_id、order_date、status等字段），products表（包含product_id、product_name、price等字段），order_items表（包含order_id、product_id、quantity等字段）。要查询每个客户最近一周内已完成订单中购买的产品及总金额。原始SQL如下：

SELECT c.customer_id, 
       p.product_name, 
       SUM(oi.quantity * p.price) AS total_amount 
FROM customers c 
JOIN orders o ON c.customer_id = o.customer_id 
JOIN order_items oi ON o.order_id = oi.order_id 
JOIN products p ON oi.product_id = p.product_id 
WHERE o.status = 'completed' 
  AND o.order_date >= CURDATE() - INTERVAL 1 WEEK 
GROUP BY c.customer_id, p.product_name;

这个SQL存在的问题是，order_date字段上如果没有索引，在大数据量下范围查询o.order_date >= CURDATE() - INTERVAL 1 WEEK会比较慢。 2. 重写SQL及优化思路：重写后的SQL如下：

SELECT c.customer_id, 
       p.product_name, 
       SUM(oi.quantity * p.price) AS total_amount 
FROM customers c 
JOIN (
    SELECT order_id, customer_id 
    FROM orders 
    WHERE status = 'completed' 
      AND order_date >= CURDATE() - INTERVAL 1 WEEK 
) o ON c.customer_id = o.customer_id 
JOIN order_items oi ON o.order_id = oi.order_id 
JOIN products p ON oi.product_id = p.product_id 
GROUP BY c.customer_id, p.product_name;

优化思路是先在子查询中过滤出符合条件的订单，减少后续连接的数据量。同时，在orders表的status和order_date字段上创建复合索引idx_status_date (status, order_date)，这样子查询就能利用索引，提升查询效率。

案例二：社交平台好友动态查询优化

原始SQL及问题分析：在社交平台中，有users表（包含user_id、name等字段），friends表（包含user_id、friend_id），posts表（包含post_id、user_id、post_content、post_date等字段）。要获取每个用户好友的最新动态（按发布时间倒序）。原始SQL如下：

SELECT u.user_id, 
       p.post_content, 
       p.post_date 
FROM users u 
JOIN friends f ON u.user_id = f.user_id 
JOIN posts p ON f.friend_id = p.user_id 
ORDER BY u.user_id, p.post_date DESC;

这个SQL的问题在于没有限制每个用户获取的动态数量，在大数据量下可能返回大量数据，并且在friends表和posts表连接时，没有利用好索引，因为posts表按post_date排序，在连接时没有索引支持。 2. 重写SQL及优化思路：重写后的SQL如下：

WITH FriendPosts AS (
    SELECT f.friend_id, p.post_id, p.post_content, p.post_date, 
           ROW_NUMBER() OVER (PARTITION BY f.friend_id ORDER BY p.post_date DESC) AS rn 
    FROM friends f 
    JOIN posts p ON f.friend_id = p.user_id
)
SELECT u.user_id, 
       fp.post_content, 
       fp.post_date 
FROM users u 
JOIN friends f ON u.user_id = f.user_id 
JOIN FriendPosts fp ON f.friend_id = fp.friend_id AND fp.rn = 1 
ORDER BY u.user_id;

优化思路是使用CTE（Common Table Expression，公共表表达式），在FriendPosts中利用窗口函数ROW_NUMBER()按好友ID分区，按发布日期倒序排列，只取每个好友的最新一条动态（rn = 1）。同时，在friends表的user_id和posts表的user_id及post_date字段上创建适当的索引，如idx_friends_user_id (user_id)和idx_posts_user_date (user_id, post_date)，以提升连接和排序的效率。

总结与注意事项

重写效果验证：每次对SQL进行重写后，一定要通过实际测试验证性能提升效果。可以使用EXPLAIN关键字查看执行计划，对比重写前后执行计划的差异，确认索引使用情况、连接类型等是否得到优化。例如，对上述电商订单查询优化的重写SQL，使用EXPLAIN：

EXPLAIN SELECT c.customer_id, 
                p.product_name, 
                SUM(oi.quantity * p.price) AS total_amount 
FROM customers c 
JOIN (
    SELECT order_id, customer_id 
    FROM orders 
    WHERE status = 'completed' 
      AND order_date >= CURDATE() - INTERVAL 1 WEEK 
) o ON c.customer_id = o.customer_id 
JOIN order_items oi ON o.order_id = oi.order_id 
JOIN products p ON oi.product_id = p.product_id 
GROUP BY c.customer_id, p.product_name;

查看key字段，确认是否使用了idx_status_date复合索引。 2. 对业务逻辑的影响：重写SQL必须保证业务逻辑的正确性。在重写过程中，要仔细核对结果集是否与原SQL一致，特别是在处理复杂的条件、聚合操作等情况下。例如在社交平台好友动态查询优化中，重写后的SQL通过ROW_NUMBER()窗口函数保证每个用户获取的是好友的最新动态，结果集逻辑与原需求一致。 3. 数据库版本差异：不同的MySQL版本在优化器和语法支持上可能存在差异。一些在高版本中有效的重写技巧，在低版本中可能不适用。例如，某些窗口函数是在较新的MySQL版本中才引入的。在进行SQL重写时，要了解所使用的MySQL版本特性，确保重写后的SQL能够正确执行。

通过深入理解MySQL慢查询日志，并运用上述SQL重写技巧，开发人员和数据库管理员能够有效地优化数据库性能，提升应用系统的响应速度和稳定性。