Оптимизируйте SQL, который использует предложение между

Вы можете попробовать что-то вроде этого

Select A.ID,
(SELECT B.ID FROM B
WHERE A.EventTime BETWEEN B.start_time AND B.end_time LIMIT 1) AS B_ID
FROM A

Если у вас есть индекс в полях Start_Time, End_Time для B, то это должно работать достаточно хорошо.

Я не уверен, что это можно полностью оптимизировать. Я попробовал это на MySQL 5.1.30. Я также добавил указатель на {B.start_time, B.end_time}, как было предложено другими людьми. Затем я получил отчет от EXPLAIN, но лучшее, что я смог получить, это Метод доступа к диапазону:

EXPLAIN SELECT A.id, B.id FROM A JOIN B 
ON A.event_time BETWEEN B.start_time AND B.end_time;

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                          |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | A     | ALL  | event_time    | NULL | NULL    | NULL |    8 |                                                | 
|  1 | SIMPLE      | B     | ALL  | start_time    | NULL | NULL    | NULL |   96 | Range checked for each record (index map: 0x4) | 
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------------------------+

См. примечание в крайнем правом углу. Оптимизатор считает, что он может использовать индекс для {B.start_time, B.end_time}, но в итоге решил не использовать этот индекс. Ваши результаты могут отличаться, потому что ваше распределение данных является более репрезентативным.

Сравните с использованием индекса, если вы сравните A.event_time с постоянным диапазоном:

EXPLAIN SELECT A.id FROM A
WHERE A.event_time BETWEEN '2009-02-17 09:00' and '2009-02-17 10:00';

+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | A     | range | event_time    | event_time | 8       | NULL |    1 | Using where | 
+----+-------------+-------+-------+---------------+------------+---------+------+------+-------------+

И сравните с зависимой формой подзапроса, заданной @Luke и @Kibbee, которая, кажется, более эффективно использует индексы:

EXPLAIN SELECT A.id AS id_from_a,
    (
        SELECT B.id
        FROM B
        WHERE A.id BETWEEN B.start_time AND B.end_time
        LIMIT 0, 1
    ) AS id_from_b
FROM A;

+----+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | A     | index | NULL          | PRIMARY | 8       | NULL |    8 | Using index | 
|  2 | DEPENDENT SUBQUERY | B     | ALL   | start_time    | NULL    | NULL    | NULL |  384 | Using where | 
+----+--------------------+-------+-------+---------------+---------+---------+------+------+-------------+

Как ни странно, EXPLAIN перечисляет possible_keys как NULL (т. е. нельзя использовать индексы), но затем решает использовать первичный ключ. Может ли быть особенностью отчета MySQL EXPLAIN?

Обычно я бы не рекомендовал такой запрос, но...

Поскольку вы указали, что в таблице A всего около 980 строк и что каждая строка сопоставляется ровно с одной строкой в ​​таблице B, вы можете сделать следующее, и это, скорее всего, будет намного быстрее, чем декартово соединение:

SELECT A.id AS id_from_a,
    (
        SELECT B.id
        FROM B
        WHERE A.event_time BETWEEN B.start_time AND B.end_time
        LIMIT 0, 1
    ) AS id_from_b
FROM A

Я сделал несколько тестов для решения аналогичной проблемы - расчета страны на основе IP-адреса (указанного в виде числа). Вот мои данные и результаты:

• Таблица A (содержащая пользователей и IP-адреса) содержит около 20 записей.
• Таблица B (которая содержит диапазоны IP-адресов для каждой страны) содержит около 100 000 записей.

Запрос JOIN с использованием «между» занимает около 10 секунд; SELECT внутри запроса SELECT с использованием «между» занимает около 5,5 секунд; SELECT внутри запроса SELECT с использованием пространственного индекса занимает около 6,3 секунды. Запрос JOIN с использованием пространственного индекса занимает 0 секунд!

Обратите внимание, что при выполнении этого запроса вы фактически создаете в памяти записи 980x130000 перед применением условия. Такой JOIN не очень рекомендуется, и я понимаю, почему это вызовет проблемы с производительностью.

Если вы не можете изменить схему — в частности, если вы не можете добавить индекс для a.event_time, я не вижу больших возможностей для улучшения на уровне SQL.

Я был бы более склонен сделать это в коде.

• прочитать все кортежи B start/end/id в список, отсортированный по времени начала
• читать все события А
• за каждое событие А
• • find the largest start time <= event time (binary search will do fine)
• • if the event time is <= end time, add A to this B's list of events
• • else this B has no home

