阅读 903

Android SQL,你用对了吗(一)——条件限定

提到SQLiteDatabase这个类,大家都不陌生。对数据库进行增删改查,免不了跟它打交道,其中:

删改查三个操作往往需要进行条件限定。限定条件通过参数String whereClause, String[] whereArgs来指定。

whereClause的取值形如_id = ? AND condition1 >= ? OR condition2 != ?,其中的?用于参数绑定,按顺序,填入whereArgs数组内。

但说实话,使用这种方式,需要:

  1. 先将限定部分的SQL语句写出来,将限定的参数替换为?
  2. 记住次序,填入参数数组内。

这么做一次还好,写多了挺烦人的,如果后期修改的话,还需要仔细确保SQL语句书写正确,再三确保修改不会弄错参数顺序。如果弄错了?那只能慢慢debug了。

为了方便,有的同学就直接放弃了whereClausewhereArgs这种搭配,直接传入完整的SQL限定字符串作为whereClause参数的值,放弃了参数化,在whereArgs参数传入了null

这种用法一样能达成我们的需求,甚至用起来更加方便,为什么SDK无端端要搞得这么复杂呢?答:一切都是为了性能。

源码探究

下面我们来看下源码,探究下处理过程,扒一扒SDK在哪一步优化了性能。

int delete(String table, String whereClause, String[] whereArgs)这个方法为切入点,相关实现在SQLiteDatabase.java里。

    /**
     * Convenience method for deleting rows in the database.
     *
     * @param table the table to delete from
     * @param whereClause the optional WHERE clause to apply when deleting.
     *            Passing null will delete all rows.
     * @param whereArgs You may include ?s in the where clause, which
     *            will be replaced by the values from whereArgs. The values
     *            will be bound as Strings.
     * @return the number of rows affected if a whereClause is passed in, 0
     *         otherwise. To remove all rows and get a count pass "1" as the
     *         whereClause.
     */
    public int delete(String table, String whereClause, String[] whereArgs) {
        acquireReference();
        try {
            // 组装成完整的SQL语句,实例化SQLiteStatement
            SQLiteStatement statement =  new SQLiteStatement(this, "DELETE FROM " + table +
                    (!TextUtils.isEmpty(whereClause) ? " WHERE " + whereClause : ""), whereArgs);
            try {
                // 执行SQL语句
                return statement.executeUpdateDelete();
            } finally {
                statement.close();
            }
        } finally {
            releaseReference();
        }
    }复制代码

源码里并没做什么神奇的事情,仅仅是组装成完整的SQL语句,和参数数组一起,实例化SQLiteStatement,然后执行这个语句。

执行的过程实现在SQLiteStatement.java

    /**
     * Execute this SQL statement, if the the number of rows affected by execution of this SQL
     * statement is of any importance to the caller - for example, UPDATE / DELETE SQL statements.
     *
     * @return the number of rows affected by this SQL statement execution.
     * @throws android.database.SQLException If the SQL string is invalid for
     *         some reason
     */
    public int executeUpdateDelete() {
        acquireReference();
        try {
            // 获取各个参数:sql语句、要绑定的参数等
            // 然后才是真正的执行
            return getSession().executeForChangedRowCount(
                    getSql(), getBindArgs(), getConnectionFlags(), null);
        } catch (SQLiteDatabaseCorruptException ex) {
            onCorruption();
            throw ex;
        } finally {
            releaseReference();
        }
    }复制代码

