mysql에서는 explain이라는 키워드를 활용하여 질의문에 대한 정보를 얻을수 있다.
우선 테스트를 할 테이블부터 생성
1
2
3
4
5
|
CREATE TABLE Users(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL
);
| cs |
1
2
3
4
5
6
7
8
9
|
CREATE TABLE UserInfos(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT NOT NULL DEFAULT 0,
gender INT NOT NULL DEFAULT 0, #여:1, 남:2
UserId INT NOT NULL,
FOREIGN KEY(UserId) REFERENCES Users(id) On DELETE CASCADE
);
| cs |
2개의 테이블을 생성을 하였다.
UserInfos는 userId를 통해 users의 id를 참조를 하고있다.
(2개의 테이블간 관계 성립)
1
|
SELECT *FROM UserInfos INNER JOIN Users ON Users.id = UserInfos.UserId;
| cs |
INNER JOIN을 통해 두개의 테이블을 엮어서 조회를 할 수 있다.(JOIN문을 쓸때에는 INNER JOIN을 쓰는것이 가장 효과적이다.)
SELECT앞에 EXPLAIN을 붙여주면 해당 쿼리가 실행됬을 때의 정보를 보여준다.
여기서 가장 유심히 지켜볼 부분은 EXTRA부분이다.
이제 조금바꿔서 조회를해보자.
위 테이블에서 gender컬럼을 통해 성별을 기준으로 몇명이 있는지 조회를 해보자.
가장 기본적인 방법은 GROUP BY를 사용을 하게되는 것이다.
1
2
3
4
5
6
7
8
|
SELECT
*, COUNT(*)
FROM
Users
INNER JOIN
UserInfos ON Users.id = UserInfos.UserId
GROUP BY
UserInfos.gender;
| cs |
GROUP BY를 통해 해당 컬럼이 같은것끼리 그룹을 지었다 또한 그것의 갯수를 출력을 해주었다.
자 그럼 해당 쿼리문의 정보를 봐보자.
extra 부분에 using temporary, using filesort가 추가가 되었다.
extra에서 가장 피해야할 것이 바로 저 두가지이다.
using temporary : 가상 테이블 생성
using filesort : 정렬
우선 group by는 기본적으로 정렬을 해서 조회를 한다.
order by null을 주면 정렬을 하지않고 처리를 한다.
ORDER BY를 통해 Using filesort를 없앴다.
이제 temporary를 없애보자.
요건 꽤나 삽질을 했던 부분이다.
여기서 인덱스라는 개념이 등장을 한다.
우선 인덱스에 대한 개념은 나중에 설명을 하도록 하겠다. group by에 사용되고 있는 gende라는 컬럼을 index로 추가를 하자.
1
|
ALTER TABLE UserInfos ADD INDEX gender(gender);
| cs |
1
|
SHOW INDEX FROM test.UserInfos;
| cs |
SHOW 명령어를 통해 해당 테이블에서 어떤 컬럼이 인덱스로 잡혀있는지 확인이 가능하다.
1
2
3
4
5
6
7
8
9
10
11
|
SELECT *, COUNT(*)
FROM
UserInfos
WHERE
EXISTS (
SELECT
id
FROM
Users
)
GROUP BY UserInfos.gender;
| cs |
위 처럼 쿼리를 좀 많이 바꾸었다. 서브쿼리를 활용을 하였다.
temporary를 없앴다 대신에
두번째 id가 2로 바뀌면서 select_type이 서브쿼리로 바뀌었다. 또한 extra가 using index로 바뀌었다.
inner를 활용했을 때에는 gender에 대한 결과를 임시로 저장을 할 테이블이 필요로 했다. 하지만 두번째 방법은 쿼리를 두번으로 날림으로써 임시 테이블 생성을 없앴것이다.
WHERE문을 보면 select id FROM Users 쿼리를 날리고 있다.
UserInfos의 데이터의 양과 상관없이 Users의 row의 갯수만큼만 결과가 나타날것이다.(위 방법은 1:1 모델에서만 적용해야 알맞을것이다.)
Users의 row 한줄을 delete를 이용해 없앴다.
이제 다시 위 쿼리를 통해 결과를 봐보자 위에서는 count의 합이 5가 나왔다.
이번엔 count의 총합이4가 나왔다.
아 이건 전에 어떤분이 물어봤던건데 GROUP BY를 하면서 해당 그룹에 포함된 데이터를 나열하는 방법에 대해 물어본적이 있었다.
이건 mysql에서 제공하는 GROUP_CONCAT를 이용을 하면 된다.
현재는 성별이 몇명인지 출력을 하는 쿼리문을 작성을 하고 있었다.
해당 성별이 누가 있는지 조회 하는 쿼리를 봐보자.
1
2
3
4
5
6
7
8
9
10
|
SELECT
*,
COUNT(*) AS count,
GROUP_CONCAT(UserInfos.name) AS names
FROM
UserInfos
WHERE
EXISTS
(SELECT id FROM Users)
GROUP BY UserInfos.gender;
| cs |
GROUP_CONCAT이 기본적인 구분 인자는 ,이다
1
|
GROUP_CONCAT(UserInfos.name SEPARATOR '-')
| cs |
SEPARATOR를 통해 구분자를 바꾸어 줄 수 있다.
흠...
사실 위 방법이 정말 최적화인지는 잘 모르겠다.
사실 서브쿼리를 날리는 것도 결국은 디비 성능에 부하를 일으키는 부분이긴 하니깐 가상 테이블 생성이냐 쿼리를 더 날리냐의 차이라... 아직은 정확이 어느것이 정답인지는 잘 모르겠다.
하지만 같은 출력결과를 다양한 방법으로 출력을 할 수 있다는 것을 명심하자.
(아 참고로 윈도에서 워크벤치라는 툴을 주로 쓰고 결과 창을 커멘드창이랑 툴을 섞으면서 써서 아마 검은 화면으로 되있는것도 있고 툴처럼 된게 있을것이다 ㅋㅋㅋㅋㅋ)
댓글
댓글 쓰기