Теоретически, нисходящий индекс не поможет, он полезен, только если вы упорядочиваете по этому столбцу desc. Пространственный индекс является хорошим предложением, но работает только для таблиц MySQL MyISAM.

ользую MySQL DB, и у меня есть следующая таблица:

CREATE TABLE SomeTable (
  PrimaryKeyCol BIGINT(20) NOT NULL,
  A BIGINT(20) NOT NULL,
  FirstX INT(11) NOT NULL,
  LastX INT(11) NOT NULL,
  P INT(11) NOT NULL,
  Y INT(11) NOT NULL,
  Z INT(11) NOT NULL,
  B BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (PrimaryKeyCol),
  UNIQUE KEY FirstLastXPriority_Index (FirstX,LastX,P)
) ENGINE=InnoDB;

Таблица содержит 4,3 миллиона строк и никогда не изменяется после инициализации.

Важными столбцами этой таблицы являютсяFirstX, LastX, Y, Z а такжеP.

Как видите, у меня есть уникальный индекс по строкамFirstX, LastX а такжеP.

КолонныFirstX а такжеLastX определить диапазон целых чисел.

Запрос, который мне нужно выполнить для этой таблицы, выбирает для данного X все строки, имеющие FirstX <= X <= LastX (т.е. все строки, чей диапазон содержит входное число X).

Например, если таблица содержит строки (я включаю только соответствующие столбцы):

FirstX     LastX      P        Y         Z
------     ------     -       ---       ---
100000     500000     1       111       222 
150000     220000     2       333       444
180000     190000     3       555       666
550000     660000     4       777       888   
700000     900000     5       999       111 
750000     850000     6       222       333 

и мне нужны, например, строки, содержащие значение185000, первый3 строки должны быть возвращены.

Я попробовал запрос, который должен использовать индекс:

SELECT P, Y, Z FROM SomeTable WHERE FirstX <= ? AND LastX >= ? LIMIT 10;

Даже без LIMIT этот запрос должен возвращать небольшое количество записей (меньше чем50) для любого данного X.

Этот запрос был выполнен приложением Java для120000 значения X. К моему удивлению, он взял на себя10 часов (!) и среднее время на запрос было0,3 секунды.

Это не приемлемо, даже близко не приемлемо. Это должно быть намного быстрее.

Я изучил один запрос, который занял0,563 секунды чтобы убедиться, что индекс используется. Запрос, который я пробовал (такой же, как запрос выше, с конкретным целочисленным значением вместо?) вернулся2 ряда.

я использовалEXPLAIN чтобы выяснить, что происходит:

id               1
select_type      SIMPLE
table            SomeTable 
type             range
possible_keys    FirstLastXPriority_Index
key              FirstLastXPriority_Index 
key_len          4
ref              NULL
rows             2104820
Extra            Using index condition

Как вы можете видеть, в исполнении участвуют2104820 строки (почти 50% строк таблицы), хотя только 2 строки удовлетворяют условиям, поэтому половина индекса проверяется, чтобы получить только 2 строки.

Что-то не так с запросом или индексом? Можете ли вы предложить улучшение для запроса или индекса?

РЕДАКТИРОВАТЬ:

В некоторых ответах предлагалось, чтобы я запускал запрос в пакетном режиме для нескольких значений X. Я не могу этого сделать, поскольку я выполняю этот запрос в режиме реального времени, когда входные данные поступают в мое приложение. Каждый раз, когда поступает ввод X, я должен выполнить запрос для X и выполнить некоторую обработку выходных данных запроса.

 Clydog13 дек. 2017 г., 18:21
@Eran, да, я это вижу, но мне интересно, можно ли определить пару уникальных индексов, в которых пропущены FirstX и LastX соответственно. Очевидно, это зависит от реальных данных.
 Raymond Nijland13 дек. 2017 г., 18:08
какое значение у буферного пула innodb?SELECT @@innodb_buffer_pool_size должен был быть на 75 - 80% от общего объема ОЗУ, если сервер предназначен для работы только на MySQL
 tadman13 дек. 2017 г., 18:04
Если некоторые точки в двоичном дереве действительно забиты записями, у вас могут быть слишком медленные запросы. Я знаю, что ограничивающие тесты могут действительно плохо масштабироваться для определенных типов данных, это постоянная проблема в приложениях типа 3D для таких вещей, как обнаружение столкновений, поэтому вам может потребоваться более лучший метод индексации, чем наивный, который у вас есть здесь.
 Eran13 дек. 2017 г., 18:06
@tadman Спасибо за комментарий. Какой это может быть лучший метод индексации?
 Clydog13 дек. 2017 г., 18:13
FirstX уникален в сочетании с P? А как насчет LastX?

Ответы на вопрос(11)

Уникальный индекс FirstLastXPriority_Index (FirstX, LastX, P) представляетконкатенация из этих значений, поэтому он будет бесполезен с 'И LastX> =?' часть вашего предложения WHERE.

 Eran13 дек. 2017 г., 18:20
Я добавил второй индекс, и теперь он использует только второй индекс (в столбце LastX). Разве это не должно использовать оба? А что касается времени выполнения, то оно быстрее, чем оригинал (для только что протестированного запроса), но все равно медленно - 0,2 секунды.
 Rick James14 дек. 2017 г., 07:41
MySQL практически никогда не использует два индекса одновременно. И я не вижу, чтобы это здесь даже рассматривалось.

чтобы разделить таблицу по различным диапазонам, а затем запрашивать только то, что входит в диапазон, и, следовательно, делать количество, необходимое для проверки, намного меньшим. Это может не сработать, поскольку Java может быть медленнее. Но это может снизить нагрузку на базу данных. Также может быть способ не запрашивать базу данных так много раз и иметь более инклюзивный SQL (вы можете отправить список значений и SQL-файл отправит его в другую таблицу).

у меня недостаточно данных, чтобы быть уверенным во времени выполнения. Это будет работать, только если столбец P уникален? Чтобы заставить работать два индекса, я создал два индекса и следующий запрос ...

Index A - FirstX, P, Y, Z
Index B - P, LastX

Это запрос

select A.P, A.Y, A.Z 
from 
    (select P, Y, Z from asdf A where A.firstx <= 185000 ) A
    join 
    (select P from asdf A where A.LastX >= 185000 ) B
    ON A.P = B.P

По какой-то причине это казалось быстрее, чем

select A.P, A.Y, A.Z 
from asdf A join asdf B on A.P = B.P
where A.firstx <= 185000 and B.LastX >= 185000
 Used_By_Already21 дек. 2017 г., 02:48
Есть уникальный столбецPrimaryKeyCol (не P) возможно играть с первичным ключом, см.sqlfiddle.com/#!9/16667b/3 а такжеsqlfiddle.com/#!9/c089a/5
 Keith Aymar21 дек. 2017 г., 18:45
Верьте или нет, я никогда не видел sqlfiddle до LOL! Я буду экспериментировать.

за исключением небольшого процента всех возможных значенийX.

Скажем, например, что:

FirstX содержит значения от 1 до 1000, равномерно распределенныеLastX содержит значения от 1 до 1042, равномерно распределенные

И у вас есть следующие индексы:

FirstX, LastX, <covering columns>LastX, FirstX, <covering columns>

В настоящее время:

Если X 50FirstX <= 50 соответствует примерно 5% строк в то время какLastX >= 50 соответствует примерно 95% строк. MySQL будет использовать первый индекс.

Если X равен 990FirstX <= 990 соответствует примерно 99% строк в то время какLastX >= 990 соответствует примерно 5% строк. MySQL будет использовать второй индекс.

