1.MySQL의 특징

1.1 MySQL은 전체적으로 어떻게 생겼나

1.1.1 서버엔진

DB가 SQL을 이해할 수 있도록 쿼리를 재구성하는 ‘쿼리 파싱’ 과 디스크나 메모리 같은 물리적인 저장장치와 통신하는 스토리지 엔진에 데이터를 요청하는 업무를 담당

또한 스토리지 엔진에서 받아온 데이터를 사용자 요청에 맞게 처리하거나 접근 제어, 쿼리 캐시, 옵티마이저 등의 역할을 수행한다. 즉, 물리적인 저장장치와 직접적으로 통신하는 역할을 제외하고 사용자와 MySQL 사이에서 발생하는 데이터 처리 프로세스의 대부분을 담당한다

스토리지 엔진이 물리적인 저장장치에서 데이터를 읽어오는 역할을 수행한다면, 서버엔진은 스토리지 엔진에서 가져온 데이터를 처리하는 역할을 담당한다. Table join Group By Order By 등 Function Trigger Constraint 전부다 여기서 함.

1.1.2 스토리지 엔진(데이터베이스 엔진이라고도함)

다른 DBMS와는 다르게 스토리지 엔진이 플러그인 방식으로 동작. 그래서 여러개 엔진을 설치해서 사용가능

서버 엔진은 스토리지 엔진의 API를 호출하여 실제 필요한 데이터를 요청하고 조작함. 그래서 API에는 구현되어 있지만 스토리지 엔진에서는 지원하지 않은 경우도 있음

위의 경우 설명

예를 들어, InnoDB 엔진의 버전 및 설정, 테이블의 크기 및 데이터 분포 등에 따라서 해시 인덱스의 효율성이 다를 수 있음. 때때로 InnoDB 엔진은 데이터 분포가 일정하지 않거나 인덱스의 크기가 커지는 상황에서 B-트리 인덱스를 선택할 수 있음.
따라서 InnoDB 엔진에서 해시 인덱스로 정의했더라도 실제로는 B-트리 인덱스로 생성될 수 있는 것이며, 이는 데이터베이스 엔진 내부에서 최적화된 인덱스 형식을 선택하는 결과. 이러한 동작은 데이터베이스 시스템의 성능과 효율성을 고려하여 이루어지며, 사용자가 직접 선택하는 것이 아니라고함
InnoDB 엔진에서는 특정 조건에서 해시 인덱스 대신 B-트리 인덱스를 사용하게 될 수 있음. 해시 인덱스는 일반적으로 매우 빠른 검색 속도를 제공하지만, 몇 가지 제약 사항이 있을 수 있다. 이로 인해 InnoDB 엔진은 몇 가지 상황에서는 B-트리 인덱스를 사용하여 해시 인덱스 대신에 사용.

MySQL은 다른 사용 DBMS와는 다르게 DB엔진에 완벽하게 최적화되어 있지 않음. 다양한 스토리지 엔진에서 동작할 수 있어햐하기 때문 그만큼 유연함 확장성 있음.

다양한 스토리지 엔진들이 있음

InnoDB
MyISAM
Archive
MRG_MYISAM
BLACKHOLE
CSV 등등

1.2 MySQL에서 스토리지 엔진이란 무엇인가?

출처https://gywn.net/2011/12/mysql-three-features/

1.2.1 MyISAM 스토리지 엔진

