如何使用OR使多个LEFT JOIN完全使用复合索引? [英] How to make multiple LEFT JOINs with OR fully use a composite index?
问题描述
我正在研究一个计时系统，并试图确定每个人的早晚排班时间. "TB_Scan"表最初具有人员代码和扫描时间，这是一个日期时间字段.但是，由于我遇到的查询问题，决定添加scYear，scMonth和scDay字段，认为这可能会有所帮助.
I am working on a timekeeping system and trying to determine that for each person, are they early or late for their scheduled shift. The "TB_Scan" table originally had the person code and scan time which is a datetime field. However, due to querying issues I was having, decided to add the scYear, scMonth and scDay fields thinking it might help.
这是一个计算用户进入/离开工作场所时如何扫描指纹的系统.我不知道英文怎么说.我需要确定用户是否早上很晚，以及用户是否提早下班.
It is a system that calculates how the users scan their fingerprints when they enter/leave the workplace. I don't know how it is called in English. I need to determine if the user is late in the morning, and if the user leaves work early.
此tb_scan表包含用户扫描指纹的日期和时间.
This tb_scan table contains date and time a user scans a fingerprint.
CREATE TABLE `tb_scan` (
`scpercode` varchar(6) DEFAULT NULL,
`scyear` varchar(4) DEFAULT NULL,
`scmonth` varchar(2) DEFAULT NULL,
`scday` varchar(2) DEFAULT NULL,
`scscantime` datetime,
KEY `all` (`scyear`,`scmonth`,`scday`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
它有100,000+行，类似这样
It has 100,000+ rows, something like this
scpercode scyear scmonth scday scdateandtime
000001 2010 10 10 2016-01-10 08:02:00
000001 2010 10 10 2016-01-02 17:33:00
000001 2010 10 11 2016-01-11 07:48:00
000001 2010 10 11 2016-01-11 17:29:00
000002 2010 10 10 2016-01-10 17:31:00
000002 2010 10 10 2016-01-02 17:28:00
000002 2010 10 11 2016-01-11 05:35:00
000002 2010 10 11 2016-01-11 05:29:00
此tb_workday表包含每个日期
CREATE TABLE `tb_workday` (
`wdpercode` varchar(6) DEFAULT NULL,
`wdshift` varchar(1) DEFAULT NULL,
`wddate` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
它具有按日期顺序排列的行，如下所示:
It has rows with date sequence like this:
wdpercode wdshift wddate
000001 1 2010-10-10
000001 1 2010-10-11
000001 1 2010-10-12
000001 1 2010-10-13
000002 2 2010-10-10
000002 2 2010-10-11
000002 2 2010-10-12
000002 2 2010-10-13
还有另一个包含班次时间的tb_shift表
There is another tb_shift table containing shift time
CREATE TABLE `tb_shift` (
`shiftcode` varchar(1) DEFAULT NULL,
`shiftbegin2` varchar(4) DEFAULT NULL,
`shiftbegin` varchar(4) DEFAULT NULL,
`shiftmid` varchar(4) DEFAULT NULL,
`shiftend` varchar(4) DEFAULT NULL,
`shiftend2` varchar(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
shiftcode shiftbegin2 shiftbegin shiftmid shiftend shiftend2
1 04:00:00 08:00:00 12:00:00 17:30:00 21:30:00
2 12:00:00 17:30:00 21:00:00 05:30:00 09:30:00
我想确定每天是员工上班迟到还是提早下班，以及在什么时候上班.
I want to determine that in each day, is the employee comes to work late or leaves work early, and at what time.
SELECT wdpercode,wddate,shiftbegin,shiftend,time(tlate.scscantime) wdlate,time(tearly.scscantime) wdearly
FROM tb_workday
LEFT JOIN tb_shift
ON wdshift=shiftcode
LEFT JOIN tb_scan tlate
ON wdpercode=tlate.scpercode
AND tlate.scyear=year(wddate)
AND tlate.scmonth=month(wddate)
AND (tlate.scday=day(wddate)
OR tlate.scday=day(wddate)+1)
AND tlate.scscantime>=ADDDATE(CONCAT(wddate,' ',shiftbegin),INTERVAL IF(shiftbegin2>shiftbegin,1,0) DAY)
AND tlate.scscantime<=ADDDATE(CONCAT(wddate,' ',shiftmid),INTERVAL IF(shiftbegin2>shiftmid,1,0) DAY)
LEFT JOIN tb_scan tearly
ON wdpercode=tearly.scpercode
AND tearly.scyear=year(wddate)
AND tearly.scmonth=month(wddate)
AND (tearly.scday=day(wddate)
OR tearly.scday=day(wddate)+1)
AND tearly.scscantime>ADDDATE(CONCAT(wddate,' ',shiftmid),INTERVAL IF(shiftbegin2>shiftmid,1,0) DAY)
AND tearly.scscantime<ADDDATE(CONCAT(wddate,' ',shiftend),INTERVAL IF(shiftbegin2>shiftend,1,0) DAY)
以下是输出示例:
wdpercode wddate shiftbegin shiftend wdlate wdearly
000001 2016-01-10 08:00:00 17:30:00 08:02:00 (null)
000001 2016-01-11 08:00:00 17:30:00 (null) 17:29:00
000002 2016-01-11 17:30:00 05:30:00 17:31:00 (null)
000002 2016-01-11 17:30:00 05:30:00 (null) 05:29:00
此ADDDATE(CONCAT(wddate,' ',shiftbegin),INTERVAL IF(shiftbegin2>shiftbegin,1,0) DAY)适用于夜班工作的员工，因此必须将轮班时间增加1天
this ADDDATE(CONCAT(wddate,' ',shiftbegin),INTERVAL IF(shiftbegin2>shiftbegin,1,0) DAY) is for employees who work on night shift, so it has to add 1 day into the shift time
问题是如果我为scscantime创建索引，MySQL拒绝将其用于比较(>=，<=，>，<).请参见此线程为什么MySQL不这样做使用索引大于比较?
The problem is if I create an index for scscantime, MySQL refuses to use it for comparison (>=,<=,>,<). Please see this thread Why does MySQL not use an index for a greater than comparison?
因此，我创建了scyear，scmonth和scday字段，并将它们与scpercode一起组合在索引中.而且我必须确保它也能计算夜班工作的工人，因此我必须添加OR scday=day(wddate)+1条件.
Because of this I created the scyear, scmonth, and scday fields and combine them in an index along with scpercode. And I have to make sure it calculates for workers working in night shift too so I have to add it with OR scday=day(wddate)+1 condition.
在添加OR条件之前，EXPLAIN结果为52行.但是，当我添加OR scday=day(wddate)+1条件时，EXPLAIN结果变为364行，这意味着MySQL不使用索引的scday部分.有没有办法使用整个索引，所以EXPLAIN结果变为52行?我还尝试删除了+1部分，结果也是52.
Before I added the OR condition, the EXPLAIN result was 52 rows. But when I added the OR scday=day(wddate)+1 condition, the EXPLAIN result became 364 rows, that means MySQL did not use scday part of the index. Is there any way to use the whole index, so the EXPLAIN result becomes 52 rows? I also tried removing the +1 part and the result is also 52.
推荐答案
从您的查询(可读性已格式化)
From your query (readability formatted)
SELECT
table1.*,
tb21.year,
tb21.month,
tb21.day,
tb22.year,
tb22.month,
tb22.day
FROM
table1
LEFT JOIN table2 tb21
ON table1.year = tb21.year
AND table1.month = tb21.month
AND (tb21.day = table1.day
OR tb21.day = table1.day+1)
LEFT JOIN table2 tb22
ON table1.year = tb22.year
AND table1.month = tb22.month
AND (tb22.day = table1.day+2
OR tb22.day = table1.day+3)
除了提供的限制性内容外，让我们看一下您试图比较当天+ 1，+ 2和+3的数据.让我们仅假设在此示例中，表1和表2中的表中只有10天(表示为2016年6月1日至6月10日).
Aside from your restrictive content provided, lets look at you trying to compare the data from same day to day + 1, +2 and +3. Lets also assume just for this example you have only 10 days in the table represented as all June 1 - June 10, 2016 in both your Table1 AND Table2 tables.
再次，这是假设每个表都具有所有10个有问题的日期，只是出于简化目的，为什么要这么多记录.因此，对于日期为2016年6月1日的表1，它将符合表2(tb21版本)的条件，并返回两个记录.一个代表6月1日，另一个代表6月2日.因此，现在您的结果中有两个记录.现在，您再次执行左连接至表2(tb22版本).这次您要寻找2天和3天，其中表格中有6月3日和4日.因此，您得到的是笛卡尔结果.因此，对于表1中的6月1日记录，您现在有4条记录，如下所示.
Again, this is an assumption that each table has all 10 dates in question just for simplistic purposes of why so many records. So, for Table 1 date of June 1, 2016, it will qualify with table 2 (tb21 version) and return TWO records. One for June 1 and another for June 2. So now you have TWO records in your result. Now, you do that again left-join to table 2 (tb22 version). This time you are looking for 2 and 3 days out, of which you have June 3 and 4 in the table. So you are getting a Cartesian result. So, for the June 1 record in table 1, you now have 4 records as follows.
T1Year T1Month T1Day T21Day T22Day
2016 6 1 1 3
2016 6 1 1 4
2016 6 1 2 3
2016 6 1 2 4
现在，假设您的表2在6月2日有3个条目，在6月3日有3个条目，并且您的数据将超级膨胀.这就是为什么您需要对正在尝试做的事情提供更多的说明.
Now, lets say your table 2 has 3 entries on June 2 and 3 entries on June 3 and your data is going to super bloat. This is why you need to provide more clarification on what you are trying to do.
因此，对于要查找的内容没有真实的上下文，请忽略它没有完全利用索引的事实.您可以通过日期比较根据日期获得OR.无论如何，它仍应用于查询.
So, not having true context on what you are looking for, ignore the fact it is not perfectly utilizing your index. You have an OR based on the date via the day comparisons. It should still utilize for the query anyhow.
这篇关于如何使用OR使多个LEFT JOIN完全使用复合索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
