一、问题来源
我所就职的公司是一家医疗互联网企业,作为一名iOS开发,目前我参与开发迭代的是一款面向医生用户的APP,这款APP的主要功能之一就是随手记病历,前不久我们的运营同学接到用户反馈,在病历数量较多(达到数千份),一些操作十分卡顿。
这个问题很容易就解决了(一些耗时操作卡住了主线程),不过在解决这个问题的过程中,反而发现了更大了一个问题,在sqlite中iOS8和iOS9执行一些相同的查询语句,效率相差十分巨大!
二、问题表现
我使用了4000左右的数据量进行了测试,发现执行同一条包含子查询共四个在表在内的查询语句:
- 在iPhone6 Plus iOS8.0.2真机上面花费0.8秒左右的时间。
- 在iPhone6 iOS9.1真机上面花费48秒左右的时间。
三、问题定位
作为一名开发者,应该都会对这类怪异的问题感兴趣(难道是恶趣味),可能是解决这类问题比较有成就感吧。
我也不例外,我就又花费了一些时间尝试去查一些解决方案,遗憾的是,百度、google之后并没有找到太有用的东西,只在stackoverflow上面找到一个相同的问题,然而并没有人回答,只是本人通过修改SQL查询语句解决了在iOS9查询速度十分慢的问题。
1.总体问题
没办法,看来不能走捷径了,于是做了进一步测试,发现分别在iOS8和iOS9的真机上面执行一些查询语句:
- 单表查询数据量即使达到上万,执行相同的sqlite查询操作性能并没有明显的差别,耗时并不会很多(取决于查询条件和单表列数等)。
- 大部分多表查询在数据量大时(数千),性能并不会有明显差异。
- 部分多表查询数据量大时(数千),执行相同的sqlite查询操作性能上会有较大差别。
看来问题就出在多表查询上了。
2.具体问题
由于公司相关信息不便公开,这里以另外的类似场景举例做描述。
假如对全国的中学做了以下一些信息统计:学校信息、学校班级班级、班级学生信息,学生期末考试成绩信息。共使用4个数据表进行数据存储。
需要做的查询:列出所有——学校基本信息+学校平均成绩最好的一个班级+学校某年级总成绩最好的一名学生
发现的问题:原来的查询会在一条sql语句中完成,执行这条sql时在iOS8和iOS9中时间相差极大(如二、描述)。通过拆分发现,把查询“学校某年级总成绩最好的一名学生”的子查询拆分出去以后iOS8和iOS9的查询速度就接近了。
那么,看来查询“学校某年级总成绩最好的一名学生”的子查询就是问题的突破点了。
四、解决方案
我先后尝试了两种解决方案:
改变sql语句,做分表查询,内存组合数据;
不改变sql语句,添加索引,加快查询速度。
1.分表查询,内存组合数据(未选用)
在我们的APP中,在满足数据展示的前提下,我尝试将原来的一条查询拆分两个查询,分别对两个子查询的速度进行了测试,发现无论在iOS8或iOS9上面速度都是没问题的。
之所以放弃这个方案,是因为SQL执行速度的问题解决了,不过新的问题出现了,那就是内存组合数据的效率问题。
内存组合数据就是将两次的查询结果所得的两个model数组组合成一个新的model数组。
以id做关键字匹配,效率最低的情况是两次全量for循环嵌套即n²,实测两个4000的model数组组合花费了15s左右的时间,后来简单做了优化,降低到了10s左右。这也是不能接受的。
2.建立索引(选用)
作为一名客户端开发,我对数据库并没有深入的学习,对数据库的性能优化并不擅长,于是和一些对数据库知识有较深了解的同事讨论这个问题,一位同事提醒我,你可以试试建立索引。
于是,我试着给影响性能的那个表单个列建立了索引,实测,无论在iOS8还是iOS9执行速度上面都没有问题。
部分测试数据表:
Tip:首次查询需要建立数据库连接,耗时较多。
|
iPhone4s iOS 7.0.4 |
|
iPhone 6 Plus iOS 8.0.2 |
|
iPhone 6 iOS 9.2 |
|
第n次执行测试 |
添加索引前 |
添加索引后 |
添加索引前 |
添加索引后 |
添加索引前 |
添加索引后 |
1 |
8.077 |
7.824 |
2.216 |
2.049 |
49.183 |
1.969 |
2 |
3.256 |
2.730 |
0.798 |
0.768 |
48.019 |
0.503 |
3 |
3.230 |
2.629 |
0.793 |
0.650 |
48.443 |
0.501 |
4 |
3.317 |
2.628 |
0.823 |
0.658 |
47.888 |
0.564 |
5 |
3.249 |
2.627 |
0.842 |
0.646 |
48.530 |
0.460 |
6 |
3.262 |
2.629 |
0.812 |
0.649 |
48.479 |
0.504 |
部分测试代码:
NSString *sqlStr = @"SELECT ...";
sqlite3 *database = nil;
if (sqlite3_open([DatabasePath UTF8String], &database) == SQLITE_OK) {
sqlite3_stmt *statement = nil;
int status = sqlite3_prepare_v2(database, [sqlStr UTF8String], -1, &statement, NULL);
if (status == SQLITE_OK) {
NSInteger j = 0;
while (sqlite3_step(statement) == SQLITE_ROW) {
j++;
NSMutableArray *row = [[NSMutableArray alloc] init];
for(int i = 0; i <10; i++){
[row addObject:[NSString stringWithFormat:@"%s", sqlite3_column_text(statement, i)]];
}
}
} else {
NSLog(@"Error: failed to prepare");
}
sqlite3_finalize(statement);
} else {
sqlite3_close(database);
NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
}
sqlite3_close(database);
五、知识拓展1.如何添加索引
给某个表某列添加索引
CREATE INDEX Index_Name ON Table_Name (Column_Name);
给某个表添加唯一索引(不能有重复的记录)
CREATE UNIQUE INDEX Index_Name on Table_Name (Column_Name);
给某个表添加组合索引
CREATE INDEX Index_Name on Table_Name (Column_Name1, Column_Name2, ...);
2.可以给任意表添加索引么
使用索引时,应遵守的准则:
- 索引不应该使用在较小的表上。
- 索引不应该使用在有频繁的大批量的更新或插入操作的表上。
- 索引不应该使用在含有大量的NULL值的列上。
- 索引不应该使用在频繁操作的列上。
3.怎么使用索引
sqlite中(其它的没有做了解)不需要显式的用
SELECT ... FROM Table_Name INDEX BY ...
的语法去使用索引,假如你添加了索引,执行sql查询语句时会自动使用。
4.怎么看是否使用了索引
你可以使用这种方法查看你的sql语句是否使用了索引进行查询:
EXPLAIN QUERY PLAN SelectQueryString;
结果表样式
selectid |
order |
form |
detail |
如果你建立了索引,执行查询语句时使用到了索引时,detail列中结果样式
SEARCH TABLE Table_Name USING INDEX sqlite_autoindex_Table_Name_n (Column_Name=?) (~1 rows)
六、小结
iOS9中部分sql的执行速度异常慢的问题应该是apple的一个BUG,目前我暂未联系apple确定,可以考虑的解决方案就是在数据库搜索本身的性能上下功夫,假如你也遇到类似的问题,而且发现了更好的解决方案,希望可以评论告诉我。
参考:
- http://stackoverflow.com/questions/32781913/sqlite-running-some-queries-under-ios9-views-a-lot-slower-than-ios8
- http://blog.csdn.net/yanbober/article/details/45581751