MySQL - index, 복합 인덱스 주의 사항, 스칼라 서브 쿼리 vs left join, index 실행 계획

index

인덱스와 카디널리티 (velog.io)

[SQL] 인덱스(INDEX) (tistory.com)

인덱스 컬럼의 분포도 및 순서(오라클 인덱스) (tistory.com)

DB Index 동작원리를 알아보자 | Eric's DevLog (데브로그) (kyungyeon.dev)

3장 인덱스 튜닝 - 그래서 어쩌라고? (velog.io)

인덱스 튜닝 – DATA ON-AIR (dataonair.or.kr)

[mysql] 인덱스 정리 및 팁 (tistory.com)

MySQL Index 특징 및 유의사항 정리 :: 뱀귤 블로그 (tistory.com)

 

index란

데이터베이스에서 검색을 할 때 더 빠르게 검색하기 위한 기술입니다.

index는 Clustered index와 Non Clustered Index가 있습니다.

 

Clustered index를 기준으로 실제 데이터가 정렬이 되고 primary key인 컬럼이 클러스터 인덱스가 됩니다. primary key 가 없는 경우에는 unique key 컬럼, unique key 컬럼이 없는 경우에는 MySQL 내부적으로 clustered index를 만듭니다.

 

Non Clustered Index는 MySQL에서 기본적으로 B+Tree 구조로 생성을 합니다.

B-Tree 구조는 root node, branch node, leaf node로 구성되어있습니다.

데이터를 검색하면 root node부터 시작해서 branch node를 거쳐서 leaf node에 도달하고 leaf node에 있는 실제 데이터의 위치 주소를 보고 실제 데이터까지 찾습니다.

 

index가 걸려있는 경우 검색 속도는 빨르지만 insert, update, delete 동작은 index에 의해 정렬되어있는 데이터를 다시 정렬해야 하기 때문에 더 느립니다.

 

인덱스로서는 카디널리티가 높은 컬럼이 좋습니다. 즉 데이터의 중복이 적은 컬럼일 수록 좋다는 말이고 예를 들어 성별 컬럼 보다는 주민등록 컬럼이 인덱스로서 더 좋은 성능을 발휘한다는 말입니다.

 

수치로 따져보면 데이터가 100만 건일 때 index로 인해 5~10%정도보다 적게 읽어야 효율이 좋고 

1000만 건인 경우는 5%정도 미만으로 읽어야 효율이 좋습니다. 이보다 더 많은 데이터를 읽는 경우에는 index 효율이 안 나와서 데이털를 full table scan 하는 것 보다 더 느립니다.

 

결합(복합) 인덱스 주의 사항

결합(복합)인덱스란?

두 개 이상의 컬럼을 합쳐서 만든 인덱스

 

결합 인덱스 생성시 효율을 좋게 하려면 디스크 I/O을 가장 적게 발생시키는 순서로 구성해야합니다.

그러려면 일반적으로

1. 선두 컬럼은 조건절에 항상 사용되거나 적어도 자주 사용되는 컬럼으로 선정해야 합니다.

2. 그리고 선두 컬럼은 '=' 조건으로 사용되는 컬럼이어야 합니다.

3. 그리고 ORDER BY 나 GROUP BY가 있는 경우 재배열 되지 않도록  ORDER BY 나 GROUP BY에서 사용되는 컬럼 순서와 같은 순서로 인덱스를 결합 해야 합니다.

4. 카디널리티가 높은 순서 즉 중복데이터가 적은 컬럼 순서로 구성하는게 일반적으로 좋습니다.

 

스칼라 서브 쿼리 vs left join

스칼라 서브 쿼리는 SELECT 문에 나타나는 서브쿼리이고  하나의 레코드만 리턴합니다.

left join은 왼쪽 테이블 전체와 왼쪽/오른쪽 테이블에서 조건에 맞는 데이터를 조회하는 join입니다.

 

스칼라 서브 쿼리의 캐싱 기능

 스칼라 서브 쿼리를 사용하면 캐시기능이 있어서 반복되는 입력값인 경우 재수행 하지 않아서 쿼리 실행 속도가 빠릅니다.

그런데, 스칼라 서브 쿼리의 조건절에 의해 입력값이 달라지는 경우에는 매번 쿼리를 재수행하기 때문에 쿼리 실행 속도가 느려질 수 있습니다.  이런 경우에는 join을 사용하면 쿼리 실행 속도를 높일 수 있습니다.

절대적인 것은 아니기 때문에 테이블 구조, join 하는 테이블에 어떤 인덱스가 걸려있는지 테이블에 데이터 건 수가 얼마나 있는 지를 고려해서 쿼리를 짜야 합니다.

 

 

아래는 틀린 말 인듯...

DRIVEN(후행) 테이블 건수에 따른 성능 차이

