Home > DB > ๐Ÿ’พ[Database] ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ์กฐํšŒ - ํŽ˜์ด์ง• ์ฒ˜๋ฆฌ

๐Ÿ’พ[Database] ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ์กฐํšŒ - ํŽ˜์ด์ง• ์ฒ˜๋ฆฌ
Database

๐Ÿ’พ[Database] ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ์กฐํšŒ - ํŽ˜์ด์ง• ์ฒ˜๋ฆฌ.

โœ…1๏ธโƒฃ ๋น„ํšจ์œจ์ ์ธ ํŽ˜์ด์ง• ์ฒ˜๋ฆฌ.

  • โ†˜๏ธŽ ์„œ๋ฒ„ ์• ํ”Œ๋ฆฌ์ผ€์ด์…˜ ๋‚ด์˜ ๋ฉ”๋ชจ๋ฆฌ๋กœ ๋””์Šคํฌ์— ์ €์žฅ๋œ ๋ชจ๋“  ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ค๊ณ , ํŠน์ • ํŽ˜์ด์ง€๋งŒ ์ถ”์ถœํ•˜๋Š” ๊ฒƒ์€ ๋น„ํšจ์œจ์ ์ž„.
    • โ†˜๏ธŽ ๋””์Šคํฌ ์ ‘๊ทผ์€ ๋ฉ”๋ชจ๋ฆฌ ์ ‘๊ทผ๋ณด๋‹ค ๋А๋ฆฌ๊ธฐ ๋•Œ๋ฌธ์ž„.
      • โ†˜๏ธŽ ๋””์Šคํฌ I/O ๋น„์šฉ
    • โ†˜๏ธŽ ๋””์Šคํฌ์— ์ €์žฅ๋œ ๋ฐ์ดํ„ฐ๋Š” ๋ฉ”๋ชจ๋ฆฌ ์šฉ๋Ÿ‰์„ ์ดˆ๊ณผํ•  ์ˆ˜ ์žˆ์Œ.
      • โ†˜๏ธŽ Out of Memory(OOM)
        • โ†˜๏ธŽ ์‹œ์Šคํ…œ์—์„œ ์‚ฌ์šฉ ๊ฐ€๋Šฅํ•œ ๋ฉ”๋ชจ๋ฆฌ๊ฐ€ ๋ชจ๋‘ ์‚ฌ์šฉ๋˜์–ด ๋” ์ด์ƒ ํ•  ๋‹นํ•  ์ˆ˜ ์—†๋Š” ์ƒํ™ฉ.

          โœ…2๏ธโƒฃ ํšจ์œจ์ ์ธ ํŽ˜์ด์ง• ์ฒ˜๋ฆฌ.

  • โ†˜๏ธŽ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ํŠน์ • ํŽ˜์ด์ง€์˜ ๋ฐ์ดํ„ฐ๋งŒ ๋ฐ”๋กœ ์ถ”์ถœํ•˜๋Š” ๋ฐฉ๋ฒ•์ด ํ•„์š”ํ•จ.
    • โ†˜๏ธŽ ํŽ˜์ด์ง• ์ฟผ๋ฆฌ

      โœ…3๏ธโƒฃ ํŽ˜์ด์ง• ๋ฐฉ์‹.

  • โ†˜๏ธŽ ํด๋ผ์ด์–ธํŠธ ๋˜๋Š” ์„œ๋น„์Šค ํŠน์„ฑ์— ๋”ฐ๋ผ ํฌ๊ฒŒ ๋‘ ๊ฐ€์ง€๋กœ ๋‚˜๋‰จ
    • โ†˜๏ธŽ ํŽ˜์ด์ง€ ๋ฒˆํ˜ธ
    • โ†˜๏ธŽ ** ๋ฌดํ•œ ์Šคํฌ๋กค**

      ๐Ÿ“Œ ํŽ˜์ด์ง€ ๋ฒˆํ˜ธ ๋ฐฉ์‹.

  • โ†˜๏ธŽ ์ด๋™ํ•  ํŽ˜์ด์ง€ ๋ฒˆํ˜ธ๊ฐ€ ๋ช…์‹œ์ ์œผ๋กœ ์ง€์ •๋จ.
  • โ†˜๏ธŽ ์›ํ•˜๋Š” ํŽ˜์ด์ง€๋กœ ์ฆ‰์‹œ ์ด๋™ ๊ฐ€๋Šฅ.

    ๐Ÿ“Œ ๋ฌดํ•œ ์Šคํฌ๋กค ๋ฐฉ์‹.

  • โ†˜๏ธŽ ์Šคํฌ๋กค์„ ๋‚ด๋ฆฌ๋ฉด ๋‹ค์Œ ๋ฐ์ดํ„ฐ๊ฐ€ ์กฐํšŒ๋จ. โžž ๋”๋ณด๊ธฐ.
  • โ†˜๏ธŽ ์ฃผ๋กœ ๋ชจ๋ฐ”์ผ ํ™˜๊ฒฝ์—์„œ ์‚ฌ์šฉํ•จ.

