Python + mysql 多条相似 sql 语句查询如何加速?

la0wei

2023-06-21 16:02:23 +08:00

@colinlikepotatos 视图和存储过程杀鸡用牛刀了，先简单的开始一点一点优化。
with...as 测试了下，没有显著减少时间。

环境实际是 mariadb 10.4.12 ，查询程序与 mariadb 在一台电脑上，带宽延迟应该都不是问题。用 heidisql 执行语句，发现有提示耗时的功能
查询 0.031 sec.(+3.307 sec. network)
等于说查询速度其实是很快的，至少这个量级我是满意的，0.1 秒都不到
但是 network 这个耗时太无语了，python 写了个测试程序，从查询到结果确实是这个时间。

没找到如何优化这点。目前思路是合并 sql ，看 1 条 sql 语句是否比多条节省 network 时间。
用 union 合并两条相似的查询，单条大概是 0.031 sec.(+3.307 sec. network)，合并后又让人看不懂了
查询 8.190 sec （+ 0.281 sec. network ）
耗时的位置调了个。

@wxf666 用了索引，根据 heidisql 的提示，查询不到 0.1 秒，后面的是网络耗时，具体消耗在哪里我还在查。

csv 超过 2000 行数据，每行有个编号，根据这个编号去查询 6 次，完整程序查询次数可能翻倍。

就是写个 python 程序，从 csv 读取，去数据库查询，查询结果写入另一个 csv

colinlikepotatos

2023-06-21 16:33:00 +08:00

数据不大确实没必要用 with as ，合并查询结果果在达到一定量效果才明显，网络问题不好说，不过要是查询结果不要处理的话直接 select into outfile 减少网络 io 应该很明显的。还可以并发走起。网络这么不稳定难道走了路由器嘛

wxf666

2023-06-21 16:47:30 +08:00

@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 ，十倍差距都有可能）

TimePPT

2023-06-21 17:18:32 +08:00

你 csv 是查询条件？如果是的话，csv 导入一张 mysql 表，和大表联表查不就得了。

la0wei

2023-06-21 17:29:37 +08:00

@colinlikepotatos 查询程序和数据库在同一台电脑。相当于说本地开了一个服务器，打开网页慢，虽然后台处理逻辑非常快，但是页面加载很慢，这就有点奇怪了。虽然配置不高，cpu 和内存应该够用了，唯一怀疑的可能是硬盘，是 hdd 。我的 sql 语句并不复杂，最有效的索引也开启了，优化的空间不大

@wxf666 是我问题问的不好，因为数据比较敏感，没有想到比较好的类似的例子来举例。
1.重用上一次查询获取的数据，大概 20 次以内。

2.是的。
基础语句：
select * from bookstore where 条件 1

然后细分出
select * from bookstore where 条件 1 and 条件 2.1
select * from bookstore where 条件 1 and 条件 2.2 and 条件 3.1
这么排列组合的话大概十几二十次。

之前怀疑 sql 执行慢，所以想只查询
sql * from bookstore where 条件 1
后面的查询自己手写，但是觉得太傻了，先看看有没有方案再考虑暴力去算。

还要有对应的
select count(*) from bookstore where 条件 1
……
……
这里就麻烦了。没想到怎么加速，只能 python 写代码循环算。根据测试效果还不错，都在 0.1 秒内，比等数据库划算。
数据库理应更快的，只是我没有 debug 的手段

目前看瓶颈是在 network ，python 只执行一条语句，然后后面全部靠编码自己算确实是一个有效的规避手段

3.数据量在 180W ，使用 sqlite3 测试查询速度和 mariadb 差不多

准备换环境试试，这是一台隔离环境的电脑，装有专用软件，目前跑 win7 ，幸好原先有双系统，准备换 win10 试试，或者加个固态。当时装 mariadb 就是因为 mysql 找支持 win7 的找烦了

la0wei

2023-06-21 17:31:04 +08:00

@TimePPT csv 是查询条件。这个操作不会……压根就不知道有这个操作。待我搜索看看，谢谢提醒

wxf666

2023-06-21 17:38:37 +08:00

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

比如：

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

当前 Python 代码：

...

la0wei

2023-06-21 20:57:24 +08:00

@wxf666 这个不能说，police 相关的内容，表结构我都不能说。

wxf666

