절개와 지조 6차 모각코

Explain SQL

SELECT/UPDATE/INSERT/DELETE 등의 쿼리가 어떻게 수행되는지

내부를 보여주는 SQL 명령

• MySQL이 해당 쿼리를 어떻게 실행할지 Execution Plan을 보여줌. 이를 바탕으로 느리게 동작하는 쿼리의 최적화가 가능해짐
• 보통 느린 쿼리의 경우 문제가 되는 테이블에 인덱스를 붙이는 것이 일반적

--EXPLAIN
EXPLAIN SELECT
	LEFT(s.created, 7) AS mon,
	c.channel,
	COUNT(DISTINCT user_id) AS mau
FROM session s
JOIN channel c ON c.id = s.channel_id
GROUP BY 1,2
ORDER BY 1 DESC, 2;

Index는 테이블에서 특정 찾기 작업을 빠르게 수행하기 위해서 MySQL이 별도로 만드는 데이터 구조를 말함

• 컬럼별로 만들어짐
• Primary Key나 Foreign Key로 지정된 컬럼은 기본적으로 Index를 갖게 됨
• 특정 컬럼을 바탕으로 검색을 자주 한다면 Index 생성이 큰 도움이 될 수 있음

INDEX와 KEY는 동의어

Index는 SELECT/DELETE/JOIN 명령을 빠르게 하지만 대신

INSERT/UPDATE 명령은 느리게 하는 단점이 존재

• 테이블에 너무 많은 인덱스를 추가하면 인덱스의 로딩으로 인한 오버헤드로 인해 시스템이 전체적으로 느려질 수 있음

Index는 테이블 생성 후 나중에 ALTER TABLE 혹은 CREATE INDEX 함수로 생성하는 것도 가능

ALTER TABLE testalter_tbl ADD INDEX (column1);

ALTER TABLE testalter_tbl ADD UNIQUE (column1);

ALTER TABLE testalter_tbl ADD FULLTEXT (column1);

ALTER TABLE testalter_tbl DROP INDEX (column1);

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, …);

인덱스가 있는 경우와 없는 경우의 SELECT 필터링의 성능 비교

대상 테이블들은 prod.session과 prod.session_with_index

• 인덱스는 user_id에 적용 (id와 channel_id에는 이미 걸려있음)

CREATE TABLE prod.session_with_index(
		id int NOT NULL auto_increment,
		user_id int not NULL,
		created timestamp not NULL default CURRENT_TIMESTAMP,
		channel_id int not NULL,
		PRIMARY KEY(id),
		FOREIGN KEY(channel_id) references channel(id),
		INDEX user_id(user_id)
);

다음 2개의 GROUP BY 작업의 성능 비교

SELECT user_id, COUNT(1)
FROM prod.session
GROUP BY 1;

SELECT user_id, COUNT(1)
FROM prod.session_with_index
GROUP BY 1;

동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성해주세요. 이때 고양이를 개보다 먼저 조회해주세요.

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS count FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY CASE WHEN ANIMAL_TYPE = 'Cat' THEN 0 ELSE 1 END, ANIMAL_TYPE;

동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.

SELECT NAME, COUNT(NAME) FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME)>=2
ORDER BY NAME;

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

SELECT HOUR(DATETIME), COUNT(DATETIME)
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME)>=9 AND HOUR(DATETIME)<20
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)

SELECT HOUR(DATETIME), COUNT(DATETIME)
FROM ANIMAL_OUTS
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME)
HAVING HOUR(DATETIME)>=9 AND HOUR(DATETIME)<20

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.(레코드에 없는 시간을 채워야 하는 문제)

SET @hour := -1;
SELECT 
(@hour := @hour +1 ) AS HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) as COUNT
FROM ANIMAL_OUTS
WHERE @hour < 23

HOUR COUNT

0	0
1	0
2	0
3	0
4	0
5	0
6	0
7	3
8	1

입양 게시판에 동물 정보를 게시하려 합니다. 동물의 생물 종, 이름, 성별 및 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 프로그래밍을 모르는 사람들은 NULL이라는 기호를 모르기 때문에, 이름이 없는 동물의 이름은 "No name"으로 표시해 주세요.

SELECT 
ANIMAL_TYPE, 
CASE
    WHEN NAME IS NULL THEN 'No name'
    ELSE NAME
END NAME, 
SEX_UPON_INTAKE 
FROM ANIMAL_INS;