Five, MySQL optimization-table vertical Division and horizontal Division

1, the horizontal division of the table

If a table has too many records, such as tens of thousands, and it needs to be retrieved frequently, it is necessary to piecemeal. If I break into 100 tables, then there are only 100,000 records for each table. Of course, the data can be logically divided. A good division basis, in favor of the simple implementation of the program, can also make full use of the advantages of the horizontal table. For example, the system interface only provides monthly query function, then the table by month split into 12, each query query only one table is enough. If you want to divide according to the region, the table can be removed in a small, query or to unite all the tables to check, it is better not to dismantle. So a good basis for splitting is the most important.

such as QQ user information, a single User data table storage card, you can do 100 the same user table, respectively, user_0,user_1,............ user_99, insert a new user, according to k=qqid/100, inserted into the User_k data table.

2, Vertical division of the table

Some table records are not many, may also be 2, 30,000, but the field is very long, the table occupies a large space, the retrieval needs to perform a large number of I/O, severely reducing performance. At this point, you need to split the large field into another table, and the table is a one-to-one relationship with the original table.

For example, the student answer sheet, Student data table field has id,name,score,email,question,answer. Where question and answer data is relatively large, you can separate the two fields, forming a separate table, the data table is divided vertically after the following, Student data table Id,name,score,email. Paper Data Sheet id,stuid,question,answer.

Five, MySQL optimization-table vertical Division and horizontal Division