Join

Inner Outer Join

Join : 두 테이블을 합쳐서 하나로 보여준다.

Union : 합집합이다. 갯수가 같아야 한다. 

mysql> select name from stu union select name from prof;
+-----------+
| name      |
+-----------+
| pobi      |
| honux     |
| happy     |
| 구승모    |
| 박재성    |
+-----------+

크로스 조인 : 두 테이블의 카테시안 곱

// 전체
mysql> select * from stu cross join prof;
+----+-------+-----+-----+-----------+
| id | name  | pid | pid | name      |
+----+-------+-----+-----+-----------+
|  1 | pobi  |   1 |   1 | 구승모    |
|  1 | pobi  |   1 |   2 | 박재성    |
|  2 | honux |   2 |   1 | 구승모    |
|  2 | honux |   2 |   2 | 박재성    |
|  3 | happy |   1 |   1 | 구승모    |
|  3 | happy |   1 |   2 | 박재성    |
+----+-------+-----+-----+-----------+

// 지도교수만(의미있는데이터)
mysql> select * from stu s join prof p where s.pid = p.pid;
+----+-------+-----+-----+-----------+
| id | name  | pid | pid | name      |
+----+-------+-----+-----+-----------+
|  1 | pobi  |   1 |   1 | 구승모    |
|  2 | honux |   2 |   2 | 박재성    |
|  3 | happy |   1 |   1 | 구승모    |
+----+-------+-----+-----+-----------+

// 멋지게 동등조인 = 을 조인 조건에 사용
mysql> select * from stu s join prof p on s.pid = p.pid where p.name='구승모';
+----+-------+-----+-----+-----------+
| id | name  | pid | pid | name      |
+----+-------+-----+-----+-----------+
|  1 | pobi  |   1 |   1 | 구승모    |
|  3 | happy |   1 |   1 | 구승모    |
+----+-------+-----+-----+-----------+

// 세타조인, 조인조건이 =이 아닌 나머지들
  • where s.pid = p.pid는 조인조건이므로 좋은쿼리가 아니다.
  • join조건은 on뒤에 적는게 일반적이다.

join은 닫힌연산이기때문에 결과물에 계속 join을 할 수 있다. 자기자신끼리도 join이 가능하다.

Q. 1번 학생이 듣고 있는 수업과 담당 교수 이름을 표시해보세요.

mysql> select s.name, c.name, p.name from stu s join sugang g on s.id = g.uid join class c on g.cid = c.cid join prof p on c.pid = p.pid where id = 1;
+------+--------------------+-----------+
| name | name               | name      |
+------+--------------------+-----------+
| pobi | 게임제작개론       | 구승모    |
| pobi | 에자일방법론       | 박재성    |
+------+--------------------+-----------+

//이름구분
mysql> select s.name as 학생, c.name as 강의명, p.name as 지도교수 from stu s join sugang g on s.id = g.uid join class
+--------+--------------------+--------------+
| 학생   | 강의명             | 지도교수     |
+--------+--------------------+--------------+
| pobi   | 게임제작개론       | 구승모       |
| pobi   | 에자일방법론       | 박재성       |
+--------+--------------------+--------------+

Outer join

left join(outer join) : 왼쪽의 조건이 안맞더라도 보여줘라

mysql> select * from stu left join prof on stu.pid = prof.pid;
+----+-------+-----+------+-----------+
| id | name  | pid | pid  | name      |
+----+-------+-----+------+-----------+
|  1 | pobi  |   1 |    1 | 구승모    |
|  3 | happy |   1 |    1 | 구승모    |
|  2 | honux |   2 |    2 | 박재성    |
+----+-------+-----+------+-----------+

right join(outer join): 오른쪽 조건이 안맞더라도 보여줘라

mysql> select * from stu right join prof on stu.pid = prof.pid;
+------+-------+------+-----+-----------+
| id   | name  | pid  | pid | name      |
+------+-------+------+-----+-----------+
|    1 | pobi  |    1 |   1 | 구승모    |
|    2 | honux |    2 |   2 | 박재성    |
|    3 | happy |    1 |   1 | 구승모    |
+------+-------+------+-----+-----------+

DB Index

