(MYSQL) Primary Key & Index 지정하는 방법

안녕하세요, 오늘은 MYSQL에서 인덱스를 지정하는 방법에 대해 포스팅 해보려고 합니다.

먼저 Primary Key와 Index에 대해 알아보겠습니다.

Primary Key란 ?

Primary Key(기본 키)는 데이터베이스 테이블에서 각 행(row)을 고유하게 식별하는 데 사용되는 하나 이상의 컬럼(column)으로, 기본 키에는 중복 값이 있을 수 없고 NULL 값이 허용되지 않습니다. 기본 키는 데이터 무결성을 보장하는 중요한 역할을 하며, 각 레코드를 구분할 수 있게 해줍니다.

Index란 ?

SQL에서 인덱스는 테이블의 데이터를 빠르게 검색하고 조회하는 데 도움을 주는 데이터 구조입니다.

데이터베이스 테이블의 특정 컬럼에 대해 인덱스를 생성하면, SQL 쿼리가 이 컬럼을 조회할 때 데이터베이스가 해당 인덱스를 사용하여 보다 효율적으로 데이터를 찾을 수 있습니다.

💡 인덱스를 설정하는 이유
인덱스가 중요한 이유는 데이터 검색 성능을 크게 향상시킬 수 있기 때문입니다. 인덱스 없이 테이블에서 데이터를 검색할 때는 모든 행을 순차적으로 확인해야 하므로(풀 테이블 스캔) 시간이 많이 소요될 수 있습니다. 특히, 테이블에 데이터가 많아질수록 성능 저하가 두드러집니다. 하지만 인덱스를 설정하면, 데이터베이스는 인덱스를 통해 필요한 데이터에 빠르게 접근할 수 있으며, 쿼리 속도가 크게 향상됩니다.

 

1.  컬럼 길이 확인

SELECT MAX(LENGTH(column)) FROM table;

 

인덱스 지정을 위해 열의 길이에 대해 먼저 확인합니다.

위 쿼리는 특정 테이블에서 특정 컬럼의 데이터 중 가장 긴 문자열의 길이를 반환합니다. 이 쿼리는 LENGTH 함수를 사용하여 각 행의 문자열 길이를 계산하고, 그중 가장 큰 값을 MAX 함수로 찾는 방식으로 작동합니다.

가령, 고객 이름을 저장하는 Customers 테이블에서 가장 긴 이름의 길이를 찾고 싶다면, 다음과 같이 쿼리를 작성할 수 있습니다.

-- 예시
SELECT MAX(LENGTH(CustomerName)) FROM Customers;

이 쿼리는 Customers 테이블의 CustomerName 컬럼을 대상으로 가장 긴 이름의 문자열 길이를 반환합니다. 예를 들어, Customers 테이블에 있는 고객 이름이 “Alice”, “Bob”, “Christina”라고 하면, 가장 긴 이름은 “Christina”로 길이가 9이기 때문에 결과는 9가 됩니다.

 

2.  컬럼 타입 변경

ALTER TABLE 테이블명 MODIFY 컬럼명 타입(숫자);

위 쿼리는 특정 테이블의 컬럼 데이터 타입을 수정하는 SQL 명령어입니다. 이 명령어를 통해 테이블의 기존 컬럼을 새롭게 정의할 수 있으며, 주로 컬럼의 데이터 타입을 변경하거나 크기를 조정할 때 사용합니다.

만약 Employees 테이블의 LastName 컬럼의 데이터 타입을 VARCHAR(100)으로 변경하고 싶다면, 다음과 같이 쿼리를 작성할 수 있습니다.

ALTER TABLE Employees MODIFY LastName VARCHAR(100);

이 쿼리는 Employees 테이블에서 LastName 컬럼의 데이터 타입을 VARCHAR(100)으로 변경하여, 이제 이 컬럼에 최대 100자의 문자열을 저장할 수 있게 합니다. 이처럼 ALTER TABLE 문을 사용하면 테이블 구조를 유연하게 수정할 수 있습니다.

 

3. Primary Key 지정

ALTER TABLE 테이블명 ADD PRIMARY KEY (컬럼);

위 쿼리는 특정 테이블의 컬럼에 기본 키(Primary Key) 제약 조건을 추가하는 SQL 명령어입니다. 기본 키는 테이블 내에서 각 행을 고유하게 식별하는 데 사용되는 하나 이상의 컬럼에 설정됩니다. 기본 키가 설정된 컬럼에는 중복 값이 있을 수 없고, NULL 값을 허용하지 않습니다.

Students 테이블에 StudentID 컬럼을 기본 키로 설정하고 싶다면, 다음과 같이 쿼리를 작성할 수 있습니다.

ALTER TABLE Students ADD PRIMARY KEY (StudentID);

이 쿼리는 Students 테이블에서 StudentID 컬럼에 기본 키 제약 조건을 추가합니다. 이로 인해 StudentID 컬럼은 고유한 값만을 가질 수 있으며, NULL 값이 들어갈 수 없습니다. 기본 키를 설정하면 데이터의 무결성을 보장할 수 있어, 테이블의 행을 고유하게 식별하는 데 유용합니다.

4. Index 지정

CREATE INDEX 인덱스명 ON 테이블명 (컬럼);

지정한 테이블의 특정 컬럼에 인덱스를 생성합니다.

Employees 테이블의 LastName 컬럼에 인덱스를 추가하고 싶다면, 다음과 같이 쿼리를 작성할 수 있습니다

CREATE INDEX idx_lastname ON Employees (LastName);

이 쿼리는 Employees 테이블의 LastName 컬럼에 idx_lastname이라는 이름의 인덱스를 생성합니다. 이제 LastName 컬럼에 대해 검색, 정렬, 필터링을 수행할 때 데이터베이스가 인덱스를 참조하여 더 빠르게 처리할 수 있습니다.

+) Tips

ALTER TABLE '테이블명' ADD COLUMN 컬럼명 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

쿼리는 기존 테이블에 새로운 컬럼을 추가하면서 그 컬럼을 자동 증가하는 기본 키로 설정하고, 테이블의 맨 앞에 위치시키는 역할을 합니다. 이 구문을 사용하면, 새로운 고유 식별자 컬럼을 기존 테이블에 추가하여 자동으로 증가하는 값을 할당받을 수 있습니다.

Employees 테이블에 자동 증가하는 EmployeeID 컬럼을 첫 번째 컬럼으로 추가하고 싶다면 다음과 같은 쿼리를 사용할 수 있습니다

ALTER TABLE Employees ADD COLUMN EmployeeID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

이를 통해 기존 테이블에 고유 식별자가 없을 때, 별도의 고유한 ID를 추가할 수 있게 되어 각 레코드를 쉽게 식별하고 참조할 수 있습니다.