Shulou(Shulou.com)06/01 Report--

This article mainly introduces mysql how to sub-table, the article introduces in great detail, has a certain reference value, interested friends must read it!

Sub-table

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

Some people will ask mysql clusters, what does it have to do with sub-tables? Although it is not a sub-table in the actual sense, it plays the role of sub-table. What is the meaning of being a cluster? To lighten the burden on a database, to put it bluntly, is to reduce the number of sql in the sql queue.

For example, if there are 10 sql requests, if you put them in the queue of a database server, he will have to wait a long time. If you assign these 10 sql requests to the queues of 5 database servers, and there are only 2 requests in one database server, will the waiting time be greatly shortened? It's already obvious.

Advantages: good scalability, no complex operations after multiple sub-tables (php code)

Disadvantages: the amount of data in a single table remains the same, the time spent on one operation is still that much, and the hardware cost is high.

2. Pre-estimate the tables with large amount of data and frequently accessed, and divide them into several tables

Whether this estimate is big or bad, the list of posts posted in the forum must be very big over a long period of time, hundreds of thousands or millions. Chat room inside the information table, dozens of people chat together for a night, for a long time, the data on this table must be very large. There are a lot of situations like this. Therefore, for this predictable big data scale, we will divide it into N tables in advance, and what this N is depends on the actual situation.

Advantages: avoid millions of pieces of data in a table and shorten the execution time of an sql

Disadvantages: when a rule is determined, it will be troublesome to break this rule. The hash algorithm I used in the above example is crc32. If I don't want to use this algorithm now, after using md5, the messages of the same user will be stored in different tables, so the data will be messed up. Poor scalability.

3. Use merge storage engine to realize sub-table.

I think this method is more suitable, those cases that have not been considered in advance, but have already occurred, the data query is slow. At this time, if you want to separate the existing big data scale, the most painful thing is to change the code, because the sql statement in the program has been written. Now a table is divided into dozens or even hundreds of tables, so does the sql statement have to be rewritten? For example, I like to raise my son very much.

When mysql > show engines;, 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 '0mm,-> PRIMARY KEY (`id`)-> ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 Query OK, 0 rows affected (.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 '0mm,-> PRIMARY KEY (`id`)-> ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; Query OK, 0 rows affected (0.01sec) mysql > INSERT INTO `user1` (`name`, `sex`) VALUES (' Zhang Ying', 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' 0mm,-> 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 | Zhang Ying | 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 expansibility, and the program code is not changed much.

Disadvantages: this method is a little less effective than the second one.

The above is all the contents of the mysql table, thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.