优化android sqlite的SQL语句

Question

我正在开发一个应用程序来跟踪用户的当前位置并将其存储到 SQLite 数据库中。一切正常，但现在我在查询数据库中查找超过 1000 条记录的曲目时遇到问题，需要近 1.5 分钟。在我的桌面上只需要 1 秒。

我知道这是一个包含许多子选择的查询，但我无法以其他方式获得正确的结果。在我看来，这属于像 avg() 和 sum() 这样的聚合函数。

这是我的查询：

Cursor c = readableDB
    .rawQuery(
            "SELECT DISTINCT t._id , title , strftime('%Y-%m-%d' , starttime , 'unixepoch' , 'localtime') as date , description, "
            + "round((SELECT sum(distToPrev)/1000 FROM positions p WHERE p.trackid=t._id) , 2) as distance , " 
            + "(SELECT count(latitude) FROM positions p WHERE p.trackid=t._id) as waypoints, "
            + "(SELECT (avg(speed)*3.6) FROM positions p WHERE p.trackid=t._id) as avgspeed, "
            + "(SELECT (max(speed)*3.6) FROM positions p WHERE p.trackid=t._id) as maxspeed, "
            + "(SELECT sum(altitudeUP) FROM positions p WHERE p.trackid=t._id) as climb , "
            + "(SELECT avg(heartbeat) FROM heartbeats h WHERE h.trackid=t._id) as avgheartbeat , "
            + "(SELECT max(heartbeat) FROM heartbeats h WHERE h.trackid=t._id) as maxheartbeat , "
            + "(SELECT avg(cadence) FROM heartbeats h WHERE h.trackid=t._id) as avgcadence "
            + "FROM tracks t LEFT JOIN heartbeats h ON t._id = h.trackid WHERE t._id = ?",
            new String[]{String.valueOf(trackId)}); 
    c.moveToFirst();

如何优化此查询？我已经尝试过这种方式，但结果是错误的，并且花费了相同的时间。

SELECT t._id , title , strftime('%Y-%m-%d' , starttime , 'unixepoch' , 'localtime') as date , description,
sum(distToPrev)/1000 as distance ,
count(latitude) as waypoints, 
(avg(speed)*3.6) as avgspeed, 
(max(speed)*3.6) as maxspeed, 
sum(altitudeUP) as climb , 
avg(heartbeat)  as avgheartbeat ,
max(heartbeat)  as maxheartbeat ,
avg(cadence) as avgcadence 
FROM tracks t 
LEFT JOIN heartbeats h ON t._id = h.trackid 
INNER JOIN positions p ON t._id = p.trackid 
     WHERE t._id = ?

两个小时以来我一直在寻找解决方案，但我不知道我做错了什么。也许我必须休息一下。

编辑：

这里是我的创建语句：

CREATE TABLE heartbeats(_id INTEGER PRIMARY KEY AUTOINCREMENT, trackid INTEGER NOT NULL, heartbeat INTEGER NOT NULL, cadence INTEGER, timestamp TIMESTAMP);


CREATE TABLE positions(_id INTEGER PRIMARY KEY AUTOINCREMENT, trackid INTEGER NOT NULL, longitude REAL NOT NULL, latitude REAL NOT NULL, altitude REAL, altitudeUP REAL, speed REAL, accuracy REAL, distToPrev REAL, timestamp TIMESTAMP);


CREATE TABLE tracks(_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, description TEXT, starttime DATETIME NOT NULL, endtime DATETIME);

Answer 1

不要过度简化，但我认为您可以通过简单地对查询期间扫描的字段建立索引来节省大量查询时间。

通过对字段建立索引，您将减轻随着记录数量的增长而减慢查询速度的所有表扫描。

索引的缺点是需要空间，但根据我在 Android 上的经验，这很少是一个因素。

Answer 2

这将完成这项工作：

SELECT tid, date, waypoints, avg(heartbeat) AS avgheartbeat 
FROM (SELECT t._id AS tid, strftime('%Y-%m-%d' , starttime , 'unixepoch' , 'localtime') AS date, count(latitude) AS waypoints
         FROM tracks t LEFT JOIN positions p ON t._id = p.trackid  
         WHERE t._id = ?
         GROUP BY t._id , strftime('%Y-%m-%d' , starttime , 'unixepoch' , 'localtime'))
    LEFT JOIN heartbeats h ON tid = h.trackid
GROUP BY tid, date, waypoints

根据需要添加字段。

Answer 3

像这样的事情可能会让你接近。请注意，我已在查询中多次包含该参数：

SELECT
    T._id,
    T.title,
    STRFTIME('%Y-%m-%d' , T.starttime , 'unixepoch' , 'localtime') as date,
    T.description,
    P_SQ.distance,
    P_SQ.waypoints,
    P_SQ.avg_speed,
    P_SQ.max_speed,
    P_SQ.climb
FROM
    Tracks T
LEFT OUTER JOIN
(
    SELECT
        P.trackid,
        SUM(P.distToPrev) AS distance,
        COUNT(P.latitude) AS waypoints,
        AVG(P.speed) * 3.6 AS avg_speed,
        MAX(P.speed) * 3.6 AS max_speed,
        SUM(altitudeUp) AS climb
    FROM
        Positions P
    WHERE
        P.trackid = ?
    GROUP BY
        P.trackid
) P_SQ ON P_SQ.trackid = T._id
LEFT OUTER JOIN
(
    SELECT
        H.trackid,
        AVG(heartbeat) AS avg_heartbeat,
        MAX(heartbeat) AS max_heartbeat,
        AVG(cadence) AS avg_cadence
    FROM
        Heartbeats
    WHERE
        H.trackid = ?
    GROUP BY
        H.trackid
) H_SQ ON H_SQ.trackid = T._id
WHERE
    T._id = ?