基于YEAR()索引的MySQL Join-列添加或生成的列 [英] MySQL Join based on YEAR () indexing - Column add or Generated Column
问题描述
基于答案 https://stackoverflow.com/a/1601812/4050261
我正在使用如下SQL查询
FROM workdone
LEFT JOIN staffcost ON YEAR(workdone.date) = staffcost.costyear
上面的查询没有使用我在workdone.date列上拥有的索引，因此非常慢.我认为我有2个选择
选项1
添加另一列workdone.year，该列通过表oncreate和onupdate事件进行更新.在查询中使用此列.
选项2
添加已生成(虚拟/持久)列，并在workdone.year和然后在查询中使用此列.
我的问题:
- 哪个选项更好?从性能以及数据重复性的角度来看?
- 我应该使用Virtual OR Persistent列类型吗?
- 还有更好的选择吗?
更新1.1
我实现了OJones建议的解决方案，但是解释说明我没有使用索引.我是否错误地阅读了以下屏幕截图?
您的查询就可以了.但是具有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
Based on the answer https://stackoverflow.com/a/1601812/4050261
I am using SQL query as below
FROM workdone
LEFT JOIN staffcost ON YEAR(workdone.date) = staffcost.costyear
The above query does not make use of index which I have on workdone.date column and hence very slow. I have 2 options, i presume
Option 1
Add another column workdone.year which is updated through table oncreate and onupdate event. Use this column in the query.
Option 2
Add Generated (Virtual/Persistent) column workdone.year and then use this column in the query.
My Question:
- Which option is better? From Performance as well as data duplicity standpoint?
- Should I use Virtual OR Persistent column type?
- Is there any better alternative?
Update 1.1
I implemented the solution suggested by OJones, but explain shows me that index was not used. Am I reading the below screenshot incorrectly?
Your query is fine as it is. But a query with a LEFT JOIN can only use an index on the right table (staffcost). No index on the left table (workdone) can support the join. So all you need is an index on staffcost(costyear).
You can test it with the following script:
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();
Results:
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
Online demo: http://rextester.com/JIAL51740
这篇关于基于YEAR()索引的MySQL Join-列添加或生成的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
