Mysql
Why divide a table into a libraryCan be used to say where the MySQL, as long as the amount of data a large, immediately encounter a problem, to be divided into the database table. Why do you want to divide the table with a question? Can't mysql handle big tables? In fact, it is a large table that can be processed. I have experienced projects in which the single table physical file size is more than 80G, the number of single-table records is more than 500 million, and this table belongs to a very nuclear table: Friend relationship table. But this is not the best way to say it. There are also many problems with file systems such as EXT3 file systems that are larger than large files. This level can be replaced with the XFS file system. But MySQL single table too big after a problem is not good to solve: Table structure adjustment related operations are basically not possible. Therefore, large projects in the use of the city will monitor the application of sub-database sub-list. From InnoDB itself, the data file btree on only two locks, leaf node lock and child node lock, you can think of and know that when the page split or add a new leaf will cause the table can not write data . So the sub-database table is a better choice.
How appropriate is the sub-list of the library?After testing in a single table 10 million records, write read performance is relatively good. In this way to save a bit of buffer, then the single table is all the data font is kept below 8 million records, the character type of the single table is kept below 5 million. If you plan by 100 library 100 tables, the amount of data supported is: 5 million *100*100 = 500 billion = 500 billion record.
Sub-database sub-table mode:
Single- Library single-table
A library single table is the most common database design, for example, a user table is placed in database db, and all users can be found in the user table in the DB Library.
single-Library multi-table
As the number of users increases, the amount of data in the user table becomes larger, and when the amount of data reaches a certain level, the query to the user table slowly slows down, affecting the performance of the entire DB. If you use MySQL, a more serious problem is that when you need to add a column, MySQL locks the table, and all read and write operations can wait.
The user can be sliced horizontally in some way, resulting in two tables of the exact same table structure as the user_0000,user_0001, user_0000 + user_0001 + ... Data is just a complete piece of data.
Multi -Library multi-table
As the amount of data increases perhaps a single db of storage space is not enough, with the increase in query volume of a single database server has no way to support. The database can be differentiated horizontally at this time.
Sub-database table rules
When designing a table, you need to determine what rules the table will use to divide the database into tables. For example, when a new user is available, the program has to decide which table to add this user information to, so when we log in we have to find the corresponding record in the database through the user's account, all of which need to be routed according to a certain rule.
The process of finding the corresponding tables and libraries by using the Sub-Library table rules. such as the sub-database of the rules are user_id mod 4, when the user registered a new account, account ID of 123, we can use the ID mod 4 way to determine that the account should be saved to the user_0003 table. When user 123 logs in, we are determined to record in user_0003 by 123 mod 4.
problems arising from the sub-tables and the matters needing attention
1. The problem of the sub-database dimension
If the user buys the commodity, need to save the transaction record, if according to the latitude of the user table, each user's transactions are saved in the same table, so it is very convenient to find a user's purchase situation, but the purchase of a product is likely to be distributed in more than one table, find it more troublesome. Conversely, according to the commodity dimension of the table, can be very convenient to find the purchase of this item, but to find out the buyer's transaction record is more troublesome.
So the common solution is as follows:
A. This approach is largely impossible and inefficient by way of a sweep of the table.
B. Record two data, one according to the latitude of the user table, a copy according to the dimensions of the commodity table.
C. Through search engine resolution, but if the real-time requirements are very high, but also related to real-time search.
2. Problems with Federated queries
Federated queries are basically not possible because the associated tables may not be in the same database.
3. Avoid cross-Library transactions
Avoid modifying the tables in the db0 while modifying the tables in a transaction, one of which is more complex to operate and will have a certain effect on the efficiency of the DB1.
4. Try to put the same set of data on the same DB server
For example, seller A's goods and transaction information are placed in the db0, when the DB1 hangs, seller a related things can be used normally. This means that the data in the database is not dependent on the data in another database.
other ways to use
a master multi-standby
In practical applications, the vast majority of cases are read far beyond writing. MySQL provides a mechanism for read and write separation, all write operations must correspond to master, read operations can be performed on the master and slave machines, slave is identical to the structure of master, a master can have multiple slave, Even under the slave can hang slave, in this way can effectively improve the DB cluster of QPS.
All of the write operations are first on the master, and then update to the slave, so the synchronization from master to slave machine has a certain delay, when the system is very busy, the delay problem will be more serious, the increase in the number of slave machines will also make this problem more serious.
In addition, it can be seen that master is the bottleneck of the cluster, when too many write operations can seriously affect the stability of master, if master hangs, the entire cluster will not work properly. So, 1. When reading the pressure is very large, you can consider adding slave machine fractional solution, but when the slave machine to achieve a certain amount of the sub-Library should be considered. 2. When writing pressure is very high, it is necessary to carry out the library operation. Transferred from: http://wentao365.iteye.com/blog/1740874
MySQL Sub-database sub-table summary
