Home > Backend Development > ๐Ÿ“š[Backend Development] ๋ฌดํ•œ Depth ๋Œ“๊ธ€ ์กฐํšŒ - Path Enumeration & ์ธ๋ฑ์Šค ์ตœ์ ํ™”

๐Ÿ“š[Backend Development] ๋ฌดํ•œ Depth ๋Œ“๊ธ€ ์กฐํšŒ - Path Enumeration & ์ธ๋ฑ์Šค ์ตœ์ ํ™”
Backend Ddevelopment

โ€œ๐Ÿ“š[Backend Development] ๋ฌดํ•œ Depth ๋Œ“๊ธ€ ์กฐํšŒ - Path Enumeration & ์ธ๋ฑ์Šค ์ตœ์ ํ™”โ€

โœ…1๏ธโƒฃ Path Enumeration ๋ฐฉ์‹์—์„œ ๋Œ“๊ธ€ path ๊ฒฐ์ •

  • Path Enumeration(๊ฒฝ๋กœ ์—ด๊ฑฐ) ๋ฐฉ์‹์€ ๊ฐ ๋Œ“๊ธ€์˜ path๋ฅผ ๊ณ„์ธต์ ์œผ๋กœ ์ €์žฅํ•˜์—ฌ ์ •๋ ฌ ๋ฐ ๊ฒ€์ƒ‰์„ ๋น ๋ฅด๊ฒŒ ์ˆ˜ํ–‰ํ•˜๋Š” ๊ธฐ๋ฒ•์ž…๋‹ˆ๋‹ค.
  • ๋Œ“๊ธ€์ด ์ถ”๊ฐ€๋  ๋•Œ ๋ถ€๋ชจ ๋Œ“๊ธ€์˜ path๋ฅผ ์ƒ์†๋ฐ›๊ณ , ํ•˜์œ„ ๋Œ“๊ธ€ ์ค‘ ๊ฐ€์žฅ ํฐ path๋ฅผ ๊ธฐ์ค€์œผ๋กœ ์ƒˆ๋กœ์šด path๊ฐ€ ๊ฒฐ์ •๋ฉ๋‹ˆ๋‹ค.

๐Ÿ“Œ1๏ธโƒฃ ํ˜„์žฌ ๋Œ“๊ธ€ ํŠธ๋ฆฌ ๊ตฌ์กฐ

  • ํ˜„์žฌ ๋Œ“๊ธ€ ํŠธ๋ฆฌ์˜ path๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์Šต๋‹ˆ๋‹ค.
    • 00a0z ๋Œ“๊ธ€ ์•„๋ž˜์— ๊ณ„์ธต์ ์œผ๋กœ ์ •๋ ฌ๋œ ํ•˜์œ„ ๋Œ“๊ธ€๋“ค์ด ์žˆ์Šต๋‹ˆ๋‹ค.
    • ๋Œ“๊ธ€์˜ path๋Š” ๋ถ€๋ชจ ๋Œ“๊ธ€์˜ path๋ฅผ ์ƒ์†๋ฐ›์•„ 00000, 00001, 00002 ๋“ฑ์œผ๋กœ ์ถ”๊ฐ€๋ฉ๋‹ˆ๋‹ค.

๐Ÿ“Œ2๏ธโƒฃ ์ƒˆ๋กœ์šด ๋Œ“๊ธ€ ์ถ”๊ฐ€ ์š”์ฒญ

  • ์‚ฌ์šฉ์ž๊ฐ€ 00a0z ๋Œ“๊ธ€์˜ ํ•˜์œ„์— ์ƒˆ๋กœ์šด ๋Œ“๊ธ€์„ ์ถ”๊ฐ€ํ•˜๋ ค๊ณ  ํ•ฉ๋‹ˆ๋‹ค.
    • ์ƒˆ๋กœ์šด ๋Œ“๊ธ€์ด ์ถ”๊ฐ€๋  path๋ฅผ ๊ฒฐ์ •ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.
    • ๊ธฐ์กด ๋Œ“๊ธ€ ์ค‘ ๊ฐ€์žฅ ํฐ path๋ฅผ ์ฐพ์•„ ์ด๋ฅผ ๊ธฐ์ค€์œผ๋กœ +1์„ ์ ์šฉํ•˜์—ฌ ์ƒˆ๋กœ์šด path๋ฅผ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค.

