How to divide tables in mysql

When a piece of data reaches several million, the time it takes for you to query once will increase. If there is a joint query, I think it may die there. The purpose of table partitioning is to reduce the burden on the database and shorten query time.

Sub-table

1, create a mysql cluster. For example: using mysql cluster, mysql proxy, mysql replication, drdb, etc.

Some people may ask, does mysql cluster have anything to do with sub-tables? Although it is not a sub-table in the actual sense, it plays the role of a sub-table. What does it mean to be a cluster? To reduce the burden on a database, to put it bluntly, it means to reduce the number of SQLs in the SQL queue.

For example: There are 10 sql requests. If they are placed in the queue of a database server, they will have to wait for a long time. If these 10 sql requests are allocated to the queues of 5 database servers In the queue, there are only 2 queues in a database server. Will the waiting time be greatly shortened? This is already obvious.

Advantages: Good scalability, no complex operations (php code) after multiple tables Many, the hardware overhead is large.

2. It is estimated in advance that there will be tables with large amounts of data and frequently accessed. Divide them into several tables.

This kind of estimation is not bad, forum The table in which posts are posted will definitely become very large over time, possibly hundreds of thousands or even millions. In the information table in the chat room, dozens of people chatted together for an entire night. After a long time, the data in this table must be very large. There are many situations like this. Therefore, for this kind of big data scale that can be estimated, we divide it into N tables in advance. The number of N depends on the actual situation.

Advantages: Avoiding millions of data in one table and shortening the execution time of a SQL

Disadvantages: When a rule is determined, it will be very difficult to break this rule. Trouble, the hash algorithm I used in the above example is crc32. If I don't want to use this algorithm now, switching to md5 will cause the same user's messages to be stored in different tables, so the data will be messed up. Scalability is poor.

3, use the merge storage engine to implement table partitioning

I think this method is more suitable. Those that have appeared without prior consideration will cause slow data query Case. At this time, it is more painful to separate the existing big data scales. The most painful thing is to change the code, because the SQL statements in the program have already been written. Now a table has to be divided into dozens of tables, or even hundreds of tables. Does this mean that the SQL statement needs to be rewritten? For example, I like to use

mysql>show engines; when you will find that mrg_myisam is actually merge.

mysql> CREATE TABLE IF NOT EXISTS `user1` (  
->   `id` int(11) NOT NULL AUTO_INCREMENT,  
->   `name` varchar(50) DEFAULT NULL,  
->   `sex` int(1) NOT NULL DEFAULT '0',  
->   PRIMARY KEY (`id`)  
-> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  
Query OK, 0 rows affected (0.05 sec)  
 
mysql> CREATE TABLE IF NOT EXISTS `user2` (  
->   `id` int(11) NOT NULL AUTO_INCREMENT,  
->   `name` varchar(50) DEFAULT NULL,  
->   `sex` int(1) NOT NULL DEFAULT '0',  
->   PRIMARY KEY (`id`)  
-> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;  
Query OK, 0 rows affected (0.01 sec)  
 
mysql> INSERT INTO `user1` (`name`, `sex`) VALUES('张映', 0);  
Query OK, 1 row affected (0.00 sec)  
 
mysql> INSERT INTO `user2` (`name`, `sex`) VALUES('tank', 1);  
Query OK, 1 row affected (0.00 sec)  
 
mysql> CREATE TABLE IF NOT EXISTS `alluser` (  
->   `id` int(11) NOT NULL AUTO_INCREMENT,  
->   `name` varchar(50) DEFAULT NULL,  
->   `sex` int(1) NOT NULL DEFAULT '0',  
->   INDEX(id)  
-> ) TYPE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST AUTO_INCREMENT=1 ;  
Query OK, 0 rows affected, 1 warning (0.00 sec)  
 
mysql> select id,name,sex from alluser;  
+----+--------+-----+  
| id | name   | sex |  
+----+--------+-----+  
|  1 | 张映 |   0 |  
|  1 | tank   |   1 |  
+----+--------+-----+  
2 rows in set (0.00 sec)  
 
mysql> INSERT INTO `alluser` (`name`, `sex`) VALUES('tank2', 0);  
Query OK, 1 row affected (0.00 sec)  
 
mysql> select id,name,sex from user2  
-> ;  
+----+-------+-----+  
| id | name  | sex |  
+----+-------+-----+  
|  1 | tank  |   1 |  
|  2 | tank2 |   0 |  
+----+-------+-----+  
2 rows in set (0.00 sec)

Advantages: Good scalability, and the program code does not change much

Disadvantages: The effect of this method is slightly worse than the second one

The above is the detailed content of How to divide tables in mysql. For more information, please follow other related articles on the PHP Chinese website!