[ZZ] MySQL Sub-table sub-block to master and slave

1. Remember to build a Web site, database, development environment, Web services a brain all on a machine, with a few m of large pictures do webpage background, feel very cool. There is no database optimization problem without access.

2. In MySQL, for example, when there are millions of records in a table, the query will naturally become slower. This is what is usually done is the sub-database table.
Sub-Library: Vertical split
The simple thing is to separate the different table from the 1 db into different db.
For example: DB contains user information and group Information 2 table, the user belongs to a group. Divide the original 1 db into 2. A storage user db, a group db, this is the sub-Library.
Advantages: For different libraries can have different operational logic, naturally dispersed database pressure.
Problem: cannot cross-Library query, can not guarantee the consistency and integrity of data. For example, inserting user information should add users to the group. The 2 DB operation is designed after the sub-Library. cannot be done with transaction.

Sub-table: Horizontal split
We have a user table because it's too big. We split it according to the last one in the UserID field. The user is split into User_0, User_1...user_9. This can also spread the pressure. But the complexity of the program will also improve.
Splitting the rule is very important, taking into account the balance and extensibility of each table.
CREATE TABLE User (id INT, name DECIMAL (7,2), date date) Engine=innodb
PARTITION byHASH (MONTH (date)) Partitions 12;
Above is an example of splitting by month.

Memcached (Cache technology) + Sub-Library table is the initial method to alleviate the database pressure.

3. Master and slave settings, MySQL replication for example.
One master more from:
In general, it consists of a main library and multiple libraries, from which the library can be used for backup and disaster recovery, and when the main library fails, it becomes the main library from the library manually.
(1) The operation of Master (main library) is recorded in log.
(2) Slave (from the library) copies the log events of the main library to its own relay log.
(3) Slave redo log events, reflected in from the library.
General write operations master, read from slave. Multiple slave can divert the pressure of the query. The general master-slave settings are in conjunction with proxy or amoeba to operate, the following will be introduced.

Specific Settings reference:

http://aciddrop.com/2008/01/10/step-by-step-how-to-setup-mysql-database-replication/

Master-Slave +mysql Proxy (MySQL Amoeba)
On the basis of the master-slave setup, you can also reduce the database pressure by using proxy or amoeba to separate read and write.



Take MySQL proxy as an example. Proxy is equivalent to a connector between the client and the server. Written by the Lua script. Use it to separate read and write. For the write operation, we let master handle it, and slave automatically synchronizes master's changes after writing. And for reading, we use slave to deal with.

Multi-Master multi-Slave:
The multi-master multi-slave setting is a loop loop with each db being both the slave of the previous DB and the second master.
Note that the auto_increment_increment and Auto_increment_offset are set when inserting operations on each db. To prevent a primary key violation error when the last merge occurs.
Advantage: A master hangs up and can also continue the db operation. Each DB can read and write, dispersing pressure.
Multi-master multi-slave design, write operations are not only in a master-ambition, but can be done on any db. The read operating pressure can also be better dispersed across the db.

Original address:

Http://blog.sina.com.cn/s/blog_7e89c3f501012vpr.html

[ZZ] MySQL Sub-table sub-block to master and slave