mysql - 使用文件排序和临时表区分(或分组)

Question

我知道有类似的问题，但我有一个关于为什么这个查询的具体查询/问题

EXPLAIN SELECT DISTINCT RSubdomain FROM R_Subdomains WHERE EmploymentState IN (0,1) AND RPhone='7853932120' 

给我这个输出解释

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  RSubdomains index   NULL    RSubdomain  767 NULL    3278    Using where

在 RSubdomains 上建立索引

但是如果我在 EmploymentState/RPhone 上添加一个综合索引

我从解释中得到这个输出

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  RSubdomains range   EmploymentState EmploymentState 67  NULL        2   Using where; Using temporary

如果我去掉 RSubdomains 上的 distinct，它会从解释输出中删除 Using temp...但我不明白的是为什么当我添加复合键(并将键保留在 RSubdomain 上)时distinct 最终使用临时表，这里哪个索引模式更好？我看到在组合键上扫描的行数要少得多，但查询是范围类型的，而且速度也较慢。

Answer 1

问:为什么...不同最终使用临时表？

MySQL 正在对索引进行范围扫描(即读取索引 block )以定位满足谓词(WHERE 子句)的行。然后 MySQL 必须从基础表中查找 RSubdomain 列的值(它在索引中不可用。)为了消除重复项，MySQL 需要扫描 RSubdomain 的值被检索到。 “Using temp”表示 MySQL 正在具体化结果集，该结果集将在后续步骤中处理。 (很可能，这是检索到的一组 RSubdomain 值；给定 DISTINCT，MySQL 可能实际上正在创建一个临时表，其中 RSubdomain 作为主键或唯一键，并且只插入非重复值.

在第一种情况下，看起来行正在按 RSubdomain 的顺序检索(很可能，这是集群键中的第一列)。这意味着 MySQL 不需要比较所有 RSubdomain 值；它只需要检查最后检索到的值是否与当前检索到的值匹配，以确定该值是否可以“跳过”。

问:这里哪种索引模式更好？

您的查询的最佳索引可能是覆盖索引:

... ON R_Subdomains (RPhone, EmploymentState, RSubdomain)

但只有 3278 行，您不太可能看到任何性能差异。

跟进

不幸的是，MySQL 不提供其他 RDBMS 中提供的检测类型(如 Oracle 事件 10046 sql 跟踪，它给出了资源和等待的实际时间。)

由于 MySQL 选择在可用时使用索引，这可能是最有效的计划。为了获得最佳效率，我将执行 OPTIMIZE TABLE 操作(对于具有动态格式的 InnoDB 表和 MyISAM 表，如果有大量 DML 更改，尤其是修改行长度的 DELETE 和 UPDATE...)至少，它会确保索引统计信息是最新的。

您可能想要比较执行 GROUP BY 而不是 DISTINCT 的等效语句的计划，即

SELECT r.RSubdomain
  FROM R_Subdomains r
 WHERE r.EmploymentState IN (0,1)
   AND r.RPhone='7853932120'
 GROUP
    BY r.Subdomain

为了获得最佳性能，我会使用以 RPhone 作为前导列的覆盖索引；这是基于对 RPhone 列的基数(接近唯一值)的假设，而不是 EmploymentState 列中的几个不同值。该覆盖索引将提供最佳性能...即最快消除需要检查的行。

但同样，只有几千行，很难看出任何性能差异。如果查询正在检查数百万行，那么您可能会看到差异，而获得良好性能的关键将是限制需要检查的行数。