对于这个典型的查询，我的 InnoDB 表上的这些索引是否正确或者我应该更改它们？

Question

我有一个经常运行的 php 查询，如下所示：

$query = 'SELECT * FROM work_orders '
    .'WHERE '    
        . "((end_time >= ?"
  . "AND start_time <= ?) "
        . "OR (start_time <= ? "
  . "AND end_time >= ? ) "
        . "OR (start_time >= ? "
  . "AND end_time <= ? )) ";

和一个表定义为：

CREATE TABLE IF NOT EXISTS `work_orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `work_order_number` varchar(32) COLLATE latin1_general_ci NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `client_name` varchar(128) COLLATE latin1_general_ci NOT NULL,
  `location` varchar(128) COLLATE latin1_general_ci NOT NULL,
  `note` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `note_idx` (`note`),
  KEY `client_idx` (`client_name`),
  KEY `location_idx` (`location`),
  KEY `start_time_idx` (`start_time`),
  KEY `end_time_idx` (`end_time`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

我经常对如何创建索引感到困惑。这是一个读取量很大的表，需要对数据进行大量搜索，这就是为什么我对每一列都建立了索引，但到目前为止，最常运行的查询使用开始日期和结束日期的 3 种组合来确定工作订单是否属于特定范围日历范围。我应该像现在一样在 start_time 和 end_time 上分别建立一个索引，还是应该从两者中创建一个复合键？有没有更好的方法来设置索引？我应该使用 InnoDB 吗？我根本不使用交易。

Answer 1

这个答案有两个方面。首先，我会将索引设置为：

KEY start_time_idx (start_time, end_time)

但是（这是第二个问题）：查询将无法使用任何索引。原因如下：

SELECT *
FROM work_orders
WHERE (
  (end_time >= <some-date> AND start_time <= <some-date>)
  OR
  (end_time >= <some-date> AND start_time <= <some-date>)
  OR
  (end_time >= <some-date> AND start_time <= <some-date>)
)

一旦使用 OR 语句，您实际上就禁用了 OR 语句涉及的字段上的索引。由于你的WHERE语句没有OR语句不涉及的其他字段，因此不会使用索引。

我怀疑这是在 PHP 脚本中执行的。您可以访问 phpMyAdmin 吗？然后尝试运行前面带有 EXPLAIN 的查询。它会给你一些提示。

如果表包含大量数据，您可能需要将此查询更改为 3 个查询，每个查询仅查询单个日期范围，然后在 PHP 中连接结果。

/卡斯滕