BackendSquid/RealMySQL

Ch15 데이터 타입 - 문자열

Closed this issue · 6 comments

kihyuk-sung commented

p356 ~ p382 문자열

ch14 는 준비하고 싶은 사람만
p326 ~ p354

sanhee commented

CHAR와 VARCHAR

  • 공통점
    • 문자열을 저장할 수 있는 데이터 타입
  • 차이점
    • 고정 길이 / 가변 길이
      • 가변 길이는 저장된 값의 유효 크기가 얼마인지 별도로 저장해 둬야 하므로 추가적인 저장 공간이 필요하다.
  • 선택 기준
    • 해당 칼럼의 값이 얼마나 자주 변경되는가?
      • 가변 길이의 경우 길이가 더 큰 값으로 변경될 때 레코드 자체를 다른 공간으로 옮겨서 저장해야 한다.
      • 주민등록번호, 부서번호, 게시물의 상태 값 등은 CHAR 타입을 사용하는 것이 좋다.
        • 자주 변경돼도 레코드가 물리적으로 다른 위치로 이동하거나 분리되지 않아도 되기 때문
  • 주의사항
    • CHAR, VARCHAR 키워드 뒤에 인자로 전달하는 숫자 값의 의미는 칼럼 바이트 크기가 아니라 문자의 수를 의미한다.
      • CHART SET에 따라 실제로 디스크나 메모리에서 사용하는 공간은 각각 달라진다.
    • VARCHAR 타입의 길이가 크게 변경될 것으로 예상된다면 길이 저장 공간의 크기가 바뀌지 않도록 미리 조금 크게 설계하는 것이 좋다.
      • 읽기 잠금을 필요로 하는 스키마 변경을 실행하는 것은 서비스의 가용성을 훼손

Character Set (문자집합)

  • 문자 집합 은 문자, 숫자, 문장 부호, 공백 등의 문자들을 표현하는 방법을 컴퓨터에 알려주는 인코딩 시스템
  • MySQL 서버에서 각 테이블의 칼럼은 모두 서로 다른 문자 집합을 사용해 문자열 값을 저장할 수 있다.

Collation (콜레이션)

  • 문자열 칼럼의 값에 대한 비교나 정렬 순서를 위한 규칙
    • 콜레이션 일치 여부에 따라 비교, 정렬 결과가 달라지며 쿼리 성능 또한 상당한 영향을 받는다.
    • 문자 집합이나 콜레이션이 다르면 비교 작업에서 콜레이션 변환 작업이 필요하기 때문에 인덱스를 효율적으로 이용하지 못할 수 있음.
  • MySQL의 모든 문자열 타입의 칼럼은 독립적인 문자 집합과 콜레이션을 가진다.
  • 콜레이션이 대소문자를 구분하지 않는다고 해서, 칼럼에 변환돼서 저장되는 것은 아님.
    • 비교할 때 해당하는 콜레이션 값으로 매칭시킨 다음 비교 수행
  • 문자집합이나 콜레이션 설정 종류
    • DB 수준 / 테이블 수준 / 칼럼 수준
  • WHERE 조건의 검색은 대소문자를 구분하지 않고 실행하되, 정렬은 대소문자를 구분해서 사용해야 하는 경우
    1. 검색과 정렬 작업 중 하나는 인덱스를 이용하는 것을 포기
    2. 정렬을 위한 콜레이션을 사용하는 칼럼을 하나 더 추가하고 검색은 원본 칼럼을, 그리고 정렬은 복사된 추출 칼럼을 이요하는 방법
  • 👍5
kihyuk-sung commented

15. 데이터 타입

적절한 칼럼의 데이터 타입 선정은 매우 중요한 작업

  • 저장되는 값의 성격에 맞는 최적의 타입을 선정
  • 가변 길이 칼럼은 최적의 길이를 지정
  • 조인 조건으로 사용되는 칼럼은 똑같은 데이터 타입으로 선정

15.1 문자열 (CHAR와 VARCHAR)

  • 가변 길이 (유효 크기를 위해 1 ~ 2 byte 정도 추가 필요, 최대 65,536 byte)
  • 고정 길이
  • 참고) 레코드당 데이터 크기 전체는 64KB를 넘을 수 없다.
  • 고정길이의 경우, 값 변경 시 해당 레코드 데이터를 변경만 하면 된다.
  • 가변 길이의 경우, 레코드를 다시 추가로 저장해야 한다.
  • 그러므로 매우 자주 변경되면서 길이가 일정한 경우는 고정 길이로 저장하면 유리하다.
  • VARCHAR() 는 글자 수

