MST

星途 面试题库

面试题：MySQL慢查询日志中的常见问题及SQL重写思路

假设你在MySQL慢查询日志中发现一条执行缓慢的SQL语句，它涉及到多表关联查询，表结构和数据量都较为复杂。请阐述你从哪些方面入手分析该SQL性能问题，并举例说明可能的SQL重写技巧。
45.7万 热度难度
数据库MySQL

知识考点

AI 面试

面试题答案

一键面试

分析方面

  1. 查询语句本身：
    • 检查多表关联条件：确保关联条件正确且使用了合适的索引。例如，在 JOIN 子句中，关联字段是否有索引。若表 A 和表 B 通过字段 id 关联，SELECT * FROM A JOIN B ON A.id = B.id，需检查 A.id 和 B.id 是否有索引。
    • 查看 WHERE 子句：分析过滤条件是否合理。避免使用函数操作字段，如 SELECT * FROM users WHERE UPPER(name) = 'JOHN'，应改写为 SELECT * FROM users WHERE name = 'john' 并给 name 字段加索引。同时检查是否有不必要的条件导致数据过滤不充分或过度。
    • GROUP BY 和 ORDER BY：确认这些操作是否使用了索引。例如 SELECT category, COUNT(*) FROM products GROUP BY category ORDER BY COUNT(*) DESC，若 category 字段无索引，会导致性能问题。
  2. 索引使用情况：
    • 执行 EXPLAIN 语句：查看查询计划，分析索引是否被正确使用。例如 EXPLAIN SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id WHERE orders.order_date > '2023 - 01 - 01'，从 EXPLAIN 结果中查看 key 列，若显示为 NULL，说明未使用索引，可能需在 orders.customer_id 和 orders.order_date 字段创建合适索引。
    • 检查索引覆盖：对于查询所需的字段，是否可以通过索引直接获取，减少回表操作。如 SELECT id, name FROM users WHERE age > 30，若在 age 字段上有索引，但查询还需要 id 和 name，可创建复合索引 (age, id, name) 实现索引覆盖。
  3. 表结构和数据量：
    • 表设计合理性：检查是否存在冗余字段或不合理的范式设计。例如，若经常需要查询用户的基本信息和其所在部门信息，而部门信息在另一张表且关联复杂，可考虑适当冗余部门信息到用户表。
    • 数据量分布：分析数据量较大的表对查询的影响。若某张表数据量过大，可考虑分区表。如按时间对订单表进行分区，CREATE TABLE orders (id INT, order_date DATE, amount DECIMAL(10, 2)) PARTITION BY RANGE (YEAR(order_date)) (PARTITION p0 VALUES LESS THAN (2020), PARTITION p1 VALUES LESS THAN (2021), PARTITION p2 VALUES LESS THAN (2022))，查询特定年份订单时可减少扫描数据量。
  4. 数据库服务器配置：
    • 内存配置：检查 innodb_buffer_pool_size 等参数，确保足够的内存用于缓存数据和索引。若值过小，频繁磁盘 I/O 会导致查询性能下降。
    • CPU 负载：查看服务器 CPU 使用情况，过高的 CPU 负载可能影响数据库查询执行效率，需排查是否有其他高 CPU 占用的进程或查询过于复杂。

SQL 重写技巧

  1. 子查询优化为 JOIN：
    • 原 SQL：SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE name = 'John')
    • 优化后：SELECT orders.* FROM orders JOIN customers ON orders.customer_id = customers.id AND customers.name = 'John'
  2. 使用 EXISTS 替代 IN（当子查询结果集较大时）：
    • 原 SQL：SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = 1)
    • 优化后：SELECT * FROM products WHERE EXISTS (SELECT 1 FROM categories WHERE categories.id = products.category_id AND categories.active = 1)
  3. 简化复杂的 OR 条件：
    • 原 SQL：SELECT * FROM users WHERE (age > 30 AND city = 'New York') OR (age > 40 AND city = 'Los Angeles')
    • 优化后：(SELECT * FROM users WHERE age > 30 AND city = 'New York') UNION (SELECT * FROM users WHERE age > 40 AND city = 'Los Angeles')
  4. 避免全表扫描的 LIKE：
    • 原 SQL：SELECT * FROM products WHERE name LIKE '%keyword%'
    • 优化后：若业务允许，可改为 SELECT * FROM products WHERE name LIKE 'keyword%'，并对 name 字段创建前缀索引 CREATE INDEX idx_name ON products(name(5))（5 为前缀长度，可根据实际调整）