multiple tables sharding with mysql

开发者_高级运维I'm making a GPS app that will deals with 200 millions records in a table. My initial thought is to divide the table into multiple tables like position_1, position_2, ... and split the data.

My question is: does it have any performance gain with MySQL(innodb) ?

The real problem is to create the relevant indexes that match the queries.
The InnoDB table size itself (see the InnoDB specific chapter) shouldn't be a problem.

As long as the indexes are accurate, the application development and maintenance using a single table will be much easier.

You don't need to create multiple tables, but you might get a performance enhancement by partitioning the single table across different disks. MySQL does actually have support for practitioning.

http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html

I'd suggest you try to measure performance with and without paritioning before you decide to use it.