[PHP] 데이터베이스 인덱스 처리하는 방법

안녕하세요 오늘은 PHP에서 데이터베이스에서 인덱스 처리하는 방법을 알아보겠습니다.

 

데이터베이스 인덱스 처리는 데이터베이스의 효율성과 성능을 향상시키기 위한 중요한 기술입니다.

인덱스는 데이터베이스 테이블의 특정 컬럼(열)에 대한 포인터를 제공해 데이터 검색 시 검색 대상의 양을 줄여 검색 속도를 빠르게 만들어 주는데요.

하지만 이러한 인덱스도 잘못 사용하면 몇 가지 단점을 초래할 수 있습니다.

 

그럼 먼저 장단점을 알아보겠습니다.

 

장점

1. 검색 성능 개선 : 특히 대규모 데이터베이스에서 검색 속도가 크게 향상됩니다.

2. 정렬된 데이터 접근 : 인덱스를 통해 데이터를 정렬된 상태로 빠르게 접근할 수 있습니다.

 

단점

1. 추가 저장 공간 필요 : 인덱스 자체가 별도의 저장 공간을 차지합니다.

2. 쓰기 작업 지연 : 데이터가 업데이트될 때 인덱스도 갱신되어야 하므로 쓰기 작업에 부담이 될 수 있습니다.

 

이러한 단점들 때문에 모든 열에 인덱스를 생성하는 것은 비효율적이고, 검색 빈도가 높고 중요한 열에 인덱스를 설정하는 것이 좋습니다. 또 이러한 인덱스들은 데이터베이스의 구조나 형태가 변경됨에 따라 주기적으로 최적화가 필요합니다.

주로 SELECT 절에서 많이 사용되는 WHERE, ORDER BY절에 자주 등장하는 컬럼을 INDEX로 사용하면 조회 속도를 빠르게 만들 수 있습니다.

 

다음은 인덱스를 생성하는 예제입니다.

addIndexToTitle() 메소드를 사용해서 myTable 테이블의 title 컬럼에 인덱스를 추가합니다.

그리고 selectByTile 메소드를 사용해서 인덱스가 설정된 title 컬럼을 사용해 데이터를 조회합니다.

class Database { // 데이터베이스 관련 클래스
	
	private $host_name = "localhost";
	private $host_id = "root";
	private $host_password = "root";
	private $db_name = "test";
	private $conn;

	private function __construct() { // db 객체 초기화
		$this->conn = mysqli_connect($this->host_name, $this->user_name, $this->user_password);
		mysqli_select_db($this->conn, $this->db_name);
		mysqli_set_charset($this->conn, "utf8");		
    	}

	public function get_insert_id() { // 저장된 마지막 PK 값 가져오는 메소드
		$id = mysqli_insert_id($this->conn);
		return $id;
	}
    
	public function addIndexToTitle(){ // 검색 시 자주 사용하는 title에 인덱스를 추가하는 메소드
		$query = "CREATE INDEX user_title ON myTable (title)";
        
		if(@mysqli_query($this->conn, $query);){
			echo "Index created successfully";
		}else{
			echo "Error creating index: " . $conn->error;
			return FALSE;
		}
	}
    
	public function selectByTitle($title){ // 인덱스 컬럼인 title에서 $title로 시작하는 사용자 정보 조회
		$query = "SELECT title FROM myTable WHERE title LIKE '$title%'";
		$result = @mysqli_query($this->conn, $query);
        
		return $result;
	}

	// 사용자 정보 저장 및 카테고리, 이미지 패스 저장 메소
	public function addMember($user_id, $title, $context, $thumbnail_path, $image_path, $category) {
		// mysqli_begin_transaction($this->conn); 해당 코드를 오토커밋 방식 대신 사용할 수도 있다.
		mysqli_autocommit($this->conn, FALSE);
		$query = "INSERT INTO `member` VALUES (NULL , '{$user_id}', '{$title}', '{$context}', NOW(), '{$thumbnail_path}')";
		
		$result = @mysqli_query($this->conn, $query);
		$id = get_insert_id();
		
		if ($result) {
			
			foreach($category as $value) { // 카테고리 저장 및 실패 시 롤백				
				$result = $this->addCategory($id, $value);
				if (!$result) {
					mysqli_rollback($this->conn);	
					return FALSE;
				}			
			}
			
			foreach($image_path as $path) { // 이미지 패스 저장 및 실패 시 롤백
				$result = $this->addImage($id, $path);
				if (!$result) {
					mysqli_rollback($this->conn);	
					return FALSE;
				}
			}			
			
			mysqli_commit($this->conn);	
			mysqli_autocommit($this->conn, TRUE);
			return TRUE;		
		}
		return FALSE;
	}	

	//카테고리 추가
	public function addCategory($user_id, $category_id) {
		$query = "INSERT INTO `member_categorys` VALUES (NULL, '{$user_id}', '{$category_id}')";
		$result = @mysqli_query($this->conn, $query);
		return $result;
	}	

	//이미지 추가
	public function addImage($user_id, $image_path) {
		$query = "INSERT INTO `member_images` VALUES (NULL, '{$user_id}', '{$image_path}')";
		$result = @mysqli_query($this->conn, $query);
		return $result;
	}
	
}

 

이러한 방식으로 데이터를 조회할 때 효율적인 인덱스 처리방법을 알아보았습니다~