JOIN에서 나중에 읽는 DRIVEN 테이블의 건수가 적은 경우에는 LEFT OUTER JOIN 보다 스칼라서브쿼리가 캐싱기능에 의해 성능이 더 좋을 수 있습니다.

 

 

참고)

테이블의 건수가 많을 때는 소요시간이 거의 동일하다.

  - JOIN 조건 컬럼에 NOT NULL이 있을 경우 MariaDB 내부적으로 INNER JOIN으로 변경한다.

 

 

 

 

 

 

꿈꾸는 개발자, DBA 커뮤니티 구루비 (gurubee.net)

[MariaDB][성능] LEFT OUTER JOIN과 스칼라 서브쿼리 실행계획 비교1 (tistory.com)

 

▶ 인덱스 컬럼 선정 ★

-인덱스 컬럼의 분포도(Selectivity)가 10~15% 이내인 “컬럼”

-가능한 한 수정이 빈번하지 않는 “컬럼”

-ORDER BY, GROUP BY, UNION이 빈번한 “컬럼”

-분포도가 좋은 컬럼은 단독 인덱스로 생성

-인덱스들이 자주 조합되어 사용되는 컬럼은 결합 인덱스로 생성

 

 

• 인덱스 튜닝 (접근 경로 튜닝)
  • B-트리 인덱스
    • 일반적으로 사용하는 방식
    • 데이터양에 상관없이 모든 데이터의 인덱스 탐색시간이 동일
  • 비트맵 인덱스
    • 인덱스 컬럼의 데이터를 bit 값인 0 또는 1로 변환하여 키로 사용하는 방법
    • 분포도가 좋은 컬럼에 적합, 효율적인 논리 연산 가능, 저장공간이 작음
  • 역방향 인덱스
    • 인덱스 컬럼의 데이터를 역으로 변환하여 인덱스 키로 사용하는 방법
    • B-트리에서의 불균형 문제를 해결 (검색 경로가 한쪽방향으로만 수행되는 경우)
    • 데이터의 분포도(선택성)가 좋아져 검색 성능이 좋음
  • 종류
    • 클러스터드 인덱스
      • 인덱스 키순으로 데이터가 정렬되어 저장되는 방식
      • 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾음
      • 삽입, 삭제시 데이터 재정렬
      • 한개의 릴레이션에 하나의 인덱스만 생성가능
    • 넌클러스터드 인덱스
      • 인덱스 키값만 정렬
      • 데이터 검색하려면 먼저 인덱스를 검색해야됨(속도 떨어짐)
      • 여러개의 인덱스 만들수 있음
  • 인덱스 선정 기준
    • 분포도가 좋은 컬럼은 단독으로 인덱스를 생성한다
    • 데이터의 변경이 빈번하지 않은 컬럼에 인덱스를 생성
    • 인덱스들이 자주 조합되어 사용되는 경우 하나의 결합 인덱스 생성
    • 결합 인덱스의 컬럼 순서는 분포도가 낮은 컬럼을 선행컬럼으로 하자
      • 선행 컬럼은 where절에 항상 '='로 비교되어야 함
  • 인덱스 사용 못하는 경우
    • NULL값은 인덱스 대상에 포함이 안됨.
    • 부정 연산자는 다수의 데이터를 검색하므로 인덱스를 이용하지 못함
      • is not null 대신 > '' 을 사용하자
    • LIKE '%A' 같은 경우도 그러함
    • 인덱스 컬럼에 함수나 수식을 사용하면 인덱스 키값이 변형되어 사용못함

 

PRIMARY KEY와 UNIQUE

• PRIMARY KEY로 지정한 열은 클러스터형 인덱스가 생성된다.
• UNIQUE NOT NULL로 지정한 열도 클러스터형 인덱스가 생성된다.
• UNIQUE 또는 (UNIQUE NULL)로 지정한 열은 보조 인덱스가 생성된다.
• PRIMARY KEY와 UNIQUE NOT NULL이 둘 다 있으면 우선이 되는 PRIMARY KEY로 지정한 열에 클러스터형 인덱스가 생성된다.
• PRIAMRY KEY로 지정한 열(클러스터형 인덱스로 지정된 열)은 데이터가 오름차순 정렬된다

 

클러스터형 인덱스의 특징

• 클러스터형 인덱스의 생성시에는 데이터 페이지 전체가 다시 정렬된다. 그러므로 이미 대용량의 데이터가 입련된 상태라면 업무시간에 클러스터형 인덱스를 생성하는 것은 심각한 시스템 부하를 줄 수 있으므로 신중하게 생각해야 한다.
• 클러스터형 인덱스는 인덱스 자체의 리프 페이지가 곧 데이터이다. 그러므로 인덱스 자체에 데이터가 포함되어 있다고 볼 수 있다.
• 클러스터형 인덱스는 보조 인덱스보다 검색 속도는 더 빠르다. 특히 범위 검색에 유리하다. 하지만 추가/수정/삭제는 페이지 분할이 일어나기 때문에 더 느리다.
• 클러스터형 인덱스는 성능이 좋지만 테이블에 한 개만 생성할 수 있다. 그러므로 어느 열에 클러스터형 인덱스를 생성하는지에 따라서 시스템의 성능이 달라질 수 있다.

