Implement 100 million pieces of data in Mysql database into 100 tables and 100 mysql tables in PHP

Implement 100 million pieces of data in Mysql database into 100 tables and 100 mysql tables in PHP_PHP Tutorial

WBOY

Release： 2016-07-13 09:45:37

Original

941 people have browsed it

100 million pieces of data are implemented in PHP with 100 MySQL database tables and 100 mysql tables

When the amount of data increases sharply, everyone will choose database table hashing Wait for ways to optimize data reading and writing speed. The author made a simple attempt, with 100 million pieces of data divided into 100 tables. The specific implementation process is as follows:

First create 100 tables:

<span><span> 1</span> <span>$i=0;
</span><span> 2</span> while($i<span><</span><span>=99</span><span>){
</span><span> 3</span> <span>echo "$newNumber \r\n";
</span><span> 4</span> <span>$sql</span><span>="CREATE TABLE `code_"</span><span>.$i."` (
</span><span> 5</span> <span> `full_code` char(10) NOT NULL,
</span><span> 6</span> <span> `create_time` int(10) unsigned NOT NULL,
</span><span> 7</span> <span> PRIMARY KEY  (`full_code`),
</span><span> 8</span> <span>) ENGINE</span><span>=MyISAM </span><span>DEFAULT CHARSET</span><span>=utf8";
</span><span> 9</span> <span>mysql_query($sql);
</span><span>10</span> <span>$i++; </span></span>

Copy after login

Let me talk about my table splitting rules. full_code is used as the primary key. We hash full_code

The function is as follows:

<span><span>1</span> <span>$table_name=get_hash_table('code',$full_code);
</span><span>2</span> <span>function get_hash_table($table,$code,$s=100){
</span><span>3</span> <span>$hash = sprintf("%u", crc32($code));
</span><span>4</span> <span>echo $hash;
</span><span>5</span> <span>$hash1 = intval(fmod($hash, $s));
</span><span>6</span> <span> return $table."_".$hash1;
</span><span>7</span> } </span>

Copy after login

Get the table name where the data is stored through get_hash_table before inserting data.

Finally we use the merge storage engine to implement a complete code table

<span><span>1</span> <span>CREATE TABLE IF NOT EXISTS `code` (  
</span><span>2</span> <span>`full_code` char(10) NOT NULL,
</span><span>3</span> <span>`create_time` int(10) unsigned NOT NULL,
</span><span>4</span> <span>INDEX(full_code)  
</span><span>5</span> ) TYPE=MERGE UNION=(code_0,code_1,code_2.......) INSERT_METHOD=LAST ; </span>

Copy after login

In this way, we can get all the full_code data by selecting * from code.