SQLinfo.ru - Все о MySQL Webew.ru: теория и практика веб-технологий

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 05.03.2023 10:38:50

rugo
Участник
Зарегистрирован: 04.03.2023
Сообщений: 2

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

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

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;

+----+-------------+---------------+------------+------+-----------------------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table         | partitions | type | possible_keys               | key  | key_len | ref  | rows    | filtered | Extra                       |
+----+-------------+---------------+------------+------+-----------------------------+------+---------+------+---------+----------+-----------------------------+
|  1 | SIMPLE      | t_sensor_data | NULL       | ALL  | t_sensor_data_sensor_id_IDX | NULL | NULL    | NULL | 9722303 |    20.48 | Using where; Using filesort |
+----+-------------+---------------+------------+------+-----------------------------+------+---------+------+---------+----------+-----------------------------+
1 row in set, 1 warning (0,00 sec)

P.S. Пробовал тип datetime изменить на timestamp - тоже эффекта не увидел.

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

Неактивен

 

#2 05.03.2023 16:32:51

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5833

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

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

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

Неактивен

 

#3 05.03.2023 16:45:45

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5833

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

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

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

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

Неактивен

 

#4 05.03.2023 20:20:41

rugo
Участник
Зарегистрирован: 04.03.2023
Сообщений: 2

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

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson