慢查询与MySQL语句优化：从诊断到调优的全链路实践

一、慢查询的本质与诊断方法

慢查询是数据库性能问题的直接体现，其本质是SQL语句执行时间超过系统预设阈值（通常为1秒）。MySQL通过slow_query_log和long_query_time参数实现慢查询日志记录，开发者可通过以下步骤定位问题：

1. 开启慢查询日志
   在my.cnf配置文件中设置：

   slow_query_log = 1
   slow_query_log_file = /var/log/mysql/mysql-slow.log
   long_query_time = 0.5  # 降低阈值以捕获更多潜在问题
   log_queries_not_using_indexes = 1  # 记录未使用索引的查询

   重启MySQL服务后，所有执行时间超过0.5秒的查询将被记录，同时包含未使用索引的查询信息。

2. 使用mysqldumpslow分析日志
   该工具可按执行时间、锁定时间、返回行数等维度排序，例如：

   mysqldumpslow -s t /var/log/mysql/mysql-slow.log  # 按时间排序
   mysqldumpslow -t 5 /var/log/mysql/mysql-slow.log  # 显示前5条慢查询

   输出结果会明确显示查询模板、执行次数、总时间等信息，帮助快速定位高频慢查询。

3. 结合EXPLAIN深入分析
   对可疑查询执行EXPLAIN命令，重点关注以下字段：

   • type：访问类型（ALL表示全表扫描，应优化为range/ref/eq_ref）
   • key：实际使用的索引
   • rows：预估扫描行数
   • Extra：额外信息（如Using temporary表示使用临时表）

   示例分析：

   EXPLAIN SELECT * FROM orders WHERE customer_id = 100 AND order_date > '2023-01-01';

   若type为ALL且key为NULL，则需为(customer_id, order_date)创建复合索引。

二、索引优化：从理论到实践

索引是提升查询性能的核心手段，但需遵循以下原则：

1. 选择合适的索引类型

   • B-Tree索引：适用于等值查询（=）、范围查询（>、<）、排序（ORDER BY）
   • 哈希索引：仅适用于等值查询（Memory引擎支持）
   • 全文索引：用于文本搜索（需启用ngram插件处理中文）
   • 空间索引：用于地理数据（MyISAM/InnoDB 5.7+支持）

2. 复合索引设计策略
   遵循“最左前缀原则”，例如对(a, b, c)的索引：

   • 有效：a=1、a=1 AND b=2、a=1 AND b>2 AND c=3
   • 无效：b=2、c=3、b=2 AND c=3

   案例：优化订单查询

   -- 优化前：全表扫描
   SELECT * FROM orders WHERE status = 'completed' AND create_time > '2023-01-01';
   -- 优化后：创建复合索引
   ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);

3. 避免索引失效的常见场景

   • 隐式类型转换：WHERE phone = '13800138000'（phone为INT类型）
   • 使用函数：WHERE DATE(create_time) = '2023-01-01'
   • 模糊查询前导通配符：WHERE name LIKE '%张%'
   • OR条件未全部使用索引：WHERE a=1 OR b=2（需确保a、b均有索引）

三、SQL语句优化：从重写到重构

1. 避免SELECT *
   仅查询必要字段，减少I/O压力。例如：

   -- 优化前
   SELECT * FROM users WHERE id = 100;
   -- 优化后
   SELECT id, name, email FROM users WHERE id = 100;

2. 优化JOIN操作

   • 确保JOIN字段有索引
   • 小表驱动大表（MySQL优化器通常自动处理）
   • 避免多表JOIN导致笛卡尔积

   案例：优化用户订单查询

   -- 优化前：子查询效率低
   SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
   -- 优化后：使用JOIN
   SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.total > 1000;

3. 分页查询优化
   传统LIMIT M,N在深分页时性能差，可改用“延迟关联”：

   -- 优化前：扫描M+N行
   SELECT * FROM products ORDER BY price LIMIT 10000, 20;
   -- 优化后：先定位主键，再关联查询
   SELECT p.* FROM products p JOIN (
       SELECT id FROM products ORDER BY price LIMIT 10000, 20
   ) AS tmp USING(id);

四、高级优化技术

1. 使用覆盖索引
   若查询字段均包含在索引中，可避免回表操作。例如：

   -- 创建覆盖索引
   ALTER TABLE products ADD INDEX idx_price_name (price, name);
   -- 查询直接使用索引
   SELECT price, name FROM products WHERE price > 100;

2. 优化子查询
   将IN子查询改为EXISTS（当子查询结果集大时更高效）：

   -- 优化前
   SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE total > 5000);
   -- 优化后
   SELECT * FROM customers c WHERE EXISTS (
       SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 5000
   );

3. 数据库参数调优

   • innodb_buffer_pool_size：设为物理内存的50-70%
   • query_cache_size：MySQL 8.0已移除，需通过应用层缓存
   • tmp_table_size/max_heap_table_size：控制内存临时表大小

五、监控与持续优化

1. 使用Performance Schema
   监控高频查询、锁等待、IO操作等：

   SELECT * FROM performance_schema.events_statements_summary_by_digest 
   ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

2. 定期分析表
   执行ANALYZE TABLE更新统计信息，帮助优化器选择更优执行计划：

   ANALYZE TABLE orders;

3. 建立慢查询预警机制
   通过Prometheus+Grafana监控慢查询数量、平均执行时间等指标，设置阈值告警。

结语

MySQL慢查询优化是一个系统工程，需结合日志分析、索引设计、SQL重写、参数调优等多维度手段。开发者应养成“先诊断，后优化”的习惯，避免盲目调整。通过持续监控和迭代优化，可显著提升数据库性能，为业务提供稳定的数据支撑。