가장 오래됨. 파일 기반 스토리지 엔진이며 데이터에 대한 키, 즉 인덱스만 메모리에 올려서 처리.데이터는 메모리에 적재하지 않고 디스크에서 바로 접근. 트랜잭션을 지원하지 않고 테이블 단위 잠금으로 데이터 변경을 처리. 따라서 특정 테이블의 여러 세션에서 데이터를 변경하면 성능 저하. 텍스트 전문을 검색할 수 있는 풀텍스트 인덱싱과 지리 정보를 처리할 수 있는 지오메트릭 스파셜 인덱싱 같은 기능도 제공 근데 위의 기능 사용하면 테이블 파티셔닝을 사용할 수 없다는 제약이 있음. 애초에 MySQL은 저사양 서버에서 구동이 목적이어서 다양한 기법으로 사이즈를 줄임. 그 대표적인 방법이 프리픽스 인덱스 압축 기법으로 키 사이즈를 최소화한다. prefix 인덱스 압축 사용하면 키 사이즈 줄어들고 메모리 효율 높아짐 근데 키 역순으로 데이터를 찾아야 할 경우에는 성능 이슈가 있음. 그래서 MyISAM은 로그 수집 또는 단순 SELECT에는 적합하나 동시 데이터 처리에는 한계가 있음

Full-Text Indexing

풀텍스트 인덱싱은 텍스트 데이터(문자열 데이터)를 빠르게 검색하기 위해 사용되는 기술입니다. 이를 통해 데이터베이스에서 전문 검색(full-text search)을 수행할 수 있습니다. 풀텍스트 인덱스를 사용하면 단어의 형태소 분석, 불용어 처리, 단어의 등장 빈도 등을 고려하여 효율적인 텍스트 검색을 제공할 수 있습니다. MySQL과 같은 데이터베이스 시스템에서는 풀텍스트 검색을 위한 특별한 인덱스를 제공하며, 풀텍스트 검색 함수 및 연산자를 사용하여 복잡한 텍스트 검색을 수행할 수 있습니다

-- 풀텍스트 인덱스 생성
CREATE FULLTEXT INDEX idx_fulltext_content ON articles(content);

-- 풀텍스트 검색
SELECT title FROM articles WHERE MATCH(content) AGAINST('important keyword');

Geometric Spartial Indexing

-- 지오메트릭 스파셜 인덱스 생성 
CREATE SPATIAL INDEX idx_spatial_location ON locations(location); 
-- 주변 위치 검색
SELECT name FROM locations WHERE ST_Distance(location, POINT(37.123, -122.456)) < 10;

지오메트릭 스파셜 인덱싱은 공간 데이터(지리 정보)를 효율적으로 검색하기 위해 사용되는 기술입니다. 이는 지리 정보를 가진 위치 데이터를 인덱스화하여 위치 기반 검색 및 분석을 수행하는데 사용됩니다. 지오메트릭 스파셜 인덱스를 사용하면 공간 데이터를 효율적으로 관리하고 위치 기반 쿼리를 빠르게 수행할 수 있습니다. 이는 지리 정보 시스템(GIS) 및 위치 기반 서비스(LBS)와 같은 분야에서 주로 사용됩니다.

1.2.2 InnoDB 스토리지 엔진

MySQL 에서 유일하게 트랜잭션을 지원하는 스토리지 엔진, 일반적인 서비스에서 가장 많이 사용됨. 다중버전 동시성 제어 메커니즘(MVCC) 을 제공하고 행 단위 잠금으로 데이터 변경 작업을 수행하기 때문에 연관 없는 데이터를 다른 사용자가 변경가능. MyISAM은 인덱스만 메모리에 올리지만 InnoDB는 인덱스와 데이터를 모두 메모리에 올림. 메모리에 인덱스와 데이터가 적재되어 있어서 메모리 버퍼 크기가 성능에 큰 영향을 줌

1.2.3 Archive 스토리지 엔진

로그 수집에 적합. 데이터가 메모리상에서 압축되고 압축된상태로 디스크에 저장되어서 행 단위 잠금이 가능. 단, 한 번 INSERT된 데이터는 UPDATE와 DELETE를 사용할 수 없으며 인덱스를 지원하지 않음.원시로그, 즉 가공이 한 번 필요한 데이터 수집에는 효율적

1.3 MySQL은 데이터를 어떻게 처리할까?

