时间戳

首页 > 解决方案 > 按日期列的功能分组时查询慢

mysql - 按日期列的功能分组时查询慢

问题描述

我有一个相当大的表（大约 8000 万行），其中包含基于特定财务状况（即存款、合同期限和允许的年度里程）的特定车辆最低价格的汇总定价数据。最低价格每天汇总。

我想再创建两个表；从此表中获取的每周和每月聚合，每天更新。这是主表的创建表语句。

CREATE TABLE `offers_lowest` (
  `cap_id` int(10) unsigned NOT NULL,
  `date` date DEFAULT NULL,
  `lowest_price` decimal(10,2) DEFAULT NULL,
  `deposit` tinyint(3) unsigned DEFAULT NULL,
  `term` tinyint(3) unsigned DEFAULT NULL,
  `mileage` int(11) DEFAULT NULL,
  `finance_type` enum('P','B') DEFAULT NULL,
  `offer_id` int(10) unsigned DEFAULT NULL,
  `broker_id` int(10) unsigned DEFAULT NULL,
  KEY `idx_lowest_price` (`lowest_price`),
  KEY `idx_specials_extra` (`finance_type`,`date`),
  KEY `idx_specials_id_price` (`offer_id`,`lowest_price`),
  KEY `idx_price_history` (`cap_id`,`deposit`,`term`,`mileage`,`finance_type`,`date`,`offer_id`,`broker_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

我为每周汇总创建了一个表。这是我想在 INSERT INTO 语句中使用的查询：

SELECT
    cap_id,
    DATE_ADD(date, INTERVAL(-WEEKDAY(date)) DAY),
    MIN(lowest_price) as lowest_price,
    deposit,
    term,
    mileage,
    finance_type,
    offer_id,
    broker_id
FROM
    offers_lowest
GROUP BY cap_id , deposit , term , mileage , finance_type , DATE_ADD(date, INTERVAL(-WEEKDAY(date)) DAY) , offer_id , broker_id

该查询在没有日期功能的情况下运行约 50 毫秒，但使用它运行几分钟（我没有离开它足够长的时间来找出它到底慢了多少）。

我认为我的索引是正确的，除非您可以对列的函数进行索引？我已经阅读了这个，但无法弄清楚。

我应该在主表中创建一个新列并用每个日期值的开始日期填充它，然后创建一个新索引吗？如果是这样，最好的方法是什么？我试过了：

ALTER TABLE offers_lowest
ADD COLUMN week_start;
UPDATE TABLE offers_lowest (week_start)
SET week_start = DATE_ADD(date, INTERVAL(-WEEKDAY(date)) day)

无济于事。

编辑：理想情况下，星期将显示为日期（DD/MM/YYY），月份显示为完整的月份名称。

标签： mysqlsql

解决方案

主要的性能问题是由一次又一次地重建汇总表引起的，而不是增量地进行。

表中有多少行？数据集中有多少天？（我想知道汇总表给您带来了多大的收缩。）

考虑为每一天创建一行，然后从中得出每周和每月（以及任何其他日期范围）。请注意，一周的最小值是 7 个每日值的最小值。

一个小的简化：

DATE_ADD(date, INTERVAL(-WEEKDAY(date)) DAY),

-->

date - INTERVAL WEEKDAY(date) DAY

关于你的第一个SELECT ... GROUP BY，一旦你建立了汇总表，你就不需要重建它。也就是说，只INSERT .. SELECT使用最新的时间单位。而不是在 GROUP BY,

 WHERE date >= CURDATE() - INTERVAL 1 DAY
   AND date  < CURDATE()

（这涵盖了“昨天”。）这将解决主要的性能问题；我的其余观点是其他提示。

显示为日期 (DD/MM/YYY)

用于DATE_FORMAT()实现该格式，但将日期存储为“YYYY-MM-DD”，以便可以执行算术运算。

推荐阅读