MYSQL为统计表建立索引

Question

我有一个mysql表，其中存储从一些‘日志文件’解析的信息。该表包含以下列

 Table "logfiles"
 log_id        INT (PRIMARY)
 logfile_name  TEXT
 date          DATE (INDEX)
 username      TEXT (INDEX)
 field1        FLOAT
 field2        FLOAT
 field3        FLOAT

我想做一个统计查询，以按年对事物求和，如下所示：

SELECT 
    YEAR(date) AS year, 
    COUNT(DISTINCT date) AS active_days,
    COUNT(log_id) AS total_logs,
    COUNT(DISTINCT username) AS active_users,
    SUM(field1) AS total_field1,
    SUM(field2) AS total_field2
FROM 
    logfiles
GROUP BY 
    year
ORDER BY
    year ASC 

什么是索引这个表的最好方法，以使查询运行得更快(并使用索引)。

目前，100000行的查询耗时3秒。从EXPLAIN查询中，我可以理解它没有使用索引。

id              1   
select_type     SIMPLE  
table           logfile_list    
type            ALL     
possible_keys   NULL    
key             NULL    
key_len         NULL    
ref             NULL    
rows            88963   
Extra           Using filesort

感谢您的帮助，向Pikot致以最美好的问候

Answer 1

• 这一点，特别是因为COUNT(DISTINCT...)需要读取每一行。
• 因此将创建一个临时表。
• 由于TEXT的存在，它必须在磁盘(MyISAM)上，而不是在内存(内存)中。

因此，将username TEXT更改为username VARCHAR(55) (或一些合理的大小)可能会加快查询速度，可能是原来的两倍。

在没有COUNT(DISTINCT...)的情况下，您可以通过使用获得10倍的加速。

它很复杂，但可以相当精确地总结COUNT(DISTINCT...)。请参阅