A detailed description of MySQL table implementation method

If you need to make a MySQL sub-table we will prove that your database is relatively large, is to divide a table into more than n small tables, the table, the concurrency of the single table increased, disk I/O performance has improved. Why does concurrency increase, because the time spent on the search is shorter, and if there is a high concurrency, the total table can be divided into different small tables according to different queries

What is a sub-table, from the surface of the meaning, is to divide a table into n more than a small table

MySQL's sub-table is a real sub-table, a table into many tables, each of the small table is the completion of a table, all corresponding to three files, one. MyD data file,. Myi index file,. frm table structure file.

1.[[email protected] test]# ls |grep user
2.alluser. MRG
3.alluser.frm
4.user1. MYD
5.user1. MYI
6.user1.frm
7.user2. MYD
8.user2. MYI
9.user2.frm

1, do MySQL cluster, for example: Using MySQL cluster, MySQL proxy,mysql replication,drdb, etc.

Some people will ask MySQL cluster, what is the root table related? Although it is not the actual point of the table, but it is the role of the sub-table, what is the significance of the cluster? To reduce the burden on a database, let's just cut down the number of SQL queued SQL, for example: There are 10 SQL requests, if placed in a database server queue, he will wait a long time, if the 10 SQL requests, allocated to 5 database server queue, There are only 2 queues in a database server, so is the wait time greatly shortened? It's already obvious. So I put it in the range of the table, and I did some MySQL clusters:

installation, configuration, and read/write separation of Linux MySQL proxy

MySQL replication is the main slave installation and configuration, and data synchronization

Pros: Good extensibility, no complex operations after multiple tables (PHP code)

Cons: The amount of data in a single table is still unchanged, and the time spent on one operation is still much, and hardware overhead is high.

2, pre-estimated large data volumes and frequent access to the table, divided into a number of tables

This kind of forecast big difference, the forum in the post of the table, the time of the table is certainly very large, hundreds of thousands of, millions of is possible. Chat room inside information table, dozens of people together a chat one night, time is long, this table of data certainly very big. There are a lot of things like this. So this big data scale that can be estimated, we have to separate n table, this n is how much, depending on the actual situation. Take the chat information table as an example:

I built 100 such tables beforehand, message_00,message_01,message_02..........message_98,message_99. Then according to the user's ID to determine the user's chat information into which table, You can use a hash of the way to obtain, you can use to find the way to obtain, a lot of methods, each person to think of it. Here's a hash method to get the table name:

1<?PHP2 functionGet_hash_table ($table,$userid) {3  $str=CRC32($userid);4  if($str<0){5  $hash= "0".substr(ABS($str), 0, 1);6}Else{7  $hash=substr($str, 0, 2);8  }9  return $table." _".$hash;Ten } One EchoGet_hash_table (' message ', ' user18991 ');//The result is message_10 A EchoGet_hash_table (' message ', ' user34523 ');//The result is message_13

Explain, above this method, tells us user18991 this user's message all records in message_10 This table, user34523 This user's message all records in message_13 This table, reads, as long as reads from the respective table the line.

Pros: Avoid millions of of data in a single table, shortening the execution time of a SQL

Disadvantage: When a rule is determined, breaking this rule will be very troublesome, the above example, I use the hash algorithm is CRC32, if I do not want to use this algorithm, instead of MD5, will make the same user's message is stored in a different table, so the data is out of order. Extensibility is poor.

3. Improve performance

A) After the table, the concurrency of the single table is improved, and the disk I/O performance is improved. Why does concurrency improve, because the time spent on the search is shorter, and if high concurrency occurs, the total table can divide the concurrency pressure into different small tables based on different queries. How does disk I/O performance get high, originally a very big one. The MyD file is now also allocated to each of the small tables. MyD.

A detailed description of MySQL table implementation method