有钱android索引优化之analyze命令

一个简单的查询语句

从SQLite查询一个时间段内的账单,查询sql、执行结果如下图:

 

如上图,返回699行账单数据,sql执行时间731ms。

查询前执行SQLite analyze语句,如下图:

如上图,同样的查询语句,同样的结果,仅仅查询前运行了analyze命令,查询时间却只有21ms,后者的性能几乎是前者的40倍!

查询计划

为了弄清以上sql执行时间差异的原因,执行一下SQLite的query plan。

无analyze的query plan如下:

 

加上analyze的query plan如下:

 

其中,TB_MONEY_FUNDTRANS_deleteTime_idx索引对应deleteTime,deleteTime默认值是0,但是当删除账单时,deleteTime值为账单删除时间;TB_MONEY_FUNDTRANS_showTime_idx对应showTime的索引,表示账单的显示时间。

从query plan上看,没有执行analyze语句走的是TB_MONEY_FUNDTRANS_deleteTime_idx索引,执行analyze之后走的是TB_MONEY_FUNDTRANS_showTime_idx索引。 因为我们执行的sql语句是showTime的一段时间,业务上账单的showTime几乎没有相同的,但是很多账单的deleteTime是默认值即0,所以走showTime的索引是最高效的。

SQLite analyze command

SQLite中的analyze命令用于分析数据表和索引中的数据,并将统计结果存放于SQLite的内部系统表中,以便于查询优化器可以根据分析后的统计数据选择最优的查询执行路径,从而提高整个查询的效率。

第一次运行analyze命令,SQLite会创建sqlite_stat1表:create table sqlite_stat1(tbl, idx, stat),其中tbl是表名,idx是索引的名字,stat则是索引对应的统计信息(包含两个integer的字符串),如下图,有钱账单TB_MONEY_FUNDTRANS表对应的索引统计信息:

如上图number 55,showTime索引对应的统计信息是“200811 2”,其中第一个数字200811表示table的行数,第二个数字表示给定索引的一个值可能返回多少行数据。假如k表示table对应的行数,d对应索引列table不同值的数量,则第二个数等于k/d(向上取值)。

从上面的分析可以得到,stat第一个数与第二个数的比值越大,索引对应的区分度越大。SQLite的查询优化器实际就是根据这个比值来选择最优的索引的。

总结

通过以上过程,可以得出两个结论:

1、根据analyze语句生成的统计数据删除无效索引。无效或者说索引区分度不大的索引,不仅不能提升db读速度,而且增加读写db的时间、占用外存空间。

2、执行analyze语句可以保证SQLite查询的时候走最优的索引,极大的提升查询速度。


本文来自网易实践者社区,经作者王朋授权发布。