MySQL中常用的查询优化技巧有哪些 MySQL查询性能提升方法大全

絕刀狂花

发布时间：2025-08-08 11:24:02

954人浏览过

来源于php中文网

原创

提升mysql查询性能的核心在于减少数据库负担并确保其高效执行，需从索引优化、sql语句调整、服务器参数配置及架构扩展四方面入手。1. 善用索引：遵循最左前缀原则创建复合索引，优先使用b-tree或哈希索引，利用覆盖索引避免回表，但避免过度创建索引以减少写开销。2. 优化sql语句：避免select ，明确指定所需列；杜绝在where子句中对列使用函数或类型转换；优化join顺序，小表驱动大表；合理使用union all替代or；用between替代date()函数；避免大偏移的limit分页。3. 合理配置服务器参数：设置innodb_buffer_pool_size为物理内存的50%-80%以提升缓存命中率；调整sort_buffer_size、join_buffer_size减少磁盘i/o；根据负载设置innodb_log_file_size；适当增大tmp_table_size防止内存临时表转磁盘；mysql 8.0前版本谨慎使用query_cache_size。4. 架构层面优化：实施读写分离以分散读压力；在单机瓶颈时采用分库分表（垂直或水平）提升扩展性；引入redis等缓存层降低数据库负载；使用proxysql等数据库代理实现连接池与sql路由。诊断性能瓶颈应优先使用explain分析执行计划，关注type（避免all）、key（确保使用索引）、rows（扫描行数越少越好）和extra（避免using filesort、using temporary，优先using index）。常见慢sql包括select 、where中使用函数、like %keyword、or连接多列、大in列表、未用索引的order by/group by，均需通过改写sql或添加索引规避。最终优化路径应循序渐进：先sql与索引，再参数调优，最后架构升级，每一步都需基于实际性能数据决策，确保系统稳定高效运行。

MySQL中常用的查询优化技巧有哪些 MySQL查询性能提升方法大全

MySQL查询性能提升，说到底，就是让数据库少干活、干对活。核心在于善用索引、优化SQL语句、合理配置服务器参数，以及在必要时进行架构层面的调整。这不仅仅是技术活，更是一种对数据流动和系统瓶颈的直觉判断。

查询优化，在我看来，很多时候就像是在玩一场精密的拼图游戏。你手上的每一块，无论是索引、SQL写法还是服务器配置，都得恰到好处地放置，才能拼出性能最优的那张图。

解决方案

提升MySQL查询性能，首先要做的就是理解你的查询到底在干什么，它慢在哪里。然后，从以下几个核心点着手：

索引的艺术与科学： 索引是提升查询速度的“魔法”。B-tree索引最常见，适用于等值、范围查询和排序。哈希索引则用于精确匹配。理解复合索引的“最左前缀原则”至关重要，它能帮你避免创建冗余索引。另外，覆盖索引（covering index）是个高级技巧，如果查询所需的所有列都能从索引中获取，MySQL就不需要回表查询，速度自然飞快。但索引并非越多越好，它会增加写操作的开销，所以要权衡。
SQL语句的精雕细琢： 这是最直接的优化点。
- 避免全表扫描： 确保
```
WHERE
```
  子句中的条件列有索引，且能有效利用索引。
- *告别`SELECT `：** 只选择你真正需要的列，减少数据传输和解析的负担。
- 优化
  JOIN
  操作：总是用小结果集驱动大表，即
```
LEFT JOIN
```
  时左边放小表，
```
INNER JOIN
```
  时MySQL会自动优化，但你心里得有数。确保
```
JOIN
```
  条件列有索引。
- WHERE
  子句的玄机：避免在
```
WHERE
```
  子句的列上使用函数、进行类型转换或使用
```
!=
```
  、
```
OR
```
  （在某些情况下）。例如，
```
WHERE DATE(create_time) = '2023-01-01'
```
  会让索引失效，更好的做法是
```
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59'
```
  。
- GROUP BY
  和
  ORDER BY
  ：如果可能，让它们利用索引来完成排序或分组，避免额外的文件排序（Using filesort）。
- LIMIT
  的陷阱：对于
```
LIMIT offset, count
```
  这种分页查询，当