2023-06-21 21:35:25 +08:00

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

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

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

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

la0wei

2023-06-24 15:31:26 +08:00

@wxf666 感谢热心回复，前两天回老家，今天才有机会碰电脑，而且文字交流输出效率不高，有点犹豫。另外我思路有点跳脱，不知道能不能讲明白
回复里不知道 markdown 能不能用，姑且试下

数据库主要字段
| devicecode(varchar18) | date(datetime) | code | status(char1) |
| --------------------- | ---------------- | ---- | ------------- |
| 1000001 | 2023-06-24 14:20 | 9527 | 4 |
| 1000001 | 2023-06-24 14:22 | 9528 | 1 |
| 1000002 | 2023-06-24 14:22 | 9527 | 1 |
| 1000002 | 2023-06-24 14:25 | 9530 | 3 |
| 1000003 | 2023-06-24 14:25 | 9527 | 3 |
| 1000004 | 2023-06-24 14:25 | 9527 | 4 |

devicecode 设备名称
date 时间
code 该条数据所属的业务分类
status 该条信息的分拣状态，有 1 上传成功，2 待审核，3 作废，4 上传失败等等

csv 主要两个信息。甚至只有设备编号也可以，因为读写都是指定列表位置，主要作用是占位，列表修改和 append 是不同的操作，对我的需求来说修改更灵活一些

| 设备编号 |设备名称|3 个月内数据量| 3 个月内上传量（ status 1 ）| 1 个月内数据量 | 1 个月内上传量（ status 1)| 1 个月内 9527 数据量| 1 个月内 9527 上传量|
| -------- | ---------------- | ------------ | ----------------------- | ------------ | ------------------------ | ---------------- | ---------------- |
| 1000001 | 我不知道这是什么 1 | 占位 | 占位 | 占位 | 占位 | 占位 | 占位 |
| 1000002 | 我不知道这是什么 2 | | | | | | |
| 1000003 | | | | | | | |
| 1000004 | | | | | | | |
| 1000005 | | | | | | | |

程序逐行读取 csv 文件为列表类型，获取设备编号，在数据库内查询。
可以看到，我想获得
1000001 设备 3 个月内数据总量
1000001 设备 3 个月内上传的数据总量
1000001 设备 1 个月内数据总量
1000001 设备 1 个月内上传的数据总量
1000001 设备 1 个月内业务代码为 9527 的数据总量
1000001 设备 1 个月内业务代码为 9527 且上传成功的数据总量

上面的都是用 select count(*)查询，似乎不能优化
不过再看一个你就明白了
1000001 设备 3 个月内最新一条数据
select * from info where date>date_sub(NOW,INTERVAL 3 MONTH) AND DEVICECODE=1000001 order by date desc

1000001 设备 3 个月内最新一条上传成功的数据
select * from info where date>date_sub(NOW,INTERVAL 3 MONTH) AND DEVICECODE=1000001 AND STATUS=1 order by date desc
cursor.fetchone()第一条就是了
这个帖子最初目的是第二条 sql 查询能复用第一条的结果来加速查询

由于查询速度太慢，还有很多需要查询的数据没有写

目前只能先读出 3 个月数据，循环读取，设置几个计数器
fetchone 第一条数据做最新数据
status=1 的是最新上传成功数据 upl += 1
status=1 and code=9527 则 upl += 1 同时 upl9527 += 1
等等等

总之这么排列组合，把 select count(*) 执行的任务用 python 来实现

原先我以为是 sql 执行慢，所以希望
select * from info where date>date_sub(NOW,INTERVAL 3 MONTH) AND DEVICECODE=1000001 AND STATUS=1 order by date desc 能使用 select * from info where date>date_sub(NOW,INTERVAL 3 MONTH) AND DEVICECODE=1000001 order by date desc 结果查询

但是后来发现不是 sql 执行效率的问题，所以这个问题目前意义不大了。

我在找目前 sql 执行只用 0.1 秒，网络耗时几秒的原因，这个解决的话，多执行几条 sql 不是问题

估计打了有上千字，思路不清，表达不畅的地方还请见谅

wxf666

2023-06-25 16:58:39 +08:00

