MySQL异常查询案例分析

问题

用户工作单问题：相同的语句，只有最后的限制行数是不同的。奇怪的是，极限10的性能比限制100语句慢10倍左右。

隐藏用户表信息、语句和结果如下

从F1不为null的t中选择f1、和。
执行时间3分钟3.65秒
从F1不为null的t中选择f1、和。
执行时间1.24sec。

性能差距很大！

分析

最常用的跟踪句子执行的方法是通过解释来查看语句的执行计划。

更大的影响是通过缩小范围，在这个数据下，极限67和极限68的执行计划是非常不同的。

两个实施计划：

限制67
编号：1
select_type：简单
表：一
类型：范围
possible_keys：A，B，C
答案：B
key_len：387
参考：空
行数：2555192
额外的：使用；使用临时用filesort；
1行集（0秒）

限制68
编号：1
select_type：简单
表：一
类型：裁判
possible_keys：A，B，C
一个关键：
key_len：3
参考：const
行数：67586
额外的：使用；使用临时用filesort；
1行集（0秒）

正如您所看到的，两种语句的执行计划不同：所使用的索引是不同的。

在MySQL提示的结果中：解释，键代表最终索引，行代表索引需要扫描的行数，这是一个估计值。

表中的索引A定义为（F3，F4，f1，F2，F5）；索引B定义为（f1，F2，F3）；

确认

虽然行是一个估计值，但是它是指导索引使用的基础。因为限制68可以达到67586行，所以它应该在第一个语句优化器的可选结果中有这个值，为什么不选择索引A呢

让我们先确认上面的结论。

MySQL提示：MySQL索引可以使用强制索引来强制优化器使用索引。
从T力指数（a）中解释选择f1，求和（F2）CNT

编号：1
select_type：简单
Table: a
类型：裁判
possible_keys：一
一个关键：
key_len：3
参考：const
行数：67586
额外的：使用；使用临时用filesort；
1行集（0秒）

顺便说一下，因为我们指定的力量指数，优化器将不考虑其它指标，和possible_keys只会显示我们关心行：67586.this表明67的限制语句，索引的使用也可以减少扫描线。

MySQL提示：MySQL优化器将计算每个在possiable_key可能指标的查询成本，选择成本最低的查询计划。

在这一点上，我们可以猜测，这应该是MySQL实现的错误：没有选择合适的索引，导致使用了一个清晰的错误执行计划。

MySQL提示优化器：MySQL依赖于执行过程中表的统计信息，而统计信息是估计值，因此可能导致非最佳执行计划。

但是应该解释上面的提示是客观的（可接受的），但是这个例子是例外的，所以优化器实际上可以获得可以选择正确结果的数据（行值），但最后选择错误。

原因分析

MySQL优化器是对查询的成本的估计，以确定要使用的索引。估计值的计算过程基本上是根据估计需求扫描所需行数来确定的。

MySQL提示：MySQL只能在当前主流组中使用的5.1和5.5版本中使用前缀索引。

因此，使用索引A只能使用上字段F3，而索引B只能使用上字段f1，行使用索引来检查上下界，然后检查需要扫描的数据行数（估计值）。

上述语句需要使用组和秩序，所以有使用临时执行计划中使用filesort。

在这个过程中，使用索引A的查询成本是按顺序计算的。

然后计算出其他possitabe_key查询费用。由于过程中需要整理，需要确定是否有更便宜的排序方法（test_if_cheaper_ordering）后得到的是一个暂时的结果。

成本是通过估计扫描行数来计算的。

在实现这一逻辑时，存在一个错误：当当前索引的索引被估计时，未考虑前缀索引。

那就是：如果表中有50w行，指数B（F1、F2、F3），那么指数的区分度需要根据前缀部分可以用来确定。例如，F1有1000种不同的价值观，并对每个关键值记录的平均数是500。，如（F1，F2）有10000个相同的值。每个组合键的平均记录数是50。如果（F1、F2、F3）已50w不同的价值观，对每一个组合键记录的平均数为1。

提示：在每一个MySQL的关键记录数少，使用索引的效率是最高的。大的基数值在显示指数从TBL的输出结果。

在这种情况下，指数B只能用F1作前缀索引，但计算列平均值单密钥时，它使用F1、F2和F3，结果在使用时导致误选指标B.估算成本。

回到问题本身

1。为什么大的时候选择正确的极限

这是因为B的计算查询费用时，那limit_rows需要返回的行数也参与产品。如果限制值很大，则B的成本会更高，但这将是成本。值大于A，这将导致优化器最终选择A。

2、本表有50W线数之间的差异，为什么限制这么大

这与语句本身有关。在这个语句中，有一个组，这意味着每增加一个值，实际上需要扫描更多行n。这里n是表中的行总数。

也就是说，这个语句使bug具有放大效应。

解决方案

分析清楚，解决方法相对简单。修改代码的逻辑，并执行test_if_cheaper_ordering过程，利用场F1的歧视程度计算。