```
offset
```
  非常大时，性能会急剧下降。可以考虑先通过索引定位到目标行ID，再
```
JOIN
```
  回原表获取数据，或者利用上一次查询的最后一条记录的ID来优化。
- OR
  与
  UNION ALL
  ：当
```
OR
```
  连接的条件涉及不同列且无法利用单个索引时，考虑拆分成多个
```
SELECT
```
  语句，用
```
UNION ALL
```
  连接。
- IN
  与
  EXISTS
  ：对于子查询，通常小表用
```
IN
```
  ，大表用
```
EXISTS
```
  ，但具体情况要看优化器。
数据库结构与范式： 合理的数据类型选择（比如用
```
TINYINT
```
而不是
```
INT
```
如果范围允许），以及适当的范式化或反范式化设计，都能对查询性能产生深远影响。
服务器参数的调优： 比如
```
innodb_buffer_pool_size
```
（InnoDB最重要的缓存参数）、
```
query_cache_size
```
（虽然在MySQL 8.0中被移除，但早期版本很重要）、
```
sort_buffer_size
```
、
```
join_buffer_size
```
等。这些参数直接影响MySQL处理查询的内存使用和效率。
架构层面的考量： 当单机性能达到瓶颈时，读写分离、分库分表是常用的扩展手段。读写分离可以分散读请求压力，分库分表则能将数据分散到多个节点，突破单机存储和并发的限制。

如何高效分析MySQL查询性能瓶颈？

要诊断MySQL查询的性能问题，最核心的工具非

EXPLAIN

莫属。它就像一个X光机，能透视你的SQL语句在执行时的内部机制，告诉你MySQL打算如何执行这条查询，以及它可能遇到的问题。

当我面对一个慢查询时，我首先会把这条SQL语句扔给

EXPLAIN

。它的输出结果虽然看起来有点复杂，但掌握几个关键字段，就能快速定位问题：

id
: 查询的序列号，同一
```
SELECT
```
语句的查询会共享一个
```
id
```
。
select_type
: 查询的类型，比如
```
SIMPLE
```
（简单查询）、
```
PRIMARY
```
（最外层查询）、
```
SUBQUERY
```
（子查询）、
```
DERIVED
```
（派生表，如
```
FROM
```
子句中的子查询）等。了解这个能帮助你理解复杂查询的执行顺序。
table
: 当前操作的表名。
type
: 这是最重要的字段之一，表示MySQL访问数据的方式。
- ```
system
```
  >
```
const
```
  >
```
eq_ref
```
  >
```
ref
```
  >
```
range
```
  >
```
index
```
  >
```
ALL
```
  。
- 目标是达到
```
const
```
  、
```
eq_ref
```
  、
```
ref
```
  或
```
range
```
  。
- ```
ALL
```
  意味着全表扫描，这是性能杀手，通常需要优化。
- ```
index
```
  表示全索引扫描，虽然比
```
ALL
```
  好，但如果索引很大，效率也低。
possible_keys
: MySQL在执行查询时可能用到的索引。
key
: MySQL最终决定使用的索引。如果
```
key
```
是
```
NULL
```
，那表示没有使用索引。
key_len
: 使用的索引的长度。这对于复合索引非常有用，能看出索引的哪部分被使用了。
ref
: 表示使用哪个列或常量与
```
key
```
一起从表中选择行。
rows
: MySQL估计要扫描的行数。这个值越小越好。
Extra
: 额外信息，这里面藏着很多性能线索。
- ```
Using filesort
```
  ：表示MySQL需要对结果进行排序，而无法利用索引排序。这通常是性能瓶颈。
- ```
Using temporary
```
  ：表示MySQL需要创建临时表来处理查询，比如
```
GROUP BY
```
  或
```
DISTINCT
```
  操作。这也是性能瓶颈。
- ```
Using index
```
  ：表示使用了覆盖索引，这是非常好的情况，数据直接从索引中获取，无需回表。
- ```
Using where
```
  ：表示
```
WHERE
```
  子句被用于限制返回的行。
- ```
Using index condition
```
  ：MySQL 5.6引入的索引条件下推优化，在存储引擎层进行过滤，减少回表次数。

举个例子，如果我看到

type

是

ALL

，

rows

很大，并且

