Блог

У меня очень большая таблица, состоящая примерно из 3 миллионов записей в день.

Следующий запрос выполняется очень медленно

 EXPLAIN SELECT *
FROM summary_by_to_days_range 
WHERE(record_date BETWEEN '2019-03-12' AND '2019-03-15')
AND unit_id = 1148210
AND enum_key IN (9, 10, 38, 311)
GROUP BY unit_id, record_date
ORDER BY record_date DESC;
  

Со следующими результатами:

  --------- ---------- ------------- --------------- --------- ----------------------------------------------------- 
|  rows   | filtered |    Extra    | possible_keys |   key   |                     partitions                      |
 --------- ---------- ------------- --------------- --------- ----------------------------------------------------- 
| 9072566 |        4 | Using where | PRIMARY       | PRIMARY | from20190312,from20190313,from20190314,from20190315 |
 --------- ---------- ------------- --------------- --------- ----------------------------------------------------- 
  

По сравнению с

 EXPLAIN SELECT *
FROM summary_by_to_days_range 
WHERE(record_date IN ('2019-03-12','2019-03-13','2019-03-14','2019-03-15'))
AND unit_id = 1148210
AND enum_key IN (9, 10, 38, 311)
GROUP BY unit_id, record_date
ORDER BY record_date DESC;
  

С гораздо лучшими результатами:

  ------ ---------- ------------- --------------- --------- ----------------------------------------------------- 
| rows | filtered |    Extra    | possible_keys |   key   |                     partitions                      |
 ------ ---------- ------------- --------------- --------- ----------------------------------------------------- 
|   16 |      100 | Using where | PRIMARY       | PRIMARY | from20190312,from20190313,from20190314,from20190315 |
 ------ ---------- ------------- --------------- --------- ----------------------------------------------------- 
  

И я не могу понять, почему.. Я предоставляю значения PK, единственное различие заключается в предложении Between date!

Схема таблицы

   `CREATE TABLE summary_by_to_days_range (
  `record_date` date NOT NULL,
  `unit_id` int(11) NOT NULL,
  `enum_key` int(11) NOT NULL,
  `str_value` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`record_date`,`unit_id`,`enum_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(record_date))
(PARTITION START_h VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION from20181231 VALUES LESS THAN (737425) ENGINE = InnoDB,
 PARTITION from20190101 VALUES LESS THAN (737426) ENGINE = InnoDB,
.
.
PARTITION future VALUES LESS THAN MAXVALUE ENGINE = InnoDB)`
  

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

Кто-нибудь?

Обычно неэффективно начинать PRIMARY KEY со столбцов «ключа раздела». В конце концов, сначала выполняется «обрезка разделов»; зачем тогда фильтровать один и тот же материал?

Обычно неэффективно начинать любой составной индекс со столбца, который будет использоваться в тесте «диапазона». Это неуловимо, но я думаю, это объясняет разницу, которую вы видите. С IN (вашим вторым запросом) можно использовать больше полей в PK, что ускоряет выполнение.

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

(для справки)

 WHERE record_date BETWEEN '2019-03-12' AND '2019-03-15'
  AND unit_id = 1148210
  AND enum_key IN (9, 10, 38, 311)

`record_date` date NOT NULL,
PRIMARY KEY (`record_date`,`unit_id`,`enum_key`)

PARTITION BY RANGE (TO_DAYS(record_date))
  

Давайте рассмотрим оба моих комментария следующим образом.

Изменить на

 PRIMARY KEY(unit_id, enum_key, record_date)
  

С этим PK, любой из ваших SELECTs будет

1. Сократите количество разделов до 4 (как и раньше)
2. Быстрая фильтрация до желаемого unit_id . (Я подозреваю, что именно в этом заключается большая часть эффективности.)
3. Перейдите по строкам для enum_key
4. Проверьте, что record_date это правильно.

Я рад установить разделы start и future . (Возможно, вы уже прочитали это.)

Примечание: Иметь более 50 разделов может быть несколько неэффективно. Если у вас есть (или будет иметь) больше, чем это, рассмотрите возможность использования еженедельных или ежемесячных разделов. Это окажет небольшое влияние на мой PK, но не до шага 4.

Что касается PARTITION BY HASH … Я не обнаружил никакого увеличения производительности, используя это. (Или, по крайней мере, ни один, которого нельзя было бы достичь каким-либо другим способом.)