잔재미코딩

데이터베이스 심화 (MySQL+AWS) - 인덱스 (MySQL INDEX)

5. 인덱스 (MySQL INDEX)

  • 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조
  • 인덱스를 저장하는 데 필요한 디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작음
    • 인덱스는 키-필드만 갖고 있고, 나머지 세부 테이블 컬럼 정보는 가지고 있지 않기 때문
  • 어떤 데이터를 인덱스로 만드느냐에 따라 방대한 데이터의 경우 성능에 큰 영향을 미칠 수 있음

5.1. 인덱스 종류

  • 클러스터형 인덱스: 영어 사전과 같은 형태로 이해하면 됨 (데이터가 순서대로 정렬됨)
    • 테이블에 PRIMARY KEY 로 정의한 컬럼이 있을 경우, 자동 생성 (즉, 테이블당 하나)
  • 보조 인덱스: 일반 책 뒤에 있는 <찾아보기> 와 같은 형태로 이해하면 됨
In [ ]:
db.close()
In [44]:
import requests
from bs4 import BeautifulSoup
import pandas as pd
import pymysql
In [45]:
host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sqlDB"    # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
In [46]:
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 [38]:
SQL = "DESC userTbl"
df = pd.read_sql(SQL, db)
df
Out[38]:
Field Type Null Key Default Extra
0 userID char(8) NO PRI None
1 name varchar(10) NO None
2 birthYear int(11) NO None
3 addr char(2) NO None
4 mobile1 char(3) YES None
5 mobile2 char(8) YES None
6 height smallint(6) YES None
7 mDate date YES None
In [39]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
Out[39]:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
0 userTbl 0 PRIMARY 1 userID A 11 None None BTREE
  • 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 [40]:
SQL = "DESC buyTbl"
df = pd.read_sql(SQL, db)
df
Out[40]:
Field Type Null Key Default Extra
0 num int(11) NO PRI None auto_increment
1 userID char(8) NO MUL None
2 prodName char(4) YES None
3 groupName char(4) YES None
4 price int(11) NO None
5 amount smallint(6) NO None
In [41]:
SQL = "SHOW INDEX FROM buyTbl"
df = pd.read_sql(SQL, db)
df
Out[41]:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
0 buyTbl 0 PRIMARY 1 num A 13 None None BTREE
1 buyTbl 1 userID 1 userID A 13 None None BTREE
  • Key_name 이 PRIMARY 가 아닌 것은 보조 인덱스를 의미
  • foreign key로 설정된 컬럼이 인덱스가 없다면, 인덱스를 자동 생성


In [42]:
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 [92]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
Out[92]:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
0 userTbl 0 PRIMARY 1 userID A 10 None None BTREE
1 userTbl 0 TMP_mDate 1 mDate A 10 None None YES BTREE

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 [97]:
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 [73]:
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 [74]:
cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()
In [75]:
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 [120]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
Out[120]:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
0 userTbl 0 PRIMARY 1 userID A 1 None None BTREE
1 userTbl 0 name 1 name A 1 None None BTREE
2 userTbl 0 idx_uerTbl_name 1 name A 1 None None BTREE
3 userTbl 1 idx_userTbl_addr 1 addr A 1 None None BTREE
  • 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 [121]:
SQL_QUERY = "ALTER TABLE userTbl DROP INDEX idx_userTbl_name"
cursor.execute(SQL_QUERY)
db.commit()


  • 인덱스 확인
In [122]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
Out[122]:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
0 userTbl 0 PRIMARY 1 userID A 0 None None BTREE
1 userTbl 0 name 1 name A 0 None None BTREE
2 userTbl 1 idx_userTbl_addr 1 addr A 0 None None BTREE
  • idx_userTbl_addr 인덱스 삭제
In [123]:
SQL_QUERY = "ALTER TABLE userTbl DROP INDEX idx_userTbl_addr"
cursor.execute(SQL_QUERY)
db.commit()
  • 인덱스 확인
In [124]:
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
Out[124]:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
0 userTbl 0 PRIMARY 1 userID A 0 None None BTREE
1 userTbl 0 name 1 name A 0 None None BTREE

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