Extra

里有

Using filesort

或

Using temporary

，那我就知道，要么是索引没建好，要么是SQL写法有问题，或者两者兼有。我的优化方向就很明确了：先考虑加合适的索引，如果不行，就调整SQL语句，看看能否避免这些额外的操作。

Figma Slides

Figma Slides 是 Figma 发布的PPT制作和演示文稿生成工具，可以帮助创建、设计、定制和分享演示文稿

下载

MySQL查询中常见的慢SQL写法有哪些，如何避免？

在日常开发中，我们总会不经意间写出一些“慢SQL”，它们就像是系统中的隐形杀手，慢慢地拖垮整个应用的响应速度。识别并避免这些写法，是优化查询的关键一环。

*`SELECT
```
：** 这是最常见的“懒惰”写法。我个人习惯是，哪怕只是需要所有字段，也会明确列出字段名。为什么？因为
```
SELECT *`会取出所有列的数据，包括那些你根本不需要的大文本（BLOB/TEXT）或JSON字段，这不仅增加了网络传输开销，还可能导致MySQL无法使用覆盖索引。
- 避免： 明确指定所需列，如
```
SELECT id, name, age FROM users WHERE ...
```
  。
OR
连接的条件：当
```
OR
```
连接的两个或多个条件涉及不同的列时，MySQL往往难以有效利用索引，甚至可能导致全表扫描。
- 避免： 考虑将
```
OR
```
  拆分成多个
```
SELECT
```
  语句，然后使用
```
UNION ALL
```
  连接。
  - 慢：
```
SELECT * FROM users WHERE status = 1 OR age > 30;
```
  - 优：
```
SELECT * FROM users WHERE status = 1 UNION ALL SELECT * FROM users WHERE age > 30 AND status != 1;
```
    (注意去重，如果需要)
LIKE %keyword
或
LIKE keyword%
：前者（以通配符开头）会导致索引失效，因为索引是按照从左到右的顺序构建的。后者（以通配符结尾）则可以利用索引。
- 避免： 如果业务允许，尽量使用
```
LIKE keyword%
```
  。如果必须使用
```
%keyword%
```
  ，考虑使用全文索引（Full-Text Index）或引入Elasticsearch、Solr等外部搜索服务。
在
WHERE
子句的列上使用函数或进行类型转换：任何对列本身进行操作的函数（如
```
DATE()
```
,
```
SUBSTR()
```
,
```
CONCAT()
```
等）或隐式/显式类型转换，都会导致MySQL无法使用该列上的索引。
- 慢：
```
SELECT * FROM orders WHERE DATE(order_time) = '2023-01-01';
```
- 优：
```
SELECT * FROM orders WHERE order_time >= '2023-01-01 00:00:00' AND order_time < '2023-01-02 00:00:00';
```
不恰当的
JOIN
顺序：虽然MySQL优化器通常会尝试找到最佳的
```
JOIN
```
顺序，但如果表的数据量差异巨大，或者统计信息不准确，优化器也可能“犯错”。
- 避免： 通常遵循“小表驱动大表”的原则。对于
```
LEFT JOIN
```
  ，左边的表应该是结果集较小的表。对于
```
INNER JOIN
```
  ，理论上哪个表驱动哪个表不影响结果，但实际上，让结果集较小的表作为驱动表，可以减少循环次数。
IN
子句中的大量值：当
```
IN
```
子句中的列表过长时（比如成千上万个ID），查询性能会下降。
- 避免： 考虑将这些ID放入一个临时表，然后与原表进行
```
JOIN
```
  操作。或者，如果这些ID来自另一个查询，考虑使用
```
EXISTS
```
  。
ORDER BY
和
GROUP BY
不使用索引：当
```
ORDER BY
```
或
```
GROUP BY
```
的列没有索引，或者索引无法被有效利用时，MySQL会进行文件排序（
```
Using filesort
```
）或创建临时表（
```
Using temporary
```
），这都是非常耗时的操作。
- 避免： 确保
```
ORDER BY
```
  和
```
GROUP BY
```
  的列有合适的索引，并且这些列的顺序与索引的顺序匹配。

这些“反模式”的出现，很多时候是因为开发时只关注了功能实现，而忽略了数据量增长后可能带来的性能问题。多用

