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 ...

1. 不应该SELECT *:您应该只选择所需的列
2. 应将您的提取范围限制为较小的数字:我敢打赌您不会同时处理50000条记录.尝试分批获取.

1. should not SELECT * : You should only select the columns you want
2. 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屋！