다음 SELECT 쿼리를 최적화하는 방법

다음 SELECT 쿼리를 최적화하는 방법

아래 테이블이 있습니다

id  # primary key

device_id_fk 

auth # there's an index on it

old_auth  # there's an index on it

그리고 다음 질문입니다.

$select_user = $this->db->prepare("
        SELECT device_id_fk 
        FROM wtb_device_auths AS dv 
        WHERE (dv.auth= :auth OR dv.old_auth= :auth) 
        LIMIT 1
");

메인 클라이언트의 서버에 접속할 수 없습니다만, 여기 데이터가 적은 다른 클라이언트가 있습니다.

인증에는 다른 많은 업데이트 쿼리가 있기 때문에 업데이트 쿼리가 느린 쿼리 로그에 기록되기 시작하고 CPU가 급증합니다.

auth에서 로그에 .auth에 하면 select 쿼리는 select 쿼리에 기록됩니다.device_id_fk 다르지않다

union 대신 union을 사용하여 쿼리를 다시 쓰려고 했지만 여전히 CPU 스파이크가 있고 select 쿼리가 느린 쿼리 로그에 기록된다고 합니다.

$select_user = $this->db->prepare("
    (SELECT device_id_fk 
     FROM wtb_device_auths 
     AS dv WHERE dv.auth= :auth) 
    UNION ALL
    (SELECT device_id_fk 
     FROM wtb_device_auths AS dv 
     WHERE dv.old_auth= :auth)
     LIMIT 1"
    );
");

설명하라.

대부분의 경우 저속 쿼리 로그에 있는 유일한 쿼리입니다.질문 작성에 더 적합한 방법이 있나요?인덱스를 추가할 수 있는 최적의 방법이 있습니까?클라이언트는 LAMP를 실행하고 있는 Centos 6 서버에서 MYSQL 5.5와 동등한 오래된 MariaDB 버전을 사용하고 있습니다.

추가 정보

가 에 될 auth하고 있다

$update_device_auth = $this->db->prepare("UPDATE wtb_device_auths SET auth= :auth WHERE device_id_fk= :device_id_fk");

일부 인덱스로 인해 업데이트가 느려지면 안 됩니다.

업데이트와 선택을 모두 제대로 수행하려면 두 개의 인덱스가 필요합니다.내 추측으로는 넌 동시에 둘 다 먹어본 적이 없어

UPDATE wtb_device_auths SET auth= :auth WHERE device_id_fk= :device_id_fk

요.device_id_fk이 업데이트가 제대로 수행되도록 합니다.그리고 그 지수에 관계없이 그것은 외국 열쇠로 선언되어야 한다.

    SELECT device_id_fk 
    FROM wtb_device_auths AS dv 
    WHERE (dv.auth= :auth OR dv.old_auth= :auth) 
    LIMIT 1

.auth, old_auth이 쿼리가 정상적으로 수행되도록 합니다.

★★★auth ★★★★★★★★★★★★★★★★★」old_auth중복되는 항목이 너무 많지 않다고 가정할 때 인덱스가 제대로 작동합니다.MySQL이 인덱스의 결과를 병합하므로 병합 속도가 빨라야 합니다.행이 많이 일치하지 않는 한요.

「 」도 는, 「 」old_auth on에 지표를 붙입니다.old_auth.

다른 처럼, 이 문구는select쿼리는 일치하는 auth 또는 old_auth를 가진 여러 일치 디바이스 중 하나를 반환할 수 있습니다.이건안안 안좋좋요요 요요요요 。 ifauth ★★★★★★★★★★★★★★★★★」old_auth는 디바이스를 식별하고 고유한 제약을 추가하는 것을 목적으로 합니다.

---

또는 데이터를 재구성해야 합니다.동일한 값을 가진 여러 열은 빨간색 플래그입니다.이로 인해 인덱스가 급증하고 저장할 수 있는 버전 수가 제한될 수 있습니다.대신에, 1 행에 1 개의 인증만을 사용하고, 각 디바이스에 복수의 행을 설정할 수 있습니다.

create table wtb_device_auths (
  id serial primary key,
  device_id bigint not null references wtb_devices(id),
  auth text not null,
  created_at datetime not null default current_timestamp,

  index(auth)
);

이제 하나의 열만 검색하면 됩니다.

select device_id from wtb_device_auths where auth = ?

1개의 디바이스에 다수의 wtb_device_auths 행을 설정할 수 있게 되었습니다.디바이스의 현재 인증을 원할 경우 최신 인증을 검색합니다.

select device_id
from wtb_device_auths
where device_id = ?
order by created_at desc
limit 1

각 디바이스에는 몇 가지 인증밖에 없기 때문에 이 조작은 고속입니다.device_id인덱스만 사용할 수 있습니다.디바이스의 몇 개의 행을 빠르게 정렬할 수 있습니다.

그렇지 않으면 다음과 같은 추가 결합 인덱스가 필요할 수 있습니다.created_at, device_id여기에서는 검색 및 정렬에 대해 설명합니다.created_at검색 및 정렬을 위한 쿼리뿐 아니라created_at그리고.device_id.

OR보통 전체 테이블 스캔이 느립니다.이것.UNION적절한 트릭과 함께INDEXes보다 고속입니다.

( SELECT  device_id_fk
    FROM  wtb_device_auths AS dv
    WHERE  dv.auth= :auth
    LIMIT  1 )
UNION ALL
( SELECT  device_id_fk
    FROM  wtb_device_auths AS dv
    WHERE  dv.old_auth= :auth
    LIMIT  1 )
LIMIT 1

또한 다음과 같은 "복합" 인덱스를 사용합니다.

INDEX(auth, device_id)
INDEX(old_auth, device_id)

이러한 인덱스는 기존 인덱스를 동일한 첫 번째 열로 대체할 수 있습니다.

내가 3개를 가지고 있는 것을 주목해라.LIMITs; 1개밖에 없었습니다.

그거UNION ALL에는 임시 테이블이 포함됩니다.5.7(적어도)로 업그레이드해야 합니다.이 버전은 temp 테이블을 최적화합니다.

A LIMIT없이ORDER BY임의의 행이 표시됩니다.괜찮으시겠어요?

이 쿼리에 대해 slowlog 항목의 전체 텍스트를 제공하십시오. 이 항목에는 유용할 수 있는 정보가 포함되어 있습니다."Rows_examed"가 2보다 크거나 3보다 크면 뭔가 이상한 일이 일어나고 있는 것입니다.

언급URL : https://stackoverflow.com/questions/60217266/how-to-optimize-the-following-select-query