โœ…4๏ธโƒฃ ํŽ˜์ด์ง€ ๋ฒˆํ˜ธ ๋ฐฉ์‹.

  • โ†˜๏ธŽ ํ•„์š”ํ•œ ์ •๋ณด
    • โ†˜๏ธŽ 1๏ธโƒฃ N๋ฒˆ ํŽ˜์ด์ง€์—์„œ M๊ฐœ์˜ ๊ฒŒ์‹œ๊ธ€
    • โ†˜๏ธŽ 2๏ธโƒฃ ๊ฒŒ์‹œ๊ธ€์˜ ๊ฐœ์ˆ˜
      • โ†˜๏ธŽ ํŽ˜์ด์ง€ ๋ฒˆํ˜ธ ํ™œ์„ฑํ™”์— ํ•„์š”.
      • โ†˜๏ธŽ ์˜ˆ์‹œ: ํŽ˜์ด์ง€ ๋‹น 30๊ฐœ์˜ ๊ฒŒ์‹œ๊ธ€์„ ๋ณด์—ฌ์ฃผ๊ณ , ์ด 94๊ฐœ์˜ ๊ฒŒ์‹œ๊ธ€์ด ์žˆ๋‹ค๋ฉด, ์‚ฌ์šฉ์ž๋Š” ํด๋ผ์ด์–ธํŠธ ํ™”๋ฉด์—์„œ 4๋ฒˆ ํŽ˜์ด์ง€๊นŒ์ง€ ์ด๋™ํ•  ์ˆ˜ ์žˆ๋‹ค๋Š” ์‚ฌ์‹ค์„ ์ธ์ง€ํ•ด์•ผ ํ•จ.
    • โ†˜๏ธŽ 3๏ธโƒฃ N๋ฒˆ ํŽ˜์ด์ง€์—์„œ M๊ฐœ์˜ ๊ฒŒ์‹œ๊ธ€์„ ์กฐํšŒํ•˜๋ ค๋ฉด?
      • โ†˜๏ธŽ SQL offset, limit์„ ํ™œ์šฉํ•˜์—ฌ ํŽ˜์ด์ง€ ์ฟผ๋ฆฌ๋ฅผ ํ•  ์ˆ˜ ์žˆ์Œ.
      • โ†˜๏ธŽ offset ์ง€์ ๋ถ€ํ„ฐ, limit๊ฐœ์˜ ๋ฐ์ดํ„ฐ ์กฐํšŒ.
    • โ†˜๏ธŽ 4๏ธโƒฃ SELECT ์ฟผ๋ฆฌ๋กœ ๋งŒ๋“ค์–ด๋ณด๋ฉด?
      • โ†˜๏ธŽ ๊ฒŒ์‹œํŒ๋ณ„ ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ์ตœ์‹ ์ˆœ ์กฐํšŒ
        • โ†˜๏ธŽ shard key = board_id์ด๊ธฐ ๋•Œ๋ฌธ์—, ๋‹จ์ผ ์ƒค๋“œ์—์„œ ์กฐํšŒํ•  ์ˆ˜ ์žˆ์Œ.
        • โ†˜๏ธŽ limit = M๊ฐœ์˜ ๊ฒŒ์‹œ๊ธ€
        • โ†˜๏ธŽ offset = (N๋ฒˆ ํŽ˜์ด์ง€ - 1) * M
          • โ†˜๏ธŽ N > 0
      • โ†˜๏ธŽ SQL QUERY
          SELECT * FROM article // ๊ฒŒ์‹œ๊ธ€ ํ…Œ์ด๋ธ”
          WHERE board_id = {board_id} // ๊ฒŒ์‹œํŒ๋ณ„
          ORDER BY created_at DESC // ์ตœ์‹ ์ˆœ
          LIMIT {LIMIT} OFFSET {OFFSET}; // N๋ฒˆ ํŽ˜์ด์ง€์—์„œ M๊ฐœ
        

      • โ†˜๏ธŽ 1,200๋งŒ ๊ฑด์˜ ์ ์€ ๋ฐ์ดํ„ฐ์—์„œ 30๊ฐœ์˜ ๊ฒŒ์‹œ๊ธ€์„ ์กฐํšŒํ•˜๋Š”๋ฐ 4์ดˆ๊ฐ€ ์†Œ์š”๋˜์—ˆ์Œ. ์ •์ƒ์ ์œผ๋กœ ์„œ๋น„์Šคํ•˜๊ธฐ์—๋Š” ์–ด๋ ค์šด ์ƒํ™ฉ์ž„.

      • โ†˜๏ธŽ explain select * from article where board_id = 1 order by created_at desc limit 30 offset 90;
      • โ†˜๏ธŽ type = ALL โžž ํ…Œ์ด๋ธ” ์ „์ฒด๋ฅผ ์ฝ๋Š”๋‹ค (ํ’€ ์Šค์บ”)
      • โ†˜๏ธŽ Extras = Using where; Using filesort โžž where ์ ˆ๋กœ ์กฐ๊ฑด์— ๋Œ€ํ•ด ํ•„ํ„ฐ๋ง,
        • โ†˜๏ธŽ ๋ฐ์ดํ„ฐ๊ฐ€ ๋งŽ๊ธฐ ๋•Œ๋ฌธ์— ๋ฉ”๋ชจ๋ฆฌ์—์„œ ์ •๋ ฌ์„ ์ˆ˜ํ–‰ํ•  ์ˆ˜ ์—†์–ด์„œ, ํŒŒ์ผ(๋””์Šคํฌ)์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ์ •๋ ฌํ•˜๋Š” filesort ์ˆ˜ํ–‰
        • โ†˜๏ธŽ ์ „์ฒด ๋ฐ์ดํ„ฐ์— ๋Œ€ํ•ด ํ•„ํ„ฐ๋ง ๋ฐ ์ •๋ ฌํ•˜๊ธฐ ๋•Œ๋ฌธ์— ์•„์ฃผ ํฐ ๋น„์šฉ์ด ๋“  ๊ฒƒ.

