CREATE INDEX <인덱스명> ON <테이블명> ( 칼럼명1, 칼럼명2, ... );
2. 테이블 생성시 만들기
끝에....
INDEX <인덱스명> ( 칼럼명1, 칼럼명2 )
UNIQUE INDEX <인덱스명> ( 칼럼명 ) --> 항상 유일해야 함.
3. 이렇게도 생성한다
ALTER TABLE <테이블명> ADD INDEX <인덱스명> ( 칼럼명1, 칼럼명2, ... );
SHOW INDEX FROM <테이블명>;
5. 인덱스 삭제
ALTER TABLE <테이블명> DROP INDEX <인덱스명>;
인덱스 추가 전략은 다음과 같다.
1. 제일 많이 돌릴 쿼리를 조사한다.
2. 위에서 조사한 쿼리에서 인덱스가 필요한지 검토한다. 이 때에 데이터 삽입, 갱신 연산도 같이 고려해야 한다.
- 어떤 컬럼에 고유 값이 많은지, 어떤 컬럼이 WHERE 절에 가장 많이 나타나는지 검토한다.
- 고유 값이 많은 컬럼의 인덱스는 매우 선택적이다.
- 인덱스를 설계할 때는 기존 쿼리에 필요한 인덱스 유형만 생각하지 말고 쿼리 최적화도 함께 고려해야 한다.
3. 여러 종류의 쿼리가 있어서 완벽한 인덱스 추가하지 못 하는 경우에는 타협을 보아야 한다.
- 인덱스 추가로 속도가 저하될 쿼리가 있다면 쿼리를 수정할 수 없는지 생각해 보자.
- 최적의 타협안을 찾으려면 쿼리와 인덱스를 함께 최적화해야 한다.
인덱스를 추가해야 하는 경우는 다음과 같다.
1. 응답 시간이 오래 걸리는 쿼리를 위한 인덱스를 추가하자.
2. 부하가 가장 많이 걸리는 쿼리를 위한 인덱스를 추가하자.
3. 가능하면 새 인덱스를 추가하는 것보다는 기존 인덱스를 확장하는 것이 좋다.
[출처] [MySQL] 인덱스 추가 전략|작성자 까미유
[출처] MySQL 인덱스 추가, 보기, 삭제|작성자 jjjhyeok
출처 - http://blog.naver.com/websearch?Redirect=Log&logNo=70106231179
===================================================================================
7.4.5. MySQL의 인덱스 사용 방법
인덱스는 특정 컬럼 값을 가지고 있는 열을 빨리 찾기 위해서 사용된다. 인덱스를 사용하지 않는다면, MySQL은 첫 번째 열부터 전체 테이블에 걸쳐서 연관된 열을 찾아야만 한다. 테이블이 크면 클수록 비용도 늘어나게 되는 것이다. 만일 테이블이 쿼리에 있는 질문 컬럼에 대한 인덱스를 가지고 있다면, MySQL은 모든 데이터를 조사하지 않고 데이터 파일 중간에서 검색 위치를 재빨리 알아낼 수가 있다. 만일 테이블이 1,000개의 열을 가지고 있다면, 이것은 최소 100배의 속도 향상을 가질 수가 있다. 만일 대부분의 열을 접속할 필요가 있는 경우라면, 순차적인 읽기 (sequential read)가 더 빠르게 되는데, 그 이유는 이 방법이 디스크 검색을 최소화 하기 때문이다.
대부분의 MySQL 인덱스 (PRIMARY KEY, UNIQUE, INDEX, 그리고 FULLTEXT)는 B-트리에 저장된다. 하지만, 공간 데이터 (spatial data)가 R-트리를 사용하고, MEMORY 테이블도 해시 인덱스 (hash indexes)를 지원한다는 점에 대해서는 함께 알아 두기 바란다.
스트링은 자동적으로 접두사- (prefix-)와 끝-공간 (end-space)을 압축한다.
MySQL은 다음과 같은 경우에 인덱스를 사용한다:
WHERE구문과 일치하는 열을 빨리 찾기 위해.- 열을 고려 대상에서 빨리 없애 버리기 위해. 만일 여러 개의 인덱스 사이에서 선택을 해야 한다면, MySQL은 보통 최소의 열을 찾는 인덱스를 사용한다.
- 조인 (join)을 실행할 때 다른 테이블에서 열을 추출하기 위해.
- 특정하게 인덱스된 컬럼 key_col을 위한
MIN()또는MAX()값을 찾기 위해. 이것은 인덱스에서 key_col 전에 발생하는 모든 키 부분의WHEREkey_part_N = constant를 사용할 수 있는지를 검사하는 프리 프로세서 (pre-processor)에 의해 최적화 된다. 이와 같은 경우, MySQL은 각각의MIN()또는MAX()수식에 대해서 단일 키 룩업 (lookup)을 실행해서 상수로 대체한다. 모든 수식이 상수로 대체가 되면, 쿼리는 즉시 리턴 된다. 예를 들면:
SELECT MIN(key_part2),MAX(key_part2)
FROM tbl_name WHERE key_part1=10;
- 사용할 수 있는 키의 최 좌측 접두사 (leftmost prefix)를 가지고 정렬 (sorting) 및 그룹화 (grouping)를 하기 위해 (예를 들면,
ORDER BYkey_part1, key_part2). 만일 모든 키 부분이DESC다음에 나온다면, 그 키는 역순으로 읽혀진다. - 데이터 열을 참조하지 않는 상태로 값을 추출하기 위해서 쿼리를 최적화 하는 경우도 있다. 만일 쿼리가 숫자로만 이루어진 테이블 컬럼만을 사용하고 몇몇 키에 대해서는 최 좌측 접두사를 가지고 있다면, 선택된 값은 인덱스 트리로부터 매우 빠른 속도로 추출이 될 것이다:
SELECT key_part3 FROM tbl_name
WHERE key_part1=1
여러분이 아래와 같은 SELECT 명령문을 입력했다고 가정하자:
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
만일 다중-컬럼 인덱스가 col1 과 col2에 존재한다면, 적당한 열을 직접 가져올 수가 있다. 만일 별개의 단일-컬럼 인덱스가 col1 과col2에 존재한다면, 옵티마이저는 어떤 인덱스가 가장 적은 열을 찾아내는지를 알아낸 후에 그 인덱스를 사용해서 열을 패치 (fetch)함으로써 가장 제한적인 인덱스를 찾으려고 시도를 한다.
만일 테이블이 다중-컬럼 인덱스를 가지고 있다면, 옵티마이저는 인덱스의 최 좌측 접두사를 사용해서 열을 찾는다. 예를 들면, 만일 여러분이 세개의 컬럼 인덱스를 (col1, col2, col3)에서 가지고 있다면, 여러분은 (col1), (col1, col2), 그리고 (col1, col2, col3)에서 검색 기능이 있는 인덱스를 가지게 되는 것이다.
컬럼이 인덱스의 최 좌측 접두사를 가지고 있지 않다면, MySQL은 부분 인덱스를 사용할 수 없게 된다. 아래와 같은 SELECT 명령문을 가지고 있다고 가정하자:
SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
만일 (col1, col2, col3)에 인덱스가 하나 존재한다면, 처음 두 개의 쿼리만이 인덱스를 사용하게 된다. 세 번째와 네 번째도 인덱스된 컬럼을 가지고 있기는 하지만, (col2) 와 (col2, col3) 는 (col1, col2, col3)의 최 좌측 접두사가 아니다.
B-트리 인덱스는 =, >, >=, <, <=, 또는 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이 3개의 문자 보다 길다면, MySQL 은 스트링용 패턴을 초기화 시키기 위해Turbo Boyer-Moore algorithm 을 사용하게 되며, 이 패턴을 사용해서 검색을 보다 빠르게 실행한다.
col_name IS NULL을 사용하는 검색은 col_name 이 인덱싱 되는 경우에는 적용할 수가 있다.
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 indexes)는 위에서 설명한 것과는 다소 다른 특징을 가지고 있다:
- 이것은
=또는<=>연산자를 사용하는 등식 비교에 대해서만 사용된다 (하지만 매우 빠름). 이것은 값의 범위를 찾기 위한<와 같은 비교 연산자와는 같이 사용되지 않는다. - 옵티마이저는
ORDER BY연산 속도를 증가 시키기 위해 이것을 사용할 수가 없다. (이런 타입의 인덱스는 다음 순서의 엔트리를 위한 검색용으로 사용될 수 없다.) - MySQL은 두 개의 값 사이에 얼마나 많은 열이 있는지를 추정할 수 없다 (이것은 사용한 인덱스가 어떤 것인지를 결정하기 위한 범위 옵티마이저가 사용하는 것이다).
- 단지 전체 키만이 열에 대한 검색을 할 수가 있다. (B-트리 인덱스를 사용한다면, 키의 최 좌측 접두사가 열 검색에 사용될 수가 있다.)
===================================================================================
|
mysql 인덱스와 외래키 걸기 alter table [tablename] add index [컬럼명]; 이렇게 인덱스를 걸어놓은 컬럼에만 foreign key 를 걸수 있다. 인덱스를 걸어놓지 않고 외래키를 걸면 SQL 문법 에러가 난다. foreign key 거는 문법은 alter table [테이블명] add constraint foreign key ( [컬럼명] ) references [테이블명] ( [컬럼명] ) 이고 디폴트는 no action 이다.
맨 뒤에 on delete, on update 문을 추가해서 cascade, restrict, set null, set default 등의 옵션을 줄수있다.
각각이 어떤 역할인지는 조금 생각하면 바로 알수있다.
참고로 mysql 4.0.x 대 버전에서는 SQL 에 컬럼명을 기재할때 그냥 쓰면되는데 5.0 대에서는 ` ` 로 둘러싸야 된다. |
출처 - http://blog.naver.com/declspec?Redirect=Log&logNo=10099470747
===================================================================================
중소형 DBMS mysql
보통의 DBMS 에는 index 를 걸수 있다 필드에....
이 index란 것은 주소와 같은 개념이다....
어떤 테이블에 여러 필드가 존재하고 각 필드에는 row 마다 다른 정보들이 들어가 있다..
여기서 특정 정보를 읽어 오기 위해서는 1번 주소부터 끝나는 주소까지 순차적으로 검색을 하게 된다.
이렇게 되면 100개의 데이터가 있고 그중 찾으려는 데이터가 100번째에 있다면 100번의 접근이 필요하다...
물론 운좋게 찾으려는 데이터가 10번째에 있다면 10번만 접근하게 된다.
이럴때 index를 해당 필드에 걸어 준다면 해당 필드값들은 b-tree 라는 자료 구조로 정리되게 된다. 데이터들이 섞이는것이아니라 그 데이터를 가리키는 주소를 b-tree 라는 형태로 저장을 해두는 것이다.
그래서 index가 걸린 필드를 검색하게 되면 필드에 직접 접근하는것이 아니라 b-tree를 저장해둔 곳으로 우선 접근해서 해당 데이터의 주소를 얻어 온다..
그런후에 그 주소로 직접 접근하게 된다.
이런식으로 접근하는 횟수가 많이 줄게 되기때문에 select 시에 많은 효과를 볼수가 있다..
그러나 index가 있으면 insert 또는 update 시에는 데이터 넣는 작업 외에도 b-tree를 재정의 해서 저장해야 하기때문에 속도가 느려지게 됨에 유의해야 한다.
또한 검색 조건에 맞는 index를 걸어야 좋은 효과를 볼수 있다.
[출처] mysql index 의 개념|작성자 다크몽
출처 - http://blog.naver.com/dark0111?Redirect=Log&logNo=30017199486
===================================================================================
어떤 DBMS라도 인덱스를 사용할 수 있죠. 그보다는 인덱스를 사용할 수 없으면 DBMS라고 부르기 힘들져.
mysql에서 index사용법에 대하여 몇가지 썰을 풀어 볼까 합니다.
누구나 다 아는 야그일것 같은데 헛튼짓 하는것 아닌지 모르겟네여.
다음과 같은 member 테이블을 가지고 놀아 봅시다.
CREATE TABLE member (
id CHAR(10),
name CHAR(20),
age int,
level CHAR(10)
);
요 테이블은 index가 잡혀 있지 않죠.
다음과 같은 select문에 대해서 잘 동작합니다.
select * from member where id = 'myid';
별 문제 없는것 같습니다. 그런데 레코드 수가 좀 많아 지면 query해오는데 시간이 점점 늘어 나져. 그 이유는 member 테이블에 있는 모든 레코드에 대하여 id가 'myid' 인지 전부 비교합니다.(full scan) 레코드가 늘어 날수록 시간은 점점 오래 걸리겠죠.
그리고 다음과 같은 select의 경우에는 시간이 오래 걸리져.
select * from member where id id ='myid' order by id;
id란 필드에 대한 값으로 전체를 sort해야 되기 때문이져.
테이블을 변경해 봅시다.
CREATE TABLE member (
id CAHR(10) NOT NULL DEFAULT '',
name CHAR(20),
age INT,
level CHAR(10),
INDEX myindex1 (id)
);
달라진것은 id 필드에 NOT NULL DEFAULT ''이 추가 됬고, 마지막에 index myindex1(id)라는 항목이 추가 됐네여. id라는 필드에 대하여 인덱스를 설정한 것입니다. myindex1은 인덱스의 이름일 뿐입니다. 사용자가 임의로 결정해 줍니다.
NOT NULL이 추가된 것은 index로 사용될 필드는 반드시 NOT NULL이어야 하기 때문이고, DEFUALT ''는 입력값이 없을 때 null이 아닌 어떤값을 디펄트로 사용하겠다는 겁니다.
그냥 index로 사용될 필드는 반드시 NOT NULL과 DEFAULT를 설정해야 된다고 알고 있음 되겟네여.
이렇게 index가 설정되면 id의 순서에 따라 정렬된 index를 가지기 때문에 레코드 수의 증가와 거의 관계없이(사실은 관계있지만 없다고 하겠습니다.) query시간이 일정합니다. 그리고 당근 order by id와 같이 정렬이 필요한 경우에도 따로 sort해줄 필요 없기 때문에 퀘리 시간이 짧지여.
select의 키가 되는 필드는 당연히 보다는 반듯이 index를 잡아 주어야 합니다.
이제 다음과 같은 select문을 생각해 봅시다.
select * from member where name='아무개' age > 20 order by id;
name과 age 필드가 where문에서 사용되었네여. 그렇다면 다음과 같이 name과 age도 index로 잡아 줍니다.
CREATE TABLE member (
id CHAR(10) NOT NULL DEFAULT '',
name CHAR(20) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0
level CHAR(10),
INDEX myindex1 (id),
INDEX myindex2 (name),
INDEX myindex3 (age)
);
별로 어렵지 않죠.
그렇다면 위의 CREATE문과 다음의 CREATE문은 어떤 차이가 있을 까여?
CREATE TABLE member (
id CHAR(10) NOT NULL DEFAULT '',
name CHAR(20) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0
level CHAR(10),
INDEX myindex1 (id, name, age)
);
위의 두개의 CREATE문으로 생성된 member 테이블은 다음과 같은 select에 대해서는 똑같은 동작을 합니다.
select * from member where id = 'myid' order by id;
select * from member where id = 'myid' and name='아무개' order by id;
하지만 다음의 select문에대해서는 서로 다르게 동작합니다.
select * from member where name='아무개';
음.. 두번째 member 테이블에서도 name 필드가 인덱스에 포함되었는데 왜 그럴까...
첫번째의 member 테이블에서는 id, name, age가 각기 다른 index로 잡혀 있었습니다. 그런데 두번째 member 테이블에서는 id, name, age가 하나의 index로 잡혀 있습니다.
두번째 테이블의 경우 id, name, age의 값이 concate된 값이 index로 작용합니다.
name이란 필드 하나는 index의 역활을 하지 못합니다.
그래서 name이나 age가 따로 키값으로 사용되는 select문에서는 인덱스의 덕을 보지 못하져.
하지만 다음과 같이 id, name, age가 순차적으로 검새될 때는 인덱스의 덕을 봅니다.
select * from member where id = 'myid';
select * from member where id = 'myid' and name='아무개';
select * from member where id = 'myid' and name='아무개' and age=20;
위의 sql의 where 부분은 다음과 같이 작용됩니다.(다음 구문은 sql이 아닙니다. 단지 설명을 위해서)
where id = 'myid'
where id+name = 'myid아무개'
where id+name+age = 'myid아무개20'
그렇다면 다음과 같은 select의 경우는 인덱스의 덕을 볼까여?
select * from member where id like 'my%' ane name='아무개';
id like 'my%'구문은 정규표현식과 비슷합니다. id가 'my'로 시작되는 값을 의미합니다.
적용되는 것을 다시 적어 보면
where id+name like 'my%아무개'
일단은 id부분에서는 덕을 봅니다. 하여간에 id부분이 my로 시작되는 레코드에 대해서 검색을 할테니까여 그러나 name이 '아무개'인 부분은 인덱스의 덕을 보지 못합니다. id필드값이 my로 시작되는 모든 레코드에 대하여 역시 풀스켄합니다.
테이블의 여러 필드가 인덱스로 잡혔을 때 동작하는 원리를 설명하겠습니다.
feild1, field2, field3가 인덱스로 잡혀 있을때 다음과 같은 where문이 있다고 합시다.
where field1 = 'value1' and field2 like 'value2%' and field3 > value3
1. field1의 값이 'value1'인 레코드의 레퍼런스 리스트를 구합니다. 이때 field1의 인덱스를 사용합니다. 요 리스트는 역시 field2와 field3에 대한 인덱스를 가지고 있지여.
2. 위에서 구한 리스트에서 field2의 값이 'value2'로 시작되는 레코드의 레퍼런스 리스트를 구합니다. 이때도 역시 field2의 인덱스를 사용해서 구합니다.
3. 다시 위에서 구한 리스트에서 field3의 값이 value3보다 큰 레코드의 레퍼런스 리스트를 구합니다.
4. 최종적으로 구한 리스트의 레퍼런스를 가지고 각 레퍼런스가 가리키는 레코드의 값을 반환합니다.
!!! 테이블의 index설정에 따라 시스템의 퍼포먼스가 엄청나게 차이납니다.
!!! 인덱스 설정은 엄!청! 중요합니다.
----------------------------------------------------------------------------------------
인덱스 정보 보기
mysql> SHOW INDEX FROM tablename
[출처] MYSQL INDEX에 관하여..|작성자 디플
출처 - http://blog.naver.com/rsr10?Redirect=Log&logNo=130007347356
'DB > MySQL' 카테고리의 다른 글
| Mysql 설치 후 초기 root 패스워드를 mysqladmin 으로 생성하기 (0) | 2012.04.01 |
|---|---|
| Mysql auto_increment (0) | 2012.03.30 |
| Mysql Command Line Client charset 설정 (0) | 2012.03.27 |
| Apache + php + mysql + jdk + tomcat + mod_jk 설치 (0) | 2012.03.24 |
| mysql storage engine - InnoDB (0) | 2012.03.19 |