1.3.1 MySQL 에서는 모든 SQL을 단일 코어에서 처리

MySQL은 SQL을 병렬 처리하지 않는다. 3rd Party 스토리지 엔진을 설치하여 병렬 처리 할 수는 있음.

MySQL입장에서는 CPU 코어 개수를 늘리는 Scale-Out 보다는 단위 처리량이 좋은 CPU로 Scale-Up하는 게 훨씬 좋음

1.3.2 MySQL 은 테이블 조인을 Nested Loop Join 알고리즘으로만 처리한다

하나씩 다 비교해 가면서 조건 맞으면 조인하는 방식임. 인덱스에 의한 랜덤 액세스에 기반하고 있어서 대량의 데이터 처리 시 적합하지 않음

근데 DB내부에서는 이것보다 업그레이드된 Block Nested Loop Join 방식으로 처리함. 테이브 조인 시 필요한 데이터를 메모리에 일시적으로 저장하여 효율적으로 데이터 접근함.

(최근 사항 MySQL 8.0.18에서 해쉬 조인이 추가되었다고함)

Block Nested Loop Join
간단한 예를 들면 Driving 테이블의 결과 루프의 결과 집합에는 1000 행의 데이터가 있고 Driven 테이블(내부 테이블,Inner 테이블) 은 NLJ(Nested-Loop Join) 알고리즘을 사용하게 되면 1000 번 스캔 해야 하지만 BNL 알고리즘을 사용하는 경우 예를들어 100개 행 집합을 비교한다면 먼저 Driving 테이블 결과를 조인 버퍼에 설정하고 저장 한 다음 Driven 테이블의 각 데이터 행을 사용하여 100 개 행의 결과 집합과 비교합니다.Driving 테이블의 row를 조인 버퍼에 더이상 데이터를 채울 수 없는 시점, 즉 Driving 테이블 조건에 해당하는 데이터를 모두 처리할 때까지 반복 수행합니다. 여기서 조인되어 지는 Driven(후행 테이블)의 스캔하는 횟수는 조인 버퍼에 데이터가 적재되는 횟수와 동일하게 됩니다.
한 번에 100 개의 데이터 행과 비교할 수 있으므로 내부 테이블에는 실제로 1000/100 = 10 회 로 루프 순환 하면서 NLJ보다 9/10 감소하게 되는 것입니다.
BNL 알고리즘의 원리를 다시 설명 드리면 Driving 테이블의 결과를 조인 버퍼에 저장하고 메모리 루프의 각 데이터 행을 전체 버퍼의 레코드와 비교하여 내부 루프의 스캔 횟수를 줄일 수 있습니다.

MySQL 에서는 단일 코어에서 Nested Loop Join 방식으로 데이터를 처리한다는걸 기억해야함

2. 쿼리 성능 진단은 최적화의 기초

2.1 쿼리 실행 계획에 대해서 알아보자

성능 진단의 첫걸음은 실행한 SQL이 DB에서 어떻게 처리되는지를 파악하는것. SQL로 명령을 내리면 내부적으로 파싱해서 옵티마이징 한후 실제로 데이터를 찾음

쿼리 실행 계획이란 DB가 데이터를 찾아가는 일련의 과정을 사람이 알기 쉽게 DB결과셋으로 보여주는 것임.쿼리 실행 계획을 활용하여 기존의 쿼리를 튜닝할 수 있을 뿐만 아니라 성능 분석, 인덱스 전략 수집 등과 같이 성능 최적화에 대한 전반적인 업무를 처리할 수있음.

예시