๐Ÿ™‹โ€โ™‚๏ธ Query Plain
๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ์ฟผ๋ฆฌ์— ์˜ํ•ด ๋ฐ์ดํ„ฐ ์ ‘๊ทผ์ด ์‹คํ–‰๋˜๋Š” ๊ณ„ํš ๋˜๋Š” ์ ˆ์ฐจ ์‚ฌ์šฉ๋ฒ•.
explain {query}

โœ…5๏ธโƒฃ ์ธ๋ฑ์Šค์— ๋Œ€ํ•œ ์ดํ•ด

๐Ÿ“Œ Intro

  • โ†˜๏ธŽ ์ธ๋ฑ์Šค(Index)๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ๋น ๋ฅด๊ฒŒ ๊ฒ€์ƒ‰ํ•˜๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉํ•˜๋Š” ๋ฐ์ดํ„ฐ ๊ตฌ์กฐ.
  • โ†˜๏ธŽ ์ฑ…์˜ ์ƒ‰์ธ(Index)๊ณผ ๋น„์Šทํ•œ ๊ฐœ๋…์œผ๋กœ, ํŠน์ • ๋ฐ์ดํ„ฐ๋ฅผ ์ฐพ๊ธฐ ์œ„ํ•ด ๋ชจ๋“  ๋ฐ์ดํ„ฐ๋ฅผ ์ฒ˜์Œ๋ถ€ํ„ฐ ๋๊นŒ์ง€ ํƒ์ƒ‰ํ•˜์ง€ ์•Š๊ณ ๋„ ์›ํ•˜๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ํšจ์œจ์ ์œผ๋กœ ์กฐํšŒํ•  ์ˆ˜ ์žˆ๋„๋ก ๋„์™€์คŒ.