Любой X между этими двумя приведет к тому, что MySQL не будет использовать ни один из индексов (я не знаю точного порога, но 5% работали в моих тестах). Даже если MySQL использует индекс, совпадений слишком много, и индекс, скорее всего, будет использоваться для поиска вместо поиска.

Ваше решение лучшее. То, что вы делаете, это определение верхней и нижней границы поиска по диапазону:

WHERE FirstX <= 500      -- 500 is the middle (worst case) value
AND   FirstX >= 500 - 42 -- range matches approximately 4.3% rows
AND   ...

Теоретически, это должно работать, даже если вы ищете FirstX для значений в середине. Сказав это, вам повезло с4200000 стоимость; возможно, потому что максимальная разница между первым и последним составляет меньший процент.

Если это поможет, вы можете сделать следующее после загрузки данных:

ALTER TABLE testdata ADD COLUMN delta INT NOT NULL;
UPDATE testdata SET delta = LastX - FirstX;
ALTER TABLE testdata ADD INDEX delta (delta);

Это делает выборMAX(LastX - FirstX) Полегче.

Я протестировал ПРОСТРАНСТВЕННЫЕ ИНДЕКСЫ MySQL, которые можно использовать в этом сценарии. К сожалению, я обнаружил, что пространственные индексы были медленнее и имеют много ограничений.

Изменить: идея № 2

У вас есть контроль над приложением Java? Потому что, честно говоря, 0,3 секунды для сканирования индексане Плохо. Ваша проблема в том, что вы пытаетесь получить запрос, который выполняется 120000 раз, чтобы получить разумное время окончания.

если тыделать иметь контроль над приложением Java, вы можете отправить еговсе значения X сразу - и пусть SQL не должен выполнять сканирование индекса 120k раз. Или вы могли бы просто запрограммировать логику на стороне Java, поскольку ее было бы относительно легко оптимизировать.

Оригинальная идея:

Вы пытались создать многостолбцовый индекс?

Проблема с наличием нескольких индексов состоит в том, что каждый индекс собирается сузить его до ~ 50% записей - он должен затем сопоставить эти ~ 2 миллиона строк индекса A с ~ 2 миллионами строк индекса B.

Вместо этого, если вы получаете оба столбца в одном и том же индексе, механизм SQL может сначала выполнить операцию Seek, чтобы добраться до начала записей, а затем выполнить одно сканирование индекса, чтобы получить список записей, в которых он нуждается. Нет соответствия одного индекса другому.

Я бы предложилне делая это Кластеризованным Индексом, все же. Причина этого? Вы не ожидаете много результатов, поэтому сопоставление результатов индексного сканирования с таблицей не займет много времени. Вместо этого вы хотите сделать индекс как можно меньшим, чтобы сканирование индекса проходило как можно быстрее. Кластерные индексынаходятся таблица - так что кластерный индекс будет иметь такую ​​же скорость сканирования, как и сама таблица. В том же ключе, вы, вероятно, не хотите, чтобы в вашем индексе были какие-либо другие поля, кроме FirstX и LastX - сделайте этот индекс настолько маленьким, насколько это возможно, чтобы сканирование продолжалось.

Наконец, как и сейчас, вам нужно будет указать движок на то, что вы не ожидаете большой набор данных от поиска - вы хотите убедиться, что он использует этот компактный индекс для сканирования ( вместо того, чтобы сказать: «Эх, я бы лучше просто сделал полное сканирование таблицы.)

 Eran18 дек. 2017 г., 20:07
Если я не понимаю, что вы подразумеваете под «индексом по нескольким столбцам», в моем индексе уже есть несколько столбцов -UNIQUE KEY FirstLastXPriority_Index (FirstX,LastX,P), Как я должен изменить это?
 Kevin19 дек. 2017 г., 15:15
