Блог

У меня есть таблица с именем data_table .

В этой таблице уже около 10 миллионов записей. Ранее я проверял, существует ли комбинация itemID , FromDate и ToDate перед вставкой данных. Чтобы упростить задачу, я создал unique индекс с полями itemID FromDate ToDate .

Теперь в этой таблице все вместе три индекса ID (Pk) itemID и UniqueIndex

Проблема

1. В первый раз, если я попытаюсь сгенерировать отчет, скажем itemID=2630 , для диапазона дат 2018-01-01 2021-01-01 . Это занимает около 60 секунд.
2. Во второй раз для тех же параметров это занимает около 1 секунды.
3. Затем я удалил все данные для этого элемента (2630) и повторно вставил много случайных данных для этого ItemId между выбранным диапазоном дат.
4. Теперь, если я запускаю отчет в третий раз, он все равно занимает около 1 секунды.

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

Я использую механизм обработки таблиц innodb , а моя версия mysql — 5.7.33

Обновить Это мой запрос

 SELECT 
  * 
FROM
  DataTable AS D 
WHERE ItemID = :ItemID 
  AND IsJoined = '0' 
  AND (
    (
      :paramFromDate < ToDate 
      AND ToDate < :paramToDate
    ) 
    OR (
      :paramFromDate < FromDate 
      AND FromDate < :paramToDate
    ) 
    OR (
      FromDate < :paramFromDate 
      AND :paramToDate < FromDate
    )
  ) 
ORDER BY FromDate DESC 
 

Обновить
Перезапуск mysql снова приводит к замедлению запроса. И последующие запросы выполняются быстро, пока я снова не перезапущу mysql.

Спасибо

Кэширование

Основным кэшем для InnoDB является «buffer_pool». Он кэширует блоки (по 16 КБ каждый), каждый из которых содержит несколько последовательных строк данных или строк индекса. Все операции (чтение или запись) строк работают с этими блоками.

После запуска или перезапуска MySQL кэш пуст. Следовательно, все должно быть извлечено с диска, следовательно, «медленно».

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

Для вставленных строк требуется, чтобы соответствующие блоки находились в кэше. Итак, в течение некоторого времени после выполнения a INSERT SELECT из этих строк будет «быстрым».

Лучший ИНДЕКС

Что касается оптимизации этого запроса,

 INDEX(ItemID, IsJoined, FromDate)   -- (in this order)
 

Первые два столбца помогают с частью WHERE .

OR В остальной части WHERE предотвращает любую полезную оптимизацию, связанную с двумя столбцами даты.

Однако оптимизатор может избежать сортировки (для ORDER BY ), если он решит использовать FromDate то, что я добавил в индекс.

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

 AND  fromDate <= :toParam
AND  :fromParam <= toDate
 

Если это работает для вас, то другая часть WHERE обрабатывается моим индексом. (Но будет невозможно также обработать другую часть.) (Кроме того, я не знаю, нужен ли вам < или <= .)