Vertical segmentation and horizontal segmentation of tables

1, Horizontal Division:

Example: QQ Login table. Suppose QQ users have 10 billion, if only a table, each user log in when the database will be from the 10 billion to find, it is very slow. If the table is divided into 100 parts, each table has 100 million, a lot smaller, such as qq0,qq1,qq1...qq99 table.

User login, you can id%100 the user, then will get 0-99 of the number, query table, the table name QQ with the number of modules connected to the building of the table name. For example 123456789 users, take the model of 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 space, the retrieval table needs to perform a large number of IO, significantly reducing performance. You need to split the large field into another table, and the table is one-to-one with the original table.

For example, student answer form TT: The following fields are available:

Id name score Question Answer

The topic and answer is the larger field, the ID name score is relatively small.

If we only want to query the score of the student ID 8: Select fraction from tt where ID = 8; Although the knowledge query score, but the title and answer these two large segments are also to be scanned, very consuming performance. But we only care about the score, and do not want to query questions and answers. This lets you use vertical segmentation. We can put the topic in a separate table, with the ID and the TT table to establish a one-to-one relationship, also put the answer separately in a table. This way we Sussu the points in TT without scanning the questions and answering them.

3, Other points:

1 storing pictures, files and other large files with file system. The database only stores paths, pictures and files are stored in the file system, and even stored separately in a server (Figure 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, 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 the machine has more than 4G of RAM, it should use a 64-bit operating system and 64-bit MySQL.