MySql에서의 파티셔닝은
물리적으로 여러개의 테이블 만들고
논리적으로 하나로 보이게 만들어놓은 WRAPPER 일뿐이다.
인덱스는 파티션 테이블당 만들어진다.(전체 테이블이 아니라)
storage engine의 입장에서는 (파티션된 개개의) 테이블일 뿐이다.
옵티마이저는 PRATITION BY 에 따라 쿼리 대상테이블을 정한다.
파티셔닝의 주 목적은 거친 index를 만들어서 data들을 여러 테이블에 걸쳐 저장하는거다
1.엑세스 할때 빠르다
2.비슷한 데이터를 한곳에 몰아 넣을수 있다
장점
1.HOT SPOT! 특정 부분만 메모리에 있어야 되고 나머지는 히스토리로 가지고 있을때
2.파티션된 테이블은 유지하기 편하다(안된거에 비해)
3.파티션 테이블은 물리적으로 분리될수 있다 (HDD에)
4.테이블의 보틀넥을 피하기 위해 사용 할수 있다 (InnoDB의 per-index mutexes)
5.원하다면 파티션된 테이블을 백업 및 복원 할수 있다
한계
1.최대 1024테이블
2.PK 나 유니크 인덱스 반드시 파티션에 사용된 모든 컬럼이 들어가야한다.
3.포린 키 사용불가
4.파티션의 모든테이블은 같은 엔진을 사용한다.
파티션은 물리적으로 분리된 테이블이며 인덱스또한 테이블당 하나씩 붙는다.
스토리지 엔진은 심지어 파티션 테이블인지 노말 테이블인지 모른다.(그냥 테이블처럼 취급)
동작 로직
1.SELECT
파티셔닝 레이어가 열리고 모든 테이블을 잠근다.
옵티가 필요없는 테이블들을 제거
파티셔닝 레이러가 스토리지 엔진 API콜
2.INSERT
파티셔닝 레이어가 열리고 모든 테이블을 잠근다.
테이블 결정 후
인설트
3.DELETE
파티셔닝 레이어가 열리고 모든 테이블을 잠근다.
어떤 테이블에 로우 있니
삭제
3.UPDATE
파티셔닝 레이어가 열리고 모든 테이블을 잠근다.
로우를 가져와 변경
변경된 로우가 얼루 가야되는지 결정
업데이트 OR INSERT
(필요하면 예전꺼 삭제)
파티션 테이블락을걸지만 이노디비는 로우레벨 락킹으로 처리한다.
Types of Partitioning
RANGE
PARTITION BY RANGE(YEAR(order_date))
PARTITION p_2010 VALUES LESS THAN (2010),
PARTITION p_2011 VALUES LESS THAN (2011),
PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_catchall VALUES LESS THAN MAXVALUE )
LIST
PARTITION BY LIST (category_id)
PARTITION p_1_2 VALUES LESS THAN (1,2),
PARTITION p_3_4_5 VALUES LESS THAN (3,4,5),
PARTITION p_6_7_8_NULL VALUES LESS THAN VALUE(6,7,8,NULL)
HASH
PARTITION BY HASH(user_id)
PARTITION 4 (
PARTITION p0,
PARTITION p1,
PARTITION p2,
PARTITION p3,
)
추가나 병합하면
모든 데이터를 옴긴다!
값(컬럼, 컬럼즈), 펑션 으로 파티션 range 선택가능
*5.5에서는 데이터 타입에 의해 파티션이 가능하다고함
* HASH 펑션을 사용하여 테이블 락을 줄일수 있다
단 range의 값은 반드시
nonconstant, deterministic integer를 돌려줘야함
CREATE TABLE sales (
order_date DATETIME NOT NULL,
) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
PARTITION p_2010 VALUES LESS THAN (2010),
PARTITION p_2011 VALUES LESS THAN (2011),
PARTITION p_2012 VALUES LESS THAN (2012),
PARTITION p_catchall VALUES LESS THAN MAXVALUE );
* key 파티션하면 이노디비에서 락이 줄어든
* 나누기 함수 을 이용해서 파티셔닝 할수있다
모든 테이블에 일정한 양의 데이터가 들어가길 원할떄
* 날짜별로 해야 할때
날짜를 키에 추가 해야 되기 때문에 문제가 발생할수 있다
AI 를 사용하는대 날자별로 파티셔닝 하고 싶으면 아래처럼 해라
이럴때는 HASH(id DIV 1000000) 등으로 해서 처리 할 수도 있다.
실제 구현
거친 인덱스를 만들어 1차로 프루닝 한다.
사용방법
Scan the data, don’t index it
늘 테이블에서 읽겠다 (날짜별 로그)
인덱스를 안달고
Index the data, and segregate hot data
핫데이터가 존재한다면 그리고
그테이블과 인덱스에 들어올 크기라면 인덱스를 사용
What Can Go Wrong
NULLs can defeat pruning
파시젼 프루닝에서 null이 나온면 첫번째 테이블에 저장됨
즉 널값이 있다면 ! 테이블을 두개 뒤짐
YEAR, TO_DAYS 등 동일
** (즉 첫번째 파티션은 더미로 만들어 두자)
** PARTITION p_nulls VALUES LESS THAN (0)
5.5에서는 안해도 된다는대 왜 인지 이해안감 p270
Mismatched PARTITION BY and index
인덱스는 무저건 파티션 프루닝에 사용된 값들을 1번으로 가지고 있어야한다.
Selecting partitions can be costly
파티션이 커지면 커질수록 오래걸린다
(100정도는 문제없다)
Opening and locking partitions can be costly
모든 타입에 영향을 끼친다.
하지만 심각하지는 않다.
피하고 싶으면 LOAD DATA INFILE 또는 BULK INSERT 하는 방법
Maintenance operations can be costly
REORGANIZE PARTITION
누르게 되면 템프 만들고 삭제하고 다시 집어 넣는댄다
파티셔닝 쿼리 튜닝
무저건 WHERE 절에 파티셔닝 키로 사용되는 조건을 집어넣어라(중복되도!)
EXPLAIN PARTITIONS
파티셔닝과 index 관계
파티셔닝 테이블에서 인덱스에 파티셔닝 키가 들어가 있지 않으면 모든 테이블의 인덱스를 뒤지는거같다
(잘모르겠당...근대 옵티 로직을 생각하면 대충 그렇다)
왠만하면 인덱스에도 파티셔닝 키는 집어넣자
CREATE TABLE PT_TEST (
start_date datetime
, catg_code int
, primary key (start_date )
)ENGINE = InnoDB
PARTITION BY RANGE ( TO_DAYS(start_date))
(PARTITION P17 VALUES LESS THAN (735431) ENGINE = InnoDB,
PARTITION P18 VALUES LESS THAN (735432) ENGINE = InnoDB,
PARTITION P19 VALUES LESS THAN (735433) ENGINE = InnoDB,
PARTITION P20 VALUES LESS THAN (735434) ENGINE = InnoDB,
PARTITION P21 VALUES LESS THAN (735435) ENGINE = InnoDB,
PARTITION P22 VALUES LESS THAN (735436) ENGINE = InnoDB,
PARTITION P23 VALUES LESS THAN (735437) ENGINE = InnoDB,
PARTITION P24 VALUES LESS THAN (735438) ENGINE = InnoDB);
alter table pt_test add index ind_catg(catg_code);
EXPLAIN PARTITIONS SELECT * FROM test.pt_test WHERE catg_code = 10 AND start_date > '2013-07-15' and start_date < '2013-07-16';
P17 ref PRIMARY,ind_catg ind_catg
Views
두가지 접근법이 있다
이렇게 뷰를 만들었으면
mysql> CREATE VIEW Oceania AS
-> SELECT * FROM Country WHERE Continent = 'Oceania'
-> WITH CHECK OPTION;
뷰에서 검색을 하면
SELECT Code, Name FROM Oceania WHERE Name = 'Australia
1.TEMPTABLE 이렇게 해서 결과를 돌려주는 방법
mysql> CREATE TEMPORARY TABLE TMP_Oceania_123 AS
-> SELECT * FROM Country WHERE Continent = 'Oceania';
mysql> SELECT Code, Name FROM TMP_Oceania_123 WHERE Name = 'Australia';
2.MERGE 아니면 쿼리 자체를 변경하는 방법
mysql> SELECT Code, Name FROM Country
-> WHERE Continent = 'Oceania' AND Name = 'Australia';
MySQL은 두개 다쓴다
왠만하면 MERGE를 사용하려한다.
view 안에 nested view가 있어도 쓸수있다
TEMPTABLE은
쿼리 안에 GROUP BY, DISTINCT, aggregate
functions, UNION, subqueries, or any other construct that doesn’t preserve a one-to-onerelationship
*EXPLAIN EXTENDED, followed by SHOW WARNINGS 으로 실제 사용된 쿼리를 확인 할수 있다
DERIVED 가 나온다면 TEMPTABLE이다.
뷰가쓰기 좋을때
칼럼들을 숨겨야 해서 복잡한 권한이 필요할때
Storing code inside mysql
장점
1.데이터가 있는곳에서 처리 할수 있다
2.데이터 정합성을 잘유지 할수 있다
3.정책을 만들고 유지하기 쉽다
4.권한관리가 쉽다
5.서버캐쉬가 store procedure의 execution plan을 저장하여
좀더 빠르다
6.일을 분화 할수있다
단점
1.만들고 디버깅하기 힘들다
2.어플랭퀴지에 비해 느리다
3.유지보수가 힘들다
4.로드를 디비로 가져와 나중에 스케일항기 힘들다
5.스토어 프로시저 때문에 서버다운될수있다
6.커서는 temp table이다....
7.튜닝하기 힘들다 메서드명만 로깅에 찍히기 때문에
8.로깅 남기기힘들다
9.레플리케이션에 잠재적 위험이 있다
*참고
DETERMINISTIC
인풋값이 같다면 시간에 따라 결과값 변하지 않음
NOT DETERMINISTIC
인풋값에 상관없이 시간이 변하면 결과값 변함
Stored Procedures and Functions
단점
1.옵티가 스토어 코드들의 코스트를 측정할수없다
2.스토어 코드들은 컨낵션당 캐쉬된다.(즉 낭비될수있다
단 커낵션 풀링이나 퍼시스턴트를 쓴다면 좋다)
3.레플리케이션에서는 스토어 프로시져를 부르는게아니라
변경된 로우를 부르길 원할 수있다
log_bin_trust_function_creators.
*복잡한 로직은 디비에서 빼서 앱서버에서 처리해라
Triggers
denormalized and summary table 을 유지하기 좋다
InnoDB에서 사용하기전 한번 읽자 P287
제약
1.하나의 이벤트에 하나의 트리거만 등록가능
2.로우단위만 지원(즉 for each row 에 동작함)
3.에러 날경우 찾기 힘듬
4.트리거는 아토믹이 아닐수있다
MyISAM 안됨
InnoDB 됨
However, you need to be
very careful with triggers that insert rows into other tables that have autoincrementing
primary keys. This doesn’t play well with statement-based replication, as the autoincrement
values are likely to be different on replicas.
Events
레플리케이션에서 위험할수 있다
*이벤트가 레플리케이션에서 실행될지 않될지 정할수있다
이벤트가 길게 되서 실행되는 순간 다른이벤트가 실행 되는 경우도 있다
아래와 같이 락을 잡아서 실행 안되게 할수있다
CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK
DO
BEGIN
DECLARE CONTINUE HANLDER FOR SQLEXCEPTION
BEGIN END;
IF GET_LOCK('somedb', 0) THEN
DO CALL optimize_tables('somedb');
END IF;
DO RELEASE_LOCK('somedb');
END
*이벤트 관리 쓰레디는 하나지만 실행은 새로운 프로세스를 생성해서 한다
즉 동시에 여러개가 돌아 갈수 있다
Cursors
read-only, forward-only
temp table로 구현된다
경우에 따라BLOC, text 너무 크거나 그럼 디스크로
아니면 메모리에서
Mysql에서는 커서를 닫아도 전체가 다 돌 때까지 무시하고 실행한다.
클라이언트에서 cursor를 사용하는걸 지원하지 않는다.
Prepared Statements
동작과정
1.클라가 서버에 쿼리의 프로토 타입을 던진다.
2.서버가 파싱해서 skeleton 쿼리를 만든 후 저장후 저장된 포인터 리턴
3.클라는 저장 포인터에 데이터를 넘기면서 실행
장점
1.한번만 파싱하면된다.
2.네트워크 트래픽양을 줄일수있다
값만 던지면된다.
그값도 바이너리로 던짐으로 (ascii가 아니라)많이 줄어든다
3.서버는 버퍼를 사용해 그값을 저장해 놓는다.
메모리 사용량이 줄어든다(그냥 여러개 날리는거에 비해)
The SQL Interface to Prepared Statements
서버에서 prepare stmt 사용하기위한 문법의 명칭 = sql interface
*바이너리에서만 동작하던걸 개선함
한계
1.컨낵션당 가능
2.쿼리캐쉬 사용안됨
3.만약 쿼리를 한번 호출하는대 프리페어를 쓴다면 시간이 더걸림
(1.준비하고, 2.나중에 풀어줘야됨)
4.store function 에서 사용안됨(store procedure에서는 사용가)
5.꼭 deallcate preparestmt 호출할것
Character Sets and Collations
character set
비트패턴 과 문자 연결
is a mapping from binary encodings to a defined set of symbols
you can think of it as how to represent a particular alphabet in bits.
collation
문자의 정렬 순서
is a set of sorting rules for a character set.
어떻게 사용되는가
Defaults for creating objects
DB -> 스키마->테이블 ->컬럼 등으로 상속된다
character_set_server 에서 정할수 있고
각 단계마다 필요하면 변경할수 있다
설정안하면 상속 값 사용
Settings for client/server communication
서로 대화할려면 같은 charset을 사용해야한다.
SET CHARACTER SET 으로 변경가능
1.클라가 해당 옵션 차셋을 사용한다고 가정character_set_client.
2.character_set_connection 으로 컨낵션마다 바꿀수있음
3.character_set_result. 서버가 리절트를 돌려줄때 사용
How MySQL compares values
만약 다른 차셋의 두벨류를 비교한다면
1.일단 같은 차셋으로 변경
특이점
LOAD DATA INFILE, SELECT INTO OUTFILE 은 character_set_database 를 사용
참고
CHAR(10) 일경우 UTF-8이면 30byte가 필요
LENGTH() 비트 길이
CHAR_LENGTH() 캐릭터 길이
UTF-8을 사용할 경우 인덱스의 최대 문자 길이는 333까지이다.
FULL TEXT SEARCH
P 313 확인
Distributed (XA) Transactions
two-phase commit
phase one
are u ready for commit?
phase tow
go ahead and commit!
Internal XA Transactions
왜 필요한가
서버 - 스토리지 엔진 구조이기 때문에
2개라면 당연이 필요
1개라도 binary log 에 committing이라고 남는다
왜? 바이너리 로그를 서버가 관리하니까
이노디비에서 트랜잭션을 사용할때 각각의 트랜잭션 바다 최소 3번의 fsync()가 필요하다
퍼포먼스에 문제가 올수있고 innodb_support_xa to 0을 함으로 줄일수있다
단 그렇게 하면 레플리케이션을 사용못한다.
External XA Transactions
여러대의 서버를 동기화 할때 사용한다.
단점
1.레플리케이션을 사용하지 않거나
2.퍼포먼스가 중요하지 않을때
사용가능하다
The MySQL Query Cache
1.쿼리의 전체 bit 가 정확히 맞다면
2.cache에서 꺼내서 result 를 클라한태 돌려줄수 있다
쿼리 캐쉬는 적재된 리절트 셋이 어느 테이블을 사용했는지 알고 있으며
만약 테이블이 변경된다면 캐쉬를 버린다.
하지만 아예 disable 하거나
정말 필요하다면 아주작은 양을 캐쉬 하게 설정하길 바란다.
캐쉬 히트를 결정하는 방법
1.hash map 을 만든다.
key = hash of the query text
val = res
2.키를 찾고 있으면 히트 없음 노우!
3.non DETERMISTIC function 을 사용한건 캐쉬에 적재 안함
ex) now() , current_data()
사용 못하는 예제
SELECT 문중
subquery가 있으면(temp table 때문에)
view (위와 동일)
query in stored procedures
prepared statement
주의점
1.READ 쿼리는 무저건 cache 를 체크해햐한다.
2.만약 쿼리가 캐쉬가능하고 저장되지 않았다면 저장하는대 코스트가 든다.
3.WRITE 쿼리는 저장된 캐쉬를 disable시켜야 되는대
invalidation 은 메모리가 크거나 캐쉬가 쪼개져 있다면 더럽게 비싸다
4.캐쉬를 사용함에 락이 걸릴수도 있다
5.innodb 의 경우 mvcc 를 하기때문에 트랜잭션 중이라면 테이블을 캐쉬 못하게 한다.
즉 트랜잭션이 조금이라도 길면 계속 캐쉬 미스난다.
6.캐쉬를 invalidation 시키기 위해서는 쿼리 캐쉬에 글로박락을 건다
만약 오래 걸린다면.. 모든 쿼리가 막힌다..
How the Cache Uses Memory
쿼리 캐쉬는 메모리에 상주
쿼리 관리를 위한 메모리 40KB
1.서버가 시작하면 쿼리 캐쉬 메모리를 할당받음
2.최초 하나의 커다란 블락임(컨피겨에 설정된)
3.쿼리 결과를 캐쉬할때
1.최소 메모리를 예약함(query_cache_min_res_unit 크기만큼)
2.mysql은 쿼리가 실행되기 전에 예약함으로 결과 크기를 모른다.
그러므로 필요한 블락을 계속 요청하는대 코스트가 비싸다
(mysql은 리절트를 지속적으로 한줄씩 클라로 보내니까)
4.적재 완료 되면 필요 없는 아래 블락을 하나의 free 블락으로 머지
단 가운대 비계되면 머지가 안된다..메모리 프레그 먼테이션 문제가 생기기 시작
When the Query Cache Is Helpful
쿼리캐쉬가 실행 시간은 줄여지지만 네트워크 센딩 시각은 줄여주지 못한다.
SELECT 쿼리가 다수라면 괞찬다.
쿼리 히트율이 Qcached_hits/ (Qcache_hits+Com_select)
히트율이 낮아도 hit 되는 쿼리가 무거운 쿼리라면 좋다
Qcache_hits relative to Qcache_inserts 비율이 10:1 정도 되면좋다
즉 간단한 방법으로 는 설명할수 없다
가장 좋은 방법은 쿼리 캐쉬를 키고 와 끄고의 시간 차이를 보는게 좋다
설정방법
query_cache_type
OFF, ON, or
DEMAND
SQL_CACHE 가 붙은애만 캐쉬함
query_cache_size
전체 캐쉬 사이즈
1024 byte 의 배수로 지정
query_cache_min_res_unit
캐쉬 블락의 최소 사이즈
query_cache_size - Qcache_free_memory
Qcache_queries_in_cache
을 봐서 가장 적당한 크기를 찾는다.
query_cache_limit
전체캐쉬보다 크면 캐쉬 안댄다
캐쉬 될수 있는 최대 리절트의 크기
query_cache_wlock_invalidate
다른 커넥션이 락건 캐쉬를 읽을수 있다
설정하지 않는게 좋다
326쪽 7-5 도표화인
캐쉬 설정 확인 순서 나와있음
디스에이블 방법
query_cache_size 0. query_cache_type = 0
