mysql - var1 的正确索引 IN (1, 2, ...) GROUP BY (var2) ORDER BY SUM(var3) LIMIT X

Question

我正在尝试优化以下查询:

            SELECT name  
            FROM  tbl 
            WHERE user_id
                IN (".$user_ids.") 
            GROUP BY name ORDER BY SUM(counter) DESC LIMIT 10

Tbl 信息:名称为 VARCHAR，计数器和 user_id 为 INT。 user_id、name是唯一的。

我尝试添加 IDX(user_id, counter, name) 但在 EXPLAIN 中我仍然看到 Using where;使用索引；使用临时的；使用 filesort 所以我想我做错了什么。

此类查询的正确索引是什么？

Answer 1

正确的索引是 IDX(user_id, name, counter) ，但是从索引中获取数据后查询需要额外的计算。如果不同名称的数量大约是10个，那么你几乎无能为力(大部分时间都用在求和运算上)，但是如果有很多不同的名称，你可以通过使用关于SUM(counter)的一些经验知识来减少排序阈值:

SELECT name  
FROM  tbl 
WHERE user_id IN (".$user_ids.") 
GROUP BY name
HAVING SUM(counter) > 1000 -- adjust the threshold 
ORDER BY SUM(counter) DESC LIMIT 10

UPD1。嗯，如果你说你尝试过 IDX(user_id, name, counter) 索引，性能是一样的，我其实看不出它慢的原因，除非你传递几百个用户 id(这种情况下时间都花在查询上)解析而不是执行)。

UPD2。 MySQL IN 运算符还有一些额外的魔力:

Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search.

这意味着如果您将 INT 值传递给运算符 IN (1,2,3) ，它们将按 INTS 排序，如果您序列化存储为字符串 IN ('1', '11', '111', '12') 的整数，它们将按字典顺序排序。排序的基本原理是消除随机索引读取，这在将大量值传递给运算符时非常重要。