mysql을 이용한 인덱스 생성 방법에 대한 자세한 설명과 장단점 분석_MySQL

머리말

인덱스는 MySQL이 데이터를 효율적으로 얻을 수 있도록 돕는 데이터 구조입니다. 이는 고성능에 매우 중요하지만 사람들은 종종 이를 잊어버리거나 오해합니다. 데이터가 클수록 인덱스가 더 중요해집니다. 규모가 작고 로드가 가벼운 데이터베이스는 인덱스가 없어도 좋은 성능을 발휘할 수 있지만, 데이터가 늘어나면 성능이 급격히 저하됩니다.

색인을 만드는 이유는 무엇인가요?

인덱스를 생성하면 시스템 성능이 크게 향상될 수 있기 때문입니다.

첫째, 고유 인덱스를 생성하면 데이터베이스 테이블의 각 데이터 행에 대한 고유성을 보장할 수 있습니다.

둘째, 데이터 검색 속도를 크게 높일 수 있으며 이는 인덱스를 만드는 주요 이유이기도 합니다.

셋째, 테이블 간의 연결 속도를 높일 수 있는데, 이는 데이터의 참조 무결성을 달성하는 데 특히 의미가 있습니다.

넷째, 데이터 검색을 위해 그룹화 및 정렬 절을 사용하면 쿼리에서 그룹화 및 정렬하는 시간도 크게 줄일 수 있습니다.

다섯째, 인덱스를 사용하면 쿼리 프로세스 중에 최적화 숨기기를 사용하여 시스템 성능을 향상시킬 수 있습니다.

어떤 사람들은 다음과 같이 질문할 수 있습니다. 인덱스를 추가하면 많은 이점이 있는데 테이블의 모든 열에 대해 인덱스를 생성하는 것은 어떨까요? 이 생각은 합리적이기는 하지만 일방적이기도 하다. 인덱스에는 많은 장점이 있지만 테이블의 모든 열에 인덱스를 추가하는 것은 매우 현명하지 않습니다.

인덱스를 추가하는 것에도 단점이 많기 때문입니다.

먼저 인덱스를 생성하고 유지하는 데 시간이 걸리며, 이 시간은 데이터 양이 늘어날수록 늘어납니다.

둘째, 인덱스는 데이터 테이블이 차지하는 데이터 공간 외에도 일정량의 물리적 공간을 차지해야 합니다. 클러스터형 인덱스를 만들려면 필요한 공간이 더 커집니다.

셋째, 테이블에 데이터를 추가, 삭제, 수정하는 경우 인덱스를 동적으로 유지해야 하므로 데이터 유지 속도가 저하됩니다.

색인 생성에 적합한 필드 종류:

인덱스는 데이터베이스 테이블의 특정 열에 구축됩니다. 따라서 인덱스를 생성할 때 어떤 열을 인덱싱할 수 있고 어떤 열을 인덱싱할 수 없는지 신중하게 고려해야 합니다.

일반적으로 다음과 같은 열에 인덱스를 생성해야 합니다.

첫째, 자주 검색되는 항목에 대한 검색 속도를 높일 수 있습니다.

둘째, 해당 열을 기본 키로 사용하여 열의 고유성을 강화하고 테이블의 데이터 배열 구조를 구성합니다.

셋째, 연결에 자주 사용되는 열의 경우 이러한 열은 주로 외래 키이므로 연결 속도를 높일 수 있습니다.

넷째, 인덱스가 정렬되어 있고 지정된 범위가 연속되어 있으므로 범위를 기준으로 자주 검색해야 하는 열에 대해 인덱스를 만듭니다.

다섯째, 인덱스가 정렬되어 있기 때문에 자주 정렬이 필요한 열에 인덱스를 생성하면 쿼리에서 인덱스 정렬을 활용하여 정렬 쿼리 시간을 단축할 수 있습니다.