보조 인덱스 특징

• 보조 인덱스의 생성시에는 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성한다.
• 보조 인덱스의 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 주소 값(RID)이다. 클러스터형 보다 검색 속도는 더 느리지만 데이터의 입력/수정/삭제는 덜 느리다.
• insert할 때 보조 인덱스인 경우에 데이터페지에 그대로 순차적으로 데이터가 쌓이는 것이고, 루트 페이지와 리프 페이지에서 약간의 조정만 일어나지, 페이지 분할이 일어나지는 않는다. 페이지 분할이 일어나지 않기 때문에 클러스터형 인덱스보다 덜 느리다.
• 보조 인덱스는 여러개 생성할 수 있따. 하지만 함부로 남용할 경우에는 오히려 시스템 성능을 떨어뜨리는 결과를 초래할 수 있으므로 꼭 필요한 열에만 생성하는 것이 좋다. --> 책은 얇은데 뒤에 찾아보기만 두꺼운 느낌

 

 

 

실행계획 

explain select쿼리

 

type 컬럼

const : primary key나 unique key를 사용해서 1건을 가져오는 쿼리

eq-ref : join에서 두번째 이후에 읽는 테이블의 primary key로 조인

ref : index에 equal 검색

fulltext : 전문검색 인덱스(fulltext key)를 사용

range : 인덱스를 범위로 검색

all : 테이블에 full scan

 

MySQL 실행 계획(좋은 쿼리는 못 만들어도 뭐같은 쿼리는 만들지 말아야지) (tistory.com)

옵티마이저의 실행 계획 수립

RDBMS에서 가장 복잡하면서 가장 중요한 것은 옵티마이저(Optimizer)가 쿼리를 어떻게 실행할지 실행 계획을 결정하는 부분이다.

똑같은 쿼리라 할지라도 다양한 방법과 순서로 실행 될 수 있다.

어떤 실행 계획이 좋고 어떤 실행 계획이 안 좋은지 판단하는 건 온전히 옵티마이저의 몫이지만 개발자 역시도 어떤 실행 계획으로 수행되어야 좋은지를 알아야 최적의 실행 계획을 사용할 수 있도록 옵티마이저에게 힌트를 줄 수 있기 때문에 중요하고 반드시 학습해야하는 부분이다.

사전 지식

실행 계획에 대해 자세히 살펴보기 전에 아래의 사전 지식이 있어야 한다.

• 쿼리 실행 절차
  1. SQL을 SQL 파서가 파싱하여 파서 트리(parser tree)를 만든다.
  2. 파서 트리를 기준으로 옵티마이저가 분석하여 실행 계획을 만든다.
• 옵티마이저의 종류
  1. 규칙 기반 옵티마이저 : 이제는 안 쓰임
  2. 비용 기반 옵티마이저 : 쿼리 대상 테이블의 레코드 수, 선택도 등 통계 정보를 바탕으로 비용이 가장 적을 것 같은 방향으로 실행 계획 생성 → 대부분 이 방식 옵티마이저 사용(MySQL포함)
• 통계 정보가 정확할 수록 좋은 실행 계획을 만들 확률이 높다.
  • 통계 정보는 서비스중에도 수집되고 수정된다. innodb_stats_sample_pages 설정값은 통계 정보를 위해 분석할 인덱스 페이지의 개수(기본값:8)를 지정하는 것인데, 분석할 페이지 개수를 늘려서 더 정확한 통계 정보를 수집할 수 있지만, 수집 중에 테이블의 읽기/쓰기가 되지 않으므로 문제될 수 있다. 따라서 최대 16~24까지만 설정해야한다.
  • MySQL 8.0 부터는 인덱스 말고 일반 컬럼에도 통계 정보(Histogram)를 수집하기 때문에 더 정확한 통계를 토대로 실행 계획을 수립할 수 있다.
  • ANALYZE 키워드를 이용해 테이블의 통계 정보를 다시 수집할 수 있다.
• EXPLAIN 키워드를 SELECT 쿼리 앞에 붙여서 실행 계획을 살펴볼 수 있다.
  • INSERT, UPDATE, DELETE 쿼리는 실행 계획을 알 수 없다.

실행 계획 전격 분석

EXPLAIN 키워드를 붙여서 실행 계획 정보를 제공받을 때 테이블 형식으로 결과가 보여진다. (MySQL 8.0부터는 JSON이나 TREE로도 볼 수 있음)

따라서 제공받은 테이블의 각 컬럼이 어떤 의미인지 이해할 수 있어야 하기에 정리한다.

