php - MySQL 中的覆盖索引决策

Question

我的 MySQL 慢速查询日志显示一个查询看起来相当简单，因为它是我服务器上运行的最慢的查询之一:

SELECT result_known,AVG(points_total) as points
FROM tbl_points
WHERE uid IN (N,{1023 repeats}N)
GROUP BY gid
ORDER BY gid ASC;

我基本上是在尝试找到一个小组的一个子组(一组 uid，例如基于性别或其他)的平均总分。 uid 和 gid 上有单独的索引，但 EXPLAIN 显示它们未被使用:

| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows | Extra
|  1 | SIMPLE      | tbl_points          | ALL  | combined      | NULL | NULL    | NULL |   64 | Using where; Using temporary; Using filesort

现在，据我所知，显而易见的解决方案是对这些字段进行覆盖索引:

CREATE INDEX index1 ON dbo.tbl_points(result_known, points_total, uid, gid)

事实上，这使得它使用了索引:

| id | select_type | table               | type  | possible_keys | key    | key_len | ref  | rows | Extra                                                     |
|  1 | SIMPLE      | tbl_points          | index | combined      | index3 | 18      | NULL |   64 | Using where; Using index; Using temporary; Using filesort |

但是，我有两个问题:

• 在 Extra 字段中，EXPLAIN 现在说“Using where; Using index; Using temporary; Using filesort”。这很糟糕，对吧？那么我应该使用这个索引吗？用虚拟术语来说，type=index 和 key=something 是否比“extra”字段中发生的事情更重要？

  <

• 覆盖索引对大插入有什么影响？我通过对一个相当大的临时表执行 JOIN-UPDATE 在同一个表中插入点。我不想把它放慢太多。

Answer 1

一般来说，可以使用索引优化IN(...)的范围谓词，也可以使用索引优化掉由GROUP引起的临时表BY(尽管您指出这可能不适用于 AVG())。但是您不能在同一个 SELECT 中同时使用索引。

我将得出结论，您无法在这个特定查询中删除临时表。您可以做的最好的事情就是通过增加 tmp_table_size 来防止它进入磁盘。或者，如果它确实进入磁盘，请配置一个 tmpfs 文件系统并将该挂载点用作您的 tmpdir。

因此您必须选择，是否要为您的 uid 值列表搜索索引？您有一个非常长的 uid 列表，因此仅估计行数就会很昂贵。一定要升级到 MySQL 5.6，它在这方面有一些新的优化(参见 Equality Range Optimization of Many-Valued Comparisons)。

type=index 表示它正在执行索引扫描，虽然成本很高，但至少它可以单独从索引中获取结果，而不必读取表行。所以它需要更少的缓冲池页面来满足这个查询。