单列索引与多列索引

Question

我必须在一个包含大约2000万行的大型表上运行两个简单的查询。

表列是id|user_id|earned_amount|created_at

查询1：

select user_id, sum(earned_amount) as total_earning 
from earning_history 
where user_id=XX;

查询2：

SELECT date(created_at) date, sum(earned_amount) as earning, count(id) as total_entry 
FROM `earning_history` 
where user_id=xx 
GROUP by date

我必须运行第二个查询而不是第一个查询。因此，我想索引user_id和created_at列；

在没有索引的情况下，执行第二个查询大约需要6-7秒。我的问题是，

1. 我应该只索引user_id列吗？或者我应该同时索引user_id和created_at列？
2. 我应该使用像=> ALTER TABLE earning_history ADD INDEX (user_id, created_at);这样的多列索引吗？

Answer 1

可以使用复合索引来快速访问数据，并减少对数据检索值的访问。可以将列用于where (user_id)和select中使用的列。

但对于函数或计算列中的列，通常使用索引。

总之，你应该有一些好处，使用和索引

create index my_index  on my_table ( user_id, id, created_at, earned_amount)

或

create index my_index  on my_table ( user_id,  created_at,id, earned_amount)

Answer 2

除非id可以是NULL，否则说COUNT(*)而不是id。那么，这两个查询的最佳索引是以下顺序

INDEX(user_id, earned_amount, created_at)

这两个查询都将使用它作为“覆盖”索引。两者都需要user_id是第一位的--以满足WHERE。第一个查询将只使用前两列，由于未使用的第三列，只需要少量开销。第二个查询不关心第二列和第三列的顺序，我选择这个顺序是为了使单个INDEX都能很好地工作。

两个单独的、单列的索引将不那么有效。MySQL可能只使用一个索引，它将是(user_id)。然后，它必须在包含索引的BTree和包含所有列的BTree之间反弹--以便至少得到earned_amount。“掩护”避免了这种反弹。