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

Есть таблица измерений :

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
CREATE TABLE measurement
(
    id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    opdate DATETIME,
    u VARCHAR(255),
    t1 DECIMAL(4,2),
    t2 DECIMAL(4,2),
    h DECIMAL(4,2),
    p DECIMAL(5,2),
    pid INT UNSIGNED,
  );



t1,t2,h,p - это измерения. две температуры, какая-то там высота и давление. физически это не так важно.
pid это id-шка устройства с которого считали эти измерения. каждое такое устройство выдаёт все измерения которые и записываются в таблицу за текущую дату - opdate . которая помимо даты содержит ещё и время.

а теперь сама задача : требуется выяснить для каждого устройства в разрезе дней максимальное и минимальное значение каждого из измерений. и время когда они эти макс и мин были достигнуты .
Пример
pid t1 t2 h p opdate0 1115556 10.06.1990 13:000 3915569 10.06.1990 13:201 110355416 11.06.1990 13:001 54760 11.06.1990 13:301 1115556 10.06.1990 13:002 11015051560 10.06.1990 13:002 3178960 10.06.1990 13:23

скаежем устройство с id=0 дало результаты 10.06.1990 . для этого устройства будет только одна строка :
pid date t1max t1maxDatet1minDate t1minDatet2maxt2maxDate t2mint2minDate hmax hmaxdate hmin hmindate pmax pmaxdatepmin pmindate010.06.19901113:00313:201513:00 913:2015513:205513:006913:20613:00

а для устройства с id=1 будет 2 строки так как там 2 различных даты (10.06.1990 и 11.06.1990)

а для устройства с id=2 как и для устройства id=0 будет 1 строка так как там одна различная дата но с разным временем (10.06.1990 и 11.06.1990)

сам запрос был мною сделан

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
select device,date1,
mint1,max(mx1.opdate) mintdate1,
maxt1,max(mx2.opdate) maxtdate1,

mint2, max(mx3.opdate) mintdate2,
maxt2, max(mx4.opdate) maxtdate2,
minh,  max(mx5.opdate) minhdate ,
maxh,  max(mx6.opdate) maxhdate,
minp,  max(mx7.opdate)  minpdate,
maxp,  max(mx8.opdate) maxpdate


from 
(
select coalesce(pid,0) as device, d.date1, count(*) as measurecount,
min(t1) as  mint1, 

max(t1)  as  maxt1   ,
min(t2)  as  mint2,
max(t2)  as  maxt2,
min(h)   as  minh ,
max(h)   as  maxh,
min(p)   as  minp ,
max(p)   as  maxp

                        from measurement m
                        inner join 
                        (select distinct date(opdate) date1 from measurement) d on d.date1= date(m.opdate)
                        
group by d.date1,pid
)  as res
inner join measurement mx1 on res.mint1  = mx1.t1 and res.date1 = date(mx1.opdate) and coalesce(mx1.pid,0)=res.device
inner join measurement mx2 on res.maxt1  = mx2.t1 and res.date1 = date(mx2.opdate) and coalesce(mx2.pid,0)=res.device
inner join measurement mx3 on res.mint2  = mx3.t2 and res.date1 = date(mx3.opdate) and coalesce(mx3.pid,0)=res.device
inner join measurement mx4 on res.maxt2  = mx4.t2 and res.date1 = date(mx4.opdate) and coalesce(mx4.pid,0)=res.device
inner join measurement mx5 on res.minh   = mx5.h and res.date1 = date(mx5.opdate) and coalesce(mx5.pid,0)=res.device
inner join measurement mx6 on res.maxh   = mx6.h and res.date1 = date(mx6.opdate) and coalesce(mx6.pid,0)=res.device
inner join measurement mx7 on res.minp   = mx7.p and res.date1 = date(mx7.opdate) and coalesce(mx7.pid,0)=res.device
inner join measurement mx8 on res.maxp   = mx8.p and res.date1 = date(mx8.opdate) and coalesce(mx8.pid,0)=res.device

group by device, date1, 
mint1,
maxt1,
mint2,
maxt2,
minh,
maxh,
minp,
maxp



но вот проблема уж очень много строк в таблицы - просто очень. дождаться не удаётся терпения , да и веб. запросы не должны столько работать.
Как можно было бы оптимизировать такой запрос ? или индексов нарубить или ещё как
...
Рейтинг: 0 / 0
23.10.2018, 18:06
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721636
MAPA3OT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MAPA3OT
Участник
andron81,