EXPLAIN SELECT *
FROM employees e
    INNER JOIN salaries s ON s.emp_no = e.emp_no
WHERE first_name='ABC';

 

id 컬럼

SELECT 쿼리를 구분하기 위한 용도의 컬럼이다. (SELECT문이 몇 개인지를 확인한다고 보면 된다.)

• SELECT 쿼리에 JOIN을 통해 여러 개의 테이블을 조회하더라도 JOIN으로 연결되어있다면 다른 SELECT 문으로 보지 않기 때문에 연관된 레코드는 같은 ID가 부여된다.
• SELECT 쿼리 내부에서 서브 쿼리(SELECT)를 사용한다든지 UNION을 사용하여 여러 테이블을 조회하는 경우가 있다. 이 때는 서브 쿼리와 UNION 쿼리로 조회되는 것은 다른 ID가 부여된다.

select_type 컬럼

SELECT 쿼리가 어떤 타입인지 나타내는 용도의 컬럼이다.

• SIMPLE : UNION이나 서브쿼리를 사용하지 않은 단순 SELECT 쿼리의 경우 SIMPLE로 표시 (JOIN한 경우도 SIMPLE 타입으로 친다.)
  • 실행 계획 테이블에서 select_type이 SIMPLE인 쿼리는 반드시 하나만 존재한다.
• PRIMARY : UNION, 서브쿼리가 포함된 SELECT 쿼리의 경우 가장 바깥쪽에 있는 SELECT 쿼리가 PRIMARY로 표기되고 마찬가지로 select_type이 PRIMARY인 쿼리는 반드시 하나만 존재한다.
• UNION : UNION을 사용한 경우 SELECT 쿼리 가운데 두 번째 이후 쿼리는 UNION으로 표시된다.
  • UNION에 사용된 첫 번째 SELECT 쿼리는 UNION의 결과를 대표하는 select_type으로 설정되어 DERIVED 라는 select_type을 갖는다.
• DEPENDENT UNION : UNION을 사용한 경우 중에서도 UNION으로 결합된 쿼리가 외부 쿼리에 의해 영향을 받는 것을 표기한다.
  • 내부 쿼리가 외부의 값을 참조해서 처리될 때 DEPENDENT 키워드가 추가된다.
• UNION RESULT : MySQL8.0부터 UNION ALL을 쓸 때는 결과를 담을 "임시 테이블"을 생성하지 않기로해서 안 보이지만 UNION 또는 UNION DISTINCT 쿼리는 임시 테이블에 결과를 담는다. 그래서 실행 계획 상 UNION RESULT는 UNION의 결과를 담는 임시 테이블을 의미한다.
• SUBQUERY : FROM절 이외에 사용된 서브 쿼리에 표기한다.
  • FROM 절에 사용된 서브 쿼리는 DERIVED 로 표기된다.
• DEPENDENT SUBQUERY : FROM절 이외에 사용된 서브 쿼리가 바깥쪽 SELECT 쿼리에서 정의된 컬럼을 사용하는 경우에 해당 서브 쿼리에 표기된다.
• DERIVED : FROM절에 사용된 서브 쿼리로 SELECT 쿼리의 결과로 메모리나 디스크에 임시 테이블을 만드는 경우를 의미한다.
• DEPENDENT DERIVED : MySQL 8.0 이전에는 FROM 절의 서브쿼리에 외부 컬럼을 사용할 수 없었으나 8.0 이후로는 가능하게 되었다. 래터럴(LATERAL) 조인으로 FROM 절의 서브 쿼리가 외부 컬럼을 참조할 수 있게 되었는데 래터럴 조인을 사용했을 때 표기된다.
• UNCACHEABLE SUBQUERY : SUBQUERY, DEPENDENT SUBQUERY는 서브 쿼리 결과를 캐시할 수 있는데 특정 조건때문에 캐시를 이용할 수 없을 때 표기된다.
  • 사용자 변수가 서브 쿼리에 들어갔다거나 UUID(), RAND() 같이 결과값이 호출할 때마다 변경되는 함수가 서브 쿼리에 들어갔다거나 NOT-DETERMINISTIC 속성의 스토어드 함수가 서브 쿼리에 들어간 경우가 특정 조건이다.
• MATERIALIZED : FROM 절이나 IN (subquery) 형태의 쿼리에 사용된 서브 쿼리를 최적화할 때 사용된다. 이 경우에 보통 서브 쿼리보다 외부 쿼리의 테이블을 먼저 읽어서 비효율적으로 실행되기 마련인데 이렇게 실행하지 않고 서브 쿼리의 내용을 임시테이블로 구체화한 후 외부 테이블과 조인하는 형태로 최적화된다. 이 때, 서브 쿼리가 먼저 구체화되었다는 것을 표기할 때 사용된다.
  • 뭔가 효율적으로 개선된 듯하지만 결국 임시테이블을 사용하므로 엄청 효율적이지는 않다.