Ах, пропустил это. В этом случае вы можете попробовать удалить P из индекса. Имейте в виду, что вам придется выполнять частичное сканирование индекса независимо от того, как вы структурируете запрос, поэтому вы хотите, чтобы индекс был как можно меньше. Удаление P сокращает индекс на 33%, сокращая время сканирования на 33%. Если у вас есть только несколько записей из основной таблицы, возможно, будет быстрее без P в индексе. В любом случае, перебирая вопрос снова, я думаю, что мог бы ответить с другим ответом под другим углом.

вы сократили время выполнения до 0,1 секунды. Будут ли приемлемыми 3 часа, двадцать минут?

Простой факт заключается в том, что тысячи обращений к одному и тому же запросу невероятно неэффективны. Помимо того, что должна выдержать база данных, есть сетевой трафик, время поиска диска и все виды накладных расходов на обработку.

, что у вас еще нет 120000 значений дляx в таблице, это то, где я бы начал. Я вставил бы их в таблицу партиями по 500 штук за раз:

insert into xvalues (x)
select 14 union all
select 18 union all
select 42 /* and so on */

Затем измените свой запрос, чтобы присоединиться кxvalues.

Я считаю, что одна только оптимизация сократит ваше время выполнения до минут или секунд, а не часов (на основе многих таких оптимизаций, которые я делал за годы).

Это также открывает двери для дальнейшей оптимизации. Еслиx значения могут иметь как минимум несколько дубликатов (скажем, как минимум 20% значений встречаются более одного раза), возможно, стоит изучить решение, в котором вы только запускаете запрос для уникальных значений и выполняете вставку вSomeTable для каждогоx с совпадающим значением.

Как правило: все, что вы можете сделать массово, скорее всего, экспоненциально превзойдет все, что вы делаете построчно.

PS:

Вы ссылались на запрос, но хранимая процедура также может работать с входной таблицей. В некоторых РСУБД вы можете передать таблицу в качестве параметра. Я не думаю, что это работает в MySQL, но вы можете создать временную таблицу, к которой заполняется вызывающий код и к которой присоединяется хранимая процедура. Или постоянный стол, используемый таким же образом. Основным недостатком неиспользования временной таблицы является то, что вам может понадобиться управление сеансом или удаление устаревших данных. Только вы будете знать, применимо ли это к вашему делу.

 Eran20 дек. 2017 г., 09:56
120000 значений x были просто образцом входных данных, на которых я проверил свой запрос. Фактические возможные уникальные значения x могут быть намного больше, чем 120000 (ограничение составляет 2 ^ 32). Я запускаю запрос, когда ввод x поступает в мое приложение, поэтому я не могу сгруппировать запросы и заранее не знаю всех возможных значений x. Я могу кэшировать значения, которые я получаю в таблице (в этом случае мне не понадобится исходный запрос - я просто сохраню для каждого значения x соответствующие значения y и z), но мне все еще нужно поддерживать новый значения х.
 Cobus Kruger20 дек. 2017 г., 10:04
Если вы выполняете запрос по мере поступления значений (я, должно быть, пропустил это в вашем вопросе), то на первый взгляд можно выполнить запрос отдельно для каждого. Нотолько если 10 часов, то на самом деле меньший компонент общего времени выполнения. Если значения приходят очень быстро, вы все равно можете получить выгоду от их группировки. Тогда вы не запускаете его один раз для 120K записей, а раз в минуту или в любое другое подходящее время. Дело в том, что обработка на основе множеств, как правило, экспоненциально быстрее, а не только на основе вашего текущего набора данных.
 Eran20 дек. 2017 г., 09:59
Что касаетсяSuppose you got the execution time down to 0.1 seconds. Would the resulting 3 hours, twenty minutes be acceptable? - нет, это не так, поскольку, как вы можете прочитать в ответе, который я разместил, у меня уже есть обходной путь, который приводит к 5,5 минутам, поэтому любое лучшее решение должно иметь аналогичную производительность.

ВЫБЕРИТЕ P, Y, Z ИЗ SomeTable, ГДЕ FirstX <=? И LastX> =? LIMIT 10;

