时间戳

首页 > 解决方案 > 优化“distinct where equals”查询和索引

mysql - 优化“distinct where equals”查询和索引

问题描述

我正在尝试优化看起来像的查询

SELECT DISTINCT(some_attribute)
FROM some_table
WHERE soft_deleted=0

我已经有单独的索引some_attribute了soft_deleted。

我从中提取的表相对较大（> 100GB），因此此查询可能需要数十分钟。some_attribute 和 soft_deleted 上的多列索引会产生重大影响，还是我可以进行其他一些优化？

标签： mysql

解决方案

我们将假设该表使用 InnoDB 存储引擎，并假设该soft_deleted列是整数数据类型，并且该some_attribute列是一个小型数据类型列。

对于问题中显示的确切查询文本，最佳执行计划可能会使用索引，soft_deleted并按some_attribute该顺序作为前导列，即

... ON some_table (soft_deleted, some_attribute, ...)

该索引还将包含集群索引中的列（即使它们未列出），因此我们还可以在索引中的两个前导列之后包含这些列的名称。MySQL 也将能够使用包含附加列的索引，同样位于两个前导列之后。

用于EXPLAIN查看执行计划。

我预计最佳执行计划将在 Extra 列中包含“为 GROUP BY 使用索引”，并避免“使用文件排序”操作。

使用上面建议的索引，比较此查询的执行计划：

SELECT t.some_attribute
  FROM some_table t
 WHERE t.soft_deleted = 0  
 GROUP 
    BY t.soft_deleted 
     , t.some_attribute
 ORDER 
    BY NULL

如果我们已经有一个定义some_attribute为前导列的索引，并且还包括该soft_deleted列，例如

... ON some_table (some_attribute, soft_deleted, ... )

（仅 some_attribute 列上的索引将是多余的，可以删除）

我们可能会重写 SQL 并检查 EXPLAIN 输出中的查询，如下所示：

SELECT t.some_attribute
  FROM some_table t
 GROUP 
    BY t.some_attribute
     , IF(t.soft_deleted = 0,1,0)
HAVING t.soft_deleted = 0
 ORDER
    BY NULL

如果我们有一个soft_deleted只有两个不同值的保证，那么我们可以简化为 

SELECT t.some_attribute
  FROM some_table t
 GROUP 
    BY t.some_attribute
     , t.soft_deleted
HAVING t.soft_deleted = 0
 ORDER
    BY NULL

针对该表的查询的最佳性能，以返回指定的结果集，可能会在避免“使用文件排序”操作并使用索引来满足 DISTINCT/GROUP BY 操作的执行计划中找到。

请注意，它DISTINCT是关键字而不是函数。周围的括号some_attribute没有作用，可以省略。（包括虚假的括号几乎使它看起来像我们认为DISTINCT的一个函数。）

推荐阅读