이 예시에서 각 열의 의미는 다음과 같습니다:
- id: 쿼리 내의 테이블 조인 순서 또는 쿼리의 하위 쿼리에 대한 순서를 나타냅니다.
- select_type: 쿼리 유형을 나타냅니다.
- table: 쿼리에서 사용되는 테이블 이름을 나타냅니다.
- type: 어떤 방식으로 데이터를 읽을지를 나타냅니다. (예: ALL, index, range, ref, eq_ref, const, system, null)
- possible_keys: 사용 가능한 인덱스를 나타냅니다.
- key: 실제로 선택된 인덱스를 나타냅니다.
- key_len: 사용된 인덱스의 길이를 나타냅니다.
- ref: 조인에 사용되는 열 또는 상수를 나타냅니다.
- rows: 예상되는 반환 행 수를 나타냅니다.
- filtered: 필터링된 데이터의 비율을 나타냅니다.
- Extra: 추가 정보를 나타냅니다. (예: Using where, Using index, Using temporary, Using filesort 등)

+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+ 
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3000 | 33.33 | Using where | 
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+

추가적으로 EXPLAIN EXTENDED는 실행한 쿼리가 실제 db에서 내부적으로 어떻게 처리되는지를 알고자 할 때 유용한 명령어다.

기본적으로 실행 결과 읽는 순서는 id 별로 읽다가 테이블에서 derived 뜬거 있으면 id 찾아가서 봄

여기서 쿼리 플랜 보는법 표로 나옴 필요할때마다 찾아보는게 나을듯.

TYPE 부분만 간단히 정리
- system
  - 테이블에 단 하나의 행만 존재(시스템 테이블)
  - const join 의 특수한 경우
- const
  - 많아야 하나의 매치되는 행만 존재할 때
  - PRIMARY KEY 나 UNIQUE index 를 상수와 비교할 때
  - 각 컬럼값은 나머지 연산에서 상수로 간주, 처음 한번만 읽어들이면 되므로 매우 빠름
- eq_ref
  - 조인수행을 위해 각 테이블에서 하나씩의 행만이 읽히는 경우
  - 조인연산에 PRIMARY KEY 나 UNIQUE index 인덱스가 사용되는 경우
  - 인덱스된 컬럼이 = 연산에 사용되는 경우
- ref
  - 이전 테이블과의 조인에 사용될 매치되는 인덱스의 모든행이 이 테이블에서 읽혀질 때
  - leftmost prefix 키만을 사용하거나 사용된 키가 PRIMARY KEY 나 UNIQUE 가 아닐때 (즉 키값으로 단일행을 추출할수 없을때)
  - 사용된 키가 적은수의 행과 매치되면 이것은 적절한 조인 타입
  - ref 는 인덱스된 컬럼과 = 연산에서 사용됨
- ref_or_null
  - ref 와 같지만 NULL 값을 포함하는 행에대한 검색이 수반될 때
  - 서브쿼리 처리에서 대개 사용됨
- index_merge
  - 인덱스 병합 최적화가 적용되는 조인 타입
  - 이 경우, key 컬럼은 사용된 인덱스의 리스트를 나타내며 key_len 컬럼은 사용된 인덱스중 가장 긴 key 명을 나타냄
- unique_subquery
  - 몇몇 IN 서브쿼리 처리에서 ref 타입대신 사용됨
  - unique_subquery 는 성능향상을 위해 서브쿼리를 단순 index 검색 함수로 대체함
- index_subquery
  - unique_subquery 와 마찬가지로 IN 서브쿼리를 대체
  - 서브쿼리에서 non-unique 인덱스가 사용될때 동작 함
- range
  - 인덱스를 사용하여 주어진 범위 내의 행들만 추출
    - key 컬럼: 사용된 인덱스
    - key_len: 사용된 가장 긴 key 부분
    - ref 컬럼: 이 타입의 조인에서 NULL
    - 키 컬럼이 상수와 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN 또는 IN 연산에 사용될때 적용됨
- index
  - 인덱스가 스캔된다는걸 제외하면 ALL 과 같음
  - 일반적으로 인덱스 파일이 데이타파일보다 작기 때문에 ALL 보다는 빠름
  - MySQL 은 쿼리에서 단일 인덱스의 일부분인 컬럼을 사용할때 이 조인타입을 적용함
