2015. 10. 27. 18:14 IT/MySQL

Mysql Join Update.

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

update 를 할 때 다른 테이블의 데이터를 바탕으로 데이터 갱신을 시켜야 하는 경우가 있을 수 있습니다.

이 경우 몇 가지 방법이 있는데 하나는 where 절에 서브쿼리를 쓰는 방법입니다.

물론 단순한 경우라면 서브쿼리를 써도 문제없겠지만 복잡한 경우일 수록 서브쿼리는 시간이 오래 걸리게 됩니다.

 

다른 방법으로는 JOIN을 써서 update를 하는 방법입니다.

우리가 select 쿼리를 날릴 때와 마찬가지로 필요한 데이터를 여기저기서 긁어모은 다음 한방에 갱신을 할 수 있습니다.

syntax는 다음과 같습니다.

 

 

UPDATE [테이블명1] A INNER JOIN [테이블명2] B
ON A.[조인할 컬럼명] = B.[조인할 컬럼명]
SET [변경할 컬럼명] = 변경할값
( WHERE 절 )
 

 

'IT > MySQL' 카테고리의 다른 글

이전의 INSERT 작업으로부터 생성된 ID를 반환  (0) 2016.01.07
Mysql 전체 날짜 리스트 출력.  (0) 2015.11.25
MySQL 커버링 인덱스  (0) 2015.10.16
MySQL 설치 및 권한 관리.  (0) 2015.10.01
mysql JOIN DELETE  (0) 2015.09.11
Posted by 당양부부34

2015. 10. 16. 11:00 IT/MySQL

MySQL 커버링 인덱스

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

MySQL에서 커버링 인덱스로 쿼리 성능을 높여보자!!

안녕하세요.  오늘 짧지만 재미있는 내용을 하나 공유할까 합니다.

커버링 인덱스(Covering Index)라는 내용인데, 대용량 데이터 처리 시 적절하게 커버링 인덱스를 활용하여 쿼리를 작성하면 성능을 상당 부분 높일 수 있습니다.

커버링 인덱스란?

커버링 인덱스란 원하는 데이터를 인덱스에서만 추출할 수 있는 인덱스를 의미합니다. B-Tree 스캔만으로 원하는 데이터를 가져올 수 있으며, 칼럼을 읽기 위해 굳이 데이터 블록을 보지 않아도 됩니다.

인덱스는 행 전체 크기보다 훨씬 작으며, 인덱스 값에 따라 정렬이 되기 때문에 Sequential Read 접근할 수 있기 때문에, 커버링 인덱스를 사용하면 결과적으로 쿼리 성능을 비약적으로 올릴 수 있습니다.

백문이 불여일견! 아래 테스트를 보시죠.

테이블 생성

먼저 다음과 같이 테이블을 생성합니다.

create table usertest (
 userno int(11) not null auto_increment,
 userid varchar(20) not null default '',
 nickname varchar(20) not null default '',
 .. 중략 ..
 chgdate varchar(15) not null default '',
 primary key (userno),
 key chgdate (chgdate)
) engine=innodb;

약 1,000만 건 데이터를 무작위로 넣고 몇가지 테스트를 해봅니다.

커버링 인덱스(SELECT)

select chgdate , userno
from usertest
limit 100000, 100
************* 1. row *************
           id: 1
  select_type: SIMPLE
        table: usertest
         type: index
possible_keys: NULL
          key: CHGDATE
      key_len: 47
          ref: NULL
         rows: 9228802
        Extra: Using index
1 row in set (0.00 sec)

쿼리 실행 계획의 Extra 필드에 “Using Index” 결과를 볼 수 있는데, 이는 인덱스만으로 원하는 데이터 추출을 하였음을 알 수 있습니다.

이처럼 데이터 추출을 인덱스에서만 수행하는 것을 커버링 인덱스라고 합니다. 아시겠죠? ^^

그렇다면 일반 쿼리와 성능 테스트를 해볼까요?

커버링 인덱스(WHERE)

1) 일반 쿼리

select *
from usertest
where chgdate like '2010%'
limit 100000, 100

