Horizontal split and vertical split for MySQL

1, split horizontally:

Example: QQ's login form. If QQ users have 10 billion, if there is only one table, each user log in when the database to find from the 10 billion, will be very slow and slow. If the table is divided into 100 parts, each table has 100 million, it is much smaller, such as the qq0,qq1,qq1...qq99 table.

User login, you can be the user's id%100, then get 0-99 of the number, query the table, the table name QQ with the number of modules to connect together, the table name is built. For example, 123456789 users, modulo 89, then to the qq89 table query, the query time will be greatly shortened.

This is the horizontal split.

2, Vertical split:

Vertical segmentation refers to: The table is not a lot of records, but the field is very long, the table occupies a large amount of space, when retrieving the table need to execute a large number of IO, severely reduce performance. You need to split the large field into another table, and the table is a one-to-a-kind relationship with the original table.

For example, student answer sheet TT: There are the following fields:

Id Name Score Topic answer

Where the title and answer are relatively large fields, the ID name score is relatively small.

If we only want to query the score of the student with ID 8: The select score from the TT where ID = 8; Although the knowledge query score, but the question and answer these two large print segments also to be scanned, very consumes the performance. But we only care about the score, and do not want to query questions and answers. This allows you to use vertical partitioning. We can put the topic in a single table, through the ID and the TT table to establish a one-on relationship, the same answer is placed in a single table. This way we will not scan the question and answer when we socialize the score in TT.

3, Other points:

1) Storage of large files, such as pictures and files, with file system. The database only stores paths, pictures and files stored in the file system, or even stored separately on a single server (bed).

2) data parameter configuration.

The most important parameter is memory, we mainly use the InnoDB engine, so the following two parameters are very large:

innodb_additional_mem_pool_size=64m

innodb_buffer_pool_size=1g

For MyISAM, you need to adjust the key_buffer_size, of course, to adjust the parameters to see the state, with the show status statement can see the current state, to determine which parameters to adjust.

4, reasonable hardware resources and operating system

If your machine has more than 4G of memory, you should use a 64-bit operating system and 64-bit MySQL.

Case:

The simple shopping system temporary setting involves the following table:

1. Product table (data volume 10w, Stable)

2. Order form (data volume 200w, and growth trend)

3. User table (data volume 100w, and growth trend)

With MySQL as an example of horizontal split and vertical split, MySQL can tolerate an order of magnitude in millions of static data can go to tens of thousands

Vertical split:

Solve the problem:

IO Competition between table and table

Does not solve the problem:

Pressure on the growth of data volume in a single table

Scheme:

Put product tables and user tables on one server

The order form is placed on a single server

Split horizontally:

Solve the problem:

Pressure on the growth of data volume in a single table

Does not solve the problem:

IO contention between table and table

Scheme:

User table by gender split into male user table and female user table

The order form is split into completed and unfinished orders through completion and completion

Product table unfinished orders placed on one server

Completed order form box male user table put on a server

Female user table on a server

Horizontal split and vertical split for MySQL