MariaDB 고급 SQL 및 쿼리 최적화

1️⃣ Index 개념 및 성능 최적화 (CREATE INDEX, EXPLAIN)

Index 개념

Index는 MariaDB에서 데이터를 빠르게 검색할 수 있도록 도와주는 중요한 요소입니다. 데이터베이스에서 검색 속도를 향상시키기 위해 테이블의 열에 대해 인덱스를 생성할 수 있습니다. 인덱스는 마치 책의 목차와 같아서, 특정 데이터를 찾을 때 전체 데이터를 검색하지 않고 빠르게 위치를 찾아주는 역할을 합니다.

Index 생성 예시

CREATE INDEX idx_users_name ON users (name);

이 쿼리는 users 테이블의 name 열에 인덱스를 생성합니다. 이 인덱스를 사용하면 이름으로 데이터를 검색할 때 성능이 크게 향상됩니다.

EXPLAIN을 이용한 성능 분석

EXPLAIN 명령어를 사용하면 쿼리가 어떻게 실행되는지 분석할 수 있습니다. 이를 통해 쿼리 성능을 개선할 수 있는 방법을 찾을 수 있습니다.

EXPLAIN 사용 예시

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

EXPLAIN을 실행하면 쿼리의 실행 계획을 보여주며, 이 정보를 통해 쿼리 최적화 방안을 찾을 수 있습니다. 예를 들어, 쿼리가 테이블을 풀 스캔하는 경우 인덱스를 추가해야 할 수 있습니다.

2️⃣ JOIN 종류 및 성능 차이 (INNER JOIN, LEFT JOIN, RIGHT JOIN)

JOIN 개념

JOIN은 여러 테이블에서 데이터를 결합할 때 사용됩니다. MariaDB에서 지원하는 JOIN 종류는 여러 가지가 있으며, 각각의 JOIN은 성능과 용도에 따라 다르게 사용됩니다.

INNER JOIN

INNER JOIN은 두 테이블에서 일치하는 데이터를 결합합니다. 일치하는 데이터만 결과로 반환됩니다.

INNER JOIN 예시

SELECT users.name, orders.id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

이 쿼리는 users 테이블과 orders 테이블에서 user_id가 일치하는 데이터를 반환합니다.

LEFT JOIN

LEFT JOIN은 왼쪽 테이블의 모든 데이터를 반환하고, 오른쪽 테이블에서 일치하는 데이터가 없는 경우 NULL을 반환합니다.

LEFT JOIN 예시

SELECT users.name, orders.id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

이 쿼리는 users 테이블의 모든 데이터를 반환하고, orders 테이블에서 일치하는 데이터를 반환합니다. 만약 orders 테이블에 일치하는 데이터가 없다면 NULL이 반환됩니다.

RIGHT JOIN

RIGHT JOIN은 오른쪽 테이블의 모든 데이터를 반환하고, 왼쪽 테이블에서 일치하는 데이터가 없는 경우 NULL을 반환합니다.

RIGHT JOIN 예시

SELECT users.name, orders.id
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

이 쿼리는 orders 테이블의 모든 데이터를 반환하고, users 테이블에서 일치하는 데이터를 반환합니다. 만약 users 테이블에 일치하는 데이터가 없다면 NULL이 반환됩니다.

3️⃣ 서브쿼리와 CTE (Common Table Expressions)

서브쿼리

서브쿼리는 다른 쿼리 안에 포함된 쿼리입니다. 서브쿼리는 주로 SELECT, INSERT, UPDATE, DELETE 문에서 사용됩니다.

서브쿼리 예시

SELECT name
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

이 쿼리는 orders 테이블에서 amount가 100을 초과하는 user_id를 찾고, 해당 user_id를 가진 users 테이블의 name을 반환합니다.

CTE (Common Table Expressions)

CTE는 쿼리 내에서 임시 결과를 정의하는 방법입니다. 서브쿼리와 비슷하지만 가독성이 뛰어나고 재사용이 용이한 장점이 있습니다.