๐Ÿ“Œ3๏ธโƒฃ childrenTopPath ์ฐพ๊ธฐ

  • ์ƒˆ๋กœ์šด ๋Œ“๊ธ€์„ ์ถ”๊ฐ€ํ•  ๋•Œ ํ˜„์žฌ ์กด์žฌํ•˜๋Š” ํ•˜์œ„ ๋Œ“๊ธ€ ์ค‘ ๊ฐ€์žฅ ํฐ path(childrenTopPath)๋ฅผ ์ฐพ๊ณ  ํ•ด๋‹น ๊ฐ’์— +1์„ ํ•˜์—ฌ ์ƒˆ๋กœ์šด ๋Œ“๊ธ€์˜ path๋ฅผ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค.
    • ํ˜„์žฌ 00a0z์˜ ํ•˜์œ„ ๋Œ“๊ธ€ ์ค‘ ๊ฐ€์žฅ ํฐ path๋Š” 00a0z 00002์ž…๋‹ˆ๋‹ค.
    • ๋”ฐ๋ผ์„œ, ์ƒˆ๋กœ์šด ๋Œ“๊ธ€์˜ path๋Š” 00a0z 00003์ด ๋ฉ๋‹ˆ๋‹ค.

๐Ÿ“Œ4๏ธโƒฃ descendantsTopPath๋ฅผ ๊ณ ๋ คํ•œ ์ตœ์ข… path ๊ฒฐ์ •

  • ํ•˜์ง€๋งŒ ์ž์‹ ๋Œ“๊ธ€์ด ์กด์žฌํ•˜๋Š” ๊ฒฝ์šฐ, ๋‹จ์ˆœํžˆ childrenTopPath๋งŒ ๊ณ ๋ คํ•˜๋ฉด ์•ˆ ๋ฉ๋‹ˆ๋‹ค. ๊ฐ€์žฅ ๊นŠ์€ depth๊นŒ์ง€ ๊ณ ๋ คํ•œ descendantsTopPath๋ฅผ ์ฐพ์•„์•ผ ํ•ฉ๋‹ˆ๋‹ค.
    • descendantsTopPath๋Š” ๋ถ€๋ชจ ๋Œ“๊ธ€์„ ํฌํ•จํ•œ ๋ชจ๋“  ์ž์‹ ๋Œ“๊ธ€ ์ค‘ ๊ฐ€์žฅ ํฐ path์ž…๋‹ˆ๋‹ค.
    • childrenTopPath = 00a0z 00002์ด๋ฏ€๋กœ, ์ƒˆ๋กœ์šด ๋Œ“๊ธ€์˜ path๋Š” 00a0z 00003์ด ๋ฉ๋‹ˆ๋‹ค.

โœ…2๏ธโƒฃ MySQL์—์„œ descendantsTopPath ์ฐพ๊ธฐ.

  • Path Enumeration ๋ฐฉ์‹์—์„œ๋Š” ๋น ๋ฅธ ๊ฒ€์ƒ‰์„ ์œ„ํ•ด ์ธ๋ฑ์Šค๋ฅผ ํ™œ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ํŠนํžˆ descendantsTopPath๋ฅผ ์ฐพ์„ ๋•Œ Backward Index Scan์„ ์‚ฌ์šฉํ•˜๋ฉด ์„ฑ๋Šฅ์„ ์ตœ์ ํ™”ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

๐Ÿ“Œ1๏ธโƒฃ descendantsTopPath๋ฅผ ์ฐพ๋Š” SQL