Не изменяя схему, вы не можете добавить индекс? Попробуйте индекс с несколькими столбцами для start_time и end_time.

Попробуйте использовать стандартный оператор сравнения (‹ и >).

Я вижу, что вы делаете перекрестное соединение двух таблиц. Это не очень хорошо, и СУБД потребует много времени для выполнения этой операции. Перекрестное соединение — самая дорогая операция в SQL. Причина столь длительного времени исполнения могла быть вот в чем.

Поступайте таким образом, это может решить...

ВЫБЕРИТЕ A.id, B.id ИЗ A, B, ГДЕ A.id = B.id И A.event_time МЕЖДУ B.start_time И B.end_time

Я надеюсь, что это поможет вам :)

Есть ли индекс на B (start_time, end_time)? Если нет, возможно, добавление одного из них может ускорить сопоставление строк B со строками A?

Имейте в виду, если вы не можете изменить схему, может быть, вы также не можете создавать новые индексы?

Единственный способ ускорить выполнение этого запроса — использовать индексы.

Позаботьтесь о том, чтобы поместить в индекс свой A.event_time, а затем поместить в другой индекс B.start_time и B.end_time.

Если, как вы сказали, это единственное условие, которое связывает две сущности вместе, я думаю, что это единственное решение, которое вы можете принять.

Феде

Даремон, этот ответ основан на одном из ваших комментариев, в котором вы сказали, что каждая запись в таблице A соответствует только одной записи в таблице B,

Можете ли вы добавить дополнительную таблицу в вашу схему? Если да, вы можете предварительно вычислить результат этого запроса и сохранить его в другой таблице. Вам также придется синхронизировать эту предварительно вычисленную таблицу с изменениями в таблицах A и B.

Основываясь на вашем комментарии о том, что каждая запись в A соответствует ровно одной записи в B, самым простым решением было бы удалить AUTOINCREMENT из столбца идентификатора B, а затем заменить все идентификаторы B идентификаторами из A.

Поместите индекс в B.start_time по убыванию, а затем используйте этот запрос:

 SELECT A.id AS idA,
 (SELECT B.id FROM B WHERE A.event_time > B.start_time LIMIT 0, 1
 ORDER BY B.start_time DESC) AS idB
 FROM A

Поскольку сегменты времени в B не пересекаются, это даст вам первое совпадающее время, и вы избавитесь от промежуточного, но все еще будете иметь там подзапрос. Возможно, включение B.id в индекс даст вам небольшой дополнительный прирост производительности. (отказ от ответственности: не уверен в синтаксисе MySQL)

Я не могу понять, почему у вас есть таблица со 130 000 строк с временными интервалами. В любом случае, для такого дизайна должна быть веская причина, и если это так, вы должны избегать попыток вычислять такое соединение каждый раз. Итак, вот мое предложение. Я бы добавил ссылку на B.id в таблицу A (A.B_ID) и использовал триггеры для обеспечения согласованности. Каждый раз, когда вы добавляете новую запись (триггер вставки) или изменяется столбец even_time (триггер обновления), вы будете пересчитывать ссылку на B, которой соответствует это время. Ваш оператор выбора будет сокращен до одного выбора * из A.

MySQL не позволяет использовать INDEX ORDER BY WITH RANGE в производных запросах.

Вот почему вам нужно создать пользовательскую функцию.

Обратите внимание, что если ваши диапазоны перекрываются, запрос выберет только один (который начался последним).

CREATE UNIQUE INDEX ux_b_start ON b (start_date);

CREATE FUNCTION `fn_get_last_b`(event_date TIMESTAMP) RETURNS int(11)
BEGIN
  DECLARE id INT;
  SELECT b.id
  INTO id
  FROM b
  FORCE INDEX (ux_b_start)
  WHERE b.start_time <= event_date
  ORDER BY
    b.start_time DESC
  LIMIT 1;
  RETURN id;
END;

SELECT COUNT(*) FROM a;

1000


SELECT COUNT(*) FROM b;

200000

SELECT *
FROM (
  SELECT fn_get_last_b(a.event_time) AS bid,
         a.*
  FROM a
) ao, b FORCE INDEX (PRIMARY)
WHERE b.id = ao.bid
  AND b.end_time >= ao.event_time

1000 rows fetched in 0,0143s (0,1279s)

