android SQLite性能优化（二）——使用NDK优化

文章目录

在做android SQLite数据插入性能优化时，前面一篇android SQLite性能优化（一）——使用事务和预处理提到了使用事务和预处理做优化，本篇介绍使用NDK的方式进行数据插入优化。
使用NDK操作数据库时，需要使用sqlite的官方源码，我这里测试使用的是官方的3.8.7.4版本的代码。同样在使用sqlite操作数据库时使用了事务和预处理。

下面是Java层的代码，主要是将List数据转换成数组数据，指定数据库文件路径，及数组数据和长度。

void insertNative(List<DemoItem> data) {
    Log.d(TAG, "databases path: " + getCacheDir());
    final int size = data.size();
    DemoItem[] items = new DemoItem[size];
    data.toArray(items);
    File file = new File(getCacheDir(), "tel.db");
    long start = System.currentTimeMillis();
    TelTools.insertNative(file.getAbsolutePath(), items, items.length);
    long end = System.currentTimeMillis();
    Log.d(TAG, "used time: " + (end - start) + ", nums: " + items.length + ", average item: " + (end - start) / items.length);

    // 使用预处理
    // used time: 2372, nums: 65536, average item: 0
    // used time: 2422, nums: 65536, average item: 0
}

整个耗时2.4秒左右，是使用Java的方式耗时的一半都不到。下面是C代码，可以看出来，使用C代码操作数据库需要做很多事情，远没有Java来的方便；如果数据库结构发生修改，就更难维护了。

