时间范围查询如何优化？

[myd]

以用户上线 /下线记录表为例：

CREATE TABLE `online_log` (
  `id` int(11) AUTO_INCREMENT,
  `user_id` int(11),
  `online_time` datetime COMMENT '上线时间',
  `offline_time` datetime COMMENT '下线时间',
  PRIMARY KEY (`id`)
);

查询某个时间点在线的用户：

select user_id from online_log where "2021-01-23 12:00:00" between online_time and offline_time;

存在的问题：

1. 加联合索引(online_time, offline_time)显然不行，用不上；
2. 加 2 个普通索引 online_time 和 offline_time，只能用一个，也有可能都用不上，扫描的记录很多，速度慢。

如何优化？

[Altale]

方案没有，但是提个小建议，第二点问题可能是错的

“加 2 个普通索引 online_time 和 offline_time，只能用一个，也有可能都用不上”

这个方案最理想的情况是两个索引都使用上，最终取交集，具体情况要看 DBMS 的统计数据认为交集的方案开销是否比其他查询路径小。

另一个小建议是如果你这个表很大，还有其他字段，那么加这两个索引：
- online_time - user_id
- offline_time - user_id

将取交集的操作继续缩小为不需要回表的方案，性能可能有大幅提升（安利测试看看）

[rund11]

多加几个字段年月日，时间点，多个索引会快

[qiayue]

用时间戳会不会好一点呢

[JustLookBy]

联合索引怎么会用不上。
查询的时候 online_time<'' and offline_time>'' 不就是正常联合索引的操作吗？

[546L5LiK6ZOt]

我理解联合索引用不上，是因为两个字段的比较是不一样的，一个大于一个小于（ online_time <= "2021-01-23 12:00:00" and offline_time >= "2021-01-23 12:00:00"）。那么可以来个骚操作，变成一样的。假设 online_time 和 offline_time 都是存时间戳，但 offline_time 存的是时间戳的负数。那么条件就变成了 online_time <= 时间戳 and offline_time <= 时间戳的负数 。这样子就可以建联合索引了。

[myd]

@Altale 是的，数据量小会取交集。但是数据量大的时候，很可能就是全表扫描。MySQL 数据库

[myd]

@rund11 但是上线时间和下线时间可能跨日，甚至跨月，跨年

[myd]

@JustLookBy
@546L5LiK6ZOt
联合索引用不上，是因为，根据最左前缀原则，只要有一个索引字段使用了范围查询(>、<、<=、>=)，后面的索引字段就不生效了。

[Huelse]

两个日期之间的天数用 DATEDIFF()怎么样？

[Huelse]

还有什么 timediff 、datediff 、timestampdiff，可以测试下性能

[Altale]

@myd 那提供的条件太有限了，无法知道具体的查询 pattern，范围，这些建议都给不了

[Altale]

其实让数据库执行你的查询，同一条 sql 在数据分布不同的情况下执行路径也会大有不同，问题太过 general 了，问人还不如相信数据库的分析

[JustLookBy]

@myd 我想当然了，复合索引确实不能用
你这问题可以用空间索引 spatial index 来解决。
具体方案如下：
1. 去掉 online offline 俩个字段，用 online_range 来表示在线时间访问，type 为 linestring.
2. 加入索引 online_range, index type 设为 spatial
3. 用 int 表示时间，自己设定一个开始时间 为 0，这个方案如果精确度到秒，那区间只能有十几年。2^32/86400/365
4. 插入数据 `insert log (online_range) values (ST_GeomFromText('LINESTRING(online_timestamp 0,offline_timestamp 0)')))。 这里你的情况只要线段范围就行，所以 y 左边都设为 0 即可。
5. 查询数据 ` select * from log where
MBRContains(online_range,ST_GeomFromText('point(28302301 0)'))`

[myd]

@JustLookBy

有点像 online_time 和 offline_time，数据量少的时候可以使用索引。

数据量 100w 时，也是全表扫描。SQL:
```sql
EXPLAIN
select * from online_log where
MBRContains(online_range,ST_GeomFromText('point(1614152940 0)'));

+----+-------------+------------+------------+------+--------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+--------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | online_log | NULL | ALL | index_online_range | NULL | NULL | NULL | 1343396 | 31.03 | Using where |
+----+-------------+------------+------------+------+--------------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
```
这条 SQL 最后筛选出来的数据大约 100 条。


优化过程：
```
......
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "index_online_range",
"ranges": [
"online_range unprintable_geometry_value"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 425368,
"cost": 510443,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
......
```

[JustLookBy]

@myd 不能直接用时间戳，值大于 2^32 就不能用到索引了。 我在第三点里面说了，但是没说清
```自己设定一个开始时间 为 0，这个方案如果精确度到秒，那区间只能有十几年。```
你百万数据查询耗时多少？我这是一秒内，结果是十万条左右

[myd]

@JustLookBy 现在我用的时间戳是 4 字节的 int，没有超过 2^32 。百万数据 1 秒是正常的，全表扫描就是这个速度。