MySQL 查询优化案例分享

devtools/2024/12/26 22:46:49/

在日常开发中,SQL 查询性能直接影响到系统的响应速度和用户体验。随着数据量的增长,慢查询可能成为系统的瓶颈。本文将通过实际案例,分享几种常见的 MySQL 查询优化方法,帮助开发者快速定位和优化慢查询,提升数据库性能。


一、慢查询定位与分析

1. 开启慢查询日志

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

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

示例输出(日志格式):

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

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

问题描述

查询订单表 orders 中近一年的订单数据,查询速度缓慢。

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

分析:

  • 订单表数据量庞大,每次查询都进行全表扫描(EXPLAIN 显示 type=ALL)。
  • order_date 列没有建立索引。

优化方案:

  1. order_date 字段添加索引:
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 操作,执行时间过长。

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

分析:

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

优化方案:

  1. 为关联字段添加索引:
ALTER TABLE users ADD INDEX idx_user_id (id);  
  1. 优化 JOIN 查询,确保主表和从表都能利用索引:
EXPLAIN SELECT o.order_id, u.username   
FROM orders o   
JOIN users u ON o.user_id = u.id   
WHERE o.order_date > '2023-01-01';  

优化效果:

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

四、案例 3:COUNT 优化

问题描述

统计用户订单总数,使用 COUNT(*) 查询时速度缓慢。

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

分析:

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

优化方案:

  1. 使用 COUNT(索引字段) 替代 COUNT(*)
SELECT COUNT(order_id) FROM orders WHERE user_id = 1001;  
  1. 如果查询量巨大,可考虑维护统计表:
CREATE TABLE order_summary AS   
SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id;  

优化效果:

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

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

问题描述

分页查询性能较差,用户访问较深的分页时响应时间缓慢。

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;  
  1. 使用 ID 范围方式优化分页:
SELECT * FROM orders   
WHERE order_id > (SELECT order_id FROM orders ORDER BY order_date LIMIT 100000, 1)  
LIMIT 10;  

优化效果:

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

六、案例 5:OR 查询优化

问题描述

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

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

分析:

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

优化方案:

  • OR 拆分为 UNION 查询:
SELECT * FROM orders WHERE user_id = 1001   
UNION   
SELECT * FROM orders WHERE order_date > '2023-01-01';  

优化效果:

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

七、索引优化总结

1. 创建合适的索引
  • 经常用于 WHEREJOIN 的字段应建立索引。
  • 避免在低选择性的字段上建立索引(如性别字段)。
2. 避免索引失效的情况
  • 使用函数计算的字段不会使用索引,如:
SELECT * FROM orders WHERE YEAR(order_date) = 2023;  -- 索引失效  

优化方式:

SELECT * FROM orders WHERE order_date >= '2023-01-01';  
3. 组合索引的最左前缀法则
  • 组合索引 (col1, col2, col3),查询时必须遵循最左前缀。
  • 示例:
SELECT * FROM orders WHERE col1 = 1 AND col2 = 2;  -- 可用索引  
SELECT * FROM orders WHERE col2 = 2;  -- 索引失效  

八、总结

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

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


http://www.ppmy.cn/devtools/145646.html

相关文章

SpringBoot 整合 Avro 与 Kafka

优质博文:IT-BLOG-CN 【需求】:生产者发送数据至 kafka 序列化使用 Avro,消费者通过 Avro 进行反序列化,并将数据通过 MyBatisPlus 存入数据库。 一、环境介绍 【1】Apache Avro 1.8;【2】Spring Kafka 1.2&#xf…

VScode 查看linux 内核代码

0,安装c.c 1,查看linux 目录下的linux代码,安装remote ssh 2, 输入服务器IP 3 选择服务器为linux

2025考研加油!Jing也加油哦!

一恍惚,离自己考研初试过去都两年了!研究生生活也过去一大半!借此机会也总结一下研究生这一段生活——研究生生活(上) About I 昨天实验室聚餐,作为老生欢迎新生,啊啊啊,真的没想到…

Android笔试面试题AI答之Android基础(3)

文章目录 1.谈一谈 Android 的安全机制一、系统架构层面的安全设计二、核心安全机制三、其他安全机制与措施 2.Android 的四大组件是哪四大?3.Android 的四大组件都需要在清单文件中注册吗?4.介绍几个常用的Linux命令一、文件和目录管理二、用户和权限管…

STM32-笔记7-继电器定时开闭

1、复制02项目,重命名08-继电器定时开闭 打开项目工程 在\Drivers\BSP\该路径下,新建alarm文件夹,该文件夹下里面包含alarm.c和alarm.h文件 加载进该项目中 为什么这里使用的是 这个单词,而不是继电器(relay&#…

TypeScript 流程控制语句

文章目录 前言一、if - else 与 else - if 条件判断二、switch 语句的使用及注意事项三、for 循环(常规、for - in、for - of)(一)常规 for 循环(二)for - in 循环(三)for - of 循环…

时间轮在 Netty , Kafka 中的设计与实现

本文基于 Netty 4.1.112.Final , Kafka 3.9.0 版本进行讨论 在业务开发的场景中,我们经常会遇到很多定时任务的需求。比如,生成业务报表,周期性对账,同步数据,订单支付超时处理等。针对业务场景中定时任务逻辑复杂&…

Artec Space Spider助力剑桥研究团队解码古代社会合作【沪敖3D】

挑战:考古学家需要一种安全的方法来呈现新出土的陶瓷容器,对比文物形状。 解决方案:Artec Space Spider, Artec Studio 效果:本项目是REVERSEACTION项目的一部分,旨在研究无国家社会中复杂的古代技术。研究团队在考古地…