在某些情况下语句之间缺少索引 [英] Between statement missing indexes in certain cases

问题描述

我有一个庞大的数字数据库，我正在寻找两者之间的匹配项:

I have a large database of numbers that I am looking for a match in between:

例如:

1112203488

我的桌子看起来像这样:

My table looks like this:

|萨顿| eATON |信息|

| sATON | eATON | info |

我在sATON和eATON上有两个索引(分别为s和e)

I have two index's on sATON and eATON (named s and e)

所以我的SQL如下:

SELECT * 
FROM  `data2` 
FORCE INDEX ( s, e ) 
WHERE 1112203488
BETWEEN  `sATON` 
AND  `eATON`;

因此，通常在使用索引时，查询将花费几乎零的时间(0.02).但是，有时我似乎决定使用MySQL中的表统计信息来进行全表扫描，尽管我强迫在SQL中使用索引.查询时间从0.02缩短到120 s，这对性能造成了巨大影响.

So usually when the index is used the query will take nearly zero time (0.02). However, sometimes it looks like the table stats in MySQL is taking the decision to do a full table scan instead, despite me forcing the use of the indexes in the SQL. This is a huge performance hit as it takes the query from 0.02 to 120 s.

以下是一些运行速度很快(使用索引)的示例:

Here are some samples that run fast (using indexes) :

67372289
134744072

还有慢的:

1112203488
1348203839

如果有帮助，索引正在使用BTREE.

If it helps the indexes are using BTREE.

推荐答案

如果任何这样的查询将返回最大一行，则表示(sATON, eATON)范围不重叠.

If any such query will return maximum one row, this means that the (sATON, eATON) ranges are not overlapping.

因此，并且仅当范围不重叠时，您才可以使用以下查询:

Therefore, and only if the ranges are not overlapping, you can use this query instead:

SELECT * 
FROM data2 
WHERE sATON = 
      ( SELECT MAX(sATON)
        FROM data2 
        WHERE sATON <= 1112203488
      )
  AND eATON = 
      ( SELECT MIN(eATON)
        FROM data2 
        WHERE eATON >= 1112203488
      )

甚至是这个(只需使用一个索引，sATON一个):

or even this (that will need to use just one index, the sATON one):

SELECT * 
FROM data2 
WHERE sATON = 
      ( SELECT MAX(sATON)
        FROM data2 
        WHERE sATON <= 1112203488
      )
  AND eATON >= 1112203488

这篇关于在某些情况下语句之间缺少索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！