处理MySQL慢查询的正确姿势

发布时间：2025-11-02 14:02:01 来源：科技前瞻 作者：应用开发

写在前面

在业务项目发展过程中，处理查询我们常常会面对要处理 MySQL 慢查询问题，确姿那我们应该如何分析解决问题呢?处理查询

部分同学在处理 MySQL 慢查询时候主要思路是加索引来解决，加索引确实是确姿一个很好的解决问题的手段，但不是处理查询全部。既然慢查询是确姿问题，那就需要明确问题发生原因，处理查询和解决问题路径分析。确姿我们一起来 get 下 MySQL 慢查询的处理查询正确姿势。

查询 SQL 执行到底经历了什么?确姿

首先需要明确：一个查询 SQL 的执行到底经历了什么?

数据库执行 sql 的大致流程如下：

具体执行过程可能会因 MySQL 服务器具体配置和执行场景有一些差异。

情况如下：

查询 SQL 为什么会慢?

我们可以把查询 SQL 执行看做是一个任务的话，那它是由一些列子任务组成的，每个子任务都存在一定的时间消耗。通常情况下，导致慢查询最根本的问题就是访问的数据太多，导致查询不可避免的需要筛选大量的数据。

面对慢查询，我们需要注意以下两点：

查询了过多不需要的数据

MySQL 并不是只返回需要的数据，实际上会返回全部结果集再进行计算。

尤其是多表关联查询 select * 的情况，我们是不是真的需要全部的WordPress模板列呢?如果不是，那我们直接指定对应字段就好了。

例如我们要查询用户关联订单下的商品信息，如下所示：

SELECT

FROM users

LEFT JOIN orders ON orders.user_id = users.user_id

LEFT JOIN goods ON goods.good_id = orders.good_id

这将返回三个表的全部数据列，可以调整为仅取需要的列：

SELECT goods.title, goods.description

FROM users

LEFT JOIN orders ON orders.user_id = users.user_id

LEFT JOIN goods ON goods.good_id = orders.good_id

取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的 I/O、内存和 CPU 的消耗。

扫描了额外的记录

此种情况大部分属于索引应用不当造成的(包括：该建的索引没有建，或者未应用到最佳索引)。

实例表结构如下：

