mysql index btree/hash 차이

http://www.sqldic.co.kr/informationView.action?bbs.bbs_seq=2264&page=2

B-Tree Index 특징
 

B-tree index 는 =, >, >=, <, <=, 또는 BETWEEN 연산자를 사용하는 표현에서 컬럼 비교에 사용할 수 있다.
인덱스는 또한 LIKE 인수가 와일드카드 문자로 시작하지 않는 상수 문자열인 경우 LIKE 비교에 사용할 수 있다.
예를 들어, 아래 SELECT 구문은 인덱스를 사용한다:
 

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';
 

첫 번째 구문에서는, 'Patrick' <= key_col < 'Patricl' 인 열만 고려된다. 
두 번째 구문에서는, 'Pat' <= key_col < 'Pau' 인 열만 고려된다.
아래 SELECT 구문은 인덱스를 사용하지 않는다:
 

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;
 

첫 번째 구문에서는, LIKE 값은 와일드카드 문자로 시작한다. 
두 번째 구문에서는, LIKE 값은 상수가 아니다.
 

... LIKE '%string%'을 사용하고 string 이 세 글자보다 길면, MySQL은 해당 문자열에 대한 패턴을 초기화 하기 위해 Turbo Boyer-Moore algorithm 을 사용한다. 그리고 이런 패턴을 더 빨리 조회하기 위해 사용한다.

col_name 이 인덱스인 경우, col_name IS NULL 을 사용하는 조회는 인덱스를 사용한다.
WHERE 절에서 모든 AND 레벨에 미치지 않는 인덱스는 그 쿼리를 최적화하는데 사용할 수 없다. 다시 말해서, 인덱스를 사용하기 위해서, 인덱스의 접두어는 모든 AND 그룹에서 사용되어야 한다.
 

아래 WHERE 절은 인덱스를 사용한다:
 

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2
    /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5
    /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
 

아래 WHERE 절은 인덱스를 사용하지 않는다:
 

    /* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10

    /* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10
 

사용할 수 있는 인덱스가 있다고 해도, 때론 MySQL은 인덱스를 사용하지 않는다. 이런일이 발생하는 요인 중에 하나는 옵티마이져가 인덱스 사용이 MySQL로 하여금 테이블에 있는 많은 수의 열을 접근해야 한다고 판단할 때 발생한다. (이런 경우, 테이블 스캔이 더욱 더 빠를 수가 있다. 더욱 적은 수를 스캔하기 때문이다. 하지만, 단지 열의 몇 개만 추출하기 위해 LIMIT를 사용하는 쿼리이면, MySQL은 인덱스를 사용한다. 결과적으로 리턴할 적은 량의 수를 더욱 더 빨리 찾을 수 있기 때문이다.
 

Hash Index 특징
 

Hash indexes 는 앞에서 언급한 것과 다소 다르다:
 

• 이것은 단지 = 또는 <=> 연산자를 사용하는 균등 비교에만 사용된다. (하지만 매우 빠르다). 값의 범위를 찾는 < 같은 비교 연산자에 사용하지 않는다.
• 옵티마이져는 ORDER BY 연산의 속도를 높이기 위해 hash index를 사용할 수 없다. (이런 타입의 인덱스는 순서대로 다음 값을 찾는 것에 사용할 수 없다).
• MySQL 은 두 값 사이에 얼마나 많을 열이 있는지 대략적으로 결정할 수 없다. (어떤 인덱스를 사용할지 결정하기 위해 범위 옵티마이져를 사용한다). MyISAM 테이블을 a hash-indexed MEMORY 테이블로 변경하면 몇몇 쿼리에 영향을 준다.
• 단지 전체 키만이 한 열에 대해 찾기에 사용될 수 있다. (B-tree index 에서는, 키의 가장 왼쪽 접두어가 열을 찾는데 사용할 수 있다.)