MySQL 查询优化详解

最新推荐文章于 2025-05-12 08:25:23 发布
最新推荐文章于 2025-05-12 08:25:23 发布
阅读量2.5k 收藏 33
点赞数 10
CC 4.0 BY-SA版权
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/yimeixiaolangzai/article/details/141471795

在使用 MySQL 数据库时，查询性能往往是影响应用程序整体性能的关键因素。通过对查询进行优化，可以显著提升数据库的响应速度和处理能力。本文将深入探讨 MySQL 查询优化的几个重要方面，包括查询分析与执行计划（EXPLAIN）、查询缓存的使用，以及 SQL 重写技巧。

一、查询分析与执行计划（EXPLAIN）

在优化查询之前，首先需要了解查询是如何执行的。MySQL 提供了 EXPLAIN 命令，可以帮助开发人员分析 SQL 语句的执行计划，从而识别潜在的性能瓶颈。

1. 使用 EXPLAIN 进行查询分析

EXPLAIN 命令的基本语法如下：

EXPLAIN SELECT * FROM table_name WHERE condition;

EXPLAIN 返回的结果通常包括以下几个重要字段：

  • id：查询中每个 SELECT 子句的标识符。id 值越大，优先级越低，表示这个部分的查询将被最后执行。
  • select_type：表示查询的类型，常见的值有 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
  • table：正在访问的表的名称。
  • type：连接类型，表示查询中表的访问方式。常见的类型有：
    • ALL：全表扫描，性能最差。
    • INDEX：全索引扫描，性能较差。
    • RANGE：范围扫描，通常用于带有范围条件的查询。
    • REF：非唯一索引扫描，性能较好。
    • EQ_REF：对于每个主键或唯一索引的值，只访问一行，性能优秀。
    • CONST：常量访问，性能最好。
  • possible_keys：查询中可能使用的索引。
  • key：实际使用的索引。如果该字段为 NULL，表示没有使用索引。
  • key_len：使用的索引长度，越短越好。
  • ref：显示索引的哪一列与表的哪个列进行比较。
  • rows：MySQL 估计查询将扫描的行数。该值越小，查询越快。
  • Extra：额外信息，比如是否使用了文件排序或临时表等。

2. 分析 EXPLAIN 输出示例

假设有如下查询语句：

SELECT * FROM Orders WHERE customer_id = 123 AND order_date BETWEEN '2023-01-01' AND '2023-01-31';

执行 EXPLAIN 后，可能得到如下输出：

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEOrdersRANGEidx_customer_orderidx_customer_order5NULL100Using where

在这个例子中：

  • type 为 RANGE，表明查询使用了范围扫描，这通常比全表扫描（ALL）或全索引扫描（INDEX）更高效。
  • key 显示 idx_customer_order 索引被使用，这表明查询正在利用索引来加快检索。
  • rows 显示 MySQL 预估需要扫描 100 行数据，这意味着查询性能可能比较好。

3. 优化建议

  • 如果 type 显示为 ALL，应该考虑增加适当的索引，以避免全表扫描。
  • 如果 possible_keys 不为空但 key 为空，说明 MySQL 没有选择最优的索引，可以通过调整查询或强制使用特定索引来优化。
  • 检查 Extra 字段中的信息，避免使用 Using filesort（文件排序）和 Using temporary（临时表），这两种操作通常会显著降低查询性能。

二、查询缓存

MySQL 查询缓存是一种优化机制，通过缓存查询结果，减少相同查询的执行时间。不过，MySQL 8.0 版本已经移除了查询缓存功能，因为在现代高并发的环境下，查询缓存的效率反而可能不如其他优化策略。

1. 查询缓存的工作原理

在 MySQL 5.7 及之前的版本中，查询缓存用于存储 SELECT 查询的结果。如果相同的查询再次执行，MySQL 会直接返回缓存的结果，而不需要重新执行查询语句。这可以显著加快查询速度，尤其是在数据更新频率较低的情况下。

查询缓存的配置可以通过以下参数控制：

  • query_cache_size：缓存的总大小。
  • query_cache_type：设置查询缓存的工作模式。可以是 ON（启用缓存）、OFF（禁用缓存）或 DEMAND（根据 SQL 语句的 SQL_CACHE 指令决定是否缓存）。
  • query_cache_limit：指定可以缓存的单个查询结果的最大大小。

2. 查询缓存的限制

