阅读 70

Matrix SQLiteLint使用及源码分析

前言

这篇文章差不多是去年这个时候写的,写好了一直忘了发出来,现在补发一下...

SQLiteLint。虽然名带“lint”,但并不是代码的静态检查,而是在APP运行时对sql语句、执行序列、表信息等进行分析检测。而和“lint”有点类似的是:在开发阶段就介入,并运用一些最佳实践的规则来检测,从而发现潜在的、可疑的SQLite使用问题。

SQLiteLint的用处

在真正使用SQLiteLint之前先说明一下SQLiteLint的用处,以下用处提炼自SQLiteLint 官方wiki

  1. 检测索引使用问题
  • 未建索引导致的全表扫描
  • 索引未生效导致的全表扫描
  • 不必要的临时建树排序
  • 不足够的索引组合
  1. 检测冗余索引问题
  2. 检测select * 问题
  3. 检测Autoincrement问题
  4. 检测建议使用prepared statement
  5. 检测建议使用without rowid特性

快速接入

1.添加依赖

  • gralde.propeties指定Matrix版本 MATRIX_VERSION=0.5.2(编写该文档时的最新版本)
  • 添加 dependencies
dependencies {
    debugCompile "com.tencent.matrix:matrix-sqlite-lint-android-sdk:${MATRIX_VERSION}"
    releaseCompile "com.tencent.matrix:matrix-sqlite-lint-android-sdk-no-op:${MATRIX_VERSION}"
}
复制代码

2.SQLiteLint初始化并添加关注的database

Application#onCreate方法中初始化

            SQLiteLintConfig config =new SQLiteLintConfig(SQLiteLint.SqlExecutionCallbackMode.HOOK);
            SQLiteLintPlugin sqLiteLintPlugin = new SQLiteLintPlugin(config);
            builder.plugin(sqLiteLintPlugin);

            Matrix.init(builder.build());
            sqLiteLintPlugin.start();
            SQLiteLintPlugin plugin = (SQLiteLintPlugin) Matrix.with().getPluginByClass(SQLiteLintPlugin.class);
            if (plugin == null) {
                return;
            }
            if (!plugin.isPluginStarted()) {
                plugin.start();
            }
            plugin.addConcernedDB(new SQLiteLintConfig.ConcernDb(TestDBHelper.get().getWritableDatabase())
                    //.setWhiteListXml(R.xml.sqlite_lint_whitelist)//disable white list by default
                    .enableAllCheckers());
复制代码

实际测试使用

这里结合Matrix 官方例子来看。

public class TestDBHelper extends SQLiteOpenHelper {
    private static final int DB_VERSION = 1;
    private static final String DB_NAME = "sqliteLintTest.db";
    public static final String TABLE_NAME = "testTable";
    public static final String TABLE_NAME_AUTO_INCREMENT = "testTableAutoIncrement";
    public static final String TABLE_NAME_WITHOUT_ROWID_BETTER = "testTableWithoutRowid";
    public static final String TABLE_NAME_Redundant_index = "testTableRedundantIndex";
    public static final String TABLE_NAME_CONTACT = "contact";
    private static TestDBHelper mHelper = null;
	...
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String sql = "create table if not exists " + TABLE_NAME + " (Id integer primary key, name text, age integer)";
        sqLiteDatabase.execSQL(sql);
        String sqlAutoIncrement = "create table if not exists " + TABLE_NAME_AUTO_INCREMENT + " (Id integer primary key AUTOINCREMENT, name text, age integer)";
        sqLiteDatabase.execSQL(sqlAutoIncrement);
        String sqlWithoutRowId = "create table if not exists " + TABLE_NAME_WITHOUT_ROWID_BETTER + " (Id text primary key, name integer, age integer)";
        sqLiteDatabase.execSQL(sqlWithoutRowId);
        String sqlRedundantIndex = "create table if not exists " + TABLE_NAME_Redundant_index + " (Id text, name text, age integer, gender integer)";
        sqLiteDatabase.execSQL(sqlRedundantIndex);
        String indexSql = "create index if not exists index_age on " + TABLE_NAME_Redundant_index + "(age);";
        String indexSql2 = "create index if not exists index_age_name on " + TABLE_NAME_Redundant_index + "(age, name);";
        String indexSql3 = "create index if not exists index_name_age on " + TABLE_NAME_Redundant_index + "(name,age);";
        String indexSql4 = "create index if not exists index_id on " + TABLE_NAME_Redundant_index + "(Id);";

