Android SQLite 데이터베이스 실전 활용 — JOIN, LIKE 검색, UPSERT와 Coroutine 비동기 처리

Android 앱을 개발하다 보면 서버 없이 기기 내부에 데이터를 저장해야 하는 경우가 있습니다.

대표적으로 도감 앱, 오프라인 사전, 일정 관리 앱, 메모 앱, 즐겨찾기 기능 등이 있습니다.

Android에서는 Room을 사용하는 방법도 있지만, 기존 프로젝트에서 SQLiteOpenHelper와 SQLiteDatabase를 직접 사용하고 있다면 반드시 Room으로 변경할 필요는 없습니다.

다만 SQLite를 직접 사용할 때는 몇 가지 문제가 자주 발생합니다.

데이터가 많아질수록 검색 속도가 느려짐
여러 테이블을 JOIN할 때 결과가 중복됨
LIKE 검색에서 %, _ 문자가 의도하지 않게 처리됨
좋아요 상태를 저장할 때 INSERT와 UPDATE를 반복해야 함
데이터베이스 조회 때문에 화면이 멈추는 현상 발생
앱 업데이트 후 데이터베이스 스키마가 변경되면서 오류 발생
assets에 포함된 SQLite 파일과 사용자 데이터 관리가 복잡해짐

이번 글에서는 Kotlin + SQLiteOpenHelper + Coroutines + ViewModel 구조를 기준으로 이러한 문제를 해결하는 방법을 정리하겠습니다.

작성 기준: 2026년 10월

1. Android SQLite 직접 사용 구조

SQLite는 Android에 기본적으로 포함된 관계형 데이터베이스입니다.

별도의 서버 없이 기기 내부에 데이터를 저장하고 SQL을 이용해 조회할 수 있습니다.

예를 들어 조류 정보 앱을 개발한다고 가정하겠습니다.

기본 조류 정보는 korean_birds 테이블에 저장하고, 사용자가 작성한 메모와 좋아요 정보는 bird_memos 테이블에 저장합니다.

Android App
    │
    ├── Activity / Fragment
    │       │
    │       ▼
    ├── ViewModel
    │       │
    │       ▼
    ├── Repository
    │       │
    │       ▼
    └── SQLiteOpenHelper
            │
            ▼
        SQLite Database
            │
            ├── korean_birds
            │
            └── bird_memos

이 구조에서는 UI에서 SQL을 직접 실행하지 않습니다.

ViewModel은 Repository를 호출하고, Repository는 SQLite 데이터베이스를 조회합니다.

이렇게 구성하면 데이터베이스 관련 코드가 Activity나 Fragment에 분산되는 문제를 줄일 수 있습니다.

2. 테이블 구조 설계

먼저 예제에서 사용할 두 개의 테이블을 정의하겠습니다.

조류 기본 정보 테이블

CREATE TABLE korean_birds (
    KTSN INTEGER PRIMARY KEY,
    Species TEXT NOT NULL,
    SpeciesKor TEXT,
    OrderKor TEXT,
    FamilyKor TEXT,
    GenusKor TEXT
);

KTSN은 각 조류 데이터를 구분하는 고유 ID입니다.

사용자 메모 테이블

CREATE TABLE bird_memos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    bird_ktsn INTEGER NOT NULL UNIQUE,
    is_like INTEGER NOT NULL DEFAULT 0,
    is_watching INTEGER NOT NULL DEFAULT 0,
    memo TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL,
    FOREIGN KEY (bird_ktsn)
        REFERENCES korean_birds(KTSN)
);

사용자 메모 테이블은 조류 정보 테이블과 bird_ktsn으로 연결합니다.

bird_ktsn에 UNIQUE 제약 조건을 적용한 이유는 조류 한 종류당 메모 레코드를 하나만 관리하기 위해서입니다.

여기서는 고유 ID인 KTSN을 사용하지만, 기존 데이터베이스가 Species를 기준으로 연결하도록 설계되어 있다면 해당 구조에 맞춰 수정해야 합니다.

다만 학명이나 이름이 변경될 가능성이 있으므로 장기적으로는 고유 ID 기반 연결이 더 안전합니다.

3. SQLiteOpenHelper 구현하기

