SQLite查询5000条数据耗时超1分钟，求性能优化方案

问题描述 #

查询SQLite中5000条数据耗时超过1分钟，无法通过分页限制数据量解决问题，需要针对性的性能优化方案。

现有代码 #

表创建语句 #

//Table Create    
private String CREATE_COST_CENTER_TABLE = "CREATE TABLE IF NOT EXISTS "
        + SQLConstants.TABLE_COST_CENTER + "( "
        + SQLConstants.FIELD_COST_CENTER_ID + " INTEGER PRIMARY KEY,"
        + SQLConstants.FIELD_COST_CENTER_NAME + " VARCHAR,"
        + SQLConstants.FIELD_BUSINESS_UNIT_ID + " INTEGER,"
        + SQLConstants.FIELD_COMPANY_ID + " INTEGER)";

数据插入逻辑 #

//Insertion of Costcentre
public synchronized void saveCostCenterItemList(
        List<CostCenterItem> costCenterItemList) {
    if (costCenterItemList == null)
        return;

    database.delete(SQLConstants.TABLE_COST_CENTER, null, null);

    for (CostCenterItem costCenterItem : costCenterItemList) {
        saveCostCenterItem(costCenterItem);
    }
 }

public long saveCostCenterItem(CostCenterItem costCenterItem) {
    if (costCenterItem == null)
        return -1;

    long rowId = -1;
    database = sqlHelper.getWritableDatabase();
    SQLiteStatement insStmt = database.compileStatement("INSERT INTO " + 
    SQLConstants.TABLE_COST_CENTER
            + " (" + SQLConstants.FIELD_COST_CENTER_ID + "," + 
    SQLConstants.FIELD_COST_CENTER_NAME
            + "," + SQLConstants.FIELD_BUSINESS_UNIT_ID + ","+ 
    SQLConstants.FIELD_COMPANY_ID +
            ") VALUES (?, ?, ?, ?);");
    database.beginTransaction();

    try {
        try {
             insStmt.bindString(1, String.valueOf(costCenterItem.getId()));
            insStmt.bindString(2, costCenterItem.getName());
            insStmt.bindLong(3,  costCenterItem.getBusinessUnitId());
            insStmt.bindString(4, String.valueOf(costCenterItem.getCompanyId()));

            rowId = insStmt.executeInsert();

            database.setTransactionSuccessful();
        } finally {
            database.endTransaction();
        }
    } catch (Exception e1) {
        e1.printStackTrace();
        rowId = -1;
    }

    return rowId;
 }

数据查询逻辑 #

//Fetch Data  
public ArrayList<CostCenterItem> getCostCenterItemListByBusinessIDandCompanyid1(
        int businessUnitId, int companyId ) {
    ArrayList<CostCenterItem> list = new ArrayList<>();
    Cursor cursor = null;

    try {
        cursor = database.query(SQLConstants.TABLE_COST_CENTER, 
null,
                SQLConstants.FIELD_BUSINESS_UNIT_ID + "=" + 
businessUnitId
                        + " AND "
                        + SQLConstants.FIELD_COMPANY_ID + "="
                        + companyId ,
                null, null, null, null);

        if (cursor != null && cursor.getCount() > 0) {
            for (int i = 0; i < cursor.getCount(); i++) {
                cursor.moveToPosition(i);

                CostCenterItem item = new CostCenterItem();
                item.setId(cursor.getInt(cursor
                        .getColumnIndex(SQLConstants.FIELD_COST_CENTER_ID)));
                item.setName(cursor.getString(cursor
                        .getColumnIndex(SQLConstants.FIELD_COST_CENTER_NAME)));
                item.setBusinessUnitId(cursor.getInt(cursor
                        .getColumnIndex(SQLConstants.FIELD_BUSINESS_UNIT_ID)));
                
                item.setCompanyId(cursor.getInt(cursor.getColumnIndex(SQLConstants.FIELD_COMPANY_ID)));

                list.add(item);
            }
        }
    } catch (SQLiteException e) {
        e.printStackTrace();
    } finally {
        if (cursor != null && !cursor.isClosed()) {
            cursor.close();
        }
    }

    return list;
}

已尝试的优化 #