        sqLiteDatabase.execSQL(indexSql);
        sqLiteDatabase.execSQL(indexSql2);
        sqLiteDatabase.execSQL(indexSql3);
        sqLiteDatabase.execSQL(indexSql4);

        String contact = "create table if not exists " + TABLE_NAME_CONTACT + " (Id integer primary key, name text, age integer, gender integer, status integer)";
        sqLiteDatabase.execSQL(contact);
        String contactIndex = "create index if not exists index_age_name_status on " + TABLE_NAME_CONTACT + "(age, name, status);";
        String contactIndex2 = "create index if not exists index_name_age_status on " + TABLE_NAME_CONTACT + "(name, age, status);";
        String contactStatusIndex = "create index if not exists index_status on " + TABLE_NAME_CONTACT + "(status);";
        sqLiteDatabase.execSQL(contactIndex);
        sqLiteDatabase.execSQL(contactIndex2);
        sqLiteDatabase.execSQL(contactStatusIndex);
    }
	...
}
复制代码

这里新建了几个数据库表:

testTable:正常的表,integer 类型的id作为主键

testTableAutoIncrement:id 作为主键,且是自增属性

testTableWithoutRowid:设置了withoutRowid属性

testTableRedundantIndex:设置多个索引

contact:主要用于多条件查询

以下为测试的sql语句:

    public static String[] getTestSqlList() {
        String[] list = new String[]{
                "select * from testTable",//select *
                "select name from testTable where age>10",//no index
                "select name from testTableRedundantIndex where age&2 != 0",//not use index
                "select name from testTableRedundantIndex where name like 'j%'",//not use index
                "select name from testTableRedundantIndex where name = 'jack' and age > 20",
                "select testTable.name from testTable, testTableAutoIncrement where testTableAutoIncrement.age=testTable.age",
                "select Id from testTable where age = 10 union select Id from testTableRedundantIndex where age > 10",//union
                "select name from testTable order by age",//use tmp tree
                "select name from testTableRedundantIndex where gender=1 and age=5",//bigger index
                "select name, case when age>=18 then 'Adult' else 'child' end LifeStage from testTableRedundantIndex where age > 20 order by age,name,gender",
                "select name,age,gender from testTableRedundantIndex where age > 10 and age < 20 or id between 30 and 40 or id = 1000 ORDER BY name,age,gender desc limit 10 offset 2;",
                "select * from (select * from testTable where age = 18 order by age limit 10) as tb where age = 18 " +
                        "UNION select m.* from testTable AS m, testTableRedundantIndex AS c where m.age = c.age;",
                "SELECT name FROM testTable WHERE name not LIKE 'rt%' OR name LIKE 'rc%' AND age > 20 GROUP BY name ORDER BY age;",
                "SELECT id AS id_alias FROM testTable AS test_alias WHERE id_alias = 1 or id = 2",
                "SELECT name FROM testTable WHERE id = (SELECT id FROM testTableRedundantIndex WHERE name = 'hello world')",
                "SELECT * FROM testTable where name = 'rc' UNION SELECT * FROM testTableWithoutRowid UNION SELECT * FROM testTableAutoIncrement",
                "SELECT name FROM testTable WHERE AGE GLOB '2*';",
                "SELECT DISTINCT name FROM testTable GROUP BY name HAVING count(name) < 2;",
                "SELECT name FROM contact WHERE status = 2;",
                "select rowid from contact where name = 'rr' or age > 12",
                "select t1.name ,t2.age from testTable as t1,testTableRedundantIndex as t2 where t1.id = t2.id and (t1.age=23 or t2.age=12);",
                "select t1.name ,t2.age from testTable as t1,testTableRedundantIndex as t2 where t1.id = t2.id and (t1.age=23 and t2.age=12);",
                "select name,age from contact where name like 'w%' and age > 12",
                "select name,age from contact where name >= 'rc' and age&2=1",
                "select name,age from contact where name = 'r' or age > 12  or status = 1",
        };
        return list;
    }