- ALL
  - 이전 테이블과의 조인을 위해 풀스캔 (조인에 쓰인) 첫번째 테이블이 고정이 아니라면 비효율적
  - 대부분의 경우에 아주 느린 성능

Extra 칼럼에서 Using Filesort 와 Using Temporay 는 좋지 않으며 반드시 쿼리튜닝이 필요하다 함

2.2 쿼리 프로파일링을 이해하자

2.1절에서는 db가 데이터를 찾아가는 일련의 과정에 대해서 설명했다면 여기서는 실행시 병목이 되는 부분을 찾아낼 수 있는 방법에 대해서 설명

만약 프로파일링 결과에서 Sending data 부분이 오래걸린 것으로 나왔다면 스토리지 엔진이 DB엔진으로 데이터를 적게 보내도록 쿼리를 튜닝한다. (근데 자꾸 용어를 헷갈리게 쓰는데 db엔진이 위에서 서버 엔진을 말하는건가?) (근데 검색해 보니까 Sending Data 단계는 결과 데이터를 생성하고 가공하는 부분을 포함함, 실제로 클라이언트로 보내는 단계는 아님. 필요한 데이터가 디스크나 메모리에서 읽혀와 가공된 후 보내기 전 준비된 상태임

3. WHERE 조건 이해

3.1 묵시적 형변환 함정에 빠지지 말자

애플리케이션에서 변수를 바인딩하여 SQL을 실행한다면 성능이 안 좋은 이유를 찾기 어렵다

왜?
1. Query Plan Caching: 매번 실행될 때마다 쿼리 플랜이 다시 생성되어야 하는 경우, 쿼리 실행에 불필요한 오버헤드가 발생할 수 있습니다. 변수를 사용할 때 쿼리가 매번 다른 것으로 취급되어 플랜 캐싱이 제대로 이루어지지 않을 수 있습니다.
2. Parameter Sniffing: 데이터베이스는 쿼리 실행 시점에 변수에 바인딩되는 값에 따라 최적의 실행 계획을 결정합니다. 그러나 때로는 처음 실행될 때 사용되는 값에 최적화된 실행 계획이 그 다음 실행에서는 부적절한 경우가 발생할 수 있습니다. 이를 "Parameter Sniffing"이라고 합니다.
3. Plan Reuse: 변수를 사용하는 경우 동일한 쿼리에 대해 다른 변수 값이 들어오면 동일한 실행 계획을 재사용하는 것이 어려울 수 있습니다. 이로 인해 최적의 실행 계획이 선택되지 않을 수 있습니다.

이번 장에서는 묵시적 형변환이 발생하여 테이블을 풀스캔에 따른 현상 방지를 위한 팁 소개

3.1.1 묵시적 형변환이란?

조건절의 데이터 타입이 다를 때 우선순위가 높은 타입으로 형이 내부적으로 변환되는 것을 말함. 정수랑 문자열 비교할때 정수가 더 높으면 문자열도 정수로 형변환되어 처리됨

만약 형변환이 일어나는 대상이 인덱스 필드라면? 조건절을 처리하기 위해 모든 데이터를 묵시적으로 형변환하기 때문에 테이블 풀스캔을 할 수 밖에 없음.

예시

-- users 테이블 생성 
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50), age INT ); -- age 필드에 인덱스 생성 CREATE INDEX idx_age ON users(age); 
-- 레코드 추가 
INSERT INTO users (id, name, age) VALUES (1, 'John', 30); INSERT INTO users (id, name, age) VALUES (2, 'Jane', 25); 
-- age 필드에 문자열로 검색을 시도 
SELECT * FROM users WHERE age = '30';

정수인데 조회할때 문자열로 하면 인덱스를 활용한 검색이 어려워짐.

