When Forum Page Loads Are Slow: Checking Index Effectiveness Using SQLite Execution Plans
κ²μνμ κΈμ΄ μμ΄λ©΄μ λͺ©λ‘μ΄ λλ €μ§λ©΄ λ°μ΄ν°λ² μ΄μ€ μ νμ λ°κΎΈκ±°λ μλ²λ₯Ό λλ €μΌ νλ€κ³ μκ°νκΈ° μ½μ΅λλ€. κ·Έλ¬λ λ¨Όμ κ°μ 쑰건μ μ‘°νκ° λ¬΄μμ μ½κ³ μ΄λ»κ² μ λ ¬νλμ§ νμΈν΄μΌ ν©λλ€. ν νμ΄μ§μ νμλλ κΈμ΄ μ μ΄λ κ·Έ κΈμ μ°Ύλ κ³Όμ μμ λ§μ λ°μ΄ν°λ₯Ό λ³Ό μ μμ΅λλ€.
μ΄ κΈμ μ΄μ λ°μ΄ν°λ² μ΄μ€λ₯Ό 건λλ¦¬μ§ μκ³ λ©λͺ¨λ¦¬ μμ κ°μ κ²μκΈ 1λ§ κ±΄μ λ§λλλ€. μΉ΄ν κ³ λ¦¬λ³ μ΅μ κΈ 10κ°λ₯Ό μ‘°ννλ SQLμ κ³ μ νκ³ μΈλ±μ€ μ νμ μ€ν κ³νκ³Ό κ²°κ³Όλ₯Ό λΉκ΅ν©λλ€. μ€μ μ¬μ΄νΈκ° PostgreSQLμ΄λ MySQLμ μ¬μ©νλ€λ©΄ μλ SQLite κ²°κ³Όλ₯Ό κ·Έλλ‘ μ±λ₯ μμΉλ‘ μ μ©νμ§ λ§μΈμ.
2. μ€ν κ³νκ³Ό μ€ν μκ°μ λ€λ¦ λλ€
EXPLAIN QUERY PLANμ SQLiteκ° μ‘°νλ₯Ό μ²λ¦¬ν κ²½λ‘λ₯Ό μ€λͺ ν©λλ€. SCANμ μ 체 μ€μΊμ, SEARCHλ 쑰건μ μ΄μ©ν νμμ λνλ΄λ ννμΌλ‘ λ³Ό μ μμ΅λλ€. λ€λ§ νν λ¬Έμμ΄κ³Ό μΆλ ₯ νμμ λ²μ μ λ°λΌ λ¬λΌμ§ μ μμ΅λλ€. λ¬Έμμ΄μ κ³ μ ν΄ νλ‘κ·Έλ¨ λ‘μ§μ λ§λλ κ²λ³΄λ€ μ§λ¨ μλ£λ‘ μ¬μ©νλ νΈμ΄ μ ν©ν©λλ€.
μ€ν κ³νμ΄ λ°λμλ€κ³ 무쑰건 μ²΄κ° μλκ° λͺ λ°° μ’μμ‘λ€κ³ λ§ν μλ μμ΅λλ€. λ°μ΄ν° κ·λͺ¨, μΊμ μν, μ μ₯ μ₯μΉ, λμ μμ²μ΄ λ€λ¦ λλ€. μ΄λ² μ€μ΅μμλ μ€μ λ‘ νμΈν κ³ν λ³νμ κ²°κ³Ό μΌμΉλ§ κΈ°λ‘ν©λλ€. μκ° μμΉλ₯Ό μ£Όμ₯νλ €λ©΄ κ°μ 쑰건μμ λ°λ³΅ μΈ‘μ ν λ°©λ²κ³Ό νΈμ°¨λ ν¨κ» μ μν΄μΌ ν©λλ€.
3. μ‘°ν 쑰건μ λ§λ λ³΅ν© μΈλ±μ€λ₯Ό μκ°ν©λλ€
μμ λ categoryκ° itμΈ νμ μ°Ύκ³ idλ₯Ό λ΄λ¦Όμ°¨μμΌλ‘ μ λ ¬ν λ€ 10κ°λ₯Ό κ°μ Έμ΅λλ€. categoryμ idλ₯Ό ν¬ν¨ν λ³΅ν© μΈλ±μ€κ° μ΄ μ‘°νμ μ΄λ€ μν₯μ μ£Όλμ§ νμΈν©λλ€. μΈλ±μ€ μ€κ³λ μ΄ μ΄λ¦λ§ λ³΄κ³ νλ κ²μ΄ μλλΌ μμ£Ό μ¬μ©νλ WHEREμ ORDER BYλ₯Ό ν¨κ» μ΄ν΄λ³΄λ μμ μ λλ€.
μ€μ κ²μνμ΄ created_atμΌλ‘ μ λ ¬νκ±°λ μμ½ λ°ν, 곡μ§, κ³΅κ° λ²μλ₯Ό νν°λ§νλ€λ©΄ μΏΌλ¦¬κ° λ¬λΌμ§λλ€. μμ μ μΈλ±μ€λ₯Ό κ·Έ μ¬μ΄νΈμ λ°λ‘ μΆκ°νμ§ λ§μΈμ. κΈ°μ‘΄ μΈλ±μ€μ μ‘°ν 쑰건μ νμΈνκ³ ν보 νλλ₯Ό κ²μ¦μ© νκ²½μμ λΉκ΅ν©λλ€. λΉμ·ν μ΄λ¦μ μ€λ³΅ μΈλ±μ€λ₯Ό μ¬λ¬ κ° λ§λλ κ²μ κ΄λ¦¬ λΉμ©μ λ릴 μ μμ΅λλ€.
4. κ²°κ³Όλ₯Ό λ¨Όμ κ³ μ ν λ€ κ³νμ λΉκ΅ν©λλ€
μλ μ½λλ μ ν΄μ§ κ·μΉμΌλ‘ 1λ§ κ°μ νμ μ½μ ν©λλ€. 맀 μ΄ λ²μ§Έ νλ§ itλ‘ μ§μ ν΄ κ²°κ³Όλ₯Ό μ¬ννκΈ° μ½κ² λ§λ€μμ΅λλ€. μΈλ±μ€λ₯Ό μΆκ°νκΈ° μ κ²°κ³Όλ₯Ό 보κ΄νκ³ , μΆκ° ν κ°μ κ²°κ³ΌμΈμ§ assertλ‘ νμΈν©λλ€. μ΄ κ²μ¬λ λΉ λ₯Έ μ‘°νκ° μ¬λ°λ₯Έ μ‘°νμ΄κΈ°λ νμ§ νμΈνλ μ΅μ μ μ°¨μ λλ€.
μ‘°ν κ°μ SQL λ¬Έμμ΄μ μ§μ μ°κ²°νμ§ μκ³ λ§€κ°λ³μλ‘ μ λ¬ν©λλ€. μμ μμ μ λ ₯μ κ³ μ λΌ μμ§λ§ μ€μ κ²μ 쑰건μμλ κ°μ μ΅κ΄μ μ μ§νλ νΈμ΄ μ’μ΅λλ€. in-memory λ°μ΄ν°λ² μ΄μ€λ νλ‘κ·Έλ¨μ΄ λλλ©΄ μ¬λΌμ§λλ€. λ‘컬μ μ€μ DB νμΌμ μ€μ΅ λμκ³Ό νΌλνμ§ μλλ‘ μ€ν μ μ μ°κ²° λ¬Έμμ΄μ νμΈνμΈμ.
5. μΈλ±μ€λ₯Ό λ리면 μκΈ°λ λΉμ©λ μμ΅λλ€
μΈλ±μ€λ μ½κΈ° μμ μ λμ§λ§ μ½μ κ³Ό μμ λλ κ΄λ¦¬ν΄μΌ ν©λλ€. κΈμ μμ£Ό μμ§νλ μ¬μ΄νΈμμλ μ°κΈ°λκ³Ό μΈλ±μ€ μ μ₯ 곡κ°μ ν¨κ» κ³ λ €ν΄μΌ ν©λλ€. μ‘°νκ° λ리λ€λ μ΄μ λ§μΌλ‘ λͺ¨λ μ΄μ μΈλ±μ€λ₯Ό μΆκ°νλ©΄ μ°κΈ°μ μ μ§ κ΄λ¦¬μ λΆλ΄μ΄ μκΈΈ μ μμ΅λλ€.
νΉν λ³Έλ¬Έ κ²μ, μΌλΆ λ¬Έμμ΄ κ²μ, λ€λ₯Έ μ λ ¬ κΈ°μ€μ κ°μ μΈλ±μ€μ ν¨κ³Όλ₯Ό κΈ°λνκΈ° μ΄λ ΅μ΅λλ€. ν¨μ μ μ©μ΄λ 쑰건 ννλ μ€μν©λλ€. μ΄λ² μμ μ category λλ± μ‘°κ±΄κ³Ό μΌλ° μ λͺ© κ²μμ λ€λ₯Έ λ¬Έμ μ λλ€. λͺ©λ‘ μ‘°νλ₯Ό κ°μ ν κ²°κ³Όλ₯Ό μ 체 κ²μ μ±λ₯ κ°μ μΌλ‘ νλν΄ μ€λͺ νμ§ λ§μΈμ.
6. μ΄μμ μ μ©νκΈ° μ μ νμΈν νλͺ©
μ€μ μ‘°ν SQLκ³Ό μ€ν κ³νμ ν보νκ³ , μμ£Ό μ¬μ©νλ 쑰건과 λλ¦° 쑰건μ λ°λ‘ λΉκ΅ν©λλ€. λ°μ΄ν°κ° μ μ κ°λ° νκ²½μμλ§ μννλ©΄ μ΄μ κ·λͺ¨μ λΉμ©μ λμΉ μ μμ΅λλ€. κ°μΈμ 보λ₯Ό μ κ±°ν λν λ°μ΄ν°λ λ³λ μν νκ²½μ μ¬μ©νκ³ κ²°κ³Ό νμ μμμ κ³΅κ° λ²μλ νμΈνμΈμ.
λ³κ²½μ κΈ°μ‘΄ μΈλ±μ€ λͺ©λ‘κ³Ό λλ릴 λ°©λ²μ κΈ°λ‘ν λ€ μ§νν©λλ€. μ¬μ© μ€μΈ DB μ νμ λ°λΌ μΈλ±μ€ μμ± μ€μ μ κΈμ΄λ λΆνκ° λ€λ₯Ό μ μμ΅λλ€. SQLite μ€μ΅λ§μΌλ‘ κ·Έ μ΄μ μν₯μ νλ¨ν μλ μμ΅λλ€. μ± λ΄λΆ μΊμμ DB μΈλ±μ€μ ν¨κ³Όλ ꡬλΆν΄ μΈ‘μ ν΄μΌ μ€μ λ³λͺ©μ΄ λ¨μ μλμ§ μ μ μμ΅λλ€.
7. μ‘°ν 쑰건μ λ°κΎΈμ΄ νκ³λ₯Ό νμΈνκΈ°
μμ μμ λ°ν μ΄μ id λμ titleλ‘ λ°κΎΈκ³ κ³νμ΄ μ΄λ»κ² λ¬λΌμ§λμ§ μ΄ν΄λ³Ό μ μμ΅λλ€. μΈλ±μ€κ° νμμ μ¬μ©λΌλ νμν λ°μ΄ν°λ₯Ό μ»λ κ²½λ‘λ λ¬λΌμ§ μ μμ΅λλ€. μ΄ λ³νλ κ²°κ³Όκ° νλ Έλ€λ λ»μ΄ μλλΌ λΉμ©μ μ½μ νλͺ©μ΄ λ¬λΌμ‘λ€λ λ»μ λλ€. μλ³Έ μμ μ covering κ²°κ³Όλ₯Ό λͺ¨λ λ°ν μ΄μ νλνμ§ λ§μΈμ.
μΉ΄ν κ³ λ¦¬κ° freeμΈ κ²½μ°λ λΉκ΅ν©λλ€. λ°μ΄ν° λΆν¬κ° λ€λ₯΄λ©΄ μ νν μ‘°κ±΄μ΄ μ 체 νμ μ΄λ μ λλ₯Ό ν¬ν¨νλμ§ λ¬λΌμ§λλ€. κ°μ SQLμ΄λΌκ³ λͺ¨λ κ°μμ λΉμ©μ΄ κ°λ€κ³ 보기 μ΄λ ΅μ΅λλ€. μ΄μ νκ²½μμλ λ°©λ¬Έμκ° μμ£Ό μ ννλ κ²μνκ³Ό λλ¬Έ κ²μνμ ꡬλΆν΄ μ κ²νλ©΄ κ°μ μ ν¨κ³Όλ₯Ό λ νμ€μ μΌλ‘ μ΄ν΄ν μ μμ΅λλ€.
νμ΄μ§ λ²νΈκ° μ»€μ§ λμ λͺ©λ‘ μ‘°νλ λ³λ μ£Όμ μ λλ€. μ΄λ² SQLμ μ΅μ 10κ°λ§ κ°μ Έμ€λ©° μμ£Ό κΉμ OFFSET μ‘°νλ₯Ό μννμ§ μμ΅λλ€. μ΅μ κΈ μ‘°νκ° κ°μ λλ€λ κ²°κ³Όλ₯Ό μ€λλ λͺ¨λ λͺ©λ‘μ μλ κ°μ μΌλ‘ μ€λͺ νμ§ λ§μΈμ. μ€μ μλΉμ€κ° μ¬μ©νλ νμ΄μ§ λ°©μκ³Ό νμ© λ²μλ₯Ό ν¨κ» νμΈν΄μΌ ν©λλ€. κ²μκ³Ό μ λ ¬ μ΅μ λ κ°μ μ΄μ λ‘ κ΅¬λΆν©λλ€.
μΈ‘μ κΈ°λ‘μλ SQLite λ²μ , ν μ, λ°μ΄ν° μμ± κ·μΉ, μ‘°ν 쑰건과 λ°ν μ΄μ μ μ΅λλ€. μ μ© μ νμ κ³νκ³Ό κ²°κ³Ό κ°μλ μ μ₯νμΈμ. μ€ν μκ° μΈ‘μ μ μΆκ°νλ€λ©΄ μ€λΉμ μ½μ μκ°μ μ μΈνκ³ μ‘°ν ꡬκ°μ λ°λ³΅ν΄μ μΈ‘μ ν΄μΌ ν©λλ€. κ²°κ³Όλ₯Ό ν λ² μ½μ λ€μ μΊμ ν¨κ³Όλ κ³ λ €νκ³ μΈ‘μ νκ²½μ 곡κ°νλ©΄ λ€λ₯Έ μ¬λμ΄ λΉκ΅νκΈ° μ¬μμ§λλ€.
μΈλ±μ€ ν보λ₯Ό μ΄μμ μ μν λλ μ½κΈ° κ°μ λΏ μλλΌ κΈ μ½μ κ³Ό μμ λΉλλ₯Ό μ μ΅λλ€. λ°μ΄ν°λ² μ΄μ€μ κ°ν΄μ§λ μ€μ λΆνλ₯Ό μμ§ λͺ»νλ©΄ λ¨μ μ μΈ λΉμ© μ κ°μ μ£Όμ₯νμ§ μμ΅λλ€. μ΄λ²μ²λΌ μκ² μ¬νν μ¬λ‘λ κ°μ€μ νμΈνλ μΆλ°μ μ΄λ©° μ΄μ λ³κ²½μ μ΅μ’ κ²μ¦μ μλλλ€. κ²°κ³Όμ λ²μλ₯Ό μ ννκ² μ λ¬νλ νΈμ΄ μ μ§ κ΄λ¦¬μ λμμ΄ λ©λλ€.
8. λ°λΌ νλ μμ
λͺ λ Ήκ³Ό μ½λλ νμ΅μ©μ λλ€. μ€λͺ μ νμλ μ€ν νκ²½μ ꡬλΆνκ³ μ μν ν΄λ λλ λͺ¨μ μ λ ₯μΌλ‘ μμνμΈμ.
μμ μ½λ
import sqlite3
con = sqlite3.connect(':memory:')
con.execute('CREATE TABLE posts(id INTEGER PRIMARY KEY, category TEXT, title TEXT)')
con.executemany('INSERT INTO posts(category,title) VALUES (?,?)',
[('it' if i % 10 == 0 else 'free', f'post {i}') for i in range(10000)])
sql = 'SELECT id FROM posts WHERE category=? ORDER BY id DESC LIMIT 10'
args = ('it',)
print('before:', con.execute('EXPLAIN QUERY PLAN ' + sql, args).fetchall())
expected = con.execute(sql, args).fetchall()
con.execute('CREATE INDEX idx_posts_category_id ON posts(category,id)')
print('after:', con.execute('EXPLAIN QUERY PLAN ' + sql, args).fetchall())
assert con.execute(sql, args).fetchall() == expected
con.close()9. κ²μ¦ κ²°κ³Όμ ν΄μ
| νμΈ νλͺ© | κ²°κ³Ό λλ κ²μ¦ λ²μ |
|---|---|
| ν μ | 10,000 |
| μ μ© μ | SCAN posts |
| μ μ© ν | SEARCH posts USING COVERING INDEX idx_posts_category_id (category=?) |
| μ‘°ν κ²°κ³Ό | μ μ© μ ν λμΌ |
| μλ λ°°μ | μΈ‘μ Β·μ£Όμ₯νμ§ μμ |
Python 3.12 λ΄μ₯ SQLiteλ‘ μ€ν κ³νκ³Ό κ²°κ³Ό μΌμΉλ₯Ό νμΈνμ΅λλ€. μ΄μ DBμ λμ μ μ μν©μ μλ΅ μκ°μ μΈ‘μ ν κ²°κ³Όλ μλλλ€.
10. μμ£Ό 묻λ μ§λ¬Έ
SCANμ΄ λμ€λ©΄ νμ λμκ°μ?
μλλλ€. μμ ν μ΄λΈμ΄λ λ§μ νμ μ½λ 쿼리μμλ μ μ ν μ νμΌ μ μμ΅λλ€. μ€μ λΉμ©κ³Ό λ°ν 쑰건μ ν¨κ» νλ¨ν©λλ€.
COVERING INDEXλ 무μμΈκ°μ?
μ΄λ² μ‘°νμ νμν categoryμ idλ₯Ό μΈλ±μ€μμ μ²λ¦¬ν μ μλ κ³νμ λλ€. λ³Έλ¬Έ λ± λ€λ₯Έ μ΄μ μΆκ°νλ©΄ κ°μ κ³νμ κΈ°λν μ μμ΅λλ€.
μ€μ μ¬μ΄νΈμ κ·Έλλ‘ μΆκ°ν΄λ λλμ?
μ¬μ© DB, 쿼리, κΈ°μ‘΄ μΈλ±μ€μ μμ± μν₯μ νμΈν΄μΌ ν©λλ€. μ΄ κΈμ νμ΅μ© SQLite μμ μ λλ€.
11. 곡μ μ°Έκ³ μλ£
λμ μ μμ μ΅μ μ μλ μ μμ λ¬Έμμμ νμΈν μ μμ΅λλ€. λ³Έλ¬Έμ μ¬λ‘μ μμ λ νμ΅μ μν΄ λ 립μ μΌλ‘ ꡬμ±νμ΅λλ€.
μμ μ λ ₯μ νμ΅μ© λͺ¨μ μλ£μ λλ€. μ€μ νκ²½μ μ μ©ν λλ λ³Έλ¬Έμ μλ΄ν κ²μ¦ λ²μμ 쑰건μ νμΈνμΈμ.
IT Board
π¬ 0 Comments