复制代码

最终检测结果如下图:

共检查出31项建议项或者提示项,每个建议或提示点击后可以查看详细结果,详细结果页面会展示检查的sql语句,sql语句explain qurey plan结果,优化建议,部分详情页会有堆栈信息。如下图:

根据优化建议,我们可以知道SQLiteLint建议我们建立name和age的复合索引。

SQLiteLint 存在问题

  1. targetSdkVersion>=28 hook失效

    targetsdk>=28后,SqliteDebug属于Hide类型,反射无法访问。建议如果可以,暂时修改targetSdkVersion用以分析数据库,修改完后再改回来。

  2. 部分app接入后出现using JNI after critical get崩溃。经排查发现是项目中存在虚拟数据表,后面在Application的onCreate方法中初始化并启动SQLiteLint,再添加关注的数据库后解决,注意,这里不要添加含有虚拟表的数据库。

SQLiteLint源码分析

从启动SQLiteLintPlugin开始看起,

            SQLiteLintConfig config = initSQLiteLintConfig();
            SQLiteLintPlugin sqLiteLintPlugin = new SQLiteLintPlugin(config);
            builder.plugin(sqLiteLintPlugin);

            Matrix.init(builder.build());
            sqLiteLintPlugin.start();

    private static SQLiteLintConfig initSQLiteLintConfig() {
        try {
            /**
             *
             * HOOK模式下,SQLiteLint会自己去获取所有已执行的sql语句及其耗时(by hooking sqlite3_profile)
             * @see 而另一个模式:SQLiteLint.SqlExecutionCallbackMode.CUSTOM_NOTIFY , 则需要调用 {@link SQLiteLint#notifySqlExecution(String, String, int)}来通知
             * SQLiteLint 需要分析的、已执行的sql语句及其耗时
             * @see TestSQLiteLintActivity#doTest()
             */
            return new SQLiteLintConfig(SQLiteLint.SqlExecutionCallbackMode.HOOK);
        } catch (Throwable t) {
            return new SQLiteLintConfig(SQLiteLint.SqlExecutionCallbackMode.HOOK);
        }
    }
复制代码

在这里初始化了一个SQLiteLintConfig,设置了sql执行语句的分析回调模式为Hook方式。

    public SQLiteLintConfig(SQLiteLint.SqlExecutionCallbackMode sqlExecutionCallbackMode) {
        SQLiteLint.setSqlExecutionCallbackMode(sqlExecutionCallbackMode);
        sConcernDbList = new ArrayList<>();
    }
	
	//com.tencent.sqlitelint.SQLiteLint#setSqlExecutionCallbackMode
	public static void setSqlExecutionCallbackMode(SqlExecutionCallbackMode sqlExecutionCallbackMode){
        if (sSqlExecutionCallbackMode != null) {
            return;
        }

        sSqlExecutionCallbackMode = sqlExecutionCallbackMode;
        if (sSqlExecutionCallbackMode == SqlExecutionCallbackMode.HOOK) {
            // hook must called before open the database
            SQLite3ProfileHooker.hook();
        }
    }
复制代码

SQLiteLintConfig的构造方法主要就是设置了SqlExecutionCallbackMode,继续跟下去可以看到,如果是HOOK方式,则调用SQLite3ProfileHooker.hook()执行hook操作。hook方法内部最终会调用到com.tencent.sqlitelint.util.SQLite3ProfileHooker#doHook

    private static boolean doHook() {
		//hookOpenSQLite3Profile方法主要作用是将SQLiteDebug类中的DEBUG_SQL_TIME变量设置为true
        if (!hookOpenSQLite3Profile()) {
            SLog.i(TAG, "doHook hookOpenSQLite3Profile failed");
            return false;
        }
		//nativeDoHook是个native方法,hook住native层sqlite3_profile方法注册sql执行结果回调
        return nativeDoHook();
    }
复制代码