查询缓存虽然可以提升查询性能，但也有一些限制：

  • 当表中的数据发生更新时，相关的缓存结果会失效，缓存需要重新生成，这在高并发写入的情况下可能会导致性能下降。
  • 查询缓存对复杂查询的效果不明显，尤其是涉及动态数据的查询。

3. 替代方案

由于查询缓存在高并发环境中的局限性，现代数据库更倾向于使用其他优化策略，如使用高效的索引、SQL 重写、以及应用层的缓存（如 Redis）来提升性能。

三、SQL 重写技巧

通过重写 SQL 语句，可以优化查询的执行方式，减少不必要的资源消耗，从而提升查询性能。以下是一些常见的 SQL 重写技巧：

1. 避免 SELECT *

尽量避免使用 SELECT *，而是明确指定需要的列。这不仅减少了传输的数据量，还能让 MySQL 更高效地使用索引。

原始查询：

SELECT * FROM Employees WHERE department_id = 1;

优化查询：

SELECT employee_id, employee_name FROM Employees WHERE department_id = 1;

2. 利用索引覆盖查询

索引覆盖查询（Covering Index）是指查询的所有字段都可以通过索引获取，而无需访问表数据本身。这可以显著减少 I/O 操作。

原始查询：

SELECT employee_id, department_id FROM Employees WHERE department_id = 1;

假设 department_id 上有索引，但没有覆盖所有查询字段。

优化查询：

ALTER TABLE Employees ADD INDEX idx_employee_department (employee_id, department_id);

这样，查询可以完全通过索引 idx_employee_department 完成，无需访问表数据。

3. 避免函数操作和隐式转换

在 WHERE 子句中使用函数或进行数据类型转换，会导致 MySQL 无法利用索引，从而进行全表扫描。

原始查询：

SELECT * FROM Orders WHERE YEAR(order_date) = 2023;

优化查询：

SELECT * FROM Orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

通过重写查询，消除了 YEAR() 函数操作，MySQL 可以使用 order_date 上的索引。

4. 拆分复杂查询

对于非常复杂的查询，尤其是包含多个子查询和联合操作的查询，考虑将其拆分为多个简单的查询，或者使用临时表。这样可以减少 MySQL 的查询优化器的压力，并可能提高查询效率。

原始查询：

SELECT * FROM Orders WHERE customer_id IN (SELECT customer_id FROM VIP_Customers);

优化查询：

SELECT customer_id FROM VIP_Customers INTO TEMPORARY TABLE TempVIP;
SELECT * FROM Orders INNER JOIN TempVIP ON Orders.customer_id = TempVIP.customer_id;

通过将子查询拆分为单独的查询，并使用临时表，可以提高查询的效率。

5. 使用适当的连接方式

在处理多表连接时，选择合适的连接方式可以显著影响查询性能。尽量使用 INNER JOIN 而不是 OUTER JOIN，除非必须返回没有匹配数据的记录。

原始查询：

SELECT * FROM Orders LEFT JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Customers.region = 'North';

优化查询：

SELECT * FROM Orders INNER JOIN Customers ON Orders.customer_id = Customers.customer_id WHERE Customers.region = 'North';

通过使用 INNER JOIN，可以减少不必要的数据处理，提高查询效率。

四、总结

MySQL 查询优化是提升数据库性能的关键，通过查询分析与执行计划（EXPLAIN），可以深入理解查询的执行过程，识别性能瓶颈。同时，虽然 MySQL 8.0 已移除查询缓存功能，但在早期版本中，查询缓存仍然是提升性能的一种方式。最后，使用 SQL 重写技巧，可以通过优化查询结构和减少资源消耗，显著提升查询效率。

在实际应用中，优化查询不仅仅依赖于单一的技巧，而是需要结合数据库结构、索引设计、执行计划等多个因素进行综合考虑。通过持续的监控和优化，可以确保数据库在高负载情况下依然能够高效运行。