CTE 예시

WITH OrderSummary AS (
    SELECT user_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id
)
SELECT users.name, OrderSummary.total_amount
FROM users
INNER JOIN OrderSummary ON users.id = OrderSummary.user_id;

CTE는 쿼리의 상단에 정의되며, 후속 쿼리에서 참조할 수 있습니다. 이 방식은 복잡한 쿼리를 더 읽기 쉽고 효율적으로 만듭니다.

4️⃣ 트랜잭션 관리 (BEGIN, COMMIT, ROLLBACK)

트랜잭션 개념

트랜잭션은 데이터베이스에서 하나의 작업 단위로, 여러 쿼리를 묶어서 하나의 단위로 처리하는 기능입니다. 트랜잭션을 사용하면 데이터의 일관성, 원자성, 격리성, 지속성을 보장할 수 있습니다.

트랜잭션 시작 (BEGIN)

트랜잭션을 시작하려면 BEGIN 명령어를 사용합니다.

BEGIN;

트랜잭션 커밋 (COMMIT)

트랜잭션을 커밋하면, 트랜잭션 내에서 수행된 모든 변경사항이 실제 데이터베이스에 반영됩니다.

COMMIT;

트랜잭션 롤백 (ROLLBACK)

트랜잭션 중에 오류가 발생하면 ROLLBACK을 사용하여 모든 변경사항을 취소할 수 있습니다.

ROLLBACK;

5️⃣ Stored Procedure, Function, Trigger 활용

Stored Procedure (저장 프로시저)

저장 프로시저는 미리 작성된 SQL 쿼리 집합으로, 여러 번 실행할 수 있는 기능을 제공합니다. 프로시저는 데이터를 수정하거나 처리하는 복잡한 작업을 자동화할 수 있습니다.

Stored Procedure 예시

DELIMITER $$

CREATE PROCEDURE GetUserInfo(IN userId INT)
BEGIN
    SELECT * FROM users WHERE id = userId;
END $$

DELIMITER ;

이 프로시저는 userId를 입력받아 해당 사용자의 정보를 반환합니다.

Function (함수)

함수는 입력값을 받아서 값을 반환하는 SQL 쿼리입니다. 함수는 주로 값을 계산하거나 특정 작업을 처리하는 데 사용됩니다.

Function 예시

CREATE FUNCTION GetUserAge(userId INT)
RETURNS INT
BEGIN
    DECLARE age INT;
    SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) INTO age
    FROM users WHERE id = userId;
    RETURN age;
END;

이 함수는 사용자 ID를 입력받아 사용자의 나이를 반환합니다.

Trigger (트리거)

트리거는 특정 이벤트(INSERT, UPDATE, DELETE)가 발생할 때 자동으로 실행되는 SQL 명령입니다.

Trigger 예시

CREATE TRIGGER UpdateUserTimestamp
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users SET last_modified = NOW() WHERE id = OLD.id;
END;

이 트리거는 users 테이블에서 데이터가 업데이트될 때마다 last_modified 필드를 현재 시간으로 업데이트합니다.

6️⃣ View 및 Materialized View 개념

View

View는 하나 이상의 테이블에서 데이터를 조회하는 가상 테이블입니다. View는 데이터를 저장하지 않으며, 단순히 쿼리의 결과를 반환합니다.

View 예시

CREATE VIEW ActiveUsers AS
SELECT name, email FROM users WHERE status = 'active';

이 쿼리는 ActiveUsers라는 View를 생성하며, users 테이블에서 상태가 active인 사용자만 반환합니다.

Materialized View

Materialized View는 View와 유사하지만, 쿼리 결과를 실제로 저장하는 테이블입니다. MariaDB에서는 직접적으로 Materialized View를 지원하지 않지만, 이를 흉내 내기 위해 주기적인 쿼리 업데이트를 사용하여 비슷한 기능을 구현할 수 있습니다.