Android에서 SQLite를 직접 관리할 때는 SQLiteOpenHelper를 사용할 수 있습니다.

class BirdDatabaseHelper(
    context: Context
) : SQLiteOpenHelper(
    context,
    DATABASE_NAME,
    null,
    DATABASE_VERSION
) {

    companion object {
        private const val DATABASE_NAME = "birds.db"
        private const val DATABASE_VERSION = 1
    }

    override fun onCreate(db: SQLiteDatabase) {

        db.execSQL(
            """
            CREATE TABLE korean_birds (
                KTSN INTEGER PRIMARY KEY,
                Species TEXT NOT NULL,
                SpeciesKor TEXT,
                OrderKor TEXT,
                FamilyKor TEXT,
                GenusKor TEXT
            )
            """.trimIndent()
        )

        db.execSQL(
            """
            CREATE TABLE bird_memos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                bird_ktsn INTEGER NOT NULL UNIQUE,
                is_like INTEGER NOT NULL DEFAULT 0,
                is_watching INTEGER NOT NULL DEFAULT 0,
                memo TEXT NOT NULL DEFAULT '',
                created_at INTEGER NOT NULL,
                FOREIGN KEY (bird_ktsn)
                    REFERENCES korean_birds(KTSN)
            )
            """.trimIndent()
        )
    }

    override fun onUpgrade(
        db: SQLiteDatabase,
        oldVersion: Int,
        newVersion: Int
    ) {
        // 버전별 마이그레이션 처리
    }

    override fun onConfigure(db: SQLiteDatabase) {
        super.onConfigure(db)
        db.setForeignKeyConstraintsEnabled(true)
    }
}

onCreate()는 데이터베이스가 처음 생성될 때 실행됩니다.

onUpgrade()는 데이터베이스 버전이 증가했을 때 실행됩니다.

중요한 점은 사용자 데이터를 저장하는 앱에서는 업데이트 시 테이블을 무조건 삭제하면 안 된다는 것입니다.

예를 들어 다음 코드는 개발 중에는 편리할 수 있지만 실제 서비스 앱에서는 위험합니다.

db.execSQL("DROP TABLE IF EXISTS bird_memos")
onCreate(db)

이 코드를 실행하면 사용자가 작성한 메모와 좋아요 정보가 삭제됩니다.

실제 서비스에서는 스키마 변경에 맞는 마이그레이션 SQL을 작성해야 합니다.

4. JOIN으로 기본 정보와 사용자 데이터 함께 조회하기

도감 앱에서는 기본 조류 정보와 사용자가 저장한 좋아요 상태를 함께 표시해야 할 수 있습니다.

이 경우 LEFT JOIN을 사용하면 편리합니다.

SELECT
    b.KTSN,
    b.Species,
    b.SpeciesKor,
    b.OrderKor,
    b.FamilyKor,
    b.GenusKor,
    COALESCE(m.is_like, 0) AS is_like,
    COALESCE(m.is_watching, 0) AS is_watching,
    COALESCE(m.memo, '') AS memo
FROM korean_birds b
LEFT JOIN bird_memos m
    ON b.KTSN = m.bird_ktsn
ORDER BY b.SpeciesKor;

여기서 중요한 부분은 LEFT JOIN입니다.

INNER JOIN을 사용하면 사용자 메모가 존재하는 조류만 조회될 수 있습니다.

반면 LEFT JOIN을 사용하면 메모가 없는 조류도 결과에 포함됩니다.

COALESCE 사용 이유

사용자 메모가 없는 경우 m.is_like는 NULL이 될 수 있습니다.

COALESCE(m.is_like, 0)

이 표현식은 값이 NULL이면 0을 반환합니다.

따라서 Kotlin 코드에서 매번 NULL 여부를 확인하는 부담을 줄일 수 있습니다.

5. Kotlin에서 JOIN 결과 조회하기

먼저 결과를 담을 데이터 클래스를 정의합니다.

data class BirdInfo(
    val ktsn: Long,
    val species: String,
    val speciesKor: String,
    val isLiked: Boolean,
    val isWatching: Boolean,
    val memo: String
)

Repository에서는 다음과 같이 조회할 수 있습니다.