Вот 2 ресурса, которые вы можете использовать:

нисходящие индексыпространственные индексы

Нисходящие индексы:

Одним из вариантов является использование индекса, который убывает в FirstX и возрастает в LastX.

https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html

что-то вроде:

СОЗДАТЬ ИНДЕКС SomeIndex для SomeTable (FirstX DESC, LastX);

И наоборот, вы можете создать вместо этого индекс (LastX, FirstX DESC).

Пространственные индексы:

Другой вариант - использовать SPATIAL INDEX с (FirstX, LastX). Если вы думаете о FirstX и LastX как о двухмерных пространственных координатах, тогда вы выполняете поиск, выбирая точки в смежной географической области, разделенные линиями FirstX <= LastX, FirstX> = 0, LastX> = X.

Вот ссылка на пространственные индексы (не только для MySQL, но с чертежами):

https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-indexes-overview

 Salman A24 дек. 2017 г., 09:46
Теоретически, нисходящий индекс не поможет, он полезен, только если вы упорядочиваете по этому столбцу desc. Пространственный индекс является хорошим предложением, но работает только для таблиц MySQL MyISAM.

что единственный способ сделать запрос быстрым - это уменьшить количество извлекаемых и сравниваемых полей. Вот идея.

Мы можем объявить новое индексированное поле (например, UNSIGNED BIGINT) и сохранить в нем оба значения FistX и LastX, используя смещение для одного из полей.

Например:

FirstX     LastX      CombinedX
100000     500000     100000500000
150000     220000     150000220000
180000     190000     180000190000
550000     660000     550000660000   
70000      90000      070000090000 
75         85         000075000085

альтернатива - объявить поле какDECIMAL и сохраните в нем FirstX + LastX / MAX (LastX). Позже поищите значения, удовлетворяющие условиям, сравнивая значения с одним полем CombinedX.

прилагаемая

И тогда вы можете получить строки, проверяющие только одно поле: чем-то вроде, где param1 = 160000

SELECT * FROM new_table 
WHERE
(CombinedX <= 160000*1000000) AND
(CombinedX % 1000000 >= 160000);

Здесь я предполагаю, что для всех FistX <LastX. Конечно, вы можете заранее рассчитать смещение param1 * и сохранить его в переменной, с которой будут выполняться дальнейшие сравнения. Конечно, вы можете рассмотреть не десятичные смещения, а побитовые сдвиги. Десятичные смещения были выбраны, поскольку они легче читаются человеком, чтобы показать в образце.

 asd-tm18 дек. 2017 г., 21:14
@ Я исправил ответ.
 asd-tm18 дек. 2017 г., 20:45
@Eran я добавил ответ.
 Eran18 дек. 2017 г., 20:04
Как будет выглядеть запрос с использованием предложенного вами столбца CombinedX или столбца DECIMAL?
 Eran18 дек. 2017 г., 20:50
В моем исходном запросе есть только один параметр. Мне нужно найти строки, которые удовлетворяют FirstX <= x AND LastX> = x, поэтому я не уверен, какие два параметра в вашем примере (param1=100000 and param2=120000) имеют в виду.
 Paul Spiegel19 дек. 2017 г., 14:42
В этом нет смысла. Действительно - не мало.

которое опирается на свойства данных в таблице. Я бы предпочел более общее решение, которое не зависит от текущих данных, но пока это лучшее, что у меня есть.

Проблема с оригинальным запросом:

SELECT P, Y, Z FROM SomeTable WHERE FirstX <= ? AND LastX >= ? LIMIT 10;

является то, что выполнение может потребовать сканирования большого процента записей вFirstX,LastX,P индекс при первом условииFirstX <= ? удовлетворен большим процентом строк.

То, что я сделал, чтобы сократить время выполнения, это заметить, чтоLastX-FirstX относительно небольшой.

Я выполнил запрос:

