SQLite

Sqlite 性能调优笔记

Posted by KingXt on February 1, 2017

Sqlite 性能调优笔记

1 索引优化

现在有一个表如下面字段所示，uuid是存储纯文本，cursor是时间戳，status和direction是枚举值

CREATE TABLE IF NOT EXISTS test (
  uuid text PRIMARY KEY,
  cursor integer NOT NULL DEFAULT(-1),
  status integer NOT NULL DEFAULT(-1),
  direction integer NOT NULL DEFAULT(-1)
);

现在表里面有5万数据，5万数据在移动客户端算是比较多的，假如有一条如下需求的sql。

SELECT `uuid` FROM `test` WHERE `cursor`>=1484921122571 AND `status`=2

如果任何索引都没有的话就是一个全表查询，用iPhone7 plus测试需要68ms(如测试数据第10行所示),如果App对性能要求比较严格的话，这个是蛮耗时的，如果运行在更慢的手机上会需要更长时间。现在对sql做如下测试：

- (void)createIndex {
    NSString *sql = @"CREATE INDEX IF NOT EXISTS test_status ON test (status);";
    [self executeSQL:sql printSql:YES];
    sql = @"CREATE INDEX IF NOT EXISTS test_cursor ON test (cursor);";
    [self executeSQL:sql printSql:YES];
    sql = @"CREATE INDEX IF NOT EXISTS test_direction ON test (direction);";
    [self executeSQL:sql printSql:YES];
}

- (void)createMixIndex {
    NSString *sql = @"CREATE INDEX IF NOT EXISTS test_status_cursor ON test (status, cursor);";
    [self executeSQL:sql printSql:YES];
}

- (void)dropIndex {
    [self executeSQL:@"drop index IF EXISTS test_status" printSql:YES];
    [self executeSQL:@"drop index IF EXISTS test_cursor" printSql:YES];
    [self executeSQL:@"drop index IF EXISTS test_direction" printSql:YES];
}

- (void)dropMixIndex {
    [self executeSQL:@"drop index IF EXISTS test_status_cursor" printSql:YES];
}

- (void)executeQueryTest {
    NSString *sql = @"SELECT `uuid` FROM `test` WHERE `cursor`>=1484921122571 AND `status`=2";
    [self executeSQL:sql printSql:YES];
}

第一个测试方案是对status、cursor两个字段单独建立索引，然后执行查询，测试结果需要26毫秒(如测试数据第18行所示)，因为status是枚举型值，建立索引对数据查询性能提高并不高，但为什么没有用到cursor索引了？这个可以通过SQLite的explain query工具发现。

EXPLAIN query plan SELECT `uuid` FROM `test` WHERE `cursor`>=1484921122571 AND `status`=2

SEARCH TABLE test USING INDEX test_status (status=?)

这个查询只用到了status索引而并没有用到cursor索引，Sqlite会在where clause中选择索引列，Sqlite选择索引列的原理是此索引能够最快缩小结果集，比如等于是缩小结果集最快的列。如果两个结果集都是非等于，那么Sqlite会选择重复列少的作为索引列。为了加快此语句查询速度，我们可以建立联合索引， CREATE INDEX IF NOT EXISTS test_status_cursor ON test (status, cursor); 在status和cursor两个字段上建立联合索引会发现此Sql查询性能大大提升，查询只需要1.5ms(如测试数据第23行所示)。

1 2017-01-31 16:55:48.273833 Sqlite[1568:546665] execute sql: drop index IF EXISTS test_status_cursor
2 2017-01-31 16:55:48.273899 Sqlite[1568:546665] Time: 0.014154
3 2017-01-31 16:55:48.286532 Sqlite[1568:546665] execute sql: drop index IF EXISTS test_status
4 2017-01-31 16:55:48.286582 Sqlite[1568:546665] Time: 0.012660
5 2017-01-31 16:55:48.298139 Sqlite[1568:546665] execute sql: drop index IF EXISTS test_cursor
6 2017-01-31 16:55:48.298191 Sqlite[1568:546665] Time: 0.011583
7 2017-01-31 16:55:48.307218 Sqlite[1568:546665] execute sql: drop index IF EXISTS test_direction
8 2017-01-31 16:55:48.307286 Sqlite[1568:546665] Time: 0.009066
9 2017-01-31 16:55:48.375384 Sqlite[1568:546665] execute sql: SELECT `uuid` FROM `test` WHERE `cursor`>=1484921122571 AND `status`=2
10 2017-01-31 16:55:48.375426 Sqlite[1568:546665] Time: 0.068109
11 2017-01-31 16:55:48.438670 Sqlite[1568:546665] execute sql: CREATE INDEX IF NOT EXISTS test_status ON test (status);
12 2017-01-31 16:55:48.438761 Sqlite[1568:546665] Time: 0.063302
13 2017-01-31 16:55:48.548882 Sqlite[1568:546665] execute sql: CREATE INDEX IF NOT EXISTS test_cursor ON test (cursor);
14 2017-01-31 16:55:48.549020 Sqlite[1568:546665] Time: 0.110194
15 2017-01-31 16:55:48.668493 Sqlite[1568:546665] execute sql: CREATE INDEX IF NOT EXISTS test_direction ON test (direction);
16 2017-01-31 16:55:48.668580 Sqlite[1568:546665] Time: 0.119491
17 2017-01-31 16:55:48.695151 Sqlite[1568:546665] execute sql: SELECT `uuid` FROM `test` WHERE `cursor`>=1484921122571 AND `status`=2
18 2017-01-31 16:55:48.695257 Sqlite[1568:546665] Time: 0.026612
19 2017-01-31 16:55:48.695579 Sqlite[1568:546665] execute sql: drop index IF EXISTS test_status_cursor
20 2017-01-31 16:55:48.695634 Sqlite[1568:546665] Time: 0.000325
21 2017-01-31 16:55:48.810500 Sqlite[1568:546665] execute sql: CREATE INDEX IF NOT EXISTS test_status_cursor ON test (status, cursor);
22 2017-01-31 16:55:48.810547 Sqlite[1568:546665] Time: 0.114871
23 2017-01-31 16:55:48.810699 Sqlite[1568:546665] execute sql: SELECT `uuid` FROM `test` WHERE `cursor`>=1484921122571 AND `status`=2
24 2017-01-31 16:55:48.810711 Sqlite[1568:546665] Time: 0.000153

联合索引需要注意点是前导列。例如create index idx ON table1(a, b, c)创建索引，那么 a, ab, abc 都是前导列，而 bc，b，c 这样的就不是。在 where clause中，前导列必须使用等于或者 in 操作，最右边的列可以使用不等式，这样索引才可以完全生效。如果前面建立索引方式是CREATE INDEX IF NOT EXISTS test_status_cursor ON test (cursor, status);这种方式，那查询时候是不会用到此联合索引的，因为where clause中cursor不是等于或者in操作。

经常使用Sqlite的玩家，还需需要仔细阅读下https://www.sqlite.org/queryplanner.html#searching这篇文章，这个官方文章很有帮助。