QA视角的MySQL探险之旅：穿越慢查询沼泽，揭示实战避坑指南

快乐再出发

前言数据库是“按照数据结构来组织、存储和管理数据的仓库”，承接核心的业务数据，在业务中占有非常重要的地位。日常测试工作中，我们通常会将重点放在业务逻辑准确性的验证中，从而忽略掉数据库自身的一些问题。本文将以关系型数据库MySQL为例，通过讲解一条查询SQL的工作过程来了解MySQL的核心模块、执行流程以及索引的组织形式，进而从典型的数据库慢查询和其他踩坑经验两个方面分析日常测试中遇到的问题，并提供解决方案。一条SQL查询语句是怎么执行的？众所周知，MySQL 可以分为 Server 层和存储引擎层两部分。Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。存储引擎层则负责数据的存储和提取。接下来思考一个问题，输入一条语句到返回结果中间经过了哪些过程呢？一条查询SQL的执行过程如下图所示：连接器：你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。查询缓存：连接建立完成后，执行逻辑就会来到查询缓存。如果开启来了查询缓存，之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。如果命中，value直接返回给客户端。没有命中，则继续。执行完成后，执行结果会被存入查询缓存中。分析器：如果没有命中查询缓存，进入分析器，通过词法分析+语法分析对 SQL 语句做解析，语法错误是从这个环节报出的。优化器：优化器是为了提升SQL的执行性能。经过了分析器，MySQL 就知道要做什么了。在开始执行之前，还要先经过优化器的处理。在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。执行器：优化器优化后进入了执行器阶段，执行器跟存储层进行交互，取得执行结果并返回什么是索引？从上面的查询流程可以看出，在优化器环节，会决定用什么索引来加速SQL的执行性能，那我们再来看下MySQL的索引。简单来讲，索引的出现其实就是为了提高数据查询的效率，就像书的目录一样，如果你想快速找到其中的某一个知识点，在不借助目录的情况下，那我估计你可得找一会儿。同样，对于数据库的表而言，索引其实就是它的“目录”。以InnoDB引擎为例，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。索引主要包括主键索引和和非主键索引，通过一个例子看看它们的区别。假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。M1表中 R1~R5 的 (id,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。●主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。●非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。基于上面的索引结构说明，我们来讨论一个问题：基于主键索引和普通索引的查询有什么区别？如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。当然，也有走了普通索引却不需要回表的例子，上面的SQL把取全部字段改成只取列k，因为k的值在 k 索引树可以取到，所以不需要回表，这种通常称为使用了覆盖索引。慢查询什么是慢查询？上面了解了SQL查询语句的执行过程，以及索引，那么什么是慢查询呢？慢查询定义：慢查询就是那些执行慢的SQL语句，包括crud（创建（Create）、更新（Update）、读取（Read）和删除（Delete）操作），一般是查询，所以称为慢查询。慢查询的出现会给服务带来非常不好的影响，轻则影响用户体验，服务响应变慢、无返回等，重则会出现因慢查询占用大量内存导致MySQL服务直接挂掉，进而导致整个系统瘫痪；因此，我们应该重视慢查询问题，尽早发现，尽早解决。产生慢查询的大部分原因是未命中索引 ，也会有少部分因为网络抖动或者刷脏页问题 ，本文主要讨论索引未命中的场景发现慢查询为了优化慢查询的问题，先要发现慢查询，主要有以下两种手段：间接发现：添加接口响应时间的监控慢查询的出现会让系统响应变慢，比如接口返回超时，可以通过添加接口监控来监测，出现超时的场景就去排查是否存在慢查询。但是这种方式不够直接，因为接口响应慢的因素有很多，可能是网络原因、测试服务器资源等问题，无法快速定位是否是慢查询；主动发现：监控慢查询日志数据库开启慢查询日志（通常会存储在文件中，可自行设置），监控慢查询日志，如果新增慢查询便立即发送通知。这种方式我个人比较推崇，可以第一时间发现并着手分析优化。分析慢查询发现慢查询以后，需要着手优化，通常有以下几种情况： 情况1：通过explain你可能会发现，SQL压根没走任何索引，而且现在表中的数据量巨大无比。解决：建合适索引 情况2：通过explain查看SQL执行计划中的key字段。如果发现优化器选择的Key和你预期的Key不一样。那显然是优化器选错了索引。解决：最快的解决方案就是：force index ，强制指定索引，当然还有其他方案，后面实践会展开说明。当你发现即使你使用了force index之后，查询依然很慢。这就意味着，你需要设计一个更好的索引。此外：也可能是数据量太大了，即使走了索引依然超过了阈值。这种情况其实挺糟糕的，DBA同学能做的依然只能是kill这些出事的SQL。最好的解决方案其实是分表，比如将大表拆分成128张小表。如果来不急做分表，可能这条SQL面临被下线的风险。分析优化实践接下面来看两个分析优化的例子。分析优化1-正确添加索引：数据库版本：5.7.13我们有个日志表，数据的量级200w+目标比较简单，查询某个脚本的历史执行日志，通过脚本id（script_id）去查询,优化之前的SQL执行情况，时间大概1.8s，如下图所示：查看执行计划：EXPLAIN SELECT * FROM `prepare_data_log` WHERE script_id = 24;结果如下图所示：从图中可以看出，type列的值是ALL，走的全表扫描；key字段是NULL，没有使用任何索引；优化方案是加索引：在script_id上添加二级索引：ALTER TABLE prepare_data_log ADD INDEX script_id (script_id);加索引之后，执行时间从1.8s->0s，详见下图，可见，合理的添加索引可以显著的提升查询性能。再看下执行计划：从图中可以看出，type列的值是ref，表示走了索引；key字段是索引的名字script_id，使用了刚创建的索引，符合预期；分析优化2-选择正确的索引上面的例子添加了索引，执行器也选择了期望的索引，那MySQL会一直选择期望的索引么？答案是：不一定看一个例子：数据表数据有10W，包含id（自增主键）、a、b、c三列，其中a=b=c=id，a和b两列都有二级索引。查询语句如下：M2从条件上看，这个查询没有符合条件的记录，因此会返回空集合。执行之前，可以先设想一下，如果你来选择索引，会选择哪一个呢？为了便于分析，我们先来看一下 a、b 这两个索引的结构图。如果使用索引 a 进行查询，那么就是扫描索引 a 的前 1000 个值，然后取到对应的 id，再到主键索引上去查出每一行，然后根据字段 b 来过滤。显然这样需要扫描 1000 行。如果使用索引 b 进行查询，那么就是扫描索引 b 的最后 50001 个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描 50001 行。所以你一定会想，如果使用索引 a 的话，执行速度明显会快很多。那么，执行器会按照我们期望的选择么？下图是执行 explain 的结果：可以看到，返回结果中 key 字段显示，这次优化器选择了索引 b，而 rows 字段显示需要扫描的行数是 50210。从这个结果中可见 MySQL “选错了”索引。在日常工作中也遇到过几次选错索引的例子，通常开发的做法是采用 force index 强行选择一个索引。 方案一：force index 强行选择一个索引。刚开始分析时，我们认为选择索引 a 会更好。现在，我们就来看看执行效果：从使用不同索引的语句执行耗时可以看到，原本语句需要执行 0.06 秒，而当你使用 force index(a) 的时候，只用了 0.01 秒，比优化器的选择快了 6倍。也就是说，优化器没有选择正确的索引，force index 起到了“矫正”的作用。这个方案并不优雅，一来这么写不优美，二来如果索引改了名字，这个语句也得改，显得很麻烦。而且如果以后迁移到别的数据库的话，这个语法还可能会不兼容。所以，数据库的问题最好还是在数据库内部来解决。 方案二：修改SQL，引导走期望索引。既然优化器放弃了使用索引 a，说明 a 还不够合适，所以第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引。比如，在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。我们来看看改之后的效果：之前优化器选择使用索引 b，是因为它认为使用索引 b 可以避免排序（b 本身是索引，已经是有序的了，如果选择索引 b 的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。现在 order by b,a 这种写法，要求按照 b,a 排序，就意味着使用这两个索引都需要排序。因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描 1000 行的索引 a。当然，这种修改并不是通用的优化手段，只是刚好在这个语句里面有 limit 1，因此如果有满足条件的记录， order by b limit 1 和 order by b,a limit 1 都会返回 b 是最小的那一行，逻辑上一致，才可以这么做。 方案三：在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。在日常工作中遇到慢查询，可以根据实际的应用场景灵活的选择合适的解决办法。当然，优化的前提是先要清楚影响优化器选择索引的因素。其他踩坑经验除了慢查询之外，我们还常遇到一些因不了解MySQL配置而导致的bug。group_concat截断问题在MySQL中，有个函数叫“group_concat”，平常使用可能发现不了问题，在处理大数据的时候，会发现内容被截取了。如下图例子，当需要拼接的数据量大出现了因截断导致数据缺失的问题：可以通过命令查group_concat允许的最大长度，如下图所示，默认长度是1024：group_concat被截断可能会导致数据损失和错误，因此在进行字符串拼接时应格外注意。建议使用其他字符串处理函数（例如SUBSTRING）和适当的数据类型（例如TEXT和BLOB）来避免这种情况的发生。MySQL数据类型溢出MySQL中某些字段出现溢出时并不会报错，而是截断处理，导致数据达到最大范围时会发生溢出，从而导致数据错误或丢失。遇到过INT类型溢出的case，如图所示：为了避免和解决MySQL 类型溢出的发生，如下建议可参考：1.使用更大范围的数据类型，如INT换成BIGINT,可以存储更大的整数范围。2.对于存储非负整数的字段可以使用U。3.使用程序代码对输入的数字进行有效性检查，避免用户输入超出定义范围的数字。以上就是MySQL测试方面的一些经验之谈，主要针对慢查询和一些不易察觉的参数设定引发的相关问题，希望能给你在日常工作中有所指引~分享给第一个想到的人