SELECT MAX(LastX-FirstX) FROM SomeTable;

и получил4200000.

Это значит, чтоFirstX >= LastX – 4200000 для всех строк в таблице.

Таким образом, чтобы удовлетворитьLastX >= ?мы также должны удовлетворитьFirstX >= ? – 4200000.

Таким образом, мы можем добавить условие к запросу следующим образом:

SELECT P, Y, Z FROM SomeTable WHERE FirstX <= ? AND FirstX >= ? - 4200000 AND LastX >= ? LIMIT 10;

В примере, который я тестировал в этом вопросе, число обработанных записей индекса было уменьшено с2104820 в18 и время работы было сокращено с0,563 секунды в0,0003 секунды.

Я проверил новый запрос с тем же120000 значенияX, Вывод был идентичен старому запросу. Время ушло из-за10 часов в5,5 минут, который закончилсяВ 100 раз быстрее.

 Rick James17 дек. 2017 г., 07:36
Подобное улучшение для другого набора значений
 Eran18 дек. 2017 г., 08:40
@PaulSpiegel, который был бы полезен, если бы таблица была обновлена ​​нашим приложением. Поскольку это не так, нет проблем с определением этой константы в качестве параметра конфигурации.
 Eran18 дек. 2017 г., 08:38
@RickJames Ну, таблица остается постоянной, пока мы не импортируем ее новую версию в нашу БД (наше приложение никогда не обновляет таблицу), поэтому я могу установить4200000 константа в качестве параметра конфигурации.
 Paul Spiegel17 дек. 2017 г., 19:45
Вы можете определить индексированный виртуальный столбец какRangeX INT AS (LastX - FirstX), Тогда вы можете заменить жестко закодированное значение4200000 с участием(SELECT MAX(RangeX) FROM SomeTable).
 Rick James17 дек. 2017 г., 16:20
Нет, не похоже на мое. Каков диапазон значений в таблице? Будет ли4200000 оставаться неизменным, или он будет меняться при добавлении новых данных?

является лучший с точки зрения минимальных затрат. В процессе оптимизации обычно учитывают свойства распределения данных в БД. Более того, в больших системах, как правило, невозможно достичь удовлетворительной производительности, если характер данных не учитывается.

Однако это решение также имеет недостатки. И необходимость изменения параметра конфигурации при каждом изменении данных является наименьшей. Более важным может быть следующее. Давайте предположим, что однажды в таблице появится очень большой диапазон. Например, пусть его длина покрывает половину всех возможных значений. Я не знаю природу ваших данных, поэтому не могу точно знать, может ли такой диапазон когда-либо появиться или нет, так что это всего лишь предположение. С точки зрения результата все нормально. Это просто означает, что о каждом втором запросе теперь будет возвращаться еще одна запись. Но даже один такой интервал полностью убьет вашу оптимизацию, потому что условиеFirstX <=? AND FirstX> =? - [MAX (LastX-FirstX)] больше не будет эффективно отсекать достаточно записей.

Поэтому, если у вас нет уверенности в том, что когда-либо появятся слишком большие расстояния, я бы посоветовал вам придерживаться той же идеи, но принять ее с другой стороны. Я предлагаю при загрузке новых данных в таблицу разбивать все длинные диапазоны на более мелкие с длиной, не превышающей определенного значения. Вы написали этоThe important columns of this table are FirstX, LastX, Y, Z and P, Таким образом, вы можете один раз выбрать какое-то число N и при каждой загрузке данных в таблицу, если найден диапазон с LastX-FirstX> N, заменить его несколькими строками:

FirstX; FirstX + N
FirstX + N; FirstX + 2N
...
FirstX + kN; LastX

и для каждой строки сохраняйте одинаковые значения Y, Z и P.

Для данных, подготовленных таким образом, ваш запрос всегда будет одинаковым:

SELECT P, Y, Z FROM SomeTable WHERE FirstX <=? AND FirstX> =? - N AND LastX> =?