Mysql查询优化
Skycrab
11-17 5069
Mysql优化 ##1：索引可以大幅度提高查询性能 1.1  缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说： a.有大量重复值、且经常有范围查询( > , =, b.经常同时存取多列，且每列都含有重复值可考虑建立组合索引; c.组合索引要尽量使关键查询形
MySQL 查询优化详解：从执行计划到实战优化
最新发布
weixin_42593797的博客
05-17 974
本文详细介绍了MySQL查询优化的核心方法论，涵盖查询分析、索引优化、语句调优、锁与事务优化以及架构级优化等多个方面。通过使用EXPLAIN和SHOW PROFILE工具，开发者可以精准定位查询性能瓶颈。索引设计遵循最左匹配、区分度优先和覆盖索引原则，避免常见索引失效场景。查询语句优化包括减少数据传输、优化JOIN顺序、避免深分页等。锁与事务优化强调减少锁持有时间和事务拆分。架构级优化则通过分区表、分库分表和读写分离应对大规模数据挑战。最后，通过实战案例展示了从慢查询到高效SQL的优化过程。
MySQL数据库查询优化
01-22
课程大纲： 第1课 数据库与关系代数 综述数据库、关系代数、查询优化技术 综述数据库调优技术 预计时间1小时 第2课 数据库查询优化技术总揽 综述查询优化技术范围，包括查询重用、查询重写规则、查询算法优化、并行查询优化等 综述逻辑查询优化，包括子查询的优化、视图重写、等价谓词重写、条件化简、连接消除、非SPJ的优化等 综述逻辑物理优化，包括单表扫描算法、两表连接算法、多表连接算法、基于代价的算法等 初步理解MySQL的查询执行计划。 预计时间1小时 第3课 查询优化技术理论与MySQL实践（一）------子查询的优化（一） 第4课 查询优化技术理论与MySQL实践（二）------子查询的优化（二） 从理论看，子查询包括的内容和范围，建立清晰的概念 从实践看，MySQL的子查询优化技术的内容和范围，明确掌握子查询优化手段 预计时间2小时，每小时一个课程段（子查询是SQL查询优化的重点内容，务必掌握好） 第5课 查询优化技术理论与MySQL实践（三）------视图重写与等价谓词重写 什么是视图重写？哪些类型的视图可以被优化？MySQL是怎么优化视图的？从而明白在MySQL中怎么写与视图相关的查询语句才能有好的效果？ 什么是等价谓词重写？MySQL中怎么写WHERE子句有利于提高查询效率？ 预计时间1小时 第6课 查询优化技术理论与MySQL实践（四）------条件化简 什么是条件化简？MySQL中对什么样的条件自动进行优化？如何写出可利用索引的条件语句？ 预计时间1小时 第7课 查询优化技术理论与MySQL实践（五）------外连接消除、嵌套连接消除与连接消除 连接方式有些什么类型？不同类型的连接又是怎么优化的？外连接优化的条件是什么？MySQL中怎么写出可优化的连接语句？MySQL是否支持嵌套连接消除？MySQL是否支持连接消除？MySQL中书写SQL连接查询语句时的优化技巧。 预计时间1小时 第8课 查询优化技术理论与MySQL实践（六）------数据库的约束规则与语义优化 数据库的参照完整性（CHECKt NULL等）。什么是语义优化？ MySQL是否支持语义优化？怎么利用语义优化的思路人工进行SQL语句的优化？ 预计时间1小时 第9课 查询优化技术理论与MySQL实践（七）------非SPJ的优化 什么是非SPJ优化？ 从理论看，GROUP BY、ORDER BY、LIMIT、DISTINCT等怎么被优化？ MySQL中：GROUP BY是怎么优化的？ORDER BY是怎么被优化？LIMIT是怎么被优化？DISTINCT是怎么被优化？ 非SPJ优化与索引的关系。 预计时间1小时 第10课 MySQL物理查询优化技术概述 从理论看，物理查询优化技术的范围。 从MySQL实践看，怎么利用物理查询优化技术对SQL查询语句调优？ 本节预计会承接第9课的部分内容。 预计时间1小时 第11课 MySQL索引的利用、优化 从MySQL索引的角度出发，看各种SQL查询语句的优化怎么进行？（以前都是从语句的角度看怎么优化，现在站在索引的角度去总结SQL查询语句的优化） 预计时间1小时 第12课 表扫描与连接算法与MySQL多表连接优化实践 MySQL的单表扫描算法。MySQL的两表连接算法。MySQL的多表连接算法。 MySQL的多表连接的优化技巧。 预计时间1小时 第13课 查询优化的综合实例（一）------TPCH实践（一） 第14课 查询优化的综合实例（一）------TPCH实践（二） 以TPC-H国际标准的22条查询语句为实例，综合前面课程的内容，把所学的知识用于实践，进行综合的实战演练。 预计时间2小时（每个课时为1个小时） 第15课 关系代数对于数据库的查询优化的指导意义------查询优化技术总结 再次回到理论，从理论的高度总结关系代数理论与MySQL查询优化实践的关系。真正认识、掌握MySQL的查询优化技术，大步流星步入查询优化的高手之列。
MySQL性能优化总结
梁靓凉的博客
03-14 1256
优化需要从多个维度进行优化 其中包括四个：SQL语句及索引、表结构设计、系统配置、硬件配置。 其中SQL语句相关的优化手段是最为重要的 硬件配置 硬件方面的优化可以有对磁盘进行扩容、将机械硬盘换为SSD 等等。但这个优化手段成本最高，见效也较小。 系统配置 系统选择 系统通常使用Linux作为服务端的系统，本地开发的话可以随意。Linux 系统版本和 MySQL 版本选择稳定的版本即可。 保证从内存读取 MySQL 会在内存中保存一定的数据，通过 LRU（最近最少使用）算法将不常访问的数据保存在硬盘文件中
MySQL慢查询克星，15个SQL优化技巧让查询起飞
ly853602的博客
05-12 962
掌握MySQL查询优化的精髓，让你的数据库查询飞起来！
Mysql-查询优化
04-22 6037
一、查询慢的原因 在Mysql查询过程成，出现性能问题可能存在方方面面的原因，主要包括如下几种：网络、CPU、IO、上下文切换、系统调用、生成统计信息、锁等待时间等。 二、如何优化数据访问 1、减少访问数据量 确认应用程序是否存在检索大量超过需要的数据。 mysql> explain select id, phone from test_user_copy limit 30000,1; +----+-------------+----------------+------------+--
MySQL查询优化
qq_28014809的博客
04-28 5499
索引是什么 索引是数据库存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其当表中的数据量越来越大时。 MySQL索引数据结构
Mysql查询优化详解（含示例）
05-06
### MySQL查询优化详解 #### 一、使用EXPLAIN分析查询 **EXPLAIN** 是MySQL中一个非常有用的工具，用于分析SQL语句的执行计划。它能够揭示MySQL如何处理查询，帮助我们理解查询的效率和潜在的性能瓶颈。下面是一些...
MySQL查询优化详解及执行计划输出解析
本文主要总结了两篇关于 MySQL 运行原理的文章，分别是《MySQL 是怎样运行的：从根儿上理解 MySQL（下）.pdf》和《查询优化的百科全书——Explain 详解（下）》。这两篇文章详细介绍了 MySQL 的执行计划输出以及 ...
Mysql Limit 分页查询优化详解
09-10
总的来说，MySQL LIMIT分页查询的优化主要集中在减少不必要的行扫描、利用索引以及采用更适合大数据量的查询策略。通过这些方法，可以显著提升分页查询的性能，尤其是对于那些需要跳过多行的情况。在实际应用中，应...
MySQL性能优化详解
04-24
# MySQL性能优化详解 ## 一、MySQL性能优化的重要性及概览 ### 1.1 MySQL性能优化简介 在Web应用程序架构中，数据持久层（通常指关系数据库）扮演着核心角色，对整个系统的性能至关重要。MySQL作为一种广泛使用的...
mysql in查询不要去重_MySQL 查询优化
weixin_42514777的博客
01-27 5101
使用索引规则全值匹配：查询的字段按照顺序在索引中都可以匹配到，最佳左前缀法则：指的过滤条件要使用索引必须按照索引建立时的顺序依次满足 ， 一旦跳过某个字段 ， 索引后面的字段都无法被使用。不在索引列上做计算：不在索引列上做任何操作(计算、函数、(自动 or 手动)类型转换「尤其注意 varchar 和 int」)，会导致索引失效而转向全表扫描。索引列上不能有范围查询：将可能做范围查询的字段的索引顺...
MySQL优化-查询优化
qq_51398439的博客
06-02 2519
查询优化是数据库优化中的一个重要方面，可以提高查询的效率和响应速度，从而提升数据库系统的整体性能。以下是一些查询优化的总结：使用索引：索引可以加速查询的速度，因此需要在经常使用的字段上创建索引，避免在 where 子句中使用不等于避免在 where 子句中使用 or 操作符，避免在 where 子句中使用函数，避免在 where 子句中使用模糊查询。避免全表扫描：尽量避免使用不带条件的 select 语句，因为这会导致数据库执行全表扫描，而且会消耗大量的系统资源。
MYSQL-查询优化
qq_45939519的博客
02-12 601
MYSQL查询优化
mysql优化查询
标配的小号
07-28 287
使用索引查询MariaDB [test]> explain select * from te where id=22; #在没有增加索引情况下，rows为7，即查询行数 +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值