Java World

Java Korean

Category Archives: Databases

데이터베이스 위키 – http://ko.wikipedia.org/wiki/데이터베이스

[MongoDB] 몽고디비 스토리지 엔진 종류

Posted on by Posted in Mongo DB
1. 엔진종류
   – Memory Mapping Storage-Engine
   – wiredTiger Storage-Engine       # 3.0버전 이상부터는 디폴트
   – In-Memory Storage-Engine        # 3.2버전 이후부터 사용가능
1-1. MMAP
   – File Base 기반
   – BigData 빠른 쓰기와 읽기 중심의 데이터 처리에 적합
   – 서버 장애 발생 시 빠른 복구가 보장됨
   – Point in time Recovery가 가능한 Dignostic 기능을 제공함
   – Single CPU 환경에서 구현 가능하며 충분한 시스템 메모리와 빠른 성능이 기대되는 SSD장치가 요구됨
   – 옵션 = –storageEngine mmapv1
1-2. wiredTiger
   – File Base 기반
   – 압축과 암호화 기능 제공
   – 다중 트랜잭션 중심의 데이터 처리에 적합
   – Point in time Recovery가 가능한 Dignostic 기능을 제공함
   – 다중 CPU 환경이 요구되며, MMAP 엔진에 비해 적은 시스템 메모리와 디스크 저장 장치로도 구현 가능.
   – 옵션 = –storageEngine wiredTiger
1-3. In-Memory
   – Pure Memory 기반
   – 빠른 연산 처리 중심의 데이터 처리에 적합.
   – 충분한 메모리 영역이 요구됨.
   – 옵션 = –storageEngine inMemory

MySQL Federated 알아보기

Posted on by Posted in MySQL

MySQL Federated

위키백과, 우리 모두의 백과사전.

MySQL 관계형 데이터베이스 관리 시스템용 MySQL Federated 저장 엔진은 유저가 외부(또는 원격) 테이블을 로컬로 보여주는 테이블을 생성할 수 있게 해주는 저장 엔진이다. 이것은 MySQL 클라이언트 라이브러리 API를 데이터 통로로 이용하여, 원격의 데이터 소스를 동일하게 다른 저장 엔진으로 취급하여 로컬 데이터 자료를 MYD 파일(MyISAM), 메모리(클러스터, 힙) 또는 테이블스페이스(InnoDB)에 상관없이 취급한다. 그곳에 규정된 각 Federated 테이블은 하나의 .frm (데이터 소스 URL과 같은 정보를 포함한 데이터 정의 파일)이다. 실제 데이터는 로컬 또는 원격의 MySQL 인스턴스에 존재한다.

Federated 테이블을 생성하기 위해서는 “CONNECTION” 문자열에서 URL을 지정해주어야 한다 :

create table t1 (
 a int,
 b varchar(32))
ENGINE=FEDERATED CONNECTION='mysql://user@hostname/test/t1'

연결 URL 포맷은 다음과 같이 되어야 한다 :

scheme://user:pass@host:port/schema/tablename

Federated 테이블을 만들면, 유저는 원격 데이터 소스가 실제로 존재해야 하며, 그렇지 않으면 에러가 발생할 것이다.

MySQL Federated 저장 엔진은 패트릭 갈브래이드(Patrick Galbraith)와 브라이언 애커(Brian Aker)가 만들었고, 현재는 패트릭 갤브래이드와 앤토니 커피스에 의해 유지되고 있다. 최초로 소개된 것은 2005년 MySQL 5.0 때 처음 소개되었다.

mac에서 mysql삭제하기

Posted on by Posted in MySQL

1. sudo rm /usr/local/mysql
2. sudo rm -rf /usr/local/mysql*
3. sudo rm -rf /Library/StartupItems/MySQLCOM
4. sudo rm -rf /Library/PreferencePanes/My*
5. vim /etc/hostconfig

 --> MYSQLCOM=_YES_ 라인 삭제