и всегда будет одинаково эффективным.

Теперь, как выбрать лучшее значение для N? Я бы взял несколько экспериментов с различными значениями и посмотреть, что будет лучше. И возможно, что оптимум будет меньше текущей максимальной длины интервала 4200000. Сначала он может удивить одного, потому что уменьшение N обязательно сопровождается ростом таблицы, так что он может стать намного больше, чем 4,3 миллиона. Но на самом деле, огромный размер таблицы не является проблемой, когда ваш запрос использует индекс достаточно хорошо. И в этом случае при уменьшении N индекс будет использоваться все более и более эффективно.

 Eran21 дек. 2017 г., 07:58
Это хорошая идея, и если мы когда-нибудь получим слишком большой столMAX (LastX-FirstX)Я определенно рассмотрю возможность разделения строк таблицы таким образом. В настоящее время я удовлетворен производительностью моего текущего решения, поэтому не вижу необходимости разбивать строки для текущих данных. +1

WHERE col1 < ... AND ... < col2 практически невозможно оптимизировать.

Любой полезный запрос будет включать «диапазон» на col1 или col2. Два диапазона (в двух разных столбцах) нельзя использовать в одномINDEX.

Таким образом, любой индекс, который вы пробуете, рискует проверить большую часть таблицы:INDEX(col1, ...) будет сканировать с самого начала, гдеcol1 хиты..., Аналогично дляcol2 и сканирование до конца.

Чтобы добавить к вашим бедам, диапазоны перекрываются. Таким образом, вы не можете вытащить быстрый и добавитьORDER BY ... LIMIT 1 быстро остановиться И если вы скажетеLIMIT 10, но есть только 9, он не остановится до начала / конца таблицы.

Одна простая вещь, которую вы можете сделать (но это не сильно ускорит процесс) - это поменять местамиPRIMARY KEY иUNIQUE, Это может помочь, потому что InnoDB «кластеризует» PK с данными.

Если бы диапазоны не перекрывались, я бы указал наhttp://mysql.rjweb.org/doc.php/ipranges .

Так что можно сделать?? Насколько «четны» и «малы» диапазоны? Если они достаточно «хороши», то следующий код может занять немного кода, но он должен быть намного быстрее. (В вашем примере100000 500000 довольно уродливо, как вы увидите через минуту.)

Определите ведра, скажем, по полу (число / 100). Затем создайте таблицу, которая соотносит ведра и диапазоны. Образцы:

FirstX  LastX  Bucket
123411  123488  1234
222222  222444  2222
222222  222444  2223
222222  222444  2224
222411  222477  2224

Обратите внимание, как некоторые диапазоны «принадлежат» нескольким сегментам.

Затем поиск выполняется сначала по сегменту (ам) в запросе, а затем по деталям. Поиск X = 222433 найдет две строки с bucket = 2224, а затем решит, что оба в порядке. Но для X = 222466, две строки имеют сегмент, но только одна соответствует firstXа также lastX.

WHERE bucket = FLOOR(X/100)
  AND firstX <= X
  AND X <= lastX

с участием

INDEX(bucket, firstX)

Но с100000 500000, было бы 4001 рядов, потому что этот диапазон находится во множестве «сегментов».

План б (для решения широкого спектра)

Разделите диапазоны на широкие и узкие. Делайте широкие диапазоны простым сканированием таблицы, узкие диапазоны - методом моего ведра.UNION ALL результаты вместе. Надеемся, что «широкий» стол будет намного меньше, чем «узкий» стол.

 Eran14 дек. 2017 г., 08:13
Хорошая идея, но наибольшая разница между FirstX и LastX в моей таблице составляет 4200000, поэтому для одного диапазона потребуется много строк.
 Rick James14 дек. 2017 г., 17:03
Хорошо, я добавил Kludge для обработки больших различий.

Ваш ответ на вопрос