class BirdRepository(
    private val dbHelper: BirdDatabaseHelper
) {

    fun getAllBirds(): List<BirdInfo> {

        val result = mutableListOf<BirdInfo>()
        val db = dbHelper.readableDatabase

        val sql = """
            SELECT
                b.KTSN,
                b.Species,
                b.SpeciesKor,
                COALESCE(m.is_like, 0) AS is_like,
                COALESCE(m.is_watching, 0) AS is_watching,
                COALESCE(m.memo, '') AS memo
            FROM korean_birds b
            LEFT JOIN bird_memos m
                ON b.KTSN = m.bird_ktsn
            ORDER BY b.SpeciesKor
        """.trimIndent()

        db.rawQuery(sql, null).use { cursor ->

            val ktsnIndex =
                cursor.getColumnIndexOrThrow("KTSN")

            val speciesIndex =
                cursor.getColumnIndexOrThrow("Species")

            val nameIndex =
                cursor.getColumnIndexOrThrow("SpeciesKor")

            val likeIndex =
                cursor.getColumnIndexOrThrow("is_like")

            val watchingIndex =
                cursor.getColumnIndexOrThrow("is_watching")

            val memoIndex =
                cursor.getColumnIndexOrThrow("memo")

            while (cursor.moveToNext()) {

                result.add(
                    BirdInfo(
                        ktsn = cursor.getLong(ktsnIndex),
                        species = cursor.getString(speciesIndex),
                        speciesKor = cursor.getString(nameIndex) ?: "",
                        isLiked = cursor.getInt(likeIndex) == 1,
                        isWatching =
                            cursor.getInt(watchingIndex) == 1,
                        memo = cursor.getString(memoIndex) ?: ""
                    )
                )
            }
        }

        return result
    }
}

여기서는 use {}를 사용해 Cursor를 자동으로 닫습니다.

SQLite를 직접 사용할 때 Cursor를 닫지 않으면 리소스 누수 문제가 발생할 수 있습니다.

또한 getColumnIndexOrThrow()를 사용하면 필요한 컬럼이 존재하지 않을 때 문제를 빠르게 확인할 수 있습니다.

6. LIKE를 이용한 부분 검색

사용자가 검색창에 조류 이름을 입력하면 이름이나 학명에 해당 문자열이 포함된 데이터를 조회할 수 있습니다.

예를 들어 참새를 검색한다고 가정하겠습니다.

SELECT *
FROM korean_birds
WHERE SpeciesKor LIKE '%참새%';

하지만 실제 앱에서는 검색어를 SQL 문자열에 직접 연결하지 않는 것이 좋습니다.

다음과 같은 코드는 피해야 합니다.

val sql = """
    SELECT *
    FROM korean_birds
    WHERE SpeciesKor LIKE '%$keyword%'
"""

검색어에 특수문자가 포함되면 SQL 오류가 발생할 수 있고, SQL Injection 문제도 생길 수 있습니다.

대신 바인딩 인자를 사용합니다.

val sql = """
    SELECT *
    FROM korean_birds
    WHERE SpeciesKor LIKE ?
"""

val cursor = db.rawQuery(
    sql,
    arrayOf("%$keyword%")
)

이렇게 하면 검색어가 SQL 문법으로 해석되는 문제를 줄일 수 있습니다.

다만 바인딩 인자를 사용하더라도 LIKE의 와일드카드 문자는 별도로 처리해야 합니다.

7. LIKE 검색에서 %, _ 문자 처리하기

SQLite의 LIKE에서는 다음 문자가 특별한 의미를 가집니다.

문자	의미
`%`	길이에 관계없이 여러 문자
`_`	임의의 한 문자

예를 들어 사용자가 %를 검색했을 때 모든 데이터가 검색되는 문제가 발생할 수 있습니다.

사용자가 입력한 문자열 자체를 검색하려면 이스케이프 처리가 필요합니다.

fun escapeLike(keyword: String): String {
    return keyword
        .replace("\\", "\\\\")
        .replace("%", "\\%")
        .replace("_", "\\_")
}

SQL에서는 ESCAPE 절을 사용합니다.

val escaped = escapeLike(keyword)

val sql = """
    SELECT *
    FROM korean_birds
    WHERE SpeciesKor LIKE ? ESCAPE '\'
"""