Лично, если у вас есть отношение «один ко многим» и каждая запись в таблице a относится только к одной записи в таблице b, я бы сохранил идентификатор таблицы b в таблице a, а затем выполнил обычное соединение для получения данных. То, что у вас сейчас есть, — это плохой дизайн, который никогда не сможет быть по-настоящему эффективным.

Есть два предостережения к моему решению:

1) Вы сказали, что можете добавлять индексы, но не изменять схему, поэтому я не уверен, сработает ли это для вас или нет, поскольку вы не можете иметь индексы на основе функций в MySQL, и вам нужно будет создать дополнительный столбец в таблице B. 2) Другое предостережение относительно этого решения заключается в том, что вы должны использовать механизм MyISAM для таблицы B. Если вы не можете использовать MyISAM, это решение не будет работать, поскольку для пространственных индексов поддерживается только MyISAM.

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

Это решение использует поддержку MySQL для пространственных данных (см. документацию здесь ). Хотя типы пространственных данных могут быть добавлены к различным механизмам хранения, для индексов пространственного R-дерева поддерживается только MyISAM (см. здесь), которые необходимы для достижения необходимой производительности. Еще одно ограничение заключается в том, что типы пространственных данных работают только с числовыми данными, поэтому вы не можете использовать этот метод со строковыми запросами диапазона.

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

Основная предпосылка заключается в том, что мы можем взять начало/конец и использовать их две для создания четырех различных точек, по одной для каждого угла прямоугольника с центром вокруг 0,0 на сетке xy, а затем выполнить быстрый поиск в пространственной координате. index, чтобы определить, находится ли интересующий нас конкретный момент времени в пределах прямоугольника или нет. Как упоминалось ранее, см. объяснение Джереми Коула для более подробного обзора того, как это работает.

В вашем конкретном случае нам нужно будет сделать следующее:

1) Измените таблицу, чтобы она стала таблицей MyISAM (обратите внимание, что вы не должны этого делать, если вы полностью не осведомлены о последствиях такого изменения, таких как отсутствие транзакций и поведение блокировки таблицы, связанное с MyISAM).

alter table B engine = MyISAM;

2) Затем мы добавляем новый столбец, который будет содержать пространственные данные. Мы будем использовать тип данных polygon, так как нам нужно иметь возможность хранить полный прямоугольник.

alter table B add column time_poly polygon NOT NULL;

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

update B set time_poly := LINESTRINGFROMWKB(LINESTRING(
    POINT(unix_timestamp(start_time), -1),
    POINT(unix_timestamp(end_time), -1),
    POINT(unix_timestamp(end_time), 1),
    POINT(unix_timestamp(start_time), 1),
    POINT(unix_timestamp(start_time), -1)
  ));

4) Затем мы добавляем в таблицу пространственный индекс (как упоминалось ранее, это будет работать только для таблицы MyISAM и приведет к ошибке «ОШИБКА 1464 (HY000): используемый тип таблицы не поддерживает пространственные индексы»).

alter table B add SPATIAL KEY `IXs_time_poly` (`time_poly`);

5) Затем вам нужно будет использовать следующий выбор, чтобы использовать пространственный индекс при запросе данных.

SELECT A.id, B.id 
FROM A inner join B force index (IXs_time_poly)
ON MBRCONTAINS(B.time_poly, POINTFROMWKB(POINT(unix_timestamp(A.event_time), 0)));

Принудительный индекс нужен для того, чтобы на 100 % убедиться, что MySQL будет использовать индекс для поиска. Если все прошло хорошо, запуск объяснения для приведенного выше выбора должен показать что-то похожее на следующее:

mysql> explain SELECT A.id, B.id
    -> FROM A inner join B force index (IXs_time_poly)
    -> on MBRCONTAINS(B.time_poly, POINTFROMWKB(POINT(unix_timestamp(A.event_time), 0)));
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                                           |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------------------------------+
|  1 | SIMPLE      | A     | ALL  | NULL          | NULL | NULL    | NULL |    1065 |                                                 | 
|  1 | SIMPLE      | B     | ALL  | IXs_time_poly | NULL | NULL    | NULL | 7969897 | Range checked for each record (index map: 0x10) | 
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------------------------------------------+
2 rows in set (0.00 sec)

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

Дайте знать, если у вас появятся вопросы.

Спасибо,

-Дипин

что-то вроде этого?

SELECT A.id, B.id 
FROM A
JOIN B ON A.id =  B.id 
WHERE A.event_time BETWEEN B.start_time AND B.end_time