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

感谢您的帮助此致皮科特

Answer 1

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

因此，将username TEXT更改为username VARCHAR(55)(或一些合理的大小)可能可以加快查询速度，也许可以通过因数二。

如果没有 COUNT(DISTINCT...)，使用 summary tables 可以获得 10 倍的加速.

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