代码之家 › 专栏 › 技术社区 › Shpigford

如何优化此SQL查询以除去filesort和temp表?

refactoring database mysql sql

Shpigford · 技术社区 · 15 年前

以下是问题:

SELECT 
  count(id) AS count 
FROM `numbers` 
GROUP BY 
  MONTH(created_at), 
  YEAR(created_at) 
ORDER BY 
  YEAR(created_at), 
  MONTH(created_at)

在进行解释时,该查询抛出“Using temporary”和“Using filesort”。

最终,我要做的是查看一个用户提交的跟踪号表,并按月/年对提交的行数进行分组。

即2008年11月,共有11312行提交。

更新 numbers 桌子。

id  int(11) NO  PRI NULL    auto_increment
tracking    varchar(255)    YES     NULL    
service varchar(255)    YES     NULL    
notes   text    YES     NULL    
user_id int(11) YES     NULL    
active  tinyint(1)  YES     1   
deleted tinyint(1)  YES     0   
feed    text    YES     NULL    
status  varchar(255)    YES     NULL    
created_at  datetime    YES     NULL    
updated_at  datetime    YES     NULL    
scheduled_delivery  date    YES     NULL    
carrier_service varchar(255)    YES     NULL

6 回复 | 直到 15 年前

OMG Ponies 15 年前

试一试:

  SELECT COUNT(x.id)
    FROM (SELECT t.id,
                 MONTH(t.created_at) 'created_month', 
                 YEAR(t.created_at) 'created_year'
            FROM NUMBERS t) x
GROUP BY x.created_month, x.created_year
ORDER BY x.created_month, x.created_year

在 WHERE GROUP BY 和 ORDER BY 子句,因为不能使用索引。

从我身上 found ,使用DISTINCT/GROUP BY时会出现这种情况。

DavidWinterbottom 15 年前

确保您有一个年和月的覆盖索引(即同一索引中的两个字段),以便查询的ORDER BY组件可以使用索引。这将消除对文件排序的需要,尽管仍然需要临时表来处理分组。

Kevin Peno 15 年前

SELECT
    count(`id`) AS count, MONTH(`created_at`) as month, YEAR(`created_at`) as year
FROM `numbers`
GROUP BY month, year
ORDER BY created_at

据我所知,这将是你能得到的最好的。我创建了一个带有id和datetime列的表,并用10000行填充它。上面的查询使用了一个子选择,但它实际上没有任何不同,并且具有子选择的开销。结果我的时间是0.015s,他的时间是0.016s。

确保你有一个索引 created_at ,这将帮助您完成初始查询。当group by出现时,不使用文件排序是很少见的,但在其他情况下可能会出现这种情况。MySql的文档 an article

Monkey Boson 15 年前

每当MySQL必须在内存中工作,并且该工作超过可用量(innodb_buffer_pool_size)时,它就开始使用磁盘来存储临时工作。您可以增加我提到的变量,但设置得太高可能会导致其他方面的性能问题。

如果运行的是专用服务器,请将其设置为~50-75%。

Quassnoi 15 年前

最好的方法是创建一个helper列,该列包含 YEAR MONTH 连接在一起:

YEAR(created_at) * 100 + MONTH(created_at)

INDEX FOR GROUP BY .

但是,您可以创建两个helper表,第一个表包含合理的年数(例如 1900 到 2100 ),第二个包含月(从 0 到 11

SELECT  (
        SELECT  COUNT(*)
        FROM    numbers
        WHERE   created_at >= '1900-01-01' + INTERVAL y YEAR + INTERVAL m MONTH
                AND created_at < '1900-01-01' + INTERVAL y YEAR + INTERVAL m + 1 MONTH
        )
FROM    year_table
CROSS JOIN
        month_table
WHERE   y BETWEEN 2008 AND 2010

Jonathan 15 年前

对不起,我不得不不同意其他的答案。
covering index .

如果在要搜索的列(创建位置)上添加索引,并且也在您希望从(id)中获得结果的列上,它将比以前快得多。

要加快分组速度,可以更改MySQL服务器设置,以增加tmp表的大小和最大堆表大小,以便临时表位于内存中。