处理MySQL慢查询的正确姿势

写在前面

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

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

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

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

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

  • 建立与 MySQL 服务器连接(基础)
  • 客户端发送查询 SQL 到数据库，数据库验证是否有执行的权限
  • MySQL 服务器先检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果，否则继续流转;
  • MySQL 服务器语法解析器，进行词法与语法分析，预处理
  • 流转至查询优化器生成执行计划
  • 根据生成的执行计划，调用存储引擎暴露的 API 来执行查询
  • 将查询执行结果返回给客户端
  • 关闭 MySQL 连接

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

情况如下：

  1. 如未开启应用查询缓存，则直接忽略查询缓存的检查;
  2. 执行过程中，如同时对于被扫描的行可能加锁，同时也可能会被其他 SQL 阻塞

查询 SQL 为什么会慢?

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

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

  1. 查询了过多不需要的数据
  2. 扫描了额外的记录

查询了过多不需要的数据

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 的消耗。

扫描了额外的记录

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

实例表结构如下：

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`)
) ENGINE = InnoDB CHARSET = utf8mb4;

示例查询 SQL 执行计划：

EXPLAIN SELECT * FROM test_table WHERE age = 10;

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

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

如何定位问题呢?

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

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

  • id
  • select_type
  • table
  • partitions
  • type
  • possible_keys
  • key
  • key_len
  • ref
  • rows
  • filtered
  • Extra

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

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

select_type

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

type

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

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

possible_keys 和 key

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

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

rows

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

Extra

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

几种实用解决方案

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

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

优化数据结构

选择索引的数据类型

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

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

越小的数据类型通常更好

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

简单的数据类型更好

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

尽量避免 NULL

应该指定列为 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)。

匹配全值(Match the full value)

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

-- 以下均可以应用到此联合索引（此情况字段顺序无关）
col1 = 'a' AND col2 = 'b' AND col3 = 'c'
col2 = 'c' AND col3 = 'b' AND col1 = 'a' 
col3 = 'c' AND col1 = 'b' AND col2 = 'a'

匹配最左前缀(Match a leftmost prefix)

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

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

-- 以下均可以应用联合索引中的部分索引
col1 =  'a' AND col2 = 'b' 
col1 = 'a' 
col1 like 'a%'

索引覆盖(Index-only queries)

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

select a, b, c

聚簇索引

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

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

查询缓存

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

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

查询缓存注意事项

1. 缓存情况严格

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

2. 缓存易失效

假如缓存过查询结果，但是由于查询缓存设置内存不足，新缓存加入时 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
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 有它自己的实现细节要注意。

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

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

索引设计优化

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

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

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

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

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

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

应用查询优化

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

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

1. 重构查询方式

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

  • 从数据库中查询计算直接获取到结果数据;
  • 拆分多条子查询来逐步得到结果数据;
  • 从数据库获取到基础数据，然后应用代码逻辑加工后获得结果数据。

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

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

常见查询优化建议如下：

  • 对于任何查询，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立并应用索引。
  • 尽量避免在 where 子句中使用 or 来连接条件、对字段进行 null 值判断、匹配查询 '%abc%'、!= 或 <> 操作符，否则将导致引擎放弃使用索引而进行全表扫描。
  • 尽量避免在 where 子句中对字段进行表达式、函数操作，这将导致引擎放弃使用索引而进行全表扫描。
  • 使用索引字段作为条件时，如果是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
  • 添加索引尽量避开区分度不大的字段，如：sex、male、female 这种五五开的索引列有大量数据重复时，那么即使在 sex 上建了索引也对查询效率起不了作用。
  • 一个表的索引数最好不要超过 6 个。索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率， 因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
  • 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。 这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
  • 尽量避免使用 select * from table ，用具体的字段列表代替 *，不要返回用不到的任何字段，尤其是多表关联查询的情况。

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

小结

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

写在最后

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

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

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


 
版权声明 本文仅代表作者观点，不代表本站立场。
如遇本文系为网络转载到本站发表，图片或文章有版权问题的请联系客服确认后会立即删除文章。
如遇本文系作者授权本站发表，未经许可，不得转载。

--结束END--

有问题投稿请发送至: 邮箱/kf@guangfuqiang.com    QQ/162020580

本文标题: 处理MySQL慢查询的正确姿势

本文链接: https://www.guangfuqiang.com/zobk/a3162.html (转载时请保留)

网站首页

返回栏目
今日推荐
新闻举报 返回首页
更多
友情链接
鄂ICP备19019357号-22