nativeDoHook 是native方法,先按下不表,继续来看SQLiteLintPlugin调用start方法之后做了什么。 com.tencent.sqlitelint.SQLiteLintPlugin#start

 @Override
    public void start() {
        super.start();
        if (!isSupported()) {
            return;
        }

        SQLiteLint.setReportDelegate(new IssueReportBehaviour.IReportDelegate() {
            @Override
            public void report(SQLiteLintIssue issue) {
                if (issue == null) {
                    return;
                }
				//issue上报
                reportMatrixIssue(issue);
            }
        });

        List<SQLiteLintConfig.ConcernDb> concernDbList = mConfig.getConcernDbList();
        for (int i = 0; i < concernDbList.size(); i++) {
            SQLiteLintConfig.ConcernDb concernDb = concernDbList.get(i);
            String concernedDbPath = concernDb.getInstallEnv().getConcernedDbPath();
            SQLiteLint.install(mContext, concernDb.getInstallEnv(), concernDb.getOptions());
			//设置sql语句不进行检查的白名单
            SQLiteLint.setWhiteList(concernedDbPath, concernDb.getWhiteListXmlResId());
			//设置检查项
            SQLiteLint.enableCheckers(concernedDbPath, concernDb.getEnableCheckerList());
        }
    }
复制代码

SQLiteLint检查项简单说明:

  1. ExplainQueryPlanChecker :在sqlite命令行中可通过explain query plan sql语句获取sqlite 特点SQL查询的策略或计划的高级描述,最重要的是可报告查询使用数据库索引的方式
  2. AvoidSelectAllChecker: sql语句使用select * 检查
  3. WithoutRowIdBetterChecker: sql语句建表时without rowid检查
  4. PreparedStatementBetterChecker: sql 语句PreparedStatement检查
  5. RedundantIndexChecker:冗余索引检查

具体检查项以及检查依据请查看Matrix Android SQLiteLint

SQLiteLint install后执行的操作:

	//com.tencent.sqlitelint.SQLiteLint#install
    public static void install(Context context, InstallEnv installEnv, Options options) {
        assert installEnv != null;
        assert sSqlExecutionCallbackMode != null
                : "SqlExecutionCallbackMode is UNKNOWN!setSqlExecutionCallbackMode must be called before install";

        options = (options == null) ? Options.LAX : options;

        SQLiteLintAndroidCoreManager.INSTANCE.install(context, installEnv, options);
    }
	
	
	//com.tencent.sqlitelint.SQLiteLintAndroidCoreManager#install
    public void install(Context context, SQLiteLint.InstallEnv installEnv, SQLiteLint.Options options) {
        String concernedDbPath = installEnv.getConcernedDbPath();
        if (mCoresMap.containsKey(concernedDbPath)) {
            SLog.w(TAG, "install twice!! ignore");
            return;
        }

        SQLiteLintAndroidCore core = new SQLiteLintAndroidCore(context, installEnv, options);
        mCoresMap.put(concernedDbPath, core);
    }

	//com.tencent.sqlitelint.SQLiteLintAndroidCore#SQLiteLintAndroidCore
    SQLiteLintAndroidCore(Context context, SQLiteLint.InstallEnv installEnv, SQLiteLint.Options options) {
        mContext = context;
        //初始化SQLiteLintInternal.db,用于存放检查发现的issue
        SQLiteLintDbHelper.INSTANCE.initialize(context);
        mConcernedDbPath = installEnv.getConcernedDbPath();
        mSQLiteExecutionDelegate = installEnv.getSQLiteExecutionDelegate();

        if (SQLiteLint.getSqlExecutionCallbackMode() == SQLiteLint.SqlExecutionCallbackMode.HOOK) {
            //hook sqlite3_profile api
            SQLite3ProfileHooker.hook();
        }
		//开启检查,下面会继续分析
        SQLiteLintNativeBridge.nativeInstall(mConcernedDbPath);

        //设置发现issue后的行为
        mBehaviors = new ArrayList<>();
        /*PersistenceBehaviour is a default pre-behaviour */
        mBehaviors.add(new PersistenceBehaviour());
        if (options.isAlertBehaviourEnable()) {
            mBehaviors.add(new IssueAlertBehaviour(context, mConcernedDbPath));
        }
        if (options.isReportBehaviourEnable()) {
            mBehaviors.add(new IssueReportBehaviour(SQLiteLint.sReportDelegate));
        }
    }
