Principles of database table sharding and database sharding and implementation method based on thinkPHP

Why table sharding and database sharding: when the data volume and access volume of our data tables are large or frequently used, a data table cannot afford such a large amount of data access and storage, to reduce the burden on the database and accelerate data storage, You need to divide a table into multiple tables and store a type of data into several different tables.

Why table sharding and database sharding: when the data volume and access volume of our data tables are large or frequently used, a data table cannot afford such a large amount of data access and storage, to reduce the burden on the database and accelerate data storage, You need to divide a table into multiple tables and store a type of data into several different tables.

Why table sharding? database sharding:

When the data volume and access volume of our data tables are large or frequently used, a data table cannot afford such a large amount of data access and storage. Therefore, in order to reduce the burden on the database, to speed up data storage, we need to divide a table into multiple tables and store a type of data into several different tables. When the table shard cannot meet the requirement, we can also split the database, and several databases are used for storage.

Table sharding has different implementation methods based on different requirements and functions. The following is an example of my project:

Requirement: the two tables of product and product_price are one-to-multiple relationships, and the daily prices of products and products. One product corresponds to several prices. Because the data volume of the product table is large, every day there are more than a million pieces of data, on the day as the unit of table sharding, the unit of month for database sharding, the table named 'product _ price2014-07-20 'table format: the first is the name of the original database table, followed by the date (year-month-day ).

The code for creating a database and a table is as follows:

Function get_product_price_table () {$ db_info = array (); // This month $ newmot_time = date ("Y-m "); // today's date $ newday_time = date ("Y-m-d"); // this month's database, today's table $ db_name = 'acbooking '. $ newmot_time; $ table_name = "product_price ". $ newday_time; // date of Yesterday $ yesday_time = date ("Y-m-d", strtotime ("-1 day"); // retrieve the data of yesterday, get the last id of product_price as the starting value of the new table id $ last_one_product_price = get_info ('product _ price_table_id ', array ('time' => $ yesday_time )); if ($ last_one_product_price ['table _ id_end ']> 0) {$ table_id = $ last_one_product_price ['table _ id_end'] + 1;} else {$ table_id = 1 ;} // create a database

$ Db_string_line = C ('db _ type '). '://'. C ('db _ user '). ':'. C ('db _ pwd '). '@'. C ('db _ host '). '/'. $ new_db_database; $ DB_P = C ('db _ prefix'); $ Model = M ($ table, $ DB_P, $ Db_string_line ); $ db_name = $ Model-> execute ($ SQL); // create a new database tag using SQL statement configuration. The SQL statement is omitted here.

// Create a data table

$Db_string_line = C('DB_TYPE').'://'.C('DB_USER').':'.C('DB_PWD').'@'.C('DB_HOST').'/'.$new_db_database;$DB_P=C('DB_PREFIX');$Model=M($table,$DB_P,$Db_string_line);$table_name=$Model->execute($sql)；

// Return to the new database. The new table $ db_info ['database'] = $ db_name; $ db_info ['table'] = $ table_name; return $ db_info ;}

After creating a sub-table, you can store all the data of the day. You can change the table every day, which is highly efficient ....