INDEX 실습

---

가상 데이터

INDEX를 이용했을때의 효율을 알아보기 위해 가상의 Student Data 10만개를 생성하는 sql 코드를 만드는 파이썬 코드를 작성하였다.

import pandas as pd
import numpy as np

# 학생 수 정의
num_students = 100000

# 기본 데이터 생성
names = ['Lee', 'Kim', 'Park', 'Choi', 'Jung', 'Kang', 'Cho', 'Yoon', 'Lim', 'Han', 
            'Hyun', 'Ji', 'Yeon', 'Hoon', 'Sun', 'Min', 'Soo', 'Seok', 'Hee', 'Kyu']
domains = ['example.com']
password = '1234'
did = 1

# 데이터프레임 생성
df_students = pd.DataFrame({
    'Sname': np.random.choice(names, size=num_students),
    'Sphonenum': ["010" + "".join(np.random.choice(list("0123456789"), size=8)) for _ in range(num_students)],
    'StudentId': ["12" + "".join(np.random.choice(list("0123456789"), size=6)) for _ in range(num_students)],
    'password': [password] * num_students,
    'Did': [did] * num_students
})

# Semail column 생성
df_students['Semail'] = [name.lower() + "@" + np.random.choice(domains) for name in np.random.choice(names, size=num_students)]

# SQL 파일을 생성하기 위한 INSERT 문 생성
with open('virtual_students.sql', 'w') as file:
    for index, row in df_students.iterrows():
        insert_query = f"INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) 
                VALUES ('{row['Sname']}', '{row['Semail']}', '{row['Sphonenum']}', '{row['StudentId']}', '{password}', {did});\n"
        file.write(insert_query)

# SQL 파일이 현재 폴더에 저장되었다고 알림
print("SQL 파일이 현재 폴더에 저장되었습니다: virtual_students.sql")

완전한 Random 가상 데이터는 아니지만 값들이 조합되면 대략적으로 겹치는 값이 많지 않도록 만들어주었다.

위의 코드로 아래와 같은 sql코드가 10만줄이 생성되었다.

INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Kim', 'yeon@example.com', '01020780555', '12651279', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Kim', 'soo@example.com', '01083323142', '12170266', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Hoon', 'min@example.com', '01070406662', '12018221', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Yoon', 'min@example.com', '01093975965', '12384582', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Yeon', 'choi@example.com', '01085998842', '12638951', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Lim', 'cho@example.com', '01072453237', '12891892', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Lim', 'lim@example.com', '01089806439', '12065347', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Lee', 'ji@example.com', '01075351168', '12540433', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Lim', 'lim@example.com', '01038995284', '12783632', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Cho', 'kyu@example.com', '01047434946', '12450188', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Choi', 'park@example.com', '01071976700', '12628398', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Lim', 'hee@example.com', '01026978866', '12474328', '1234', 1);
						.
						.
						.
						.
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Cho', 'choi@example.com', '01040051170', '12806226', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Jung', 'seok@example.com', '01068292721', '12796487', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Kyu', 'sun@example.com', '01083639794', '12782910', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Ji', 'kyu@example.com', '01087697427', '12813856', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Lim', 'park@example.com', '01029265382', '12124122', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Choi', 'kang@example.com', '01015651109', '12287544', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Kyu', 'ji@example.com', '01075521960', '12615157', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Kyu', 'hyun@example.com', '01003578972', '12227371', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Min', 'choi@example.com', '01086333579', '12431817', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Min', 'jung@example.com', '01008191447', '12508114', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Jung', 'kang@example.com', '01076551927', '12353032', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Lim', 'lee@example.com', '01054596513', '12697315', '1234', 1);
INSERT INTO student (Sname, Semail, Sphonenum, StudentId, password, Did) VALUES ('Kim', 'seok@example.com', '01079532315', '12670700', '1234', 1);

---

INDEX의 효율성 확인

INDEX를 적용하지 않았을 때 결과

 

다섯개의 INDEX 생성

 

위에서의 쿼리문 다시 실행

결과를 비교하면 Sname=’Soo’인 경우는 0.03에서 0.01로 줄었고 Sname=’Kim’인 경우는 0.04에서 0.00으로 줄었고 Sname=’Lee’인 경우는 0.03에서 0.01로 줄었다. INDEX를 사용하는 것이 분명히 효율적임을 볼 수 있었다.

 

여기서 Cardinality 값은 인덱스에서 사용하는 키의 고유성을 대략적으로 나타내는 수치이다. 높은 cardinality는 해당 키의 값이 고유하다는 것을 의미하며, 이는 인덱스를 통한 검색 성능이 좋다는 것을 의미한다. cardinality는 인덱스가 가리키는 열의 값 중에서 얼마나 많은 고유한 값이 있는지를 추정한 값이다. 예를 들어, 만약 어떤 열의 cardinality가 매우 높다면, 그 열의 각각의 값들은 테이블 내에서 거의 한 번씩만 나타난다는 것을 의미한다. 반면, cardinality가 낮다면, 많은 수의 중복된 값이 있다는 것을 나타낸다. 위의 경우에도 Did는 모두 1로 통일 해주어 cardinality가 1로 나와 INDEX로서의 가치가 없음을 확인할 수 있었다.

 

rows: 이 필드는 쿼리를 수행하기 위해 MySQL 옵티마이저가 테이블에서 접근해야 한다고 예상하는 행의 수를 나타낸다. 이 예상 값은 옵티마이저가 통계를 바탕으로 계산한 것이며, 실제 접근해야 할 행의 수와는 다를 수 있다. 여기서 'rows'는 9028로, MySQL 옵티마이저는 이 쿼리를 처리하기 위해 약 9028행을 살펴볼 것이다.

filtered: 이 필드는 쿼리의 WHERE 절에 의해 필터링된 후 남는 행의 비율을 백분율로 나타낸다. 예를 들어, 'filtered'가 100이면, WHERE 절 조건을 모든 행이 만족한다는 것을 의미하고, 만약 'filtered'가 50이라면, 예상되는 행들 중 절반만이 조건을 만족한다는 것을 의미한다. 사진에서 'filtered' 값은 100.00으로, 즉, 조건을 만족하는 예상 행이 전체 예상 행의 100%라는 것을 의미한다.

결과를 확인했을때 filtered의 값이 100이라는 것은 조사한 값들이 전부 결과로 나온다는 것으로 굉장히 효율적으로 조사하고 있다고 볼 수 있지만 제 케이스에서는 rows가 9028로 너무 큰 수이기 때문에 결국 조사하는 수 자체가 커져서 INDEX를 효율적으로 잘만들었다고 볼 수는 없겠다.

그럼에도 INDEX를 이용하였을 때 실행시간이 획기적으로 줄어든 것을 보면 INDEX라는 기능은 DB에서 필수적이고 DB control을 효율적으로 하도록 해주는 좋은 도구임을 알게 되었다.

---