Оптимизация производительности больших таблиц MySQL

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

+--------------+------------------+------+-----+---------+----------------+
| 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) или нет? Если это поможет, как лучше всего это сделать?

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

YEAR(created_at), MONTH(created_at), DAY(created_at) А?
Strawberry 09.12.2020 16:52

@Strawberry не имеет значения, я думаю, что это можно было бы опустить

Artem Ilchenko 09.12.2020 16:56
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
0
2
51
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Во-первых, давайте исправим ваш запрос, чтобы он стал действительным запросом агрегации. Предположительно, вам нужно среднее значение 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 для индексации?

Artem Ilchenko 09.12.2020 17:00

В частности, MySQL 8.0.13 — первая версия, поддерживающая индексы выражений.

Bill Karwin 09.12.2020 17:14

кажется, что это не имеет никакого эффекта. Индекс используется, но есть еще 26200 строк

Artem Ilchenko 09.12.2020 17:19

@ArtemIlchenko: Я не уверен в вашем комментарии. Индекс предназначен для повышения производительности, а не для изменения количества строк, возвращаемых запросом.

GMB 09.12.2020 19:44

Индекс называется «покрывающим», потому что он включает все столбцы, необходимые в любом месте запроса. Объяснение укажет на это, сохранив «Использование индекса» (что не то же самое, что «Использование условия индекса»).

Rick James 09.12.2020 20:07
Ответ принят как подходящий

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

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. Я думаю, это единственный способ. Спасибо.

Artem Ilchenko 10.12.2020 12:08

Другие вопросы по теме