First, MySQL sub-table simple introduction

I. Reasons for MySQL sub-table

1, when a piece of data reached millions of, you query the time spent will be more, if there is a joint query, I think it might be dead there.

The purpose of the sub-table is to reduce the burden on the database and shorten the query time. 2, MySQL has a mechanism is the table lock and row locking, why to appear this mechanism, is to ensure the integrity of the data, I give an example, if there are two SQL to modify the same table of the same data, this time what to do, is not two SQL can simultaneously modify this data? It is clear that MySQL handles this situation in the form of a table lock (MyISAM storage engine) and a row lock (InnoDB storage engine). Table locking means that you can't operate on this table, and you have to wait until I finish working on the table. Row locking is the same, other SQL must wait until I'm done with this data before I can manipulate this piece of data. If there is too much data, the time to execute is too long, and the longer the wait, which is why we have to divide the table. Ii. Rules of the sub-table

This is just the simplest table rule-taking model.

If we need to divide the user table into 100 sheets

User for horizontal segmentation, resulting in two table structure of the exact same user_1,user_2 table, user_1 + user_2 + ... Data is just a complete piece of data.

1) We use the simplest sub-table scheme to take the modelWe model according to the user ID 100 for example, the user ID 1000156 modulo 100 56 that add data or read the data is the user_56 This table operation is a sub-table script

#!/bin/SH# dev|idcdbenv=Devif["${dbenv}"="Dev"]; ThenMysql_user=Root Mysql_pass=Root Mysql_host=fiif["${dbenv}"="IDC"]; ThenMysql_user=Root Mysql_pass=Root Mysql_host="- H 10.10.10.10"fiMysql_cmd="Mysql-u${mysql_user}-p${mysql_pass} ${mysql_host}--default-character-set=utf8"  forIinch{1.. -} Do${mysql_cmd}<<EOF use Md_mydatabasel; CREATE TABLE t_user_$i (F_uin bigint ( -) Not NULL DEFAULT'0', F_name varchar ( the) Not NULL DEFAULT"', PRIMARY KEY (F_uin)) ENGINE=innodb DEFAULT charset=UTF8; EOF Done

Here is the script to delete the table

#!/bin/SH# dev|idcdbenv=Devif["${dbenv}"="Dev"]; ThenMysql_user=Root Mysql_pass=Root Mysql_host=fiif["${dbenv}"="IDC"]; ThenMysql_user=Midea Mysql_pass=Md_midea mysql_host="- H 10.10.10.10"fiMysql_cmd="Mysql-u${mysql_user}-p${mysql_pass} ${mysql_host}--default-character-set=utf8"  forIinch{1.. -} Do${mysql_cmd}<<EOF use Md_mydatabasel; drop table t_user_$i; EOF Done

2) Each table needs to ensure that the user's ID is globally uniqueThere is a simple solution is to create a table in the database only one field is dedicated to take the number of a new user each time, then take a number from the table and let this number add one to ensure that each user's ID is globally unique

CREATE TABLE T_global_number (  bigint(notNULL,    PRIMARYKEY  (f_global_id)) ENGINE=DEFAULT CHARSET= Utf8

3) About data readWe try to make the request not to query the database directly we can put the data into the Redis cache every time we find it. If the data has not changed, always use the cache. Deleting the cache Once the data has changed can reduce the query burden on the database
third, the problem of sub-database sub-table, and 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.

First, MySQL sub-table simple introduction