쿼리 수행 속도는 30.37초이며, 쿼리 실행 계획은 다음과 같습니다.

************* 1. row *************
           id: 1
  select_type: SIMPLE
        table: usertest
         type: range
possible_keys: CHGDATE
          key: CHGDATE
      key_len: 47
          ref: NULL
         rows: 4352950
        Extra: Using where

Extra 항목에서 “Using where” 내용은, Range 검색 이후 데이터는 직접 데이터 필드에 접근하여 추출한 것으로 보면 됩니다.

2) 커버링 인덱스 쿼리

select a.*
from (
      select userno
      from usertest
      where chgdate like '2012%'
      limit 100000, 100
) b join usertest a on b.userno = a.userno

쿼리 수행 시간은 0.16초이며 실행 계획은 다음과 같습니다.

************* 1. row *************
           id: 1
  select_type: PRIMARY
        table:
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100
        Extra:
************* 2. row *************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: b.userno
         rows: 1
        Extra:
************* 3. row *************
           id: 2
  select_type: DERIVED
        table: usertest
         type: range
possible_keys: CHGDATE
          key: CHGDATE
      key_len: 47
          ref: NULL
         rows: 4352950
        Extra: Using where; Using index

Extra 에서 “Using Index”를 확인할 수 있습니다.

그렇다면 30초 넘게 수행되는 쿼리가 0.16초로 단축됐습니다. 왜 이렇게 큰 차이가 발생했을까요?

첫 번째 쿼리는 Where에서 부분 처리된 결과 셋을 Limit 구문에서 일정 범위를 추출하고, 추출된 값을 데이터 블록에 접근하여 원하는 필드를 가져오기 때문에 수행 속도가 느립니다.

두 번째 쿼리에서도 동일하게 Where에서 부분 처리된 결과 셋이 Limit 구문에서 일정 범위 추출되나, 정작 필요한 값은 테이블의 Primary Key인 userno 값입니다. InnoDB에서 모든 인덱스 Value에는 Primary Key를 값으로 가지기 때문에, 결과적으로 인덱스 접근만으로 원하는 데이터를 가져올 수 있게 됩니다. 최종적으로 조회할 데이터 추출을 위해서 데이터 블록에 접근하는 건 수는 서브 쿼리 안에 있는 결과 갯수, 즉 100건이기 때문에 첫 번째 쿼리 대비 월등하게 좋은 성능이 나온 것입니다.

커버링 인덱스(ORDER BY)

커버링 인덱스를 잘 사용하면 Full Scan 또한 방지할 수 있습니다. 대부분 RDBMS에는 테이블에 대한 통계 정보가 있고, 통계 정보를 활용해서 쿼리 실행을 최적화 합니다.

다음 재미있는 테스트 결과를 보여드리겠습니다. 전체 테이블에서 chgdate 역순으로 400000번째 데이터부터 10 건만 가져오는 쿼리입니다.

1) 일반 쿼리

select *
from usertest
order by chgdate
limit 400000, 100
************* 1. row *************
           id: 1
  select_type: SIMPLE
        table: usertest
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9228802
        Extra: Using filesort
1 row in set (0.00 sec)

분명 인덱스가 있음에도, Full Scan 및 File Sorting이 발생합니다. 인덱스를 태웠을 때 인덱스 블록을 읽어들이면서 발생하는 비용보다 단순 Full Scan이 더 빠르다고 통계 정보로부터 판단했기 때문이죠. 인덱스도 데이터라는 것은 항상 기억하고 있어야 합니다^^

결과 시간은 책정 불가입니다. (안끝나요~!)

2) 커버링 인덱스 쿼리

위 결과와 다르게 커버링 인덱스는 조금 더 재미있는 결과를 보여줍니다.

select a.*
from (
      select userno
      from usertest
      order by chgdate
      limit 400000, 100
) b join usertest a on b.userno = a.userno
************* 1. row *************
           id: 1
  select_type: PRIMARY
        table:
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100
        Extra:
************* 2. row *************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: b.userno
         rows: 1
        Extra:
************* 3. row *************
           id: 2
  select_type: DERIVED
        table: usertest
         type: index
possible_keys: NULL
          key: CHGDATE
      key_len: 47
          ref: NULL
         rows: 400100
        Extra: Using index

File Sorting이 발생하지 않고 커버링 인덱스가 사용되었으며, 실행 시간 또한 0.24초로 빠르게 나왔습니다.^^

Conclusion

커버링 인덱스는 InnoDB와 같이 인덱스와 데이터 모두 메모리에 올라와 있는 경우에 유용하게 쓰일 수 있습니다. 물론 커버링 인덱스가 좋기는 하지만, 커버링 인덱스를 사용하기 위해 사용하지 않는 인덱스를 주구장창 만드는 것은 최대한 피해야 하겠죠^^

인덱스도 데이터입니다.

 


'IT > MySQL' 카테고리의 다른 글

Mysql 전체 날짜 리스트 출력.  (0) 2015.11.25
Mysql Join Update.  (0) 2015.10.27
MySQL 설치 및 권한 관리.  (0) 2015.10.01
mysql JOIN DELETE  (0) 2015.09.11
MySQL DB 생성 및 사용자 추가, 권한 부여  (0) 2015.09.08
Posted by 당양부부34
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

############### MySQL 설치 ##############

>> 업데이트 목록 보기

 yum list updates 


>> 업데이트 목록의 다운로드 및 업데이트 설치

 yum update ?y 


>> 설치된 rpm 패키지 목록 보기

 rpm -qa 

or

 rpm -qa | grep ^mysql-server

 yum list installed 


>> gcc 패키지 설치여부 확인

 rpm -qa | grep gcc 

 yum list installed gcc 


>> gcc 패키지 설치

 yum install gcc gcc-c++ 


>> gcc 패키지 업데이트

 yum update gcc gcc-c++ 


>> 패키지 이름으로 검색

 yum list 패키지명 

 yum list 정규식 

 yum list gcc 

 yum list gcc* 


>> MySQL 설치

 yum install mysql-server


>> 서비스 시작

 service mysqld start


>> root 비번 변경

 /usr/bin/mysqladmin -u root password 'P@ssw0rd'


>> 접속 테스트

 mysql -uroot -p


>> 부팅시 자동시작

 chkconfig --list mysqld

 mysqld         0:off 1:off 2:off 3:off 4:off 5:off 6:off


 chkconfig mysqld on


 chkconfig --list mysqld 

 mysqld         0:off 1:off 2:on 3:on 4:on 5:on 6:off


>> 방화벽 열기

 vi /etc/sysconfig/iptables


############### MySQL 권한 관리 ##############


mysql > show databases;     // DB 목록 확인

msyql > create database DB명;    // 데이터베이스 생성

 or

mysql > create database DB명 default character set utf8;


msyql > drop database DB명;       // 데이터베이스 삭제


mysql > create user 사용자ID;   // 사용자 추가

 or

mysql > create user userid@localhost identified by '비밀번호'; // 사용자(user)를 추가하면서 패스워드까지 설정


기존에 사용하던 계정에 외부 접근 권한을 부여하려면, Host를 '%' 로 하여 똑같은 계정을 추가한다

mysql > create user 'userid'@'%' identified by '비밀번호';  // '%' 의 의미는 외부에서의 접근을 허용


mysql > select * from user;    // 등록된 모든 사용자 ID 조회


mysql > delete from user where user = '사용자ID';      // 사용자 삭제


mysql > GRANT ALL PRIVILEGES ON DB명.테이블 TO 계정아이디@host IDENTIFIED BY '비밀번호';

 or

mysql> grant select, insert, update on test.* to user@localhost;   -- 패스워드는 변경없이 권한만 부여하는 경우


사용자에게 부여된 권한 확인

mysql > SHOW GRANTS FOR test@localhost;  -- userid 와 host명까지 붙여서 검색해야 함

mysql > SHOW GRANTS FOR test@'%';

mysql > SHOW GRANTS FOR test@'200.100.100.50';



사용자에게 데이터베이스 사용권한 제거

