Very big data in mysql table. Even select statemen

问题:

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

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?

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.

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.

回答1:

A common practice is, if you have huge amount of data, you ...

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.

回答2:

A common problem many database administrators face. The solution: Caching.

Break the Queries into more simpler and small queries. Use Memcached or other caching techniques and tools Memcached saves key vaue pairs, check for a data in memcache..if available, use it. If not fetch it from database and then use and cach. Next tie the data will be available from cahe.

You will have to develop own logic and change some queries. Memcached is available here:

http://memcached.org/

Many tutorials are available on the Web

回答3:

enable in your my.conf the slow queries up to N seconds, then execute some queries and watch this log, this gives you some clues and maybe you could add some indexes to this table.

or do some queries with EXPLAIN. http://hackmysql.com/case1

回答4:

A quick note that is usually an easy win ...

If you have any columns that are large text blobs, try selecting everything except for those fields. I've seen varchar(max) fields absolutely kill query efficiency.

回答5:

You have a very wide average row size and 35 columns. You could try vertically partitioning the table, that is, split the table up into smaller tables that are related to each other 1:1 with a subset of columns from the table. InnoDB stores rows in pages and is not efficient for very wide rows.

If the data is append-only consider looking at ICE.

You might also look at TokuDB because it supports good compression.

You can consider using partitioning and Shard-Query (http://code.google.com/p/shard-query) to access data in parallel. You can also split data over more than one server for parallelism using Shard-Query.

回答6:

Try adding WHERE clause: WHERE 1=1 If it doesn't give any effect then you should change your engine type to MyISAM.