Попробуйте денормализовать таблицу - вынесите trunc (odate) в отдельное поле и постройте индекс на пару pid, newDate
А потом что-нибудь типа этого:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
select 
	t.pid,
	t.dt,
	max(t1) over (partition by t.dt) maxt1,
	LAST_ROW(odate) OVER (PARTITION BY t.pid, t.dt ORDER BY t1 ) maxtdate1,
	min(t1) over (partition by t.pid, t.dt) mint1,
	FIRST_ROW(odate) OVER (PARTITION BY t.pid, t.dt ORDER BY t1 ) mintdate1,
	max(t2) over (partition by t.pid, t.dt) maxt2,
	LAST_ROW(odate) OVER (PARTITION BY t.pid, t.dt ORDER BY t2 ) maxtdate2,
	min(t2) over (partition by t.pid, t.dt) mint2,
	FIRST_ROW(odate) OVER (PARTITION BY t.pid, t.dt ORDER BY t2 ) mintdate2,
	max(h) over (partition by t.pid, t.dt) max2,
	LAST_ROW(odate) OVER (PARTITION BY t.pid, t.dt ORDER BY h ) maxhdate,
	min(h) over (partition by t.pid, t.dt) mint2,
	FIRST_ROW(odate) OVER (PARTITION BY t.pid, t.dt ORDER BY h ) minhdate,
	max(p) over (partition by t.pid, t.dt) max2,
	LAST_ROW(odate) OVER (PARTITION BY t.pid, t.dt ORDER BY p ) maxpdate,
	min(p) over (partition by t.pid, t.dt) mint2,
	FIRST_ROW(odate) OVER (PARTITION BY t.pid, t.dt ORDER BY p ) minpdate,
(
	select pid, opdate, trunc(odate) dt, t1, t2, h, p
) t
group by t.pid, t.dt
...
Рейтинг: 0 / 0
23.10.2018, 18:55
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721638
MAPA3OT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MAPA3OT
Участник
andron81,
Пардон, забыл FROM, но в принципе понятно, куда его добавить :)
...
Рейтинг: 0 / 0
23.10.2018, 18:57
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721660
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
MAPA3OT,

trunc это наверно из оракла. Но попробую
...
Рейтинг: 0 / 0
23.10.2018, 19:54
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721732
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
andron81MAPA3OT,

trunc это наверно из оракла. Но попробуюТам весь запрос из Оракла
...
Рейтинг: 0 / 0
23.10.2018, 23:19
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721757
MAPA3OT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MAPA3OT
Участник
miksoft,

И эти люди запрещают мне ковыряться пальцем в носу:

FIRST_VALUE
Пример в одну строку

Про TRUNC погорячился, но ТС вполне себе сам знает про DATE() :)
...
Рейтинг: 0 / 0
23.10.2018, 23:48
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721758
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
MAPA3OT,

А, 8-ая версия!
Сорри, я с ней плохо знаком :)
...
Рейтинг: 0 / 0
23.10.2018, 23:49
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721759
MAPA3OT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MAPA3OT
Участник
Вот блин, я ещё и ROW вместо VALUE написал... Не-не-не-не, пора уже высыпаться.
...
Рейтинг: 0 / 0
23.10.2018, 23:50
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721760
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
andron81в разрезе днейЕсли не требуется учитывать свежепоступившие данные, то можно предрассчитывать агрегаты за каждый день. А из них выборка будет работать уже быстро.
...
Рейтинг: 0 / 0
23.10.2018, 23:53
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721763
MAPA3OT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MAPA3OT
Участник
miksoftMAPA3OT,

А, 8-ая версия!
Сорри, я с ней плохо знаком :)

