MySQL 하기 쉬운 실수와 Tip에 대한 소개

 

MySQL 사용하고 있지만 잘 모를 수 있는 부분들과 간단한 Tip에 대한 단편적인 내용들에 대한 정리한 내용이다.

 

정리 내용

# 인덱스 생성시 유니크 인덱스로 생성할 수 있는데, 유니크 인덱스는 제약사항일 뿐이다.

특별히 성능이 다른 인덱스보다 좋은 것은 아니다. (동일 컬럼 Non-unique 인덱스와 비교했을 때)

MySQL은 특이한게 모든 인덱스의 끝에 PK 컬럼이 추가된다.
그래서 많은 키로 PK를 구성하는게 좋지 않다.
PK를 구성하는 컬럼이 많다면 auto increment 컬럼을 PK로 설정하고 필요 시 다른 인덱스를 추가하는게 더 나은 선택이 될 수 있을 것 같다.

 

# 단일 컬럼 인덱스는 잘못된 설계인 경우가 많다.

되도록이면 컬럼을 중첩시켜 인덱스의 갯수를 적게 유지하는 것이 좋다.

 

# ORDER BY도 인덱스 사용을 통해 성능을 높일 수 있는데, 인덱스를 쓸 수 있는지 아닌지 헷갈릴 때는
인덱스 컬럼들을 ORDER BY 절에 다 나열했을 때 동일한 결과를 얻을 수 있는지 확인해본다.

 

# MySQL은 reverse index를 8.0부터 지원한다. 이전 버전에서는 인덱스를 DESC로 생성하더라도 실제로는 ASC로 만들어짐. (호환성을 위해 문법만 지원)

대안으로는 정렬용 컬럼을 별도로 만들어두는 것이 있다. (예, int형 컬럼에 -를 붙여서 저장하는 방법)

그래서 이전 버전에서는 ORDER BY 시 ASC, DESC 섞어서 정렬하면 인덱스 사용이 불가능하다.

 

# 레코드 생성 시간순 정렬이 필요한 경우 생성 시간 컬럼에 별도의 인덱스 생성하는 방법 외에 auto increment 컬럼을 PK로 두는 방법이 있다.

시간순으로 커지고 있으므로 정렬하면 동일한 결과를 얻을 수 있다.

 

# 가끔 매우 긴 문자열 컬럼에 대해 인덱스를 생성하거나 검색을 해야하는 경우가 있다. (URL 등)

MySQL에서 인덱스 키는 최대 767바이트라 인덱스 생성이 불가능할 수도 있다.

이럴 때는 긴 문자열의 해시값을 저장하는 컬럼을 추가하여 PRIMARY KEY나 인덱스를 생성하는 방법이 있다.

하지만 hash값이기 때문에 LIKE 검색은 할 수 없는 단점이 있다.

  CREATE TABLE tb_access_log (
    access_id BIGINT NOT NULL AUTO_INCREMENT,
    access_url VARCHAR(1000),
    access_url_hash CHAR(32), -- hash 컬럼 추가
    access_date DATETIME,
    PRIMARY KEY (access_id),
    INDEX ix_access_url_hash (access_url_hash)
  );

  INSERT INTO tb_access_log (access_url, access_url_hash, access_date)
  VALUES ('http://www.naver.com', MD5('http://www.naver.com'), NOW());

  SELECT * FROM tb_access_log
  WHERE access_url_hash = MD5('http://www.naver.com');

 

# InnoDB의 레코드 락은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리됨 (넥스트 키 락)

그래서 UPDATE, DELETE 시 변경 대상 레코드만 잠그는 것이 아니라, 변경해야할 레코드를 찾기위해 검색한 인덱스의 레코드가 모두 잠긴다.

CREATE TABLE employees (
  emp_no int NOT NULL,
  first_name varchar(14) NOT NULL,
  last_name varchar(16) NOT NULL,
  hire_date date,
  PRIMARY KEY (emp_no),
  KEY ix_first_name (first_name) -- first_name 컬럼으로만 인덱스 생성
);

-- first_name = 'Scott' 인 모든 레코드가 잠기게 된다
UPDATE employees SET hire_date = NOW()
WHERE first_name = 'Scott' AND last_name = 'Tiger';

만약 ix_first_name 인덱스도 없다면 테이블 풀 스캔하며 UPDATE 작업을 하는데
그러면 테이블의 모든 레코드가 잠기게 된다.