쿼리 타입만 보고도 어떤 쿼리를 개선해야할지 감이 잡히기 때문에 잘 알아두는 것이 좋겠다.

기본적으로 DEPENDENT 는 외부 쿼리에 의존하므로 성능이 느리고 임시 테이블을 사용하는 것들도 디스크에 임시 테이블을 만들 위험이 있기에 제거 대상이다. (메모리에 임시 테이블은 그나마 좀 낫다.)

DERIVED 도 최대한 JOIN으로 해결해야하기에 제거 대상이 된다.

table 컬럼

EXPLAIN 키워드로 실행 계획을 확인할 때, SELECT 쿼리로 분류하는게 아니라 테이블 단위로 분류해서 결과가 나온다.

그래서 어떤 테이블에 대한 정보인지를 나타낸다.

table 컬럼에 <> 로 감싸져있는 경우를 볼 수 있는데 이 테이블은 임시 테이블을 의미한다.

partitions 컬럼

파티셔닝(partitioning)하여 테이블을 관리한다고 가정할 때, 어떤 파티션을 읽었는지를 알려주는 정보다.

5.7 이전에는 EXPLAIN PARTITION 명령을 해야 보였으나 8.0 부터는 EXPLAIN 명령만해도 보이게 되었다.

type 컬럼

실행 계획이 적절하게 인덱스를 참조했는지를 확인하는데 가장 🌈핵심인 컬럼이다.

ALL을 빼고는 다 인덱스를 참조하긴 했다는것인데 인덱스를 참조했다고해서 무조건 효율적인 것은 아니다.

아래 type 컬럼의 종류는 성능이 좋은 순서로 나열했다.

참고로 1 부터 9 까지는 일반적으로 효율적인 케이스고 10부터 12까지는 일반적으로 비효율적인 케이스다.

1. system : 레코드가 0건 또는 1건만 존재하는 테이블을 접근할 때의 방법 (InnoDB에는 없으니 생략)
2. const : 쿼리에 프라이머리 키/유니크 키 컬럼을 이용하는 where 조건에 있으면서 결과가 반드시 1건을 반환하는 쿼리로 접근할 때의 방법 (= UNIQUE INDEX SCAN)
   • ❗결과가 1개인 것을 쿼리 전에 DBMS가 예측할 수 있어야 한다. (쿼리 실행 후에 결과가 1개인 것은 의미없음)
3. eq_ref : 여러 테이블이 JOIN되는 쿼리에서만 발생, JOIN에서 처음 읽은 테이블의 컬럼 값을 두 번째 이후 읽는 테이블의 프라이머리 키/유니크 인덱스 컬럼(NOT NULL)의 동등 조건에 사용될 때 = 반드시 1건만 존재한다는 보장이 있을 때 사용되는 접근 방법
4. ref : 'eq_ref'와 달리 JOIN 순서에 상관없이 사용되며, 프라이머리 키나 유니크 인덱스 등의 제약도 상관없이 사용된다. 인덱스의 종류와 상관없이 동등(equal)조건이 사용될 때 접근 방법이다. (단, 레코드가 반드시 1건이라는 보장이 없으므로 eq_ref보다 느리지만 비교적 느릴 뿐 엄청 빠른 인덱스다.)
5. fulltext : MySQL로 전문 검색 인덱스를 사용해서 레코드에 접근하는 방법, 전문 검색할 컬럼에 인덱스가 있어야 한다. "MATCH ... AGAINST ..." 구문을 사용해서 실행된다. (잘 안 써봐서 모르겠다...)
6. ref_or_null : ref와 같은데 NULL 비교(IS NULL)가 추가된 형태 (실무에서 보기 힘든 형태???)
7. unique_subquery : where 조건에 IN (subquery) 형태를 갖을 때, 서브 쿼리에서 중복되지 않는 유니크한 값만 반환될 때 이 접근 방식을 선택한다.
8. index_subquery : IN 연산자 특성상 IN 괄호 조건에 나오는 목록에 중복값이 제거 되어야 한다. 유니크하지 않은 경우에 인덱스를 이용하여 중복을 제거하는 케이스다.
9. range : 인덱스를 하나의 값이 아니라 범위로 검색하는 경우에 사용되는 접근 방법이다. 주로 < , > , IS NULL , BETWEEN , IN , LIKE 등의 연산자로 인덱스를 검색하는 경우다. 통상적으로 '인덱스 스캔이다' 라고하면 range, const, ref를 묶어서 지칭한다.
10. index_merge : 2개 이상의 인덱스를 이용해 각각의 검색결과를 만든 후 결과를 합치는 접근 방식, 실제 우선순위가 range보다 높지만 생각보다 효율적으로 동작하지 않은 문제가 있다고 한다.
11. index : 인덱스를 처음부터 끝까지 읽어야 하는 경우에 쓰는 접근 방식으로 비효율적이다. 다음 두 가지 조건에서 발생한다. 최악은 아니고 차악정도 느낌이다.
    • 인덱스 스캔(range, const, ref) 불가능한 경우 + 인덱스에 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우(굳이 데이터파일 읽지 않아도 되는 경우)
    • 인덱스 스캔(range, const, ref) 불가능한 경우 + 인덱스를 이용해 정렬이나 그룹핑 작업이 가능한 경우(정렬 작업을 피할 수 있는 경우)
