인덱스 - 실습

인덱스 문법

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

• INDEX 추가로 생성
• CREATE INDEX <index_name> ON <table_name> (column [ASC|DESC],...);
• TABLE 생성시 INDEX 생성
• CREATE TABLE <table_name> ( ..., INDEX <index_name> (column [ASC|DESC],...) );
• TALBE에 INDEX 추가
• ALTER TABLE <table_name> ADD <index_name> (column [ASC|DESC],...);
• INDEX 확인
• SHOW INDEX FROM <table_name>;
• INDEX 삭제
• ALTER TABLE <table_name> DROP INDEX <index_name>;

인덱스 생성 예제

1. timedailysum 테이블에서 역 별 일일 이용횟수가 10 회 미만인 날짜와 역 번호를 모두 출력하는 쿼리이다. cnt 컬럼으로 구성된 인덱스를 생성한 뒤 해당 인덱스의 유무에 따른 수행시간과 실행계획을 비교하세요

SELECT usedDate , stationCode FROM timedailysum WHERE cnt < 10

인덱스 생성 전의 explain

-> Filter: (timedailysum.cnt < 10) (cost=673412.20 rows=2176723)  
-> Table scan on timedailysum (cost=673412.20 rows=6530822)

CREATE INDEX ix_timedailysum_01 ON timedailysum(cnt);

인덱스 생성 후의 explain

-> Index range scan on timedailysum using ix\_timedaulysum\_01 over (NULL < cnt < 10), with index condition: (timedailysum.cnt < 10) (cost=26121.51 rows=24925)

2. dailyusedcnt 테이블에서 2021년 8월 15일을 기준으로 1주 전후 간 역 이름과 탑승 인원을 조회 하는 쿼리이다. useddate 컬럼으로 구성된 인덱스를 생성한 뒤 해 당 인덱스의 유무에 따른 수행 시간과 실행 계획을 비교하세요.

select stationName, sum(inCnt)+sum(outCnt) as '탑승인원'
from dailyusedcnt
where usedDate between adddate('2021-08-15', -7) and adddate('2021-08-15',7)
group by stationName;

인덱스 생성 전의 explain

-> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Filter: (dailyusedcnt.usedDate between <cache>(('2021-08-15' + interval -(7) day)) and <cache>(('2021-08-15' + interval 7 day)))  (cost=29400.65 rows=32388)
            -> Table scan on dailyusedcnt  (cost=29400.65 rows=291519)

인덱스 생성 후의 explain

-> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Index range scan on dailyusedcnt using ix_dailyusedcnt_01 over ('2021-08-08' <= usedDate <= '2021-08-22'), with index condition: (dailyusedcnt.usedDate between <cache>(('2021-08-15' + interval -(7) day)) and <cache>(('2021-08-15' + interval 7 day)))  (cost=4057.01 rows=9015)

Function Based Index 예제

3. ‘LEFT(stationCode, 1)’ 컬럼으로 구성된 Function Based Index를 생성한 뒤, 해당 인덱스의 유무에 따른 아래 쿼리의 실행계획을 비교하세요.

   SELECT stationCode, CONCAT('+82-', SUBSTR(phone, 2, 12)) as phone
   FROM stationdetail
   WHERE LEFT(stationCode, 1) = '2'
   AND SUBSTR(stationCode, LENGTH(stationCode) - 1, 1) = '1';

CREATE INDEX ix_stationdetail_01 on stationdetail((LEFT(stationCode, 1)));

인덱스 생성 전의 explain

-> Filter: ((left(stationdetail.stationCode,1) = '2') and (substr(stationdetail.stationCode,(length(stationdetail.stationCode) - 1),1) = '1'))  (cost=29.80 rows=288)
    -> Table scan on stationdetail  (cost=29.80 rows=288)

인덱스 생성 후의 explain

-> Filter: (substr(stationdetail.stationCode,(length(stationdetail.stationCode) - 1),1) = '1')  (cost=23.50 rows=205)
    -> Index lookup on stationdetail using ix_stationdetail_01 (left(stationCode,1)='2')  (cost=23.50 rows=205)

4. stationName과 usedDate의 일자로만 구성된 Function Based Index를 생성한 뒤, 해당 인덱스의 유무에 따른 아래 쿼리의 실행계획을 비교하세요.

SELECT date_format(usedDate, '%d'), sum(inCnt), sum(outCnt)
 FROM dailyusedcnt
WHERE date_format(usedDate, '%d') in ('01', '15')
 AND stationName='정발산'
GROUP BY date_format(usedDate, '%d');

CREATE INDEX ix_dailyusedcnt_02 ON dailyusedcnt(stationName, (date_format(usedDate, '%d'))) ;

인덱스 생성 전의 explain

-> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Filter: ((dailyusedcnt.stationName = '정발산') and (date_format(dailyusedcnt.usedDate,'%d') in ('01','15')))  (cost=29400.65 rows=29152)
            -> Table scan on dailyusedcnt  (cost=29400.65 rows=291519)

인덱스 생성 후의 explain

-> Group aggregate: sum(dailyusedcnt.inCnt), sum(dailyusedcnt.outCnt)  (cost=18.11 rows=6)
    -> Filter: ((dailyusedcnt.stationName = '정발산') and (date_format(usedDate,_utf8mb4'%d') in ('01','15')))  (cost=14.91 rows=32)
        -> Index range scan on dailyusedcnt using ix_dailyusedcnt_02 over (stationName = '정발산' AND date_format(`usedDate`,_utf8mb4'%d') = '01') OR (stationName = '정발산' AND date_format(`usedDate`,_utf8mb4'%d') = '15')  (cost=14.91 rows=32)