메모리 디비에서는 해쉬 인덱스 만들수 있음
당연한거지만 컬리즌 일어나면 성능 떨어짐
선택도 높은 컬럼에 적용 해야함
이노디비에서는 자주 참조되는 값을 해쉬 인덱스로 만듬(자동임)
즉 비트리 있고 그위에 해쉬 인덱스 만듬
hash indexes.
Mysql에서 지원한는 인덱스는 없지만 비슷하게 구현 할 수있음
검색해야 하는 스트링이 길경우에 사용 할수 있음
SELECT id FROM url WHERE url="http://www.mysql.com"
AND url_crc=CRC32("http://www.mysql.com");
물런 SHA1() MD5를 사용할수 도 있지만 느리고 벨류가 길다!
hash function으로 사용하기 좋지 않다.
* 해시 컬리즉이 일어나기때문에 꼭 where 조건에 url = full url을 해줘야 된다.
93000개에서 약 1%으로 hash collision 이 일어난다.
데이터가 많아 충돌이 심하면 FNV64() 를 Percona Server plugin으로 깔자
Spatial (R-Tree) indexes
mysql 구리다 쓸라면 postgreSQL써라
index 장점
1.서버가 찾아야할 데이터 양을 줄인다.
2.인덱스는 정렬 되어 있기 때문에 서버가 temp 에서 정렬하는걸 없앤다
3.random I/O를순차 I/O로 바꾼다
좋은 인덱스 3스타 구분법
쿼리가 필요한 ..
1.관련 있는 로우들끼리 인접해 있다면 +1
2.로우들이 정렬되어 있다면 +1
3.결과를 covered index 로 처리할수 있다면 +1
데이터가 정말 크다면
1.정말 데이터가 크다면 메타 데이터 테이블을 만들어서 처리 할수도있다
ex)A유저의 정보가 여러 테이블에 분리되어있으면 그 정보를 가지고 있는 테이블을 관리해도 된다
단 데이터가 테라바이트 단위로 나간다면
정말 데이터가 많다면 메타데이터 테이블 만들고 appserver에서 참조 하게 하자
고성능 인덱스 전략
인덱스 고립
1.펑션안에서 쓰여도 안되고
2.연산식 안에 쓰여도 안된다
ex)못쓴다.
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;
선택도
범위 : 1/테이블로우~1
ex)
유니크 인덱스는 선택도 1
prefix 인덱스
데이터가 너무길어 hash func 으로 풀지 못한다면
prefix 인덱스를 만든다.
BLOB, TEXT, VARCHAR(긴거)
앞에 몇 글자를따서
어떤 기준으로 만들것인가?
토탈을 카운팅 하고
프리픽스를 늘려가면서 토탈과 비슷할떄까지 프리픽스를 늘린다.
선택도가 1에 가까워지 질수록 좋다
SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;
COUNT(DISTINCT city)/COUNT(*) |
+-------------------------------+
| 0.0312 |
SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
-> COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
-> COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
-> COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
-> COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
-> FROM sakila.city_demo;
sel3 | sel4 | sel5 | sel6 | sel7 |
+--------+--------+--------+--------+--------+
| 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 |
평균으로만 생각하면 그러면 4,5를 택할수도 있다
문제는 한쪽에 값이 몰려있을 경우다
SELECT COUNT(*) AS cnt, LEFT(city, 4) AS pref
-> FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 5;
+-----+------+
| cnt | pref |
+-----+------+
| 205 | San |
| 200 | Sant |
| 135 | Sout |
| 104 | Chan |
| 91 | Toul |
San으로 시작하는놈만 엄청나게 많으면?
선택도가 구려진다.
해서 적당한 길이를 택해야 한다.
mysql> ALTER TABLE sakila.city_demo ADD KEY (city(7));
*단점
prefix 로 인덱스 구현하면 order by, group by, 커버링 인덱스 안된다.
revers index
mysql은 지원안한다. 컬럼에 값을 거꾸로 집어 넣어 구현가능
index merges가 나오면(4.1이상)
더 느릴수도 있다 랜덤I/O, temptable들 때문에
정말 빠른가를 확인해야 한다.(대체적으로 더 느리단다)
optimize_switch 로 머지 안하게 할수있다
IGNORE INDEX 로도 가능
Choosing a Good Column Order
1.가장많이 선택되는 컬럼을 맨 앞에 배치(필터로 사용)
if 정렬, 그룹핑이 필요 없을 경우
필터로 사용할때는 인덱스의 selectivity 도 생각해야 된다.
ex)
SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;
의 인덱스를 만들때
SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
> COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
> COUNT(*)
> FROM payment\G
*************************** 1. row ***************************
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
커스텀 아이디의 선택도가 더 좋기 때문에 customer_id를 앞에 둔다.
mysql> ALTER TABLE payment ADD KEY(customer_id, staff_id);
* 단 인덱스의 선택도도 정말 중요하다.
Clustered Indexes
Btree 인덱스 마지막 leaf에 포인터가 아닌 실제 데이터를 저장
1.당연이 테이블당 단 한개의 클러스터 인덱스를 가짐
클러스터 장점
1.비슷한 벨류를 한곳에 모아 둘수 있다
2.논클러스터에 비해 선택된 로우에 접근하는게 빠르다.
리프노드에 포인터가 아닌 실제 데이터를 저장하고 있음으로
그냥 읽으면 된다 그 포인터로 다시 읽는게 아니라
3.커버링 인덱스 쿼리에서는 리프에 저장된 프라이머리키를 사용할수있다
단점
***.세컨더리 인덱스 룩업은 실제적으로 2번의 룩업이 필요하다
이노디비 자체의 hash index로 커버할려고 노력은 함
1.클러스터링인덱스는 I/O에서 큰 이득이 있다
즉 테이블이 메모리에 다 올라와있으면 별 이득이 없다
2.PK순서로 insert 할때 가장 빠르다
만약 PK순서가 아니라면 중간 테이블을 만들어 정렬후 insert 하라
3.cluster pk 자체 를 업데이트 하는건 비싸다
그 로우를 새로운 장소로 옴겨야 할수도 있다
4.페이지 분할을 사용하기때문에 더많은 저장 공간을 사용할수도 있다
5.풀테이블 스캔이 느릴수도 있다
페이지 별로 분리되어 있기 때문에
PK가 밀집되어 순서대로 되어있으면 좀 났다
6.세컨더리 인덱스는 pk를 포함하기 때문에
좀 커질수도 있다
InndoDB
*이노디비에서는 새컨더리 인덱스 마지막에 무저건 pk를 집어 넣기 때문에
pk칼럼 추가하지 말자(눈에 안보일뿐 커버링 인덱스도된다)
PK index에서
리프노드는
PK col, TrasactionId,Rollback pointer, col2,,,,,
으로 구성되어있음
*즉 테이블 자체임
secondary index에서
leaf node 에
pk values 를 가지고 있음(로우 pointer 가 아님)
insert시
pk순과 랜덤 속도 약 3~4배 차이남
인덱스의 크기는 약 0.5배 정도 차이 나는대
페이지 분할때문에 그럴수있음
이노디비 페이지는 15/16(fillfactor)만큼 데이터 차면 새로운 페이지 생성
*이노 디비를 사용할때는 PK가 순차적으로 늘어나는애로 잡아야한다.
auto_incre 사용하는대 문제가 생긴다면 innodb_autinc_lock_mode를 사용해라
커버링 인덱스
장점
로우보다 데이터 량이 적기 때문에 쉽게 로드되고
캐쉬로도 쉽게 사용된다.
인덱스는 키로 솔팅 되어 있기때문에(페이지 사용시)
범위 검색에서는 I/O시 훨씬 빠르다.
*MyISAM에서는 OPTIMIZE 할수 있다
주의
*MyISAM 은 MySQL에 메모리에 캐슁한다(즉 OS에)
문제는 캐쉬 알고리즘에 가장 비싼 부분은 system call을 사용하는거다
*메모리 디비는 현재 커버링 인덱스 지원 안한다.
explain에 using index로 나온다.
MySQL에서 LIKE 검색은 앞부분 매치에만 사용가능하다.
why? low-level storage engine API
양쪽에 써야 할때는 약간의 트릭을 사용한다.
mysql> EXPLAIN SELECT *
-> FROM products
-> JOIN (
-> SELECT prod_id
-> FROM products
-> WHERE actor='SEAN CARREY' AND title LIKE '%APOLLO%'
-> ) AS t1 ON (t1.prod_id=products.prod_id)\G
문제는 서브쿼리 튜닝이 늘빠를까?
1. In the first, 30,000 products have Sean Carrey as the actor, and 20,000 of those
contain “Apollo” in the title.
2. In the second, 30,000 products have Sean Carrey as the actor, and 40 of those
contain “Apollo” in the title.
3. In the third, 50 products have Sean Carrey as the actor, and 10 of those contain
“Apollo” in the title.
Dataset Original query Optimized query
Example 1 5 queries per sec 5 queries per sec
Example 2 7 queries per sec 35 queries per sec
Example 3 2400 queries per sec 2000 queries per sec
1번은 필터링이 안되니 동일
2최적의 사항이 좋아짐
3데이터 자체가 없으니 full table read 가 훨빠름
즉 어느정도의 데이터가 있으면 튜닝하는게 낮다 정도?
5.6에서는 index 관련하여 많은 발전있음
인덱스 조건을 스토리지 엔진에 던질수 있을정도
인덱스 솔팅 기능
order by 절의 컬럼순서와 방향까지(ASC,DESC)(복수) 인덱스와 똑같다면 사용할 수 있다
인덱스 룩업 처럼 왼쪽부터 같으면 된다
*만약 여러개를 조인한다면 ORDER BY의 모든 컬럼이 첫번쨰 테이블에 있을때만 가능
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
EXPLAIN SELECT rental_id, staff_id FROM sakila.rental
-> WHERE rental_date = '2005-05-25'
-> ORDER BY inventory_id, customer_id
일때 인덱스로 정렬 된다.
타는 애들
WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;
WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id;
rental_date 레인지 조건 + 정렬 조건 1번 rental_date
못타는 애들
WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;
WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;
WHERE rental_date = '2005-05-25' ORDER BY customer_id;
WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;
레인지에 첫번째로 정렬 조건이 들어가지 않아서 안됨
WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_id;
Packed (Prefix-Compressed) Indexes
MyISAM uses prefix compression to reduce index size
단어를 블락으로 묶어서 압축한다.
ex)
perform
performance =>7,ance
2번째 로우가 저렇게 압축된다. 동일한 방법이 row pointer에도 적용된다.
*문제는 리버스 검색은 안된다 ex) order by desc
*중간을 찾을려도 블락의 반은 읽어야 한다.
설정 방법
PACK_KEYS option to CREATE TABLE.
Redundant and Duplicate Indexes
Duplicate Indexes
CREATE TABLE test (
ID INT NOT NULL PRIMARY KEY,
A INT NOT NULL,
B INT NOT NULL,
UNIQUE(ID),
INDEX(ID)
) ENGINE=InnoDB;
3개다 같은 인덱스다
볼때마다 지워라
Redundant
(A, B)
, another index on (A)
지워라
물런 (B,A) (B)는 다른인덱스다
일부로 의도 하는 경우도 있다
인덱스 (state_id)
1. SELECT count(*) FROM userinfo WHERE state_id=5;
2. SELECT state_id, city, address FROM userinfo WHERE state_id=5;
1번은 빠르지만 2번은 느리다
그래서 2을 위해 인덱스를 확장 하면
인덱스(state_id, city, address)
1번이 상대적으로 느려진다!
1,2번 두개다 빨라야 한다면 둘다 유지해라
이노디비는 인덱스를 확장해도 QPS는 별 차이가 없다
because InnoDB doesn’t use key compression.
뭔말인지 모르겠다 P186
쿼리사항 보는 좋은 툴 두개
http://code.google.com/p/common-schema/).
상용에서는 쓰지말고
SLAVE나 OFFLINE에서 쓸것
mysql -u root -p
>SOURNCE '파일주소
'
인덱스 제거하기 pt-duplicate-key-checker
사용하지 않는 인덱스 지우기
pt-index-usage
로그를 읽어서 explian 을 부르고 거기에 없는 index를 리턴
Percona Server and MariaDB.
변경 userstats = ON
확인 INFORMATION_SCHEMA.INDEX_STATISTICS
*Mysql은 index에서 range 검색을 한다면 그쿼리에 다른 인덱스를 쓰지 못한다.
If MySQL uses an index for a range criterion in a query, it cannot also use another index
case study
온라인 데이트 사이트를 만든다고 생각해보자
user
country
sex
age
..
등이 들어갈꺼고 country, sex 는 검색조건에 필수적으로 들어가게될거다
문제 1
즉 대부분의 인덱스는(sex,country)를 가져야 한다.
두개를 select 안하는 쿼리들은?
where sex in('M','F') 같은 트릭사용
*원칙 1
쿼리를 보고 인덱스를 수정하는 방법 말고
인덱스를 보고 쿼리를 수정하는 방법도 있다
문제 2
(sex, country, age)
, (sex, country, region, age)
, (sex, country, region, city, age).
등 비슷한 인덱스가 많이 필요하다면?
해결 2
(sex, country, age)
, (sex, country, region, age)
를 만들고
해당 질의 가 없으 sex in(',,'), country in(''..)
을 써주라는대 country는 너무 길수도 있잔아..
문제 3
잘 검색 되지 않는 애들이 조건으로 들어온다면?
has_pictures, eye_color, hair_color, and education
해결 3
그냥 인덱스 추가 안해 필터로 쓰던가
(sex, country,[추가], age)
추가후 IN 트릭 사용
문제 4
왜 age를 맨끝에 두는가?
이유 4
mysql은 (leftmost prefix) 인덱스 같을때까지 따라가다가
범위 조건이 한번 나오면 더이상 인덱스를 사용하지 못한다.
age는 범위 쿼리이기때문이다.
물런 age를 in( 19,20..)으로 하는 법도 있긴하다.(하지만 못쓸때도 있다)
*원칙2
범위 조건은 Mysql이 이용할수 있도록 최대한 인덱스 끝에 둔다.
*원칙3
IN 트릭도 너무 많이 쓰면 (100개이상값)
MySQL옵티마이저에서 시간이 많이 걸린다 적절이 쓰자
*주의
range condition
age > 45
multiple condition
age in(1,2,3)
explian 에는 둘다 range 로 나오지만
range 컨디션은 뒤의 index 컬럼을 사용하지 못한다.
문제 5
WHERE eye_color IN('brown','blue','hazel')
AND hair_color IN('black','red','blonde','brown')
AND sex IN('M','F')
AND last_online > DATE_SUB(NOW(), INTERVAL 7 DAY)
AND age BETWEEN 18 AND 25
해결 5
레인지 컨디션이 2개라면 MySql은 하나밖에 쓰지 못한다.
하지만 정말 두개다 필요하다면?
last_before_7_day_yn 컬럼을 배치로 돌리면서
Y, N값을 주고 쿼리에서
(eye_color ,hair_color,sex ,last_before_7_day_yn, age )
로 인덱스를 만들수 있다
물런 last_before_7_day_yn을 인덱스 셀렉트 후 필터 옵션으로
사용하여 인덱스 자체에 추가 하지 않는 방법도 있다.
페이징 쿼리에서
이거 엄청 느리다 col들을 가져 오기때문에..
SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 100000, 10;
아래같이 걍 서브쿼리를 써라
SELECT <cols> FROM profiles INNER JOIN (
SELECT <primary key cols> FROM profiles
WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10
) AS x USING(<primary key cols>);
테이블과 인덱스 유지 보수
만약 이상한 행동을 보인다면(쿼리 결과 값, dup 에러등)
CHECK TABLE 을 해라
고장이 있다면 REPAIR TABLE 안되면
지원하지 않으면 같은 엔진에서 같은 엔진으로 변경
ALTER TABLE innodb_tbl ENGINE=INNODB;
이노디비가 커럽 되면 반드시 이유를 찾아라
이노디비는 잘 설계되어있다 커럽된다면 추후 다시 커럽될 가능성이 높다
*팁 innodb_force_recovery 옵션으로 자동으로 복구한다.
Updating Index Statistics
MySQL’s optimizer is cost-based, and the
main cost metric is how much data the query will access
통계가 갱신이 안되어있으면 실행계획이 구리다
업데이트 처주자
ANALYZE TABLE [테이블명](,[테이블명]..);
인덱스 정보 보기
show index from actor;
InnoDB.Cardinality 에서는 sample page 뽑아서 통계를 낸다
디펄트 8page이고
innodb_stats_sample_pages 옵션에서 바꿀수 잇다.
이노디비 인덱스 통계 호출 시점
1.부팅된 후 테이블 처음 select 될시(젓될수 있다 조심하자!)
(5.6이상) innodb_analyze_is_persistent 에서 변경가능
2.ANALYZE TABLE 호출 될시
3.1/16 만큼 변할때
4.200만 로우 insert시
*INFORMATION_SCHEMA tables, SHOW TABLE STATUS and SHOW INDEX queries,
에 쿼리를 날리면 호출 된다 문제는 상용에서 날리면
디비가 기어다닌다 조심하자
innodb_stats_on_metadata 옵션에서 자동으로 동작하지 않게 수정할수 있다 .
Reducing Index and Data Fragmentation
인덱스 Fragmentation
B-tree 인덱스가 하드에서 (Fragmentation)조각나면 index-cover 쿼리가 더럽게 느려진다.
인덱스 Fragmentation 수정
InnoDB에서는 online 으로 rebuild 할수 있다고 한다.
나중에 찾아보장
데이터(테이블) Fragmentation
Row fragmentation
하나의 로우가 여러 조각으로 여러 공간에 흩어져있을때
Intra-row fragmentation
로직적으로 정렬된 페이지 또는 로우가 디스크에 순서되로 정렬되어 있지 않는 상태
Free space fragmentation
데이터 페이지에 빈공간이 만들때
MyISAM은 모든 문제가 생길수 있지만 InnoDB에서 로우가 짧다면 잘 이러 나지 않는다.
데이터 Fragmentatation 수정
1.OPTIMIZE TABLE [테이블명]
2.덤프후 리로드
3.테이블이 OPTIMIZE TABLE 을 지원안하면
동일 엔진을 호출하는 방법으로 가능
ALTER TABLE <table> ENGINE=<engine>;
