在SQLite中做范围筛选时，IN和BETWEEN是最常用的两种写法。不少人对它们的理解停留在语法层面，觉得只是表达方式不同，结果一样，性能自然也差不了多少。实际上，SQLite查询规划器对这两种谓词的处理路径有明显差异：IN会被展开成一组等值约束，配合索引做多次精确跳转；BETWEEN会被改写成一对区间约束，在B树上做一次连续范围扫描。写法选错或者索引设计不合理，都可能让查询从走索引退化成全表扫描，数据量一大，耗时差距会从毫秒级拉大到秒级。
一、IN与BETWEEN在执行机制上的本质差异
要理解两种写法的性能差异，得先回到SQLite的索引结构。SQLite的索引默认是B树，索引列的值按序存放，叶子节点之间通过横向指针串联。等值查询可以沿着B树一路下钻直接命中目标；范围查询则先下钻到区间起点，再沿叶子节点横向遍历到区间终点。BETWEEN在解析阶段等价于created_at >= 1700000000 AND created_at <= 1700500000，规划器会把它识别为索引上的两个区间约束，一次下钻加一段连续扫描就能拿到全部结果，页面读取具有良好的局部性。
IN的处理方式则不同。规划器把IN列表看作一个取值集合，常见策略有两种：列表较短时，直接对每个值做一次独立的索引查找，相当于把一条查询拆成N次等值定位，每次定位都要从B树根节点下钻一遍；列表较长或来自子查询时，SQLite可能物化一个临时索引来加速集合匹配。这意味着IN的代价大致与列表长度成正比，列表里塞进几百个值时，几百次B树下钻的开销就不能忽视了。
-- 建一张订单表，准备测试数据
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
amount REAL,
created_at INTEGER NOT NULL
);
CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_orders_user ON orders(user_id);
-- BETWEEN：一次范围扫描
EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE created_at BETWEEN 1700000000 AND 1700500000;
-- 输出：SEARCH orders USING INDEX idx_orders_created (created_at>? AND created_at<?)
-- IN：每个值一次索引定位
EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE user_id IN (10, 20, 30, 40);
-- 输出：SEARCH orders USING INDEX idx_orders_user (user_id=?)
-- IN 子查询：物化结果集后匹配
EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE user_id IN (SELECT uid FROM vip_users WHERE level >= 3);| 对比维度 | IN | BETWEEN |
|---|---|---|
| 谓词形式 | 离散值集合 | 连续区间 |
| 索引利用方式 | 每个值一次等值定位 | 一次下钻加连续扫描 |
| 代价构成 | 与列表长度成正比 | 与区间内行数成正比 |
| 适用场景 | 少量离散取值 | 连续数值或时间区间 |
从执行计划能直观看出差别：BETWEEN对应的约束是区间形式，IN对应的约束是等值形式外加一层循环。结论也就清晰了，筛选取值是连续区间时用BETWEEN，一次扫描搞定；取值是少量离散值时用IN，几次精确定位搞定。反过来也能得到正确结果，但代价会放大：用IN罗列一个连续区间里的上千个值，或者用一长串OR去拼区间，都是典型的性能反模式。
二、用EXPLAIN QUERY PLAN确认索引是否生效
优化范围查询的第一步不是急着改SQL，而是确认当前写法到底走没走索引。SQLite提供了EXPLAIN QUERY PLAN命令，输出虽然只有一行文字，信息量却很大：SEARCH开头说明用上了索引，SCAN开头说明在做全表扫描或全索引扫描。对于范围查询，重点看括号里的约束描述，出现列名加问号的组合，说明该列上的约束被索引利用了；约束只出现在WHERE里而计划中没有体现，说明索引没帮上忙，需要回头检查写法或索引结构。
一个常见陷阱是隐式类型转换导致索引失效。比如索引列是INTEGER类型，查询时却传了字符串参数，或者反过来。SQLite的类型转换规则比较宽松，数值和文本之间可以自动转换，但转换发生在索引列上时，规划器无法再用原始索引做区间定位，只能退化为全表扫描。日期字段尤其容易踩这个坑：表里存的是Unix时间戳整数，查询却拿字符串去比较，表面上看语法没问题，执行计划却已经悄悄变成了SCAN。
-- 隐式类型转换导致索引失效的典型场景 EXPLAIN QUERY PLAN SELECT * FROM orders WHERE created_at BETWEEN '1700000000' AND '1700500000'; -- created_at 是 INTEGER，传入字符串后可能退化为 SCAN orders -- 正确做法：参数类型与列类型保持一致 EXPLAIN QUERY PLAN SELECT * FROM orders WHERE created_at BETWEEN 1700000000 AND 1700500000; -- SEARCH orders USING INDEX idx_orders_created (created_at>? AND created_at<?)
建议把执行计划检查纳入日常开发流程，凡是涉及范围筛选的SQL，上线前都跑一遍计划确认。命令行工具里可以执行.eqp on开启自动输出，之后每条查询都会附带执行计划，排查效率高很多。应用侧也可以在测试环境封装一个调试开关，把关键查询的计划输出到日志里，方便版本迭代时做回归对比。
三、索引设计：复合索引的列顺序决定范围查询效率
单列索引的场景比较简单，真正的难点在复合索引。范围查询经常和其他条件组合出现，比如查某个用户在某段时间内的订单，这时复合索引的列顺序至关重要。规则只有一条：等值条件的列放在前面，范围条件的列放在后面。原因是B树按索引列的先后顺序排序，前面的列被等值约束锁定后，后面的列在这段锁定范围内仍然有序，范围扫描可以继续利用这份有序性；反过来把范围列放前面，等值列的取值会散落在各个区间里，索引的过滤能力大打折扣。
-- 正确的复合索引：等值列在前，范围列在后 CREATE INDEX idx_orders_user_time ON orders(user_id, created_at); -- 完整利用索引：先定位 user_id，再在 created_at 上做范围扫描 EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 42 AND created_at BETWEEN 1700000000 AND 1700500000; -- 输出：SEARCH orders USING INDEX idx_orders_user_time -- (user_id=? AND created_at>? AND created_at<?) -- 只有范围条件时，前导列用不上，退化为全表扫描 EXPLAIN QUERY PLAN SELECT * FROM orders WHERE created_at BETWEEN 1700000000 AND 1700500000; -- 输出：SCAN orders
注意第二个例子：建了复合索引不等于所有查询都能受益。查询条件里没有前导列时，这个索引对范围筛选毫无帮助，此时要么补一个单列索引，要么调整查询让前导列参与过滤。索引也不是越多越好，每多一个索引，插入和更新都要多维护一棵B树，写放大和空间成本都需要按实际查询模式权衡。
另一个值得关注的点是覆盖索引。如果查询涉及的列全部包含在索引里，SQLite可以直接从索引取数，省掉回表查整行记录的开销。对于高频的范围统计查询，把SELECT的列收窄到索引覆盖的范围内，收益往往比换写法更大。比如统计某用户某段时间的订单总额，把user_id、created_at、amount三列做成复合索引，整个查询在索引上就能完成，速度比回表版本快一个量级。
四、进阶技巧与常见误区
第一点是IN列表的规模控制。IN适合十几个到几十个离散值，超过这个量级就要考虑换思路：值本身有规律时改写成BETWEEN，或者调整数据结构用关联查询表达。IN子查询的写法也要留意，SQLite会把子查询结果物化成临时表并构建索引再匹配，多数情况下这是合理的，但如果子查询本身很重，整个查询的耗时会被子查询主导，此时先在应用层算好值列表再传入，或者改用JOIN，可能更划算。
-- IN 子查询与 JOIN 的等价改写 SELECT o.* FROM orders o WHERE o.user_id IN (SELECT uid FROM vip_users WHERE level >= 3); SELECT o.* FROM orders o JOIN vip_users v ON v.uid = o.user_id WHERE v.level >= 3;
第二点是统计信息。SQLite规划器靠sqlite_stat1表里的统计数据估算各条查询路径的成本，数据分布倾斜时，缺少统计信息就可能出现选错索引的情况，比如明明有更合适的范围索引，规划器却估算出更低的全表扫描成本。定期在业务低峰执行一次ANALYZE，让规划器掌握真实的基数分布，范围查询的索引选择会稳定得多。数据量变化大的表尤其建议做，成本很低，收益直接。
第三点是日期时间的存储格式。SQLite没有原生日期类型，常见存法有三种：ISO格式的文本、Unix时间戳整数、Julian Day浮点数。从索引效率看，整数时间戳是最优解，比较操作是纯数值运算，存储紧凑，B树排序天然有序；文本格式虽然也能走索引，但要求格式严格统一，混入不同精度的字符串后区间比较容易出错；浮点数存在精度隐患，长时间跨度下累计误差不可忽视。统一用整数时间戳存时间，再配合BETWEEN做区间筛选，是经过大量实践检验的稳妥方案。
最后总结一下选择思路：IN和BETWEEN没有绝对的好坏，关键看取值特征是离散还是连续，索引是否按等值在前、范围在后的顺序设计，参数类型是否和列类型严格一致。养成先看执行计划再动手调优的习惯，配合覆盖索引、定期ANALYZE和合理的存储格式，千万级数据表上的范围查询基本都能稳定在毫秒级。