val cursor = db.rawQuery(
    sql,
    arrayOf("%$escaped%")
)

이렇게 하면 사용자가 입력한 %, _를 와일드카드가 아닌 일반 문자로 검색할 수 있습니다.

8. 여러 검색 조건을 동적으로 조합하기

도감 앱에서는 이름 검색뿐 아니라 목(Order), 과(Family), 속(Genus)을 조합한 필터 기능을 사용할 수 있습니다.

예를 들어 다음과 같은 조건입니다.

이름에 참새 포함
목은 참새목
과는 참새과

이때 조건이 선택되지 않았는데도 모든 조건을 SQL에 넣는 방식은 비효율적입니다.

동적으로 WHERE 절을 구성하는 것이 좋습니다.

fun searchBirds(
    keyword: String?,
    order: String?,
    family: String?,
    genus: String?
): Cursor {

    val conditions = mutableListOf<String>()
    val args = mutableListOf<String>()

    if (!keyword.isNullOrBlank()) {
        conditions.add(
            "(SpeciesKor LIKE ? ESCAPE '\\' OR Species LIKE ? ESCAPE '\\')"
        )

        val pattern = "%${escapeLike(keyword)}%"
        args.add(pattern)
        args.add(pattern)
    }

    if (!order.isNullOrBlank()) {
        conditions.add("OrderKor = ?")
        args.add(order)
    }

    if (!family.isNullOrBlank()) {
        conditions.add("FamilyKor = ?")
        args.add(family)
    }

    if (!genus.isNullOrBlank()) {
        conditions.add("GenusKor = ?")
        args.add(genus)
    }

    val whereClause = if (conditions.isEmpty()) {
        ""
    } else {
        " WHERE " + conditions.joinToString(" AND ")
    }

    val sql = """
        SELECT *
        FROM korean_birds
        $whereClause
        ORDER BY SpeciesKor
    """.trimIndent()

    return dbHelper.readableDatabase.rawQuery(
        sql,
        args.toTypedArray()
    )
}

이 함수는 Repository 내부에 작성하는 예시입니다.

호출하는 쪽에서는 반환된 Cursor를 반드시 닫아야 합니다.

실제 프로젝트에서는 Cursor를 외부로 반환하기보다 Repository 내부에서 데이터 클래스로 변환한 후 반환하는 방식을 추천합니다.

9. DISTINCT를 이용한 Spinner 필터 구성

3단계 Spinner를 구현할 때는 중복되지 않은 목, 과, 속 목록이 필요합니다.

예를 들어 목 목록을 조회하려면 다음과 같이 작성합니다.

SELECT DISTINCT OrderKor
FROM korean_birds
WHERE OrderKor IS NOT NULL
ORDER BY OrderKor;

특정 목에 속하는 과 목록은 다음과 같습니다.

SELECT DISTINCT FamilyKor
FROM korean_birds
WHERE OrderKor = ?
  AND FamilyKor IS NOT NULL
ORDER BY FamilyKor;

속 목록도 같은 방식으로 조회할 수 있습니다.

SELECT DISTINCT GenusKor
FROM korean_birds
WHERE OrderKor = ?
  AND FamilyKor = ?
  AND GenusKor IS NOT NULL
ORDER BY GenusKor;

이처럼 상위 Spinner 선택값을 하위 조회 조건으로 전달하면 연쇄 필터를 구현할 수 있습니다.

단, 상위 Spinner가 변경될 때 하위 Spinner의 기존 선택값을 초기화해야 합니다.

그렇지 않으면 서로 맞지 않는 필터 조건이 남아 검색 결과가 비어 있을 수 있습니다.

10. UPSERT로 좋아요 상태 저장하기

사용자가 좋아요 버튼을 누르면 다음 두 가지 상황이 발생합니다.

해당 조류의 메모 레코드가 아직 없음 → INSERT
이미 레코드가 있음 → UPDATE

이 작업을 매번 별도 SELECT로 확인한 뒤 처리할 수도 있지만 SQLite의 UPSERT를 사용하면 더 간결하게 구현할 수 있습니다.

INSERT INTO bird_memos (
    bird_ktsn,
    is_like,
    created_at
)
VALUES (?, ?, ?)
ON CONFLICT(bird_ktsn)
DO UPDATE SET
    is_like = excluded.is_like;