复制代码

上面所说的nativeDoHook如下:

   JNIEXPORT jboolean JNICALL Java_com_tencent_sqlitelint_util_SQLite3ProfileHooker_nativeDoHook(JNIEnv *env, jobject /* this */) {
        LOGI("SQLiteLintHooker_nativeDoHook");
        if (!kInitSuc) {
            LOGW("SQLiteLintHooker_nativeDoHook kInitSuc failed");
            return false;
        }
        loaded_soinfo* soinfo = elfhook_open("libandroid_runtime.so");
        if (!soinfo) {
            LOGW("Failure to open libandroid_runtime.so");
            return false;
        }
        if (!elfhook_replace(soinfo, "c", (void*)hooked_sqlite3_profile, (void**)&original_sqlite3_profile)) {
            LOGW("Failure to hook sqlite3_profile");
            elfhook_close(soinfo);
            soinfo = nullptr;
            return false;
        }
        elfhook_close(soinfo);
        soinfo = nullptr;

        kStop = false;

        return true;
    }
复制代码

重点关注elfhook_replace方法所做的事情,这里采用PLT(GOT) Hook的方式hook了系统android_runtime.so中的sqlite3_profile方法。

为什么hook了sqlite3_profile方法就可以达到我们的要求? 每个SQL语句完成后,将调用由sqlite3_profile注册的回调函数。配置文件回调包含原始语句文本以及该语句运行多长时间的挂钟时间的估计值.

再来看看hooked_sqlite3_profile中的逻辑:

    void* hooked_sqlite3_profile(sqlite3* db, void(*xProfile)(void*, const char*, sqlite_uint64), void* p) {
        LOGI("hooked_sqlite3_profile call");
        return original_sqlite3_profile(db, SQLiteLintSqlite3ProfileCallback, p);
    }
复制代码

hooked_sqlite3_profile中做的事情非常简单,使用原有sqlite3_profile方法并设置了一个SQLiteLintSqlite3ProfileCallback回调,因为这个SQLiteLintSqlite3ProfileCallback我们才可以拿到sqlite profile的结果。

    void Java_com_tencent_sqlitelint_SQLiteLintNativeBridge_nativeNotifySqlExecute(JNIEnv *env, jobject, jstring dbPath
            , jstring sql, jlong executeTime, jstring extInfo) {
        char *filename = jstringToChars(env, dbPath);
        char *ext_info = jstringToChars(env, extInfo);
        char *jsql = jstringToChars(env, sql);

        NotifySqlExecution(filename, jsql, executeTime, ext_info);

        free(jsql);
        free(ext_info);
        free(filename);
    }
复制代码

之前所说的SqlExecutionCallbackMode 一个是Hook,一个是NotifySqlExecution,可以看到sql语句执行后hook方式会主动调用NotifySqlExecution, 传入需要分析的、已执行的sql语句及其耗时参数,所以我们才不需要手动调用SQLiteLint#notifySqlExecution(String, String, int)来通知。

SQLiteLintNativeBridge.nativeInstall(mConcernedDbPath)调用了native方法nativeInstall

     void Java_com_tencent_sqlitelint_SQLiteLintNativeBridge_nativeInstall(JNIEnv *env, jobject, jstring name) {
        char *filename = jstringToChars(env,name);
        InstallSQLiteLint(filename, OnIssuePublish);
        free(filename);
        SetSqlExecutionDelegate(SqliteLintExecSql);
    }
	
	//sqlitelint::InstallSQLiteLint
    void InstallSQLiteLint(const char* db_path, OnPublishIssueCallback issue_callback) {
        LintManager::Get()->Install(db_path, issue_callback);
    }
	
	//LintManager::Install
	void LintManager::Install(const char* db_path, OnPublishIssueCallback issued_callback) {
        sInfo("LintManager::Install dbPath:%s", db_path);
        std::unique_lock<std::mutex> lock(lints_mutex_);
        std::map<const std::string, Lint*>::iterator it = lints_.find(db_path);
        if (it != lints_.end()) {
            lock.unlock();
            sWarn("Install already installed; dbPath: %s", db_path);
            return;
        }

        Lint* lint = new Lint(db_path, issued_callback);
        lints_.insert(std::pair<const std::string, Lint*>(db_path, lint));
        lock.unlock();
    }