已为FIELD_COST_CENTER_NAME列创建索引：

private static final String SQL_CREATE_EMAIL_INDEX =
"CREATE INDEX cost_center_cc_name_idx ON " + SQLConstants.TABLE_COST_CENTER + "
(" + SQLConstants.FIELD_COST_CENTER_NAME + ")";

针对性优化方案 #

1. 创建查询条件的复合索引 #

当前查询通过businessUnitId和companyId过滤数据，单独字段索引无法发挥最大效用，创建这两个字段的复合索引可大幅提升查询速度：

private static final String SQL_CREATE_COMPOSITE_INDEX =
"CREATE INDEX cost_center_bu_company_idx ON " + SQLConstants.TABLE_COST_CENTER + "
(" + SQLConstants.FIELD_BUSINESS_UNIT_ID + ", " + SQLConstants.FIELD_COMPANY_ID + ")";

2. 优化Cursor遍历方式 #

避免moveToPosition(i)的随机访问，改用顺序遍历减少Cursor定位开销，同时预获取列索引避免重复查找：

// 修改查询方法中的遍历逻辑
if (cursor != null) {
    // 预获取所有列索引
    int idIndex = cursor.getColumnIndex(SQLConstants.FIELD_COST_CENTER_ID);
    int nameIndex = cursor.getColumnIndex(SQLConstants.FIELD_COST_CENTER_NAME);
    int buIdIndex = cursor.getColumnIndex(SQLConstants.FIELD_BUSINESS_UNIT_ID);
    int companyIdIndex = cursor.getColumnIndex(SQLConstants.FIELD_COMPANY_ID);
    
    // 顺序遍历Cursor
    while (cursor.moveToNext()) {
        CostCenterItem item = new CostCenterItem();
        item.setId(cursor.getInt(idIndex));
        item.setName(cursor.getString(nameIndex));
        item.setBusinessUnitId(cursor.getInt(buIdIndex));
        item.setCompanyId(cursor.getInt(companyIdIndex));
        list.add(item);
    }
}

3. 移除cursor.getCount()调用 #

cursor.getCount()会触发额外的结果集遍历，直接通过moveToNext()判断是否有数据即可，无需提前获取总数。

4. 优化批量插入逻辑 #

当前每条数据单独开启事务，修改为批量事务插入减少事务开销：

public synchronized void saveCostCenterItemList(
        List<CostCenterItem> costCenterItemList) {
    if (costCenterItemList == null || costCenterItemList.isEmpty())
        return;

    database = sqlHelper.getWritableDatabase();
    SQLiteStatement insStmt = database.compileStatement("INSERT INTO " + 
    SQLConstants.TABLE_COST_CENTER
            + " (" + SQLConstants.FIELD_COST_CENTER_ID + "," + 
    SQLConstants.FIELD_COST_CENTER_NAME
            + "," + SQLConstants.FIELD_BUSINESS_UNIT_ID + ","+ 
    SQLConstants.FIELD_COMPANY_ID +
            ") VALUES (?, ?, ?, ?);");
            
    database.beginTransaction();
    try {
        database.delete(SQLConstants.TABLE_COST_CENTER, null, null);
        for (CostCenterItem costCenterItem : costCenterItemList) {
            insStmt.bindString(1, String.valueOf(costCenterItem.getId()));
            insStmt.bindString(2, costCenterItem.getName());
            insStmt.bindLong(3,  costCenterItem.getBusinessUnitId());
            insStmt.bindString(4, String.valueOf(costCenterItem.getCompanyId()));
            insStmt.executeInsert();
            insStmt.clearBindings();
        }
        database.setTransactionSuccessful();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        database.endTransaction();
    }
 }

5. 使用参数化查询 #

避免直接拼接SQL参数，改用参数化查询提升缓存效率并避免SQL注入风险：

cursor = database.query(SQLConstants.TABLE_COST_CENTER, 
null,
        SQLConstants.FIELD_BUSINESS_UNIT_ID + "=? AND " + SQLConstants.FIELD_COMPANY_ID + "=?",
        new String[]{String.valueOf(businessUnitId), String.valueOf(companyId)},
        null, null, null);

内容的提问来源于stack exchange，提问作者Gaurav Sharma