여기서 ON CONFLICT(bird_ktsn)은 bird_ktsn의 UNIQUE 제약 조건과 연결됩니다.

해당 ID가 없으면 INSERT하고, 이미 존재하면 is_like만 UPDATE합니다.

기존 메모나 관찰 기록은 변경하지 않습니다.

Android 버전 호환성 주의

SQLite UPSERT 문법은 SQLite 3.24.0부터 지원됩니다.

Android 기기에 내장된 SQLite 버전은 Android OS 버전에 따라 달라집니다.

따라서 minSdk 24처럼 구형 Android 버전까지 지원하는 앱에서는 이 문법을 무조건 사용할 수 없습니다.

앱에서 실제 SQLite 버전을 확인한 뒤 사용하거나, 호환성이 필요한 경우 다른 방식으로 구현해야 합니다.

11. 구형 Android에서도 사용할 수 있는 UPSERT 대안

구형 Android 버전까지 지원해야 한다면 트랜잭션 안에서 UPDATE를 먼저 실행하고, 변경된 행이 없으면 INSERT하는 방법을 사용할 수 있습니다.

fun saveLike(
    birdKtsn: Long,
    isLiked: Boolean
) {
    val db = dbHelper.writableDatabase

    db.beginTransaction()

    try {
        val values = ContentValues().apply {
            put("is_like", if (isLiked) 1 else 0)
        }

        val updatedRows = db.update(
            "bird_memos",
            values,
            "bird_ktsn = ?",
            arrayOf(birdKtsn.toString())
        )

        if (updatedRows == 0) {
            val insertValues = ContentValues().apply {
                put("bird_ktsn", birdKtsn)
                put("is_like", if (isLiked) 1 else 0)
                put("created_at", System.currentTimeMillis())
            }

            db.insertOrThrow(
                "bird_memos",
                null,
                insertValues
            )
        }

        db.setTransactionSuccessful()
    } finally {
        db.endTransaction()
    }
}

이 코드는 기존 메모를 유지하면서 좋아요 상태만 변경합니다.

beginTransaction()과 endTransaction()을 이용해 두 작업을 하나의 트랜잭션으로 묶었습니다.

이 방식은 SQLite의 최신 UPSERT 문법에 의존하지 않으므로 구형 Android 기기에서도 사용할 수 있습니다.

단, 실제 앱에서는 데이터베이스를 여러 스레드에서 동시에 접근하는 상황도 고려해야 합니다.

12. SQLite 조회를 메인 스레드에서 실행하면 안 되는 이유

SQLite 조회는 디스크 I/O 작업입니다.

데이터가 적으면 큰 문제가 없어 보일 수 있지만 데이터가 많아지거나 복잡한 JOIN을 수행하면 메인 스레드가 멈출 수 있습니다.

예를 들어 다음과 같은 코드는 피하는 것이 좋습니다.

val birds = repository.getAllBirds()
adapter.submitList(birds)

이 코드를 Activity나 Fragment의 메인 스레드에서 직접 실행하면 데이터베이스 조회가 끝날 때까지 UI가 지연될 수 있습니다.

이 문제를 해결하기 위해 Kotlin Coroutines를 사용할 수 있습니다.

13. Coroutine Dispatchers.IO 사용하기

데이터베이스 조회는 Dispatchers.IO에서 실행하도록 구성합니다.

class BirdRepository(
    private val dbHelper: BirdDatabaseHelper
) {

    suspend fun getBirds(): List<BirdInfo> =
        withContext(Dispatchers.IO) {
            getAllBirds()
        }

    private fun getAllBirds(): List<BirdInfo> {
        // SQLite 조회 및 Cursor 변환
        return emptyList()
    }
}

위 코드는 Coroutine 구조를 설명하기 위한 예시이며, getAllBirds()의 실제 구현은 앞서 작성한 JOIN 조회 코드를 사용합니다.

Dispatchers.IO는 디스크 I/O나 네트워크 작업을 수행할 때 사용할 수 있는 Coroutine Dispatcher입니다.

이렇게 구성하면 데이터베이스 조회 때문에 메인 스레드가 장시간 차단되는 문제를 줄일 수 있습니다.

14. ViewModel에서 데이터 조회하기