6. rm -rf ~/Library/PreferencePanes/My*
7. sudo rm -rf /Library/Receipts/mysql*
8. sudo rm -rf /Library/Receipts/MySQL*
9. sudo rm -rf /var/db/receipts/com.mysql.*

mysql 실행계획 Explain

Posted on by Posted in MySQL

Explain 정보보는법

 

인덱스가 적절히 사용되고 있는지 검토
나열된 순서는 MYSQL 이 쿼리처리에 사용하는 순서대로 출력

EXPLAIN 의 각 행 설명

1. id : SELECT 번호, 쿼리내의 SELECT 의 구분번호

2. select_type : SELECT 의 타입

  • SIMPLE: 단순 SELECT (UNION 이나 서브쿼리를 사용하지 않음)
  • PRIMARY: 가장 외곽의 SELECT
  • UNION: UNION 에서의 두번째 혹은 나중에 따라오는 SELECT
  • DEPENDENT UNION: UNION 에서의 두번째 혹은 나중에 따라오는 SELECT, 외곽쿼리에 의존적
  • UNION RESULT: UNION 의 결과물
  • SUBQUERY: 서브쿼리의 첫번째 SELECT
  • DEPENDENT SUBQUERY: 서브쿼리의 첫번째 SELECT, 외곽쿼리에 의존적
  • DERIVED: SELECT 로 추출된 테이블 (FROM 절 내부의 서브쿼리)

3. table : table명

4. type : 조인타입, 우수한 순서대로… 뒤로갈수록 나쁜 조인형태

  • system
    테이블에 단 하나의 행만 존재(시스템 테이블). const join 의 특수한 경우
  • const
    많아야 하나의 매치되는 행만 존재할 때
    PRIMARY KEY 나 UNIQUE index 를 상수와 비교할 때
    각 컬럼값은 나머지 연산에서 상수로 간주, 처음 한번만 읽어들이면 되므로 매우 빠름
  • eq_ref
    조인수행을 위해 각 테이블에서 하나씩의 행만이 읽히는 경우
    조인연산에 PRIMARY KEY 나 UNIQUE index 인덱스가 사용되는 경우
    인덱스된 컬럼이 = 연산에 사용되는 경우
  • ref
    이전 테이블과의 조인에 사용될 매치되는 인덱스의 모든행이 이 테이블에서 읽혀질 때
    leftmost prefix 키만을 사용하거나 사용된 키가 PRIMARY KEY 나 UNIQUE 가 아닐때
    (즉 키값으로 단일행을 추출할수 없을때)
    사용된 키가 적은수의 행과 매치되면 이것은 적절한 조인 타입
    ref 는 인덱스된 컬럼과 = 연산에서 사용됨
  • ref_or_null
    ref 와 같지만 NULL 값을 포함하는 행에대한 검색이 수반될 때
    서브쿼리 처리에서 대개 사용됨
  • index_merge
    인덱스 병합 최적화가 적용되는 조인 타입
    이 경우, key 컬럼은 사용된 인덱스의 리스트를 나타내며
    key_len 컬럼은 사용된 인덱스중 가장 긴 key 명을 나타냄
  • unique_subquery
    몇몇 IN 서브쿼리 처리에서 ref 타입대신 사용됨
    unique_subquery 는 성능향상을 위해 서브쿼리를 단순 index 검색 함수로 대체함
  • index_subquery
    unique_subquery 와 마찬가지로 IN 서브쿼리를 대체
    단, 서브쿼리에서 non-unique 인덱스가 사용될때 동작 함
  • range
    인덱스를 사용하여 주어진 범위 내의 행들만 추출
    key 컬럼: 사용된 인덱스
    key_len: 사용된 가장 긴 key 부분
    ref 컬럼: 이 타입의 조인에서 NULL
    키 컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN 또는 IN 연산에 사용될때 적용됨
  • index
    인덱스가 스캔된다는걸 제외하면 ALL 과 같음
    일반적으로 인덱스 파일이 데이타파일보다 작기 때문에 ALL 보다는 빠름
    MySQL 은 쿼리에서 단일 인덱스의 일부분인 컬럼을 사용할때 이 조인타입을 적용함
  • ALL
    이전 테이블과의 조인을 위해 풀스캔
    (조인에 쓰인) 첫번째 테이블이 고정이 아니라면 비효율적
    대부분의 경우에 아주 느린 성능
