面试题：MySQL服务器负载剖析之查询负载

Question

在MySQL服务器负载剖析中，查询负载是重要部分。假设你通过慢查询日志发现部分查询导致服务器负载升高，阐述下从哪些方面优化这些查询以降低负载，例如索引优化、查询语句重构等，每个方面简单说明优化思路。

Answer 1

索引优化

• 添加索引：分析查询语句中WHERE、JOIN等子句涉及的列，对这些列添加合适的索引，加快数据检索速度。例如对于SELECT * FROM users WHERE age > 30;，可在age列添加索引。
• 复合索引：当多个条件联合查询时，创建复合索引。注意索引顺序，把选择性高（区分度大）的列放在前面，如SELECT * FROM orders WHERE status = 'completed' AND user_id = 123;，创建(status, user_id)复合索引。
• 删除无用索引：定期检查索引使用情况，删除长时间未使用的索引，避免索引维护开销。

查询语句重构

• 避免全表扫描：尽量使用索引覆盖查询，使查询所需数据都能从索引中获取，减少回表操作。例如SELECT id, name FROM users WHERE age > 30;若id、name、age在同一索引中，可减少查询开销。
• 优化JOIN操作：确保JOIN条件使用索引，合理选择JOIN类型（如INNER JOIN、LEFT JOIN等）。例如两张表关联查询时，JOIN条件字段要有索引。
• 减少子查询：子查询嵌套过多会增加查询复杂度和性能开销，可尝试用JOIN替代子查询。例如SELECT * FROM users WHERE user_id IN (SELECT user_id FROM orders WHERE amount > 100);可改写为SELECT u.* FROM users u JOIN orders o ON u.user_id = o.user_id WHERE o.amount > 100;。

数据库配置优化

• 调整缓存参数：适当增大innodb_buffer_pool_size，使更多数据能缓存到内存，减少磁盘I/O。例如根据服务器内存情况，合理设置该参数比例。
• 优化线程参数：根据服务器硬件资源和并发访问量，调整max_connections、thread_cache_size等参数，避免线程频繁创建和销毁带来的开销。

表结构优化

• 范式与反范式：遵循数据库范式设计减少数据冗余，但某些场景下可适当引入反范式设计，通过增加少量冗余数据减少表关联，提高查询性能。例如在一些统计报表相关表中可适当冗余部分字段。
• 分区表：对于数据量巨大的表，按一定规则（如按时间、地域等）进行分区，查询时可只扫描相关分区，减少扫描数据量。如按月份对订单表分区。