[TIL - 0903] 인덱스 성능 비교해보기 등, Lets Encrypt HTTPS 통신
INDEX
- 테이블 읽기 속도를 높여주는 자료구조
속도 비교해보기
- 200만건 데이터 bulk insert 하기
- bulk insert 할 때 foreign key가 있다면 foreign key 체크가 기본적으로 설정되어있어(참조 무결성) insert 하는 데에 많은 시간이 듦, 감사합니다 호눅스
- bulk insert 한글이 깨지는 현상이 있다면 : 아래 bulk insert 토픽 참고
/* 데이터베이스 캐릭터셋 설정 */
> show variables like 'c%';
/* 테이블 생성 */
> SET foreign_key_checks=0;
DROP TABLE IF EXISTS user;
CREATE TABLE user (
id int not null primary key,
name varchar(16) default null,
last_visit datetime default null,
level int default null,
choo int default null,
CONSTRAINT user_fk FOREIGN KEY (choo) REFERENCES user (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/* 데이터 삽입 */
> LOAD DATA LOCAL INFILE '/Users/imjinbro/Downloads/mock.csv' INTO TABLE user FIELDS TERMINATED BY ',';
Query OK, 1 row affected, 65535 warnings (2 min 15.49 sec)
- 속도 비교 : PK(인덱스)와 일반 컬럼의 검색 속도 차이
- 위는 실행 속도 차이, 아래는 쿼리플랜을 통해서 어떻게 쿼리 옵티마이저(SQL은 선언형 언어 - 어떻게 처리할 지 정해주지않음, 어떻게 처리할지 정하는 역할)가 쿼리를 실행했는지 보여줌(key 항목을 보면 됨)
- 쿼리플랜 key : 인덱스 컬럼 이름 - PRIMARY(PK INDEX)와 NULL(인덱스가 아닌 테이블 풀스캔 - type ALL)
- PK, FK 모두 인덱스 자동 생성 : PK(Primary Index), FK(Secondary Index - 인덱스 최종 노드에는 PK가 저장되어있어서 PK를 찾아 PK 인덱스에서 진짜 데이터 위치를 찾음)
mysql> select * from user where id = 656329;
+--------+-----------+---------------------+--------+
| id | name | last_visit | choo |
+--------+-----------+---------------------+--------+
| 656329 | 밤람범 | 2018-06-23 09:29:09 | 656329 |
+--------+-----------+---------------------+--------+
1 row in set (0.00 sec)
mysql> select * from user where name = '밤람범';
+--------+-----------+---------------------+--------+
| id | name | last_visit | choo |
+--------+-----------+---------------------+--------+
| 656329 | 밤람범 | 2018-06-23 09:29:09 | 656329 |
+--------+-----------+---------------------+--------+
1 row in set (0.41 sec)
mysql> explain select * from user where id = 656329;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
mysql> explain select * from user where name = '밤람범';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1994916 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
세컨더리 인덱스 만들기
- 만들기 전 고려해야할 것 : 알아봐야하는 부분
- 무조건 만들면 안되는 이유
- 생성, 수정, 삭제 성능이 느려짐 : 해당 컬럼 인덱스 재정렬을 해야하므로, 아까와 같은 데이터를 bulk insert하면 훨씬 더 오래걸림
- 용량이 늘어남
- 인덱스 생성 후 비교해보기
/* 인덱스 생성 전 */
select count(*) from user where level = 500;
+----------+
| count(*) |
+----------+
| 1958 |
+----------+
1 row in set (0.34 sec)
/* 인덱스 생성 후 */
mysql> select count(*) from user where level = 500;
+----------+
| count(*) |
+----------+
| 1958 |
+----------+
1 row in set (0.00 sec)
bulk insert - 겪은 문제와 해결
- 삽입을 했는데, 이름 컬럼의 데이터가 깨져서 나옴 - 분명 테이블 utf-8로 설정하고 생성했는데 데이터베이스 설정때문인가해서 my.conf 임의로 생성해서 적용하려다가 mysql 재시작 과정에서 에러가 발생함 -> /etc/my.conf(최우선 적용) 저장
- mysql 데이터베이스 별로 charset 적용되는 것 이제 알았음(직접 경험해보지 않았으면 몰랐을..) : 아래 명령어로 변경함(삽질이었군..), 테이블 별로 설정되는 것만 봐도 데이터베이스 별로 가능하다는 것을 유추할 수 있었음;;, mysql 하나에 여러 데이터베이스로 여러 테이블을 관리할 수 있으니깐 일괄적인 설정만 되는 것이 아니라 별도 설정되는 것이 당연하지
/* 변경 */
> ALTER DATABASE $데이터베이스이름 CHARACTER SET utf8 COLLATE utf8_general_ci;
/* 생성 시에 지정하기 */
> CREATE DATABASE $데이터베이스이름 DEFAULT CHARACTER SET UTF8;
/* 한글 깨짐 현상 수정 확인 */
select * from user where id = 2000000;
+---------+-----------+---------------------+---------+
| id | name | last_visit | choo |
+---------+-----------+---------------------+---------+
| 2000000 | 밤솜참 | 2018-03-21 07:48:25 | 2000000 |
+---------+-----------+---------------------+---------+
쿼리
- 외래키 컬럼 지정하기
참고
HTTPS
HTTPS 구성 환경 갖추기
- AWS EC2 인스턴스 + 고정 IP 할당 + 호스팅(freenom free dns 서비스 사용)
- ubuntu 연결 DNS 서버 변경하기 : /etc/resolv.conf
- 연결 확인
- certbot을 활용해서 Let’s Encrypt 인증서 받고 설정까지
- 가이드를 그대로 따라가면 쉽게 할 수 있어요
- certbot이 nginx의 conf를 수정함 : /etc/nginx/sites-enabled/default 파일 수정
server {
.
.
.
listen [::]:443 ssl ipv6only=on; # managed by Certbot
listen 443 ssl; # managed by Certbot
ssl_certificate /etc/letsencrypt/live/https-test.cf/fullchain.pem; # managed by Certbot
ssl_certificate_key /etc/letsencrypt/live/https-test.cf/privkey.pem; # managed by Certbot
include /etc/letsencrypt/options-ssl-nginx.conf; # managed by Certbot
ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem; # managed by Certbot
}
- SSL 인증서 확인