15.1.2 저장 공간과 스키마 변경(Online DDL)

  • VARCHAR 변경 시 길이에 따라 실제 사용하는 저장공간 (길이를 표현하는 공간) 이 변경되면 Online DDL Copy 알고리즘이 적용될 수 있다.

15.1.3 문자 집합

  • utf8mb4 (이모지 포함 다국어 지원, 1~ 4 byte)
  • 클라이언트 / 서버 별로 문자 집합 설정이 가능하므로, 이후 요청 시 변경 작업이 필요할 수 있다.

15.1.4 콜레이션(Collation)

  • 문자열 칼럼의 값에 대한 비교나 정렬 순서
  • case sensitive
  • accent sensitive

15.1.4.2 utf8mb4 문자 집합의 콜레이션

  • 언어 종속 유무가 있다.

15.1.5 비교 방식

  • UCA 버전 등에 따라 공백에 대한 비교 규칙이 달라진다.
  • PAD_ATTRIBUTE 로 확인 가능

14. 스토어드 프로그램

14.2.6.4 핸들러와 컨디션을 이용한 에러 핸들링

  • ERROR-NO (MySQL, engine 별로도 다를 수 있음) 와 SQL-STATE ( 표준 )
  • Handler (Error 뿐만 아니라 다른 경우도 핸들)
  • 컨디션 : 커스텀으로 정의 가능

14.2.6.5 시그널을 이용한 예외 발생

  • 직접 예외 발생 가능
  • 시그널은 SQLSTATE와 직접 또는 간접적으로 연결되어야 한다.

14.2.6.6 커서

  • 센서티브(레코드의 포인터)
  • 인센서티브(임시 테이블 복사후 사용)
  • 어센서티브(둘다 사용)
  • MySQL 스토어드 프로그램 내의 커서는 Asensitive

14.3 스토어드 프로그램의 보안 옵션

  • 8.0 부터는 CREATE ROUTINE / ALTER ROUTINE / EXECUTE 로 분리
  • DEFINER (소유권)
  • SQL SECURITY , INVOKER / DEFINER
  • DETERMINISTIC : 입력이 같으면 결과도 같다
  • NOT DETERMINISTIC : 입력이 같아도 결과가 다를 수 있다.

14.4 스토어드 프로그램의 참고 및 주의 사항

  • 한글 처리 (클라이언트가 한글 등을 지원하는 연결을 하지 않으면 깨질 수 있음)
  • 사용자 변수는 주의하여 사용 (세션 내에서 유지된다.)
  • 재귀 호출
  • 중첩된 커서 사용
  • 👍3
polynomeer commented

15. 데이터 타입

물리 모델링에서 데이터 타입 선정은 중요

  • 저장되는 값의 성격에 맞는 타입 선정 ->
  • 가변 길이 칼럼은 최적의 길이 지정 -> 자원 절약 및 성능 향상
  • 조인 조건 대상 칼럼은 같은 타입으로 선정

15.1 문자열(CHAR와 VARCHAR)

항목 CHAR VARCHAR
저장공간 - 고정적, 추가 공간 필요x
- 저장되는 문자열 길이가 비슷할 때 유리
- 칼럼의 값이 자주 변경되는 경우 유리		 - 가변적, 유효 크기를 저장할 추가 공간(1~2byte) 필요
- 저장되는 문자열 길이가 비슷할 때 유리
- 칼럼의 값이 자주 변경되지 않는 경우 유리
Online DDL - CHAR(60) -> CHAR(63) or CHAR(64): 길이정보를 저장하지 않으므로 잠금이 되지 않을 것 - VARCHAR(60) -> VARCHAR(63): 잠금없이 빠른 변경
- VARCHAR(60) -> VARCHAR(64): 길이저장공간 1byte -> 2byte로 변경해야해서 잠금
- 예상되는 최대 크기로 설정하는게 좋음