여섯째, WHERE 절에서 자주 사용되는 컬럼에 인덱스를 생성하여 조건 판단을 빠르게 합니다.

인덱스 생성은 일반적으로 select의 where 조건을 기반으로 합니다. 예를 들어 select의 조건이 wheref1과 f2인 경우 f1 필드 또는 f2 필드에서 이력서를 인덱싱하면 쓸모가 없습니다. 그리고 f2를 동시에 기다리는 것이 유용합니다.

인덱스 생성에 적합하지 않은 필드 유형:

또한 색인을 생성하면 안 되는 열도 있습니다. 일반적으로 색인을 생성해서는 안 되는 열의 특징은 다음과 같습니다.

첫째, 쿼리에서 거의 사용되거나 참조되지 않는 열에 대해서는 인덱스를 생성해서는 안 됩니다. 이러한 컬럼은 거의 사용되지 않기 때문에 색인이 생성되거나 생성되지 않기 때문입니다.

쿼리 속도가 향상되지 않습니다. 반대로, 인덱스 추가로 인해 시스템 유지 속도가 감소하고 필요한 공간이 증가합니다.

둘째, 데이터 값이 적은 열의 경우 인덱스를 늘리면 안 됩니다. 이는 인사 테이블의 성별 열과 같이 이러한 열에는 값이 거의 없기 때문에

쿼리 결과에서 결과 집합의 데이터 행이 테이블의 데이터 행 중 많은 부분을 차지합니다. 즉, 테이블에서 검색해야 할 데이터 행의 큰 부분이 나타납니다.

색인을 늘려도 검색 속도가 크게 향상되지는 않습니다.

셋째, 텍스트, 이미지, 비트 데이터 유형으로 정의된 열에는 인덱스를 추가하면 안 됩니다. 이는 이러한 열의 데이터 볼륨이 상당히 크거나 값이 거의 없기 때문입니다.

넷째, 수정 성능이 검색 성능보다 훨씬 높을 경우 인덱스를 생성해서는 안 됩니다. 수정 성능과 검색 성능이 서로 상반되기 때문이다.

인덱스를 늘리면 검색 성능은 향상되지만 수정 성능은 저하됩니다. 인덱스를 줄이면 수정 성능이 향상되고 검색 성능이 저하됩니다.

따라서 수정 성능이 검색 성능보다 훨씬 높을 경우 인덱스를 생성해서는 안 됩니다.

색인 생성 방법:

1. 인덱스를 생성합니다(예: createindex08b89cf114db8f2ff9639abf26d3b9deontable_name(열 목록);

2. altertabletable_nameaddindex [인덱스 이름](열 목록)과 같은 테이블을 수정합니다.

3. 테이블 생성 시 인덱스를 지정합니다. 예: createtabletable_name([...],INDEX[인덱스 이름](열 목록))

테이블에서 인덱스를 보는 방법:

showindexfromtable_name;색인 보기

색인 유형 및 생성 예:

1.PRIMARYKEY(기본 키 인덱스)

mysql>altertabletable_nameaddprimarykey(`column`)

2.UNIQUE 또는 UNIQUEKEY(고유 인덱스)

mysql>altertabletable_nameaddunique(`column`)

3.FULLTEXT(전체 텍스트 색인)

mysql>altertabletable_nameaddfulltext(`column`)

4.INDEX(보통지수)

mysql>altertabletable_nameaddindexindex_name(`column`)

5. 다중 열 인덱스(클러스터형 인덱스)

mysql>altertable`table_name`addindexindex_name(`column1`,`column2`,`column3`)

테이블에서 인덱스 수정:

altertabletablenamedropprimarykey,addprimarykey(fileda,filedb)

요약

인덱스를 사용하면 레코드 수가 많은 테이블의 쿼리 속도를 향상시킬 수 있습니다. 하지만 인덱스는 공간을 차지하므로 인덱스 구축 시 이 글을 참고하시면 도움이 될 것입니다.