Так, всё равно надо в один проход, то бишь как-то через динамику, если мы забиваем на оконки. Пока в голове только откровенно уродливые идеи.
Как вариант раз materialized view нет, одинокой тёмной ночью запускать заполнение таблицы.
...
Рейтинг: 0 / 0
24.10.2018, 00:06
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721773
MAPA3OT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MAPA3OT
Участник
Да, что вы знаете о боевом безумии?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
SELECT
	ttt.pid, ttt.dt,
	ROUND(ttt.mat1/1000000000000.0, 2) -273 maxt1, FROM_UNIXTIME(mod(ttt.mat1,10000000000)) maxtdate1,
	ROUND(ttt.mit1/1000000000000.0, 2) -273 mint1, FROM_UNIXTIME(mod(ttt.mit1,10000000000)) mintdate1,
	ROUND(ttt.mat2/1000000000000.0, 2) -273 maxt2, FROM_UNIXTIME(mod(ttt.mat2,10000000000)) maxtdate2,
	ROUND(ttt.mit2/1000000000000.0, 2) -273 mint2, FROM_UNIXTIME(mod(ttt.mit2,10000000000)) mintdate2,
	ROUND(ttt.mah/1000000000000.0, 2) maxh, FROM_UNIXTIME(mod(ttt.mah,10000000000)) maxhdate,
	ROUND(ttt.mih/1000000000000.0, 2) minh, FROM_UNIXTIME(mod(ttt.mih,10000000000)) minhdate,
	ROUND(ttt.maxp/1000000000000.0, 2) maxp, FROM_UNIXTIME(mod(ttt.maxp,10000000000)) maxpdate,
	ROUND(ttt.minp/1000000000000.0, 2) minp, FROM_UNIXTIME(mod(ttt.minp,10000000000)) minpdate
	
FROM
(
	select 
			tt.pid, tt.dt,
			max(tt.t1) mat1, min(tt.t1) mit1,
			max(tt.t2) mat2, min(tt.t2) mit2,
			max(tt.h) mah, min(tt.h) mih,
			max(tt.p) maxp, min(tt.p) minp
	FROM (
		SELECT t.pid, t.dt, 
			t.t1*10000000000 + odt t1,
			t.t2*10000000000 + odt t2,
			t.h*10000000000 + odt h,
			t.p*10000000000 + odt p
		(
			select pid, UNIX_TIMESTAMP(opdate) odt, trunc(odate) dt, 100*(273+t1) t1, 100*(273+t2) t2, 100*h h, 100*p p
		) t
	) tt
	group by tt.pid, tt.dt
) ttt
...
Рейтинг: 0 / 0
24.10.2018, 00:40
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721805
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
miksoftandron81в разрезе днейЕсли не требуется учитывать свежепоступившие данные, то можно предрассчитывать агрегаты за каждый день. А из них выборка будет работать уже быстро.

ну можно вообще вести таблицу итогов параллельно с наполнением . то есть инсертим в основную таблу + тут же делаем итоги в таблицу.
...
Рейтинг: 0 / 0
24.10.2018, 07:55
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721853
MAPA3OT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MAPA3OT
Участник
andron81ну можно вообще вести таблицу итогов параллельно с наполнением . то есть инсертим в основную таблу + тут же делаем итоги в таблицу.
Можно, но зависит от того, как часто идёт вставка. Пересчёт - это больно, опять же нужен явный лок на таблицу, может всё же тайная джобина глубокой ночью?

Кстати, если вам нужна вся-вся-вся простынка с данными, то независимо от индексов и хитрых запросов вы упрётесь в размер, так что либо "урезайте осётра" (фильтр по датам), либо сторонняя таблица - ваш единственный вариант.
...
Рейтинг: 0 / 0
24.10.2018, 09:57
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721918
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
MAPA3OT, да записи будут частые , но не думаю , что будет так уж "больно" . объясню и как вы считаете, поясните , пожалуйста (интересует мнение всех участников темы )

То есть ведём 2 таблы : ту что расписал в теме. и табличку итогов:
(pid,date,t1max,t1maxDate,t1minDate,t1minDate,t2max,t2maxDate,t2min,t2minDate ,hmax ,hmaxdate,hmin,hmindate,pmax,pmaxdate, pmin,pmindate)
1. Появляется новая запись записываем в таблицу measurement,
2. А затем ищем запись по ключу (pid,date) в итоговой таблице и сравниваем c макс. показателями. ну то есть t1 сравниваем с t1max, t1 с t1min , t2 с t2max, t1 с t2min и т.д. заменяя эти значения если нашли макс (мин) и пробивая время в этих случаях.

Разве это займет большие ресурсы ?

а ещё вот такую штуку нашли для поиска значения других столбцов строки, содержащей минимальное или максимальное значение. возможно сэкономит ресурсы :

ссылка
...
Рейтинг: 0 / 0
24.10.2018, 11:09
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721951
MAPA3OT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MAPA3OT
Участник
andron81, потенциальная проблема в определении слова "часто" и в записях задним числом. Так (если запись задним числом невозможна) вообще можно раскидать на 2 таблицы и сделать связующую вьюху - то есть report_total, report_daily и view my_cool_report который union из предыдущих таблиц. Таким образом быстро апдейтим маленькую таблицу за день, условной ночью (спад нагрузки) перекидываем всё в большую таблицу и чистимся.

