mariadb join, union, subquery, group by

Join

-- 글을 안 쓴 글쓴이 출력
select a.id, a.email from author a left join post p on p.author_id=a.id where p.author_id is null;

 

 

Union

• 여러 개의 SELECT 문의 결과를 하나의 테이블이나 결과 집합으로 표현
• 각각의 SELECT 문으로 선택된 필드의 개수와 타입은 모두 일치해야함
• UNION은 DISTINCT 키워드를 따로 명시하지 않아도 중복되는 레코드를 제거

-- union : 중복제외한 두 테이블의 select을 결합
-- 컬럼의 개수와 타입이 같아야 함에 유의
-- union all : 중복 포함
select 컬럼1, 컬럼2 from table1 union select 컬럼1, 컬럼2 from table2;
-- author table의 name, email 그리고 post 테이블의 title, contents
select name, email from author union select title, contents from post;

Subquery

다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미

• select절
• from절
• where절

inner join과 대체 가능하다 서브쿼리는 select쿼리를 많이 날리기 때문에 join이 더 성능이 좋다고 알려짐

-- 서브쿼리 : select문 안에 또 다른 select문을 서브쿼리라 한다
-- select절 안에 서브쿼리
-- author email과 해당 author가 쓴 글의 개수를 출력
select email, (select count(*)from post p where p.author_id = a.id) as count from author a ;
select a.id, if(p.id is null,0,count(*)) from author a left join post p on a.id=p.author_id group by a.id;

-- from절 안에 서브쿼리
select a.name from (select * from author) as a;

-- where절 안에 서브쿼리
-- 글을 쓴 글쓴이만 출력
select a.* from author a inner join post p on a.id = p.author_id;
select * from author where id in (select author_id from post);

 

Group by

주로 집계 함수와 같이 그룹 데이터의 값을 집계하기 위해 사용 → 빈번하게 사용

GROUP BY 절에 포함되지 않은 열을 SELECT 절에서 집계 함수 없이 단독사용불가

집계함수

• COUNT() : 행의 개수를 세어줌
• AVG() : 행 안에 있는 값의 평균을 내어줌
• MIN() : 행 안에 있는 값의 최솟값을 반환해줌
• MAX() : 행 안에 있는 값의 최댓값을 반환해줌
• SUM() : 행 안에 있는 값의 합을 내어줌

-- group by와 집계함수
select author_id, count(*) from post group by author_id;

-- where와 group by
-- 연도별 post 글 출력, 연도가 null인 데이터는 제외
select date_format(created_time,'%Y') as year ,count(*) from post p where created_time is not null group by year;

-- HAVING : group by를 통해 나온 통계에 대한 조건
select count(*) from post group by author_id;
select count(*) as count from post group by author_id having count>=2;
--(실습) 포스팅 price가 2000원 이상인 글을 대상으로, 작성자별로 몇건인지와 평균 price를 구하되 평균price가 3000원 이상인 데이터를 대상으로만 통계 출력
select author_id , count(*), avg(price) as ap from post where price>=2000 group by author_id having ap>=3000;
-- (실습) 1건 이상의 글을 쓴 사람의 id와 name을 구할건데, 나이는 25세 이상인 사람만 통계에 사용하고, 가장 나이가 많은 사람 1명의 통계만 출력
select a.id, a.name from post p inner join author a group by author_id having count(*)>=2;

-- 다중열 group by
select author_id, title, count(title) from post group by author_id, title;

 

 

 

Index

• 개념

인덱스는 색인과 목차처럼 데이터 검색 속도를 향상시키는데 사용

검색 원리 → 목차페이지를 통해 전체 조회 횟수를 줄이는 원리

일반적으로 인덱스는 B-tree의 자료구조를 가지고, 이는 이진 트리를 확장한 형태로, 한 노드가 두 개 이상의 자식을 가질 수 있는 자료구조

• 사용

언제 index가 사용되냐 ? where author where id 조회조건을 걸었을 때

INDEX는 높은 카디널리티(=종류)를 갖는 컬럼에 생성됐을때 유의미한 성능향상(중복이 적은 경우)

테이블에 데이터가 추가 될 때 인덱스에도 데이터가 추가되어야 함 → 데이터 삽입, 업데이트, 삭제 작업이 느려짐

-- index 생성문
create index 인덱스명 on 테이블명(컬럼명);

-- index 조회
show index from 테이블명;

-- index 삭제문
alter table 테이블명 drop index 인덱스명

-- 테이블 생성
CREATE TABLE author (
   id bigint(20) NOT NULL AUTO_INCREMENT,
   email varchar(255) DEFAULT NULL,
   PRIMARY KEY (id)
 );

 -- 대량 데이터 생성 프로시저 생성
 -- 미리 컴파일되어 있기 때문에 CALL을 통해 호출만 하면 됨
 -- 반복문과 if문을 자유롭게 사용 가능
DELIMITER //
CREATE PROCEDURE insert_authors()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE email VARCHAR(100);
    DECLARE batch_size INT DEFAULT 10000; -- 한 번에 삽입할 행 수
    DECLARE max_iterations INT DEFAULT 100; -- 총 반복 횟수 (100000000 / batch_size)
    DECLARE iteration INT DEFAULT 1;
    WHILE iteration <= max_iterations DO
        START TRANSACTION;
        WHILE i <= iteration * batch_size DO
            SET email = CONCAT('seonguk', i, '@naver.com');
            INSERT INTO author (email) VALUES (email);
            SET i = i + 1;
        END WHILE;
        COMMIT;
        SET iteration = iteration + 1;
        DO SLEEP(0.1); -- 각 트랜잭션 후 0.1초 지연
    END WHILE;
END //
DELIMITER ;

-- 인덱스 생성
create index email_index on author(email);