728x90
반응형

1. 프로시저란 무엇인가?

프로시저(Procedure)는 데이터베이스에서 실행할 수 있는 저장된 프로그램 단위입니다. 이는 SQL 문들의 집합으로, 복잡한 처리를 모듈화하여 재사용성을 높이고 편리하게 사용할 수 있게 해줍니다.

1.1 프로시저의 특징

  • 여러 SQL 문을 하나의 단위로 실행
  • 매개변수를 받아 동적으로 동작 가능
  • 데이터베이스 내에 저장되어 필요할 때 호출하여 사용
  • 보안성 향상 및 네트워크 트래픽 감소

2. 프로시저의 장점

2.1 성능 향상

프로시저는 미리 컴파일되어 저장되므로, 실행 시 빠른 처리가 가능합니다.

2.2 재사용성

한 번 작성된 프로시저는 여러 곳에서 반복적으로 사용할 수 있습니다.

2.3 보안 강화

데이터베이스 객체에 대한 직접적인 접근을 제한하고, 프로시저를 통해서만 데이터 조작을 허용할 수 있습니다.

3. 프로시저 작성 및 사용 예시

3.1 MySQL에서 프로시저 생성 예시

DELIMITER //

CREATE PROCEDURE GetEmployeeCount(IN departmentId INT, OUT employeeCount INT)
BEGIN
    SELECT COUNT(*) INTO employeeCount 
    FROM employees 
    WHERE department_id = departmentId;
END //

DELIMITER ;

이 프로시저는 특정 부서의 직원 수를 계산합니다.

 

3.2 프로시저 호출

CALL GetEmployeeCount(1, @count);
SELECT @count AS 'Employee Count';

 

3.3 Node.js에서 프로시저 사용 예시

const mysql = require('mysql2/promise');

async function callProcedure() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'your_username',
    password: 'your_password',
    database: 'your_database'
  });

  try {
    const [results] = await connection.execute('CALL GetEmployeeCount(?, @count)', [1]);
    const [countResult] = await connection.execute('SELECT @count AS count');
    console.log('Employee Count:', countResult[0].count);
  } catch (error) {
    console.error('Error calling procedure:', error);
  } finally {
    await connection.end();
  }
}

callProcedure();

 

4. 프로시저의 고급 기능

4.1 조건문 사용

프로시저 내에서 IF-ELSE 문을 사용하여 조건에 따른 로직 실행이 가능합니다.

DELIMITER //

CREATE PROCEDURE CheckBalance(IN accountId INT)
BEGIN
    DECLARE balance DECIMAL(10,2);
    
    SELECT account_balance INTO balance
    FROM accounts
    WHERE id = accountId;
    
    IF balance < 0 THEN
        SELECT 'Account is overdrawn' AS message;
    ELSE
        SELECT 'Account balance is positive' AS message;
    END IF;
END //

DELIMITER ;

 

4.2 루프 사용

WHILE, REPEAT, LOOP 등을 사용하여 반복 작업을 수행할 수 있습니다.

DELIMITER //

CREATE PROCEDURE GenerateNumbers(IN max INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    
    WHILE i <= max DO
        SELECT i;
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

 

5. 프로시저 사용 시 주의사항

5.1 과도한 사용 지양

모든 로직을 프로시저로 구현하면 애플리케이션의 유연성이 떨어질 수 있습니다.

5.2 버전 관리의 어려움

데이터베이스에 저장되는 프로시저는 일반 코드에 비해 버전 관리가 어려울 수 있습니다.

5.3 디버깅의 복잡성

프로시저 내부의 로직을 디버깅하는 것은 일반 애플리케이션 코드보다 복잡할 수 있습니다.

결론

프로시저는 데이터베이스 프로그래밍에서 강력하고 유용한 도구입니다. 복잡한 비즈니스 로직을 데이터베이스 레벨에서 처리할 수 있게 해주며, 성능 향상과 보안 강화에 기여합니다. 그러나 과도한 사용은 피하고, 적절한 상황에서 균형있게 사용하는 것이 중요합니다.

프로시저를 효과적으로 활용하면 데이터 중심의 작업을 효율적으로 처리할 수 있으며, 애플리케이션과 데이터베이스 간의 결합도를 낮출 수 있습니다. 하지만 항상 유지보수성과 확장성을 고려하여 설계해야 합니다. 프로시저는 데이터베이스 개발자의 강력한 도구이지만, 그 사용에는 신중한 접근이 필요합니다.

728x90
반응형
728x90
반응형

1. 인덱스(Index)란 무엇인가?

인덱스는 데이터베이스 테이블의 검색 속도를 향상시키기 위해 사용되는 데이터 구조입니다. 책의 색인과 유사하게, 데이터베이스 인덱스는 특정 열(컬럼)의 값과 해당 레코드의 위치 정보를 저장합니다.

예를 들어, 도서관에서 책을 찾을 때 책의 제목이나 저자별로 정리된 목록을 사용하는 것과 비슷합니다.


2. 인덱스의 장점

2.1 검색 속도 향상

인덱스의 주요 장점은 데이터 검색 속도를 크게 향상시킨다는 점입니다. 인덱스가 없으면 데이터베이스는 모든 레코드를 순차적으로 검색해야 하지만, 인덱스를 사용하면 원하는 데이터를 빠르게 찾을 수 있습니다.

 

예시 코드 (Node.js와 MySQL 사용):

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your_username',
  password: 'your_password',
  database: 'your_database'
});

