MySQL sub-table + query

Vertical Sub-table:

In fact, there is nothing to say, that is, the primary key + common columns in the original table , and then the primary key + some non-commonly used columns placed in another table .

Such a data page can hold more data. However, the drawbacks are obvious and may increase operations such as join or union.

Horizontal Sub-table:

Today's interview was asked to the level of the table, suddenly stunned, the points are known, but how to effectively query the end is not good to say.

Principle: Concrete situation concrete analysis.

Several common sub-methods:

1, according to time points

Typical application: News category, QQ status, circle of friends and other real-time or recent, can be divided by time, such as a table in the month, a table last month.

2. Divide by interval

Typically, each table will have a self-increment ID, which can be used for self-increment ID points, such as

User1 table is 1~50

User2 table is 51~100//insert after the operation is completed, determine the ID value, more than 50w, create a new table

3, the hash of the table

Essentially no meaning, for each inserted data to take the mold, for the heads-up record query is OK, if the query adjacent rows of data, it is tragic. Interested students can see the following links related to the first article.

Query after table:

1, for the time level of the table:

Suppose a friend Circle state table, every day will produce 20w records, build table table_20150401 storage. Then use cron to run a script that creates a new table every night, such as table_20150402. In the PHP program

Execute the inserted function to encapsulate, using the following two functions.

InsertData ($data){    $table= "Table_".Date("Ymd", Time());//generate the day table nameInsert$data,$table);//insert a new table}getdata ($condition){      //If the condition takes time to look up, for example: $condition [times], the corresponding table name is parsed, and one or more tables are selected    $table= "Table_".Date("Ymd", Time());//generate the day table nameGet ($condition,$table);//find in a new table}

2, for the ID interval division

News or friends Circle status ID 1~1000 1001~2000
First, the database inside the table breakup_table//This table special records, new sub-table and the original table record number, convenient to determine which table to find
For example: News_1 1//Starting key is 1
News_2 1001//Starting key is 1001
News_3 2001

Second, the above news_1 and other data, the first time need to take the database, and then can be placed in the session or memcached inside

Insertuser ($data){
　　 $table _num = Getcurrentnewnum ();    This value is wrapped with memcached, if ($count < 2001) return 3;elseif ($count < 1001) return 2;else 1; $table= "Table_".$table _num);//generate the specified table nameInsert$data,$table);//insert a new table}getuser ($condition) { $table _num = Getcurrentnewnum ();     $table= "Table_".$table _num);//generate the specified table nameGet ($condition,$table);//find in a new table}

3, Hash sub-table:

Typical user table//pull out alone, because it is more special, the first lookup may take time, because the user name must determine which table to find
        can this Do:
                $md 5_val = MD5 ($ USER_NAME); //with CRC32 () should also be able, but not tried, if you try to remember%u so that it is not negative
                 $first _val = substr ($md 5_val, 0,1);//Then go to the first value
    &NB sp;           $decimal = Hexdec ($first _val); Hexadecimal goto decimal
                $table _num = $decimal%3 + 1; For the remainder of 3, making only three sheets, Table_1,table_2,table_3
            main Original User_name uniqueness, the MD5 uniqueness is derived, and the remainder limit is calculated by the number of tables

 Insertuser ( $data    $table  = "Table_".  $table _num ); //  generates the specified table name  insert ( $data ,  $table ); //  insert a new table in  }getuser (  $condition    $table  = "Table_".  $table _num ); //     generate the specified table name   Get ( $condition ,  $table ); // } in a new table 

Hash table can also be used for ID interval sub-table, that is, the ID value to modulo.

A horizontal table, a typical disadvantage, is a disaster for queries such as group by or order by.

RELATED links:

Http://www.phpddt.com/php/mysql-tables.html//good

　　

MySQL sub-table + query