获取参数,然后调用executeForChangedRowCount方法。这个方法在SQLiteSession.java


    /**
     * Executes a statement that returns a count of the number of rows
     * that were changed.  Use for UPDATE or DELETE SQL statements.
     *
     * @param sql The SQL statement to execute.
     * @param bindArgs The arguments to bind, or null if none.
     * @param connectionFlags The connection flags to use if a connection must be
     * acquired by this operation.  Refer to {@link SQLiteConnectionPool}.
     * @param cancellationSignal A signal to cancel the operation in progress, or null if none.
     * @return The number of rows that were changed.
     *
     * @throws SQLiteException if an error occurs, such as a syntax error
     * or invalid number of bind arguments.
     * @throws OperationCanceledException if the operation was canceled.
     */
    public int executeForChangedRowCount(String sql, Object[] bindArgs, int connectionFlags,
            CancellationSignal cancellationSignal) {
        if (sql == null) {
            throw new IllegalArgumentException("sql must not be null.");
        }

        // 这里虽然传入了bindArgs,但并没用到
        if (executeSpecial(sql, bindArgs, connectionFlags, cancellationSignal)) {
            return 0;
        }

        acquireConnection(sql, connectionFlags, cancellationSignal); // might throw
        try {
            // 真正用到sql和bindArgs的地方
            return mConnection.executeForChangedRowCount(sql, bindArgs,
                    cancellationSignal); // might throw
        } finally {
            releaseConnection(); // might throw
        }
    }复制代码

Ok,继续深入,来到SQLiteConnection.java

    /**
     * Executes a statement that returns a count of the number of rows
     * that were changed.  Use for UPDATE or DELETE SQL statements.
     *
     * @param sql The SQL statement to execute.
     * @param bindArgs The arguments to bind, or null if none.
     * @param cancellationSignal A signal to cancel the operation in progress, or null if none.
     * @return The number of rows that were changed.
     *
     * @throws SQLiteException if an error occurs, such as a syntax error
     * or invalid number of bind arguments.
     * @throws OperationCanceledException if the operation was canceled.
     */
    public int executeForChangedRowCount(String sql, Object[] bindArgs,
            CancellationSignal cancellationSignal) {
        if (sql == null) {
            throw new IllegalArgumentException("sql must not be null.");
        }

        int changedRows = 0;
        final int cookie = mRecentOperations.beginOperation("executeForChangedRowCount",
                sql, bindArgs);
        try {
            // 获取预先编译过的SQL
            final PreparedStatement statement = acquirePreparedStatement(sql);
            try {
                throwIfStatementForbidden(statement);
                // 参数绑定
                bindArguments(statement, bindArgs);
                applyBlockGuardPolicy(statement);
                attachCancellationSignal(cancellationSignal);
                try {
                    // 交给SQLiteEngine执行
                    changedRows = nativeExecuteForChangedRowCount(
                            mConnectionPtr, statement.mStatementPtr);
                    return changedRows;
                } finally {
                    detachCancellationSignal(cancellationSignal);
                }
            } finally {
                releasePreparedStatement(statement);
            }
        } catch (RuntimeException ex) {
            mRecentOperations.failOperation(cookie, ex);
            throw ex;
        } finally {
            if (mRecentOperations.endOperationDeferLog(cookie)) {
                mRecentOperations.logOperation(cookie, "changedRows=" + changedRows);
            }
        }
    }复制代码

首先,会通过acquirePreparedStatement去获取PreparedStatement实例,源码如下:

    private PreparedStatement acquirePreparedStatement(String sql) {
        PreparedStatement statement = mPreparedStatementCache.get(sql);
        boolean skipCache = false;
        if (statement != null) {
            if (!statement.mInUse) {
                return statement;
            }
            // The statement is already in the cache but is in use (this statement appears
            // to be not only re-entrant but recursive!).  So prepare a new copy of the
            // statement but do not cache it.
            skipCache = true;
        }

        final long statementPtr = nativePrepareStatement(mConnectionPtr, sql);
        try {
            final int numParameters = nativeGetParameterCount(mConnectionPtr, statementPtr);
            final int type = DatabaseUtils.getSqlStatementType(sql);
            final boolean readOnly = nativeIsReadOnly(mConnectionPtr, statementPtr);
            statement = obtainPreparedStatement(sql, statementPtr, numParameters, type, readOnly);
            if (!skipCache && isCacheable(type)) {
                mPreparedStatementCache.put(sql, statement);
                statement.mInCache = true;
            }
        } catch (RuntimeException ex) {
            // Finalize the statement if an exception occurred and we did not add
            // it to the cache.  If it is already in the cache, then leave it there.
            if (statement == null || !statement.mInCache) {
                nativeFinalizeStatement(mConnectionPtr, statementPtr);
            }
            throw ex;
        }
        statement.mInUse = true;
        return statement;
    }复制代码