revoke all on DB명.테이블명 from 사용자ID;     // 모든 권한을 삭제


mysql > flush privileges;     // 변경된 내용을 메모리에 반영(권한 적용)


'IT > MySQL' 카테고리의 다른 글

Mysql Join Update.  (0) 2015.10.27
MySQL 커버링 인덱스  (0) 2015.10.16
mysql JOIN DELETE  (0) 2015.09.11
MySQL DB 생성 및 사용자 추가, 권한 부여  (0) 2015.09.08
Mysql Join 속도 측정 및 개선  (0) 2015.07.22
Posted by 당양부부34

2015. 9. 11. 16:34 IT/MySQL

mysql JOIN DELETE

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

JOIN DELETE (Multiple-table Delete)

두개의 테이블을 조인하여 UPDATE를 실행하는 것(JOIN UPDATE)과 같이,
두개의 테이블을 조인하여 그 결과 레코드를 삭제하는 것도 가능하다.

이를 JOIN DELETE 또는 Multiple-Table DELETE라고 하는데,
JOIN DELETE는 아래 두 가지 문법으로 작성할 수 있다.

  • DELETE와 FROM 절 사이에 삭제할 테이블 명시

    DELETE
     t1t2 
    FROM test1 t1 INNER JOIN test2 t2 INNER JOIN test3 t3
    WHERE t1.id=t2.id 
      AND t2.id=t3.id;

  • FROM과 USING 절 사이에 삭제할 테이블 명시
    DELETE 
    FROM t1t2 
      USING test1 t1 INNER JOIN test2 t2 INNER JOIN test3 t3
    WHERE t1.id=t2.id 
      AND t2.id=t3.id;

JOIN DELETE에서도 주의해야 할 사항이 있는데,
  • 조인 결과 레코드가 삭제되는 테이블이 어느 테이블인가 ?.
    • 첫번째 문법에서는 DELETE 키워드와 FROM 절 사이에 명시된 테이블의 레코드만 삭제한다.
    • 두번째 문법에서는 FROM 절과 USING 절 사이에 명시된 테이블의 레코드만 삭제한다.
    • 위의 두 예제에서는 t1(test1) 테이블과 t2(test2) 테이블의 레코드만 삭제하게 된다. t3(test3)의 레코드는 조인에만 참여하고 삭제되지는 않는다.
  • 테이블의 별명(Alias)는 어디에서 정의해야 하는가 ?.
    • 첫번째 문법에서는 FROM 절 이하에서 테이블의 별명(Alias)를 지정하며, 이 이외의 위치에서는 Alias를 지정할 수 없고, FROM 절에서 정의된 Alias를 사용만 해야 한다.
    • 두번째 문법에서는 USING 절 이하에서만 테이블의 별명(Alias)를 정의해야 한다.




'IT > MySQL' 카테고리의 다른 글

MySQL 커버링 인덱스  (0) 2015.10.16
MySQL 설치 및 권한 관리.  (0) 2015.10.01
MySQL DB 생성 및 사용자 추가, 권한 부여  (0) 2015.09.08
Mysql Join 속도 측정 및 개선  (0) 2015.07.22
mysql table 권한 관리  (0) 2015.07.09
Posted by 당양부부34
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

서버 관리를 하다보면 mysql 사용자 계정을 추가해 줄때가 있다.


MySQL 접속 및 데이터 베이스 추가

# mysql -u root -p

사용자 계정을 추가하기 전에 먼저 현재 생성된 사용자 계정을 확인한다.

mysql > use mysql;    // mysql database 선택
mysql > select host, user, password from user;    // user 테이블 살펴보기


사용자 추가 (권한추가)
mysql > create user 사용자ID;   // 사용자 추가

mysql > create user userid@localhost identified by '비밀번호';

// 사용자(user)를 추가하면서 패스워드까지 설정

기존에 사용하던 계정에 외부 접근 권한을 부여하려면, Host를 '%' 로 하여 똑같은 계정을 추가한다

mysql > create user 'userid'@'%' identified by '비밀번호';  // '%' 의 의미는 외부에서의 접근을 허용



