ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса
10 сообщений из 10, страница 1 из 1
Оптимизация запроса
    #40078869
Andy_Shuum
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andy_Shuum
Гость
Есть запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
WITH recursive Date_Ranges AS (
   SELECT STR_TO_DATE('2021-06-13 00:43:00', '%Y-%m-%d %H:%i:%s') as dt
   UNION all
   SELECT dt + interval 1 minute
   FROM Date_Ranges
   WHERE dt < '2021-06-20 00:43:00')
SELECT IFNULL(a.date, dt) AS date, IFNULL(a.voltage, 0) AS greenhouse_nullvoltage, IFNULL(a.mac, 146014537921031) AS mac 
FROM Date_Ranges 
LEFT JOIN (SELECT * FROM voltage ORDER BY id DESC LIMIT 10500) as a
ON DATE_FORMAT(a.date, '%Y-%m-%d %H:%i') = DATE_FORMAT(Date_Ranges.dt, '%Y-%m-%d %H:%i') 
    AND a.mac = 146014537921031
ORDER BY date ASC;



Структура таблицы voltage (в ней сейчас порядка 30000 строк):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE `voltage` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `voltage` float NOT NULL,
  `mac` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `date` (`date`),
  UNIQUE KEY `voltage_idx1` (`id`,`date`) USING BTREE,
  KEY `voltage_idx2` (`mac`)
) ENGINE=InnoDB AUTO_INCREMENT=9079 DEFAULT CHARSET=utf8 PACK_KEYS=0;



Выполняется неприлично долго, тормозит, я так подозреваю, что на LEFT JOIN (т.к. по отдельности куски запроса летают). Может кто подскажет как оптимизировать (может как-то можно индексы на временные таблицы наложить) ???

- И когда я уже думал, что завязал - они затащили меня обратно!!!
...
Рейтинг: 0 / 0
19.06.2021, 20:20
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #40078870
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Andy_Shuum
Код: sql
1.
ORDER BY id DESC LIMIT 10500

Какой смысл в этом?
...
Рейтинг: 0 / 0
19.06.2021, 20:26
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #40078873
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Andy_Shuum
Код: sql
1.
DATE_FORMAT(a.date, '%Y-%m-%d %H:%i')

Это зачем? В a.date могут быть секунды?
...
Рейтинг: 0 / 0
19.06.2021, 20:30
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #40078874
Andy_Shuum
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andy_Shuum
Гость
miksoft, отдавать в джоин только то, что нужно, а не все содержимое voltage.
Да, там могут быть секунды.
...
Рейтинг: 0 / 0
19.06.2021, 20:30
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #40078875
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Andy_Shuum
Код: sql
1.
UNIQUE KEY `id` (`id`),

Это избыточно при наличии PRIMARY KEY (`id`),


Andy_Shuum
Код: sql
1.
UNIQUE KEY `voltage_idx1` (`id`,`date`)

Зачем это когда отдельные поля и так уникальны?
...
Рейтинг: 0 / 0
19.06.2021, 20:32
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #40078876
Andy_Shuum
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andy_Shuum
Гость
miksoft, удалил всё, на что вы указали, - быстрее не стало :(
...
Рейтинг: 0 / 0
19.06.2021, 20:34
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #40078878
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Andy_Shuum
miksoft, удалил всё, на что вы указали, - быстрее не стало :(
А зачем вы удалили?
Из все этого точно не нужен только UNIQUE KEY `id` (`id`), все остальное может быть полезным в некоторых случаях. Но вы никакой дополнительной информации не даете.

Кстати, план запроса тоже нужен.
...
Рейтинг: 0 / 0
19.06.2021, 20:36
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #40078879
Andy_Shuum
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andy_Shuum
Гость
miksoft, удалил, чтобы проверить гипотезу, - быстрее не стало, гипотеза проверена :) Я готов дать любую дополнительную информацию по этой теме.

Вот план запроса без
Код: sql
1.
ORDER BY id DESC LIMIT 10500

:
Код: sql
1.
2.
3.
4.
5.
6.
# id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL					2	Using temporary; Using filesort
1	PRIMARY	voltage	ref	voltage_idx2	voltage_idx2	8	const	4549	Using where
2	DERIVED								No tables used
3	RECURSIVE UNION	<derived2>	ALL					2	Using where
	UNION RESULT	<union2,3>	ALL



Но так он работает в три раза дольше, поэтому я таки сортировку и лимит вернул и вот план запроса с ними:
Код: sql
1.
2.
3.
4.
5.
6.
7.
# id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL					2	Using temporary; Using filesort
1	PRIMARY	<derived4>	ALL					9107	Using where; Using join buffer (flat, BNL join)
4	DERIVED	voltage	index		PRIMARY	4		9107	
2	DERIVED								No tables used
3	RECURSIVE UNION	<derived2>	ALL					2	Using where
	UNION RESULT	<union2,3>	ALL
...
Рейтинг: 0 / 0
19.06.2021, 20:39
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #40078885
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Andy_Shuum
Код: sql
1.
a.mac = 146014537921031

Сколько записей остается с этим ограничением?
...
Рейтинг: 0 / 0
19.06.2021, 21:23
| Ответить | Цитировать | Написать  
Оптимизация запроса
    #40078887
Andy_Shuum
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andy_Shuum
Гость
miksoft, это отбор по MAC-адресу (его числовое представление, мне казалось так будет быстрее, чем хранить символы), пока в таблицу voltage пишет только одно устройство, т.е. все записи ... но в будущем будут разные.

Кстати, если изменить запрос таким образом:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
with recursive Date_Ranges AS (
   SELECT STR_TO_DATE('2021-06-13 00:43:00', '%Y-%m-%d %H:%i:%s') as dt
   UNION all
   SELECT dt + interval 1 minute
   FROM Date_Ranges
   WHERE dt < '2021-06-20 00:43:00')
SELECT IFNULL(a.date, dt) AS date, IFNULL(a.voltage, 0) AS greenhouse_nullvoltage, IFNULL(a.mac, 146014537921031) AS mac 
FROM Date_Ranges 
LEFT JOIN (SELECT * FROM voltage WHERE mac = 146014537921031 ORDER BY id DESC LIMIT 10500) as a
ON DATE_FORMAT(a.date, '%Y-%m-%d %H:%i') = DATE_FORMAT(Date_Ranges.dt, '%Y-%m-%d %H:%i') 
ORDER BY date ASC;



Становится незначительно, но быстрее, план запроса тогда выглядит так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
# id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	ALL					2	Using temporary; Using filesort
1	PRIMARY	<derived4>	ALL					4578	Using where; Using join buffer (flat, BNL join)
4	DERIVED	voltage	ref	voltage_idx2	voltage_idx2	8	const	4578	Using where
2	DERIVED								No tables used
3	RECURSIVE UNION	<derived2>	ALL					2	Using where
	UNION RESULT	<union2,3>	ALL
...
Рейтинг: 0 / 0
19.06.2021, 21:28
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=40078870&tid=1828029]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 164ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 46ms
get tp. blocked users:
 2ms
others: 12ms
total:  264ms
созд. / загр.: 264ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]