1. 해시알고리즘
해시 알고리즘은 검색을 위한 인덱스와 테이블의 파티셔닝 용도로 사용된다. 해시 알고리즘이 사용될 때 해시 함수의 결과 값이 범위가 넓어야 충돌이 줄어들고 그만큼 검색 성능이 높아진다.(버켓이 많이 필요해서 공간의 낭비 발생) 해시 함수의 결과 값의 범위가 좁으면 입력 값은 다르지만 해시값이 같아져 충돌이 많이 발생하여 검색의 효율이 떨어진다.
테이블 파티셔닝 용도로 사용되는 경우에는 해시 함수가 필요한 파티션의 개수만큼만 만들어지도록 설계해야 하므로 해시 함수의 결과 값의 범위를 좁게 사용한다.
2. 해시 인덱스의 가용성 및 효율성
2-1. 해시 인덱스를 사용하는 쿼리 (작업 범위 제한 조건)
다음 패턴의 쿼리는 동등 비교 조건으로 값을 검색하고 있으므로 해시 인덱스를 사용할 수 있다.
SELECT * FROM TEST WHERE NAME = '해시';
SELECT * FROM TEST WHERE NAME <=> '해시';
SELECT * FROM TEST WHERE NAME IN ( '해시','알고리즘');
SELECT * FROM TEST WHERE NAME IS NULL;
SELECT * FROM TEST WHERE NAME IS NOT NULL;
* NULL-SAFE EQUAL 연산자 (<=>)
NULL-Safe Equal 연산자는 = 연산자와 같은 비교 연산을 실행하지만 양쪽의 연산자가 모두 NULL이면 1을 하나의 연산자만 NULL이면 0 을 리턴한다.
select 1=1, 1<=>1, null=null, null<=>null, 1=null, 1<=>null;
|
1=1
|
1<=>1
|
null=null
|
null<=>null
|
1=null
|
1<=>null
|
|
1
|
1
|
NULL
|
1
|
NULL
|
0
|
2-2. 해시 인덱스를 사용하지 못하는 쿼리
범위 비교나 부정형 비교는 해시 인덱스를 사용할 수 없다.
SELECT * FROM TEST WHERE NAME >= '해시';
SELECT * FROM TEST WHERE SAL BETWEEN 1500 AND 2000;
SELECT * FROM TEST WHERE NAME LIKE '해시%';
SELECT * FROM TEST WHERE NAME <> '해시';
다중 컬럼으로 생성된 해시 인덱스에서도 모든 컬럼이 동등 조건으로 비교되는 경우에만 인덱스를 사용할 수 있다
MySQL 8.0 테스트시에는 하나의 선행 컬럼만 쓰여도 해시 인덱스를 사용한다.
[TEST] MySQL 8.0
-- drop index ix_hashtest on employees;
create index ix_hashtest on employees (last_name, hire_date ) using hash ;
EXPLAIN
select *
from employees
where last_name='Aingworth'
-- and hire_date = '1993-05-21'
;|
last_name='Aingworth'
|
|||||||||||
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
|
1
|
SIMPLE
|
employees
|
NULL
|
ref
|
ix_hashtest
|
ix_hashtest
|
66
|
const
|
172
|
100
|
NULL
|
|
last_name ='Aingworth' and hire_date = '1993-05-21'
|
|||||||||||
|
id
|
select_type
|
table
|
partitions
|
type
|
possible_keys
|
key
|
key_len
|
ref
|
rows
|
filtered
|
Extra
|
|
1
|
SIMPLE
|
employees
|
NULL
|
ref
|
ix_hiredate,ix_hashtest
|
ix_hashtest
|
69
|
const,const
|
1
|
100
|
NULL
|
선행컬럼이 = 조건으로 사용 안되어 있을 시에는 해시 인덱스를 사용 못한다.
drop index ix_hashtest on employees;
create index ix_hashtest on employees (last_name, birth_date ) using hash ;
EXPLAIN
select *
from employees
where birth_date = '1958-02-19'
;
*인덱스 생성 삭제 예시
create index ix_ascdesc on employees (emp_no desc, hire_date);
drop index ix_ascdesc on employees;
'MySQL > MySQL' 카테고리의 다른 글
| MySQL 실행계획 (0) | 2024.12.06 |
|---|---|
| RealMySQL 예제 데이터 (0) | 2024.12.06 |
| INDEX 엑세스 조건 (0) | 2024.12.06 |
| INDEX의 구조(B-Tree) (0) | 2024.12.06 |
| MySQL의 격리 수준 (0) | 2024.12.06 |
