잔재미코딩

인덱스 (MySQL INDEX)

파이썬을 활용하여 자동화 프로그램 만들기

혹시 자료가 도움이 되셨다면, 제가 수년간의 강의경험으로 기존과 다르게 특별히 만든 파이썬 강의를 익혀보세요!

업무 시간 단축! 파이썬을 활용하여 자동화 프로그램 만들기

1:1 코칭으로 단순 반복 작업을 없애줄 각자만의 자동화 프로그램까지 가져가기

자세히 알아보기

5. 인덱스 (MySQL INDEX)

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

5.1. 인덱스 종류

  • 클러스터형 인덱스: 영어 사전과 같은 형태로 이해하면 됨 (데이터가 순서대로 정렬됨)
    • 테이블에 PRIMARY KEY 로 정의한 컬럼이 있을 경우, 자동 생성 (즉, 테이블당 하나)
  • 보조 인덱스: 일반 책 뒤에 있는 <찾아보기> 와 같은 형태로 이해하면 됨
db.close()

import requests
from bs4 import BeautifulSoup
import pandas as pd
import pymysql

host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sqlDB"    # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
파이썬을 활용하여 자동화 프로그램 만들기

혹시 자료가 도움이 되셨다면, 제가 수년간의 강의경험으로 기존과 다르게 특별히 만든 파이썬 강의를 익혀보세요!

업무 시간 단축! 파이썬을 활용하여 자동화 프로그램 만들기

1:1 코칭으로 단순 반복 작업을 없애줄 각자만의 자동화 프로그램까지 가져가기

자세히 알아보기 
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 테이블 구조

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;

SQL = "DESC userTbl"
df = pd.read_sql(SQL, db)
df

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 테이블 구조

파이썬을 활용하여 자동화 프로그램 만들기

혹시 자료가 도움이 되셨다면, 제가 수년간의 강의경험으로 기존과 다르게 특별히 만든 파이썬 강의를 익혀보세요!

업무 시간 단축! 파이썬을 활용하여 자동화 프로그램 만들기

1:1 코칭으로 단순 반복 작업을 없애줄 각자만의 자동화 프로그램까지 가져가기

자세히 알아보기 
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;

SQL = "DESC buyTbl"
df = pd.read_sql(SQL, db)
df

SQL = "SHOW INDEX FROM buyTbl"
df = pd.read_sql(SQL, db)
df
  • Key_name 이 PRIMARY 가 아닌 것은 보조 인덱스를 의미
  • foreign key로 설정된 컬럼이 인덱스가 없다면, 인덱스를 자동 생성
cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()

SQL_QUERY = "ALTER TABLE userTbl ADD CONSTRAINT TESTDate UNIQUE(mDate);"
cursor.execute(SQL_QUERY)
db.commit()
  • ALERT TABLE 테이블이름 ADD CONSTRAINT 제약조건명 UNIQUE(컬럼명)
    • 테이블에 특정 컬럼에 duplicate 값이 나오지 않도록 제약조건을 추가하기
파이썬을 활용하여 자동화 프로그램 만들기

혹시 자료가 도움이 되셨다면, 제가 수년간의 강의경험으로 기존과 다르게 특별히 만든 파이썬 강의를 익혀보세요!

업무 시간 단축! 파이썬을 활용하여 자동화 프로그램 만들기

1:1 코칭으로 단순 반복 작업을 없애줄 각자만의 자동화 프로그램까지 가져가기

자세히 알아보기

UNIQUE 제약을 넣으면, 보조 테이블이 만들어짐

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 사용)
SQL_QUERY = "CREATE INDEX idx_name ON userTbl (name);"
cursor.execute(SQL_QUERY)
db.commit()
  • 인덱스 확인
파이썬을 활용하여 자동화 프로그램 만들기

혹시 자료가 도움이 되셨다면, 제가 수년간의 강의경험으로 기존과 다르게 특별히 만든 파이썬 강의를 익혀보세요!

업무 시간 단축! 파이썬을 활용하여 자동화 프로그램 만들기

1:1 코칭으로 단순 반복 작업을 없애줄 각자만의 자동화 프로그램까지 가져가기

