Блог

Я пытаюсь решить проблему с производительностью с помощью этой таблицы

  -------------- ------------------ ------ ----- --------- ---------------- 
| Field        | Type             | Null | Key | Default | Extra          |
 -------------- ------------------ ------ ----- --------- ---------------- 
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| direction_id | int(10) unsigned | NO   | MUL | NULL    |                |
| created_at   | datetime         | NO   |     | NULL    |                |
| rate         | decimal(16,6)    | NO   |     | NULL    |                |
 -------------- ------------------ ------ ----- --------- ---------------- 
 

которая содержит около 100 миллионов строк

Существует только один запрос, который выбирает данные из этой таблицы:

 SELECT AVG(rate) AS rate, created_at 
FROM statistics 
WHERE direction_id = ? 
AND created_at BETWEEN ? AND ? 
GROUP BY created_at
 

direction_id это внешний ключ, но он обладает довольно низкой избирательностью:

  ---- ------------- ------------ ------------ ------ --------------------------------- --------------------------------- --------- ------- ------- ---------- --------------------------------------------------------------------- 
| id | select_type | table      | partitions | type | possible_keys                   | key                             | key_len | ref   | rows  | filtered | Extra                                                               |
 ---- ------------- ------------ ------------ ------ --------------------------------- --------------------------------- --------- ------- ------- ---------- --------------------------------------------------------------------- 
|  1 | SIMPLE      | statistics | NULL       | ref  | statistics_direction_id_foreign | statistics_direction_id_foreign | 4       | const | 26254 |    11.11 | Using index condition; Using where; Using temporary; Using filesort |
 ---- ------------- ------------ ------------ ------ --------------------------------- --------------------------------- --------- ------- ------- ---------- --------------------------------------------------------------------- 
 

Итак, я ищу способ решить эту проблему и нуждаюсь в совете.
Помогает ли мне разделение по ХЭШУ (direction_id) или нет?
Если это поможет, каков наилучший способ сделать это?

Или, может быть, есть какой-то другой способ это исправить.

Вы имели в виду это для среднесуточных ставок?

 SELECT AVG(rate) AS rate, 
       DATE(created_at) 
    FROM statistics 
    WHERE direction_id = ? 
      AND created_at BETWEEN ? AND ? 
    GROUP BY DATE(created_at)
 

И есть INDEX(direction_id, created, rate) — это и «покрытие», и «составное». В объяснении будет указано «Использование индекса», чтобы указать «покрытие», что указывает на то, что весь запрос может быть выполнен, просматривая только BTree индекса. Следовательно, «Покрытие» дает дополнительный прирост производительности.

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

PARTITIONing не указано.

может быть указано «Сводные таблицы». http://mysql.rjweb.org/doc.php/summarytables

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

 SELECT AVG(rate) AS rate, DATE(created_at) as created_day
FROM statistics 
WHERE direction_id = ? AND created_at BETWEEN ? AND ? 
GROUP BY DATE(created_at)
 

Затем я бы рекомендовал создать следующий индекс:

 create index idx_statistics on statistics (direction_id, created_at, rate);
 

В последних версиях MySQL мы могли бы также рассмотреть возможность использования индекса on date(create_at) . Если вы можете жить со следующим where предложением:

 WHERE direction_id = ? AND DATE(created_at) BETWEEN ? AND ? 
 

Тогда пригодился бы следующий индекс:

 create index idx_statistics on statistics (direction_id, (date(created_at)), rate);