다른 방법으로는

mysql > USE mysql;     // mysql database 선택
mysql > INSERT INTO user (Host, User, Password) VALUES ('localhost', '계정아이디', password('비밀번호'));
mysql > INSERT INTO user (Host, User, Password) VALUES ('%', '계정아이디', password('비밀번호'));
mysql > FLUSH privileges;



mysql > drop user '사용자ID'@localhost;    // 사용자 삭제



mysql > select * from user;    // 등록된 모든 사용자 ID 조회
mysql > delete from user where user = '사용자ID';      // 사용자 삭제


계정이 생성되었다면, 그 계정이 접근할 수 있는 데이터베이스를 생성하고 권한을 부여해야 한다

데이터베이스 생성

mysql > show databases;     // DB 목록 확인



msyql > create database DB명;    // 데이터베이스 생성



msyql > create schema DB명 default character set utf8; -- 둘중에 하나를 입력하면 DB 생성됨

mysql > create database DB명 default character set utf8;

// default character set을 지정하지 않으면 한글이 깨져서 나오므로 주의해야 한다.


msyql > drop database DB명;       // 데이터베이스 삭제



사용자에게 데이터베이스 사용권한 부여

MySQL은 사용자 이름, 비밀번호, 접속 호스트로 여러분을 인증한다. 

MySQL은 로그인을 시도하는 위치가 어디인가 하는 것도 인증의 일부로 간주한다.

MySQL 에서 사용자 계정을 추가하고 권한을 추가하거나 제거하는 데 GRANT 와 REVOKE 명령을 사용하기를 권장한다. 

사용자에게 허가된 것을 확인하려면 SHOW GRANTS 를 사용한다.


IDENTIFIED BY '비밀번호'; 는 권한부여를 하면서 비밀번호까지 변경하고자 하는 경우

이므로 비밀번호는 변경하지 않으려면 이 부분을 빼면 된다.


mysql > GRANT ALL PRIVILEGES ON DB명.테이블 TO 계정아이디@host IDENTIFIED BY '비밀번호';

// 계정이 이미 존재 하는데 'identified by '비밀번호' 부분을 추가하면 비밀번호가 변경된다

mysql> GRANT ALL privileges ON DB명.* TO 계정아이디@locahost IDENTIFIED BY '비밀번호';
mysql> GRANT ALL privileges ON DB명.* TO 계정아이디@'%' IDENTIFIED BY '비밀번호';


mysql > grant all privileges on DB명.* to userid@'%' identified by '비밀번호' ;  //모든 원격지에서 접속 권한 추가 

host에 '200.100.%' 로 하면 IP주소가 200.100.X.X 로 시작되는 모든 IP에서 원격 접속을 허용한다는 의미

host에 '200.100.100.50' 으로 하면 IP주소가 200.100.100.50 인 곳에서만 원격 접속을 허용한다는 의미


mysql > grant all privileges on test.* to userid@localhost identified by '비밀번호';

// user 에게 test 데이터베이스 모든 테이블에 대한 권한 부여 


mysql> grant select, insert, update on test.* to user@localhost identified by '비밀번호';

// user 에게 test 데이터베이스 모든 테이블에 select, insert, update 권한 부여


mysql> grant select, insert, update on test.* to user@localhost;   -- 패스워드는 변경없이 권한만 부여하는 경우

// user 에게 test 데이터베이스 모든 테이블에 select, insert, update 권한 부여


mysql> grant all privileges on *.* to user@localhost identified by '비밀번호' with grant option;

// user 에게 모든 데이터베이스 모든 테이블에 권한 부여

// 전역 권한은 모두 광범위한 보안문제가 수반되므로 권한을 허용하는 경우 신중해야 함


mysql > flush privileges;     // 변경된 내용을 메모리에 반영(권한 적용)



사용자에게 부여된 권한 확인

mysql > SHOW GRANTS FOR test@localhost;  -- userid 와 host명까지 붙여서 검색해야 함

mysql > SHOW GRANTS FOR test@'%';

mysql > SHOW GRANTS FOR test@'200.100.100.50';