Про MAX-CONCAT я собственно это в "боевом безумии и предложил" сдвиг всех чисел на 12 позиций (с учётом дробной части 14, в случае температуры - привести к положительному значению, ниже -273 по Цельсию не бывает)+дата в числовом формате, в пределах дня влиять не будет.
Но опять же смотрите на вашу версию MySQL, может быть всё же оконки?
...
Рейтинг: 0 / 0
24.10.2018, 11:46
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39721992
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
MAPA3OTandron81, потенциальная проблема в определении слова "часто" и в записях задним числом. Так (если запись задним числом невозможна) вообще можно раскидать на 2 таблицы и сделать связующую вьюху - то есть report_total, report_daily и view my_cool_report который union из предыдущих таблиц. Таким образом быстро апдейтим маленькую таблицу за день, условной ночью (спад нагрузки) перекидываем всё в большую таблицу и чистимся.

Про MAX-CONCAT я собственно это в "боевом безумии и предложил" сдвиг всех чисел на 12 позиций (с учётом дробной части 14, в случае температуры - привести к положительному значению, ниже -273 по Цельсию не бывает)+дата в числовом формате, в пределах дня влиять не будет.
Но опять же смотрите на вашу версию MySQL, может быть всё же оконки?

ну а что плохого в проблеме (потенциальной как вы её назвали) ?

Скажем есть запись в таблице итоги - назовем её ITOGO. (ограничимся параметром t1max)

вот допустим там вот такая картина :
piddatet1maxt1maxDate0 10.06.1990 11 13:000 11.06.1990 23 22:000 12.06.1990 5 06:001 10.06.1990 110 13:301 11.06.1990 230 22:051 12.06.1990 0.523 06:102 11.06.1990 20 22:052 12.06.1990 10 06:10

скажем сейчас 12.06.1990 07:20 и появилась новые показатели для pid=1 (в данном случае это только t1 как договорились) и их нужно записать в таблицу измерения measurement. пусть новая строка это добавляет для pid=1 значение 300.
1. Записываем в measurement.
2. Анализируем стоит ли обновить таблицу итоги исходя из новой строки measurement. для этого ищем в таблице строку по паре ключей (pid=1, date = 12.06.1990 ) смотрим есть , а это строка
1 12.06.1990 0.523 06:10
видим , что 300 > 0.523 , значит апдейтим строчку в итогах. в противном случае не трогаем.
3. Возможен варик когда в итогах не будет строки по ключам (pid, date) .тогда инсерт.

Можете прокомментировать что тут будет так уж забивать ресурсы ?
...
Рейтинг: 0 / 0
24.10.2018, 12:16
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39722004
MAPA3OT
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MAPA3OT
Участник
andron81, в каждой бочке свои заморочки. Если в примере ваша стандартная частота обновления, то забудьте всё, что я писал. Всё будет хорошо.
Просто каждый танцует от своей печки, а в моей печке сейчас немножечко (на порядк и ) веселее, поэтому, извините, спроецировал на вас.
А забивать может (а вернее будет) триггер (или джоб) вместо тупого инсёрт будут инсёрт+селект (с локом таблицы, чтобы не напороться на одновременное изменение) + инсёрт/апдейт.
...
Рейтинг: 0 / 0
24.10.2018, 12:28
| Ответить | Цитировать | Написать  
помогите расставить индексы или как-то ещё ускорить выборку
    #39722027
andron81
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andron81
Участник
MAPA3OTandron81, в каждой бочке свои заморочки. Если в примере ваша стандартная частота обновления, то забудьте всё, что я писал. Всё будет хорошо.
Просто каждый танцует от своей печки, а в моей печке сейчас немножечко (на порядк и ) веселее, поэтому, извините, спроецировал на вас.
А забивать может (а вернее будет) триггер (или джоб) вместо тупого инсёрт будут инсёрт+селект (с локом таблицы, чтобы не напороться на одновременное изменение) + инсёрт/апдейт.

так может вы и правы ) может быть есть подводные камни. валиться строки могут по разному . с разной частотой ))
...
Рейтинг: 0 / 0
24.10.2018, 12:44
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / помогите расставить индексы или как-то ещё ускорить выборку
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&fpage=45&tid=1829524]:
 0ms
get settings:
 10ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 29ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 86ms
get tp. blocked users:
 2ms
others: 255ms
total:  417ms
созд. / загр.: 417ms
x
x
Закрыть


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