《MySQL排错指南》——1.2 SELECT返回错误结果

本节书摘来自异步社区出版社《MySQL排错指南》一书中的第1章，第1.2节，作者：【美】Sveta Smirnova（斯维特 斯米尔诺娃），更多章节内容可以访问云栖社区“异步社区”公众号查看。

1.2　SELECT返回错误结果

这是用户反馈的另一个非常常见的问题，主要的现象有：用户看不到更新的结果、展示的顺序错误或者查询到了非预期的结果。

这个问题主要有两方面的原因：一方面是你的SELECT查询有误；另一方面是数据库中的数据和你想象的不同。我先介绍第一种情况。

在我规划本节示例的时候，我考虑要么使用真实的示例，要么使用我自己设计的小场景。真实的示例可能占用大量篇幅，但是我自己设计的示例可能对你没有什么帮助，因为没有人会写出那样的代码。因此，我选择使用典型的真实示例作为示例，只是大幅简化了它们。

第一个示例是用户在大量使用join时的常见错误。我们将使用之前介绍的例1-1中的表。这张表包含了MySQL中会引起一些常见使用错误的特性，这些特性是我在MySQL支持团队中收集的。每个错误在items表中都有一行记录。我还有一张关联资源信息的links表。因为条目和关联信息之间是多对多的关系，所以我通过items_links关联表把它们联系起来。下面是items表和items_links表的定义（在这个示例中不需要links表）：

我编写的第一条查询正常运行，并且似乎返回了合理的结果：

……直到我把返回的数值与关联总数进行比较的时候，我才发现：

查询到的关联信息数比关联表的记录还多，这怎么可能？

我们再来检查一下我特意编写的这个查询。它很简单，仅仅包含两部分，一个子查询：

和一个外部查询：

子查询是开始错误排查的好切入点，因为它可以独立运行。因此，我们可以预期一个完整的结果集：

令人惊讶的是，我们居然有一个输入错误，事实上items_links表中并没有id字段，而是iid字段（代表items的ID）。如果我们重写该条查询，让它使用正确的标识符，它便可正常运行：

我们刚刚学习了一个新的调试技巧。如果一个SELECT查询没有按预期工作，可以将其拆分成小段语句，然后分析每一部分直到你找到产生错误行为的原因。
提示　提示

如果你通过表名.列名的格式指定完整的列名，那么你可以从一开始就避免这个错误，因为你会立即获得错误：

MySQL命令行客户端是一个非常好的测试工具，该工具包含在MySQL的安装包中。第6章将讨论这个重要的工具。

然而，为什么MySQL在执行原始查询语句的时候没有返回同样的错误呢？这是因为在items表有一个名为id的列，因此MySQL认为我们想要执行一个依赖子查询，结果实际上从items_links表中查询了items.id。“依赖子查询”是指引用外部查询中字段的查询。

我们也可以借助EXPLAIN EXTENDS命令，通过SHOW WARNINGS来查找这个错误。如果我们用该命令运行原始查询，会得到：

EXPLAIN EXTENDED输出的2.row表明该子查询实际上是依赖的：select_type是DEPENDENT SUBQUERY。

在结束这个示例之前，我想再介绍一个可以在请求语句涉及很多表的时候，帮助你避免迷茫的小技巧。要知道当你面对10个甚至更多表的连接时，即使你很了解它们应该怎么连接，你也会感到迷茫。

上面示例中一个值得注意的地方是SHOW WARNINGS的输出信息。MySQL服务器不是总按照语句输入的顺序执行它，而是调用优化器去构造一个更好的执行计划，因此用户通常都会很快得到返回结果。在EXPLAIN EXTENDED之后，SHOW WARNINGS命令展示的就是优化后的查询。

在该示例中，SHOW WARNINGS的输出包含两个主要信息。第一个是：

这条信息明确指出服务器是通过items表而不是items_links表解析id的值。

第二条信息包含了优化过的语句：

这个输出信息也指出服务器是从items表接受id的值。

现在我们来对比一下正确的查询和之前列出的错误查询的EXPLAIN EXTENDED的结果：

这次优化过的语句看起来完全不同了，并且确实像我们预期的那样比较items.id和items_links.iid的值。

我们刚刚学习了另一教训：在EXPLAIN EXTENDED命令之后使用SHOW WARNINGS命令查看查询是如何优化（与执行）的。
在正确的查询中，select_type的值仍然是DEPENDENT SUBQUERY。我们已经通过items_links表来解析字段的名称了，为什么结果仍是那样？答案从SHOW WARNINGS中下面这部分输出开始：

子查询仍然显示是依赖的，因为外部查询子句中的id需要子查询去检查与内部查询对应的每行里的iid值。这个问题在MySQL社区bug数据库的12106号报告的讨论中提出。

这个bug报告给我们了另一个重要的教训：如果你怀疑你的查询的执行行为，可以通过有效的资源去获取信息。社区bug数据库就是这样的一种资源。
SELECT查询运行异常可能有很多不同的原因，但是查找问题的一般方法总是相同的。

将查询分解成小段，然后依次执行它们直到你发现问题的原因。
使用EXPLAIN EXTENDED，然后使用SHOW WARNINGS命令去获得查询执行计刬及其实际运行方式的相关信息。
如果你不理解MySQL服务器的执行状况，可以使用互联网和其他有效的资源去获得信息。附录提供了非常有用的资源列表。