Mysql์˜ Select Count(*) ์–ผ๋งˆ๋‚˜ ๋น ๋ฅผ๊นŒ?

์‚ฌ์šฉํ•˜๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์—”์ง„์— ๋”ฐ๋ผ ๋‹ค๋ฅด๋‹ค.

  • InnoDB : O(n)
  • MyISAM : O(1)

์ด์œ ๋Š” MyISAM์˜ ๊ฒฝ์šฐ row๊ฐ€ ๋ช‡ ๊ฐœ ์žˆ๋Š”์ง€ ์ €์žฅํ•˜๊ณ  ์žˆ๊ธฐ์— Count(*) ์ฟผ๋ฆฌ๊ฐ€ ๋“ค์–ด์˜ค๋ฉด ๊ทธ ์ €์žฅํ•œ ๊ฐ’์„ ๋ฐ”๋กœ ๋ฆฌํ„ดํ•˜์—ฌ O(1)์ด ๋œ๋‹ค.


Count(*) vs Count(column) vs Count(*) Where

Count(*)์ด ๊ฐ€์žฅ ๋น ๋ฅด๋‹ค.

์ด์œ ๋Š” Count(*) ์˜ ๊ฒฝ์šฐ ๋‚ด๋ถ€์ ์œผ๋กœ ๊ฐ’์„ ํ™•์ธํ•˜์ง€ ์•Š๊ณ , row์˜ ๊ฐœ์ˆ˜๋งŒ ํ™•์ธํ•œ๋‹ค.

ํ•˜์ง€๋งŒ column์„ ์ฃผ๋ฉด ๋‚ด๋ถ€์ ์œผ๋กœ ๊ฐ’์„ ํ™•์ธํ•˜์—ฌ null์ด ์•„๋‹Œ row๋งŒ ๋ฆฌํ„ดํ•˜๊ธฐ์— ์‹œ๊ฐ„์ด ์ข€ ๋” ๊ฑธ๋ฆฌ๊ฒŒ ๋œ๋‹ค.

where ์กฐ๊ฑด๋„ ๋งˆ์ฐฌ๊ฐ€์ง€๋‹ค. ๊ฐ’์„ ํ™•์ธํ•œ๋‹ค์Œ ์กฐ๊ฑด์— ๋งž๋Š” ๊ฒƒ๋งŒ ์„ธ๊ธฐ์— Count(*)๋ณด๋‹ค ๋А๋ฆฌ๋‹ค.

์ด๋Š” MyISAM ์—”์ง„์—์„œ๋„ ๋งˆ์ฐฌ๊ฐ€์ง€๋‹ค. MyISAM ์—”์ง„์—์„œ ๋น ๋ฅธ ๊ฒƒ์€ SELECT COUNT(*)์ด๊ณ  Count(column)์ด๋‚˜ Count(*) Where ์€ InnoDB์™€ ๋งˆ์ฐฌ๊ฐ€์ง€๋กœ ๊ฐ’์„ ์ฝ๊ณ  ๋ฆฌํ„ดํ•˜๊ธฐ์— ์‹œ๊ฐ„์ด ์˜ค๋ž˜ ๊ฑธ๋ฆฐ๋‹ค.


์ •๋ฆฌ

MyISAMInnoDB
Count(*)๋น ๋ฆ„๋ณดํ†ต
Count(col)๋А๋ฆผ๋А๋ฆผ
Count(*) Where๋А๋ฆผ๋А๋ฆผ

์ ˆ๋Œ€์ ์œผ๋กœ ๋น ๋ฆ„, ๋А๋ฆผ์„ ์˜๋ฏธํ•˜๋Š”๊ฒŒ ์•„๋‹Œ ์ƒ๋Œ€์ ์ธ ์†๋„๋ฅผ ์˜๋ฏธ.


์ถœ์ฒ˜

https://stackoverflow.com/questions/5257973/mysql-complexity-of-select-count-from-mytable

https://m.blog.naver.com/pjt3591oo/221030483713

https://stackoverflow.com/questions/22352073/improve-innodb-count-performance

https://imnkj.tistory.com/49