在向查询中添加第二个表时忽略mysql日期范围idex

um6iljoc  于 2021-06-23  发布在  Mysql
关注(0)|答案(3)|浏览(337)

长期潜伏者,第一次提问者
使用php5.6和mysql版本14.14发行版5.6.41,对于win64(x86\u64)是的,我知道有点落后于时代,我们正在努力更新。但那就是我们现在的处境
问题更新:索引在createdate上。我认为该列是datetime可能有问题,所以我创建了另一个只是日期的列,在该列上设置索引并重试,但没有任何效果。
ulc总共有8965行。使用索引搜索3787 et有9530行。在不使用索引的查询中,它在第一个查询的主键上搜索时只搜索一行。
比较日期的格式似乎无关紧要。我尝试过各种格式,包括直接的“2018-01-01{00:00:00}”。没有变化。
我有一个我认为奇怪的,但我怀疑这里的人会是一个“duh!”一个。我得到了一个查询,其中包含主表的日期范围,然后根据第一个表中的一组唯一ID从其他表中获取其他数据位。别担心,我会在下面举一些例子。当我只搜索主表时,范围索引按预期工作,只搜索相关行。但是,当我在下一个表中添加 ON 子句,它忽略索引并搜索主表的所有行。如果不使用on子句,它将返回到正确使用索引。我试着用 FORCE INDEX ( USE 当它使用索引时,它会减慢查询速度。总之,以下是查询:
作品:

select CreateDate
from ulc
Inner Join et
WHERE ulc.CreateDate >= STR_TO_DATE("01/01/2018", "%m/%d/%Y")
AND ulc.CreateDate <= STR_TO_DATE("08/02/2018", "%m/%d/%Y")

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  ulc     range   index_CreateDate    index_CreateDate    5   NULL    3787    Using where; Using index
1   SIMPLE  et  index   NULL    index_BankProcessorProfile  5   NULL    9530    Using index; Using join buffer (Block Nested Loop)

不起作用:

select CreateDate
from ulc
Inner Join et on et.TranID = ulc.TranID
WHERE ulc.CreateDate >= STR_TO_DATE("01/01/2018", "%m/%d/%Y")
AND ulc.CreateDate <= STR_TO_DATE("08/02/2018", "%m/%d/%Y")

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   SIMPLE  ulc     ALL     TranID,index_CreateDate     NULL    NULL    NULL    8965    Using where
1   SIMPLE  et  eq_ref  PRIMARY     PRIMARY     8   showpro.ulc.TranID  1   Using index

对于第二个,我只是添加了 on et.TranID = ulc.TranID 另外,如果我将它从一个范围更改为一个特定的日期,索引也可以工作。

ghhaqwfi

ghhaqwfi1#

只需猜测这里没有更多的数据,但添加一个新的表到连接改变了数据分布。
因此,如果在第一种情况下where条件返回的数据可能占数据的一小部分(相对而言),那么在第二种情况下,优化器决定不使用索引就可以获得更快的结果,因为相同的条件可能对新的数据批不太有选择性。
为两个查询添加表定义和计数,包括总计和基于您的查询,以获得更好的答案。

esyap4oy

esyap4oy2#

如果在查询中使用datetime,建议在where类中使用“yyyy-mm-dd hh:mm:ss”
如果您在查询中使用date,建议在where类中使用格式“yyyy-mm-dd”。您使用str\u-to-date(“01/01/2018”,“%m/%d/%y”)将类型转换为“2018-01-01”似乎可以
您尝试使用explain查找查询的复杂性

explain select CreateDate
from ulc
Inner Join et on et.TranID = ulc.TranID
WHERE ulc.CreateDate >= STR_TO_DATE("01/01/2018", "%m/%d/%Y")
AND ulc.CreateDate <= STR_TO_DATE("08/02/2018", "%m/%d/%Y")

您可以检查et.tranid和ulc.tranid是否有正确的索引

guz6ccqo

guz6ccqo3#

)有些事情我得猜一下,因为你没有提供 SHOW CREATE TABLE . 作为一个“长期潜伏者”,你应该意识到这一点。)
第一个猜测是 TranID 不是 PRIMARY KEYulc ?
解决方法是添加一个“复合” INDEX(CreateDate, TranID)ulc . (实际上,你应该替换现有的 INDEX(CreateDate) (第二个猜测是你现在有了索引。)
现在我将解释为什么第一个查询对 INDEX(CreateDate) 但第二个不是。
在第一个查询中, INDEX(CreateDate) 是一个“覆盖”索引。也就是说,这个索引包含 ulc 是政府所需要的 SELECT . 因此,几乎可以保证使用索引比扫描表更好。它将是该索引的“范围索引扫描”。
第二个查询需要两者 CreateDate 以及 TranID ,所以您的索引将不会“覆盖”。有两种方法可以执行查询的第一部分。但首先,请注意(在innodb中)二级索引包含 PRIMARY KEY (第三种猜测:是的 (id) ).
索引的范围扫描。但是,为了 TranID ,它首先得到 id ,然后在 PRIMARY KEY /获取tranid的数据。这个过程比简单地停留在索引中花费更多,因此优化器不想这样做,除非估计的行数“很小”。
因为3787/8965不是“小的”,优化器决定它可能更快地扫描 ALL 8965行,过滤掉不需要的行。
我建议的索引是“覆盖”,从而避免了索引和数据之间的来回边界。因此,范围索引扫描是有效的。
根据你的观察,切换到一个单一的日期是利用索引的——好吧,8965行中有一行是“小的”,所以索引(和跳跃)被认为是更快的方法。
至于日期的格式——是的,这无关紧要。这是因为解析器注意到 STR_TO_DATE("01/01/2018", "%m/%d/%Y") 是一个可以计算一次的常量,并且可以这样做。
我的食谱应该直接带你到综合指数,而不必为这个问题挠头。
您的第一个查询是“交叉连接”,因为它没有 ON 子句将表关联在一起,它将返回大约3500万行(9530*3787)。第二个查询将有大约3787行,可能更少(如果一些连接找不到匹配项)。
“两个查询之间的变化太小了”--千万别这么想!优化器将抓住看似无关紧要的差异。 SELECT CreateDateSELECT * --一个巨大的不同。我所说的关于“第一个查询”的大部分内容都会被抛出。甚至改成 SELECT ChangeDate, x 就足以造成一条大皱纹。如果 TranID 在两个表相差足够大的情况下,索引就变得毫无用处了。等等,等等。

相关问题