复制代码

LintManager::Install方法中新建了一个Lint,在其构造方法开启了一个线程,死循环执行check任务。TakeSqlInfo不断从执行的sql语句中获取执行结果信息,当没有sql语句时会进入wait状态,当有sql语句执行完后,系统会调用sqlite3_profile,而我们在sqlite3_profile回调中执行了Lint::NotifySqlExecution 方法,这个方法会执行notify方法,使得重新进入运行状态,使用可用的检查器开始sql语句检查。

    void Lint::Check() {
        init_check_thread_ = new std::thread(&Lint::InitCheck, this);

        std::vector<Issue>* published_issues = new std::vector<Issue>;
        std::unique_ptr<SqlInfo> sql_info;
        SqlInfo simple_sql_info;
        while (true) {
            int ret = TakeSqlInfo(sql_info);
            if (ret != 0) {
                sError("check exit");
                break;
            }
            //sql语句计数
            env_.IncSqlCnt();
            //sql语句预处理,去除多余空格,sql语句转成小写
            PreProcessSqlString(sql_info->sql_);
            sDebug("Lint::Check checked cnt=%d", env_.GetSqlCnt());
            //是否支持检查,只支持select,update,delete,insert,replace类型的sql语句
            if (!IsSqlSupportCheck(sql_info->sql_)) {
                sDebug("Lint::Check Sql not support");
                env_.AddToSqlHistory(*sql_info);
                sql_info = nullptr;
                continue;
            }

            //预处理,按sql语句类别不同分别做处理
            if (!PreProcessSqlInfo(sql_info.get())) {
                sWarn("Lint::Check PreProcessSqlInfo failed");
                env_.AddToSqlHistory(*sql_info);
                sql_info = nullptr;
                continue;
            }

            sql_info->CopyWithoutParse(simple_sql_info);
            env_.AddToSqlHistory(simple_sql_info);

            published_issues->clear();

            //各类检查器检查sql语句
            ScheduleCheckers(CheckScene::kSample, *sql_info, published_issues);

            const std::string& wildcard_sql = sql_info->wildcard_sql_.empty() ? sql_info->sql_ : sql_info->wildcard_sql_;
            bool checked = false;
            if (!checked_sql_cache_.Get(wildcard_sql, checked)) {
                ScheduleCheckers(CheckScene::kUncheckedSql, *sql_info, published_issues);
                checked_sql_cache_.Put(wildcard_sql, true);
            } else {
                sVerbose("Lint::Check() already checked recently");
            }

            if (!published_issues->empty()) {
                sInfo("New check some diagnosis out!, sql=%s", sql_info->sql_.c_str());
                if (issued_callback_) {
                    //issue上报
                    issued_callback_(env_.GetDbPath().c_str(), *published_issues);
                }
            }

            sql_info = nullptr;
            env_.CheckReleaseHistory();
        }

        sError("check break");
        delete published_issues;
    }
复制代码

至此,SQLiteLint流程基本已经串通了。用两张图来说明整个流程:

初始化流程

sql检查流程

总结

通过Matrix SQLiteLint,可以让我们在开发、测试或者灰度阶段进行sql语句检查,可以写出更加高效性能更好的sql语句,特别是对数据库使用和优化没有太多经验的开发人员来说是非常有用的。当然,SQLiteLint也不是完美的,SQLiteLint检测只是建议性质,特别是关于索引的检测有可能会有误报,并且有些情况下是无法避免索引失效的,因此是否需要修改要结合实际情况。

参考文章: Query Planning Sqlite索引优化 数据库索引原理及优化 性能优化之数据库优化 索引在什么情况下会失效

关注下面的标签,发现更多相似文章
评论