mysql - 当我从 >= DATE 切换到 BETWEEN DATE and DATE 时，为什么此查询花费的时间如此之长？

Question

此查询需要很长时间才能完成。当我将 WHERE 子句设置为 new_dl >= '2014-01-01' 时，查询大约需要 6 分钟才能浏览大约 3 个月的数据。现在不知道为什么这个应该从 12 个月的数据中选择的查询已经运行了一个小时二十分钟，但仍然没有完成。

SELECT      customers.kayako_id,
            customers.firstname,
            customers.lastname,
            customers.address1_city,
            customers.address1_stateorprovince,
            customers.address1_postalcode,
            customers.new_product, 
            customers.new_total,
            MAX(last_modified)
FROM        customers
INNER JOIN  (
            SELECT kayako_id, max(new_total) Price
            FROM customers
            GROUP BY kayako_id
            ) NewTable 
            ON customers.kayako_id = NewTable.kayako_id 
            and customers.new_total = NewTable.Price
WHERE       new_dldate BETWEEN '2013-01-01' AND '2014-01-01'
GROUP BY    customers.kayako_id, 
            customers.firstname, 
            customers.lastname,
            customers.address1_city,
            customers.address1_stateorprovince,
            customers.address1_postalcode,
            customers.new_product, 
            customers.new_total;

customers http://imageshack.com/a/img543/6840/74do.png

CREATE TABLE `customers` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`last_modified` DATETIME NULL DEFAULT NULL,
`kayako_id` INT(11) NULL DEFAULT NULL,
`firstname` VARCHAR(255) NULL DEFAULT NULL,
`lastname` VARCHAR(255) NULL DEFAULT NULL,
`new_product` VARCHAR(255) NULL DEFAULT NULL,
`new_total` DECIMAL(10,2) NULL DEFAULT NULL,
`new_dldate` DATE NULL DEFAULT NULL,
`address1_city` VARCHAR(255) NULL DEFAULT NULL,
`address1_stateorprovince` VARCHAR(255) NULL DEFAULT NULL,
`address1_postalcode` VARCHAR(255) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=432290;

INSERT INTO customers values (1,'2013-01-02', 12345, 'bob', 'smith', 'CLO50', 39.99,'2013-01-02','portland', 'OR',97229);
INSERT INTO customers values (2,'2013-01-04', 12345, 'bob', 'smith', 'CLO50', 29.99,'2013-01-02','portland', 'OR',97229);
INSERT INTO customers values (3,'2013-01-05', 12345, 'bob', 'smith', 'CLO50', 59.99,'2013-01-02','portland', 'OR',97229);
INSERT INTO customers values (4,'2013-01-05', 78654, 'joe', 'guy', 'CLO60', 39.99,'2013-01-02','salem', 'OR',97229);
INSERT INTO customers values (5,'2013-01-05', 45698, 'karen', 'min', 'CLO40', 49.99,'2013-01-02','eugene', 'OR',97229);
INSERT INTO customers values (6,'2014-01-06', 82987, 'sue', 'jones', 'Sub-CLO50', 29.99,'2014-01-02','portland', 'OR',97229);
INSERT INTO customers values (7,'2008-01-02', 39845, 'jack', 'sam', 'CLM50', 49.99,'2008-01-02','corvallis', 'OR',97330);
INSERT INTO customers values (8,'2013-01-05', 65189, 'steve', 'ou', 'CLO50', 59.99,'2013-01-02','portland', 'OR',97229);
INSERT INTO customers values (9,'2013-01-06', 19999, 'matt', 'kim', 'CLO60', 39.99,'2013-01-02','beaverton', 'OR',97005);
INSERT INTO customers values (10,'2013-01-07', 19999, 'matt', 'kim', 'CLO60', 59.99,'2013-01-02','beaverton', 'OR',97005);
INSERT INTO customers values (11,'2013-01-08', 19999, 'matt', 'kim', 'CLO60', 29.99,'2013-01-02','beaverton', 'OR',97005);

Answer 1

造成差异的原因之一可能是您选择的数据量不同:

WHERE clause to new_dl >= '2014-01-01', the query took about 6 minutes

