mysql - 如何更改我的 MySQL 查询或表布局以使用索引

Question

我正在为我的表格布局搜索有效的索引。或者可能需要提示来更改我的表格布局。

我有一张 table start , end和 actual值(时间戳，在下面的示例中用小数字简化)。 actual可以增加直到达到 end .

CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `start` int(10) unsigned NOT NULL DEFAULT '0',
  `actual` int(10) unsigned NOT NULL DEFAULT '0',
  `end` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `actual` (`actual`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `t1`
(`id`, `start`, `actual`, `end`)
VALUES 
(1, 1, 0, 5),
(2, 1, 6, 6),
(3, 2, 8, 9),
(4, 2, 5, 9);

在我的 SELECT结果我想要表中的所有行 actual值小于当前时间戳(为简化示例假设当前时间戳为 7)。另外我只想要这些行 actual小于 end 的值.这第二个条件造成了问题。

SELECT `id`
  FROM `t1`
  WHERE `actual` < `end`
    AND `actual` < 7;

+----+
| id |
+----+
|  1 |
|  4 |
+----+
2 rows in set (0.00 sec)

索引将用于actual < 7 , 但我想不是 actual < end .因为 actual < end 的比较将对所有旧行执行查询随着表中的每个新(旧)行而变慢。

end < 7没有解决问题，因为我想要 actual < end 的过时行在结果中。

我可以在名为 remaining 的表中添加一个新的计算列值为 end - actual并使用 WHERE条件 WHERE remaining > 0 AND actual < 7 (并确保更改索引或创建一个新索引)。但我对此有疑问，感觉表格布局很糟糕。如果有人更新end并且忘记更新计算的 remainig我有一个断行。

解释结果:

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | actual        | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

将键定义更改为:

KEY `actual_end` (`actual`,`end`)

解释结果:

+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | range | actual_end    | actual_end | 4       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+

这最后的Explain 证明索引用于actual < 7对于 actual < end，不 .对于 10 亿个古老的行，最后一个条件将检查 10 亿行。我想优化这个问题。

Answer 1

您可以在 actual 和 end 这两个列上创建连接(复合)索引，因此索引支持这两种情况。

ALTER TABLE t1 ADD INDEX `ActualEnd` (`actual`, `end`)

您还可以检查this线程..

更新:

说明您刚刚添加的显示您的查询是 Using index，在添加最适合此查询的复合索引之后。不需要临时表或写入光盘。由于您的表使用的是 InnoDB 引擎，并且它在 id 列上有 PRIMARY key，因此 PRIMARY key 会自动添加到复合索引的末尾，从而可以满足您直接从索引查询中提出的所有要求.您没有对索引值执行任何操作，没有使用任何会破坏索引使用的函数，因此没有理由不使用索引。解释只是证明。