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 查询不能利用上一次查询获取的数据，有优雅的方案吗？

[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 确实可以深挖下，后面再改改程序

非常感谢这几天的指导！