IT评测·应用市场-qidao123.com技术社区

标题: MySQL 查询优化案例分享 [打印本页]

---

作者: 尚未崩坏    时间: 2024-12-27 13:59
标题: MySQL 查询优化案例分享
在一样平常开发中，SQL 查询性能直接影响到系统的响应速度和用户体验。随着数据量的增长，慢查询可能成为系统的瓶颈。本文将通过实际案例，分享几种常见的 MySQL 查询优化方法，帮助开发者快速定位和优化慢查询，提拔数据库性能。

---

一、慢查询定位与分析

1. 开启慢查询日志

通过开启慢查询日志，可以记载执行时间超过指定阈值的 SQL 查询，帮助分析系统中的瓶颈。

1. SET GLOBAL slow_query_log = 1;  
2. SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询会记录  
3. SHOW VARIABLES LIKE 'slow_query_log_file';  -- 查看慢查询日志位置  

复制代码

示例输出（日志格式）：

1. # Time: 2023-12-01T10:05:34  
2. # Query_time: 2.456 Lock_time: 0.002 Rows_sent: 1000 Rows_examined: 500000  
3. SELECT * FROM orders WHERE order_date > '2023-01-01';  

复制代码

---

二、案例 1：大表全表扫描优化

问题描述

查询订单表 orders 中近一年的订单数据，查询速度迟钝。

1. SELECT * FROM orders WHERE order_date > '2023-01-01';  

复制代码

分析：

• 订单表数据量巨大，每次查询都进行全表扫描（EXPLAIN 表现 type=ALL）。
  
• order_date 列没有建立索引。
  

优化方案：

• 为 order_date 字段添加索引：
  

1. ALTER TABLE orders ADD INDEX idx_order_date (order_date);  

复制代码

• 使用覆盖索引查询，避免回表：
  

1. SELECT order_id, order_date FROM orders WHERE order_date > '2023-01-01';  

复制代码

优化结果：

• 添加索引后，查询由全表扫描变为索引范围扫描（range），性能明显提拔。
  

---

三、案例 2：JOIN 查询优化

问题描述

多表关联查询，订单表和用户表进行 JOIN 使用，执行时间过长。

1. SELECT o.order_id, u.username   
2. FROM orders o   
3. JOIN users u ON o.user_id = u.id   
4. WHERE o.order_date > '2023-01-01';  

复制代码

分析：

• EXPLAIN 表现 orders 表使用索引，但 users 表执行全表扫描。
  
• 关联字段 user_id 缺少索引，导致 users 表每次都需遍历整个表。
  

优化方案：

• 为关联字段添加索引：
  

1. ALTER TABLE users ADD INDEX idx_user_id (id);  

复制代码

• 优化 JOIN 查询，确保主表和从表都能使用索引：
  

1. EXPLAIN SELECT o.order_id, u.username   
2. FROM orders o   
3. JOIN users u ON o.user_id = u.id   
4. WHERE o.order_date > '2023-01-01';  

复制代码

优化结果：

• 查询速度由原来的 5 秒降低到 500 毫秒，减少 JOIN 过程中 users 表的全表扫描。
  

---

四、案例 3：COUNT 优化

问题描述

统计用户订单总数，使用 COUNT(*) 查询时速度迟钝。

1. SELECT COUNT(*) FROM orders WHERE user_id = 1001;  

复制代码

分析：

• COUNT(*) 会扫描匹配的所有行，即使只统计数量。
  
• 如果 orders 表数据量巨大，性能可能受限。
  

优化方案：

• 使用 COUNT(索引字段) 替换 COUNT(*)：
  

1. SELECT COUNT(order_id) FROM orders WHERE user_id = 1001;  

复制代码

• 如果查询量巨大，可考虑维护统计表：
  

1. CREATE TABLE order_summary AS   
2. SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id;  

复制代码

优化结果：

• 使用索引字段统计可以减少不须要的数据扫描，性能提拔约 30%-50%。
  

---

五、案例 4：分页查询优化

问题描述

分页查询性能较差，用户访问较深的分页时响应时间迟钝。

1. SELECT * FROM orders ORDER BY order_date LIMIT 100000, 10;  

复制代码

分析：

• 深度分页查询时，LIMIT 会跳过前 100000 条记载，导致大量数据扫描。
  

优化方案：

• 使用覆盖索引加快分页：
  

1. SELECT order_id FROM orders ORDER BY order_date LIMIT 100000, 10;  

复制代码

• 使用 ID 范围方式优化分页：
  

1. SELECT * FROM orders   
2. WHERE order_id > (SELECT order_id FROM orders ORDER BY order_date LIMIT 100000, 1)  
3. LIMIT 10;  

复制代码

优化结果：

• 查询时间由原来的 3 秒降至 200 毫秒。
  

---

六、案例 5：OR 查询优化

问题描述

查询多个条件时，OR 语句导致索引失效。

1. SELECT * FROM orders WHERE user_id = 1001 OR order_date > '2023-01-01';  

复制代码

分析：

• OR 查询跨多个字段，可能导致索引失效，执行全表扫描。
  

优化方案：

• 将 OR 拆分为 UNION 查询：
  

1. SELECT * FROM orders WHERE user_id = 1001   UNION   SELECT * FROM orders WHERE order_date > '2023-01-01';  

复制代码

优化结果：

• 查询效率提拔约 40%，避免全表扫描。
  

---

七、索引优化总结

1. 创建符合的索引

• 常常用于 WHERE 和 JOIN 的字段应建立索引。
  
• 避免在低选择性的字段上建立索引（如性别字段）。
  

2. 避免索引失效的情况

• 使用函数盘算的字段不会使用索引，如：
  

1. SELECT * FROM orders WHERE YEAR(order_date) = 2023;  -- 索引失效  

复制代码

优化方式：

1. SELECT * FROM orders WHERE order_date >= '2023-01-01';  

复制代码

3. 组合索引的最左前缀法则

• 组合索引 (col1, col2, col3)，查询时必须遵循最左前缀。
  
• 示例：
  

1. SELECT * FROM orders WHERE col1 = 1 AND col2 = 2;  -- 可用索引  
2. SELECT * FROM orders WHERE col2 = 2;  -- 索引失效  

复制代码

---

八、总结

• 慢查询分析 是优化的第一步，通过 EXPLAIN 和慢查询日志，可以有效定位性能瓶颈。
  
• 索引优化 是查询优化的关键，合理创建索引可以明显提拔查询速度。
  
• 分页与统计优化 能有效减少大数据量下的扫描范围，提拔响应速度。
  
• 复杂查询优化 需要结合具体场景，合理使用 UNION、JOIN 和覆盖索引计谋。
  

希望通过以上案例分享，帮助开发者在实际项目中有效优化 MySQL 查询，提拔系统团体性能。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/)

Powered by Discuz! X3.4