인덱스 (MySQL INDEX)
이해하기 쉽고, 장황하지 않은 자료를 기반으로 강의를 진행합니다.
5. 인덱스 (MySQL INDEX)¶
- 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조
- 인덱스를 저장하는 데 필요한 디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작음
- 인덱스는 키-필드만 갖고 있고, 나머지 세부 테이블 컬럼 정보는 가지고 있지 않기 때문
- 어떤 데이터를 인덱스로 만드느냐에 따라 방대한 데이터의 경우 성능에 큰 영향을 미칠 수 있음
5.1. 인덱스 종류¶
- 클러스터형 인덱스: 영어 사전과 같은 형태로 이해하면 됨 (데이터가 순서대로 정렬됨)
- 테이블에 PRIMARY KEY 로 정의한 컬럼이 있을 경우, 자동 생성 (즉, 테이블당 하나)
- 보조 인덱스: 일반 책 뒤에 있는 <찾아보기> 와 같은 형태로 이해하면 됨
In [ ]:
db.close()
In [ ]:
import requests
from bs4 import BeautifulSoup
import pandas as pd
import pymysql
In [ ]:
host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sqlDB" # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
In [ ]:
db = pymysql.connect(
host=host_name, # DATABASE_HOST
port=3306,
user=username, # DATABASE_USERNAME
passwd=password, # DATABASE_PASSWORD
db=database_name, # DATABASE_NAME
charset='utf8'
)
userTbl 테이블 구조¶
In [ ]:
CREATE TABLE userTbl (
userID CHAR(8) NOT NULL PRIMARY KEY,
name VARCHAR(10) NOT NULL,
birthYear INT NOT NULL,
addr CHAR(2) NOT NULL,
mobile1 CHAR(3),
mobile2 CHAR(8),
height SMALLINT,
mDate DATE
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
In [ ]:
SQL = "DESC userTbl"
df = pd.read_sql(SQL, db)
df
In [ ]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
- Key_name 이 PRIMARY 로 된 것은 클러스터형 인덱스를 의미
- Column_name 이 userID 임을 확인할 수 있음
- (참고) 주요 인덱스 컬럼
- Table: The name of the table.
- Non_unique: 0 if the index cannot contain duplicates, 1 if it can.
- Key_name: The name of the index. If the index is the primary key, the name is always PRIMARY.
- Seq_in_index: The column sequence number in the index, starting with 1.
- Column_name: The column name.
- Collation: How the column is sorted in the index. This can have values A (ascending) or NULL (not sorted).
- Cardinality: An estimate of the number of unique values in the index.
- Index_type: The index method used (BTREE, FULLTEXT, HASH, RTREE).
buyTbl 테이블 구조¶
In [ ]:
CREATE TABLE buyTbl (
num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(4),
groupName CHAR(4),
price INT NOT NULL,
amount SMALLINT NOT NULL,
FOREIGN KEY (userID) REFERENCES userTbl(userID)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
In [ ]:
SQL = "DESC buyTbl"
df = pd.read_sql(SQL, db)
df
In [ ]:
SQL = "SHOW INDEX FROM buyTbl"
df = pd.read_sql(SQL, db)
df
- Key_name 이 PRIMARY 가 아닌 것은 보조 인덱스를 의미
- foreign key로 설정된 컬럼이 인덱스가 없다면, 인덱스를 자동 생성
In [ ]:
cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()
In [ ]:
SQL_QUERY = "ALTER TABLE userTbl ADD CONSTRAINT TESTDate UNIQUE(mDate);"
cursor.execute(SQL_QUERY)
db.commit()
- ALERT TABLE 테이블이름 ADD CONSTRAINT 제약조건명 UNIQUE(컬럼명)
- 테이블에 특정 컬럼에 duplicate 값이 나오지 않도록 제약조건을 추가하기
UNIQUE 제약을 넣으면, 보조 테이블이 만들어짐¶
In [ ]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
5.2. 인덱스 생성 및 삭제¶
- 인덱스를 필요에 따라 생성/삭제 가능
5.2.1. 생성된 테이블에 인덱스 추가하기¶
- 기본 문법
- CREATE INDEX 인덱스명 ON 테이블명 ( column 1, column 2, ... );
- ALTER TABLE 테이블명 ADD INDEX 인덱스명 ( column 1, column 2, ... );
- 생성된 테이블에 인덱스 추가 예제 (CREATE INDEX 사용)
In [ ]:
SQL_QUERY = "CREATE INDEX idx_name ON userTbl (name);"
cursor.execute(SQL_QUERY)
db.commit()
- 인덱스 확인
In [ ]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
- 생성된 테이블에 인덱스 추가 예제 (ALTER TABLE 사용)
In [ ]:
SQL_QUERY = "ALTER TABLE userTbl ADD INDEX idx_addr (addr)"
cursor.execute(SQL_QUERY)
db.commit()
- 인덱스 확인
In [ ]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
1) groupName 으로 인덱스 추가하고 확인해보기 (실습)¶
In [ ]:
SQL_QUERY = "CREATE INDEX idx_groupName ON buyTbl (groupName);"
cursor.execute(SQL_QUERY)
db.commit()
2) prodName 으로 인덱스 추가하고 확인해보기 (실습)¶
In [ ]:
SQL_QUERY = "ALTER TABLE buyTbl ADD INDEX idx_prodName (prodName)"
cursor.execute(SQL_QUERY)
db.commit()
In [ ]:
SQL = "SHOW INDEX FROM buyTbl"
df = pd.read_sql(SQL, db)
df
5.2.2. 테이블 생성하며 인덱스도 함께 만들기¶
- 기본 문법
- INDEX <인덱스명> ( 컬럼명1, 컬럼명2 )
- UNIQUE INDEX <인덱스명> ( 컬럼명 ) --> 항상 유일해야 함.
- UNIQUE INDEX 의 경우 컬럼명은 유일한 값을 가지고 있어야 함
In [ ]:
db.close()
In [ ]:
host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sqlDB" # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
In [ ]:
db = pymysql.connect(
host=host_name, # DATABASE_HOST
port=3306,
user=username, # DATABASE_USERNAME
passwd=password, # DATABASE_PASSWORD
db=database_name, # DATABASE_NAME
charset='utf8'
)
In [ ]:
cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()
In [ ]:
SQL_QUERY = """
DROP DATABASE IF EXISTS sqlDB;
CREATE DATABASE sqlDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
USE sqlDB;
DROP TABLE IF EXISTS userTbl;
CREATE TABLE userTbl (
userID CHAR(8) NOT NULL PRIMARY KEY,
name VARCHAR(10) UNIQUE NOT NULL,
birthYear INT NOT NULL,
addr CHAR(2) NOT NULL,
mobile1 CHAR(3),
mobile2 CHAR(8),
height SMALLINT,
mDate DATE,
UNIQUE INDEX idx_userTbl_name (name),
INDEX idx_userTbl_addr (addr)
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
DROP TABLE IF EXISTS buyTbl;
CREATE TABLE buyTbl (
num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(4),
groupName CHAR(4),
price INT NOT NULL,
amount SMALLINT NOT NULL
) DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO userTbl VALUES('LSG', '이승기', 1987, '서울', '011', '11111111', 182, '2008-8-8');
INSERT INTO buyTbl (userID, prodName, groupName, price, amount) VALUES('KBS', '운동화', '의류', 30, 2);
"""
In [ ]:
cursor.execute(SQL_QUERY)
db.commit()
In [ ]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
- UNIQUE INDEX idx_uerTbl_name (name) : name 컬럼에 대해 idx_userTbl_name 이름으로 인덱스 생성, name 은 UNIQUE 제약조건 필요
- INDEX idx_userTbl_addr (addr) : addr 컬럼에 대해 idx_userTbl_addr 이름으로 인덱스 생성
5.2.3. 인덱스 삭제¶
- 기본 문법
- ALTER TABLE 테이블명 DROP INDEX 인덱스명
- idx_userTbl_name 인덱스 삭제
In [ ]:
SQL_QUERY = "ALTER TABLE userTbl DROP INDEX idx_userTbl_name"
cursor.execute(SQL_QUERY)
db.commit()
- 인덱스 확인
In [ ]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
- idx_userTbl_addr 인덱스 삭제
In [ ]:
SQL_QUERY = "ALTER TABLE userTbl DROP INDEX idx_userTbl_addr"
cursor.execute(SQL_QUERY)
db.commit()
- 인덱스 확인
In [ ]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
3) PRIMARY KEY 놔두고 모든 인덱스 삭제하기 (실습)¶
In [ ]:
SQL_QUERY = ""
cursor.execute(SQL_QUERY)
db.commit()
In [ ]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df