MariaDB - sql 쿼리 성능 또는 최적화

MariaDB - sql 쿼리 성능 또는 최적화

보관 절차가 있습니다.성능 면에서 약간 문제가 있습니다.저장된 절차의 성능을 개선하고 싶은데 어떻게 해야 할지 모르겠어요.제 데이터베이스에는 약 300만 건의 기록이 있습니다.이 쿼리를 하나씩 실행하면 성능이 좋습니다.하지만 150명이 동시에 이 저장 프로시저를 실행하면 CPU에 스파이크가 발생합니다.

예를 들어, 절차와 테이블 구조를 만들었습니다.

내 저장 프로시저:

BEGIN
SELECT ss.car_route from person o
inner join car_time ss on ss.inst_id =o.inst_id  
and ss.start_time<=DATE_FORMAT(CURTIME(),'%H:%i')  AND  ss.finish_time>= date_format(curtime() ,'%H:%i') AND ss.car_id=carid
and ss.days like concat('%',(select WEEKDAY(now())+1),'%') 
where (o.car_id=carid or o.back_car_id=carid ) LIMIT 1 into @route_;
select sf.stop_service from car_comp sf 
inner join cars s on s.inst_id = sf.id and s.id=carid and s.active=1 limit 1
into @stop_ser;



if @route_ = 1 and @stop_ser=0 THEN

select DISTINCT ss.start_time,ss.finish_time ,o.id,o.name,r.photo, oh.state ,oh.datee,ss.car_route,
ifnull(bh.id,0) AS called,
ifnull(mh.excuse_id,0) AS excuse_id,
ifnull(o.latitude_1,0) AS latitude_1,
ifnull(o.longitude_1,0) AS longitude_1,
ifnull(o.latitude_2,0) AS latitude_2,
ifnull(o.longitude_2,0) AS longitude_2,
case when (ifnull(o.call_notify,0)=1 or ifnull(o.mes_notify,0)=1) then 1 else 0 end AS call_notify ,
ifnull(o.rownumber,0) AS rownumber,
ifnull(o.number_1,0) AS number_1,
ifnull(o.number_2,0) AS number_2,
ifnull(o.brownumber,0) AS brownumber,
ifnull(ROUND(o.notify_meter_1/2),0) AS notify_meter_1,
ifnull(ROUND(o.notify_meter_2/2),0) AS notify_meter_2
from person o
inner join car_time ss on ss.inst_id =o.inst_id and o.car_id=ss.car_id
and ss.start_time<=DATE_FORMAT(CURTIME(),'%H:%i')  AND  ss.finish_time>= date_format(curtime() ,'%H:%i')
and ss.days like concat('%',(select WEEKDAY(now())+1),'%')

LEFT JOIN notify_records bh ON bh.table_id=o.id AND bh.car_route=@route_
and bh.table_name='person' AND bh.notify=4 AND bh.car_id=o.car_id and bh.date_ >= CURDATE() and bh.date_ < CURDATE() + INTERVAL 1 DAY 

left join  person_records oh   on  oh.person_id=o.id
and oh.car_id=o.car_id
and date_format(oh.datee,'%H:%i') >=ss.start_time
and date_format(oh.datee,'%H:%i') <=ss.finish_time
AND oh.car_route= @route_
and
oh.id in(select max(id) from person_records
where date_time >= CURDATE() and date_time < CURDATE() + INTERVAL 1 DAY and car_id = carid and car_id = carid
GROUP by person_id
)
left join inst ok on o.inst_id = ok.id and o.car_id=carid
left join excuse_records mh on mh.person_id=o.id and mh.date_time >= CURDATE() and mh.date_time < CURDATE() + INTERVAL 1 DAY and (mh.car_route=ss.car_route)
left join photo_ r on r.table_id = o.id and r.table_name = 'person'
where
(ss.car_route=o.cars_route_ or o.cars_route_=3) and

o.car_id = carid and o.active=1
AND o.work_time=ss.work_time;


elseif @route_ = 2 and @stop_ser=0 then

