Форум пользователей MySQL

rugo · 05.03.2023 10:38:50

Всем добрый день.
Есть такая таблица.

CREATE TABLE `data` (

  `id` bigint NOT NULL AUTO_INCREMENT,

  `sensor_id` int NOT NULL DEFAULT '0',

  `timestmp` datetime NOT NULL,

  `val` float NOT NULL DEFAULT '0',

  `aux_val` float NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),

  UNIQUE KEY `t_sensor_data_sensor_id_IDX` (`sensor_id`,`timestmp`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=9999998 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

В ней 10млн записей.
Нужно определить максимально возможный размер выборки с временем ожидания не более 5с.
Пока у меня получается цифра около 300т записей.
Вопрос 1, это максимум или можно еще что-то оптимизировать?
Запрос всегда такого типа:

SELECT sensor_id,timestmp,val FROM `test`.data WHERE (sensor_id=1) and (timestmp>'2021-01-01 00:00:00') ORDER BY timestmp ASC

Вопрос 2, сейчас MySQL установлен с дефолтными настройками. Я пытался увеличить параметр innodb_buffer_pool_size до 4Гб, как пишут в любой статье про оптимизацию, но заметного эффекта это не приносит. Пробовал и в Ubuntu, и в Windows. Так и должно быть? Проверяю и на других запросах, тоже самое.
Пробовал изменять и другие параметры (innodb_flush_log_at_trx_commit=2) - заметного ускорения запросов тоже не было.

Вопрос 3, правильно ли я понимаю, что время запроса зависит от:
1. Общего количества записей в таблице (в меньшей степени).
2. Размера выборки из записи.
3. Количества записей в выборке.
Перечислены не все параметры, хотелось бы узнать именно про эти.

Вопрос 4.
Делаю такой запрос.

SELECT sensor_id,timestmp,val FROM `test`.t_sensor_data WHERE (sensor_id=1 or sensor_id=2 or sensor_id=3 or sensor_id=4 or sensor_id=5 or sensor_id=6 or sensor_id=7 or sensor_id=8 or sensor_id=9 or sensor_id=10) and (timestmp>'2021-01-01 00:00:00') ORDER BY timestmp ASC

Время его выполнения 15с, размер выборки - 1млн записей.
Если разбить его на 10 запросов, т.е. каждый sensor_id запрашивать отдельно, то суммарное время будет около 7с, что в два раза меньше.
EXPLAIN показывает, что индексы не будут использованы.

mysql>

explain SELECT sensor_id,timestmp,val FROM `test`.t_sensor_data WHERE (sensor_id=1 or sensor_id=2 or sensor_id=3 or sensor_id=4 or sensor_id=5 or sensor_id=6 or sensor_id=7 or sensor_id=8 or sensor_id=9 or sensor_id=10) and (timestmp>'2021-01-01 00:00:00') ORDER BY timestmp ASC;

Отредактированно rugo (05.03.2023 10:52:56)

vasya · 05.03.2023 16:32:51

Для первого запроса нужен индекс (`sensor_id`,`timestmp`,`val`)

Желательно, чтобы innodb_buffer_pool_size вмещал данные и индексы или активно использующуюся часть данных.

vasya · 05.03.2023 16:45:45

3. Зависит от того:
-) Сколько требуется на нахождение нужных записей (размер таблицы, подходящие индексы)
-) Сколько выбирается (кол-во записей, размер каждой записи)

4. Какая версия?
Разбить на десять запросов не эквивалентно исходному, т.к. общий результат отсортирован по timestmp
В случае десяти отдельных запросов их выборки нужно будет складывать и сортировать отдельно.

timestamp занимает в два раза меньше места. Явно эффект проявится, когда буфера будет не хватать.

rugo · 05.03.2023 20:20:41

vasya написал:
Для первого запроса нужен индекс (`sensor_id`,`timestmp`,`val`)
.

Сделал индекс на три колонки, стало чуть побыстрее (вместо 15с - 10с).
И вообще с индексами на sensor_id и timestmp интересная картина. Без них и с ними запрос 15с.

vasya написал:
Желательно, чтобы innodb_buffer_pool_size вмещал данные и индексы или активно использующуюся часть данных.
.

Делаю innodb_buffer_pool_size=1G (или 4G), перезапускаю сервис - результаты запросов те же самые.

vasya написал:
4. Какая версия?
.

mysql> status;
--------------
mysql Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))

Connection id: 10
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.32-0ubuntu0.22.04.2 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
Uptime: 44 sec

Threads: 2 Questions: 21 Slow queries: 1 Opens: 147 Flush tables: 3 Open tables: 65 Queries per second avg: 0.477

Форум пользователей MySQL

#1 05.03.2023 10:38:50

Время запроса к большой базе

#2 05.03.2023 16:32:51

Re: Время запроса к большой базе

#3 05.03.2023 16:45:45

Re: Время запроса к большой базе

#4 05.03.2023 20:20:41

Re: Время запроса к большой базе

vasya написал:

vasya написал:

vasya написал:

Board footer