M1 Mac์์ workbench๋ก EC2์ ๋์์ ธ์๋ docker mysql์ ์ฐ๊ฒฐํ์ฌ ์งํํ์์ต๋๋ค.
-- tuning ๋ฐ์ดํฐ๋ฒ ์ด์ค ์ฌ์ฉ
USE tuning;
ํ๋์ค์ธ(Active) ๋ถ์์ ํ์ฌ ๋ถ์๊ด๋ฆฌ์ ์ค ์ฐ๋ด ์์ 5์์์ ๋๋ ์ฌ๋๋ค์ด ์ต๊ทผ์ ๊ฐ ์ง์ญ๋ณ๋ก ์ธ์ ํด์คํ๋์ง ์กฐํํด๋ณด์ธ์. (์ฌ์๋ฒํธ, ์ด๋ฆ, ์ฐ๋ด, ์ง๊ธ๋ช , ์ง์ญ, ์ ์ถ์ ๊ตฌ๋ถ, ์ ์ถ์ ์๊ฐ)
SELECT
`ํ๋์ค์ธ ๋ถ์์ ํ์ฌ ๋ถ์๊ด๋ฆฌ์ ์ ๋ณด`.์ฌ์๋ฒํธ, `ํ๋์ค์ธ ๋ถ์์ ํ์ฌ ๋ถ์๊ด๋ฆฌ์ ์ ๋ณด`.์ด๋ฆ, `ํ๋์ค์ธ ๋ถ์์ ํ์ฌ ๋ถ์๊ด๋ฆฌ์ ์ ๋ณด`.์ฐ๋ด, `ํ๋์ค์ธ ๋ถ์์ ํ์ฌ ๋ถ์๊ด๋ฆฌ์ ์ ๋ณด`.์ง๊ธ๋ช
, `์
์ถ์
์ ๋ณด`.์
์ถ์
์๊ฐ, `์
์ถ์
์ ๋ณด`.์ง์ญ, `์
์ถ์
์ ๋ณด`.์
์ถ์
๊ตฌ๋ถ
FROM(
SELECT
`ํ๋์ค์ธ ๋ถ์์ ํ์ฌ ๋ถ์๊ด๋ฆฌ์ ์ฌ์๋ฒํธ`.์ฌ์๋ฒํธ, ์ฌ์.์ด๋ฆ, ๊ธ์ฌ.์ฐ๋ด, ์ง๊ธ.์ง๊ธ๋ช
FROM(
SELECT
์ฌ์๋ฒํธ
FROM (SELECT ๋ถ์๋ฒํธ FROM ๋ถ์ WHERE ๋น๊ณ = 'active') AS ๋ถ์
JOIN (SELECT ์ฌ์๋ฒํธ, ๋ถ์๋ฒํธ FROM ๋ถ์๊ด๋ฆฌ์ WHERE ์ข
๋ฃ์ผ์ = '9999-01-01') AS ๋ถ์๊ด๋ฆฌ์
ON ๋ถ์.๋ถ์๋ฒํธ = ๋ถ์๊ด๋ฆฌ์.๋ถ์๋ฒํธ
) AS `ํ๋์ค์ธ ๋ถ์์ ํ์ฌ ๋ถ์๊ด๋ฆฌ์ ์ฌ์๋ฒํธ`
JOIN (SELECT ์ฌ์๋ฒํธ, ์ด๋ฆ FROM ์ฌ์) AS ์ฌ์
ON `ํ๋์ค์ธ ๋ถ์์ ํ์ฌ ๋ถ์๊ด๋ฆฌ์ ์ฌ์๋ฒํธ`.์ฌ์๋ฒํธ = ์ฌ์.์ฌ์๋ฒํธ
JOIN (SELECT ์ฌ์๋ฒํธ, ์ง๊ธ๋ช
FROM ์ง๊ธ WHERE ์ข
๋ฃ์ผ์ = '9999-01-01') AS ์ง๊ธ
ON `ํ๋์ค์ธ ๋ถ์์ ํ์ฌ ๋ถ์๊ด๋ฆฌ์ ์ฌ์๋ฒํธ`.์ฌ์๋ฒํธ = ์ง๊ธ.์ฌ์๋ฒํธ
JOIN (SELECT ์ฌ์๋ฒํธ, ์ฐ๋ด FROM ๊ธ์ฌ WHERE ์ข
๋ฃ์ผ์ = '9999-01-01') AS ๊ธ์ฌ
ON `ํ๋์ค์ธ ๋ถ์์ ํ์ฌ ๋ถ์๊ด๋ฆฌ์ ์ฌ์๋ฒํธ`.์ฌ์๋ฒํธ = ๊ธ์ฌ.์ฌ์๋ฒํธ
ORDER BY ์ฐ๋ด DESC
LIMIT 0, 5
) AS `ํ๋์ค์ธ ๋ถ์์ ํ์ฌ ๋ถ์๊ด๋ฆฌ์ ์ ๋ณด`
JOIN (SELECT ์ฌ์๋ฒํธ, ์
์ถ์
์๊ฐ, ์ง์ญ, ์
์ถ์
๊ตฌ๋ถ FROM ์ฌ์์ถ์
๊ธฐ๋ก WHERE ์
์ถ์
๊ตฌ๋ถ = 'O') AS `์
์ถ์
์ ๋ณด`
ON `์
์ถ์
์ ๋ณด`.์ฌ์๋ฒํธ = `ํ๋์ค์ธ ๋ถ์์ ํ์ฌ ๋ถ์๊ด๋ฆฌ์ ์ ๋ณด`.์ฌ์๋ฒํธ
ORDER BY ์ฐ๋ด DESC;
- ๋ง์ rows๋ฅผ ์ค์บํ๊ณ ์๋ ์ฌ์์ถ์ ๊ธฐ๋ก์ ์ธ๋ฑ์ค๋ฅผ ๊ฑธ์ด์ฃผ์์ต๋๋ค. ์ฌ์๋ฒํธ๋ฅผ ํตํด JOIN์ ํ๊ณ ์๊ธฐ ๋๋ฌธ์ JOIN ์ฐ๊ฒฐ ํค์ ์๋์ ๊ฐ์ด ์ธ๋ฑ์ค๋ฅผ ๋ง๋ค์ด์ฃผ์์ต๋๋ค.
CREATE INDEX `idx_์ฌ์์ถ์
๊ธฐ๋ก_์ฌ์๋ฒํธ` on `tuning`.`์ฌ์์ถ์
๊ธฐ๋ก` (์ฌ์๋ฒํธ);
-- subway ๋ฐ์ดํฐ๋ฒ ์ด์ค ์ฌ์ฉ
USE subway;
-
์ฃผ์ด์ง ๋ฐ์ดํฐ์ ์ ํ์ฉํ์ฌ ์๋ ์กฐํ ๊ฒฐ๊ณผ๋ฅผ 100ms ์ดํ๋ก ๋ฐํ
-
Coding as a Hobby ์ ๊ฐ์ ๊ฒฐ๊ณผ๋ฅผ ๋ฐํํ์ธ์.
-
๊ฐ ํ๋ก๊ทธ๋๋จธ๋ณ๋ก ํด๋นํ๋ ๋ณ์ ์ด๋ฆ์ ๋ฐํํ์ธ์. (covid.id, hospital.name)
-
ํ๋ก๊ทธ๋๋ฐ์ด ์ทจ๋ฏธ์ธ ํ์ ํน์ ์ฃผ๋์ด(0-2๋ )๋ค์ด ๋ค๋ ๋ณ์ ์ด๋ฆ์ ๋ฐํํ๊ณ user.id ๊ธฐ์ค์ผ๋ก ์ ๋ ฌํ์ธ์. (covid.id, hospital.name, user.Hobby, user.DevType, user.YearsCoding)
-
์์ธ๋๋ณ์์ ๋ค๋ 20๋ India ํ์๋ค์ ๋ณ์์ ๋จธ๋ฌธ ๊ธฐ๊ฐ๋ณ๋ก ์ง๊ณํ์ธ์. (covid.Stay)
-
์์ธ๋๋ณ์์ ๋ค๋ 30๋ ํ์๋ค์ ์ด๋ ํ์๋ณ๋ก ์ง๊ณํ์ธ์. (user.Exercise)
-
SELECT
hobby,
ROUND(COUNT(*) * 100 / total.count, 1) AS percent
FROM
programmer
JOIN
(SELECT COUNT(*) AS count FROM programmer) AS total
GROUP BY hobby, count;
ํ์ฌ programmer
ํ
์ด๋ธ์ hobby๋ฅผ ํตํด ๊ตฌ๋ถ์ง๊ณ ์์ต๋๋ค. ์ด๋ hobby์ ๋ํ ์ธ๋ฑ์ค๊ฐ ๊ฑธ๋ ค์์ง ์๊ธฐ ๋๋ฌธ์ ์ ์ฒด ํ
์ด๋ธ์ Full Scanํ ํ, ํํฐ๋ฅผ ๊ฑธ์ด์ค๋ค๊ณ ์๊ฐ์ด๋ฉ๋๋ค. ๋ฐ๋ผ์ hobby์ ๋ํด ์ธ๋ฑ์ค๋ฅผ ๋ง๋ค์ด์คฌ์ต๋๋ค.
CREATE INDEX `idx_programmer_hobby` ON `subway`.`programmer` (hobby);
์ถ๊ฐ์ ์ผ๋ก ์ ์ฒด ํ๋ก๊ทธ๋๋จธ ์๋ฅผ ๊ตฌํ๊ธฐ ์ํ ์๋ธ์ฟผ๋ฆฌ
๋ hobby ์ธ๋ฑ์ค๊ฐ ์๋ ํด๋ฌ์คํฐ๋ง ์ธ๋ฑ์ค๋ฅผ ์ฌ์ฉํ๋ฉด ๊ฒ์์ด ๋น ๋ฅผ ๊ฒ์ด๋ผ ์๊ฐํ์ฌ programmer
ํ
์ด๋ธ์ id์ pk์ unique๋ฅผ ๊ฑธ์ด์คฌ์ต๋๋ค.
ALTER TABLE `subway`.`programmer`
CHANGE COLUMN `id` `id` BIGINT(20) NOT NULL,
ADD PRIMARY KEY (`id`),
ADD UNIQUE INDEX `id_UNIQUE` (`id` ASC);
hobby ์ธ๋ฑ์ค, id ์ธ๋ฑ์ค๋ฅผ ์ฌ์ฉํ์ฌ Table Full Scan์์ Index Full Scan์ผ๋ก ๋ณ๊ฒฝ๋ ๊ฒ์ ๋ณผ ์ ์์ต๋๋ค. ๋ค๋ง Query Cost๋ ๋์์ก์ต๋๋ค.
SELECT
c.programmer_id, hospital.name AS hospital_name
FROM
(SELECT hospital_id, programmer_id FROM covid) AS c
JOIN hospital
ON hospital.id = c.hospital_id
JOIN (SELECT id FROM programmer) AS p
ON p.id = c.programmer_id
์คํ๊ณํ์ ๋ณด๋ฉด hospital
- covid
- programmer
์์ผ๋ก ์ฟผ๋ฆฌ๊ฐ ์คํ๋ฉ๋๋ค. ๋จผ์ hospital
์ Full Table Scan์ ์ธ๋ฑ์ค๋ฅผ ๊ฑธ์ด ๋ฐ๊ฟ์คฌ์ต๋๋ค. hospital
์ id๋ฅผ ํตํด ๋น๊ตํ๊ณ ์๊ธฐ ๋๋ฌธ์ id ์นผ๋ผ์ pk, unique๋ก ๋์ด ์ธ๋ฑ์ค๋ฅผ ๊ฑธ์ด์คฌ์ต๋๋ค.
ALTER TABLE `subway`.`hospital`
CHANGE COLUMN `id` `id` INT(11) NOT NULL ,
ADD PRIMARY KEY (`id`),
ADD UNIQUE INDEX `id_UNIQUE` (`id` ASC);
์ดํ ์คํ๊ณํ์ ๋ณด๋ฉด ์คํ ์์๊ฐ covid
- hospital
- programmer
์์ผ๋ก ๋ฐ๋๊ฑธ ๋ณผ ์ ์์ต๋๋ค.
covid
๋ ์ฌ์ ํ Full Table Scan์ ํ๊ณ ์๊ธฐ๋๋ฌธ์ covid
์๋ ์ธ๋ฑ์ค๋ฅผ ๊ฑธ์ด์คฌ์ต๋๋ค. programmer
์ id์ programmer_id
๋ฅผ ํตํด, hospital
์ id์ hospital_id
๋ฅผ ํตํด JOINํ๊ณ ์๊ณ programmer
์ id์ hospital
์ id๋ ์ด๋ฏธ ์ธ๋ฑ์ค๋ก ๋ฑ๋ก๋์ด ์์ผ๋ฏ๋ก, (programmer_id
, hospital_id
) ์ธ๋ฑ์ค๋ฅผ ๋ง๋ค์ด์คฌ์ต๋๋ค.
โ ์คํ๊ณํ์ ๋ณด๋ฉด hospital
์ด ๋จผ์ ์คํ๋๊ธฐ ๋๋ฌธ์ covid
์ธ๋ฑ์ค๋ฅผ (hospital_id
, programmer_id
) ์์ผ๋ก ๋ง๋ค์ด์ผ๋์ง ์์๊น ๋ผ๊ณ ์๊ฐํ์๋๋ฐ, ์ด ์์๋ก ํ๋ ๋์ํ์ง ์์์ต๋๋ค. ์ programmer_id
๋ฅผ ์ธ๋ฑ์ค๋ก ๋ฃ์ด์ผ์ง ์ ๋์ํ๋์ง ๋ชจ๋ฅด๊ฒ ์ต๋๋ค. ๐ญ
๋ค์ ์ฒ์๋ถํฐ ํด๋ณด๋ (hospital_id
, programmer_id
)๋ ์ ์ ๋์ํ์ต๋๋ค.(์ ์ ์ด ์์๋ใ
..๐ญ) ๋ค๋ง ์ด ๊ฒฝ์ฐ hospital
์ Full Table Scan์ ํ๊ฒ ๋ฉ๋๋ค. ์ตํฐ๋ง์ด์ ์ ์ํด hospital
์ ๋จผ์ ํ์ํ๊ฒ ๋์ด ๊ทธ๋ฐ ๊ฒ ๊ฐ์ต๋๋ค. ๋ order by๋ฅผ ํด์ฃผ์ง ์๋ ์ด์ hospital_id
๋ฅผ ๊ธฐ์ค์ผ๋ก ์ ๋ ฌ์ด ๋์ด programmer_id
๋ก ์ ๋ ฌํ ๊ฒฐ๊ณผ๋ฅผ ๋ณด์ง ๋ชปํฉ๋๋ค. ๋ฐ๋ฉด์ (programmer_id
, hospital_id
)๋ก ์ธ๋ฑ์ค๋ฅผ ๊ฑธ์ด์ฃผ๋ฉด programmer_id
๋ก ์ ๋ ฌ๋ ๊ฐ์ด ์ ๋์ค๊ฒ ๋ฉ๋๋ค.
CREATE INDEX `idx_covid_programmer_id_hospital_id` ON `subway`.`covid` (programmer_id, hospital_id);
Table Full Scan์ด ์์ด์ง ๊ฒ์ ๋ณผ ์ ์์ต๋๋ค.
3. ํ๋ก๊ทธ๋๋ฐ์ด ์ทจ๋ฏธ์ธ ํ์ ํน์ ์ฃผ๋์ด(0-2๋ )๋ค์ด ๋ค๋ ๋ณ์ ์ด๋ฆ์ ๋ฐํํ๊ณ user.id ๊ธฐ์ค์ผ๋ก ์ ๋ ฌํ์ธ์.
SELECT
p.id, hospital.name AS hospital_name
FROM
(SELECT hospital_id, programmer_id FROM covid WHERE programmer_id IS NOT NULL) AS c
JOIN (SELECT id FROM programmer WHERE (hobby = 'Yes' AND student LIKE 'Yes%') OR years_coding = '0-2 years') AS p
ON c.programmer_id = p.id
JOIN hospital
ON hospital.id = c.hospital_id
์ด์ ๋ฌธ์ ์์ idx_covid_programmer_id_hospital_id
์ hospital
pk๋ฅผ ๋ฑ๋กํ์๊ธฐ ๋๋ฌธ์ ๊ฒฐ๊ณผ๊ฐ ์ ๋์์ต๋๋ค. ์ถ๊ฐ์ ์ผ๋ก programmer
์ Full Table Scan์ ๋ฐ๊ฟ๋ณด๊ณ ์ถ์์ผ๋ OR ์กฐ๊ฑด์ ์ฌ์ฉํ๊ณ ์์ด์ Full Scan์ ํ ์ ๋ฐ์ ์๋ ๊ฒ ๊ฐ์ต๋๋ค.
4. ์์ธ๋๋ณ์์ ๋ค๋ 20๋ India ํ์๋ค์ ๋ณ์์ ๋จธ๋ฌธ ๊ธฐ๊ฐ๋ณ๋ก ์ง๊ณํ์ธ์.
SELECT
c.stay AS period, COUNT(c.id) AS number_of_people
FROM (SELECT id, hospital_id, member_id, programmer_id, stay FROM covid) AS c
JOIN (SELECT id FROM hospital WHERE name = '์์ธ๋๋ณ์') AS seoul_hospital
ON seoul_hospital.id = c.hospital_id
JOIN (SELECT id FROM member WHERE age BETWEEN 20 AND 29) AS twenties
ON twenties.id = c.member_id
JOIN (SELECT id FROM programmer WHERE country = 'India') AS indian
ON indian.id = programmer_id
GROUP BY period
์คํ๊ณํ์ ๋ณด๋ฉด programmer
- covid
- member
- hospital
์์ผ๋ก ์ฟผ๋ฆฌ๊ฐ ์คํ๋ฉ๋๋ค. ๋จผ์ programmer
์ Full Table Scan์ ์์ ์ฃผ๊ธฐ ์ํด ์ธ๋ฑ์ค๋ฅผ ๊ฑธ์ด์คฌ์ต๋๋ค. programmer
์ ๊ฒฝ์ฐ WHERE ์ ์์ country๋ฅผ ์ฌ์ฉํ๊ณ ์์ผ๋ฏ๋ก country
์ ์ธ๋ฑ์ค๋ฅผ ๊ฑธ์ด์คฌ์ต๋๋ค.
CREATE INDEX `idx_programmer_country` ON `subway`.`programmer` (country);
์ดํ ์คํ๊ณํ์ ๋ณด๋ฉด programmer
๋ ์ธ๋ฑ์ค๋ฅผ ์ฌ์ฉํ๊ฒ ๋ณํ๊ฑธ ๋ณผ ์ ์์ต๋๋ค.
์ด์ด์ hospital
์๋ ์ธ๋ฑ์ค๋ฅผ ๊ฑธ์ด์คฌ์ต๋๋ค. hospital
์ WHERE์ ์์ name์ ์ฌ์ฉํ๊ณ ์์ผ๋ฏ๋ก name
์ ์ธ๋ฑ์ค๋ฅผ ๊ฑธ์ด์คฌ์ต๋๋ค.
์ด๋ name
์ด ํ์ฌ TEXT ํ์
์ด์ด์ ์ธ๋ฑ์ค๋ฅผ ๊ฑธ ์ ์๊ธฐ ๋๋ฌธ์ ๋จผ์ name
์ ํ์
์ varchar๋ก ๋ณ๊ฒฝํด์ฃผ๊ณ ์ธ๋ฑ์ค๋ฅผ ๋ง๋ค์ด์คฌ์ต๋๋ค.
ALTER TABLE `subway`.`hospital`
CHANGE COLUMN `name` `name` VARCHAR(255) NULL DEFAULT NULL;
CREATE INDEX `idx_hospital_name` ON `subway`.`hospital` (name);
์คํ๊ณํ์ ๋ณด๋ฉด ๋ ๊ฐ์ ์ธ๋ฑ์ค๋ฅผ ์ถ๊ฐํ๊ณ ๋์ ์คํ ์์๊ฐ ๋ฐ๋๋ฉด์ ๋ฉ์ฉกํ๋ covid
๊ฐ Full Table Scan์ผ๋ก ๋ณํ์ต๋๋ค.(์ตํฐ๋ง์ด์ ์ ์ํด ์คํ์์๊ฐ ๋ฐ๋๋ฉด์ ์ธ๋ฑ์ค๋ฅผ ์ฌ์ฉํ์ง ๋ชปํ๊ฒ ๋์ง ์์์๊น ์ถ์ธกํ๊ณ ์์ต๋๋ค. ํน์ ์๊ณ ๊ณ์๋ค๋ฉด ์๋ ค์ฃผ์ธ์!๐)
covid
๋ ๋จผ์ hospital_id
, programmer_id
, member_id
๋ฅผ ํตํด JOIN ํ๊ธฐ ๋๋ฌธ์ (hospital_id
, programmer_id
, member_id
)์ธ๋ฑ์ค๋ฅผ ๋ง๋ค์ด์คฌ์ต๋๋ค. (๋ณตํฉ ์ธ๋ฑ์ค๋ ์ง์ ํ ์์๋๋ก ์กฐ๊ฑด๋ฌธ์ ํ์ผ์ง ์ธ๋ฑ์ค๊ฐ ์ ์ฉ๋์ด ์คํ๊ณํ์ ๋ณด๊ณ hospital_id
, programmer_id
, member_id
์์ผ๋ก ๋ณตํฉ์ธ๋ฑ์ค๋ฅผ ์์ฑํ์ต๋๋ค. ์ ์๊ฐ์๋ ์ด ์ธ๋ฑ์ค๊ฐ ์๊ธด ํ์๋ ์ตํฐ๋ง์ด์ ์ ์ํด ์คํ์์์ ๋ณํ๊ฐ ์์ด์ ์ ๋์ํ ๊ฒ ๊ฐ์ต๋๋ค.)
CREATE INDEX `idx_covid_hospital_id_programmer_id_member_id` ON `subway`.`covid` (hospital_id, programmer_id, member_id);
์ดํ ์คํ๊ณํ์ ๋ณด๋ฉด Table Full Scan์ด ์์ด์ง ๊ฒ์ ๋ณผ ์ ์์ต๋๋ค.
์ถ๊ฐ์ ์ผ๋ก ์คํ๊ณํ์ ๋ณด๋ฉด member
์ filtered๊ฐ ๋นํจ์จ์ ์ธ ๊ฒ์ ๋ณผ ์ ์์ต๋๋ค. member
์์๋ age์ BETWEEN ๊ตฌ๋ฌธ์ ์ฐ๊ณ ์๊ธฐ ๋๋ฌธ์ age์ ์ธ๋ฑ์ค๋ฅผ ๊ฑธ์ด ์ ๋ ฌ๋๋๋ก ํ์์ต๋๋ค.
CREATE INDEX `idx_member_age` ON `subway`.`member` (age);
SELECT
p.exercise AS exercise, COUNT(c.id) AS number_of_exercises
FROM (SELECT id, hospital_id, member_id, programmer_id FROM covid) AS c
JOIN (SELECT id FROM hospital WHERE name = '์์ธ๋๋ณ์') AS seoul_hospital
ON seoul_hospital.id = c.hospital_id
JOIN (SELECT id FROM member WHERE age BETWEEN 30 AND 39) AS thirties
ON thirties.id = c.member_id
JOIN (SELECT id, exercise FROM programmer) AS p
ON p.id = programmer_id
GROUP BY p.exercise
์ด์ ๊ณผ์ ์์ ๋ง๋ค์๋ ์ธ๋ฑ์ค๋ค์ด ์ ๋์ํ์ฌ ๋ฐ๋ก ์ธ๋ฑ์ค๋ฅผ ์ถ๊ฐํ์ง ์์์ต๋๋ค.