如何优化大型SQLite数据库中多条件组合查询？

问题背景 #

我有一个约280GB的SQLite数据库（优先使用，同时有MySQL版本），存储企业财务信息。其中ItemValues表有约10亿行数据，所有单列都已创建索引。单条件查询Cost或Revenue时响应仅需毫秒，但同时查询满足特定Cost和Revenue条件的企业时，先分别查询再取交集的方式会返回海量数据，效率极低。目前仅需查询Cost和Revenue，但后续可能扩展查询字段，希望优先优化现有结构，或了解表设计调整的可行方案。

现有表结构及查询示例 #

CREATE TABLE ItemTypes (
    Id INTEGER PRIMARY KEY,
    ItemShortDescription TEXT
);
CREATE INDEX idx_ItemTypes_ItemShortDescription ON ItemTypes (ItemShortDescription);
INSERT INTO ItemTypes (Id, ItemShortDescription) VALUES (1, 'Cost');
INSERT INTO ItemTypes (Id, ItemShortDescription) VALUES (2, 'Revenue');
INSERT INTO ItemTypes (Id, ItemShortDescription) VALUES (3, 'SomeOtherFinancialMetric1');
INSERT INTO ItemTypes (Id, ItemShortDescription) VALUES (4, 'SomeOtherFinancialMetric2');

CREATE TABLE ItemValues (
    CompanyId TEXT,
    ItemTypeId INTEGER,
    NumericValue INTEGER,
    DateEpoch INTEGER
);
CREATE INDEX idx_ItemValues_CompanyId ON ItemValues (CompanyId);
CREATE INDEX idx_ItemValues_ItemTypeId ON ItemValues (ItemTypeId);
CREATE INDEX idx_ItemValues_NumericValue ON ItemValues (NumericValue);
CREATE INDEX idx_ItemValues_DateEpoch ON ItemValues (DateEpoch);

INSERT INTO ItemValues (CompanyId, ItemTypeId, NumericValue, DateEpoch) VALUES ('AB1234', 1, 100, 1569884400);
INSERT INTO ItemValues (CompanyId, ItemTypeId, NumericValue, DateEpoch) VALUES ('AB1234', 2, 200, 1569884400);
INSERT INTO ItemValues (CompanyId, ItemTypeId, NumericValue, DateEpoch) VALUES ('G17895', 7, 50, 1632956400);

-- 现有查询示例
WITH salesIdsCTE AS (
    SELECT Id 
    FROM ItemTypes 
    WHERE ItemShortDescription = 'Cost' Or ItemShortDescription = 'Revenue'
),
filteredReportItems AS (
    SELECT * 
    FROM ItemValues 
    WHERE ItemTypeId IN (SELECT Id FROM salesIdsCTE)
    AND NumericValue > 5
)
SELECT *
FROM filteredReportItems
LIMIT 5;

优化方案 #

1. 复合索引优化（优先推荐，无需修改表结构） #

现有索引均为单列，无法在多条件查询中高效发挥作用。针对同时查询Cost和Revenue的场景，创建覆盖型复合索引：

• 为ItemValues表创建(CompanyId, ItemTypeId, NumericValue)复合索引：

  CREATE INDEX idx_ItemValues_Company_ItemType_Value ON ItemValues (CompanyId, ItemTypeId, NumericValue);
  

  该索引可让数据库快速定位到某企业的特定指标（Cost/Revenue）及其数值，避免回表查询，同时在关联查询时直接通过索引过滤数据。
• 若需按时间维度筛选，可扩展为(CompanyId, DateEpoch, ItemTypeId, NumericValue)，适配带时间条件的多指标查询。

2. 重构查询语句（替代分查取交集） #

放弃先查Cost再查Revenue取交集的方式，改用自关联+分组过滤，直接在数据库层面完成条件校验：

-- 查询同时满足Cost>100且Revenue>200的企业及对应数据（含时间匹配）
SELECT iv1.CompanyId, iv1.NumericValue AS Cost, iv2.NumericValue AS Revenue, iv1.DateEpoch
FROM ItemValues iv1
JOIN ItemValues iv2 
  ON iv1.CompanyId = iv2.CompanyId 
  AND iv1.DateEpoch = iv2.DateEpoch
WHERE iv1.ItemTypeId = (SELECT Id FROM ItemTypes WHERE ItemShortDescription = 'Cost')
  AND iv1.NumericValue > 100
  AND iv2.ItemTypeId = (SELECT Id FROM ItemTypes WHERE ItemShortDescription = 'Revenue')
  AND iv2.NumericValue > 200;

若仅需获取符合条件的企业ID，可使用分组聚合方式：

SELECT CompanyId, DateEpoch
FROM ItemValues
WHERE ItemTypeId IN (1,2)
  AND ((ItemTypeId=1 AND NumericValue>100) OR (ItemTypeId=2 AND NumericValue>200))
GROUP BY CompanyId, DateEpoch
HAVING COUNT(DISTINCT ItemTypeId) = 2;

这种方式利用复合索引直接过滤分组，避免在内存中处理海量交集数据。

3. 表结构调整（适合长期扩展多指标查询） #

如果后续需要频繁查询多指标组合，可考虑宽表结构（行转列），需权衡写入性能与存储成本：

-- 新建宽表示例（按企业+时间聚合）
CREATE TABLE CompanyFinancials (
    CompanyId TEXT,
    DateEpoch INTEGER,
    Cost INTEGER,
    Revenue INTEGER,
    SomeOtherFinancialMetric1 INTEGER,
    SomeOtherFinancialMetric2 INTEGER,
    PRIMARY KEY (CompanyId, DateEpoch)
);

-- 定期从ItemValues同步数据（可通过触发器或定时任务）
INSERT OR REPLACE INTO CompanyFinancials (CompanyId, DateEpoch, Cost, Revenue)
SELECT 
    CompanyId,
    DateEpoch,
    MAX(CASE WHEN ItemTypeId=1 THEN NumericValue END) AS Cost,
    MAX(CASE WHEN ItemTypeId=2 THEN NumericValue END) AS Revenue
FROM ItemValues
WHERE ItemTypeId IN (1,2)
GROUP BY CompanyId, DateEpoch;

宽表结构下，多条件查询直接变为单表过滤，性能大幅提升，但写入时需额外聚合操作，适合读多写少的场景。

4. SQLite特定优化 #

• 开启PRAGMA journal_mode=WAL：提升并发读写性能，适配大数据量场景。
• 调整PRAGMA cache_size：增大缓存减少磁盘IO，例如设置PRAGMA cache_size=2000000（约2GB缓存，根据可用内存调整）。
• 避免使用SELECT *：仅查询所需字段，减少数据传输与内存占用。

内容的提问来源于stack exchange，提问作者LePrinceDeDhump