MySQL 查询优化案例分享

server/2024/12/27 2:10:36/

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

相关文章

细说STM32F407单片机轮询方式读写SPI FLASH W25Q16BV

目录 一、工程配置 1、时钟、DEBUG 2、GPIO 3、SPI2 4、USART6 5、NVIC 二、软件设计 1、FALSH (1)w25flash.h (2) w25flash.c 1)W25Q16基本操作指令 2)计算地址的辅助功能函数 3)器…

Berlandesk 注册系统算法实现与解析

一、引言 在不久的将来,一款名为 “Berlandesk” 的电子邮件服务将在 Berland 地区开放,站点管理员希望尽快启动项目,其中很关键的一部分就是实现站点注册系统的原型。本文将详细介绍这个注册系统的功能要求以及使用 Python 语言实现的具体代…

面试真题:Integer(128)引发的思考

引言 在 Java 编程语言中,数据类型的使用至关重要。作为一种静态类型语言,Java 提供了丰富的基本数据类型和对应的包装类。其中,Integer 类是 int 类型的包装类,承载着更复杂的功能,如缓存、装箱和拆箱等。掌握 Integ…

Hive与HBase的区别有哪些

Hive 是基于 Hadoop 的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供简单的sql 查询功能,可以将 sql 语句转换为 MapReduce 任务进行运行。 HBase 是 Hadoop 的数据库,一个分布式、可扩展、大数据的存储。 …

选择屏幕的用法

**************************定义控件*********************************** SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-002. SELECT-OPTIONS bukrs FOR iloa-bukrs . "公司代码 SELECT-OPTIONS swerk FOR iloa-swerk OBLIGATORY . "工厂 SELECT-O…

【作业】LSTM

目录 习题6-4 推导LSTM网络中参数的梯度, 并分析其避免梯度消失的效果 ​编辑 习题6-3P 编程实现下图LSTM运行过程 1. 使用Numpy实现LSTM算子 2. 使用nn.LSTMCell实现 3. 使用nn.LSTM实现 参考链接 习题6-4 推导LSTM网络中参数的梯度, 并分析其…

workman服务端开发模式-应用开发-后端api推送修改二

需要修改两个地方&#xff0c;第一个是总控制里面的续token延时&#xff0c;第二个是操作日志记录 一、总控续token延时方法 在根目录下app文件夹下controller文件夹下Base.php中修改isLoginAuth方法&#xff0c;具体代码如下&#xff1a; <?php /*** 总控制* User: 龙哥…

dubbo2.7.23注册中心、配置中心、元数据中心

一、配置中心 抽象接口&#xff1a;DynamicConfiguration 具体实现类&#xff1a;NacosDynamicConfiguration ZookeeperDynamicConfiguration 示例&#xff1a;zookeeper://10.6.32.92:2181/ConfigCenterConfig?checktrue&clientcurator&config-filedubbo.propertie…