12. ALL : 풀 테이블 스캔이다. 일반적인 조회 환경에서 가장 나쁜 방법이지만 억지로 잘못 설계된 인덱스를 타게 하는 경우보다 풀 스캔이 적절할 때도 있긴 있다.

possible_keys 컬럼

옵티마이저가 쿼리를 처리하기 위해 여러 처리 방법을 고려하던 중에 사용된 후보 인덱스 목록일 뿐이다.

key 컬럼

possible_keys 컬럼에서 보여진 후보 인덱스 목록 중 실제 사용된 인덱스를 의미한다.

인덱스 사용 못했을 경우는 NULL로 표기된다.

key_len 컬럼

실무에서 인덱스를 단일 컬럼으로 만들기 보다는 다중 컬럼으로 만들어지는 경우가 더 많다.

이 때 다중 컬럼 인덱스 중에서 몇 바이트까지 사용했는지 알려준다.

각 인덱스 컬럼에 할당된 바이트를 알 수 있으니 몇 개의 인덱스 컬럼이 사용되었는지를 추산할 수 있다.

아래 쿼리는 (dept_no, emp_no)로 다중 컬럼(2개)으로 만들어진 프라이머리 키를 포함한 dept_emp테이블을 조회하는 쿼리다.

SELECT * FROM dept_emp WHERE dept_no=3;

key_len이 4가 나온다고 해보자. 그러면 dept_no 컬럼의 타입이 INTEGER(4BYTE)이기 때문에 인덱스(dept_no + emp_no)중에서 앞에 dep_no까지만 쓰였다는 것을 확인할 수 있다.

ref 컬럼

type 컬럼에서 접근 방법이 ref 이면 어떤 컬럼이 조건에 사용되었는지를 보여준다.

"func"라고 표시될 때도 있는데 이건 단순 컬럼이 아니라 어떤 가공된 컬럼이 사용될 때 나타난다.

row 컬럼

옵티마이저가 비용을 산정하기 위해 얼마나 많은 레코드를 읽고 비교해야하는지 예측해본 레코드의 수다.

실제 테이블의 레코드 수와 일치하지 않는 경우가 많고 대략적인 통계에 의한 값이다.

Extra 컬럼

MySQL이 어떻게 쿼리를 풀었는지 부가 정보가 나온다. 꽤 많은 종류가 있고, 여러 개가 동시에 나온다.

• const row not found : const 접근 방식으로 읽었으나 레코드가 0개 일 때를 의미한다.
• distinct : JOIN시 필요한 것만 JOIN했고 중복된 값 제거했음을 의미한다.
• Full scan on NULL key : where 조건에 nullable 컬럼이 있는 경우, null을 만나면 풀 스캔을 하겠다고 경고하는 의미다.

SELECT * FROM test
WHERE col1 IN (SELECT col2 FROM test2);
-- col1이 null인 경우에 풀스캔 하겠다라고 뜬다.

• Impossible HAVING : HAVING 조건에 만족하는 레코드가 없는 경우를 의미한다. = 쿼리를 다시 짜라
• Impossible WHERE : WHERE 조건이 항상 FALSE가 될 수 밖에 없는 경우를 의미한다. = 쿼리 다시 짜라
• Impossible WHERE noticed after reading const tables : 쿼리를 실행해보기 전엔 WHERE 조건이 항상 false인지는 모르지만 실행해보니까 알게된 경우다. = 쿼리 다시 짜라
  • const 는 상수로 치환되기 때문에 옵티마이저가 쿼리 일부를 실행해보기에 이런 결과가 나올 수 있다.
• No matching min/max row : MIN(), MAX()와 같은 집합 함수가 있는 쿼리의 WHERE 조건절에 일치하는 레코드가 하나도 없는 경우를 의미한다. = 쿼리 다시 짜라
• No matching row in const table : const 방식으로 접근할 때 일치하는 레코드가 없는 경우를 의미한다. = 쿼리 다시 짜라
• No tables used : FROM 절이 없거나 "FROM DUAL" 형태의 쿼리의 경우를 의미한다.
• Not exists : A 테이블에는 존재하지만 B 테이블에 존재하지 않는 값을 조회하는 경우 두 가지 방법을 쓴다.
  • 안티조인(Anti-JOIN) : NOT IN (subquery) 형태나 NOT EXISTS 연산자를 사용한 방법
  • 아우터조인(LEFT OUTER JOIN) : 레코드가 많을 때 안티조인보다 더 나은 방법
    • 아우터조인으로 처리할 때, 안티 조인을 수행하는 쿼리에서는 B테이블의 레코드가 존재하는지 아닌지만 판단하기에 최적화 할 수 있기에 이 때, MySQL 내부적으로 최적화를 NOT EXISTS 스타일로 했다는 것으로 적절히 처리된다.

