이 랩에서는 인덱스를 사용하여 SQLite 데이터베이스 성능을 최적화하는 방법을 배우게 됩니다. 쿼리 속도를 향상시키기 위해 단일 열 인덱스를 생성하고, 실용적인 적용과 분석에 중점을 둡니다. 또한 쿼리 플랜을 분석하고 중복 인덱스를 삭제하는 방법도 배우게 됩니다.
이 단계에서는 SQLite 데이터베이스와 employees 테이블을 생성합니다. 그런 다음 테이블에 샘플 데이터를 삽입합니다.
먼저, LabEx VM 에서 터미널을 엽니다. 기본 경로는 /home/labex/project입니다.
my_database.db라는 SQLite 데이터베이스를 생성하려면 다음 명령을 실행합니다.
sqlite3 my_database.db이 명령은 프로젝트 디렉토리에 my_database.db라는 새 SQLite 데이터베이스 파일을 생성하고 SQLite 셸을 엽니다.
다음으로, 다음과 같은 구조로 employees 테이블을 생성합니다.
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
email TEXT,
department TEXT
);이 SQL 문은 employees라는 테이블을 생성하며, id, first_name, last_name, email 및 department의 다섯 개의 열을 갖습니다. id 열은 기본 키 (primary key) 로 설정되어 고유한 값을 포함해야 합니다.
이제 employees 테이블에 샘플 데이터를 삽입합니다.
INSERT INTO employees (first_name, last_name, email, department) VALUES
('John', 'Doe', 'john.doe@example.com', 'Sales'),
('Jane', 'Smith', 'jane.smith@example.com', 'Marketing'),
('Robert', 'Jones', 'robert.jones@example.com', 'Engineering'),
('Emily', 'Brown', 'emily.brown@example.com', 'Sales'),
('Michael', 'Davis', 'michael.davis@example.com', 'Marketing');이렇게 하면 employees 테이블에 다섯 개의 데이터 행이 삽입됩니다.
데이터가 올바르게 삽입되었는지 확인하려면 다음 명령을 실행합니다.
SELECT * FROM employees;다음과 같은 출력을 볼 수 있습니다.
1|John|Doe|john.doe@example.com|Sales
2|Jane|Smith|jane.smith@example.com|Marketing
3|Robert|Jones|robert.jones@example.com|Engineering
4|Emily|Brown|emily.brown@example.com|Sales
5|Michael|Davis|michael.davis@example.com|Marketing이 단계에서는 employees 테이블의 last_name 열에 인덱스를 생성합니다.
인덱스는 데이터베이스 검색 엔진이 데이터 검색 속도를 높이기 위해 사용할 수 있는 특수한 조회 테이블입니다.
last_name 열에 idx_lastname이라는 인덱스를 생성하려면 다음 명령을 실행합니다.
CREATE INDEX idx_lastname ON employees (last_name);이 SQL 문은 employees 테이블의 last_name 열에 idx_lastname이라는 인덱스를 생성합니다.
인덱스가 생성되었는지 확인하려면 다음 명령을 사용할 수 있습니다.
PRAGMA index_list(employees);이 명령은 방금 생성한 idx_lastname 인덱스를 포함하여 employees 테이블의 인덱스 목록을 표시합니다. 다음과 유사한 출력을 볼 수 있습니다.
0|idx_lastname|0|c|0이 출력은 idx_lastname 인덱스가 employees 테이블에 존재함을 확인합니다.
이 단계에서는 EXPLAIN QUERY PLAN 명령을 사용하여 SQLite 가 쿼리를 실행하는 방식을 분석하는 방법을 배웁니다. 이는 쿼리 성능을 이해하고 잠재적인 병목 현상을 식별하는 강력한 도구입니다.
쿼리를 분석하려면 쿼리 앞에 EXPLAIN QUERY PLAN을 붙입니다. 예를 들어, 다음 쿼리를 분석하려면:
SELECT * FROM employees WHERE last_name = 'Smith';다음 명령을 실행합니다.
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE last_name = 'Smith';출력은 다음과 유사합니다.
QUERY PLAN
`--SEARCH employees USING INDEX idx_lastname (last_name=?)이 출력은 SQLite 가 idx_lastname 인덱스를 사용하여 성이 'Smith'인 직원을 찾는다는 것을 알려줍니다. SEARCH 키워드는 SQLite 가 인덱스를 사용하여 검색을 수행하고 있음을 나타냅니다.
인덱스가 사용되지 않은 경우 출력은 다르게 나타납니다. 예를 들어, 이름이 'John'인 직원을 쿼리하는 경우 (first_name 열에 인덱스를 생성하지 않은 경우) 출력은 다음과 같습니다.
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE first_name = 'John';출력은 다음과 유사합니다.
QUERY PLAN
`--SCAN employeesSCAN 키워드는 SQLite 가 전체 테이블 스캔을 수행하고 있음을 나타냅니다. 즉, 이름이 'John'인 직원을 찾기 위해 테이블의 모든 행을 검사해야 합니다. 이는 인덱스를 사용하는 것보다 효율성이 떨어집니다.
쿼리 계획 분석을 더 의미 있게 만들기 위해 더 많은 데이터를 삽입해 보겠습니다. 다음 데이터를 employees 테이블에 삽입합니다.
INSERT INTO employees (first_name, last_name, email, department) VALUES
('Alice', 'Johnson', 'alice.johnson@example.com', 'HR'),
('Bob', 'Williams', 'bob.williams@example.com', 'Finance'),
('Charlie', 'Brown', 'charlie.brown@example.com', 'IT'),
('David', 'Miller', 'david.miller@example.com', 'Sales'),
('Eve', 'Wilson', 'eve.wilson@example.com', 'Marketing'),
('John', 'Taylor', 'john.taylor@example.com', 'Engineering'),
('Jane', 'Anderson', 'jane.anderson@example.com', 'HR'),
('Robert', 'Thomas', 'robert.thomas@example.com', 'Finance'),
('Emily', 'Jackson', 'emily.jackson@example.com', 'IT'),
('Michael', 'White', 'michael.white@example.com', 'Sales');이제 정렬을 포함하는 더 복잡한 쿼리를 분석해 보겠습니다. 'Sales' 부서의 모든 직원을 찾고 성으로 정렬하려는 경우 다음 쿼리를 사용할 수 있습니다.
SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;쿼리 계획을 분석합니다.
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;출력은 다음과 유사할 수 있습니다.
QUERY PLAN
`--SCAN employees USING INDEX idx_lastname이 경우 SQLite 는 전체 테이블 스캔을 수행한 다음 결과를 정렬합니다.
department 열에 인덱스를 생성해 보겠습니다.
CREATE INDEX idx_department ON employees (department);이제 쿼리 계획을 다시 분석합니다.
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' ORDER BY last_name;출력은 다음과 같이 변경될 수 있습니다.
QUERY PLAN
|--SEARCH employees USING INDEX idx_department (department=?)
`--USE TEMP B-TREE FOR ORDER BY이제 SQLite 는 idx_department 인덱스를 사용하여 'Sales' 부서의 직원을 찾지만, 여전히 결과를 정렬해야 합니다.
이 단계에서는 SQLite 에서 중복 인덱스를 식별하고 삭제하는 방법을 배웁니다. 중복 인덱스는 읽기 작업에 아무런 이점을 제공하지 않으면서 쓰기 작업의 오버헤드를 증가시켜 데이터베이스 성능에 부정적인 영향을 미칠 수 있습니다.
department 및 last_name 열 모두에 인덱스를 생성해 보겠습니다.
CREATE INDEX idx_department_lastname ON employees (department, last_name);이제 employees 테이블의 모든 인덱스를 나열합니다.
PRAGMA index_list(employees);다음과 유사한 출력을 볼 수 있습니다.
0|idx_lastname|0|c|0
1|idx_department|0|c|0
2|idx_department_lastname|0|c|0이제 department 및 last_name으로 필터링하는 쿼리를 분석해 보겠습니다.
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales' AND last_name = 'Doe';출력은 다음과 유사할 수 있습니다.
QUERY PLAN
`--SEARCH employees USING INDEX idx_department_lastname (department=? AND last_name=?)이 출력은 SQLite 가 이 쿼리에 idx_department_lastname 인덱스를 사용하고 있음을 나타냅니다.
이제 department로만 필터링하는 쿼리를 분석해 보겠습니다.
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE department = 'Sales';출력은 다음과 유사할 수 있습니다.
QUERY PLAN
`--SEARCH employees USING INDEX idx_department (department=?)이 출력은 SQLite 가 이 쿼리에 idx_department 인덱스를 사용하고 있음을 나타냅니다.
이 시나리오에서 idx_department_lastname 인덱스는 중복됩니다. 왜냐하면 idx_department 인덱스는 department로만 필터링하는 쿼리에 사용할 수 있기 때문입니다. idx_department_lastname 인덱스는 department와 last_name 모두로 필터링하는 쿼리에만 이점을 제공합니다.
중복된 idx_department 인덱스를 삭제하려면 DROP INDEX 명령을 사용할 수 있습니다.
DROP INDEX idx_department;이제 employees 테이블의 모든 인덱스를 다시 나열합니다.
PRAGMA index_list(employees);idx_department 인덱스가 더 이상 나열되지 않는 것을 볼 수 있습니다.
이 랩에서는 인덱스를 사용하여 SQLite 데이터베이스 성능을 최적화하는 방법을 배웠습니다. 쿼리 속도를 향상시키기 위해 단일 열 인덱스를 생성하고, EXPLAIN QUERY PLAN을 사용하여 쿼리 계획을 분석했으며, 중복 인덱스를 삭제했습니다. 이러한 기술은 더 효율적이고 응답성이 뛰어난 SQLite 데이터베이스를 구축하는 데 도움이 될 것입니다.
