更新查询的执行时间非常长？

Question

我正在使用MySQL 5.6。

在我的表invoices中，我添加了两个日期时间列，我将根据另一个列是某个值来设置这些列。

UPDATE invoices
SET twoWeekAlert = DATE_ADD(now(), INTERVAL 2 WEEK)
WHERE state = 6;

只有205条记录的状态=6，3,500条记录。

5分钟后，我取消了查询，在state列上创建了一个索引，然后再试一次。十分钟后我取消了那个。

怎么回事，这是使用日期时间计算函数更新MySQL的已知问题吗？我担心，因为我知道我将不得不运行类似的类型更新，我不能让它花那么长时间。

我相信的EXPLAIN语句告诉我它使用的是我的索引：

selectType   table     type   possible_keys     key        key_len    ref    rows   extra
SIMPLE       invoices  range  stateIndex        stateindex  2          const  205 using where

在我的解释声明中没有警告。

我的表的CREATE语句

CREATE TABLE `invoices` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `number` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `parentSOId` int(11) DEFAULT NULL,
  `parentProjectId` int(11) DEFAULT NULL,
  `status` int(1) DEFAULT '1',
  `active` int(1) DEFAULT '1',
  `dateEntered` varchar(45) DEFAULT NULL,
  `dateDue` varchar(45) DEFAULT NULL,
  `individualId` int(11) DEFAULT '-1',
  `amount` decimal(11,2) DEFAULT '0.00',
  `margin` decimal(11,2) DEFAULT '0.00',
  `comment` varchar(500) DEFAULT '',
  `custContactId` int(11) DEFAULT '-1',
  `custBuyerId` int(11) DEFAULT '-1',
  `taxable` int(11) DEFAULT NULL,
  `taxAmount` decimal(11,2) DEFAULT NULL,
  `totalAmount` decimal(11,2) DEFAULT NULL,
  `paymentTerms` int(11) DEFAULT '-1',
  `type` int(11) DEFAULT '-1',
  `shipVia` int(11) DEFAULT '-1',
  `manTax` int(1) DEFAULT '0',
  `state` tinyint(4) DEFAULT '0',
  `sentToContNotNeeded` int(1) DEFAULT '0',
  `sentToAcctNotNeeded` int(1) DEFAULT '0',
  `twoWeekAlert` datetime DEFAULT NULL,
  `threeWeekAlert` datetime DEFAULT NULL,
  PRIMARY KEY (`idx`),
  KEY `invoiceNum` (`number`),
  KEY `invoiceName` (`name`),
  KEY `fk-listprojects_idx` (`parentProjectId`),
  KEY `soIdIndex` (`parentSOId`),
  KEY `stateInd` (`state`),
  CONSTRAINT `fk-listprojects` FOREIGN KEY (`parentProjectId`) REFERENCES `listprojects` (`idx`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3604 DEFAULT CHARSET=latin1;

我也尝试过：

通过主键( WHERE idx IN (list of ids) )来完成

而不是使用日期时间，只是使用日期，因为它已经足够满足我的需要和使用DATE_ADD(curDate(), INTERVAL 2 WEEK)

我尝试了两种方法:日期时间类型的NOW()和日期类型的curDate()。两者都有同样的问题。

在更强大的机器上运行。还是同样的问题。

幸运的是，我只是被告知，不要追溯影响旧记录，所以我将只做一次记录，这似乎是可行的。我仍然很好奇，为什么这似乎要花费很长时间，特别是当我是开发数据库上唯一的连接时。

我的服务器是本地的，在办公室里，在衣柜里的机器上运行。所以，裸露的金属，而不是云或ESX。

SELECT IFNULL(state,'Total') state_value, COUNT(1) rowcount 
FROM invoices 
GROUP BY state WITH ROLLUP;

结果：

-1  14
0   3217
2   5
4   54
5   9
6   205
Total   3504

SHOW INDEX ON invoices结果：

Table        -  invoices
Non_Unique   -  1
Key_Name     -  stateIndex
Seq_in_Index -  1  (Same for all other indexes)
Column_name  -  state
Collation    -  A (all other indexes have an A for this)
Cardinality  - 12 (All other indexes have a Cardinality of 3476)
Sub_part     - Null (Same for all other indexes)
Packed       - Null (Same for all other indexes)
Null         - YES (Same for all other indexes)
Index_type   - BTREE (Same for all other indexes)

Answer 1

你的问题很简单

问题#1 :密钥分发

MySQL查询优化器评估最佳操作过程，并将其发布在解释计划中。请注意，解释计划在使用selectType SIMPLE索引时表示state。这是索引扫描。

我有一个基本的经验法则，我总是遵循:如果一个索引必须读取一个表的5%以上，同时仔细阅读一个索引，它就开始进行扫描。你基本上已经到了临界点。为什么？

• 要更新的205行
• 3504行总数
• 5.85%正在读取/更新的表行

我不只是想出这个主意。我一直在DBA StackExchange中提到过这一点(我的文章来自Nov 13, 2012：索引必须覆盖所有选定的列才能用于订单？和其中提到的其他帖子)

问题#2 :硬件

的配置

这张表太小了，不能说它只是索引。如果您使用的硬件很小(1 vCPU)，不要期望使用MySQL 5.6的任何性能。在某些情况下，MySQL 4.1在单个CPU环境中的性能要优于MySQL 5.6。你现在可能就在那个角落里。

看我的帖子

• Apr 26, 2012：CPU性能与数据库服务器相关吗？
• Jun 19, 2011：如何正确地执行MySQL烘焙？

从这里到哪里去.

在这一点上，最好的选择是运行OPTIMIZE TABLE invoices;。这将缩小该表(消除碎片)并刷新索引统计数据。在高写入生产环境中，您可以运行ANALYZE TABLE invoices;只刷新索引统计数据，并在维护窗口期间运行OPTIMIZE TABLE invoices;。

另外，尝试为测试服务器设置更多的CPU。

试试看！