SELECT * FROM dept_emp de LEFT JOIN departments d ON de.dept_no = d.dept_no
WHERE d.dept_no IS NULL;

• Range checked for each record (index map: N) : 두 테이블을 조인하는 경우에 WHERE 조건에 변수가 두 개가 사용되어 계속해서 변수 값이 바뀐다. 즉 매 레코드마다 인덱스를 탈지 풀 스캔을 할지 결정해야하는 쿼리다.

SELECT * FROM employees e1, employees e2
WHERE e1.emp_no >= e2.emp_no;
-- 이 경우 e1 테이블의 레코드를 읽고 e2의 레코드를 읽는데 e1 테이블 레코드를 읽을 때마다 값이 달라진다.

• Scanned N databases : DB 메타정보(테이블,컬럼,인덱스등...)가 저장되어 있는 DB(INFORMATION_SCHEMA)를 조회하는 경우를 의미한다. 애플리케이션에서 메타정보가 있는 DB에 쿼리할 일이 거의 없으므로 실무에서 볼일이 없다.
• Select tables optimized away : MIN() 또는 MAX()만 SELECT에 사용되거나 GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 적절한 인덱스를 사용해서 오름차순 또는 내림차순의 레코드 1건만 읽는 형태로 최적화되었음을 의미한다.
  • 인덱스는 정렬되어있기 때문에 제일 앞 또는 제일 뒤 레코드가 최소, 최대값이다.
• unique row not found : 두 개의 테이블이 프라이머리 키 또는 유니크 컬럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 없을 때를 의미한다.
  • "A LEFT OUTER JOIN B"에서 A테이블도 B테이블도 유니크한테 A테이블에만 있는 레코드로 조인하려고 하면 B랑 조인하는 컬럼 값이 없는 애도 생긴다.
• Using filesort : ORDER BY를 처리하기 위해 인덱스를 이용할 수도 있지만 적절한 인덱스를 사용하지 못하는 경우, MySQL에서 정렬을 한 번 해야하는데 소트 버퍼에 레코드를 복사해서 정렬하는 비효율적인 작업을 하는 것을 의미한다.
  • 실무에서 정렬은 거의 필수라 그런지 제일 자주 보이는 비효율적인 케이스라고 볼 수 있다. (= 튜닝 대상이 된다.)
• Using index : 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 처리할 수 있는 경우(= 커버링 인덱스를 탔을 때)를 의미한다. (극강의 효율이다.)
  • 간혹 type 컬럼의 index(=인덱스 풀 스캔)와 Extra 컬럼의 Using index (=커버링 인덱스)를 헷갈리는 경우가 있는데 완전 반대되게 비효율적인 것과 효율적인 것이다.
• Using index for group-by : GROUP BY 처리를 위해 (이미 정렬된) 인덱스를 이용하는 경우 즉 루스 인덱스 스캔을 의미한다.
• Using join buffer : 일반적으로 조인에 사용되는 컬럼은 인덱스를 생성한다.
  • 조인하기 위해 먼저 읽은 테이블을 드라이빙(Driving)테이블이라 하고 나중에 읽는 테이블을 드리븐(Driven) 테이블이라 한다.
    • 드리븐 테이블에 JOIN되는 컬럼에 인덱스가 없으면 조인 버퍼(JOIN BUFFER)라는 임시 공간을 사용해서 JOIN한다. 이럴 때 나타난다.
    • 추가로 조인 조건이 없는 카테시안 조인을 수행하는 경우는 항상 조인 버퍼를 사용한다.
• Using sort_union, Using union, Using intersect : 쿼리가 "index_merge" type의 접근 방법을 사용할 때만 2개 이상의 인덱스가 사용될 수 있다고 했다. 이 때 두 인덱스로부터 읽은 결과를 어떻게 합쳤는지를 보여줄 때 나타난다.
  • Using intersect : 각각 인덱스를 사용하는 조건이 AND로 연결된 경우, 처리 결과에서 교집합을 추출했다는 의미
  • Using union : 각각 인덱스를 사용하는 조건이 OR로 연결된 경우, 처리 결과에서 합집합을 추출했다는 의미
  • Using sort_union : Using union과 같은 작업을 수행하지만 Using union으로 처리될 수 없는 경우(OR로 연결된 레코드가 대량의 range조건)이다. 프라이머리 키만 읽어서 먼저 정렬하고 병합한 후에야 레코드를 읽어서 반환할 수 있다.