ViewModel에서는 viewModelScope를 사용해 데이터를 조회할 수 있습니다.

class BirdViewModel(
    private val repository: BirdRepository
) : ViewModel() {

    private val _birds =
        MutableLiveData<List<BirdInfo>>()

    val birds: LiveData<List<BirdInfo>> = _birds

    fun loadBirds() {
        viewModelScope.launch {
            try {
                val result = repository.getBirds()
                _birds.value = result
            } catch (e: Exception) {
                Log.e(
                    "BirdViewModel",
                    "Failed to load birds",
                    e
                )
            }
        }
    }
}

Repository에서 Dispatchers.IO를 사용했기 때문에 ViewModel에서는 일반적으로 메인 스레드에서 Coroutine을 시작해도 됩니다.

조회가 완료되면 메인 스레드로 돌아와 LiveData를 갱신합니다.

Fragment에서는 다음과 같이 관찰할 수 있습니다.

viewModel.birds.observe(viewLifecycleOwner) { birds ->
    adapter.submitList(birds)
}

이 구조에서는 데이터베이스 조회와 UI 갱신이 분리됩니다.

15. 검색어가 바뀔 때 이전 검색 취소하기

사용자가 검색창에 문자를 빠르게 입력하면 여러 검색 요청이 연속으로 발생할 수 있습니다.

예를 들어 다음과 같습니다.

ㅊ
차
참
참새

입력할 때마다 데이터베이스를 조회하면 불필요한 검색이 반복됩니다.

이 경우 Job을 이용해 이전 검색을 취소하고 일정 시간 기다린 뒤 최신 검색어만 조회하도록 구성할 수 있습니다.

private var searchJob: Job? = null

fun search(keyword: String) {

    searchJob?.cancel()

    searchJob = viewModelScope.launch {

        delay(300)

        try {
            val result = repository.searchBirds(keyword)
            _birds.value = result
        } catch (e: CancellationException) {
            throw e
        } catch (e: Exception) {
            Log.e("BirdViewModel", "Search failed", e)
        }
    }
}

여기서 Repository의 searchBirds()는 suspend 함수이며 내부에서 Dispatchers.IO로 조회한다고 가정합니다.

이 방식은 검색 요청 수를 줄이는 데 도움이 됩니다.

다만 Android의 SQLite 쿼리는 Coroutine을 취소했다고 해서 이미 실행 중인 SQL이 항상 즉시 중단되는 것은 아닙니다.

긴 쿼리를 실제로 취소해야 한다면 CancellationSignal 지원 여부도 검토해야 합니다.

16. 인덱스로 검색 속도 개선하기

데이터가 많아지면 WHERE 조건에 사용되는 컬럼에 인덱스를 생성하는 것이 도움이 될 수 있습니다.

예를 들어 다음과 같습니다.

CREATE INDEX IF NOT EXISTS idx_birds_order
ON korean_birds(OrderKor);

CREATE INDEX IF NOT EXISTS idx_birds_family
ON korean_birds(FamilyKor);

CREATE INDEX IF NOT EXISTS idx_birds_genus
ON korean_birds(GenusKor);

여러 필터를 함께 사용하는 경우에는 복합 인덱스를 고려할 수 있습니다.

CREATE INDEX IF NOT EXISTS idx_birds_filter
ON korean_birds(
    OrderKor,
    FamilyKor,
    GenusKor
);

다만 인덱스가 많을수록 항상 빠른 것은 아닙니다.

인덱스는 저장 공간을 사용하고 INSERT, UPDATE, DELETE 성능에도 영향을 줄 수 있습니다.

또한 다음과 같은 검색은 일반적인 B-tree 인덱스의 이점을 충분히 활용하지 못할 수 있습니다.

WHERE SpeciesKor LIKE '%참새%'

앞에 %가 붙는 부분 검색은 일반적인 접두어 검색과 다르기 때문입니다.

대량의 텍스트 검색이 필요하다면 SQLite FTS(Full-Text Search)도 검토할 수 있습니다.

17. EXPLAIN QUERY PLAN으로 인덱스 확인하기

인덱스를 생성한 뒤 실제로 사용되는지 확인하려면 EXPLAIN QUERY PLAN을 활용할 수 있습니다.

