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

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>

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>

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>

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

http://www.bkjia.com/PHPjc/1040159.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/1040159.htmlTechArticle100 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 library table hashing and other methods to optimize data reading and writing speed. The author did...