3.1.2 테스트 데이터 생성

여기서는 테스트환경 만들어서 정수를 문자열로 , 문자열을 정수로 넣을때 생기는 경우 보여줌 정수형을 문자형으로 줬을때는 어차피 우선순위가 정수형이 높아서 상관없다 근데 칼럼이 문자열일때 정수형 값으로 조회하면 테이블 풀스캔을 때림 . 그래서 파라미터 바인딩을 사용해서 문자열에 정수형 넣어버리면 묵시적 형변환 발생한 위치 파악 힘듬.

그래서 가장 쉬운건 그냥 다 문자열로 줘버리면 됨. 그럼 어차피 정수형은 자동으로 바뀌고 아니면 그대로 실행됨. PostgreSQL 같은 경우엔 형변환이 더 엄격해서 일치하지 않으면 에러를 던진다고함.

문자열 사이에 보이지 않는 문자가 포함되거나 그럴때도 찾기 힘듬. 아무튼 조건절에 칼럼 타입을 잘 맞춰서 질의 해야함

3.2 편리한 함수, 잘못 쓰면 성능에 독이 된다

SQL을 논리적으로 작성하는 것도 중요하지만 DBMS가 이해하기 쉽게 SQL을 작성하는 것도 DB튜닝에 중요하다. 편리하게 사용했던 함수가 오히려 시스템 성능에 안좋은 영향을 준 사례 소개

SELECT userid, count(*) AS cnt
FROM user_access_log
WHERE DATE_FORMTA(reg_date, '%Y%m%d' )  = '20120818'
    AND DATE_FORMAT(reg_date, '%H') >= '18'
    AND DATE_FORMAT(reg_date,'%H') <'21'
GROUP BY userid;

//밑에처럼 수정하는게 낫다
FROM user_access_log
WHERE reg_date >= '2012-08-18 18:00:00'
    AND reg_date < '2012-08-18 21:00:00'
GROUP BY userid;

처음 쿼리 처럼 하면 문제가 DATE_FORMAT 함수를 사용하면 옵티마이저는 reg_date 와 연관된 데이터 분포도를 알 수 없게 된다. DATE_FORMAT 함수로 인해 변경될 결과값을 옵티마이저가 예상하지 못하기 때문.(옵티마이저는 칼럼의 분포도를 기준으로 데이터를 추출하는 가장 빠른 방법을 도출)

위 같은 경우에 user_access_log 테이블에 데이터가 누적될수록 성능은 비약적으로 나빠짐

옵티마이저 구체적인 예시
1. 통계 정보 수집: 데이터베이스 관리 시스템(DBMS)은 테이블의 열(Column)에 대한 통계 정보를 수집합니다. 이 통계 정보에는 칼럼의 유일한 값의 개수, 중복값의 비율, 데이터의 분포 등이 포함됩니다.
2. 칼럼 선택도 계산: 선택도(Selectivity)는 쿼리에서 조건에 해당하는 레코드의 비율을 의미합니다. 예를 들어, 조건 WHERE age > 30이 있다면 선택도는 age가 30보다 큰 레코드의 비율을 나타냅니다.
3. 칼럼의 분포도 파악: 통계 정보와 선택도를 통해 옵티마이저는 칼럼의 데이터 분포도를 파악합니다. 데이터가 어떻게 분포되어 있는지에 따라 어떤 인덱스나 조인 순서가 효율적인지 결정합니다.
4. 쿼리 튜닝 및 실행 계획 생성: 옵티마이저는 쿼리에 대한 다양한 실행 계획을 고려하고, 각 계획의 비용을 평가합니다. 이때 데이터 분포와 통계 정보를 바탕으로 비용을 추정하며, 쿼리의 효율성을 최대화하는 실행 계획을 선택합니다.
5. 실행 계획 평가: 여러 실행 계획 중에서 가장 낮은 비용을 가진 계획을 선택하게 됩니다. 이 계획은 실제로 쿼리를 실행할 때 사용됩니다.

