数据库性能问题

Question

在非常基本的查询中，我遇到了一些查询时间长的问题。这是我第一次使用一个更大的表(1250万行)，我只是想弄清楚什么是瓶颈，或者我能做些什么来提高性能。所有数据都在一个表中。

服务器规范：

Windows 2008 R2标准英特尔Xeon X3430 @2.40GHz4GBRAM 1TB 7200RPMHDD

表信息

ID int(10) UN

商店 int(2) UN

日期日期时间

寄存器 int(2) UN

收银员 int(3) UN

部门 int(4)联合国

总十进制(7，2)

Customers int(5) UN

项目 int(5) UN

Time int(5) UN

示例查询和Times

SELECT Store, sum(Total)   
FROM sales   
GROUP BY Store

19.56sec

SELECT Date, Register, Customers, Items, Total   
FROM sales  
WHERE Date(Date) = Date('2013-10-22')   
AND Store = 1

9.59sec

当然，我可以提供更多的信息，但是否有什么显而易见的事情可以解释为什么这些数据运行得如此缓慢？

Answer 1

是的，有两个突出的问题。在第一个查询中，您没有标准--因此您要求读取、分组和总结整个sales表。分组还将创建一个临时表。因此，您将进行表格扫描，这将仅限于硬件可以提供的io性能，这可能是您的瓶颈。

在第二个查询中，您要对Date列Date( Date )执行一个函数，这意味着即使您有一个日期索引，它也将无法利用该索引，因此您将再次对表进行扫描。

这么说，您应该运行explain扩展SELECT。在每个查询中获得对此评估的信任。

Answer 2

查询1：

SELECT Store, sum(Total)
FROM sales
GROUP BY Store ;

在(Store, Total)上添加索引

ALTER TABLE sales
  ADD INDEX store_total_IX             -- pick a name for the index
    (store, total) ;

查询2：

SELECT Date, Register, Customers, Items, Total
FROM sales
WHERE Date(Date) = Date('2013-10-22')
AND Store = 1 ;

在(Store, Date)上添加索引

ALTER TABLE sales
  ADD INDEX store_date_IX             -- pick a name for the index
    (store, date) ;

并重写查询-以便索引可以使用-如下：

SELECT Date, Register, Customers, Items, Total
FROM sales
WHERE Date >= '2013-10-22'
  AND Date < '2013-10-22' + INTERVAL 1 DAY
  AND Store = 1 ;

Answer 3

对于第二个查询，您可以尝试两种方法：(1)为datetime列创建索引。(2)使用整数代替日期时间，2013年-10-22 => 20131022