全面透彻，MySQL 正确的慢查询处理姿势

MySQL
38
0
0
2024-12-02
2万字，深度解析SQL性能优化，值得收藏
上一篇
全面透彻，深刻理解 MySQL 索引
下一篇
标签   MySQL语句

hello，大家好，我是张张，「架构精进之路」公号作者。

发现的一些问题

  • 问题1

在过去的半年时间里，研发团队内部尝试抓了一波儿慢查询SQL跟进处理率。发现有些同学对于慢查询处理的思路就是看看有没有用到索引，没有用到就试图加一个，实在不行就甩锅给这种情况是历史设计问题或者自行判定为用户特殊操作下触发的小概率事件，随即便申请豁免掉... 其实问题没有根本上解决。

  • 问题2

还有就是网络上经常可以看到一些类似这样的文章：

“慢SQL性能优化大全”

“慢SQL性能优化看这篇就够了”...

其实内容大同小异，要么建议加索引，要么建议重写SQL....

怎么说呢？知识点是对的，但不全面，这个很容易误导新同学，哈哈哈。

本文初衷

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

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

本文计划主要让大家搞明白查询SQL为什么会变慢，废话不多说，直接开干~

写在前面

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

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

一、查询SQL执行到底经历了什么？

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

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

  • 建立与MySQL服务器连接（基础）
  • 客户端发送查询SQL到数据库，数据库验证是否有执行的权限
  • MySQL服务器先检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果，否则继续流转；
  • MySQL服务器语法解析器，进行词法与语法分析，预处理
  • 流转至查询优化器生成执行计划
  • 根据生成的执行计划，调用存储引擎暴露的API来执行查询
  • 将查询执行结果返回给客户端
  • 关闭MySQL连接
具体执行过程可能会因MySQL服务器具体配置和执行场景有一些差异。 1）如未开启应用查询缓存，则直接忽略查询缓存的检查； 2）执行过程中，如同时对于被扫描的行可能加锁，同时也可能会被其他sql阻塞

二、查询SQL为什么会慢？

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

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

1）查询了过多不需要的数据

2）扫描了额外的记录

2.1 查询了过多不需要的数据

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

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

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

SELECT *
FROM users
  LEFT JOIN orders ON orders.user_id = users.user_id
  LEFT JOIN goods ON goods.good_id = orders.good_id
WHERE users.name = 'zhangsan';

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

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
WHERE users.name = 'zhangsan';

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

2.2 扫描了额外的记录

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

示例表结构如下：