@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')
```

la0wei

2023-06-25 18:19:32 +08:00

@wxf666 太牛了！我想来 V2EX 写下进展的，正好看到你的回复。

先说下我这边的进展，尝试 1 台 win10 + SSD 的机器，在 heidisql 内执行 sql 依然有高达 3 秒以上的延迟。

再次尝试 sqlite,这次新建了索引，平均时间在 400ms~3000ms 。有数据的快，查询结果为空的反倒慢达 3 秒才有结果，这点让我不解。

另外你的帖子给了我一点灵感！
然后我在 heidisql 查询的时候打开任务管理器，发现每次执行 sql 后，ssd 都有大量读取，估计这就是延迟高的原因。而 sd 在瞬时突发传输没有和 hdd 拉开差距，导致变更硬件没有明显的改观。

使用 DB Brwoser for SQLite 就没有这个问题，虽然延迟不定，不过查询过程中没有硬盘 IO 。

查看内存占用，mysqld 占用 210M ，DB Brwoser for SQLite 占用 110M 。

再回头看你的回复，你的结论是对的
***我认为，主要是你把数据库里的内容，全部传输到 Python ，导致长时间耗时在数据传输上。***

不知道我的 mysql 是否运行正常，每次查询都要全数据库读取，还是说索引没有起作用？
sqlite 的查询都没读取数据库文件，是因为已经在程序打开的缘故吗？ 110m 的内存占用，显然远小于 sqlite.db 700M 的文件大小，或许这应该是数据库正常的工作方式，mysql 我默认安装配置有问题？

显然内存数据库速度快的多。
搜索到 sqlite 书库读取到内存的方法：
https://stackoverflow.com/questions/3850022/how-to-load-existing-db-file-to-memory-in-python-sqlite3

上班第一天太忙了，后面有空我改造下程序，把查询数据库改成查询内存中的数据库
目前思路就是这样

后面还是想知道 mysql 读取数据库的原因，这显然不是数据库正常的工作方式
另外，可以测试下在 linux 下默认安装 mysql 查询是否也是这样的速度

非常感谢，后面抄你一点代码:)

wxf666

2023-06-25 18:59:38 +08:00

@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

2023-06-25 21:00:59 +08:00

@wxf666
执行的语句除了字段名称不同，和你的其实是一样的，日期写法不同，不过我猜不是重点。

我也准备弃用 mysql ，sqlite 确实方便。
另外，延迟的问题搞不定。我在 heidisql （ gui 工具）执行 sql 时，任务管理器的磁盘是有非常明显的读写，峰值大概在 130M 的样子，硬盘读取回落后，heidisql 立刻就出结果了。连续查询，磁盘就相应的出现读取。有明显的相关性

查数据读盘很正常，读那么多数据就玩完了，我也怀疑过索引有问题，使用 explain 执行，看到是利用了索引的，我再研究下这块。明天检查下索引，再重建下试试看。

我最初的方法是用 sql 查询把数据读取到 python 中，然后自己写逻辑，现在想来其实是解决不了延迟情况下一个非常好的方案了。你的方法更进一步，使用内存数据库，这样可以省去编写逻辑编写过程，直接使用 sql ，应该是最合理的。

只要延迟不解决，就只能搞内存数据库了

https://dba.stackexchange.com/questions/172030/mysql-network-time
这里有遇到和我一样问题的人。明明本地数据库，为何有 network time.

la0wei

2023-06-26 11:07:02 +08:00

@wxf666 确认问题所在了，之前创建的索引在查询时没有使用，为什么没有使用就不知道了。

用 explain 看了下 sql 语句，发现没使用索引。把之前用 GUI 工具创建的索引删除，用 sql 语句重新创建了索引，再次 explain ，能看出使用了索引。
再次执行 select ，HDD 上执行速度多数在 0.1 秒以下，慢的也在 0.1s 的量级。

在 win10 SSD 上的 mysql 执行同样的操作，速度还稍稍有点慢，有些不能理解。可以看出，磁盘读取比重建索引之前要少的多，只有几十兆的样子，这才是数据库正确的打开方式啊

不过还是蛮喜欢内存数据库的。
https://stackoverflow.com/questions/3850022/how-to-load-existing-db-file-to-memory-in-python-sqlite3
使用
import sqlite3

source = sqlite3.connect('existing_db.db')
dest = sqlite3.connect(':memory:')
source.backup(dest)
可以把数据库文件读取到内存。昨晚在家简单测试，效果不错，可惜数据量不够，不能体现出完整效果

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