문자 집합(캐릭터 셋)

  • MySQL 서버, DB, 테이블 단위로 기본 문자 집합 설정 가능
  • utf8mb4: 다국어 문자 포함에 적절, 디스크 저장 시 1~4byte 사용, 메모리 사용 시 4byte 고정 할당
    • utf8(utf8mb3): 한 글자 당 3바이트 사용, 제거될 예정(deprecated)
    • utf8mb4: 한 글자 당 4바이트 사용, utf8로 불릴 예정
  • 문자 집합 설정 시스템 변수
    • character_set_client: MySQL 클라이언트가 보낸 SQL 문장의 캐릭터 셋
    • character_set_connection: MySQL 서버가 클라이언트로부터 전달받은 SQL 문장을 처리하기 위해 해당 캐릭터 셋으로 변환
    • character_set_results: MySQL 서버가 쿼리의 처리 결과를 클라이언트로 보낼 때 사용하는 문자 집합을 설정
    • 세 개의 시스템 변수는 세션 변수이면서 동적 변수 -> 클라이언트에서 마음대로 변경 가능
  • 인트로듀서: SQL 문장에서 별도로 문자 집합을 설정하는 지정자, 타입캐스팅과 비슷함 (SELECT * from employees WHERE first_name = _latin1'Matt';)

콜레이션

  • 문자열 칼럼의 값 비교 or 정렬 순서를 위한 규칙. 자바의 Comparator같은 역할
  • 모든 문자열 타입의 칼럼은 독립적인 캐릭터셋과 콜레이션을 가짐
  • 캐릭터셋은 2개 이상의 콜레이션을 가짐. 하나의 캐릭터셋에 포함된 콜레이션은 다른 캐릭터셋과 공유x
  • 명시하지 않으면 캐릭터셋 -> 콜레이션, 콜레이션 -> 캐릭터셋 묵시적으로 지정
  • {문자집합}_{해당 문자집합 하위 분류}_{대소문자(ci/cs)} *ci: case insensitive, cs: case sensitive: ex) utf8mb4_unicode_ci
  • {문자집합}_{bin(고정, 별도의 콜레이션x, 바이트값 기준 정렬)}: ex) utf8_bin
  • 입력된 데이터는 대소문자 변환없이 저장
  • 저장된 바이트 값은 직접적인 비교대상x -> 인코딩된 바이트 값에 해당하는 콜레이션 값으로 비교
    • 문자열 비교, 정렬을 해야하는 쿼리 실행 시 유의해야할 것!
  • MySQL 5.7에서 기본 콜레이션 utf8mb4_general_ci -> MySQL 8.0 기본 utf8mb4_0900_ai_ci로 변경

비교 방식

  • utf8mb4_bin은 뒷 공백 무시하고 비교, utf8mb4_0900_bin는 뒷 공백 포함해서 비교
  • 문자열 뒤 공백이 비교에 영향을 미치는지 information_schema.COLLATIONS.PAD_ATTRIBUTE 칼럼 값으로 판단

웬만하면 비교 대상이 되는 칼럼의 값은 공백을 제거하고 애플리케이션 레벨에서 처리하는게 안전한 것 같다.

14. 스토어드 프로그램

핸들러와 컨디션을 이용한 에러 핸들링

스토어드 프로그램 유사한 자바 키워드
핸들러 try ~ catch
컨디션 exception
시그널 throw

커서

JDB 프로그램에서 자주 사용하는 ResultSet과 비슷하지만 제약적

  • 센서티브 커서: 실제 레코드의 포인터만으로 유지, 빠름
  • 인센서티브 커서: 별도의 임시 테이블로 복사해서 유지, 느림

14.3 스토어드 프로그램 보안 옵션

  • 생성 및 변경 권한
    • MySQL 8.0 이전: SUPER -> MySQL 8.0 이후: CREATE ROUTINE, ALTER ROUTINE, EXECUTE로 분리
  • 보안 옵션
    • DEFINER: 기본 옵션, 실행 권한
    • SQL SECURITY: 실행 시점에 누구의 권한(INVOKER: 실행자 권한 or DEFINER: 관리자 권한(sudo))으로 실행할지 결정
  • 성능 옵션
    • DETERMINISTIC: 입력이 같다면 시점이나 상황에 관계없이 결과가 항상 같다
    • NOT DETERMINISTIC: 입력이 같아도 시점에 따라 결과가 달라질 수도 있다

