查询1800万行以上mysql数据库的替代解决方案

AK91 • 4 年前 • 722 次点击

试图查询一个超过1800万行的mysql表。我只需要一个简单的:

select date, url, count(*) from table
where date > '2018-01-01' and date < current_date

但15-20分钟后就崩溃了。我尝试使用pandas模块在python中连接到db,然后将一天的数据附加到一个空数据框中。但仍然坐在那里玩弄我的拇指…

import pandas as pd
import pymysql
import time

conn = pymysql.connect(...)

result = []
for date in pd.date_range(start='01/01/2019', end=pd.to_datetime(time.strftime('%d/%m/%Y'))):
    query = ("select * from table where time >= '{}' and time < '{}'").format(date, date + pd.DateOffset(days=1))
    df = pd.read_sql(query, con=conn)
    result.append(df)
pd.concat(result,axis=0)
print(result)

我有什么办法把这些数据拿出来? 主要目的是把这些数据放到表格里,然后从那里取出来…

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/46580

722 次点击

文章 [ 2 ] | 最新文章 4 年前

• 1 楼

AK91 4 年前

使用python创建一个for循环,从“unqueryable”表中查询和总结一天的数据,并将其附加到csv,然后连接到bi工具。还尝试用一些索引在数据库中创建一个新表,然后以相同的方式循环,但追加到表中。

• 2 楼

Daniel Farrell 5 年前

我在Docker中启动了一个MySQL服务器,就像这样,只是使用默认值:

docker run -d --rm --name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=true mysql

并创建了这样的数据库:

docker exec -it mysql mysql -e 'create database if not exists test'

然后像这样连接交互式会话:

docker exec -it mysql mysql test

然后我在上面填了3200万个随机日期…

INSERT into dates select date(from_unixtime(rand()*unix_timestamp(now())) );

然后运行几十次:

INSERT into dates select date(from_unixtime(rand()*unix_timestamp(now())) ) from dates;

现在我的约会次数几乎是你的两倍:

mysql> explain select * from dates;
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------+
|  1 | SIMPLE      | dates | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 33497947 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------+
1 row in set, 1 warning (0.00 sec)

最后,我可以演示如何快速搜索表格:

mysql>  select count(*), d from dates where d between '2001-01-01' and '2001-12-31' group by d order by d desc;  
....
365 rows in set (4 min 31.17 sec)

有道理,2001年每天都有几千个结果。(请记住,这些日期是在1970年到现在之间随机分布的)。

没有索引或任何东西,也没有sql优化。花了4.5分钟。希望这能为您的服务器和查询性能提供一个基线。

登录后回复