3.3 LIKE 검색을 아무 때나 써야 하나?

기본적으로 db의 자료는 인덱스 키 값 순서로 정렬, 관리되므로 중간 또는 뒷부분 부터 검색하면 인덱스의 의미가 없어짐

밑에서 각 사례들을 살펴보자

3.3.1 LIKE ‘xxx%’ 테스트

여기서 제시한 사례는 긴 숫자 문자열 예를들어 ‘1231513642’ 뭐 이런식 여기서 숫자 찾을때 ‘1%’이런식으로 LIKE 를 썼다고 해보자. 그럼 데이터 비율이 20%이상이라는 상황이면 옵티마이저는 인덱스를 읽고 다시 데이터 파일로 가는것보다 처음부터 전체 데이터를 읽고 필요한 데이터를 선별하는게 더 바르다고 옵티마이저가 판단해서 풀스캔을 한다고 한다. 그니까 어차피 1로 시작이 많은 비율로 있으니까 그냥 바로 가서 찾는거임, 여기서 강조하는 부분은 인덱스도 데이터라는걸 유념해야 한다.

예시 쿼리

근데 이거 테스트 할때 수 적으니까 둘다 range 로 인덱스 참조 하더라 그래서 데이터 수 더 늘리니까 그때 부터 풀스캔 발생.

3.3.2 LIKE ‘%xxx%’ 테스트

인덱스는 순차적으로 비교되므로 당연히 테이블 풀스캔이 발생한다

3.3.3 LIKE ‘%xxx’ 테스트

이것도 풀스캔 나온다고함. 근데 함수 기반 인덱스 사용하면 괜찮은데 mysql에선(이책이 쓰여진 시점 MySQL 5.7부터는 제공됨) 고급기능이 없다고함. 구현하려면 추가 인덱스 칼럼을 생성하고 트리거로 관리하면 된다고 한다.

상관은 없지만 간단한 트리거 예시

할인가 칼럼 추가해서 트리거로 값들어올때마다 추가해주게 만듬 할인된 가격기반 검색에 인덱스 사용가능

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    discounted_price DECIMAL(10, 2) -- 새로운 추가 인덱스 칼럼
);

DELIMITER //
CREATE TRIGGER update_discounted_price AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    SET NEW.discounted_price = NEW.price * 0.9; -- 할인율 10%
END;
//
DELIMITER ;

CREATE INDEX idx_discounted_price ON products (discounted_price);

3.3.4 결론

LIKE 검색을 별생각 없이 사용하다 보면 DBMS 성능이 전체적으로 크게 저하됨. 성능 향상 팁

LIKE 조건이 ‘검색어 %’ 와 같이 검색어가 앞 단에 있다면 데이터 분포도를 따져서 수행한다
LIKE 조건이 ‘%검색어’ 와 같은 형태로 반드시 수행해야 한다면 LIKE 조건 이외의 조건절을 적극 활용하여 LIKE 처리가 필요한 데이터 범위를 최대한 줄인다.

인덱스 또한 데이터여서 메모리를 차지하고 디스크를 소모하며 cpu연산이 필요한 데이터라는 것을 기억해야함. DB에서 처리하는 데이터 범위를 최대한 줄이는 것이 성능 최적화의 가장 기본적인 요소다! 명심!

참고: https://gywn.net/2011/12/mysql-three-features/