WHERE new_dldate BETWEEN '2013-01-01' AND '2014-01-01'

查询连接时间不一定是线性的(即，如果将所选行数加倍，则时间大约加倍)。在这种情况下，它是二次的 - 或者更糟。这意味着，如果将所选行数增加约十二倍，则查询时间将增加约一百四十四倍。

在这种情况下，customers 表实际上根本不是“客户”表，因为任何一个客户都可能在其中出现多次。它更像是一个 kayako 表，应该与 customers 连接。在 kayako 表中，仅需要客户 ID、kayako ID、价格和最后修改时间。

我们想要列出给定时间范围内 kayako 的最高价格和最后修改日期。因此，我们需要在日期上建立一个索引作为第一个元素，然后在 kayako_id 上建立索引来进行分组，最后我们也可以从索引中的其他两个字段中受益，以避免实际点击表来获取我们需要的数据。

我们在 customers 表上执行此操作:

CREATE INDEX customers_kayako_ndx ON customers
   (new_dldate, kayako_id, new_total, last_modified);

现在我们将有来自任何一个 kayako 的多个相同的“客户”实例，并且需要选择哪个客户来代表它们；我们选择具有更大客户 ID 的那个...这可能与最新的 last_modified 相同；但也请参阅底部的不同方式。所需的选择是:

SELECT
   MAX(id) AS chosen,
   MAX(new_total) AS Price,
   MAX(last_modified) AS modified
FROM customers
WHERE      
   new_dldate BETWEEN '2013-01-01' AND '2014-01-01'
GROUP BY kayako_id

然后我们需要用实际的客户数据来丰富这个选择；我们通过 JOIN 来完成此操作。

SELECT
  customers.kayako_id,
  customers.firstname,
  customers.lastname,
  customers.address1_city,
  customers.address1_stateorprovince,
  customers.address1_postalcode,
  customers.new_product, 
  Price,
  last_modified
FROM (
    SELECT
       MAX(id) AS chosen,
       MAX(new_total) AS Price,
       MAX(last_modified) AS modified
    FROM customers
    WHERE      
       new_dldate BETWEEN '2013-01-01' AND '2014-01-01'
    GROUP BY kayako_id
  )  AS selection 
  JOIN customers ON (customers.id = selection.chosen)
;

为了提高性能，我们实际上可以分成两个不同的表，通过 kayako_id 而不是 ID 来识别客户:

CREATE TABLE kayako AS
    SELECT kayako_id, new_total, new_dldate, last_modified
FROM customers;

CREATE TABLE `new_customers` (
`kayako_id` integer not null primary key auto_increment,
`firstname` VARCHAR(255) NULL DEFAULT NULL,
`lastname` VARCHAR(255) NULL DEFAULT NULL,
`new_product` VARCHAR(255) NULL DEFAULT NULL,
`address1_city` VARCHAR(255) NULL DEFAULT NULL,
`address1_stateorprovince` VARCHAR(255) NULL DEFAULT NULL,
`address1_postalcode` VARCHAR(255) NULL DEFAULT NULL
);

上面假设last_modified与kayako相关。否则，如果它引用客户修改日期，则应进入new_customers(如果您想保留客户信息历史记录，最好还添加一个 bool 列active以帮助SELECT)，并且应该相应地修改查询，方法是选择具有最新修改日期的客户并稍微使用不同的索引:

CREATE INDEX customers_kayako_ndx ON customers(new_dldate, kayako_id, new_total, last_modified);
CREATE INDEX customers_join_ndx ON customers (kayako_id, last_modified);

SELECT
  customers.kayako_id,
  customers.firstname,
  customers.lastname,
  customers.address1_city,
  customers.address1_stateorprovince,
  customers.address1_postalcode,
  customers.new_product, 
  Price,
  last_modified
FROM (
    SELECT
       kayako_id,
       MAX(new_total) AS Price,
       MAX(last_modified) AS modified
    FROM customers
    WHERE      
       new_dldate BETWEEN '2013-01-01' AND '2014-01-01'
    GROUP BY kayako_id
  )  AS selection 
  JOIN customers ON (
      customers.kayako_id = selection.kayako_id
      AND
      customers.last_modified = selection.last_modified
  )
;