MySQL: 포맷된 날짜에 왼쪽 조인 최적화

MySQL: 포맷된 날짜에 왼쪽 조인 최적화

이 쿼리의 속도를 최적화하려고 합니다.

      SELECT t.t_date td, v.visit_date vd
      FROM temp_dates t
      LEFT JOIN visits v ON DATE_FORMAT(v.visit_date, '%Y-%m-%d') = t.t_date
      ORDER BY t.t_date

v.visit_date는 DATETIME 유형이며 t.t_date는 '%Y-%m-%d' 형식의 문자열입니다.v.visitdate에 인덱스를 생성하는 것만으로는 속도가 향상되지 않습니다.그래서 저는 @oysteing이 여기서 준 솔루션을 시도해 보려고 했습니다.DATE_FORMAT을 사용하여 mysql 그룹을 최적화하는 방법 이 SQL에서 가상 열을 생성했습니다. ALTER TABLE visits ADD COLUMN datestr varchar(10) AS (DATE_FORMAT(visit_date, '%Y-%m-%d')) VIRTUAL;그러나 이 열에 인덱스를 생성하려고 하면CREATE INDEX idx_visit_date on visits(datestr)다음의 에러가 표시됩니다.

#1901 - 함수 또는 식 'date_format()'은 의 GENTERED ALWAYS AS 절에서 사용할 수 없습니다.datestr

내가 뭘 잘못하고 있지?내 DB는 Maria DB 10.4.8입니다.

잘 부탁드립니다 - Ulrich

date_format()영구 생성된 열에도 사용할 수 없습니다.인덱스에서는 가상일 수 없습니다.지속되어야 합니다.

매뉴얼에는 명시적인 문구를 찾을 수 없었지만, 이것은 의 출력에 의한 것이라고 생각합니다.date_format()는 로케일에 따라 달라지기 때문에 엄밀하게 결정적이지 않습니다.

대신date_format()다음과 같은 결정론적 함수를 사용하여 문자열을 만들 수 있습니다.concat(),year(),month(),day()그리고.lpad().

...
datestr varchar(10) AS (concat(year(visit_date),
                               '-',
                               lpad(month(visit_date), 2, '0'),
                               '-',
                               lpad(day(visit_date), 2, '0')))
...

근데 아까 댓글에서 말씀드렸듯이 지금 잘못된 부분을 수정하고 있어요.날짜/시간을 문자열로 저장해서는 안 됩니다.그래서 홍보하는 게 좋을 것 같아요.temp_dates.t_date에 대해서date및 사용date()추출하다date의 일부visit_date생성된 인덱스된 열에서

...
visit_date_date date AS (date(visit_date))
...

또한 인덱스를 작성하는 것이 좋습니다.temp_dates.t_date.

이거 괜찮으세요?

SELECT t.t_date td, v.visit_date vd
  FROM temp_dates t
  LEFT JOIN visits v ON DATE(v.visit_date) = DATE(t.t_date)
 ORDER BY t.t_date

그렇다면 문제 해결 방법이 있습니다.

1. 를 추가합니다.DATE결정론을 사용한 열DATE()기능하다visit_date물건.이것처럼.

   ALTER TABLE visits ADD COLUMN dateval DATE AS (DATE(visit_date)) VIRTUAL; 
   CREATE INDEX idx_visit_date on visits(dateval);
   

2. 그런 다음 다른 테이블에 가상 컬럼을 만듭니다(VARCHAR() 컬럼에 날짜가 올바르게 입력된 컬럼).

   ALTER TABLE temp_dates ADD COLUMN dateval DATE AS (DATE(t_date)) VIRTUAL;
   CREATE INDEX idx_temp_dates_date on temp_dates (dateval);
   

이것은, 다음과 같은 이유로 동작합니다.DATE()결정론적이다.DATE_FORMAT().

그럼 질문은 다음과 같습니다.

SELECT t.t_date td, v.visit_date vd
  FROM temp_dates t
  LEFT JOIN visits v ON v.dateval = t.dateval
 ORDER BY t.t_date

이 솔루션은 (가상)에 대한 인덱스를 제공합니다.DATE컬럼을 클릭합니다.그런 열에 대한 색인 매칭이 효율적이기 때문에 좋습니다.

단, 최적의 해결책은 데이터 유형을 변경하는 것입니다.temp_date.t_date부터VARCHAR()로.DATE.

DATE_FORMAT(expr, format)접속 로케일에 따라 다르기 때문에 가상 컬럼에서는 사용할 수 없습니다(MariaDB 발행 MDEV-11553).

local을 추가하는 date_format에 3개의 인수 형식이 생성되었습니다.

DATE_FORMAT(visit_date, '%Y-%m-%d', 'en_US')는 MariaDB-10.3+ 안정판의 가상 컬럼 표현으로 사용할 수 있습니다.

「」를 사용합니다.DATE또는 열 표현식 주위에 함수를 사용하지 않도록 쿼리를 변경하는 것이 좋습니다.

함수는 "sargeable"이 아닙니다.

고려사항:

ON  v.visit_date >= t.t_date
AND v.visit_date  < t.t_date + INTERVAL 1 DAY

언급URL : https://stackoverflow.com/questions/66144539/mysql-optimize-left-join-on-formatted-date