应该编制哪些内容来提高绩效？ [英] What should be indexed to improve performance?

问题描述

根据此查询，应对哪一列或哪些列进行索引以优化查询性能？

Given this query, which column or columns should be indexed to optimize query performance?

SELECT *
  FROM `activities`
 WHERE (user_id = 90000 AND activity_type_id IN(300,400,808,9494))
 ORDER BY created_at DESC
 LIMIT 70

推荐答案

通常，选择过滤器可以使用 user_id 或<上的索引code> activity_type_id 或两者（按任意顺序）。

In general, the selection filters can use indexes on user_id or activity_type_id or both (in either order).

订购操作可能能够在<$ c上使用过滤器$ c> created_at 。

对于此查询，可能是上的复合索引（user_id，activity_type_id）假设MySQL实际上可以使用它，那么会给出最好的结果。如果不这样做，索引 user_id 而不是 activity_type_id 可能会更好，因为它可能提供更好的选择性。想到这一点的一个原因是，如果它使用 activity_type_id 上的索引，则会扫描索引的4个子部分，而只使用一个子部分来扫描它是否使用索引单独 user_id 。

It is likely that for this query, a composite index on (user_id, activity_type_id) would give the best result, assuming that MySQL can actually make use of it. Failing that, it is likely to be better to index user_id than activity_type_id because it is likely to provide better selectivity. One reason for thinking that is that there would be 4 subsections of the index to scan if it uses an index on activity_type_id, compared with just one subsection to scan if it uses an index on user_id alone.

尝试依赖排序顺序的索引可能意味着全表扫描，所以不太可能有益。我不会在 created_at 上创建索引来支持此查询;可能还有其他问题，这将是有益的。

Trying to rely on an index for the sort order is likely to mean a full table scan, so it is less likely to be beneficial. I would not create an index on created_at to support this query; there might be other queries where it would be beneficial.

这篇关于应该编制哪些内容来提高绩效？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！