생각을 개발하자, 박진형

[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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+

세컨더리 인덱스 만들기

  • 만들기 전 고려해야할 것 : 알아봐야하는 부분
  • 무조건 만들면 안되는 이유
    1. 생성, 수정, 삭제 성능이 느려짐 : 해당 컬럼 인덱스 재정렬을 해야하므로, 아까와 같은 데이터를 bulk insert하면 훨씬 더 오래걸림
    2. 용량이 늘어남
  • 인덱스 생성 후 비교해보기
/* 인덱스 생성 전 */
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
  • 연결 확인

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 인증서 확인

참고