사용자에게 데이터베이스 사용권한 제거

revoke all on DB명.테이블명 from 사용자ID;     // 모든 권한을 삭제


이제 다시 show grants 로 정보를 확인해보면

user 정보는 남아 있는데, 권한부여 정보는 삭제되고 없는 것이 보인다.


사용자 계정마저 삭제하고 권한 설정 정보를 확인하려고 하면 Error 가 발생하는 걸 확인할 수 있다.


사용자 계정 삭제

mysql > drop user userid@'%';

mysql > drop user userid@localhost;


위에 있는 것과 비교해서 host 가 % 로 된 것이 삭제되었다.



'IT > MySQL' 카테고리의 다른 글

MySQL 설치 및 권한 관리.  (0) 2015.10.01
mysql JOIN DELETE  (0) 2015.09.11
Mysql Join 속도 측정 및 개선  (0) 2015.07.22
mysql table 권한 관리  (0) 2015.07.09
flush privileges  (0) 2015.07.09
Posted by 당양부부34
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

내가 만든 웹페이지중에 엄청나게 속도가 느린 곳이 있었다.

분명 DB에서 SELECT하는데 문제가 있을 것이었다.


문제의 그 쿼리는 3개의 테이블을 JOIN해서 SELECT 하는데, 매우 느렸다. 


아래와 같은 방법을 써서 JOIN 하는 쿼리의 속도를 개선시켰다.






1. 쿼리의 성능을 측정(EXPLAIN)


그 쿼리가 어떻게 돌아가는지 보기 위해 EXPLAIN 을 써보았다.


EXPLAIN

SELECT * FROM 테이블명 WHERE 조건 



다음과 같은 쿼리를 실행하면, 

아래와 같은 항목이 나타난다. 


 id

select type 

table 

type 

possible_keys

key 

key_len 

ref 

extra 




각 항목에 대한 자세한 정보는

http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21444&cat1=7&cat2=217&cat3=227&lang=k 

이곳을 참조 하면 된다.



이중에 유심히 봐야할 정보는 type 이다.


내 경우에는 type이 all 로 나타나고 있었다. 풀스캔 하고있었단 소리..

또한, extra에 using temporary 라든가 여러가지가 나타나면 속도는 굉장히 느려진다.





2. JOIN 쿼리의 최적화 



아래 예제의 테이블을 조인한다고 가정해보자.


A테이블 (이름이 명시된 테이블) 


A_ID(PK) 

NAME 

 1 사자 

 2 고양이 

 3

 신매력 


B테이블 (스킬이 명시된 테이블)


B_ID(PK) 

SKILL 

 1

 얼음꽃 

 2 흐규흐규 


C테이블 (A와 B를 참조하는 관계테이블)


INDEX_01 : A_ID

INDEX_02 : B_ID


A_ID(FK) 

B_ID(FK) 

 1

 1

 2



여기서, A B C 테이블을 조인해서 모든 컬럼을 가져와야한다고 하면 어떤 순서로 조인해야할까?



먼저 A테이블과 C를 조인한다고 하면..


SELECT * FROM A 

LEFT JOIN C

ON A.A_ID = C.A_ID   가 될텐데,


이렇게 하면 ALL 이 될 것이다.

왜냐>??


A테이블은 C에 없는 A_ID 행들을 쫙 가지고있다.

A기준으로 찾는다고 생각을 해보면, A테이블을 다 뒤져보게 되는 것이다.


그래서 반대로 조회를 해야한다.



SELECT * FROM C 

LEFT JOIN A

ON C.A_ID = A.A_ID


더구나 C 테이블의 C.A_ID는 인덱스까지 잡혀있다.

EXPLAIN으로 나온 type중에 index로 나와도 ALL 다음으로 최악이지만

ALL보다는 훨씬 빠르다.. (using temporary 등등 그런게 없어진다)



그래서 세개를 다 합친 쿼리는 대충 아래와 같이 된다.


SELECT * FROM C

LEFT JOIN A

ON C.A_ID = A.A_ID

LEFT JOIN B

