mysql表中的非常大的数据.即使选择语句也要花费很多时间 [英] Very big data in mysql table. Even select statements take much time
问题描述
我正在研究一个数据库,它是一个相当大的数据库,具有13亿行和大约35列.这是检查表状态后得到的结果:
I am working on a database and its a pretty big one with 1.3 billion rows and around 35 columns. Here is what i get after checking the status of the table:
Name:Table Name
Engine:InnoDB
Version:10
Row_format:Compact
Rows:12853961
Avg_row_length:572
Data_length:7353663488
Max_data_length:0
Index_length:5877268480
Data_free:0
Auto_increment:12933138
Create_time:41271.0312615741
Update_time:NULL
Check_time:NULL
Collation:utf8_general_ci
Checksum:NULL
Create_options:
Comment:InnoDB free: 11489280 kB
我面临的问题是,即使单个选择查询也要花费太多时间来处理,例如查询Select * from Table_Name limit 0,50000
大约需要2.48分钟
这是预期的吗?
The Problem I am facing that even a single select query takes too much time to process for example a query Select * from Table_Name limit 0,50000
takes around 2.48 minutes
Is that expected?
我必须做一个报告,其中必须使用整个历史数据,即整个13亿行.我可以分批执行此操作,但是随后我将不得不运行一次又一次地花费太多时间的查询.
I have to make a report in which I have to use the whole historical data, that is whole 1.3 bil rows. I could do this batch by batch but then I would have to run queries which are taking too much time many times again and again.
当简单查询花费大量时间时,我无法执行任何其他需要联接和case语句的复杂查询.
When the simple query is taking so much time I am not able to do any other complex query which needs joins and case statements.
推荐答案
一种常见的做法是,如果您有大量数据,则...
A common practice is, if you have huge amount of data, you ...
- 不应该
SELECT *
:您应该只选择所需的列 - 应将您的提取范围限制为较小的数字:我敢打赌您不会同时处理50000条记录.尝试分批获取.
- should not
SELECT *
: You should only select the columns you want - should Limit your fetch range to a smaller number: I bet you won't handle 50000 records at the same time. Try to fetch it batch by batch.
这篇关于mysql表中的非常大的数据.即使选择语句也要花费很多时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!