Я пытаюсь решить проблему производительности с этой таблицей
+--------------+------------------+------+-----+---------+----------------+
| 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 |
+----+-------------+------------+------------+------+---------------------------------+---------------------------------+---------+-------+-------+----------+---------------------------------------------------------------------+
Вот и ищу способ решить эту проблему и нужен совет. Помогает ли мне разбиение по HASH(direction_id) или нет? Если это поможет, как лучше всего это сделать?
Или, может быть, есть какой-то другой способ исправить это.
@Strawberry не имеет значения, я думаю, что это можно было бы опустить
Во-первых, давайте исправим ваш запрос, чтобы он стал действительным запросом агрегации. Предположительно, вам нужно среднее значение 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 мы также могли бы рассмотреть возможность использования индекса для 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);
нужно ли включать rate
для индексации?
В частности, MySQL 8.0.13 — первая версия, поддерживающая индексы выражений.
кажется, что это не имеет никакого эффекта. Индекс используется, но есть еще 26200 строк
@ArtemIlchenko: Я не уверен в вашем комментарии. Индекс предназначен для повышения производительности, а не для изменения количества строк, возвращаемых запросом.
Индекс называется «покрывающим», потому что он включает все столбцы, необходимые в любом месте запроса. Объяснение укажет на это, сохранив «Использование индекса» (что не то же самое, что «Использование условия индекса»).
Для среднесуточных ставок вы это имели в виду?
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
Я решил уменьшить количество данных для каждого direction_id
. Я думаю, это единственный способ. Спасибо.
YEAR(created_at), MONTH(created_at), DAY(created_at)
А?