โœ…6๏ธโƒฃ ์ธ๋ฑ์Šค์˜ ํ•ต์‹ฌ ๊ฐœ๋….

  • โ†˜๏ธŽ ๋น ๋ฅธ ๊ฒ€์ƒ‰ : ํ…Œ์ด๋ธ”์—์„œ ํŠน์ • ๋ฐ์ดํ„ฐ๋ฅผ ๋” ๋น ๋ฅด๊ฒŒ ์ฐพ์„ ์ˆ˜ ์žˆ์Œ.
  • โ†˜๏ธŽ ์ •๋ ฌ๋œ ๊ตฌ์กฐ : ์ธ๋ฑ์Šค๋Š” ์ผ๋ฐ˜์ ์œผ๋กœ B-Tree ๋˜๋Š” Hash ๊ตฌ์กฐ๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋ฐ์ดํ„ฐ๋ฅผ ์ •๋ ฌํ•จ.
  • โ†˜๏ธŽ ์กฐํšŒ ์„ฑ๋Šฅ ํ–ฅ์ƒ : WHERE, JOIN, BY์™€ ๊ฐ™์€ SQL ์—ฐ์‚ฐ ์‹œ ์„ฑ๋Šฅ์„ ๊ฐœ์„ ํ•จ.
  • โ†˜๏ธŽ ๊ณต๊ฐ„ ์ฐจ์ง€ : ์ธ๋ฑ์Šค๋ฅผ ์œ ์ง€ํ•˜๊ธฐ ์œ„ํ•ด ์ถ”๊ฐ€์ ์ธ ์ €์žฅ ๊ณต๊ฐ„์ด ํ•„์š”ํ•จ.
  • โ†˜๏ธŽ ์“ฐ๊ธฐ ์„ฑ๋Šฅ ์ €ํ•˜ : INSERT, UPDATE, DELETE ์ž‘์—… ์‹œ ์ธ๋ฑ์Šค๋ฅผ ์—…๋ฐ์ดํŠธํ•ด์•ผ ํ•˜๋ฏ€๋กœ ์“ฐ๊ธฐ ์ž‘์—…์˜ ์„ฑ๋Šฅ์ด ์ €ํ•˜๋  ์ˆ˜ ์žˆ์Œ.

โœ…7๏ธโƒฃ ์ธ๋ฑ์Šค์˜ ์ž‘๋™ ์›๋ฆฌ.

  • โ†˜๏ธŽ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋Š” ํ…Œ์ด๋ธ”์˜ ํŠน์ • ์ปฌ๋Ÿผ(์—ด)์— ์ธ๋ฑ์Šค๋ฅผ ์ƒ์„ฑํ•จ.
  • โ†˜๏ธŽ SQL ์ฟผ๋ฆฌ๋ฅผ ์‹คํ–‰ํ•  ๋•Œ, ์ธ๋ฑ์Šค๊ฐ€ ์žˆ๋Š” ์ปฌ๋Ÿผ์„ ๊ธฐ์ค€์œผ๋กœ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์—”์ง„์€ ์ธ๋ฑ์Šค๋ฅผ ๋จผ์ € ์กฐํšŒํ•จ.
  • โ†˜๏ธŽ ์ธ๋ฑ์Šค๋ฅผ ํ†ตํ•ด ๋ฐ์ดํ„ฐ์˜ ์œ„์น˜๋ฅผ ์ฐพ๊ณ , ํ•ด๋‹น ์œ„์น˜์—์„œ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ด.

โœ…8๏ธโƒฃ ์ธ๋ฑ์Šค์˜ ์ข…๋ฅ˜.

1๏ธโƒฃ ์ผ๋ฐ˜ ์ธ๋ฑ์Šค(B-Tree Index)

  • โ†˜๏ธŽ ๊ฐ€์žฅ ๋งŽ์ด ์‚ฌ์šฉ๋˜๋Š” ์ธ๋ฑ์Šค.
  • โ†˜๏ธŽ ์ปฌ๋Ÿผ์˜ ๊ฐ’์„ ์˜ค๋ฆ„์ฐจ์ˆœ ๋˜๋Š” ๋‚ด๋ฆผ์ฐจ์ˆœ์œผ๋กœ ์ •๋ ฌํ•˜์—ฌ ์ €์žฅํ•จ.

2๏ธโƒฃ ๊ณ ์œ  ์ธ๋ฑ์Šค(Unique Index)

  • โ†˜๏ธŽ ์ธ๋ฑ์Šค๊ฐ€ ์ง€์ •๋œ ์ปฌ๋Ÿผ์€ ์ค‘๋ณต๋œ ๊ฐ’์„ ๊ฐ€์งˆ ์ˆ˜ ์—†์Œ.
  • โ†˜๏ธŽ PRIMARY KEY๋‚˜ UNIQUE ์ œ์•ฝ ์กฐ๊ฑด์ด ํฌํ•จ๋œ ๊ฒฝ์šฐ ์ž๋™์œผ๋กœ ์ƒ์„ฑ๋จ.