MySQL InnoDB에서 인덱스 설계가 중요한 이유임.

 

P.S. DB 설정을 통해 대부분의 갭락, 넥스트 키 락을 없애는 방법도 존재하지만, 그럴 경우 얻는 것이 있으면 잃는 것도 있기 때문에 무조건 그렇게 설정하는게 좋은 것은 아니다. REPEATABLE-READ 에서는 위와 같이 동작하니 주의할 필요가 있다.

 

위와 같은 상황을 회피하기 위해서는 다음 정도의 방법을 생각해볼 수 있다.

1. first_name, last_name 컬럼으로 인덱스 추가

2. first_name, last_name 조건으로 emp_no 조회하여 emp_no 조건으로 업데이트

SELECT emp_no FROM employees WHERE first_name = 'Scott' AND last_name = 'Tiger'; -- 3
UPDATE employees SET hire_date = NOW() WHERE emp_no = 3;

 

# 특정 레코드 변경 집중 시 동시성을 높일 수 있는 방법

필요할 수 있는 경우는 온라인 쇼핑의 재고 & 판매 아이템 수, 이벤트의 쿠폰 개수 관리 등 많은 사용자들이 동일 이벤트에 참여하는 경우가 해당된다.

이런 경우 카운터 값 업데이트 작업이 특정 레코드로 집중되는데 특정 레코드 변경은 초당 200~300건 이상 처리가 어렵다.

그래서 동시성을 높이기 위해 다음과 같은 방법이 있다.

• 카운터 분산을 위해 카운터를 저장하는 레코드를 5~10개 생성
• 카운터 변경 시 랜덤하게 업데이트
• 카운터 조회 시는 SUM()해서 표시

CREATE TABLE coupon_counter (
    event_id INT,
    rand_id INT,
    counter INT,
    ...
    PRIMARY KEY (event_id, rand_id)
);

-- update
UPDATE coupon_counter SET counter++
WHERE event_id = ? AND rand_id = FLOOR(RAND() * 6);

-- select
SELECT SUM(counter) FROM coupon_counter
WHERE event_id = ?;

DB로만 해결하지 않고 카운터를 Memcached나 Redis로 처리하는 방법도 있으니 상황에 맞게 적용하면 된다.

 

# DISTINCT는 SELECT 하는 레코드를 유니크하게 SELECT 하는 것이지 컬럼을 유니크하게 SELECT하는 것이 아니다.

(DISTINCT는 함수가 아님)

다음 두 쿼리의 결과는 동일하다.

SELECT DISTINCT(first_name), last_name FROM employees;

SELECT DISTINCT first_name, last_name FROM employees;

 

# OUTER JOIN에서 조인되는 레코드가 없을 수도 있는 테이블에 대한 조건은 반드시 JOIN의 ON 절에 모두 명시하자.

OUTER JOIN ON 절의 조건은 조인할 레코드에 대해서만 적용되지만, WHERE 절 명시 조건은 조인 결과에 모두 적용되기 때문이다.

이로 인해 의도치 않은 결과가 나올 수 있다.

 

# "=" 연산자에 부가적으로 NULL 값에 대한 비교도 하고 싶으면 "<=>" 연산자를 사용할 수 있다.

SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL; -- 1 | 1 | 0

SELECT 1 = 1, NULL = NULL, 1 = NULL; -- 1 | NULL | NULL

 

# LIKE 비교 시 만약 와일드카드 문자(%, _) 자체를 비교하고 싶다면 ESCAPE 절을 LIKE 조건 뒤에 추가해야 한다.

-- name에 _가 들어간 car가 조회됨
SELECT * FROM car WHERE name LIKE '%/_%' ESCAPE '/';

-- name이 1글자 이상인 car가 조회됨
SELECT * FROM car WHERE name LIKE '%_%';

 

# MySQL의 GROUP BY는 기본적으로 그룹핑 컬럼 순서대로 정렬까지 수행한다.
GROUP BY 결과 건수가 많은데 정렬이 필요없다면 ORDER BY NULL 구문으로 정렬을 하지 않도록 할 수 있다.

SELECT hire_date, COUNT(*) FROM employees GROUP BY hire_date ORDER BY NULL;

 

참고 내용

• Real MySQL 책