EXPLAIN QUERY PLAN
SELECT *
FROM korean_birds
WHERE OrderKor = '참새목'
  AND FamilyKor = '참새과';

실행 계획에는 테이블 스캔 또는 인덱스 검색과 관련된 정보가 표시됩니다.

예를 들어 SCAN이 표시된다면 전체 테이블을 순회하는 상황일 수 있습니다.

반면 SEARCH ... USING INDEX 또는 USING COVERING INDEX 등이 표시된다면 인덱스를 이용한 검색일 수 있습니다.

단, 실행 계획의 구체적인 출력 형식은 SQLite 버전에 따라 달라질 수 있습니다.

인덱스의 효과는 실제 데이터 규모와 쿼리 조건을 기준으로 확인해야 합니다.

18. assets에 포함된 SQLite 데이터베이스 사용하기

도감이나 사전 앱에서는 수천 건 이상의 기본 데이터를 앱에 포함해야 할 수 있습니다.

이 경우 앱 실행 시 모든 데이터를 INSERT하는 것보다 미리 생성한 SQLite 파일을 assets에 포함하는 방식이 편리할 수 있습니다.

예를 들어 다음과 같은 구조입니다.

app/
 └── src/
      └── main/
           └── assets/
                └── databases/
                     └── birds.db

앱 최초 실행 시 데이터베이스 파일을 내부 저장소로 복사한 후 사용할 수 있습니다.

fun copyDatabaseIfNeeded(context: Context) {

    val dbFile = context.getDatabasePath("birds.db")

    if (dbFile.exists()) return

    dbFile.parentFile?.mkdirs()

    context.assets.open("databases/birds.db").use { input ->
        dbFile.outputStream().use { output ->
            input.copyTo(output)
        }
    }
}

위 코드는 기본적인 파일 복사 예시입니다.

실제 서비스에서는 복사 중 앱이 종료되는 상황, 파일 무결성, 기존 데이터베이스 버전, WAL 관련 파일 등을 고려해야 합니다.

또한 이미 사용자 데이터가 저장된 데이터베이스를 새 assets 파일로 무조건 덮어쓰면 안 됩니다.

기본 데이터와 사용자 데이터를 분리하거나, 데이터베이스 버전별 마이그레이션 전략을 수립하는 것이 중요합니다.

19. 앱 업데이트 시 데이터베이스 마이그레이션

앱 업데이트로 테이블에 새로운 컬럼을 추가해야 한다고 가정하겠습니다.

예를 들어 사용자 메모 테이블에 updated_at 컬럼을 추가하는 경우입니다.

ALTER TABLE bird_memos
ADD COLUMN updated_at INTEGER NOT NULL DEFAULT 0;

SQLiteOpenHelper에서는 다음과 같이 버전별로 처리할 수 있습니다.

override fun onUpgrade(
    db: SQLiteDatabase,
    oldVersion: Int,
    newVersion: Int
) {
    if (oldVersion < 2) {
        db.execSQL(
            """
            ALTER TABLE bird_memos
            ADD COLUMN updated_at INTEGER
                NOT NULL DEFAULT 0
            """.trimIndent()
        )
    }

    if (oldVersion < 3) {
        db.execSQL(
            """
            CREATE INDEX IF NOT EXISTS idx_birds_filter
            ON korean_birds(
                OrderKor,
                FamilyKor,
                GenusKor
            )
            """.trimIndent()
        )
    }
}

데이터베이스 버전을 3으로 올린 경우, 기존 버전이 1이라면 버전 2와 3의 변경사항이 순서대로 적용됩니다.

이처럼 누적 마이그레이션을 작성하면 이전 버전에서 업데이트하는 사용자도 대응할 수 있습니다.

단, 테이블을 새로 생성하는 onCreate()에도 최신 스키마가 반영되어 있어야 합니다.

20. SQLite 성능 및 안정성 점검 체크리스트