• Using temporary : MySQL이 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블을 사용한다. 임시테이블이 사용되었을 경우에 나타나는데 임시 테이블을 썼을 때 안 나타나기도 한다(?)
  • FROM 절에 서브쿼리를 쓰면 무조건 임시테이블(Derived table)이 생성된다.
  • "COUNT(DISTINCT column1)"를 포함하는 쿼리도 인덱스를 사용할 수 없는 경우에 임시 테이블이 만들어진다.
  • UNION, UNION ALL이 사용된 쿼리도 항상 임시 테이블을 사용한다.
  • 인덱스를 사용하지 못하는 정렬 작업도 임시 테이블을 사용한다.
• Using where : MySQL엔진이 별도의 가공, 필터링 작업을 처리한 경우일 때만 나타난다. 범위 조건은 스토리지 엔진에서 처리되어 레코드를 리턴해주지만, 체크 조건은 MySQL엔진에서 처리된다. (5.1 플러그인 버전부터는 이렇게 불합리하게 처리되지 않는다고 한다. keyword : condition push down)

SELECT * FROM employees WHERE emp_no BETWEEN 100 AND 110 AND gender = 'F';
-- 범위조건 BETWEEN은 스토리지 엔진에 의해 10개의 레코드가 리턴되고
-- 10개중에 체크 조건 gender='F' 인 것을 MySQL엔진에 의해 필터링되어 Using Where 등장

Filtered 컬럼

Extra 컬럼에 표시되는 "Using where"이 스토리지 엔진이 리턴해준 레코드들을 MySQL엔진이 필터링하면 등장한다는 것을 알았다.

그런데 정확하게 얼마나 많은 레코드가 필터링되었는 알 수 없었다.

이것을 표현하는 것이 Filtered 컬럼의 역할이다.

Filtered 컬럼을 확인하려면 EXPLAIN 키워드에 추가로 EXTENDED 키워드를 붙이면 된다.

EXPLAIN EXTENDED
SELECT * FROM employees
WHERE emp_no BETWEEN 100 AND 110 AND gender = 'F';

그러면 Filtered 컬럼에 값이 나오는데 이 값은 필터링, 가공 후 레코드가 몇 퍼센트(%)나 남았을지를 알려준다.

웃긴건 이것도 실제 값이 아니라 통계 예측 값이라는 것이다...

MySQL 8.0에서는 그냥 EXPLAIN으로 나온다.

 

 

 

 

 

 

 

 

 

 

driving, driven table

[SQL 튜닝] 드라이빙 테이블(DRIVING TABLE)의 개념/결정 규칙 (tistory.com)

 

 

 

 

 

join

[DB 기술면접 질문 리스트] : JOIN (tistory.com)

 

 

 

제약조건 추가 참고

// 제약조건 확인하기

DESC 데이터베이스 명.테이블 명;

 

// 제약조건 삭제

ALTER TABLE [테이블 명] DROP CONSTRAINT [제약조건 이름];

ALTER TABLE [테이블 명] DROP FOREIGN KEY [제약조건 이름];

 

// 제약조건 추가

외래키 : ALTER TABLE [테이블 명] ADD CONSTRAINT [제약조건 이름] FOREIGN KEY(컬럼 명)

REFERENCES [부모테이블 명](PK 컬럼 명) [ON DELETE CASCADE / ON UPDATE CASCADE];

 

기본키 : ALTER TABLE [테이블 명] ADD CONSTRAINT [제약조건 이름] PRIMARY KEY(컬럼 명);

 

// 제약조건 수정

// NOT NULL 제약 조건 추가

ALTER TABLE [테이블 명] MODIFY [컬럼 명] [데이터 타입] CONSTRAINT [제약조건 이름] NOT NULL;

CREATE TABLE `member` (

`member_id` bigint NOT NULL AUTO_INCREMENT,

`email` varchar(255) DEFAULT NULL,

`member_name` varchar(255) NOT NULL,

`password` varchar(255) DEFAULT NULL,

`phone` varchar(255) DEFAULT NULL,

PRIMARY KEY (`member_id`),

UNIQUE KEY `member_name` (`member_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

 

CREATE TABLE `member_authority` (

`member_id` bigint NOT NULL,

`authority_status` varchar(255) NOT NULL,

KEY `FK1c9yn9hp1h6t96t54n576etl4` (`authority_status`),

KEY `member_authority_fk01` (`member_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

 

desc `member`;

alter table member MODIFY member_id bigint NOT NULL;

alter table member drop primary key;

alter table member MODIFY member_id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY;

desc `member`;

 

desc `member`;

desc `member_authority`;

alter table member_authority ADD constraint member_authority_fk01 foreign key(member_id) references member(member_id);

alter table member_authority DROP foreign key member_authority_fk01;

desc `member`;

desc `member_authority`;

 

desc `member`;

alter table member MODIFY member_name varchar(255) not null unique;

desc `member`;

 

 

 

 

 

 

 

 

 

 

 

 

 

l