Оптимизировать запрос, выбрав период

В моем решении есть одно предостережение:

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

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

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

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

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

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

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

alter table events engine = MyISAM;

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

alter table events add column time_poly polygon NOT NULL;

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

update events 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 events add SPATIAL KEY `IXs_time_poly` (`time_poly`);

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

SELECT * 
FROM events force index (IXs_time_poly)
WHERE MBRCONTAINS(events.time_poly, POINTFROMWKB(POINT(unix_timestamp('2009-02-18 16:27:12'), 0)));

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

mysql> explain SELECT *
    -> FROM events force index (IXs_time_poly)
    -> on MBRCONTAINS(events.time_poly, POINTFROMWKB(POINT(unix_timestamp('2009-02-18 16:27:12'), 0)));
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | B     | range | IXs_time_poly | IXs_time_poly | 32      | NULL |    1 | Using where | 
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.00 sec)

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

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

Спасибо,

-Дипин

Не существует эффективного способа выполнить именно этот запрос в MySQL.

Однако, если ваши диапазоны не перекрываются, вы можете просто использовать start_time <= const вместе с ORDER BY start_time DESC LIMIT 1 и продолжить проверку на end_time >= const.

Вам нужно будет сделать это в функции, так как MySQL по какой-то причине не использует INDEX RANGE SCAN вместо ORDER BY в подзапросе, если условие диапазона берется из суперзапроса.

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)

У меня нет большого опыта работы с MySQL, но на MS SQL Server добавление индекса в оба столбца позволило время поиска и возврата индекса в таблице строк 1M увеличилось с 1-2 секунд до времени отклика в миллисекундах.

Кажется, вы видите разные результаты. Интересно, имеет ли значение ограничение? У меня есть контрольное ограничение, чтобы обеспечить соблюдение этого start_time ‹ end_time.

По сути, у вас есть запрос с двумя отдельными условиями диапазона. Вы используете >=, для MySQL это всегда сканирование диапазона. здесь есть документация по оптимизации сканирования диапазонов.

Суть в том, что MySQL выполняет дополнительную проверку, чтобы отфильтровать строки, которые удовлетворяют условию диапазона, а затем удовлетворяет остальную часть предложения WHERE, которое в вашем случае является другим условием диапазона.

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

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

SELECT *
FROM events
WHERE 
   ( start_time BETWEEN ( 'search_start' - INTERVAL 2 DAY ) and 'search_end' )
   AND end_time >= 'search_start'

... вы захотите иметь индекс на start_time.

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

За одним столом мало что можно сделать. Если оптимизация этих запросов 1) необходима 2) должна выполняться на уровне SQL, вам нужно будет создать производную таблицу:

Table event_times
id
event_id
mark_time

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

SELECT *
FROM events
LEFT JOIN event_times ON event_id = events.id
WHERE mark_time = '2009-02-18 16:27:12'

Вы можете сделать эту таблицу менее смешной, как вы определите «единицу времени», то есть если вы ограничите разрешение mark_time минутами или часами, а не секундами.