可以看到,这里有个mPreparedStatementCache用于缓存之前生成过的PreparedStatement,如果之前有相同的SQL语句,则取出重用,避免重复编译SQL。这个缓存本质上是一个LruCache<String, PreparedStatement>key为sql语句。

也即是,如果我们使用whereClausewhereArgs的方式操作数据库的话,同样的whereClause,不同的whereArgs取值,将能利用到这个缓存。但如果直接将限定语句拼接好,由于参数取值是可变的,一旦发生改变,就变成不同的语句,自然无法匹配上缓存,白白浪费了已编译过的PreparedStatement实例。

顺便贴下绑定参数的代码:


    private void bindArguments(PreparedStatement statement, Object[] bindArgs) {
        final int count = bindArgs != null ? bindArgs.length : 0;
        if (count != statement.mNumParameters) {
            throw new SQLiteBindOrColumnIndexOutOfRangeException(
                    "Expected " + statement.mNumParameters + " bind arguments but "
                    + count + " were provided.");
        }
        if (count == 0) {
            return;
        }

        final long statementPtr = statement.mStatementPtr;
        for (int i = 0; i < count; i++) {
            final Object arg = bindArgs[i];
            switch (DatabaseUtils.getTypeOfObject(arg)) {
                case Cursor.FIELD_TYPE_NULL:
                    nativeBindNull(mConnectionPtr, statementPtr, i + 1);
                    break;
                case Cursor.FIELD_TYPE_INTEGER:
                    nativeBindLong(mConnectionPtr, statementPtr, i + 1,
                            ((Number)arg).longValue());
                    break;
                case Cursor.FIELD_TYPE_FLOAT:
                    nativeBindDouble(mConnectionPtr, statementPtr, i + 1,
                            ((Number)arg).doubleValue());
                    break;
                case Cursor.FIELD_TYPE_BLOB:
                    nativeBindBlob(mConnectionPtr, statementPtr, i + 1, (byte[])arg);
                    break;
                case Cursor.FIELD_TYPE_STRING:
                default:
                    if (arg instanceof Boolean) {
                        // Provide compatibility with legacy applications which may pass
                        // Boolean values in bind args.
                        nativeBindLong(mConnectionPtr, statementPtr, i + 1,
                                ((Boolean)arg).booleanValue() ? 1 : 0);
                    } else {
                        nativeBindString(mConnectionPtr, statementPtr, i + 1, arg.toString());
                    }
                    break;
            }
        }
    }复制代码

代码很简单,就不多解释了。

数据验证

啰啰嗦嗦贴了这么多源码,其实只是为了证明,whereClause搭配whereArgs是很有意义的。说是这么说,但实际优化性能差多少呢?

通过demo验证,执行一千次查询的情况:
简单语句_ID = ?

无参数化 有参数化
112 65

加大复杂度_ID >= ?

无参数化 有参数化
150 71

再复杂点_ID >= ? AND COLUMN_CATEGORY LIKE ?

无参数化 有参数化
190 87

结论:

  • 随着限定语句复杂度的上升,编译一次SQL语句的耗时也随之增加;
  • 使用参数化能有效提升性能,使用参数化语句能提高约一倍的性能(场景越复杂,效果越明显)

所以...为了性能考虑,写代码的时候别再用拼接字符串的方式直接生成限定语句了。

One more things...

但,最开始提及的那种不便的使用方式,难道就只能默默忍受了?答案显然并不是,通过简单的抽象、封装,能够实现如下的效果:

Statement statement =
        Statement.where(UPDATE_TIME).lessOrEqual(now)
        .and(EXPIRY_TIME).moreThan(now)
        .or(AGE).eq(23)
        .end();
statement.sql(); // 生成sql语句
statement.whereClause(); // 生成whereClause语句
statement.args(); // 对应的参数数组复制代码

这是我尝试造的一个轮子,用于通过语义化的方式,定义和生成whereClausewhereArgs。用起来就像是写sql语句一样自然,同时还能避免人工书写sql语句导致的一些拼写错误,生成的whereClause的参数顺序也和whereArgs参数数组严格对应。

写得比较挫,就不发出来卖弄了。哈哈。如有错误的地方,还请各路大神指正!