As the number of users visitting the website grows, it is necessary to split mysql tables to improve api service performance. This articles gives three solutions to achieve that.
Assume that we have a table named users like below, and we want to split the table to 4 databases, the following variable db_index denotes which database a given record resides in, table_index denotes the table index of which the record stores in.
create table users(
`id` int(11) unsigned not null auto_increment,
`name` varchar(255) not null default '',
`age` smallint unsigned not null default 0,
primary key(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
- each database has 8 tables,
db_index = id % 4
table_index = crc(id)%8
here crc algorithm is used for distributing id evenly across the tables inside one database.
- each database has 7 tables,
db_index = id % 4
table_index = id % 7
please notice that table_index should not be id%8, otherwise some tables will never have any data, generating severe data skew problem.
- each database has 8 tables,
db_index = id % 4
table_index = id/4%8
id/4 can be seen as a hash function used to distribute records evenly across talbes, like what crc does.
