简单，索引良好，MySQL查询大约需要一秒钟

Question

我在我的web-app中使用了以下查询。它正在查询一个结果表，该表包含给定时间网上商店的价格。它有大约一百万条记录。索引是一个shopID、StartTime、pID和WebsiteID。

查询：

SELECT shopID, tresults.StartTime, tresults.Price
FROM tresults
WHERE tresults.pID = 7  
    AND WebsiteID = 1 AND StartTime BETWEEN "2013-4-10" AND "2013-4-11"
    AND tresults.shopID IN (44, 68, 23, 16, 144, 8, 9, 5) 
GROUP BY tresults.StartTime, tresults.Price 
ORDER BY tresults.StartTime, tresults.Price

解释结果：

1, SIMPLE, tresults, index_merge, PRIMARY,idxPID,idxWebsite,idxStartTimeASC,idxStartTimeDESC,fk_shopID, idxPID,idxWebsite, 4,4, , 1062, Using intersect(idxPID,idxWebsite); Using where; Using temporary; Using filesort

在我看来很好，但是查询仍然需要大约一秒钟的时间才能完成。这在一个快速的网站中是不可接受的。

我怎么才能加快速度呢？

注意:速度看起来取决于店铺it的数量

谢谢!

Answer 1

创建以下索引：

CREATE INDEX idxTresults_pwsp ON tresults (pId, WebsiteID, StartTime, Price);

这是缩小搜索范围的最好方法，它还可以防止“使用临时”和“使用文件排序”。

mysql> EXPLAIN 
SELECT shopID, tresults.StartTime, tresults.Price
FROM tresults
WHERE tresults.pID = 7  
    AND WebsiteID = 1 AND StartTime BETWEEN '2013-4-10' AND '2013-4-11'
    AND tresults.shopID IN (44, 68, 23, 16, 144, 8, 9, 5) 
GROUP BY tresults.StartTime, tresults.Price 
ORDER BY tresults.StartTime, tresults.Price 
\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tresults
         type: ref
possible_keys: idxpid,idxStarttime,fkShop_id,idxTresults_pwsp
          key: idxTresults_pwsp
      key_len: 9
          ref: const,const
         rows: 1
        Extra: Using where

这样做效果更好的原因是，索引的前两列有助于为特定的pID和WebsiteID选择正确的行子集。由于您选择了一个特定的pID和WebsiteID，因此这个新索引中选择的条目已经保证按照StartTime和Price的最佳顺序进行排序，因此查询不需要创建临时表。它只是按照条目存储在索引中的顺序来访问条目。

PS: MySQL不支持“升序”和“降序”索引。它接受关键字，但不会对它们做任何不同的事情。因此，在给定列上不需要这两种类型的索引。

PPS:不要对字符串或日期文字使用双引号。使用单引号，以符合ANSI SQL。如果您曾经使用过其他RDBMS品牌，您会更好地养成这个习惯，因为大多数其他品牌都会根据标准使用引号。MySQL允许以非标准方式使用双引号。

Answer 2

你的查询并不像你想的那样好。它使用“使用临时”和“使用文件排序”。这意味着它已经保存在您的文件系统中，以便由解析器操作，从而使查询速度变得更慢。

为了改进它，您可以尝试使子句WHERE上的字段与表中的索引相匹配。或者，按照以下顺序创建一个新索引: pID，websiteID，startTime，如果您的查询总是使用这些元素运行的话。

我也不明白为什么查询中有两次tresults.pID字段。

编辑

嘿，伙计，你得到临时表是因为你正在使用聚合函数来操作你的结果集，例如GROUP BY，ORDER BY，HAVING等等。只要临时表保存在内存中就可以了，这是访问数据的最快方法。问题是当这些表达到如此大小时，解析器必须将它们带到文件系统中。

您可以尝试找到最佳平衡，更改tmp_table_size和max_heap_table_size的值。此外，如果内存存储不支持博客/文本列，请尽量避免使用它们。

您可以在官方文档中找到更多信息。