@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 感谢热心回复，前两天回老家，今天才有机会碰电脑，而且文字交流输出效率不高，有点犹豫。另外我思路有点跳脱，不知道能不能讲明白
回复里不知道 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 这个不能说，police 相关的内容，表结构我都不能说。

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

@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 的找烦了

@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

@LeegoYih 有必要的，一条 sql1 秒多。我根据一个 csv 查询相关信息，整个文件跑完要 3 个多小时
@colinlikepotatos 这个方法好，我试试看

https://alternativeto.net/software/fiddler/
看看 mitmproxy 是否满足你需求

@tozp 我是放在 cf 的，cf 好像有什么技术可以转，但是我测试不成功

@tozp 请教裸域是怎么绑定的

回答不了
最近也在搞 blogger ，无需折腾，无广告，确实挺好的

补充 31 楼,可以打电话给我江苏移动的卡,中断间隙偶尔有几 K 的流量,只能收几条推送消息

用的流量卡归属地是佛山电信的,今天下午没网,鼓捣半天没效果,原来原因在这

@DOMO 非常感谢，这个也进入待选项，我研究下

@d2moqi @bl
踩坑如下，不管结果如何，记录下来，方便后来人

oralce 版本都是 11g11.2.0.1.0
服务器是 centos6
客户机是 win7
下载文件 pdi-ce-9.4.0.0-343.zip

Pentaho data integration(kettle),spoon 反正不管啥名字，这软件运行起来了，功能界面倒是很容易理解，但是在连接数据库那里卡住了

首先报错 driver class 'oracle.jdbc.driver.OracleDriver' could not be found,make sure the 'Oracle' driver (jar file) is installed.
显然没有连接驱动的关系，下载 ojdbc6.jar 放 pdi 的 bin 目录即可，需要重启软件加载。至于更高版本的 ojdbc 没有尝试。

依然连接数据库报错，特意复现了下：
Connection failed.verify all connection parameters and confirm that the appropriate driver is installed.Listener refused the connection with the following error:ORA-12505,TNS:listener does not currently know of SID given in connect descriptor
搜索才知道要在数据库名称前加 / ，不然会报 ORA-12505

以为表输入、插入更新两个步骤就行了，看报错是对 NULL 处理有问题，然后加了个 if field value is null 的步骤

但是又有问题了，插入那对 NUMBER(13,6)转换有问题，提示 java.lang.String cannot be cast to java.lang.Double

估计是在 if field value is null 的转换还是有问题。

暂时踩坑这么多，后面看情况，字段太多，每个都来一遍太累了，后面可能退而求其次直接 dump 数据库

@ohwind 我直接用家宽 IP 注册的，也是不需要手机号。google 必须开梯子，但是在注册阶段就已经要手机号了，不像微软，暗搓搓的

楼上凡是说 IP 不干净的，你们都是使用固定 IP 的吗？有几个家宽能拿到固定 IP 的？此外，家宽动态 IP 的话，你们又怎么在登录前验证 ip 是否干净？

前几个月为了把 onedrive 家庭版用起来，注册一个邮箱账号，在 PC 网页注册时，验证码那里始终过不了，验证通过后又让点击，反复循环，后来在手机注册的，倒是没有让填手机号，但是后来再次登录账户时就要求手机号绑定了，确实感觉是套路