[반드시 알아야할 MySQL 특징 세 가지 – gywndi's database

Overview MySQL 요구가 전보다 급증하고 있습니다. 이제 친숙해서 사용하는 간단한 소용량 DBMS 이 아닌, 많은 대형 업체에서도 사용되고 있기 때문에 많은 이슈가 되고 있습니다. 트위터, 페이스북,

gywn.net](https://gywn.net/2011/12/mysql-three-features/)

참고 및 출처: <MySQL 퍼포먼스 최적화 실무 예제로 다가가는 MySQL 쿼리 작성 > (성동찬, 한빛미디어 인사이트 , 2014)

'Book' 카테고리의 다른 글

애그리게이트와 일관성 경계 (0)	2023.10.08
작업 단위 패턴 (0)	2023.10.05
실용주의 프로그래머(7장 ~마지막) (0)	2023.07.31
실용주의 프로그래머(5~6장) (0)	2023.07.29
실용주의 프로그래머(3~4장) (0)	2023.07.28

Via Negativa

MySQL 퍼포먼스 최적화 (1~3장)

1.MySQL의 특징

1.1 MySQL은 전체적으로 어떻게 생겼나

1.1.1 서버엔진

1.1.2 스토리지 엔진(데이터베이스 엔진이라고도함)

1.2 MySQL에서 스토리지 엔진이란 무엇인가?

1.2.1 MyISAM 스토리지 엔진

Full-Text Indexing

Geometric Spartial Indexing

1.2.2 InnoDB 스토리지 엔진

1.2.3 Archive 스토리지 엔진

1.3 MySQL은 데이터를 어떻게 처리할까?

1.3.1 MySQL 에서는 모든 SQL을 단일 코어에서 처리

1.3.2 MySQL 은 테이블 조인을 Nested Loop Join 알고리즘으로만 처리한다

2. 쿼리 성능 진단은 최적화의 기초

2.1 쿼리 실행 계획에 대해서 알아보자

예시

2.2 쿼리 프로파일링을 이해하자

3. WHERE 조건 이해

3.1 묵시적 형변환 함정에 빠지지 말자

3.1.1 묵시적 형변환이란?

예시

3.1.2 테스트 데이터 생성

3.2 편리한 함수, 잘못 쓰면 성능에 독이 된다

3.3 LIKE 검색을 아무 때나 써야 하나?

3.3.1 LIKE ‘xxx%’ 테스트

3.3.2 LIKE ‘%xxx%’ 테스트

3.3.3 LIKE ‘%xxx’ 테스트

상관은 없지만 간단한 트리거 예시

3.3.4 결론

'Book' 카테고리의 다른 글

티스토리툴바

MySQL 퍼포먼스 최적화 (1~3장)

1.MySQL의 특징

1.1 MySQL은 전체적으로 어떻게 생겼나

1.1.1 서버엔진

1.1.2 스토리지 엔진(데이터베이스 엔진이라고도함)

1.2 MySQL에서 스토리지 엔진이란 무엇인가?

1.2.1 MyISAM 스토리지 엔진

Full-Text Indexing

Geometric Spartial Indexing

1.2.2 InnoDB 스토리지 엔진

1.2.3 Archive 스토리지 엔진

1.3 MySQL은 데이터를 어떻게 처리할까?

1.3.1 MySQL 에서는 모든 SQL을 단일 코어에서 처리

1.3.2 MySQL 은 테이블 조인을 Nested Loop Join 알고리즘으로만 처리한다

2. 쿼리 성능 진단은 최적화의 기초

2.1 쿼리 실행 계획에 대해서 알아보자

예시

2.2 쿼리 프로파일링을 이해하자

3. WHERE 조건 이해

3.1 묵시적 형변환 함정에 빠지지 말자

3.1.1 묵시적 형변환이란?

예시

3.1.2 테스트 데이터 생성

3.2 편리한 함수, 잘못 쓰면 성능에 독이 된다

3.3 LIKE 검색을 아무 때나 써야 하나?

3.3.1 LIKE ‘xxx%’ 테스트

3.3.2 LIKE ‘%xxx%’ 테스트

3.3.3 LIKE ‘%xxx’ 테스트

상관은 없지만 간단한 트리거 예시

3.3.4 결론

'Book' 카테고리의 다른 글

'Book' Related Articles

티스토리툴바