3๏ธโƒฃ ๋ณตํ•ฉ ์ธ๋ฑ์Šค(Composite Index)

  • โ†˜๏ธŽ ๋‘ ๊ฐœ ์ด์ƒ์˜ ์ปฌ๋Ÿผ์„ ๊ฒฐํ•ฉํ•˜์—ฌ ํ•˜๋‚˜์˜ ์ธ๋ฑ์Šค๋ฅผ ๋งŒ๋“ฆ.
  • โ†˜๏ธŽ ๋ณตํ•ฉ ์ธ๋ฑ์Šค๋Š” ์—ฌ๋Ÿฌ ์กฐ๊ฑด์„ ๋™์‹œ์— ์‚ฌ์šฉํ•  ๋•Œ ์œ ์šฉ.

4๏ธโƒฃ ํ’€ํ…์ŠคํŠธ ์ธ๋ฑ์Šค(Full-Text Index)

  • โ†˜๏ธŽ ๋ฌธ์ž์—ด ๊ฒ€์ƒ‰์— ์ตœ์ ํ™”๋œ ์ธ๋ฑ์Šค.
  • โ†˜๏ธŽ ๋Œ€๋Ÿ‰์˜ ํ…์ŠคํŠธ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฒ€์ƒ‰ํ•  ๋•Œ ์‚ฌ์šฉ.

5๏ธโƒฃ ํ•ด์‹œ ์ธ๋ฑ์Šค(Hash Index)

  • โ†˜๏ธŽ ๊ฐ’์„ ํ•ด์‹œ ํ•จ์ˆ˜๋กœ ๋ณ€ํ™˜ํ•˜์—ฌ ์ €์žฅ.
  • โ†˜๏ธŽ ์ •ํ™•ํ•œ ๊ฐ’์„ ๋น ๋ฅด๊ฒŒ ์ฐพ์„ ๋•Œ ์‚ฌ์šฉ.

โœ…9๏ธโƒฃ ์ธ๋ฑ์Šค๋ฅผ ์‚ฌ์šฉํ•ด์•ผ ํ•˜๋Š” ๊ฒฝ์šฐ.

  • โ†˜๏ธŽ WHERE ์ ˆ๋กœ ํŠน์ • ๋ฐ์ดํ„ฐ๋ฅผ ์ž์ฃผ ์กฐํšŒํ•  ๋•Œ.
  • โ†˜๏ธŽ JOIN ์—ฐ์‚ฐ์ด ๋นˆ๋ฒˆํ•˜๊ฒŒ ๋ฐœ์ƒํ•  ๋•Œ.
  • โ†˜๏ธŽ ORDER BY ๋˜๋Š” GROUP BY ์ ˆ๋กœ ์ •๋ ฌ์ด๋‚˜ ๊ทธ๋ฃนํ™”๊ฐ€ ํ•„์š”ํ•  ๋•Œ
  • โ†˜๏ธŽ ์ž์ฃผ ์‚ฌ์šฉ๋˜๋Š” ์ปฌ๋Ÿผ์— ๋Œ€ํ•ด ๋น ๋ฅธ ๊ฒ€์ƒ‰์ด ํ•„์š”ํ•  ๋•Œ.

โœ…1๏ธโƒฃ0๏ธโƒฃ ์ธ๋ฑ์Šค ์‚ฌ์šฉ ์‹œ ์ฃผ์˜์ .

  • โ†˜๏ธŽ ๊ณผ๋„ํ•œ ์ธ๋ฑ์Šค ์ƒ์„ฑ : ๋„ˆ๋ฌด ๋งŽ์€ ์ธ๋ฑ์Šค๋Š” ์“ฐ๊ธฐ ์„ฑ๋Šฅ์„ ์ €ํ•˜์‹œํ‚ค๊ณ , ์ถ”๊ฐ€์ ์ธ ์ €์žฅ ๊ณต๊ฐ„์„ ์ฐจ์ง€ํ•จ.
  • โ†˜๏ธŽ ๋ถˆํ•„์š”ํ•œ ์ธ๋ฑ์Šค : ์ž์ฃผ ์‚ฌ์šฉ๋˜์ง€ ์•Š๋Š” ์ธ๋ฑ์Šค๋Š” ์˜คํžˆ๋ ค ์„ฑ๋Šฅ ์ €ํ•˜๋ฅผ ์œ ๋ฐœํ•  ์ˆ˜ ์žˆ์Œ.
  • โ†˜๏ธŽ ์ธ๋ฑ์Šค ํžˆํŠธ ๋น„์œจ(Index Hit Rate) : ์ฟผ๋ฆฌ๊ฐ€ ์ธ๋ฑ์Šค๋ฅผ ์ œ๋Œ€๋กœ ํ™œ์šฉํ•˜๋Š”์ง€ ๋ชจ๋‹ˆํ„ฐ๋งํ•ด์•ผ ํ•จ.