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

[la0wei]

举个栗子
查询北京仓书籍
select * from bookstore where warehouse='beijing'
查询北京仓计算机分类书籍
select * from bookstore where warehouse='beijing' and category='cs'
查询北京仓计算机分类下数据库书籍
select * from bookstore where warehouse='beijing' and category='cs' and subcategory = "database"


假设查询慢，要 2 秒。
如何加速查询？
假如在上面的基础上再加条件？

有什么思路吗，临时表？


同时还要获取数量,具体的语句太多就不一一列举
select count(*) from bookstore

在计数这块，我用的方法是只查询
select * from bookstore where warehouse='beijing'

损失一点前面的速度，但是后面的查询可以通过 python 的元组遍历来获得
for i in all:
if i[5]==cs:
cs_sum += 1 #遍历 pytho 元组，获得 cs 数量
if i[7]==database:
database_sum += 1 #在 cs 基础上获取 database 量


实测 5k 的数据遍历耗时远远小于一次 sql 查询。

根本原因在于我前面写的 sql 查询不能利用上一次查询获取的数据，有优雅的方案吗？

[LeegoYih]

没这必要

[colinlikepotatos]

新版支持 WITH...AS 可以复用查询结果 你研究下。比代码里面要好点

[la0wei]

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

[colinlikepotatos]

实在不行 还有视图 和存储过程，只是这两个操作复杂一些

[wxf666]

@la0wei 加个 (warehouse, category, subcategory) 索引，应该能快很多吧？

> 我根据一个 csv 查询相关信息，整个文件跑完要 3 个多小时

你的 CSV 有多少数据呢？而且，MySQL 能直接查询 CSV ？

[la0wei]

@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]

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

[wxf666]

@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]

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

[la0wei]

@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]

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

[wxf666]

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

比如：

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

当前 Python 代码：

...

[la0wei]

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

[wxf666]

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

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

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

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

[la0wei]

@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]

@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]

@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]

@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]

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

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

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

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

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

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

[la0wei]

@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 快的，索引的效果看来是很好的

[la0wei]

@wxf666 不修改任何代码，只配合使用索引生效后的 mysql ，200 万数据，原先查询 2000 个编号，每个编号 6 次查询，需要 3 小时+，现在只要 16S ！
当然没有你的 1 亿数据效果那么夸张，不过也够用了。

后面感兴趣几点，可能会做测试。
1.我把数据量加大，大概最多到 1200W 的样子（存量历史数据就这么多），查询效果怎样

2.用 sqlite 读数据库文件进内存查询，速度不够快，单条查询在 0.5S 的样子，是因为读取数据时没有读取索引吗，而你在内存构建数据库，我看是有索引的，可能就是速度差异的原因。

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

[wxf666]

@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 ），也没提速多少。

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

[la0wei]

@wxf666
看定义应该是覆盖索引，我把 date ，devicecode ，code ，status 四个字段做了一个索引。另外该数据库没有主键，因为 mysql 的数据是从 oracle ，用 PDI(kettle)抓取过来的，原表有个 NID 是主键，但没有抓取 NID ，一个原因是 kettle 在抓取数据时报错，有 NID 字样，但我无法完全定位错误原因，而在取消 NID 的抓取后，可以顺利完成数据抓取，二是我不需要该字段，只是为了生成报表方便自己而已，所以没有抓取该字段，自然就没有主键索引

sqlite 确实可以深挖下，后面再改改程序

非常感谢这几天的指导！