데이터베이스 조회를 메인 스레드에서 실행하지 않음
Dispatchers.IO를 이용한 비동기 처리
Cursor를 use {}로 안전하게 닫음
SQL 인자를 바인딩하여 전달
LIKE 와일드카드 문자 이스케이프 처리
JOIN 조건에 고유 ID 사용
사용자 데이터 테이블에 적절한 UNIQUE 제약 조건 적용
INSERT/UPDATE를 트랜잭션으로 처리
Android SQLite 버전별 UPSERT 호환성 확인
자주 사용하는 필터 조건의 인덱스 검토
EXPLAIN QUERY PLAN으로 실행 계획 확인
assets 데이터베이스 복사 및 업데이트 전략 수립
기존 사용자 데이터 보존을 위한 마이그레이션 구현
검색 요청 중복 실행 및 취소 처리
실제 기기에서 대량 데이터 검색 성능 확인

21. 마무리

Android에서 SQLite를 직접 사용하면 데이터베이스 구조와 SQL을 세밀하게 제어할 수 있습니다.

특히 기본 데이터가 많고 사용자 메모나 즐겨찾기처럼 비교적 단순한 데이터를 추가로 관리하는 앱에서는 SQLiteOpenHelper 기반 구조도 충분히 활용할 수 있습니다.

다만 다음 사항은 반드시 고려해야 합니다.

JOIN과 고유 ID를 이용해 기본 데이터와 사용자 데이터를 연결합니다.
LIKE 검색에서는 SQL 바인딩과 와일드카드 이스케이프를 함께 처리합니다.
UPSERT는 Android 내장 SQLite 버전 호환성을 확인합니다.
데이터베이스 조회는 Coroutine의 Dispatchers.IO에서 실행합니다.
인덱스와 실행 계획을 확인해 검색 성능을 개선합니다.
앱 업데이트 시 기존 사용자 데이터를 보존하는 마이그레이션을 구현합니다.

SQLite를 직접 사용하는 프로젝트에서는 이러한 기본 원칙을 지키는 것만으로도 안정성과 유지보수성을 크게 개선할 수 있습니다.

참고 자료

Android Developers — SQLite 데이터 저장
https://developer.android.com/training/data-storage/sqlite
Android Developers — SQLiteOpenHelper
https://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper
Android Developers — SQLiteDatabase
https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase
Android Developers — Kotlin Coroutines
https://developer.android.com/kotlin/coroutines
SQLite 공식 문서 — UPSERT
https://www.sqlite.org/lang_upsert.html
SQLite 공식 문서 — Query Planner
https://www.sqlite.org/queryplanner.html
SQLite 공식 문서 — EXPLAIN QUERY PLAN
https://www.sqlite.org/eqp.html

태그: Android, Kotlin, SQLite, SQLiteOpenHelper, SQLiteDatabase, JOIN, LIKE, UPSERT, Coroutine, ViewModel, LiveData, Android 데이터베이스

Android

Android SQLite 데이터베이스 실전 활용 — JOIN, LIKE 검색, UPSERT와 Coroutine 비동기 처리

Android SQLite 데이터베이스 실전 활용 — JOIN, LIKE 검색, UPSERT와 Coroutine 비동기 처리

1. Android SQLite 직접 사용 구조

2. 테이블 구조 설계

조류 기본 정보 테이블

사용자 메모 테이블

3. SQLiteOpenHelper 구현하기

4. JOIN으로 기본 정보와 사용자 데이터 함께 조회하기

COALESCE 사용 이유

5. Kotlin에서 JOIN 결과 조회하기

6. LIKE를 이용한 부분 검색

7. LIKE 검색에서 %, _ 문자 처리하기

8. 여러 검색 조건을 동적으로 조합하기

9. DISTINCT를 이용한 Spinner 필터 구성

10. UPSERT로 좋아요 상태 저장하기

Android 버전 호환성 주의

11. 구형 Android에서도 사용할 수 있는 UPSERT 대안

12. SQLite 조회를 메인 스레드에서 실행하면 안 되는 이유

13. Coroutine Dispatchers.IO 사용하기

14. ViewModel에서 데이터 조회하기

15. 검색어가 바뀔 때 이전 검색 취소하기

16. 인덱스로 검색 속도 개선하기

17. EXPLAIN QUERY PLAN으로 인덱스 확인하기

18. assets에 포함된 SQLite 데이터베이스 사용하기

19. 앱 업데이트 시 데이터베이스 마이그레이션

20. SQLite 성능 및 안정성 점검 체크리스트

21. 마무리

참고 자료

댓글 0