MySQL 查询优化案例分享

news/2024/12/28 20:56:29/

在日常开发中,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/news/1558879.html

相关文章

React 第十九节 useLayoutEffect 用途使用技巧注意事项详解

1、概述 useLayoutEffect 是useEffect 的一个衍生版本,只是他们的执行时机不同 useLayoutEffect 用于在DOM更新执行完成之后,浏览器渲染绘制之前执行,这会阻塞浏览器的渲染; useEffect 的执行时机是在组件首次渲染和更新渲染之后…

Android APP 集成本地大模型 LLM

这篇文章介绍如何在Android项目中集成大模型LLM,并展示大模型 text-to-text 结果。主要依赖如下2项: MediaPipeGemma 2BMediaPipe Google 在2017年发布了 TensorFlow Lite, 它是一个用于在Mobile和IoT上进行 ML 推断的轻量级框架。主要用于将…

【每日学点鸿蒙知识】hap安装报错、APP转移账号、import本地文件、远程包构建问题、访问前端页面方法

1、HarmonyOS 打包生产的hap,安装报错,如何解决? 通过build打包的hap文件,通过hdc进行安装,提示错误,证书信息确认是release。如何解决? release证书无法用于安装。 2、HarmonyOS 普通账户下…

深入解析Android Framework中的android.location包:架构设计、设计模式与系统定制

深入解析Android Framework中的android.location包:架构设计、设计模式与系统定制 目录 引言android.location包概述核心类解析 LocationManagerLocationProviderLocationCriteriaGpsStatusGpsStatus.ListenerLocationListener位置服务的工作原理位置信息的获取与处理GPS状态…

Linux零基础速成篇一(理论+实操)

前言:本教程适合Linux零基础学习,也适合Linux期末考试的小伙伴,从头到尾理论与实操相结合,让你快速对Linux进行了解和掌握。 一、Linux概述 为什么要学习Linux操作系统? 完全免费-开源 任何用户均可下载使用 安全…

STM32 高级 谈一下IPV4/默认网关/子网掩码/DNS服务器/MAC

首先可以通过 winr->输入cmd->输入ipconfig 命令可以查看计算机的各种地址 IPV4:是互联网协议第 4 版(Internet Protocol version 4)所使用的地址。它是一个 32 位的二进制数字,通常被分为 4 个 8 位的部分&#xff…

Python的`turtle`库来模拟满屏烟花效果

以下是使用Python的turtle库来模拟满屏烟花效果的代码示例,运行代码后会在一个图形窗口中呈现出不断绽放的烟花场景,希望你能喜欢哦。 import turtle import random import time# 初始化屏幕 screen = turtle.Screen() screen.bgcolor("black") screen.title(&quo…

USB免驱全协议IC卡读写器 QT程序开发

15693卡片,通常指的是符合ISO/IEC 15693标准的非接触式智能卡或RFID卡。以下是对15693卡片的详细介绍: 一、基本概述 15693卡片是基于ISO/IEC 15693国际标准设计的非接触式智能卡,该标准定义了用于非接触式集成电路卡(即RFID卡&…