ON C.B_ID = B.B_ID 




3. STRAIGHT_JOIN



EXPLAIN으로 봤더니 또 type이 ALL이었다...



그 이유는 MySql 에서 내부적으로 join 순서를 바꿔서 실행하기 때문이다.(mysql 속의 통계 디비를 참조하여 실행된다고 함)


그래서.. 저 명시된 순서로 조인을 하기 위해서는 STRAIGHT_JOIN을 사용해야한다.



SELECT STRAIGHT_JOIN * FROM C

LEFT JOIN A

ON C.A_ID = A.A_ID

LEFT JOIN B

ON C.B_ID = B.B_ID  




* 참고


나의 경우는 저렇게 하고도 또 ALL이 두번 나타났는데, 

그 이유는...


1. ORDER BY 없이 LIMIT 를 거는 행위를 해서 ㅠㅠ

(어느 컬럼 기준인지 모르므로 풀스캔을 하게 되었다)


2. ORDER BY 하는 컬럼의 기준 때문이었다. 

저 위에 예제 테이블에서 A 테이블이나 B테이블 기준으로 ORDER BY를 하면 

모든 곳이 탐색될 것이다..



'IT > MySQL' 카테고리의 다른 글

mysql JOIN DELETE  (0) 2015.09.11
MySQL DB 생성 및 사용자 추가, 권한 부여  (0) 2015.09.08
mysql table 권한 관리  (0) 2015.07.09
flush privileges  (0) 2015.07.09
Mysql event schedule 확인  (0) 2015.06.25
Posted by 당양부부34
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

1. user를 등록하는데 모든권한을 N 로 해서 추가합니다.


mysql> select * from user;
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host          | User     | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

      |          |            |             |              |             0 |           0 |               0 |
| localhost     | freekang | password| N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                |          |            |             |              |             0 |           0 |               0 |
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+


2. 해당 유저의 권한설정이 제대로 되어있는지 확인합니다.


[root@test229 root]# mysql -ufreekang -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.9-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 
mysql> 
mysql> show databases;
Empty set (0.00 sec)

mysql> quit
Bye


3. root로 로그인하여 해당 테이블에 대한 권한을 설정합니다.


[root@test229 root]# mysql -uroot -p    
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.1.9-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| MIDAS    |
| myphp    |
| mysql    |
+----------+
3 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into tables_priv values('%','mysql','freekang','user','','','Select','Select');

 

--> user table에 대해서 select권한만 주도록 설정했습니다.


Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye


4. 해당 유저로 로긴하여 권한부여가 제대로 됐는지 확인합니다.


[root@test229 root]# mysql -ufreekang -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.1.9-Max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql      |
+----------+

 

-> 2번에서 아무것도 보이지 않던것에 비하여 현재는 mysql DB가 보입니다.


1 row in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| user                |
+-----------------+
1 row in set (0.00 sec)

 

-> mysql의 테이블중 user테이블만 보이고 있습니다.

 

mysql> select * from user;
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host          | User     | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections |
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+

| localhost     | freekang | *B648CC8E9ED597C6394246A09FCD6C8DCF9A2E34 | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                |          |            |             |              |             0 |           0 |               0 |
+---------------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+


-> select query에 대해서 정상적으로 실행되고 있습니다.

 

mysql> INSERT INTO `user` VALUES ('localhost','seokjoo','ajf;dljsdsfl;jowiurel;asjdlfjlas;djfl;adsjf;lasdjfasl;fj

34','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0);
ERROR 1142 (42000): INSERT command denied to user 'freekang'@'localhost' for table 'user'
mysql> delete from user where User="kkang";                                                 
ERROR 1142 (42000): DELETE command denied to user 'freekang'@'localhost' for table 'user'
mysql>


-> 권한을 주지않은 insert와 delete에 대해서는 command denied 라는 말이 나오는걸 보니 적용된거 같네요.^^

'IT > MySQL' 카테고리의 다른 글