select DISTINCT ss.start_time,ss.finish_time ,o.id,o.name,r.photo, oh.state ,oh.datee,ss.car_route,
ifnull(bh.id,0) AS called,
ifnull(mh.excuse_id,0) AS excuse_id,
ifnull(o.latitude_1,0) AS latitude_1,
ifnull(o.longitude_1,0) AS longitude_1,
ifnull(o.latitude_2,0) AS latitude_2,
ifnull(o.longitude_2,0) AS longitude_2,
case when (ifnull(o.call_notify,0)=1 or ifnull(o.mes_notify,0)=1) then 1 else 0 end AS call_notify ,
ifnull(o.rownumber,0) AS rownumber,
ifnull(o.number_1,0) AS number_1,
ifnull(o.number_2,0) AS number_2,
ifnull(o.brownumber,0) AS brownumber,
ifnull(ROUND(o.notify_meter_1/2),0) AS notify_meter_1,
ifnull(ROUND(o.notify_meter_2/2),0) AS notify_meter_2
from person o
inner join car_time ss on ss.inst_id =o.inst_id and o.back_car_id=ss.car_id
and ss.start_time<=DATE_FORMAT(CURTIME(),'%H:%i')  AND  ss.finish_time>= date_format(curtime() ,'%H:%i')
and ss.days like concat('%',(select WEEKDAY(now())+1),'%')
LEFT JOIN notify_records bh ON bh.table_id=o.id AND bh.car_route=@route_
and bh.table_name='person' AND bh.notify=4 AND bh.car_id=o.back_car_id and bh.date_ >= CURDATE() and bh.date_ < CURDATE() + INTERVAL 1 DAY  
left join  person_records oh   on  oh.person_id=o.id
and oh.car_id=o.back_car_id and oh.car_route=2
and date_format(oh.datee,'%H:%i') >=ss.start_time
and date_format(oh.datee,'%H:%i') <=ss.finish_time
AND oh.car_route= @route_
and
oh.id in (select max(id) from person_records
where date_time >= CURDATE() and date_time < CURDATE() + INTERVAL 1 DAY and car_id = carid
GROUP by person_id
)
left join inst ok on o.inst_id = ok.id and o.car_id=carid
left join excuse_records mh on mh.person_id=o.id and mh.date_time >= CURDATE() and mh.date_time < CURDATE() + INTERVAL 1 DAY and (mh.car_route=ss.car_route)
left join photo_ r on r.table_id = o.id and r.table_name = 'person'
where
(ss.car_route=o.cars_route_ or o.cars_route_=3) and

o.back_car_id = carid and o.active=1
AND o.work_time=ss.work_time;


END IF;


end

여기 데이터베이스 예시가 있습니다.

저는 .cnf를 개선했지만 여전히 성능에 어려움을 겪고 있습니다.이 쿼리에 무슨 문제가 있습니까?무엇을 바꿀 수 있습니까?

지금부터 감사합니다.

편집:

서버 버전: 10.1.41-MariaDB - MariaDB Server

인덱스가 있습니다.테스트 데이터를 생성하는 동안 인덱스를 추가하는 것을 잊었습니다.

이게 대체 뭐죠?

ss.days like concat('%',(select WEEKDAY(now())+1),'%')

최소한 다음으로 변경하면 속도를 높일 수 있습니다.

ss.days like concat('%',WEEKDAY(now()),'%')

그럼 평일이 2일이면 2, 21, 20, 12, 12, 12...

이것들은 유용할 것입니다.ss:

(car_id, inst_id, start_time)
(inst_id, car_id, finish_time)

LIMIT 1없이.ORDER BY임의의 행이 반환되는 결과를 초래하는 건가요?가?LIMIT중복?아니면.ORDER BY필요하신가요?

시간을 좀 맞춰보시라고 제안해보세요 -- 어느 것이 확실하지는 않습니다.SELECTsCPU를 가장 많이 씹고 있습니다.

만약에PRIMARY KEY의cars가id, 그럼 왜 테스트를 하십니까?inst_id그리고.active? 야익스!PK가 없는 것 같네요.cars! 모든 테이블에 PK가 있는지 확인 부탁드립니다.

중복:

              and  car_id = carid
              and  car_id = carid

왜 두 번이나?그리고 그 기둥들은 어떤 테이블에 있습니까?무슨 일인지 알 수 있도록 칼럼에 자격을 부여해 주시기 바랍니다.

언제@stop_ser=0, 그 절차는 아무 효과가 없습니까?그런 경우에는 먼저 테스트를 수행하여 컴퓨팅을 피할 수 있습니다.@route.

바꾸다start_time데이터 타이프에TIME; 그러면 당신은 그들을 제거할 수 있습니다.DATE_FORMAT인에

      and  ss.start_time<=DATE_FORMAT(CURTIME(),'%H:%i')
      AND  ss.finish_time>= date_format(curtime() ,'%H:%i')

또한 불평등 검정을 주의해야 합니다. 이로 인해 원하지 않는 에지 사례가 발생할 수 있습니다.

사용하지않습니다.(m,n)위에FLOAT(예: float(11,7)); 불필요한 반올림을 합니다.또한 적도와 경도에 매우 가까운 곳을 제외하고는 lat/lat의 소수점 7자리를 얻을 수 없습니다=0.정밀도에 대한 자세한 내용: http://mysql.rjweb.org/doc.php/latlng#representation_choices

당신이 그것들을 정리하고 요청한 정보를 제공한 후 다시 살펴보겠습니다.

언급URL : https://stackoverflow.com/questions/62327924/mariadb-sql-query-performance-or-optimization