인덱스 : 테이블에 대한 동작의 속도를 높여주는 자료구조 무조건 빠르게 해주는것이 아니라 하나의 성능을 개선하면 다른한쪽이 저하가 된다(Trade Off). Insert, Update, Delete속도를 희생해서 Read속도를 매우 높인다.

기본세팅

  • load data를 통해 200만건의 레코드를 삽입한다.
SET foreign_key_checks=0;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` char(16) NOT NULL,
  `name` varchar(16) DEFAULT NULL,
  `last_visit` datetime DEFAULT NULL,
  `money` decimal(10,0) DEFAULT NULL,
  `choo` char(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `choo` (`choo`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`choo`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA LOCAL INFILE 'data.csv'
	INTO TABLE user
	FIELDS TERMINATED BY ','
	IGNORE 1 LINES;
  • set foreign_key_checks=0 데이터를 삽입할때 외래키를 고려하지 않는다.
  • KEY choo (choo), CONSTRAINT user_ibfk_1 FOREIGN KEY (choo) REFERENCES user (id)를 할때 외래키 검사를 안하게 된다.

mysql -u jimmy -p jimmydb –local-infile=1 < index_ex.sql 명령어를 통해 index_ex.sql의 명령어를 실행하고 data.csv를 집어넣는다.

mysql> select @@foreign_key_checks;
+----------------------+
| @@foreign_key_checks |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql> set @@foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@foreign_key_checks;
+----------------------+
| @@foreign_key_checks |
+----------------------+
|                    0 |
+----------------------+
  • foreign_key_checks를 1에서 0으로 바꾼다.

실습

mysql> select count(id) from user;
+-----------+
| count(id) |
+-----------+
|   2000000 |
+-----------+

mysql> select count(*) from user where id like 'm%';
+----------+
| count(*) |
+----------+
|    83262 |
+----------+
1 row in set (0.03 sec)

mysql> select count(*) from user where money=1200;
+----------+
| count(*) |
+----------+
|      189 |
+----------+
1 row in set (0.53 sec)
  • 0.03초만에 id가 m으로 시작되는것이 83262개이다. 굉장히 빨리 실행이됬다.
  • 밑에꺼랑 위에꺼랑 시간이 굉장히 차이가 많이 난다. 왜??

쿼리마다 성능이 다른이유?

  1. 특정 칼럼에 대한 인덱스가 자동으로 만들어진다.
  2. primary key 인덱스 자동 생성
  3. foreign key에도 인덱스가 자동 생성된다.
mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     1820892 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | choo     |            1 | choo        | A         |     1781468 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


// 예쁘게
mysql> show index from user\G;
*************************** 1. row ***************************
        Table: user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 1867779
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: user
   Non_unique: 1
     Key_name: choo
 Seq_in_index: 1
  Column_name: choo
    Collation: A
  Cardinality: 1977604
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)
2 rows in set (0.00 sec)
  • id, choo column에 BTREE타입의 인덱스가 존재를 한다.
  • index는 show index명령어로 확인이 가능하다.
mysql> select count(*) from user where id like 'home%';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from user where choo like 'home%';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)
  • id와 choo에는 index가 걸려있기때문에 검색하는데 굉장히 빨리걸린다.
mysql> select count(*) from user where money = 6600;
+----------+
| count(*) |
+----------+
|      195 |
+----------+
1 row in set (0.55 sec)

mysql> select count(*) from user where money = 1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.47 sec)
  • money에는 index가 없기때문에 인덱스가 있는 Column에 비해서 오래걸린다.

    어떻게 속도를 빨리할까?? 인덱스를 만든다.

mysql> create index idx_user1 on user(money);
  • idx_user1이라는 이름을 가진 인덱스를 user(money)에 만든다.
mysql> select count(*) from user where money = 1000;
+----------+
| count(*) |
+----------+
|      194 |
+----------+
1 row in set (0.00 sec)

mysql> select * from user where money=1000;
194 rows in set (0.01 sec)
  • index가 만들어지면 이전과 비해서 굉장히 검색이 빨라진다.

create index indexName on TableName(ColumnName);을 이용하면 인덱스를 만들수 있다.

  • 모든열에 인덱스를 만들면 되지 않은가?? 안된다 왜?? 인덱스를 만들때마다. insert update delete가 느려지고 용량이 굉장히 커진다.
Written on August 24, 2018