CREATE TABLE `test_table` (
  `name` varchar(32) DEFAULT NULL,
  `desc` varchar(32) DEFAULT NULL,
  `age` int(16) DEFAULT NULL,
  `id` bigint(11) DEFAULT NULL,
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

存在索引 `idx_age` 的情况下，查询执行计划如下：

EXPLAIN SELECT * FROM test_table WHERE age = 10;

预估访问1行数据即可命中数据，如删除有效索引 `idx_age` 后则会变成全表扫描（ALL），预估需要扫描121524条记录才能完成这个查询，如下图所示：

三、如何定位问题呢？

通过梳理 MySQL中的 SQL执行过程我们发现，任何流程的执行都存在其执行环境和规则，主要导致慢查询最根本的问题就是需要访问的数据太多，导致查询不可避免的需要筛选大量的数据。

如果将MySQL慢查询作为一个问题来拆解分析的话，以上内容算是问题分析，那接下来开始问题定位和问题解决。

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

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

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

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

1）select_type

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

2）type

显示查询使用类型，从好到差依次为：system > const > eq_ref > ref > range > index > all

3）possible_keys 和 key

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

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

4）rows

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

5）Extra

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

四、几种实用解决方案

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

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

4.1 优化数据结构

4.1.1 选择索引的数据类型

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

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

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

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

(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

4.1.2 范式与反范式

  • 范式化

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

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

2）（在满足1的基础上）模型含有主键，非主键字段依赖主键；

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

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

订单表(订单编号，订购日期，顾客编号，顾客姓名，……) 初看该表没有问题，满足第二范式，每列都和主键列”订单编号”相关。 再细看你会发现“顾客姓名”和“顾客编号”相关，“顾客编号”和“订单编号”又相关，最后经过传递依赖，“顾客姓名”也和“订单编号”相关。 为了满足第三范式，应去掉“顾客姓名”列，放入客户表中。
  • 反范式化

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

4.1.3 小结

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

范式化设计和反范式化设计的优劣对比如下：

1、范式化可以尽量的减少数据冗余

2、范式化的更新操作比反范式化更快

3、范式化的表通常比反范式化的表要小

4、反范式化减少表的关联

5、反范式化相比范式化可以更好的对索引进行优化，例如使用覆盖索引。

关于数据库范式与反范式设计，详情可参考我之前的一篇文章：数据库范式与反范式设计，是一门艺术

4.2 应用索引策略

索引（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)来进行查询。

4.2.1 最左边前缀主要规则
  • 匹配全值(Match the full value)：对索引中的所有列都指定具体的值。例如，上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen。
  • 匹配最左前缀(Match a leftmost prefix)：你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。
  • 匹配列前缀(Match a column prefix)：例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。
  • 匹配值的范围查询(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列。
  • 匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name为Allen，而first name以字母K开始的人。
  • 仅对索引进行查询(Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。

由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY。

当然，使用B-tree索引有以下一些限制：

  • 查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。
  • 不能跳过某一索引列。例如，你不能利用索引查找last name为Smith且出生于某一天的人。
  • 存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE lastname="Smith" AND firstname LIKE 'J%' AND dob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询。
4.2.2 聚簇索引

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

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

4.3 查询缓存

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

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

4.3.1 查询缓存注意事项

1）缓存情况严格

存在一些不确定函数情况无法使用查询缓存，如：NOW()、CURRENT_DATE() 等类似的函数；

超过 query_cache_size （设置查询缓存空间大小）的查询结果无法被缓存；

同时大小写敏感，只有字符串相等情况下查询SQL才使用相同缓存。

-- 不会使用同一个缓存
select name from users where id = 1;
SELECT name FROM users WHERE id = 1;

2）缓存易失效

假如缓存过查询结果，但是由于查询缓存设置内存不足，新缓存加入时MySQL会将某些缓存逐出，导致后续查询未命中。同时数据结构及数据修改，内存不足，缓存碎片都会导致缓存失效。

4.3.2 小结

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

4.4 重构查询方式

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

4.4.1 复杂查询拆分

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

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

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

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

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

例如：

SELECT *
FROM users
LEFT JOIN orders ON orders.user_id = users.user_id
LEFT JOIN goods ON goods.good_id = orders.good_id
WHERE users.name = 'zhangsan';

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

SELECT * FROM users WHERE users.name = 'zhangsan';
SELECT * FROM orders WHERE orders.user_id = 103;
SELECT * FROM goods WHERE goods.good_id IN  （123, 456, 789）;

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

  • 将查询分解后，执行单个查询可减少锁的竞争；
  • 应用层做关联，更容易对数据库进行拆分，更易于做到高性能和可扩展；
  • 减少冗余记录的查询（在应用层做关联，表示对某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复访问一部分数据。）


五、高性能查询难题优化总结

如果将MySQL慢查询作为一个问题来拆解分析的话，之前内容算是问题分析、问题定位和解决，那现在来收下尾，聊聊MySQL慢查询问题解决经验总结。

废话不多说，直接开干~

我们来总结一下，应该如何处理高性能查询难题？

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

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

5.1 数据结构优化

良好的schema设计原则是普遍适用的，但是MySQL有他自己的实现细节要注意，概况来讲，尽可能保持任何东西小而简单总是好的。

主要有以下简单的原则值得你去考虑使用：

  • 尽量避免过度设计
  • 使用小而简单的合适数据类型，尽可能避免使用null
  • 尽量使用相同的数据类型存储相似或者相关的值
  • 注意可变长字符串，其在临时表和排序时可能按最大长度分配内存
  • 尽量使用整形定义标识符

5.2 索引设计优化

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

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

  • 减少服务器需要扫描的数据量；
  • 帮助服务器避免排序和临时表；
  • 将随机I/O变为顺序I/O。

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

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

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

5.3 应用查询优化

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

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

5.3.1 重构查询方式

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

1）从数据库中查询计算直接获取到结果数据；

2）拆分多条子查询来逐步得到结果数据；

3）从数据库获取到基础数据，然后应用代码逻辑加工后获得结果数据。

5.3.2 让SQL尽量符合查询优化器的执行要求

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

常见查询应用优化建议汇总如下：

1）对于任何查询，应尽量避免全表扫描

首先应考虑在 where 及 order by 涉及的列上建立并应用索引；

2）尽量避免在 where 子句中进行操作

使用 or 来连接条件、对字段进行 null 值判断、匹配查询 '%abc%'、!= 或 <> 操作符，否则将导致引擎放弃使用索引而进行全表扫描；

对字段进行表达式、函数操作，这将导致引擎放弃使用索引而进行全表扫描；

3）尽量应用索引

使用索引字段作为条件时，如果是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致；

4）索引字段要注意慎重选取

索引尽量避开区分度不大的字段，如：sex、male、female

这种五五开的索引列有大量数据重复时，那么即使在 sex 上建了索引也对查询效率起不了作用。

5）一个表的索引数最好不要超过 6 个

索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率， 因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

6）尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。 这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

7）尽量避免使用 *

select * from table ，用具体的字段列表代替 *，不要返回用不到的任何字段，尤其是多表关联查询的情况。

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

5.4 小结

根据梳理 MySQL中的 SQL执行过程我们发现，任何流程的执行都存在其执行环境和规则，其实产生慢SQL的本质是：我们没有按照数据库的要求方式来执行SQL。

主要导致慢查询最根本的问题就是需要访问的数据太多，导致查询不可避免的需要筛选大量的数据。

MySQL慢查询问题细数起来，林林总总太多了，但行之有效的无外乎这几种：

  • 优化数据结构
  • 应用索引策略
  • 查询缓存
  • 重构查询方式

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

写在最后

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

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

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

最后，感谢大家的持续关注~

2万字，深度解析SQL性能优化，值得收藏
上一篇
全面透彻，深刻理解 MySQL 索引
下一篇