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

Question

我正在尝试优化一个看起来像这样的查询

SELECT DISTINCT(some_attribute)
FROM some_table
WHERE soft_deleted=0

我已经在 some_attribute 和 soft_deleted 上分别建立了索引。

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

Answer 1

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

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

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

索引还将包含聚簇索引中的列(即使它们未列出)，因此我们还可以在索引中的两个前导列之后包含这些列的名称。 MySQL 也将能够使用包含额外列的索引，同样，在两个前导列之后。

---

使用EXPLAIN查看执行计划。

我希望最佳执行计划将在 Extra 列中包含“Using index for GROUP BY”，并避免“Using filesort”操作。

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

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 是一个函数。)