정리:
system – const – eq_ref – ref – ref_or_null – index_mergy – unique_subquery – index_subquery – range – index – ALL

5. possible_keys : MySQL 이 해당 테이블의 검색에 사용할수 있는 인덱스들
possible_keys 에 나타난 인덱스들이 결과에 나타난 테이블 순서에서 실제 사용할 수 없을수도 있음

6. key :  MySQL 이 실제 사용한 key(index)

7. key_len : MySQL 이 사용한 인덱스의 길이, key 컬럼값이 NULL 이면 이값도 NULL
key_len 값으로 MySQL 이 실제 복수컬럼 키중 얼마나 많은 부분을 사용할 것인지 알 수 있음

8. ref : 행을 추출하는데 키와 함께 사용된 컬럼이나 상수값

9. rows : 쿼리 수행에서 MySQL 이 예상하는 검색해야할 행수

10. Extra : MySQL 이 쿼리를 해석한 추가적인 정보를 나타냄

  • Distinct: MySQL 이 매치되는 첫행을 찾는 즉시 검색을 중단한다는 의미
  • Not exists: MySQL 이 LEFT JOIN 을 수행함에 매치되는 한 행을 찾으면 더이상 매치되는 행을 검색x
  • range checked for each record (index map: #): MySQL 이 사용할 좋은 인덱스가 없음 의미
  • Using filesort: MySQL 이 정렬을 위해 추가적인 과정을 필요로 함
  • Using index: 컬럼정보가 실제 테이블이 아닌 인덱스트리에서 추출, 쿼리에서 단일 인덱스된 컬럼들만을 사용하는 경우
  • Using temporary: MySQL 이 결과의 재사용을 위해 임시테이블을 사용, 쿼리 내에 GROUP BY 와 ORDER BY 절이 각기 다른 컬럼을 사용할때 발생
  • Using where: WHERE 절이 다음 조인에 사용될 행이나 클라이언트에게 돌려질 행을 제한하는 경우 테이블의 모든 행을 검사할 의도가 아니면 ALL 이나 index 라면 쿼리사용이 잘못된 것임
  • Using sort_union(…) , Using union(…) , Using intersect(…)
  • Using index for group-by: Using index 와 접근방식이 같으며, 추가적인 디스크 접근 없이 GROUP BY 나 DICTINCT 쿼리에 사용된 모든 컬럼에 대한 인덱스를 찾았음을 의미

SQL 옵티마이져

Posted on by Posted in Databases

OPTIMIZER_MODE 의 개념#

초기화 파라미터인 OPTIMIZER_MODE는 오라클 인스턴스가 최적의 Access 경로를 선택하는 기본 설정이 된다. 즉 옵티마이저가 어떠한 기준으로 최적화된 Access 경로를 추출 하는지 결정하는 방법에 대해 제시 한다고 볼 수 있다.

옵티마이저(Optimizer) : #

  1. Rule-based – SQL에 대한 Execution Plan이 여러 개 있다고 할 때, 가장 낮은 순위의 실행계획을 항상 사용
  2. Cost-based – 가장 cost가 적은 실행계획을 선택

실행환경#

  1. CHOOSE : Optimizer가 CBO와 RBO 두 가지가 모두 선택 가능할 경우에 사용
  2. ALL_ROWS : CBO 환경경에서 전체적인 처리, 즉 Batch 환경에서 주로 사용
  3. FIRST_ROWS : CBO 환경에서 빠른 응답시간을 얻고자 할 때, 즉 ON-LINE 환경에서 주로 사용
  4. RULE : RBO 환경에서 사용
MODE 장점 단점
RULE Analyze작업이 불필요,
일정한 응답속도를 기대		 전문가에게 의존도가 매우 높음,
옵티마이저 대부분의 기능을 사용 못함
CHOOSE 옵티마이저 기능을 최대한 활용 주기적인 Analyze작업이 필요,
비효율적인 실행계획수립 발생
FIRST_ROWS Nested Loop 위주의 실행계획수립 일부 Hash Join으로 바꾸는 작업 필요
ALL_ROWS Hash Join 위주의 실행계획 수립 일부를 Nested Loop로 바꾸는 작업 필요

Rule-Based Optimizer (규칙기반 최적화)#

  1. RBO는 규칙에 의해서 실행되므로 예측가능하고 통제 가능하므로 대부분의 DBA, 프로그래머들이 선호
  2. RBO는 구문 중심으로, SQL 구문을 변경할 경우 성능이 향상될 수 있음(통계정보, 비용계산 비사용). 오직 규칙에 의해서만 실행계획을 결정
  3. 테이블의 row수가 작거나 query가 일반적으로 row의 대부분을 반활할 때에도 가능하면, 항상 인덱스를 사용함으로써 비효율적이 될 수도 있음. 테이블명에 대한 파싱은 우에서 좌로 진행되기 때문에 최후 기술된 테이블이 먼저 처리

RBO 순위표(규칙)#

  1. 문장의 WHERE절에 ROWID나 Oracle Precompiler에 지원되는 확장된 SQL 구문 구조의 CURRENT OF CURSOR에 의해 지정된 ROW들이 식별될 수 있을 경우에만 사용이 가능
  2. 문장의 WHERE 절에 join이 단지 하나의 row를 return하는 것을 보증하는 조건절을 가지고 있어야 함. 이러한 조건절은 unique하거나 primary key인 칼럼의 ‘=’ 조건절과 유사함. 이러한 조건들은 반드시 AND 연산자를 통해 합쳐져야 하고, 이러한 문장을 수행하기 위해 Oracle은 nested loops 연산을 수행
  3. WHERE 절이 ‘=’조건절에 HASH CLUSTER KEY의 모든 칼럼을 사용하여야 하며, 합성된 CLUSTER KEYS인 경우에는 ‘=’ 조건절이 반드시 AND 연산자에 의해 연결되어야 함. 문장은 칼럼이 HASH CLUSTER KEY를 만들어 내듯이 UNIQUE나 PRIMARY KEY를 만들어내기 때문에 단지 하나의 ROW만을 RETURN해야 함
  4. WHERE절이 ‘=’ 조건절에서 칼럼들의 unique나 primary key를 사용할 경우에 사용
  5. 이 접근 경로는 두 Join 테이블이 같은 cluster에 저장되고, 문장의 Where절에 한 테이블의 각 칼럼에 대해 대응하는 테이블의 칼럼을 같게 하는 조건절을 포함하는 경우 사용함.
  6. WHERE절에 hash cluster key인 칼럼의 ‘=’조건절이 있을 경우에 사용
  7. WHERE절에 Index cluster key인 칼럼의 ‘=’조건절이 있을 경우에 사용
  8. WHERE절에 결합 INDEX의 칼럼이 있을 경우에 사용
  9. WHERE절에 single row index의 칼럼이 있을 경우에 사용
  10. index column이 bound range 된 것을 Where 조건에서 있을 경우에 사용
  11. index column이 unbound range 된 것을 Where조건에서 있을 경우에 사용
  12. where조건에 있는 칼럼들이 각각의 index로 구성되었을 경우에 사용
  13. MAX와 MIN함수의 argument는 칼럼, 상수, +연산자, ||연산자나 CONCAT함수 등을 포함하는 어떠한 수식도 올 수 있음. 단, select 절에 다른 수식이 오지 않아야 하며 문장은 WHERE절이나 GROUP BY절이 없어야 함.
  14. index의 칼럼이 order by절에서 이용할 경우에 사용
  15. 마지막으로 테이블 전체를 읽게 됨

Cost-Based Optimizer (비용기반 최적화)#

  1. 이론적으로 RBO에 비해 진보됨, 전문지식이 없어도 악성계획의 회피 가능
  2. 논리적인 한계성이 존재, 원하는 경로로 유도하기 어려운 단점

CBO 환경 설정 Parameter#

  1. Optimizer Mode = CHOOSE : 비용 기반 옵티마이저 환경을 의미
  2. ALL_ROWS : 비용 기반 옵티마이저 환경을 의미하며, SQL문의 WHERE 조건을 만족하는 모든 행을 가장 빠르게 검색하는 실행 계획을 결정함
  3. FIRST_ROWS : SQL문의 WHERE 조건을 만족하는 첫 번째 행을 가장 빠르게 검색하는 실행계획을 결정함
  4. FIRST_ROWS_10 : ~~ 10개 행을
  5. FIRST_ROWS_100 : ~~ 100개 행을
  6. FIRST_ROWS_1000 : ~~ 1000개 행을

CBO 특징#

  1. 좌에서 우로 파싱되므로 최초 기술된 테이블이 먼저 처리됨
  2. ANALYZE 명령은 과부하를 초래하기 때문에 항상 최선은 아님
  3. CHOOSE Mode에서 SQL문에 포함된 테이블 중 어느 하나라도 이미 분석된 상태라면, 그 SQL문은 기본적으로 CBO(비용기반 최적화)에 맞춰지게 됨

Optimizer Modes 설정 방법#

  1. Optimizer Mode = ( CHOOSE, ALL_ROWS, FIRST_ROWS, RULE
  2. 시스템 전체에 지정하기 (instance level) SQL> SET AUTOTRACE TRACE EXPLAIN
  3. 세션에서 지정 (Seesion level) ALTER SESSION SET OPTIMIZE_MODE = ( CHOOSEM ALL_ROWS, FIRST_ROWS, RULE )
  4. SQL문장에서 지정 (Statement level)
  • SELECT /*+ (ALL_ROWS, FIRST_ROWS, RULE) * /
/*+ CHOOSE */
/*+ RULE */
/*+ ALL_ROWS */
/*+ FIRST_ROWS[(n)] */
  • Hint를 사용하지 않는 경우 Session 혹은 System 전체에 정해진 Rule로 실행계획을 생성한 상태

옵티마이저를 이용한 SQL 튜닝#

인덱스 정보 이용법#

SELECT DISTINCT B.CLASS, COUNT(B.CLASS)   // DISTINCT는 아래의 GROUP BY에 의해 의미가 없음
FROM COL A, COM B
WHERE A.YEAR = B.YEAR
AND A.HOUSE = B. HOUSE
A.SERIAL_NO = B.SERIAL_NO
A.AMT >=100
B.DATE < '950501'
B.CLASS IN('C', 'Y', 'I' ,'P')
GROUP BY B.CLASS;

CHOOSE Mode와 CBO#

  1. 조합된 테이블 중 어느 하나라도 이미 분석된 상태라면, 기본적으로 CBO로 작동함
  2. SELECT /** RULE **/ ROUND (NUV(SUM(C.REQR_QTY Z.MIN_COMP_QTY * …

비용 기반 옵티마이저를 이용한 sql튜닝#

  1. 아무런 인덱스가 없는 테이블 EMP의 znalyze 되기 전의 실행계획
SELECT ename
FROM emp
WHERE deptno = 20
AND empno BETWEEN 100 TO 200
ORDER BY ename;

Execution Plan
-----------------------------------------------------------------------
0          SELECT STATEMENT Optimizer = CHOOSE
1     0      SORT (ORDER BY)
2     1         TABLE ACCESS (FULL) of 'EMP'

현재는 아무런 인덱스가 없으며 분석되지 않았으며, 비용기반이 아님

  1. 테이블 emp만 analyze 되었을 경우의 실행계획
SQL> ANALYZE TABLE emp COMPUTE STATISTICS;

분석 이후 Cost, Card, Byte등이 출력됨. (Cost가 큰 수일수록 비효율적임)

  1. 테이블 emp의 칼럼 deptno에 대해서 인덱스 생성시의 실행계획
SQL> CREATE INDEX EMP_DEMPTNO_IDX ON EMP(DEPTNO);

초기의 COST보다 18이 줄은 값이 나옴 인덱스 사용이 미사용보다 COST가 높았다면 인덱스를 사용하지 않았을 것임

예제#

SQL>conn / as sysdba
SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
optimizer_mode                       string      CHOOSE
SQL> alter session set optimizer_mode = first_rows_1;

세션이 변경되었습니다.

SQL> show parameter optimizer_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
optimizer_mode                       string      FIRST_ROWS_1

사용된 optimizer mode 알아내기#

select sql_text, optimizer_mode, optimizer_cost, parsing_schema_name 
from v$sql
where lower(sql_text) like 쿼리

mysql 실행계획 (mysql EXPLAIN)

Posted on by Posted in MySQL

인덱스가 적절히 사용되고 있는지 검토
나열된 순서는 MYSQL 이 쿼리처리에 사용하는 순서대로 출력

EXPLAIN 의 각 행 설명

1. id : SELECT 번호, 쿼리내의 SELECT 의 구분번호

2. select_type : SELECT 의 타입

  • SIMPLE: 단순 SELECT (UNION 이나 서브쿼리를 사용하지 않음)
  • PRIMARY: 가장 외곽의 SELECT
  • UNION: UNION 에서의 두번째 혹은 나중에 따라오는 SELECT
  • DEPENDENT UNION: UNION 에서의 두번째 혹은 나중에 따라오는 SELECT, 외곽쿼리에 의존적
  • UNION RESULT: UNION 의 결과물
  • SUBQUERY: 서브쿼리의 첫번째 SELECT
  • DEPENDENT SUBQUERY: 서브쿼리의 첫번째 SELECT, 외곽쿼리에 의존적
  • DERIVED: SELECT 로 추출된 테이블 (FROM 절 내부의 서브쿼리)

3. table : table명

4. type : 조인타입, 우수한 순서대로… 뒤로갈수록 나쁜 조인형태

  • system
    테이블에 단 하나의 행만 존재(시스템 테이블). const join 의 특수한 경우
  • const
    많아야 하나의 매치되는 행만 존재할 때
    PRIMARY KEY 나 UNIQUE index 를 상수와 비교할 때
    각 컬럼값은 나머지 연산에서 상수로 간주, 처음 한번만 읽어들이면 되므로 매우 빠름
  • eq_ref
    조인수행을 위해 각 테이블에서 하나씩의 행만이 읽히는 경우
    조인연산에 PRIMARY KEY 나 UNIQUE index 인덱스가 사용되는 경우
    인덱스된 컬럼이 = 연산에 사용되는 경우
  • ref
    이전 테이블과의 조인에 사용될 매치되는 인덱스의 모든행이 이 테이블에서 읽혀질 때
    leftmost prefix 키만을 사용하거나 사용된 키가 PRIMARY KEY 나 UNIQUE 가 아닐때
    (즉 키값으로 단일행을 추출할수 없을때)
    사용된 키가 적은수의 행과 매치되면 이것은 적절한 조인 타입
    ref 는 인덱스된 컬럼과 = 연산에서 사용됨
  • ref_or_null
    ref 와 같지만 NULL 값을 포함하는 행에대한 검색이 수반될 때
    서브쿼리 처리에서 대개 사용됨
  • index_merge
    인덱스 병합 최적화가 적용되는 조인 타입
    이 경우, key 컬럼은 사용된 인덱스의 리스트를 나타내며
    key_len 컬럼은 사용된 인덱스중 가장 긴 key 명을 나타냄
  • unique_subquery
    몇몇 IN 서브쿼리 처리에서 ref 타입대신 사용됨
    unique_subquery 는 성능향상을 위해 서브쿼리를 단순 index 검색 함수로 대체함
  • index_subquery
    unique_subquery 와 마찬가지로 IN 서브쿼리를 대체
    단, 서브쿼리에서 non-unique 인덱스가 사용될때 동작 함
  • range
    인덱스를 사용하여 주어진 범위 내의 행들만 추출
    key 컬럼: 사용된 인덱스
    key_len: 사용된 가장 긴 key 부분
    ref 컬럼: 이 타입의 조인에서 NULL
    키 컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN 또는 IN 연산에 사용될때 적용됨
  • index
    인덱스가 스캔된다는걸 제외하면 ALL 과 같음
    일반적으로 인덱스 파일이 데이타파일보다 작기 때문에 ALL 보다는 빠름
    MySQL 은 쿼리에서 단일 인덱스의 일부분인 컬럼을 사용할때 이 조인타입을 적용함
  • ALL
    이전 테이블과의 조인을 위해 풀스캔
    (조인에 쓰인) 첫번째 테이블이 고정이 아니라면 비효율적
    대부분의 경우에 아주 느린 성능
정리:
system – const – eq_ref – ref – ref_or_null – index_mergy – unique_subquery – index_subquery – range – index – ALL

5. possible_keys : MySQL 이 해당 테이블의 검색에 사용할수 있는 인덱스들
possible_keys 에 나타난 인덱스들이 결과에 나타난 테이블 순서에서 실제 사용할 수 없을수도 있음

6. key :  MySQL 이 실제 사용한 key(index)

7. key_len : MySQL 이 사용한 인덱스의 길이, key 컬럼값이 NULL 이면 이값도 NULL
key_len 값으로 MySQL 이 실제 복수컬럼 키중 얼마나 많은 부분을 사용할 것인지 알 수 있음

8. ref : 행을 추출하는데 키와 함께 사용된 컬럼이나 상수값

9. rows : 쿼리 수행에서 MySQL 이 예상하는 검색해야할 행수

10. Extra : MySQL 이 쿼리를 해석한 추가적인 정보를 나타냄

  • Distinct: MySQL 이 매치되는 첫행을 찾는 즉시 검색을 중단한다는 의미
  • Not exists: MySQL 이 LEFT JOIN 을 수행함에 매치되는 한 행을 찾으면 더이상 매치되는 행을 검색x
  • range checked for each record (index map: #): MySQL 이 사용할 좋은 인덱스가 없음 의미
  • Using filesort: MySQL 이 정렬을 위해 추가적인 과정을 필요로 함
  • Using index: 컬럼정보가 실제 테이블이 아닌 인덱스트리에서 추출, 쿼리에서 단일 인덱스된 컬럼들만을 사용하는 경우
  • Using temporary: MySQL 이 결과의 재사용을 위해 임시테이블을 사용, 쿼리 내에 GROUP BY 와 ORDER BY 절이 각기 다른 컬럼을 사용할때 발생
  • Using where: WHERE 절이 다음 조인에 사용될 행이나 클라이언트에게 돌려질 행을 제한하는 경우 테이블의 모든 행을 검사할 의도가 아니면 ALL 이나 index 라면 쿼리사용이 잘못된 것임
  • Using sort_union(…) , Using union(…) , Using intersect(…)
  • Using index for group-by: Using index 와 접근방식이 같으며, 추가적인 디스크 접근 없이 GROUP BY 나 DICTINCT 쿼리에 사용된 모든 컬럼에 대한 인덱스를 찾았음을 의미

mysqld기동과 정지

Posted on by Posted in MySQL

Unix계열에서 mysqld기동과 정지


1. mysqld를 쉘에서 직접기동
mysqld를 쉘에서 직접기동하는 방법은 다음과 같이 조작한다.
일반 계정에서 직접기동
shell$ mysqld &
 
그렇지만 mysqld는 root계정으로 기동되면 곧바로 종료하게 된다. 이것은 시큐리티 대책이다.
root계정으로 mysqld를 기동하는 경우에는 다음처럼 mysqld를 실행하는 계정을 지정해서 기동한다.
mysqld를 실행하는 계정을 지정한 기동
root@shell# mysqld –user=mysql &
 
–user옵션은 my.cnf의 [mysqld]그룹에서 기술가능하다.
2. mysqld_safe를 사용해서 기동
mysqld_safe스크립트는 내부에서 mysqld를 기동한다. 따라서 만약 root계정으로 mysqld_safe를 실행하는 경우는 myqld에 –user옵션을 지정하던지 mysqld_safe에 –user옵셥을 지정할 필요가 있다.
좀더 편하게 하려면 my.cnf의 [mysqld]그룹이던지 [mysqld_safe]그룹에 user옵션을 써넣는다.
mysqld_safe스크립트는 기동하면 상주하면서 mysqld프로세스를 감시한다.
무언가의 장해로 mysqld가 정지한 경우 다시 자동으로 mysqld를 기동한다.
mysqld_safe를 기동(root계정 사용시)
root@shell# mysqld_safe –user=mysql &
 
3. mysql.server를 사용해서 기동
mysql.server 스크립트는 내부에서 mysqld_safe를 기동한다. 다음처럼 조작한다.
mysql.server를 사용해서 기동
shell$ /user/local/mysql/support-files/mysql.server start
 
4. mysqld의 정지
기동과 마찬가지로 mysqld정지 방법도 몇개가 준비되어있다.
#mysql.server를 사용해서 정지
mysql.server스크립트를 사용해서 정지가능하다.
mysql.server를 사용해서 정지
shell$ /usr/local/mysql/support-files/mysql.server stop
 
#mysqladmin을 사용해서 정지
mysqladmin을 사용해서 정지하는 경우는 다음과 같이 조작한다.
mysqladmin을 사용해서 정지
shell$ mysqladmin -uroot shutdown
 
#kill을 사용해서 정지
kill명령어를 사용해서 정지하는 것이 가능하다. SIGTERM 시그널을 사용해서 MySQL은 안전하게 정지한다.
kill명령어를 사용해서 정지
shell$ kill `cat /usr/local/mysql/data/host.pid`
 
만약, mysql_safe를 이용해서 mysqld를 기동했을 경우에 최초에 mysqld_safe를 kill 명령어로 정지하고 그 다음에 mysqld를 정지한다.
그렇게 하지 않으면 mysqld_safe가 mysqld를 재기동해 버린다.
또 mysqlmanager로 mysqld를 기동했을 경우에 kill 명령어로 mysqld를 직접 정지시키면 mysqld가 재기동한다.
따라서 먼저 mysqldmanager를 정지시킬 필요가 있다.
kill 명령어를 사용하는 경우 kill -9나 kill -KILL은 절대 사용하지 않도록 해야한다.
이 조작으로 정지시키면 버퍼에 남아있던 데이터가 처리되지않은 상태로 mysqld가 정지하는 꼴이 되기 때문이다. 트랜잭션 버퍼나 INSERT DELAYED버퍼등이 영향을 받는다.

여러 개의 row를 한 줄로 가져오기 (GROUP_CONCAT)

Posted on by Posted in Databases

문제:
게시판 전체 목록의 태그들을 각각 가져와 한 줄로 뿌려주려고 한다.
각 게시물에 해당하는 태그 목록을 각각 조회해 가져오자니 너무 비효율적이라서,
게시물에 해당하는 태그(여러 개의 row)의 값을 공백으로 구분으로 넣으려고 한다.
데이터베이스는 mysql을 사용한다.

해결책:
mysql의 GROUP_CONCAT 함수를 사용하면 된다.

GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ‘ ‘)

와 같이 사용한다.

mysql reference에 잘 설명되어 있다. 참고하자.

MySQL Workbench에서 safe mode 처리

Posted on by Posted in MySQL

MySQL Workbench 5.2.42 버전을 사용하고 있는데요.

update나 delete 쿼리를 전달하니 다음과 같은 오류가 발생했습니다.

Error Code: 1175
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

safe mode로 설정되어 있어서 key 칼럼을 사용하지 않을 경우, 업데이트를 못한다는 것인데요.

관련 내용을 검색해 보니 설정을 바꾸면 된다고 하네요.

Workbench called me a dummy!

– Go to Edit -> Preferences
– Select the SQL Editor tab
– Uncheck “Forbid UPDATE and DELETE statements without a WHERE clause (safe updates)”

그런데 버전이 달라졌는지.. SQL Editor에는 저 항목이 없더라구요.