SELECT path FROM comment_v2
WHERE article_id = {article_id}
AND path > {parentPath} -- ๋ถ€๋ชจ ๋Œ“๊ธ€ ์ œ์™ธ
AND path LIKE {parentPath}% -- ๋ถ€๋ชจ ๋Œ“๊ธ€ prefix๋ฅผ ํฌํ•จํ•˜๋Š” ๋ชจ๋“  ์ž์‹ ๋Œ“๊ธ€ ์กฐํšŒ
ORDER BY path DESC LIMIT 1; -- ๊ฐ€์žฅ ํฐ path๋ฅผ ์ฐพ๊ธฐ ์œ„ํ•ด ๋‚ด๋ฆผ์ฐจ์ˆœ ์ •๋ ฌ
  • ๊ฐ€์žฅ ํฐ path(descendantsTopPath)๋ฅผ ์ฐพ์„ ๋•Œ ๋‚ด๋ฆผ์ฐจ์ˆœ ์ •๋ ฌ์„ ํ™œ์šฉํ•ฉ๋‹ˆ๋‹ค.
  • LIMIT 1์„ ์‚ฌ์šฉํ•˜์—ฌ ๋ถˆํ•„์š”ํ•œ ๋ฐ์ดํ„ฐ ์กฐํšŒ๋ฅผ ์ค„์ด๊ณ  ์„ฑ๋Šฅ์„ ์ตœ์ ํ™”ํ•ฉ๋‹ˆ๋‹ค.

๐Ÿ“Œ2๏ธโƒฃ Backward Index Scan ํ™œ์šฉ

  • MySQL์—์„œ๋Š” ORDER BY path DESC๋ฅผ ์‚ฌ์šฉํ•  ๋•Œ ์—ญ์ˆœ์œผ๋กœ ์ธ๋ฑ์Šค๋ฅผ ํƒ์ƒ‰ํ•˜๋Š” Backward Index Scan์„ ์ˆ˜ํ–‰ํ•ฉ๋‹ˆ๋‹ค.
    • path ํ•„๋“œ์— ์˜ค๋ฆ„์ฐจ์ˆœ(ASC) ์ธ๋ฑ์Šค๊ฐ€ ์„ค์ •๋˜์–ด ์žˆ์–ด๋„, ๋‚ด๋ฆผ์ฐจ์ˆœ(DESC) ์ •๋ ฌ์„ ํ†ตํ•ด ๊ฐ€์žฅ ํฐ path๋ฅผ ๋น ๋ฅด๊ฒŒ ์ฐพ์„ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
    • ์ธ๋ฑ์Šค ํŠธ๋ฆฌ(Leaf Node) ๊ฐ„์˜ ์–‘๋ฐฉํ–ฅ ํฌ์ธํ„ฐ๋ฅผ ํ™œ์šฉํ•˜์—ฌ ์—ญ์ˆœ ๊ฒ€์ƒ‰์„ ์ˆ˜ํ–‰ํ•ฉ๋‹ˆ๋‹ค.

โœ…3๏ธโƒฃ MySQL Query Plan ๋ถ„์„(EXPLAIN)

  • MySQL์—์„œ descendantsTopPath๋ฅผ ์ฐพ๋Š” ์ฟผ๋ฆฌ์˜ ์‹คํ–‰ ๊ณ„ํš์„ ๋ถ„์„ํ•ด๋ด…๋‹ˆ๋‹ค.
EXPLAIN SELECT path FROM comment_v2
WHERE article_id = 1
AND path > '00a0z'
AND path LIKE '00a0z%'
ORDER BY path DESC LIMIT 1;

