MariaDB Index 1편

목차

• 인덱스 통계 정보 확인
  • CARDINALITY
  • MariaDB 데이터 및 인덱스 용량 확인
• 커버링 인덱스

인덱스 통계 정보 확인

인덱스 통계 정보를 확인하는 방법은 다음과 같습니다.
user 테이블이 있다고 가정한다면, 다음과 같이 쿼리를 실행하면 됩니다.

ANALYZE TABLE users;
SHOW INDEX FROM users;
SHOW INDEX FROM users WHERE Index_type = 'BTREE';

-- 아래와 같이 조회해도 동일.
SELECT * FROM information_schema.STATISTICS;

통계 정보에서 조회되는 컬럼이 갖는 의미는 다음과 같습니다.

• TABLE_CATALOG: 인덱스가 속한 데이터베이스의 이름
  TABLE_CATALOG 컬럼의 값이 def인 경우, 이는 MySQL 서버에 내장된 기본 데이터베이스를 나타냅니다.
• TABLE_SCHEMA: 인덱스가 속한 스키마의 이름
  일반적으로는 SHOW DATABASES;를 했을때의 Database 이름이 여기에 해당합니다.
• TABLE_NAME: 인덱스가 속한 테이블의 이름
• NON_UNIQUE: 인덱스가 고유한지 여부를 나타내는 값 (0은 고유한 인덱스, 1은 고유하지 않은 인덱스)
  일반적으로 PRIMARY KEY가 인덱스인 경우 고유한 인덱스(0) 입니다.
  그리고, UNIQUE KEY로 생성한 경우의 인덱스도 고유한 인덱스(0) 입니다.
• INDEX_SCHEMA: 인덱스가 속한 스키마의 이름
  TABLE_SCHEMA와 동일한 값을 갖는 것으로 보입니다. TABLE_SCHEMA와 다른 값이 있는지 조회해 보니 조회되지 않습니다.
• INDEX_NAME: 인덱스의 이름
• SEQ_IN_INDEX: 인덱스 내 컬럼의 순서
  INDEX_NAME을 그룹으로 인덱스 컬럼의 순서입니다.
• COLUMN_NAME: 인덱스 내 컬럼의 이름
• COLLATION: 컬럼의 문자열 정렬 방식
   COLLATION은 데이터베이스에서 문자열을 비교하거나 정렬할 때 사용되는 규칙 집합입니다.
  A는 일반적으로 ASCII 문자셋을 사용하는 문자열 정렬 순서를 나타냅니다.
• CARDINALITY: 인덱스 내 고유한 값의 개수 (인덱스의 선택도를 나타냅니다.)
  레코드 수로 판단하자면 다음과 같습니다.
  테이블 레코드 >= 인덱스 레코드 >= 인덱스 CARDINALITY
• SUB_PART: 인덱스 내 컬럼의 일부분에 대한 길이
• PACKED: 인덱스 내의 필드가 압축되었는지 여부를 나타내는 값
• NULLABLE: 컬럼이 NULL 값을 허용하는지 여부를 나타내는 값
• INDEX_TYPE: 인덱스의 유형 (BTREE, HASH, RTREE 등)
• COMMENT: 인덱스의 주석

CARDINALITY

주요하게 볼만한 것은 CARDINALITY인 것 같습니다.

테이블에는 여러개의 인덱스를 생성할 수 있습니다. 여기서 어떤 인덱스를 활용하는게 좋은가 했을 때 참고할 수 있는 지표로 보입니다.
CARDINALITY가 높은 인덱스의 경우 인덱스에 포함된 고유한 값의 갯수가 그만큼 많다는 것이기 때문에, CARDINALITY가 높은 인덱스를 활용하는 것이 불필요한 레코드 검색을 줄이고 더욱 빠르게 데이터 검색을 할 수 있습니다.

CARDINALITY가 낮은 경우 데이터 검색이 느릴 수 있습니다. 인덱스 내에 중복된 값이 많아 데이터베이스에 불필요한 데이터를 검색하는 경우가 생길 수 있기 때문입니다.

항상 인덱스를 사용하는 것이 좋은 것은 아닙니다. 적은 개수의 데이터를 가지고 있는 경우, 인덱스를 사용하는 것이 오히려 성능 저하를 일으킬 수 있습니다.

항상 CARDINALITY가 높은 인덱스를 사용하는 것이 좋은 것만은 아닙니다. 실 세계에는 너무 다양한 상황이 존재하기 때문에 데이터베이스 특성과 쿼리 패턴에 맞게 적절한 인덱스를 선택해야 합니다.

그리고, CARDINALITY가 TABLE ROW수랑 같으면, 오히려 인덱스를 사용함으로 인해서 데이터의 양과 인덱스의 구조에 따라 풀스캔을 하는 것보다 느릴 수 있습니다.  

MariaDB 데이터 및 인덱스 용량 확인

    SELECT table_schema AS 'Database',
           table_name AS 'Table',
           ROUND(data_length / (1024 * 1024), 2) AS 'Data Size (MB)',
           ROUND(index_length / (1024 * 1024), 2) AS 'Index Size (MB)',
           ROUND((data_length + index_length) / (1024 * 1024), 2) AS 'Total Size (MB)'
      FROM information_schema.TABLES
     WHERE table_schema = 'dev'
  ORDER BY (data_length + index_length) DESC;

커버링 인덱스

커버링 인덱스란, SELECT 수행 시 인덱스에 포함된 모든 컬럼을 사용할 경우, 인덱스만을 사용해서 데이터를 조회해 오는 방법입니다.
일반적으로는, 인덱스에 포함된 컬럼을 WHERE 조건에 걸고, 인덱스에 포함되지 않은 컬럼을 SELECT 절의 조회 컬럼으로 사용 합니다.
이렇게 하게 되면, 인덱스로 데이터를 빠르게 스캔한 후 해당 인덱스에 해당하는 데이터를 다시 한번 조회하게 됩니다.

커버링 인덱스의 경우는 한번 더 테이블 조회를 하지 않기 때문에 커버링 인덱스가 적용되면 커버링 인덱스가 적용되지 않았을 때보다 빠르게 데이터를 가져올 수 있습니다.

다음과 같은 경우 커버링인덱스가 적용됩니다.

create index users_name_email_index
    on users (name, email);
    
SELECT name, email
FROM users
WHERE name = 'junseok';

아래와 같은 경우는 SELECT 절에 Index 외의 필드가 포함되어 있어서 커버링 인덱스가 적용되지 않습니다.

create index users_first_name_last_name_index
    on users (first_name, last_name);

SELECT id, first_name, last_name
 WHERE first_name = 'A'
   AND last_name = 'B';

이와 같은 경우 커버링 인덱스가 적용되게 하려면 인덱스를 재설계 해야 합니다.

create index users_first_name_last_name_id_index
    on users (first_name, last_name, id);

SELECT id, first_name, last_name
 WHERE first_name = 'A'
   AND last_name = 'B';

이렇게 하게 되면 커버링 인덱스가 적용되서 성능향상을 기대할 수 있습니다.