varchar 255가 넘는 인덱스 추가

mysql 은 기본 defult character 셋을 utfmd4로 설정해놓았다.

다만 이모티콘등을 사용하지 않는데 굳이 utfmd4를 써서 자원을 낭비할 필요가 없다.

대부분의 테이블은 utf8 3Byte로 사용하고 있고

 

테이블 생성 character set = utf8 이다.

이렇게 설정할 경우 varchar 255 * 3 = 762 바이트(해더를 뺀 254 *3)가 넘는 데이터 컬럼은 인덱스 생성이 안된다.

 

mysql> ALTER TABLE entry ADD UNIQUE INDEX (name(255));
ERROR 1071 (42000): Specified key was too long; max key length is 762bytes

 

하지만 reg_id등 암호화된 데이터를 갖은 컬럼도 인덱스 생성이 필요할 경우가 있다.

이럴 가능하게 하려면 일단 mysql 의 시스템 환경변수를 확인해야 한다.

 

1) 지원가능여부확인 

#mysq. 5.7 mysql> show variables like 'innodb_file_format'; +--------------------+-----------+ | Variable_name      | Value     | +--------------------+-----------+ | innodb_file_format | Barracuda | +--------------------+-----------+ 1 row in set (0.00 sec) mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name         | Value | +-----------------------+-------+ | innodb_file_per_table | ON    | +-----------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'innodb_large_prefix'; +---------------------+-------+ | Variable_name       | Value | +---------------------+-------+ | innodb_large_prefix | ON    | +---------------------+-------+ 1 row in set (0.00 sec) #보통 mysql 5.7은 defualt로 전부 가능하게 설정되어있다. #가장 좋은 성능튜닝은 version up 이다.ㅎㅎ # mysql 5.5를 확인해본다. mysql> show variables like 'innodb_file_format'; +--------------------+----------+ | Variable_name      | Value    | +--------------------+----------+ | innodb_file_format | Antelope | +--------------------+----------+ 1 row in set (0.00 sec) mysql> show variables like 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name         | Value | +-----------------------+-------+ | innodb_file_per_table | OFF   | +-----------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'innodb_large_prefix'; +---------------------+-------+ | Variable_name       | Value | +---------------------+-------+ | innodb_large_prefix | OFF   | +---------------------+-------+ 1 row in set (0.00 sec) #불가능하다. 지원하지 않는다. #mysql 5.7 버전부터 지원하며 5.6의경우 특정 버전 이상부터 지원하는 것으로 알고 있다.

 

2) 인덱스 생성

1) 임시테이블생성(바라쿠다엔진선택) 아래처럼 임시테이블을 만들고 rename으로 테이블 교체 CREATE TABLE `TMP_LOAD_20180605` (   ... ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='테스트';   바라쿠다엔진은 여러 format이 있는데 테이블의 사용 패턴에 따라 다양하게 설정하면 된다. 해당테이블은 update가 빈번히 일어나서 Dynamic으로 설정하였다. 실시간 데이터 연동테이블이라 Rename으로 교체작업을 진행하였다. 3) unique 인덱스 생성 alter table TMP_LOAD_20180605 add unique index IDX_LOAD_20180605_02 (REGID);   잘된다...^^ 이제 성능이슈는 한동안.. byebye...