๐Ÿ“ŒEXPLAIN ๊ฒฐ๊ณผ ๋ถ„์„

  • 1.idx_article_id_path ์ธ๋ฑ์Šค ์‚ฌ์šฉ๋จ
    • โžž ์ธ๋ฑ์Šค๋ฅผ ํ™œ์šฉํ•˜์—ฌ ๋น ๋ฅด๊ฒŒ path๋ฅผ ์กฐํšŒํ•  ์ˆ˜ ์žˆ์Œ
  • 2. Backward Index Scan ์ ์šฉ๋จ
    • โžž ORDER BY path DESC LIMIT 1์„ ํ†ตํ•ด ์—ญ์ˆœ ํƒ์ƒ‰ ์ˆ˜ํ–‰
  • 3. Using Index ์ ์šฉ๋จ
    • โžž ์ธ๋ฑ์Šค์—์„œ ์ง์ ‘ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ค๊ธฐ ๋•Œ๋ฌธ์— ์„ฑ๋Šฅ ์ตœ์ ํ™” ๊ฐ€๋Šฅ

โœ…4๏ธโƒฃ descendantsTopPath๋ฅผ ํ™œ์šฉํ•œ ์ •๋ ฌ ์ตœ์ ํ™”

  • Path Enumeration ๋ฐฉ์‹์—์„œ๋Š” ์ •๋ ฌ ์ƒํƒœ๋ฅผ ์œ ์ง€ํ•œ ์ฑ„ descendantsTopPath๋ฅผ ๊ฒ€์ƒ‰ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
    • path ์ •๋ ฌ ์ƒํƒœ๋ฅผ ์œ ์ง€ํ•˜๋ฉด์„œ ์—ญ์ˆœ์œผ๋กœ ์ธ๋ฑ์Šค๋ฅผ ํƒ์ƒ‰ํ•˜๋ฉด, ๊ฐ€์žฅ ํฐ path(descendantsTopPath)๋ฅผ ๋น ๋ฅด๊ฒŒ ์ฐพ์„ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
    • Backward Index Scan์„ ํ™œ์šฉํ•˜๋ฉด ๋กœ๊ทธ ์‹œ๊ฐ„(log time) ๋‚ด์— ์กฐํšŒ๊ฐ€ ๊ฐ€๋Šฅํ•ฉ๋‹ˆ๋‹ค.

โœ…5๏ธโƒฃ ๊ฒฐ๋ก 

๐Ÿš€ Path Enumeration ๋ฐฉ์‹์—์„œ ๋Œ“๊ธ€์„ ์ถ”๊ฐ€ํ•  ๋•Œ, path๋ฅผ ๊ฒฐ์ •ํ•˜๋Š” ๊ณผ์ •.

  • โœ… ๊ฐ€์žฅ ํฐ childrenTopPath๋ฅผ ์ฐพ๊ณ , +1์„ ํ•˜์—ฌ ์ƒˆ๋กœ์šด path๋ฅผ ์ƒ์„ฑ
  • โœ… descendantsTopPath๋ฅผ ์ฐพ์•„ ๊ณ„์ธต ๊ตฌ์กฐ๋ฅผ ์œ ์ง€ํ•˜๋ฉด์„œ ๋Œ“๊ธ€์„ ์ •๋ ฌ
  • โœ… MySQL์˜ Backward Index Scan์„ ํ™œ์šฉํ•˜์—ฌ ๋น ๋ฅด๊ฒŒ descendantsTopPath๋ฅผ ๊ฒ€์ƒ‰
  • โœ… ๋Œ€๊ทœ๋ชจ ๋ฐ์ดํ„ฐ์—์„œ๋„ ์ธ๋ฑ์Šค๋ฅผ ํ™œ์šฉํ•˜์—ฌ ๋น ๋ฅธ ์„ฑ๋Šฅ์„ ์œ ์ง€

๐Ÿ“Œ Path Enumeration ๋ฐฉ์‹์„ ์‚ฌ์šฉํ•  ๋•Œ๋Š”, Backward Index Scan์„ ํ™œ์šฉํ•˜์—ฌ ์ตœ์ ์˜ ์„ฑ๋Šฅ์„ ๋ณด์žฅํ•˜๋Š” ๊ฒƒ์ด ์ค‘์š”.