CREATE TABLE `test_table` (

`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,

`name` varchar(32) DEFAULT NULL,

`desc` varchar(32) DEFAULT NULL,

`age` int(16) DEFAULT NULL,

KEY `idx_age` (`age`)

示例查询 SQL 执行计划：

应用索引 idx_age 后， 预估访问 1 行数据，如下图所示：

如删除有效索引后则会变成全表扫描(ALL)，预估需要扫描 121524 条记录才能完成这个查询，如下图所示：

发现了慢查询之后，关于如何定位问题发生原因，最常用的方法就是利用 EXPLAIN 关键字模拟查询优化器执行查询 SQL，从而知道 MySQL 是如何处理你的查询 SQL，通过执行计划来分析性能瓶颈。

通常我们使用 EXPLAIN，源码库会得到如下的执行计划信息：

关于各字段含义，大家可以通过检索自行了解，在此就不再过多赘述。

关于定位分析问题，关键看如下几点。

表示查询类型，用于区别普通查询、联合查询、子查询等复杂查询。

显示查询使用类型，从好到差依次为：

system > const > eq_ref > ref > range > index > all

分别指可能应用的索引和实际应用的索引。

注意：查询中若使用了覆盖索引(select 后要查询的字段刚好和创建的索引字段完全相同)，则该索引仅出现在 key 列表中。

大致估算出找到所需记录所需要读取的行数(从效率上来讲，数值越小越好)。

重要的额外信息。包含 MySQL 解决查询的详细信息，也是关键参考项之一。

几种实用解决方案

我们通过 EXPLAIN 关键字模拟查询优化器执行查询 SQL，发现了慢查询问题原因，那看看如何才能有效解决呢?

推荐几种较为实用的解决方案给大家。

优化数据结构

选择索引的数据类型

MySQL 支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。

通常来说，可以遵循以下一些指导原则。

越小的数据类型通常在磁盘、内存和 CPU 缓存中都需要更少的空间，处理起来更快。

整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在 MySQL 中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间;以及用整型数据类型存储 IP 地址。

应该指定列为 NOT NULL，在 MySQL 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。

你可以用 0、一个特殊的值或者一个空串代替 NULL 值。

范式与反范式

范式化模型要求满足下面三大范式：

1. 数据库表中每个字段只包含最小的信息属性，不能再进行细化分解。

2. 模型含有主键，非主键字段依赖主键(在满足 1 的基础上)。

比如用户这个模型，它的主键是用户 ID，那么用户模型其它字段都应该依赖于用户 ID，如商品 ID 和用户没有直接关系，则这个属性不应该放到用户模型而应该放到”用户-商品”关联订单表。

3. 模型非主键字段不能相互依赖(在满足 2 的基础上)。

例如：设计订单表(订单 ID、用户 ID、用户姓名……)

乍一看该表满足第二范式，每列都和主键列“订单 ID”相关，但是其中“用户 ID”和“用户姓名”相关，而且“用户 ID”和“订单 ID”又也相关，依次推断：“用户姓名”和“订单 ID”也相关。不满足第三范式，应去掉订单表“用户姓名” 列，放入到用户表中。

反范式化模型即不满足范式化的模型。主要是为了性能和效率的考虑适当的违反范式化设计要求，允许存在少量的数据冗余，即以空间换时间。

可见一个良好而实用的数据模型往往是依赖于具体的需求场景的，在设计数据模型之前，仔细分析需求场景，不仅能提高效率，也能有效规避后期可能遇到的一些意外麻烦。

范式化设计和反范式化设计的对比：

应用索引策略

索引(MySQL 中也被称为“键 Key”)，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其当表中的数据量越来越大时，索引对性能的影响愈发重要(不恰当的索引对会随数据量增大时，性能急剧下降)。

举例如下情况：

假设数据库中一个表有 10^6 条记录，DBMS 的页面大小为 4K(约可存储 100 条记录)。

如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取 10^4 个页面，如果这 10^4 个页面在磁盘上随机分布，需要进行 10^4 次 I/O，假设磁盘每次 I/O 时间为 10ms(忽略数据传输时间)，则总共需要 100s(但实际上要好很多很多)。

如果对之建立 B-Tree 索引，则只需要进行 log100( 10^6 )=3 次页面读取，最坏情况下耗时 30ms。这就是索引带来的效果。

了解了索引的优点之后，其实正确的创建和使用索引是实现高性能查询的基础。

可以利用 B-Tree 索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。

最左前缀主要规则

最左前缀原则主要使用在联合索引中。

例如：联合索引 idx_col1_col2_col3(col1、col2、col3)。

对索引中的所有列都指定具体的值：

-- 以下均可以应用到此联合索引（此情况字段顺序无关）

col1 = a AND col2 = b AND col3 = c

col2 = c AND col3 = b AND col1 = a

尽可能应用到联合索引中的排序靠前的字段：

-- 以下均可以应用联合索引中的部分索引

-- 以下均可以应用联合索引中的部分索引

col1 = a AND col2 = b

col1 = a

仅对索引进行查询，如果查询的列都位于索引中，则不需要读取列元组的值。

聚簇索引

聚簇索引保证关键字的值相近的元组存储的物理位置也相同(所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作)，且一个表只能有一个聚簇索引。因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有 SolidDB 和 InnoDB 支持。

InnoDB 对主键建立聚簇索引。如果你不指定主键，InnoDB 会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB 会定义一个隐藏的主键，然后对其建立聚簇索引。

查询缓存

MySQL 查询缓存会保存查询返回的完整结果。当查询命中缓存，MySQL 会立刻返回结果，而跳过了后续解析、优化以及执行阶段，会有效提升查询性能。

但是查询缓存不是银弹，它也会存在一些问题。

查询缓存注意事项

1. 缓存情况严格存在一些不确定函数情况无法使用查询缓存，如：NOW()、CURRENT_DATE() 等类似的函数;超过 query_cache_size (设置查询缓存空间大小)的查询结果无法被缓存;同时大小写敏感，只有字符串相等情况下查询 SQL 才使用相同缓存。-- 不会使用同一个缓存

select name from users where id = 1;

假如缓存过查询结果，但是由于查询缓存设置内存不足，新缓存加入时 MySQL 会将某些缓存逐出，导致后续查询未命中;

数据结构及数据修改、内存不足、缓存碎片都会导致缓存失效。

小结

查询缓存对应用程序完全透明，应用程序无需关心 MySQL 是通过查询缓存返回的还是实际执行返回的结果。但随着目前服务器性能越来越强，查询缓存被发现是一个影响服务器扩展性的因素，它很可能成为整个服务器的资源竞争点，大家采用生产环境开启应用时候一定要慎重考量。

重构查询方式

优化慢查询时候，我们可以转换下思路，我们的目标是找到一个更优的方法获取时间需要的结果，而不是一定从 MySQL 获取一模一样的结果集。重构查询的技巧很有必要。

复杂查询拆分

将一个复杂查询拆分多个简单查询，考虑是否需要将一个复杂查询拆分为多个简单查询。

实际开发过程中，大家往往会强调数据库层完成尽可能多的工作，这样做的初衷是认为网络通信、查询解析和优化是一件代价很高的事情，其实 MySQL 从设计上让连接和断开都很轻量级，同时在返回一个小查询结果方面很高效。况且目前网络速度也比之前快很多，无论是带宽还是延迟。

对于大查询我们要“分而治之”，将大查询切分成多个小查询。不过在一次查询能够胜任的情况下还拆成多个独立查询就不明智了。

例如：做数据库做 10 次查询，每次返回一行记录。

分解关联查询

将关联查询进行分解，对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。

例如：

SELECT

FROM users

LEFT JOIN orders ON orders.user_id = users.user_id

LEFT JOIN goods ON goods.good_id = orders.good_id

以上查询可以分解成下面的查询来代替：

SELECT * FROM users WHERE users.name = zhangsan;

SELECT * FROM orders WHERE orders.user_id = 103;

为什么要这样做呢?看起来好像没有什么好处，而且返回数据结果也是一致的。实际上利用分解查询的方式来重构查询有很大的优势，主要表现为：

高性能查询难题优化总结

如何处理高性能查询难题?

假如把高性能查询比作一个“难题”，它其实是包括多个子难题在内，共同作用的结果。

今天我们来归纳总结下，主要包括以下几类。

数据结构优化

良好的库表数据结构设计原则是普遍适用的，但是 MySQL 有它自己的实现细节要注意。

总结应用原则如下，注意借鉴：

索引设计优化

常见的 B-Tree 索引，按照顺序存储数据，所以 MySQL 可以用来做 ORDER BY 和 GROUP BY 操作。因为数据是有序的，所以便于将相关的列值都存储在一起。由于索引中存储了实际的列值，所以一些查询只通过索引就能够完成查询(如：聚簇索引)。

根据索引的特性，总结索引的优点有如下几点：

编写查询语句时候应该注意尽可能选择合适的索引，以避免单行查找，尽可能使用索引覆盖。

根据执行计划依次扫描相关表中的行，不在数据缓冲区的走 IO 存储引擎扫描表的性能消耗参考下面的 list，消耗从大到小：

全表扫描 > 全索引扫描 > 部分索引扫描 > 索引查找 > 唯一索引/主键查找 > 常量/null

应用查询优化

应用查询优化是建立在良好的数据结构和合理的索引设计之上的。

它主要包括以下几种情况。

优化慢查询时，目标应该是找到一个更优的方案来达到我们获取结果数据的目的。其中可以存在多样的权衡方案：

MySQL 查询优化器并不是对所有查询都适用的，我们可以通过改写查询 SQL 来让数据库更高效的完成工作。

常见查询优化建议如下：

MySQL v5.6 版本以后，消除了很多 MySQL 原本的限制，让更多的查询能够以尽可能高的效率完成。

小结

良好的表结构设计是高性能查询的基石，恰当的索引设计是高性能查询的助推器，同时合理的查询应用也是必不可少的。数据结构优化、索引设计优化及应用查询优化犹如三叉戟一般，齐头并进，在高性能查询应用中缺一不可。

写在最后

全文总结一下，其实就是我们要学会用数据库的要求方式来执行 SQL。

即要写好应用查询 SQL，必须要结合良好的数据结构和合理的索引设计才可以。

其实 MySQL 查询优化中的每一项拆开讲都可以是很大的章节，在此主要是将解决问题的思路分享给大家，希望能对大家今后的工作中能有所帮助。

相关文章