자세히 알아보기 
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
  • 생성된 테이블에 인덱스 추가 예제 (ALTER TABLE 사용)
SQL_QUERY = "ALTER TABLE userTbl ADD INDEX idx_addr (addr)"
cursor.execute(SQL_QUERY)
db.commit()
  • 인덱스 확인
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df

1) groupName 으로 인덱스 추가하고 확인해보기 (실습)

SQL_QUERY = "CREATE INDEX idx_groupName ON buyTbl (groupName);"
cursor.execute(SQL_QUERY)
db.commit()
파이썬을 활용하여 자동화 프로그램 만들기

혹시 자료가 도움이 되셨다면, 제가 수년간의 강의경험으로 기존과 다르게 특별히 만든 파이썬 강의를 익혀보세요!

업무 시간 단축! 파이썬을 활용하여 자동화 프로그램 만들기

1:1 코칭으로 단순 반복 작업을 없애줄 각자만의 자동화 프로그램까지 가져가기

자세히 알아보기

2) prodName 으로 인덱스 추가하고 확인해보기 (실습)

SQL_QUERY = "ALTER TABLE buyTbl ADD INDEX idx_prodName (prodName)"
cursor.execute(SQL_QUERY)
db.commit()

SQL = "SHOW INDEX FROM buyTbl"
df = pd.read_sql(SQL, db)
df

5.2.2. 테이블 생성하며 인덱스도 함께 만들기

  • 기본 문법
    • INDEX <인덱스명> ( 컬럼명1, 컬럼명2 )
    • UNIQUE INDEX <인덱스명> ( 컬럼명 ) --> 항상 유일해야 함.
      • UNIQUE INDEX 의 경우 컬럼명은 유일한 값을 가지고 있어야 함
db.close()

host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = "davelee"
password = "korea234"
database_name = "sqlDB"    # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.

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'
)
파이썬을 활용하여 자동화 프로그램 만들기

혹시 자료가 도움이 되셨다면, 제가 수년간의 강의경험으로 기존과 다르게 특별히 만든 파이썬 강의를 익혀보세요!

업무 시간 단축! 파이썬을 활용하여 자동화 프로그램 만들기

1:1 코칭으로 단순 반복 작업을 없애줄 각자만의 자동화 프로그램까지 가져가기

자세히 알아보기 
cursor = db.cursor()
cursor.execute("set names utf8")
db.commit()

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);
"""

cursor.execute(SQL_QUERY)
db.commit()

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 인덱스 삭제
파이썬을 활용하여 자동화 프로그램 만들기

혹시 자료가 도움이 되셨다면, 제가 수년간의 강의경험으로 기존과 다르게 특별히 만든 파이썬 강의를 익혀보세요!

업무 시간 단축! 파이썬을 활용하여 자동화 프로그램 만들기

1:1 코칭으로 단순 반복 작업을 없애줄 각자만의 자동화 프로그램까지 가져가기

자세히 알아보기 
SQL_QUERY = "ALTER TABLE userTbl DROP INDEX idx_userTbl_name"
cursor.execute(SQL_QUERY)
db.commit()
  • 인덱스 확인
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
  • idx_userTbl_addr 인덱스 삭제
SQL_QUERY = "ALTER TABLE userTbl DROP INDEX idx_userTbl_addr"
cursor.execute(SQL_QUERY)
db.commit()
  • 인덱스 확인
SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df
파이썬을 활용하여 자동화 프로그램 만들기

혹시 자료가 도움이 되셨다면, 제가 수년간의 강의경험으로 기존과 다르게 특별히 만든 파이썬 강의를 익혀보세요!

업무 시간 단축! 파이썬을 활용하여 자동화 프로그램 만들기

1:1 코칭으로 단순 반복 작업을 없애줄 각자만의 자동화 프로그램까지 가져가기

자세히 알아보기

3) PRIMARY KEY 놔두고 모든 인덱스 삭제하기 (실습)

SQL_QUERY = ""
cursor.execute(SQL_QUERY)
db.commit()

SQL = "SHOW INDEX FROM userTbl"
df = pd.read_sql(SQL, db)
df

외래키 (FOREIGN KEY) Sakila 데이터베이스로 MySQL 실습하기