EXPLAIN

，多思考数据的访问模式，很多问题就能提前避免。

除了SQL和索引，还有哪些MySQL配置或架构层面的优化手段？

当SQL语句和索引的优化已经做到极致，但查询性能依然不尽如人意时，我们就需要把目光投向更广阔的层面：MySQL服务器的配置参数，以及数据库的整体架构设计。这就像是，你把车子的发动机（SQL）和轮胎（索引）都调校好了，但如果路况（服务器配置）太差，或者你需要跑长途（架构扩展），那就得考虑修路或换交通工具了。

MySQL配置参数的精细调整：
- innodb_buffer_pool_size
  ：这是InnoDB存储引擎最重要的配置参数，没有之一。它决定了InnoDB缓存数据和索引的内存大小。如果你的数据库是InnoDB为主，这个值应该设置得尽可能大，通常是服务器物理内存的50%到80%。我的经验是，当你发现
```
Innodb_buffer_pool_read_requests
```
  很高，而
```
Innodb_buffer_pool_reads
```
  也相对较高时，就说明缓存命中率不高，需要增大这个参数。
- innodb_log_file_size
  ： InnoDB重做日志文件的大小。增大这个值可以减少I/O操作，但也会增加崩溃恢复的时间。需要根据实际负载和恢复时间要求进行权衡。
- query_cache_size
  (MySQL 8.0已移除)：在早期版本中，查询缓存可以缓存完整的查询结果。对于读多写少的场景有一定帮助。但由于其锁粒度大，在高并发写入时反而会成为瓶颈。这也是为什么MySQL 8.0将其移除的原因。如果你的MySQL版本还有这个参数，并且应用写操作频繁，建议禁用或设置得很小。
- tmp_table_size
  和
  max_heap_table_size
  ：这两个参数控制内存中临时表的大小。当MySQL需要创建临时表（比如处理
```
GROUP BY
```
  或
```
DISTINCT
```
  操作）时，如果数据量超过这个限制，就会把内存临时表转储到磁盘上，导致性能急剧下降。适当增大这两个参数可以减少磁盘I/O。
- sort_buffer_size
  和
  join_buffer_size
  ：分别用于排序操作和连接操作的缓冲区大小。如果
```
EXPLAIN
```
  结果显示
```
Using filesort
```
  或
```
Using temporary
```
  ，适当增大这些参数可能有助于减少磁盘I/O。
- max_connections
  ：最大连接数。设置过小会导致连接失败，过大则可能耗尽服务器资源。需要根据服务器性能和并发需求进行调整。
架构层面的优化与扩展：
- 读写分离（Read-Write Splitting）： 这是最常见的扩展方案。将数据库分为主库（处理所有写操作和部分读操作）和从库（只处理读操作）。应用程序将写请求发送给主库，读请求分发给一个或多个从库。这极大地分散了读请求的压力，提升了整体吞吐量。它要求你的应用能够识别读写操作并路由到不同的数据库连接。
- 分库分表（Sharding）： 当单机的存储容量或并发处理能力达到极限时，分库分表是唯一的出路。它将一个大表或整个数据库的数据分散到多个独立的数据库实例上。
  - 垂直分库： 按照业务功能将不同的表放到不同的数据库实例上。例如，用户表、订单表、商品表分别放在不同的数据库。
  - 水平分表： 将一张大表的数据按照某个规则（如用户ID哈希、时间范围）分散到多个数据库或多个表中。这需要复杂的路由逻辑和数据迁移策略。
  - 分库分表虽然能解决极致的扩展性问题，但也会带来分布式事务、跨库查询、数据一致性维护等一系列复杂挑战。
- 缓存层（Caching Layer）： 在应用和数据库之间引入Redis、Memcached等缓存系统，缓存热点数据。当请求到达时，优先从缓存中读取数据，只有缓存中没有或者数据过期时才去查询数据库。这能极大地降低数据库的压力，特别是对于读多写少的应用。
- 数据库代理（Database Proxy）： 引入如MyCAT、ProxySQL等数据库中间件，它们可以提供连接池、读写分离、负载均衡、SQL审计等功能，在不修改应用代码的情况下，提升数据库的可用性和性能。