MySQL DB 생성 및 사용자 추가, 권한 부여  (0) 2015.09.08
Mysql Join 속도 측정 및 개선  (0) 2015.07.22
flush privileges  (0) 2015.07.09
Mysql event schedule 확인  (0) 2015.06.25
mysql event 조회 확인  (0) 2015.06.12
Posted by 당양부부34

2015. 7. 9. 13:14 IT/MySQL

flush privileges

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

MySQL flush privileges 명령어


INSERT나 UPDATE, DELETE문을 이용해서 MySQL의 사용자를 추가,삭제하거나, 사용자 권한 등을 변경하였을 때, MySQL에 변경사항을 적용하기 위해서 사용하는 명령어가 flush privileges 입니다.


이 flush privileges 는 정확히 말하면 grant 테이블을 reload 함으로 변경사항을 바로 적용해주는 명령어인데, INSERT, UPDATE와 같은 SQL문이 아닌 grant 명령어를 사용해서 사용자를 추가하거나 권한등을 변경하였다면 굳이 실행할 필요가 없습니다.


flush privileges 명령어처럼 grant 테이블을 reload 하는 명령어로 mysqladmin reload 나 

mysqlamdin flush-privileges 명령어가 있는데 차이라면 "mysqladmin 명령어"이므로 쉘 프롬프트에서 사용하여야 한다는 것과 비밀번호가 있을 경우 마지막 예와 같이 조금 번거로울 수 있다는 것입니다.

'IT > MySQL' 카테고리의 다른 글

Mysql Join 속도 측정 및 개선  (0) 2015.07.22
mysql table 권한 관리  (0) 2015.07.09
Mysql event schedule 확인  (0) 2015.06.25
mysql event 조회 확인  (0) 2015.06.12
MYSQL Data type  (0) 2015.06.01
Posted by 당양부부34
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

-- 이벤트 목록 보기

 

SELECT * FROM information_schema.EVENTS

 

또는

 

SHOW EVENTS ;

 

 

-- 등록 되어 있는 이벤트 내용 보기 


SHOW CREATE EVENT `이벤트명` ;

 

 

-- 등록되어 있는 이벤트 수정 하기

 

ALTER EVENT `이벤트명`

ON SCHEDULE EVERY 1 MONTH STARTS '2014-05-27 01:00:00' ;

 

 

-- 등록되어 있는 이벤트 삭제 하기 

DROP event `이벤트명` ;

'IT > MySQL' 카테고리의 다른 글

mysql table 권한 관리  (0) 2015.07.09
flush privileges  (0) 2015.07.09
mysql event 조회 확인  (0) 2015.06.12
MYSQL Data type  (0) 2015.06.01
MySql auto_increment 값 알아오기.  (0) 2015.04.27
Posted by 당양부부34

2015. 6. 12. 13:09 IT/MySQL

mysql event 조회 확인

336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

-- 이벤트 목록 보기

 

SELECT * FROM information_schema.EVENTS

 

또는

 

SHOW EVENTS ;

 

 

-- 등록 되어 있는 이벤트 내용 보기 

SHOW CREATE EVENT `이벤트명` ;

 

 

-- 등록되어 있는 이벤트 수정 하기

 

ALTER EVENT `이벤트명`

ON SCHEDULE EVERY 1 MONTH STARTS '2014-05-27 01:00:00' ;

 

 

-- 등록되어 있는 이벤트 삭제 하기 

DROP event `이벤트명` ;


'IT > MySQL' 카테고리의 다른 글

flush privileges  (0) 2015.07.09
Mysql event schedule 확인  (0) 2015.06.25
MYSQL Data type  (0) 2015.06.01
MySql auto_increment 값 알아오기.  (0) 2015.04.27
Mysql 날짜 함수  (0) 2015.04.24
Posted by 당양부부34
이전버튼 1 2 3 4 이전버튼

블로그 이미지
주요 토렌트를 블로깅하고 있습니다. 토렌트 순위 등은 다른 사이트를 찾아보세요. 주요 웹툰 순위도 게재했어요 경제를 좋아하는 일산의 행복한 프로그래머입니다.
당양부부34
Yesterday
Today
Total

달력

 « |  » 2024.3
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

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함