MySQL Sub-table technology (learning experience)

(recently in learning MySQL optimization of some problems, the following is a few personal gains, if there is insufficient, please ask!) ）

Overview: When a table of data is large, such as 200G, it is too large, we only rely on the index is not good, then we need to table and partition processing. There are two forms of the sub-table (Horizontal and vertical sub-tables).

First, the level of the table

　　Core idea: Put a big table, divide n small tables, small tables and large table structure, just spread the data into different tables.

　　1.1 Simple example: for example, through the ID directly login (such as QQ number), you can directly use the following example

　　　　

Each login verification, as long as the pass-through ID in addition to 3 modulo, according to the mold can find the corresponding table, and then go to the corresponding table to do the query operation, the following PHP related operations, registration and login.

register.php

　　　　　　

1<?PHP2     //Receive Parameters3 //Test parameters are: Pwd,name, Email4     Extract($_post);5 6     //check whether it is empty7     if(Empty($PWD) ||Empty($Name) ||Empty($Email)){8          die(' parameter cannot be empty! ‘);9     }Ten  One     //connecting to a database A     $link=mysql_connect(' 127.0.0.1:3306 ', ' root ', ' root '); -     if(!$link){ -          die(' Database connection failed! ‘); the     } -  -     //Select database: This database has: UUID table (generating ID table), USER0 table (storing 0 of user information), User1 table (user information stored as 1) and User2 table (user information with 2 stored in the module) -     mysql_select_db(' Test '); +  -     $sql= ' INSERT into UUID VALUES (null) '; +     if(mysql_query($sql,$link)){ A         //get the ID you just inserted at         $id=mysql_insert_id(); -  -         //based on id%3 to determine which table the new user is holding -         $table _name= ' User '.$Id%3; -  -         $pwd=MD5($PWD); in         $sql= "INSERT into$table _nameVALUES ($id,$Name,$pwd,$Email)"; -  to         if(mysql_query($sql,$link)){ +             Echo' Registered successfully! ‘; -}Else{ the             Echo' Registration failed! ‘; *         } $}

register.php

login.php

　　　　　　

1<?PHP2     Header("Content-type:text/html;charset:utf-8");3     4     //Receive parameters: Id, Pwd5     Extract($_post);6 7     //determines whether the empty8     if(Empty($Id) ||Empty($PWD)){9          die(' parameter cannot be empty! ‘);Ten     } One  A     //connecting to a database -     $link=mysql_connect(' 127.0.0.1:3306 ', ' root ', ' root '); -     if(!$link){ the          die(' Connection Failed! ‘); -     } -  -     //Select Database +     mysql_select_db(' Test '); -  +     $table _name= ' User '.$Id%3; A     $sql= "SELECT * from"$table _nameWHERE Id =$Id"; at     $rst=mysql_query($sql,$link); -  -     if($row=Mysql_fetch_assoc($rst)){ -         $db _pwd=$row[' PWD ']; -          -         if($db _pwd==MD5($PWD)){ in             Echo' Login Successful! ‘; -}Else{ to             Echo' User name or password is wrong! ‘; +         } -}Else{ the         Echo' ID Error! ‘; *}

login.php

　　1.1 Log in by email, split the table by mailbox

　　　　

The basic principle of the mailbox is similar to the ID, is to pass an algorithm to the MD5 string into decimal number, and then modulo, the following is the hexadecimal MD5 string into a decimal function.

　　　　

　　　　

　　　　

MySQL Sub-table technology (learning experience)