基于YEAR()索引的MySQL Join - 列添加或生成列

Question

基于https://dev59.com/yUnSa4cB1Zd3GeqPSOgs#1601812的答案：

我正在使用以下SQL查询：

FROM workdone
LEFT JOIN staffcost ON YEAR(workdone.date) = staffcost.costyear

上述查询未使用我在 workdone.date 列上创建的索引，因此非常缓慢。我有两个选项，我认为：

选项1

添加另一个列 workdone.year，通过表 oncreate 和 onupdate 事件来更新该列。在查询中使用此列。

选项2

添加生成 (虚拟/持久性) 列 workdone.year，然后在查询中使用此列。

我的问题：

1. 从性能和数据重复性方面考虑，哪个选项更好？
2. 我应该使用虚拟列还是持久化列类型？
3. 是否有更好的替代方案？

---

更新1.1

我实施了OJones建议的解决方案，但“explain”显示索引未被使用。以下屏幕截图是不正确的吗？

- Adarsh Madrecha

---

需要使用LEFT JOIN吗？ - Paul Spiegel

@PaulSpiegel，由于第二个表可能没有记录，因此加入LEFT JOIN是必要的。将其改为INNER JOIN可以提高性能吗？如果是yes，那么可以在应用程序逻辑中强制要求在第二个表中包含一条记录并将JOIN转换为INNER JOIN。 - Adarsh Madrecha

你用生成列的方法解决问题让我感到困惑。这不是这个查询的解决方案。你需要在 staffcost.costyear 上建立一个索引。 - Paul Spiegel

由于表中的行数太少，您不能信任EXPLAIN来获取“正确”的查询计划。如果有更多的行会发生什么？特别是如果数据跨越超过10年。 - Rick James

Answer 1

你可以尝试这个方法：

FROM workdone
LEFT JOIN staffcost ON workdone.date >= MAKEDATE(staffcost.costyear, 1)
                   AND workdone.date <  MAKEDATE(staffcost.costyear+1, 1)

这将允许使用 workdone.date 索引来搜索从 costyear 的第一天到 costyear+1 的第一天之前的日期范围。通常，这种范围搜索可以利用索引，而函数（如 YEAR(datestamp)）则不能。

- O. Jones

---

我尝试了你的解决方案，并在问题中更新了屏幕截图。我正在使用MariaDB 10.1。 - Adarsh Madrecha

只有在右表（staffcost）上建立索引才能使用LEFT JOIN。 - Paul Spiegel

@PaulSpiegel - 我同意你的观点。然而，在某些情况下，优化器足够聪明，意识到LEFT是无关紧要的，并愿意执行普通的JOIN。 - Rick James

@RickJames - 那就不是LEFT JOIN，只是一个伪装的INNER JOIN :-) - Paul Spiegel

我希望人们不要藏在面具后面 - 将“LEFT”更改为“INNER”以澄清意图。 - Rick James

Answer 2

我认为生成的列是更好的选择。无论您是否将其持久化，都不会有任何差别。如果您对其进行索引，则会有所不同。

MySQL（使用Innodb）支持虚拟列上的索引。因此，您可以这样做。或者持久化该列并使用它。

话虽如此，我认为这个查询不会有太大的差别。年份选择并不高限制性。而且，您正在针对另一个表而不是常量进行操作。在staffcost（costyear）上建立索引似乎更重要。

- Gordon Linoff

Answer 3

你的查询语句本身没有问题。但是一个带有LEFT JOIN的查询只能使用右表（staffcost）上的索引。左表（workdone）上没有索引可以支持连接操作。所以你只需要在staffcost(costyear)上建立索引即可。

你可以使用以下脚本进行测试：

DROP TABLE IF EXISTS `staffcost`;
CREATE TABLE IF NOT EXISTS `staffcost` (
  `id` int(10) unsigned NOT NULL,
  `costyear` year(4) NOT NULL,
  `data` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `staffcost` (`id`, `costyear`, `data`) VALUES
    (1, '2018', '0.6555866465490187'),
    (2, '2019', '0.12234661925802624'),
    (3, '2020', '0.64497318737672'),
    (4, '2021', '0.8578261098431667'),
    (5, '2022', '0.354211017819318'),
    (6, '2023', '0.19757679030073508'),
    (7, '2024', '0.9252509287793663'),
    (8, '2025', '0.03352430372827156'),
    (9, '2026', '0.3918687630369037'),
    (10, '2027', '0.8587709347333489');

DROP TABLE IF EXISTS `workdone`;
CREATE TABLE IF NOT EXISTS `workdone` (
  `id` int(10) unsigned NOT NULL,
  `date` date NOT NULL,
  `data` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `workdone` (`id`, `date`, `data`) VALUES
    (1, '2017-12-31', '0.40540353712197724'),
    (2, '2018-01-01', '0.8716141803857071'),
    (3, '2018-01-02', '0.1418603212962489'),
    (4, '2018-01-03', '0.09445909605776807'),
    (5, '2018-01-04', '0.04671454713373868'),
    (6, '2018-01-05', '0.9501954782290342'),
    (7, '2018-01-06', '0.6108337804776'),
    (8, '2018-01-07', '0.2035824984345422'),
    (9, '2018-01-08', '0.18541118147355615'),
    (10, '2018-01-09', '0.31630844279779907');

EXPLAIN
SELECT * FROM workdone
LEFT JOIN staffcost ON YEAR(workdone.date) = staffcost.costyear;

ALTER TABLE `staffcost` ADD INDEX `costyear` (`costyear`);

EXPLAIN
SELECT * FROM workdone
LEFT JOIN staffcost ON YEAR(workdone.date) = staffcost.costyear;

SELECT VERSION();

结果：

id|select_type|table    |type|possible_keys|key|key_len|ref|rows|Extra
 1|SIMPLE     |workdone |ALL |             |   |       |   |  10|
 1|SIMPLE     |staffcost|ALL |             |   |       |   |  10|Using where; Using join buffer (flat, BNL join)

id|select_type|table    |type|possible_keys|key     |key_len|ref |rows|Extra
1 |SIMPLE     |workdone |ALL |             |        |       |    |  10|
1 |SIMPLE     |staffcost|ref |costyear     |costyear|1      |func|   1|Using where

VERSION()
10.1.26-MariaDB

在线演示: http://rextester.com/JIAL51740

- Paul Spiegel