JNIEXPORT jint JNICALL Java_com_example_sqlitedemo_TelTools_insertNative
  (JNIEnv *env, jclass jcls, jstring jdbPath, jobjectArray jarr, jint jlen)
{
    if (jlen <= 0) {
        LOGW("len must > 0");
        return -1;
    }

    const char* sdbPath = (*env)->GetStringUTFChars(env, jdbPath, NULL);
    sqlite3* db;
    int result = 0;
    sqlite3_initialize( );
    result = sqlite3_open_v2(sdbPath, &db, SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE, NULL);
    if (SQLITE_OK != result) {
        LOGE("sqlite3_open failed, result: %d, error msg: %s", result, sqlite3_errmsg(db));
        return result;
    }

    const char* createSQL = "CREATE TABLE tel(_id INTEGER PRIMARY KEY, id TEXT, tel TEXT, code TEXT, address TEXT, type TEXT, date INTEGER)";
    char* pErrorMsg;
    result = sqlite3_exec(db, createSQL, NULL, NULL, &pErrorMsg);
    if (SQLITE_ERROR != result && SQLITE_OK != result) {
        LOGE("exec sql(%s) failed, result: %d, error msg: %s", createSQL, result, pErrorMsg);
        sqlite3_free(pErrorMsg);
        sqlite3_close(db);
        return result;
    }

    jclass cItem = (*env)->FindClass(env, "com/example/sqlitedemo/mode/DemoItem");

    jfieldID fID = (*env)->GetFieldID(env, cItem, "id", "Ljava/lang/String;");
    jfieldID fTel = (*env)->GetFieldID(env, cItem, "tel", "Ljava/lang/String;");
    jfieldID fCode = (*env)->GetFieldID(env, cItem, "code", "Ljava/lang/String;");
    jfieldID fAddress = (*env)->GetFieldID(env, cItem, "address", "Ljava/lang/String;");
    jfieldID fType = (*env)->GetFieldID(env, cItem, "type", "Ljava/lang/String;");
    jfieldID fDate = (*env)->GetFieldID(env, cItem, "date", "J");

    //const int sqlLen = 512;
    //char sql[sqlLen];
    const char* insertSQL = "insert into tel(id, tel, code, address, type, date) values(?,?,?,?,?,?)";
    const char* beginSQL = "begin;";
    const char* commitSQL = "commit;";
    const char* rollbackSQL = "rollback;";
    sqlite3_exec(db, beginSQL, NULL, NULL, NULL);
    //string sql;
    int isSuccess = 1;
    jobject obj;
    jstring sId, sTel, sCode, sAddress, sType;
    sqlite3_stmt *stmt = NULL;
    sqlite3_prepare_v2(db, insertSQL, strlen(insertSQL) * 2, &stmt, NULL);
    int i;
    for (i = 0; i < jlen; i++) {
        obj = (*env)->GetObjectArrayElement(env, jarr, i);
        sId = (jstring) ((*env)->GetObjectField(env, obj, fID));
        const char* id = (*env)->GetStringUTFChars(env, sId, NULL);

        sTel = (jstring) ((*env)->GetObjectField(env, obj, fTel));
        const char* tel = (*env)->GetStringUTFChars(env, sTel, NULL);

        sCode = (jstring) ((*env)->GetObjectField(env, obj, fCode));
        const char* code = (*env)->GetStringUTFChars(env, sCode, NULL);

        sAddress = (jstring) ((*env)->GetObjectField(env, obj, fAddress));
        const char* address = (*env)->GetStringUTFChars(env, sAddress, NULL);

        sType = (jstring) ((*env)->GetObjectField(env, obj, fType));
        const char* type = (*env)->GetStringUTFChars(env, sType, NULL);
        (*env)->DeleteLocalRef(env, obj);

        //jstring sDate = (jstring) (env->GetObjectField(obj, fTel));
        //const char* tel = env->GetStringUTFChars(sTel, NULL);

        sqlite3_bind_text(stmt, 0, id, strlen(id), NULL);
        sqlite3_bind_text(stmt, 1, tel, strlen(tel), NULL);
        sqlite3_bind_text(stmt, 2, code, strlen(code), NULL);
        sqlite3_bind_text(stmt, 3, address, strlen(address), NULL);
        sqlite3_bind_text(stmt, 4, type, strlen(type), NULL);
        sqlite3_bind_text(stmt, 5, "10000000", 8, NULL);

        sqlite3_step(stmt);

        sqlite3_reset(stmt);
        sqlite3_clear_bindings(stmt);

        //LOGD("sql: %s", sql.c_str());

        //result = sqlite3_exec(db, sql, NULL, NULL, &pErrorMsg);
        //sql.clear();

        (*env)->ReleaseStringUTFChars(env, sId, id);
        (*env)->ReleaseStringUTFChars(env, sTel, tel);
        (*env)->ReleaseStringUTFChars(env, sCode, code);
        (*env)->ReleaseStringUTFChars(env, sAddress, address);
        (*env)->ReleaseStringUTFChars(env, sType, type);
        (*env)->DeleteLocalRef(env, sId);
        (*env)->DeleteLocalRef(env, sTel);
        (*env)->DeleteLocalRef(env, sCode);
        (*env)->DeleteLocalRef(env, sAddress);
        (*env)->DeleteLocalRef(env, sType);

//        isSuccess = result == 0;
//        if (!isSuccess) {
//            break;
//        }
    }

    if (isSuccess) {
        sqlite3_exec(db, commitSQL, NULL, NULL, NULL);
    } else {
        sqlite3_exec(db, rollbackSQL, NULL, NULL, NULL);
    }

    (*env)->ReleaseStringUTFChars(env, jdbPath, sdbPath);
    (*env)->DeleteLocalRef(env, jdbPath);
    sqlite3_free(pErrorMsg);
    sqlite3_finalize(stmt);
    sqlite3_close(db);
    sqlite3_shutdown();
    return result;
}

So，到这一步就没能再进行优化下去了，上面测试的表结构没有加索引，加了会影响性能，但可以提高查询效率。其中要注意语言细节性能问题，因为循环有几万次，所以很小的细节都会被放大。网上还有论文优化了sqlite的查询性能，单次性能提升了10%，目测是修改了sqlite中虚拟机产生代码的处理流程，恕水平有限，没有耗更多的精力和时间在这上面，到此就结束了，如果读者感兴趣，可以接着优化。google搜索关键字《SQLite的SQL语句高速缓存技术》即可找到这篇论文。