14.4 스토어드 프로그램의 참고 및 주의사항

  • 한글 처리: 소스코드 자체에 한글이 있거나 클라이언트의 커넥션에서 캐릭터셋 설정 주의
  • 스토어드 프로그램과 세션 변수: prepared statement를 제외하고는 세션 변수 사용을 지양하자, 로컬 변수를 사용하자
  • 스토어드 프로시저와 재귀 호출: SOF 조심, 최대 호출 개수 지정 가능
  • 중첩된 커서 사용: 두 개의 커서를 동시에 열어서 사용할 시 예외 핸들링 주의
  • 👍4
janeljs commented

15.1 문자열

  • varchar와 char 선택기준
    • 저장되는 문자열의 길이가 비슷한지
    • 칼럼의 값이 자주 변경되는지
  • varchar에서는 길이가 변경되면 row migration이 일어남
  • utf8mb4보니까 생각남
  • alter할 때 varchar 64로 늘리면 inplace 알고리즘 못쓰고 copy 써야함
    • 문자열 길이 저장하는 공간이 2바이트로 늘기 때문
  • 아하 뒤에 charset이랑 collation이 나오는군요?
    • LOAD DATA INFILE 명령을 실행할 때 자꾸 문제가 생겼었는데 charset이 utf8mb4로 설정되어 있지 않아서였을까? (기본값 binary)
  • 인트로듀서 사용 후에는 다시 원래 charset으로 쿼리 실행됨
  • 시스템 설정 변수 중 character_set_client, character_set_results, character_set_connection 3개는 세션변수이면서 동적변수라는 의미?
  • collation ci = canse insensitive
  • charset+collation 모두 일치해야 같은 타입임
  • 대소문자 구분 안하고 검색하고 정렬은 구분해서 하려면 _ci 로 칼럼 지정해서 검색하고 정렬(정렬은 인덱스 안 탐)
  • Locale 종속적인 콜레이션도 있음(보통은 ai)
  • 8.0부터는 기본 collation이 utf8mb4_0900_ai_ci
  • 5.7 기본 콜레이션 테이블과 조인하면 성능 저하가 있다함.. default_collation_for_utf8mb4로 일단 호환성 맞출 수 있음
  • collations 뷰 - PAD_ATTRIBUTE 컬럼 값으로 문자열 뒤 공백 포함해서 비교할건지 아닐지 선택할 수 있음
  • 👍3
ghojeong commented

데이터 타입

스키마 변경 작업은 서비스 중지의 위험이 있음,
따라서 최적의 타입과 길이를 선정하는게 중요

문자열(CHAR와 VARCHAR)

MySQL5.5 부터 utf8mb4 인코딩 사용

저장공간

  • CHAR: 고정 길이
  • VARCHAR: 가변길이

결론: 문자열의 길이기 비슷하고, 칼럼값이 자주 변경되면 CHAR를 사용하자.

저장 공간과 스키마 변경(Online DDL)

Online DDL: 데이터가 변경되는 도중에 스키마 변경을 해줄수 있는 기능

문자열 길이를 변경하는 동안 읽기 잠금이 걸리지 않도록 조심하자.

문자 집합(캐릭터 셋)

CHAR, VARCHAR,TEXT 에 문자 집합 설정 가능.

SHOW CHARACTER SET 명령어로 문자집합 확인 가능

대부분 utfmb4 로 통일하는 것 만으로 충분할 듯

클라이언트로부터 쿼리를 요청했을 때의 문자 집합 변환

MySQL 서버는 character_set_client 에 지정된 문자집합으로 인코딩을 판단

인트로듀서 를 통해 쿼리에서 문자집합 설정 가능

처리 결과를 클라이언트로 전송할 때의 문자집합 변환

  • SET character_set_client = 'utf8mb4'
  • SET character_set_results = 'utf8mb4'
  • SET character_set_connection = 'utf8mb4'

위의 3가지 를 설정할 필요가 있음

Collation

collation: 비교와 정렬 순서를 위한 규칙

Collation 의 이해

SHOW COLLATION 명령어로 확인 가능

크게 아래 2가지의 Collation 이 존재한다.

  • 문자 집합과 공유해서 사용할 수 없는 Collation
  • 묵시적 Collation

