27 MB ，快赶上 QT 了。。

另外，占用 100 MB 内存，快赶上 Electron 应用了。。

@weijancc #20 好奇你原来的 SQL 是啥？ 50W 数据量要 7 秒？

@la0wei #21

## 1. MySQL 提升巨大

回头看了看，突然发现，会不会是你的索引不是覆盖索引，所以取整行数据时（你 SELECT * 了），都要回表去取？

恐怖地说，你回表了 180W 次？*（当然，其中应该有很多都被 MySQL 缓存下来了，实际没有去硬盘读取）*

## 2. SQLite 用了内存数据库，还是很慢

> 用 sqlite 读数据库文件进内存查询，速度不够快，单条查询在 0.5S 的样子，是因为读取数据时没有读取索引吗

那估计就是索引不对了。没看到具体代码，只能这么猜。

> 早上测试多个查询，初始需要读取整个数据库，首次查询较慢，后面速度就快多了。不过速度似乎是没有 mysql 快的，索引的效果看来是很好的

不知道你的**等价**表结构、索引、查询语句，不好判断。

我印象中，本地非并发写场景，SQLite 一般可以比 MySQL 快几倍，甚至 10 倍都有。

*（我有个 [关于树形结构存储的帖子]( https://v2ex.com/t/889443#reply21) 有这两者的速度对比）*

## 3. sqlite 读入内存查询和常规的方法速度比较

我把 16 楼的代码改造了下，随机生成完 1 亿行数据后，保存到机械硬盘（文件大小 2.12 GB ）。

然后关闭内存数据库，再重新打开机械硬盘上的数据库文件，查询 2000 行 9 个字段。结果用了 48 秒。（ SQLite 内存缓存大小，是默认的 2 MB ）

所以，真没必要用内存数据库。占内存大（ 2.5 GB ），也没提速多少。

这也是一般情况下，对于你的需求，我目前能想出的办法，一分钟统计一亿行左右。

如果你每个用户的订单不多的话，加个 force index(idx_orders_user_id)，应该能极大提速。

感觉 idx_orders_order_status 、idx_orders_status 、idx_orders_create_time 是没啥用的索引（要用它们，一般都会大量回表），建议删除

想更快一些，应该建个联合索引，甚至覆盖索引

@qq565425677 #43 我是 ROOT 后，用 Linux Deploy ，装了个 Debian 用。

咋说呢，安卓本身有很多省电机制。

去除了限制（如锁屏后保持 CPU 唤醒、WIFI 退出省电模式等）后，性能就差不多了。

再关掉安卓绝大部分服务，让出占用的几 GB 内存出来，就很像一个小服务器了。

如果要用 Docker ，那就要刷其他 Linux 发行版了。比如好像有个 PostMarketOS （我没用过，就不发表意见了）

但还有个问题，一直充电会导致电池膨胀。要是电路能像笔记本那样，优先直接供电，就好了

@qq565425677 #31 如果用不到 GPIO ，我感觉旧手机比树莓派香多了。。

300 块的二手小米 8 ，骁龙 845……

大家都用树莓派干啥呀？

如果只是当个小服务器，感觉性价比太低了。。

@la0wei #17

我也不知道你实际执行的 SQL 是啥，不太好判断延迟从何而来。。

# 1. 你不需要切换到 SQLite

我用 SQLite 是出于演示目的，而且本身也比较便捷。

另外，其功能少，也容易改造成其他数据库的 SQL 。

你可以参考 16 楼的 SQL 里的思想 *（扫一遍，就算出 6 个字段的结果）* ，稍加改造，应用到 MySQL 上。

*（虽然我认为，单机非并发写场景，SQLite 会比 MySQL 快。。）*

# 2. 16 楼的 SQL 对于机械硬盘都还算友好，不需要用内存数据库

我用内存数据库，主要是因为建表时，有大量随机写入。真的写到硬盘上，太慢了。

实际你的数据应该有很多是顺序写入的 *（你的日期是自增的）*。

另外，在计算时，实际是顺序读取了 2000 次、三个月内的覆盖索引记录。

所以，对于机械硬盘都还算友好，根本不需要用到内存数据库。

从这方面说，你的索引应该建成：

```sql
CREATE INDEX idx ON data(devicecode, date, "其他有可能用到的字段，防止回表")
```

@la0wei #15 装个[油猴插件]( https://greasyfork.org/zh-CN/scripts/449771-v2ex 评论 markdown 支持)，就能渲染 markdown 内容了。

## 1. 为什么你的 network 时间这么长？

我认为，主要是你把数据库里的内容，全部传输到 Python ，导致长时间耗时在数据传输上。

所以，让数据库自己计算出最终结果，再保存成 csv ，应该能节省很多时间。

*（除非数据库计算性能太差，还不如传数据让 Python 来）*

## 2. SQLite 可在 30 秒内，处理一亿数据，生成所需 CSV （ 9 字段，2000 行）

### 2.1 说明

我用 SQLite 进行了下测试。预先生成**一亿**行随机数据。各字段取值范围：

- **devicecode**：[1000001, 1002000]
- **date**：[2023-01-01 00:00:00, 2023-06-25 00:00:00]
- **code**：[9520, 9529]
- **status**：[1, 4]

然后假设 CSV 的设备编号是 1000001 ~ 1002000 ，让 SQLite 计算如下字段的值：

- 设备编号
- 3 个月内数据量
- 3 个月内上传量
- 1 个月内数据量
- 1 个月内上传量
- 1 个月内 9527 数据量
- 1 个月内 9527 上传量
- 3 个月内最新数据
- 3 个月内最新上传数据

### 2.2 结果

```
[ 0.001s] 开始建表……
[319.384s] 建表完成。正在查询……
[347.006s] 查询完毕！前后五行结果：
(1000001, 26306, 6608, 8786, 2222, 827, 199, '{"业务":9524,"时间":"2023-06-24 23:59:27"}', '{"业务":9524,"时间":"2023-06-24 23:59:27"}')
(1000002, 26351, 6651, 8703, 2201, 854, 228, '{"业务":9524,"时间":"2023-06-24 23:58:17"}', '{"业务":9524,"时间":"2023-06-24 23:58:17"}')
(1000003, 26297, 6655, 8755, 2225, 891, 218, '{"业务":9529,"时间":"2023-06-24 23:47:57"}', '{"业务":9527,"时间":"2023-06-24 23:37:26"}')
(1000004, 26502, 6576, 8812, 2208, 901, 239, '{"业务":9521,"时间":"2023-06-24 23:53:41"}', '{"业务":9521,"时间":"2023-06-24 23:53:41"}')
(1000005, 26225, 6520, 8766, 2128, 902, 219, '{"业务":9527,"时间":"2023-06-24 23:49:21"}', '{"业务":9524,"时间":"2023-06-24 23:19:29"}')
……
(1001996, 26328, 6663, 8853, 2282, 899, 251, '{"业务":9521,"时间":"2023-06-24 23:51:59"}', '{"业务":9528,"时间":"2023-06-24 23:38:47"}')
(1001997, 26186, 6633, 8699, 2234, 914, 242, '{"业务":9527,"时间":"2023-06-24 23:57:44"}', '{"业务":9521,"时间":"2023-06-24 23:31:51"}')
(1001998, 25887, 6418, 8727, 2111, 897, 220, '{"业务":9521,"时间":"2023-06-24 23:46:42"}', '{"业务":9521,"时间":"2023-06-24 23:46:42"}')
(1001999, 26192, 6397, 8686, 2108, 859, 201, '{"业务":9521,"时间":"2023-06-24 23:57:37"}', '{"业务":9529,"时间":"2023-06-24 23:05:04"}')
(1002000, 26070, 6470, 8841, 2166, 857, 220, '{"业务":9528,"时间":"2023-06-24 23:59:51"}', '{"业务":9523,"时间":"2023-06-24 23:21:12"}')
```

### 2.3 Python 代码

运行下面代码大约需要 2.5 GB 内存*（因为直接在内存里建表了）*。

由于 v 站 会吃掉行首空格，所以我替换成了全角空格。若要运行，记得替换回来。

```python
import time
import sqlite3

start_time = time.time()
def debug(s):
　　 print(f'[{time.time() - start_time:7.3f}s] {s}')


db = sqlite3.connect(':memory:')

db.execute('''
CREATE TABLE data(
　　 id INT,
　　 devicecode INT,
　　 date TIMESTAMP,
　　 code INT,
　　 status INT,
　　 PRIMARY KEY (devicecode, date, id)
) WITHOUT ROWID
''')

debug('开始建表……')
db.execute('''
WITH
　　 num10(num) AS (
　　　　 VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)
　　),
　　 num10000(num) AS (
　　　　 SELECT a.num * 1000 + b.num * 100 + c.num * 10 + d.num
　　　　 FROM num10 a, num10 b, num10 c, num10 d
　　),
　　 generate_series(value) AS (
　　　　 SELECT a.num * 10000 + b.num
　　　　 FROM num10000 a, num10000 b
　　)
INSERT INTO data
SELECT
　　 value,
　　 abs(random() % 2000) + 1000001,
　　 abs(random() % (SELECT unixepoch('2023-06-25') - unixepoch('2023-01-01'))) + unixepoch('2023-01-01'),
　　 abs(random() % 10) + 9520,
　　 abs(random() % 4) + 1
FROM generate_series
''')

debug('建表完成。正在查询……')
rows = db.execute('''
WITH
　　 csv(设备编号) AS (
　　　　 SELECT DISTINCT devicecode
　　　　 FROM data
　　)
SELECT
　　 devicecode 设备编号,
　　 count(*) "3 个月内数据量",
　　 IFNULL(SUM(status = 1), 0) "3 个月内上传量",
　　 IFNULL(SUM(date >= (SELECT unixepoch('now', '-1 month'))), 0) "1 个月内数据量",
　　 IFNULL(SUM(date >= (SELECT unixepoch('now', '-1 month')) AND status = 1), 0) "1 个月内上传量",
　　 IFNULL(SUM(date >= (SELECT unixepoch('now', '-1 month')) AND code = 9527), 0) "1 个月内 9527 数据量",
　　 IFNULL(SUM(date >= (SELECT unixepoch('now', '-1 month')) AND code = 9527 AND status = 1), 0) "1 个月内 9527 上传量",
　　(
　　　　 SELECT json_object('业务', code, '时间', datetime(date, 'unixepoch'))
　　　　 FROM data AS inner
　　　　 WHERE inner.devicecode = data.devicecode
　　　　　 AND inner.date >= (SELECT unixepoch('now', '-3 month'))
　　　　 ORDER BY date DESC
　　　　 LIMIT 1
　　) "3 个月内最新数据",
　　(
　　　　 SELECT json_object('业务', code, '时间', datetime(date, 'unixepoch'))
　　　　 FROM data AS inner
　　　　 WHERE inner.devicecode = data.devicecode
　　　　　 AND inner.date >= (SELECT unixepoch('now', '-3 month'))
　　　　　 AND inner.status = 1
　　　　 ORDER BY date DESC
　　　　 LIMIT 1
　　) "3 个月内最新上传数据"
FROM data
WHERE devicecode IN (SELECT 设备编号 FROM csv)
　 AND date >= unixepoch('now', '-3 month')
GROUP BY 1
''').fetchall()

debug('查询完毕！前后五行结果：')
print(*rows[:5], '……', *rows[-5:], sep='\n')
```

@Syiize 还好吧。看过知乎上有大佬，用嵌入式 Python 环境 + 手工裁剪 PyQT 5 ，最小能打包成 14MB 即可

用 Python 实现的逐字符遍历，可能还真的比不上几次 in 呢。。

可能用正则来干可以。/Andy|Jack|Jim|Mike/，然后匹配到后回调判断

@albin504 请教一下，会不会搞一个《假期表》好一些？

这个表假设所有 [周一, 周五] 是工作日，[周六, 周日] 是周末。

其余不同之处才添加记录至该表。（比如，添加 22 ～ 24 日为节假日，25 日为工作日）

各个地方可根据需要，添加自己的节日（比如广西三月三）。

各个公司有特殊需要，也能添加自己的特殊日子。

员工特殊请假、调休等，也才添加进去。

这样，同一条 SQL ，都能兼顾上述所有情况，算出需要的数据了？

每年只需添加十来条新数据即可？

@bt7vip 我还是觉得，普通人对抗大机器不现实。

毕竟人家有顶级工程师给重要软硬件埋后门，
有科学家给加密算法埋后门（比如 MD5 有缺陷），有获取所有企业数据的手腕，
再不济还有强大算力保障（苹果对上架 App Store 的应用有加密技术管制？是不是防止太大强度加密，自己也解不开？）

对抗失败的，比如有编，程，随，想。

前几年对伊朗核设施攻击等。


或许，普通人的目标，就是抵抗下小公司？

@la0wei 《等价》表结构也不能吗？

列名替换成 a 、b 、c 、d……，省略无关列 delete_at 等，数据类型统一为 VARCHAR(255)……

CSV 表头也替换成 e 、f 、g 、h……

一切不影响讨论的信息，都可以抹去

应该没啥办法吧？斯诺登十年前都说了，只要知道你的电子邮件，他就能知道有关于你的一切

国外要求实名的地方应该比国内少吧？但人家都能拿到这么多数据，你想怎么抵抗呢？

@touchmii 怎么应对预留后门呢？比如，当年棱镜门那种？

搞得定系统，那芯片层面的怎么办呢？

加密算法的漏洞，个人能堵得上吗？

感觉安全只是针对其他个人或小团体而言。在大机器面前应该抵抗不了啥

@la0wei 要不给出一些等价的表结构？这样方便用 SQL 或者 Python 交流嘛

比如：

数据库：data 表，字段：id INT, a VARCHAR(255), b VARCHAR(255), c VARCHAR(255), ...
CSV：condition_a, condition_b, condition_c, ...

当前 Python 代码：

...

@la0wei 你的原始问题是啥？

有个 CSV ，里面是书籍查询请求？（每一行是一个请求，列是 warehouse 、category 、subcategory 等？）

对于每一个请求，你按要求的过滤条件，去数据库搜索符合的书目数据，保存到独立的 CSV 里？


1. 那你说的“重用上一次查询获取的数据”，意思是想重用 1999 次？

2. 还是说，每个请求，有 6 种或 12 种过滤条件？你想重用 5 次或 11 次？

3. 过滤条件一定是严格的包含关系吗？（一定是 北京仓、北京仓计算机类、北京仓计算机类数据库类 这种层层递进？）

4. 数据库数据量有多少？


目前能想到的几种办法：

1. 每次查询，只查出 id （此时你可用 len(ids) 获取总数），然后再根据 id 查出详细数据。（查过的直接用缓存。如果数据总量不大，甚至可以缓存整个 2000 行 CSV 的结果）

2. 如果 过滤条件是严格的包含关系，可以用 WITH AS + 物化查询？

3. 如果是一次性任务，换用 pandas （要求数据库数据量不大）或其他数据库可能更快（如 SQLite 、DuckDB ，十倍差距都有可能）

@Yuesh1 @albin504 能不能根据过去放假数据，总结规律，预测出未来的假期呢？（假设节日种类及放假调休天数不变）