현재 위치

홈 ›› Forums ›› 공부 ›› 프로그래밍 QnA

MySQL join 속도 문제

koku_ma의 이미지
글쓴이: koku_ma / 작성시간: 목, 2010/03/04 - 7:34오후

테이블 두개를 join 하는데 너무 시간이 결러서 문의 드립니다.

속도를 어떻게 빠르게 할 방법이 없을까요?

==========================================
첫 번째 테이블 스키마
==========================================
CREATE TABLE w_60_fixed (
stid int(11) NOT NULL DEFAULT '0',
utime int(11) NOT NULL DEFAULT '0',
year int(11) NOT NULL DEFAULT '0',
day int(11) NOT NULL DEFAULT '0',
tod int(11) NOT NULL DEFAULT '0',
airtemp double(16,4) NOT NULL DEFAULT '0.0000',
rh double(16,4) NOT NULL DEFAULT '0.0000',
gndtemp double(16,4) NOT NULL DEFAULT '0.0000',
rad double(16,4) NOT NULL DEFAULT '0.0000',
rain double(16,4) NOT NULL DEFAULT '0.0000',
wet1 double(16,4) NOT NULL DEFAULT '0.0000',
wet2 double(16,4) NOT NULL DEFAULT '0.0000',
wspd double(16,4) NOT NULL DEFAULT '0.0000',
wdir double(16,4) NOT NULL DEFAULT '0.0000',
sdwdir double(16,4) NOT NULL DEFAULT '0.0000',
rain05 double(16,4) NOT NULL DEFAULT '0.0000',
rain_day double(16,4) NOT NULL DEFAULT '0.0000',
rain05_day double(16,4) NOT NULL DEFAULT '0.0000',
sundur double(16,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (stid,utime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

==========================================
두 번째 테이블 스키마
==========================================
CREATE TABLE w_60_org (
stid int(11) NOT NULL DEFAULT '0',
utime int(11) NOT NULL DEFAULT '0',
year int(11) NOT NULL DEFAULT '0',
day int(11) NOT NULL DEFAULT '0',
tod int(11) NOT NULL DEFAULT '0',
airtemp double(16,4) NOT NULL DEFAULT '0.0000',
rh double(16,4) NOT NULL DEFAULT '0.0000',
gndtemp double(16,4) NOT NULL DEFAULT '0.0000',
rad double(16,4) NOT NULL DEFAULT '0.0000',
rain double(16,4) NOT NULL DEFAULT '0.0000',
wet1 double(16,4) NOT NULL DEFAULT '0.0000',
wet2 double(16,4) NOT NULL DEFAULT '0.0000',
wspd double(16,4) NOT NULL DEFAULT '0.0000',
wdir double(16,4) NOT NULL DEFAULT '0.0000',
sdwdir double(16,4) NOT NULL DEFAULT '0.0000',
rain05 double(16,4) NOT NULL DEFAULT '0.0000',
rain_day double(16,4) NOT NULL DEFAULT '0.0000',
rain05_day double(16,4) NOT NULL DEFAULT '0.0000',
sundur double(16,4) NOT NULL DEFAULT '0.0000',
f_airtemp varchar(10) DEFAULT NULL,
f_rh varchar(10) DEFAULT NULL,
f_gndtemp varchar(10) DEFAULT NULL,
f_rad varchar(10) DEFAULT NULL,
f_rain varchar(10) DEFAULT NULL,
f_wet1 varchar(10) DEFAULT NULL,
f_wet2 varchar(10) DEFAULT NULL,
f_wspd varchar(10) DEFAULT NULL,
f_wdir varchar(10) DEFAULT NULL,
f_sdwdir varchar(10) DEFAULT NULL,
f_rain05 varchar(10) DEFAULT NULL,
f_rain_day varchar(10) DEFAULT NULL,
f_rain05_day varchar(10) DEFAULT NULL,
f_sundur varchar(10) DEFAULT NULL,
PRIMARY KEY (stid,utime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

w_60_fixed 테이블에는 10만건 정도의 row가 있고요.
w_60_org 테이블에는 300만건 정도의 row가 있습니다.

===============================================
쿼리문
===============================================
select
count(1)
from
( select stid,utime,airtemp,rh,gndtemp,rad,rain,wet1,wet2,wspd,wdir,sdwdir,rain05,rain_day,rain05_day,sundur from w_60_fixed where utime BETWEEN 1230735600 AND 1230818400 ) a,
( select stid,utime,airtemp,rh,gndtemp,rad,rain,wet1,wet2,wspd,wdir,sdwdir,rain05,rain_day,rain05_day,sundur from w_60_org where utime BETWEEN 1230735600 AND 1230818400 ) b
where a.stid = b.stid
and a.utime = b.utime

위 쿼리를 던졌는데 10분이 다 되도록 결과가 안나오네요.

===============================================
위 쿼리에 대한 explain 결과
===============================================
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY 　 　 　 　 　 　 　 Impossible WHERE noticed after reading const tables
3 DERIVED w_60_org ALL 　 　 　 　 3340566 Using where
2 DERIVED w_60_fixed ALL 　 　 　 　 1 Using where

===============================================
show index from w_60_fixed 결과
===============================================
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
w_60_fixed 0 PRIMARY 1 stid A 0 　 　 　 BTREE 　
w_60_fixed 0 PRIMARY 2 utime A 0 　 　 　 BTREE 　

===============================================
show index from w_60_org 결과
===============================================
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
w_60_org 0 PRIMARY 1 stid A 18 　 　 　 BTREE 　
w_60_org 0 PRIMARY 2 utime A 3340566 　 　 　 BTREE 　

의심이 가는건 MySQL Administrator 에서 스키마를 보면 위 두 테이블의 Index length가 0 으로 나온다는 겁니다.
테이블 생성시 PRIMARY KEY 로 잡았는데 왜 Index length 가 0 일까요?

속도가 느린 정말 이유를 모르겠습니다.
많은 지도편달 부탁드립니다.

Forums: 
프로그래밍 QnA
rgbi3307의 이미지

글쓴이: rgbi3307 / 작성시간: 금, 2010/03/05 - 9:57오전

w_60_fixed 0 PRIMARY 1 stid A 0 --Cardinality
w_60_fixed 0 PRIMARY 2 utime A 0 --Cardinality

위에서 Cardinality 0 이라는 것은 unique한 값이 없다는 것입니다.
이것은 키의 역할을 못합니다. 차리리 table full scan 하는 것이 효율적입니다.

w_60_org 0 PRIMARY 1 stid A 18 　 --Cardinality
w_60_org 0 PRIMARY 2 utime A 3340566 --Cardinality

위에서 Cardinality 3340566 이라는 것은 unique한 값이 3백3십여만개 있다는 것입니다.
이것의 키의 역할을 할듯합니다.

아래와 같이 키에 해당하는 행의 개수를 직접 확인해 보세요~

--A 테이블:
select A.utime, count(*) from w_60_fixed A
A.utime BETWEEN 1230735600 AND 1230818400
group by A.utime;
 
--B 테이블:
select B.utime, count(*) from w_60_org B
B.utime BETWEEN 1230735600 AND 1230818400
group by B.utime;


아마도, A 테이블의 키는 모두 동일할듯 하므로, table full scan 하여 원하는 레코드를 가지고 오고,
이것을 B 테이블과 index join 하도록 아래와 같이 해봅니다.

테이블 A(w_60_fixed)와 테이블 B(w_60_org)의
primary key(stid, utime)가 서로 같은 것을 상호 조인하여,
키가 서로 같은행의 개수 count(*)를 출력하는 것이므로, 쿼리를 아래와 같이 작성 하는 것으로도 충분합니다.
(select 에서 여러개의 컬럼값을 가져오게 되면 I/O가 많이 발생하므로, 인덱스 만으로 스캔되도록 합니다.)

select 
	count(*)
from w_60_fixed A
	, w_60_org B
where 
    A.utime BETWEEN 1230735600 AND 1230818400 
	and A.stid = B.stid
	and A.utime = B.utime
;

또는, stid 조건이 있다면 넣어 주시면,
index skip 하지 않고, index range scan 할것 같습니다.

select 
	A.stid, A.utime, A.airtemp
from w_60_fixed A
	, w_60_org B
where 
	A.stid between 1234 and 5678
    and A.utime BETWEEN 1230735600 AND 1230818400 
	and A.stid = B.stid
	and A.utime = B.utime
;

From:
*알지비 (메일: rgbi3307(at)nate.com)
*학창시절 마이크로마우스를 만들었고, 10년동안 IT관련 개발자로 일하고 있음.
*틈틈히 커널연구회(http://www.kernel.bz/) 내용물들을 만들고 있음.
*((공부해서 남을 주려면 남보다 더많이 연구해야함.))

From:
*알지비 (메일: rgbi3307(at)nate.com)
*커널연구회(http://www.kernel.bz/) 내용물들을 만들고 있음.
*((공부해서 남을 주려면 남보다 더많이 연구해야함.))

koku_ma의 이미지

글쓴이: koku_ma / 작성시간: 월, 2010/03/08 - 10:16오전

와우~ 감사합니다.
정말 쿼리 속도가 빨라졌네요.

그런데 이유를 잘 모르겠습니다.

select
count(1)
from
( select stid,utime,airtemp,rh,gndtemp,rad,rain,wet1,wet2,wspd,wdir,sdwdir,rain05,rain_day,rain05_day,sundur from w_60_fixed where utime BETWEEN 1230735600 AND 1230818400 ) a,
( select stid,utime,airtemp,rh,gndtemp,rad,rain,wet1,wet2,wspd,wdir,sdwdir,rain05,rain_day,rain05_day,sundur from w_60_org where utime BETWEEN 1230735600 AND 1230818400 ) b
where a.stid = b.stid
and a.utime = b.utime

위 쿼리와 아래 쿼리가 무슨 차이가 있나요?
제 생각에는 같은 쿼리라고 생각이 되는데 잘 모르겠습니다.
혹시 이거에 대한 설명이 잘 되어있는 책이나 사이트 좀 알려주세요. ^^;

select
count(*)
from w_60_fixed A, w_60_org B
where A.utime BETWEEN 1230735600 AND 1230818400
and A.stid = B.stid
and A.utime = B.utime
;

dormael의 이미지

글쓴이: dormael / 작성시간: 월, 2010/03/08 - 10:28오전

위와 관련해서 직접 찾아본건 아니지만 구글에서 mysql 퍼포먼스나 최적화 관련해서 검색하면

mysql performance blog에 많은 정보가 있었습니다.

mysql 다음 버전에서는 어찌 될 지 모르지만 아직까지 다른 DBMS등에서 기본적으로 제공되는 최적화 기능들이 mysql에서는 퍼포먼스 문제를 일으키는 경우가 많이 있는걸로 압니다.

혹시 궁금해 하시는 부분이 올라와 있을지도 몰라 URL을 남겨 봅니다.

http://www.mysqlperformanceblog.com/

-- Signature --
青い空大好き。
蒼井ソラもっと好き。
파란 하늘 너무 좋아.
아오이 소라 더좋아.

댓글 달기

텍스트 포맷에 대한 자세한 정보

Filtered HTML

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

BBCode

  • 텍스트에 BBCode 태그를 사용할 수 있습니다. URL은 자동으로 링크 됩니다.
  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param>
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.

Textile

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • You can use Textile markup to format text.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Markdown

  • 다음 태그를 이용하여 소스 코드 구문 강조를 할 수 있습니다: <code>, <blockcode>, <apache>, <applescript>, <autoconf>, <awk>, <bash>, <c>, <cpp>, <css>, <diff>, <drupal5>, <drupal6>, <gdb>, <html>, <html5>, <java>, <javascript>, <ldif>, <lua>, <make>, <mysql>, <perl>, <perl6>, <php>, <pgsql>, <proftpd>, <python>, <reg>, <spec>, <ruby>. 지원하는 태그 형식: <foo>, [foo].
  • Quick Tips:
    • Two or more spaces at a line's end = Line break
    • Double returns = Paragraph
    • *Single asterisks* or _single underscores_ = Emphasis
    • **Double** or __double__ = Strong
    • This is [a link](http://the.link.example.com "The optional title text")
    For complete details on the Markdown syntax, see the Markdown documentation and Markdown Extra documentation for tables, footnotes, and more.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 사용할 수 있는 HTML 태그: <p><div><span><br><a><em><strong><del><ins><b><i><u><s><pre><code><cite><blockquote><ul><ol><li><dl><dt><dd><table><tr><td><th><thead><tbody><h1><h2><h3><h4><h5><h6><img><embed><object><param><hr>

Plain text

  • HTML 태그를 사용할 수 없습니다.
  • web 주소와/이메일 주소를 클릭할 수 있는 링크로 자동으로 바꿉니다.
  • 줄과 단락은 자동으로 분리됩니다.
댓글 첨부 파일
이 댓글에 이미지나 파일을 업로드 합니다.
파일 크기는 8 MB보다 작아야 합니다.
허용할 파일 형식: txt pdf doc xls gif jpg jpeg mp3 png rar zip.
CAPTCHA
이것은 자동으로 스팸을 올리는 것을 막기 위해서 제공됩니다.