나라 언어별로 많은 Collation 이 존재한다.

multi-language 서비스를 개발할 경우 고려해서 봐야할듯 하다.

  • 👍3
jihye-woo commented

  • 칼럼의 데이터 타입과 길이 선정 체크 포인트

    • 저장되는 값의 성격에 맞는 최적의 타입
    • 가변길이 컬럼은 최적의 길이를 지정
    • 조인 조건으로 사용되는 컬럼은 똑같은 데이터 타입으로 선정

  • varchar 타입은 저장된 값의 유효크기가 얼마인지 저장하기 위해 1~2바이트 추가로 필요

  • char VS varchar 선택

    • 문자열의 길이가 보통 비슷한지
    • 값 업데이트 빈도
      • 왜 중요? varchar 사용할 경우 길이가 더 큰값으로 업데이트 시 뒷 레코드 이동해서 저장(row migration)

  • varchar 데이터 타입을 사용하는 컬럼의 길이 늘리는 작업

    • bad case : 테이블에 읽기 잠금 걸고서 레코드 복사

  • MySQL 내에서도 각 영역별로 사용하는 문자 집합이 다르고, 문자 집합을 설정하는 다양한 시스템 변수 존재 (364p)

  • Collation의 정의

    • 문자열 칼럼의 값에 대한 비교나 정렬 순서를 위한 규칙
    • 왜 알아야해? 문자열 칼럼의 값을 비교하거나 정렬할 때는 항상 문자 집합뿐 아니라 콜레이션의 일치 여부에 따라 결과 달라짐, 쿼리 성능 영향

  • 문자열 공백이 비교 결과에 영향을 미치는지 여부는 collation 마다 다름

    • information_schema DB의 COLLATIONS 뷰에서 PAD_ATTRIBUTE 칼럼 확인
      • PAD SPACE : 비교 대상 문자열 길이가 같아지도록 문자열 뒤에 공백 추가

  • utf8mb4 문자 집합 아래도 여러 종류의 콜레이션 존재

    • 대소문자에 따라서 달라짐 (비교 정렬 있 :_cs / 없 : _ci)
    • 콜레이션 비교 알고리즘 버전에 따라 콜레이션 이름과 숫자 달라짐
    • 언어 종속적인 콜레이션일 경우, Locale 정보가 콜레이션 이름에 포함

  • MySQL 8.0 이전 버전 쓰다가 MySQL 8.0 넘어갈 경우 주의사항

    • utf8mb4 문자 집합 기본 Collation 버전업되면서 변경
      • (MySQL 5.7 버전)utf8mb4_general_ci -> (MySQL 8.0) utf8mb4_0900_ai_ci
    • 설정 파일(my.cnf)에 Collation 관련 설정을 특하게 추가하지 않을 경우 각 버전별 기본 Collation 값으로 설정
    • MySQL 5.7 버전일 때 생성된 테이블과 MySQL 8.0 이후 버전에서 생성된 테이블의 Collation 달라질 수 있음
    • 이런 경우, 다른 Collaction으로 설정된 두 테이블을 조인하게 될 위험 -> 에러 혹은 성능 이슈 발생
      • sol ) MySQL 설정 파일(my.cnf)에 콜레이션 관련 시스템 변수를 고정시는 것으로 충돌 이슈 해소 가능
        • ) JDBC 드라이버 연결 문자열에서 connectionCollation=utf8mb4_general_ci 설정 (?보통 설정 하나요?)

  • Where 조건으로 검색, 정렬 조건이 다를 경우

    • 요청 사항 : 1) 검색은 대소문자 구분X, 2) 정렬은 대소문자 구분O, 3) 인덱스 사용하고싶
    • 정렬용 콜레이션 컬럼 하나 더 추가해서, 검색은 원본컬럼, 정렬은 복사된 추출칼럼 사용
      • 컬럼별로 다른 콜레이션 지정 가능

  • 명령어

 -- 특정 테이블의 collation, charset 확인 명령어
   SELECT table_name, column_name, column_type, character_set_name, collation_name
   FROM information_schema.columns
   WHERE table_schema='test' AND table_name='tb_collate';

 -- 전체 Collation 정보 조회
 SHOW COLLATION;
  • 👍2