// 인덱스가 없는 경우
connection.query('SELECT * FROM users WHERE username = ?', ['john'], (err, results) => {
  if (err) throw err;
  console.log(results);
});

// 인덱스 생성
connection.query('CREATE INDEX idx_username ON users (username)', (err) => {
  if (err) throw err;
  console.log('Index created successfully');
});

// 인덱스를 사용한 검색
connection.query('SELECT * FROM users WHERE username = ?', ['john'], (err, results) => {
  if (err) throw err;
  console.log(results);
});

 

2.2 정렬 및 그룹화 성능 개선

인덱스는 ORDER BY나 GROUP BY 연산의 성능도 향상시킵니다. 이미 정렬된 상태로 데이터를 저장하기 때문에, 추가적인 정렬 작업이 필요하지 않습니다.


3. 인덱스의 단점

3.1 추가 저장 공간 필요

인덱스는 별도의 데이터 구조로 저장되기 때문에 추가적인 저장 공간이 필요합니다. 대규모 데이터베이스의 경우 이는 상당한 양의 저장 공간을 차지할 수 있습니다.

3.2 쓰기 작업 성능 저하

INSERT, UPDATE, DELETE와 같은 쓰기 작업 시 인덱스도 함께 업데이트해야 하므로 이러한 작업의 성능이 저하될 수 있습니다.

예시 코드:

// 인덱스가 있는 테이블에 데이터 삽입
connection.query('INSERT INTO users (username, email) VALUES (?, ?)',
['newuser', 'newuser@example.com'],
(err) => {
  if (err) throw err;
  console.log('User inserted');
});

4. 인덱스 사용 시 주의사항

4.1 적절한 컬럼 선택

모든 컬럼에 인덱스를 생성하는 것은 바람직하지 않습니다. 주로 검색 조건으로 자주 사용되는 컬럼이나 JOIN 연산에 사용되는 컬럼에 인덱스를 생성하는 것이 좋습니다.

4.2 인덱스 관리

데이터베이스의 데이터가 크게 변경되면 인덱스의 효율성이 떨어질 수 있습니다. 주기적으로 인덱스를 재구축하거나 최적화하는 것이 중요합니다.

// 인덱스 재구축
connection.query('ALTER TABLE users DROP INDEX idx_username, ADD INDEX idx_username (username)', (err) => {
  if (err) throw err;
  console.log('Index rebuilt');
});

 

결론

인덱스는 데이터베이스 쿼리의 성능을 크게 향상시킬 수 있는 강력한 도구입니다.

하지만 무분별한 사용은 오히려 성능 저하를 초래할 수 있으므로, 데이터베이스의 특성과 쿼리 패턴을 고려하여 적절히 사용해야 합니다. 인덱스의 장단점을 잘 이해하고, 필요한 곳에 적절히 적용함으로써 데이터베이스의 전반적인 성능을 최적화할 수 있습니다. 정기적인 모니터링과 관리를 통해 인덱스의 효율성을 유지하는 것도 중요합니다. 이를 통해 더 빠르고 효율적인 데이터베이스 시스템을 구축할 수 있습니다.

728x90
반응형
728x90
반응형

인덱스란 무엇인가?

인덱스는 데이터베이스에서 검색 속도를 향상시키기 위해 사용되는 자료 구조입니다. 책의 찾아보기와 유사한 역할을 하며, 원하는 정보를 빠르게 찾을 수 있게 해줍니다.

