mysql 查询速度慢/可能的查询问题

Question

我有 2 个（类似的）查询：

-- Query #1 - get all new products not in currently in the Product table
-- Should match any products in the temp table that do not exist in the Product table
INSERT
  INTO `tmpProductState` (`ProductId`, `ChangedOn`, `State`)
SELECT t.`ProductId`, t.`ProcessedOn`, \'Activated\'
  FROM `tmpImport` t
  LEFT JOIN `Product` p USING (`ProductId`)
 WHERE p.`ProductId` IS NULL
    ON DUPLICATE KEY UPDATE
       `State` = VALUES(`State`)

-- Query #2 - get all Products that are removed from the Product table
-- Should match any products in the Product table that do not exist in the temp table
INSERT
  INTO `tmpProductState` (`ProductId`, `ChangedOn`, `State`)
SELECT p.`ProductId`, p.`LastSeenDate`, \'Deactivated\'
  FROM `Product` p
  LEFT JOIN `tmpImport` t USING (`ProductId`)
 WHERE t.`ProductId` IS NULL
    ON DUPLICATE KEY UPDATE
       `State` = VALUES(`State`)

在第一次运行（第 1 天）时，当 Product 表为空时，两个查询都在 1 秒内运行，但是，在第二次运行时（第 2 天），当 Product 表有 14000 条记录时，第一个查询运行时间不到 2 秒，第二个查询运行时间为 244 秒。每次连续的数据导入都是相同的（查询 #2 为 240-250 秒。在检查数据库时，所有数据似乎都是正确的，我只是不明白为什么第二个查询花了这么长时间。

---> 编辑：慢查询日志：

# Query_time: 245.328784  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 187711973
SET timestamp=1305151558;

INSERT
  INTO `tmpProductState` (`ProductId`, `ChangedOn`, `State`)
SELECT p.`ProductId`, p.`LastSeenDate`, 'Deactivated'
  FROM `Product` p
  LEFT JOIN `tmpImport` t USING (`ProductId`)
 WHERE t.`ProductId` IS NULL
    ON DUPLICATE KEY UPDATE
       `State` = VALUES(`State`);

此时我最关心的是： Rows_examined: 187711973 （它到底是如何检查这么多行的？） Product 表大小约为 14000 条记录，导入表大小为~28000 条记录，tmpProductState 最多为 60 条记录

---> 另一个编辑：

EXPLAIN SELECT p.`ProductId` , p.`LastSeenDate` , 'Deactivated'
FROM `Product` p
LEFT JOIN `tmpImport` t
USING ( `ProductId` )
WHERE t.`ProductId` IS NULL 

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE          p       ALL     NULL            NULL    NULL            NULL    14151
1   SIMPLE          t       index   NULL            PRIMARY 100             NULL    28166   Using where; Using index; Not exists

涉及的表：

CREATE TABLE IF NOT EXISTS `tmpImport` (
  `CategoryId`             smallint(5) unsigned NOT NULL,
  `ProcessedOn`            date DEFAULT NULL,
  `ProductId`              varchar(32) NOT NULL,
  `Title`                  varchar(255) DEFAULT NULL,
  `Description`            text,
  `ActivateDate`           date DEFAULT NULL,
  PRIMARY KEY (`CategoryId`,`ProductId`)
) ENGINE=MyISAM DEFAULT CHARSET = UTF8

CREATE TABLE IF NOT EXISTS `tmpProductState` (
  `ProductId` VARCHAR(32) NOT NULL,
  `ChangedOn` DATE NOT NULL,
  `State` ENUM(\'Activated\',\'Deactivated\'),
  PRIMARY KEY(`ProductId`,`ChangedOn`)
) ENGINE = Memory

CREATE TABLE `Product` (
  `ProductId` varchar(32) NOT NULL,
  `Title` varchar(255) DEFAULT NULL,
  `Description` text,
  `ActivateDate` date DEFAULT NULL,
  `LastSeenDate` date DEFAULT NULL,
  PRIMARY KEY (`ProductId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Answer 1

您的表没有标准化，您没有有效的索引，并且您的连接......不寻常。

我假设您无法对表之间的数据总重复做任何事情，所以让我们忽略这一点。

看起来您正在连接的表中的列之间复制数据，您应该在连接中使用所有这些列。所以它可能应该是：

LEFT JOIN `tmpImport` t USING (`ProductId`, `Title`, `Description`, `ActivateDate`)

将索引添加到与您要连接或过滤的字段相对应的表中。不要将复合键作为主键。相反，添加一个自动递增字段作为 PK，并在需要强制唯一性时使用唯一的键。产品表和 tmpImport 都应该有连接的每一列的键。

希望其中一些想法对您有所帮助。

Answer 2

很晚才回复此问题，但您的第一个查询是从 tmpImport 获取所有记录，并且他们使用 Product 表上的主键从 Product 获取所有记录。这非常有效。第二个查询是从 Product 获取所有记录，然后从 tmpImport 获取匹配记录，但没有使用 ProductId 上的任何索引。 em>tmpImport。因此运行得很糟糕。

将 ProductId 上的索引添加到 tmpImport 表中（此连接将忽略主键中的 ProductId，因为它不是表中的第一列密钥，并且您没有使用第一个 CategoryId ）。