예를 들어, 도서관에서 책을 찾는 상황을 생각해 봅시다. 인덱스가 없다면 모든 책장을 일일이 살펴봐야 하지만, 도서 분류 시스템(인덱스)이 있다면 원하는 책의 위치를 빠르게 찾을 수 있습니다.

 

인덱스의 작동 원리

인덱스는 주로 B-tree(Balanced Tree) 구조를 사용합니다. B-tree의 주요 특징은 다음과 같습니다:

a) 균형 트리: 모든 리프 노드가 같은 레벨에 있어 검색 성능이 일정합니다.

b) 탐색 트리: 항상 정렬된 상태를 유지하여 빠른 검색이 가능합니다.

c) 다중 데이터 저장: 하나의 노드에 여러 데이터를 저장할 수 있어 효율적입니다.

 

B-tree를 사용하면 데이터를 로그 시간 복잡도(O(log n))로 검색할 수 있어, 전체 데이터를 스캔하는 것보다 훨씬 빠릅니다.

 

인덱스의 종류

a) 클러스터드 인덱스 (Clustered Index)

  • 테이블의 물리적 데이터 순서를 결정합니다.
  • 테이블당 하나만 존재할 수 있습니다.
  • 주로 기본키(Primary Key)에 자동으로 생성됩니다.
  • 데이터 검색이 매우 빠르지만, 데이터 삽입/수정 시 비용이 높을 수 있습니다.

b) 논클러스터드 인덱스 (Non-clustered Index)

  • 별도의 인덱스 구조를 생성합니다.
  • 하나의 테이블에 여러 개 생성이 가능합니다.
  • 인덱스 구조에 실제 데이터의 위치 정보를 저장합니다.
  • 클러스터드 인덱스에 비해 검색은 조금 느리지만, 데이터 변경에 따른 부담이 적습니다.

인덱스 사용의 실제 예시

음식점 정보를 담은 테이블에서 '서울'에 있는 음식점을 찾는 상황을 가정해 봅시다.

  • 인덱스가 없을 때: 전체 데이터(예: 100만 개)를 순차적으로 스캔하며 '서울'인 주소를 찾습니다.
  • 성능: 매우 느림 (모든 데이터를 확인해야 함)
  • 주소에 인덱스가 있을 때:
    1. 인덱스 트리에서 '서울'을 빠르게 찾습니다.
    2. 해당 데이터의 실제 위치를 확인합니다.
    3. 필요한 데이터만 접근하여 결과를 반환합니다. 성능: 매우 빠름 (필요한 데이터에만 직접 접근)

실제 12만 개의 데이터에서 인덱스 사용 전후를 비교한 결과:

  • 인덱스 사용 전: 검색 시간 약 0.44초
  • 인덥스 사용 후: 검색 시간 약 0.02초 (약 20배 성능 향상)

효과적인 인덱스 사용시기

a) 적절한 컬럼 선택:

  • WHERE, JOIN, ORDER BY 절에 자주 사용되는 컬럼
  • 중복도가 낮은 컬럼 (예: 주민등록번호, 이메일 주소)
  • 범위 검색보다는 정확한 값 검색에 사용되는 컬럼

b) 복합 인덱스 고려:

  • 여러 컬럼을 함께 사용하는 쿼리가 자주 실행된다면 복합 인덱스를 고려하세요.
  • 컬럼 순서가 중요합니다. 가장 자주 사용되는 컬럼을 첫 번째로 지정하세요.

c) 인덱스 개수 제한:

  • 너무 많은 인덱스는 오히려 성능을 저하시킬 수 있습니다.
  • 데이터 변경 작업(INSERT, UPDATE, DELETE)시 모든 인덱스를 업데이트 필요. (쓰기가 많은 경우 비추천)

d) 주기적인 인덱스 관리:

  • 사용되지 않는 인덱스는 제거하세요.
  • 필요에 따라 인덱스를 재구성하여 최적의 상태를 유지하세요.

주의사항

  • 인덱스는 검색 성능을 향상시키지만, 데이터 변경 작업의 성능은 저하시킬 수 있습니다.
  • 작은 테이블에서는 인덱스의 효과가 미미할 수 있습니다. (대규모에서 추천)
  • 인덱스는 추가적인 저장 공간을 사용합니다.

출처 : https://www.youtube.com/watch?v=ywYdEls88Sw&list=PLgXGHBqgT2TvpJ_p9L_yZKPifgdBOzdVH&index=99

 

728x90
반응형

+ Recent posts